I posted this on StackOverflow and it was suggested this query was better suited here.
I'm trying to encourage the use and monitoring of autovacuum in some PostgreSQL 8.3 databases.
One objection I hit often is that people don't "trust" autovacuum or there are bugs in autovacuum in 8.3 which mean that it's ignored in preference to scheduling vacuuming. Mostly our tables are small and this approach appears to work. However, with our larger (& also heavily updated tables) this really doesn't work (dead tuple counts increase, exceed max_fsm_pages, and the tables don't get cleaned up etc etc).
I'm just wondering if anyone has a reference for autovacuum in 8.3 being buggy or not working. My own experience has shown that autovac works fine and, where necessary, adding entries to the pg_autovacuum table does the trick.
I'd like to understand the problem with autovacuum (if one exists).
Best Answer
I run some extremely busy 8.3 db servers. When I first started working on them, they were blowing out their free space map settings and going off the rails on a semi-weekly basis. The solution was to crank up the fsm settings, AND to make autovacuum far more aggressive.
autovacuum_vacuum_cost_delay was dropped to 0 or 1ms autovacuum_vacuum_cost_limit was raised to 5000 max_fsm_pages was raised to 2M to 10M depending on the machine max_fsm_relations was raised to 10k to 100k depending on the machine autovacuum_max_workers was raised to 5 or 10 depending on the machine
These machines all have fairly powerful IO subsystems (8 to 32 15K SAS drives with various HW RAID cards or SANs).
In short if someone thinks autovac in 8.3 is buggy and won't use it, they likely don't really understand it very well, and are behaving in a particular way based on superstition, not science.