Re: [GENERAL] How to know when to vacuum

Yes, but... does postgres maintain some statistics that could be queried
to determine whether vacuuming would be helpful? For Case 1 I would need
to know how many records were added since the last vacuum relative to the
total number of records in each table.  For case 2 I guess you really only
need to know how many records have been deleted.

Any way to get that information?

Chris

On Tue, 28 Jul 1998, The Hermit Hacker wrote:

> 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..
>
>
>
>
>