I have a pg database that just suffered through a multiple-hour vacuum of one of its tables. It was pretty painful. Autovacuum is configured to run every 12 hours, but for whatever reason it didn't see fit to vacuum this very busy table for substantially longer than that. (Not sure exactly how long. Over a week is my guess.)
The problem is that one size does not fit all. On smaller tables, the default autovacuum_vacuum_scale_factor of 0.4 is just fine. On this table waiting that long is unacceptable.
Comments
Yes, you only need an exclusive lock for a full vacuum, so avoid those unless absolutely necessary. But that's been the case since they split vacuum into "normal" and "full." (7.2?)
I do seem to remember seeing vacuum stuff in the 8.x changelogs, but I don't remember details. 7.3 is hella ancient at this point... There's lots of good reasons to upgrade b/s vacuum.
No, it just marks the space as ready for re-use. So you should see it hold steady at "max space ever used by this table" after a couple days.
INSERT INTO pg_autovacuum (oid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit) VALUES (
(SELECT oid FROM pg_class WHERE relname = 'bigtable'), 't'::boolean, 1000, 0.0, 0.0, 0.01);
Which means vacuum bigtable after 1000 rows have been obsoleted, and analyze it after 1% of rows have changed.
For details, please Read The Fine Manual. Actually, I'm continually surprised that people have problems with this since it's pretty well documented. Perhaps we should err in the direction of being more aggressive with the avd settings by default.
jarno, when you did the pg_dump from 7.3 to load into 8.0 did you follow the explicitly documented requirement to use the 8.0 pg_dump binary?
Thanks for the comment.