The default postgres configuration as seen in a lot of vmware appliances may not be optimum to ensure autovacuum is keeping up to date.
postgres is different to other many other databases in that when a row is deleted its not actually removed from disk. Its marked as a dead row.
Vacuuming is the process which clears up these dead rows. Leaving them build will cause queries to perform worse and worse over time.
postgres has an automated process which takes care these dead rows.
However sometimes its not aggressive enough.
Postgres enginning in VMware have suggested the following update and its working great! soon to be seen in a product near you.
You need to update the postgresql.conf and modify 2 default values.
autovacuum_max_workers = 2 + CPU count, autovacuum_vacuum_cost_limit = 300 * CPU count.
autovacuum_vacuum_cost_limit – default is 200 – should be 1600
