Re: VACUUM

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> ... As for vacuum
> full, with frequently enough "lazy" vacuuming you wont need to vacuum
> full very often. If you can afford to lock your tables, then I'd say do
> it once a night. If not, you need to decide when is a good time, but I
> think the docs recommend doing it at least once every 1 billion
> transactions, though more often is definitely better here too.

The intention is that if you do plain ("lazy") vacuum often enough to
keep your tables from bloating unreasonably, you shouldn't ever have to
do a "full" vacuum. How much is "unreasonable" is up to you to decide.

For instance, if under peak load you might update 10% of the rows in
a particular table every hour, then hourly lazy vacuums would keep you
at a steady state of about 10% wasted space in that table. That seems
pretty workable to me. If you waited 100 hours then the table would
have bloated to about 10X its minimum size, and at that point you'd
most likely want to do a vacuum full to reclaim the wasted space.

(Right now the only way to set up an appropriate vacuuming schedule is
for the DBA to hand-craft a cron script. I'd like to see the database
able to launch automatic background vacuum runs by itself.)

The business about "you must vacuum at least every billion transactions"
is a different issue. Either plain or full vacuum will satisfy that
requirement.

There is more discussion in the Admin Guide ---
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/maintenance.html

regards, tom lane