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.