We have a PostgreSQL 8.3.7 database suffering severe bloat after an algorithmic change. Unfortunately upgrading isn't an option at this time.
For a particular group of partitioned count roll-up tables, we used to update them by selecting then either inserting for new counts or updating existing counts. To avoid network saturation we switched instead to updating, checking for failed updates and then inserting.
I've read this is a bad scenario for PostgreSQL (at least circa 8.3.7) where the dead tuples from the updates are in the middle of the table rather than the end (as previously) and so are not being reclaimed by the autovacuum
which works from the back of the table.
It seems to me that autovacuum_vacuum_cost_limit
is the most likely setting I should change. Currently it is set to the default of 200 - perhaps I should start at 2,000 and go up from there?
I have a small window to make production changes to use trial and error, and no test database of equivalent size.