> > Is it reasonable to cancel and restart the vacuum process periodically
>
> No.
>
> How big is that table, anyway? Are you trying a VACUUM FULL, or plain
> vacuum?
It's only about 60GB in size but appears it has been missed for nearly a
month for vacuum and probably has a large percentage dead material (30%
or so).
I'm trying to run a plain vacuum and the Pg version is 8.0.3.
Building indexes on this structure also takes a significant amount of
time. I have maintenace_work_mem set to about 1GB in size.
The catch is that there are some other very active structures (like
pg_listener for Slony) which after a couple of hours without vacuuming
will quickly have the DB at an unreasonably high load (low tens) which
seems to all but halt the vacuum on the large structure.
Rightfully the table should be partitioned by time, but I haven't quite
figured out how to delete data from the old structure without increasing
the vacuum time required.
Another alternative I'm considering is to create new partial indexes for
the active structures, drop all of the old full table indexes and run
vacuum on that, then partition it.
--