PostgreSQL 8.3 - issues with autovacuum

Question

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).

• azp74: I would have liked to have accepted both answers, as they're both useful. However, I accepted Scott's as it highlighted a couple of extra settings for me to check out.

Answer 1

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.

Answer 2

• I can't find any evidence that 8.3 autovacuum is buggy. Autovacuum has been improved in 8.4 with free space maps which obsoletes some parameters which:

  (when set incorrectly) could make vacuum less effective

  so with 8.3 it is important to set them correctly.

• The postgres wiki says (and I'm inclined to agree):

  As of 8.3, autovacuum is turned on by default, and you should keep it that way.

  and:

  The answer to almost all vacuuming problems is to vacuum more often, not less, so that each individual vacuum operation has less to clean up.

• As you say, "adding entries to pg_autovacuum does the trick" when autovacuum needs to be tweaked - but just be aware that "pg_autovacuum [is] not saved in database dumps"