Re: [GENERAL] How to know when to vacuum

On Tue, 28 Jul 1998, Chris Johnson wrote:

>
> OK, so there's been quite a bit of traffic about vacuuming databases as
> well as more than one suggestion on how to do it. But there really hasn't
> been an answer to the question of how to know when to vacuum.
>
> I now vacuum the databases every night, but this seems somewhat
> inefficient... I know that some of the more active databases could use
> some extra vacuuming, but which ones? how often? If there is a way to ask
> postgres whether a specific database could use a vacuuming I will be happy
> to write something to automate it, but I see no way to do so.
>
> Anyone have any suggestions?

There are two reasons, that I can think of, to run vacuum:

1. update statistics used for the optimizer
2. clean up "fragmentation"

1. the optimizer decides whether or not to use indices, and which ones it
uses, based on a miriad of values, but one of them is based on
statistics that vacuum generates. ie. if the table is small, it might
be faster to just do a sequential scan vs using an index. As such, a
vacuum should be performed after a large amount of inserts/deletes or
updates have been performed, so that the optimizer has reasonably
accurate numbers to work with. VACUUM ANALYZE can be used for this
one, which, in the future, will hopefully not lock down the database
while its being performed.

2. the server currently doesn't "reuse" deleted rows, but just keeps
appending them to the end. running a straight VACUUM will perform a
de-fragmentation by essentially re-writing the database and then
performing equivalent to an 'ftruncate()' at the end to shrink the
table size back down again. The only time you should have to do a full
VACUUM is after a massive amount of DELETEs to a table...and,
hopefully, the requirement for that will decrease over time too, as
there has been talk about adding in functionality to reuse delete
rows..