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