Business Directory Security_privileges table is bloating in GBs

Business Directory Security_privileges table is bloating in 100 GB

* Shut down BD
* In /repository/data/postgresql.conf :
** turn on autovacuum (uncomment this line):
autovacuum = on 
** Uncomment the following lines:
vacuum_cost_page_hit = 1               
vacuum_cost_page_miss = 10          
vacuum_cost_page_dirty = 20           
log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions
Note: Uncomment the line above and set to 0 to see the autovacuum logging. Re-comment it and restart the repo to stop the logging that may slow the repository down.
 
* Using psql, run these commands. If needed, run these for every table that is bloating:
{code:sql}
alter table bdrepo.security_privileges set(autovacuum_enabled=true);
alter table bdrepo.security_privileges set(fillfactor=100);
alter table bdrepo.security_privileges set(autovacuum_analyze_scale_factor=0.2);
alter table bdrepo.security_privileges set(autovacuum_vacuum_cost_limit = 1000);
alter table bdrepo.security_privileges set(autovacuum_vacuum_scale_factor = 0);
alter table bdrepo.security_privileges SET (autovacuum_vacuum_threshold = 10000);
alter table bdrepo.security_privileges set(autovacuum_vacuum_cost_delay = 10);
{code}
To verify that all the settings have been added, run 
{code:sql}
select relname, reloptions, pg_namespace.nspname
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname like 'security_privileges';
{code}
* Restart the BD repository
* After some time, monitor the cs_repository-<weekday>.log. It should show autovacuum removing rows from a busy table. 
* Check to see if there are idle transactions holding a lock on the repository. This is what was preventing autovacuum from working on some busy tables before the fix in 7.0.6.00.02. It should be fixed in 7.0.6.00.02 and greater.
{code:sql}
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
{code}

If they haven't upgraded to 7.0.6.00.02 yet, setting  these  below mentioned  pool size configurations in "/apps/server/server_defs-<most recent version>.xml" to 0 may help : . However, there are some importat fixes that went into Pool manager implementation that is only available in 7.0.6 patch 2. 
* Pool Minimum Size (Current)
* Pool Minimum Size (On Server Restart)
* Monitor daemon Pool Minimum Size (Current)
* Monitor daemon Pool Minimum Size (Current)

*Manually vacuuming bloated tables*
Here is more information about a manual vacuum and how each type (manual or auto) works and what they do. 

According to Postgres documentation:
http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
{quote}
Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table.

VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed
{quote}
So autovacuum will *recycle* the space used by dead tuples to be reused by new tuples and will keep the table in at a reasonable size, but it won't *reclaim* the space if the table is already bloated. To reclaim the space, you can either 
* Dump the table to a file, drop the table, and then re-import the table. As Bart noticed when he dumped the table for us last month, the dead tuples were removed from the dump. 
* Run a VACUUM FULL on the table.