Re: vacuum strategy

On Mon, 25 Nov 2002, CSN wrote:

> What's the typical way to handle pg vacuuming? Have a
> somewhat frequently called script do it periodically,
> or just make a script for cron?
>
> Also, isn't there a project related to this that
> handles it automatically (if so please give the URL)?

The basic rule of thumb is to vacuum a table whenever it's had more than
about 25% turnover. Of course, on very large tables, as little as 5%
turnover could justify a vacuum. The same basic number applies for
analyzing.

Plain vacuums can be run as often as you'd like really, as they consume
little bandwidth and are non-blocking.

vacuum full should be run during off peak hours. While normal non-full
vacuums are good enough for most uses, it's occasionally necessary to run
a full vacuum to reclaim tuples that the normal vacuum couldn't free (if
there are a bunch freed between regular vacuums, it sometimes isn't
possible to free them.

Also, full vacuums are required every so often to keep the transaction id
from rolling over. It rolls over at 4 billion, so you don't have to run a
full vacuum all that often for that.

There is an autovacuum daemon in the works, you can find it on gborg at:

http://gborg.postgresql.org/project/pgavd/projdisplay.php

I haven't had a chance to play with it, but I did just download it and
plan on playing with it a bit.