Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] How to know when to vacuum

From: Chris Johnson <cmj(at)inline-design(dot)com>
To: The Hermit Hacker <scrappy(at)hub(dot)org>
Cc: pgsql-general(at)postgreSQL(dot)org
Subject: Re: [GENERAL] How to know when to vacuum
Date: 1998-07-28 15:27:49
Message-ID: Pine.LNX.4.00.9807281121070.411-100000@boreus.bedfo.ma.tiac.net (view raw or flat)
Thread:
Lists: pgsql-general
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.. 
> 
> 
> 
> 
> 


In response to

pgsql-general by date

Next:From: The Hermit HackerDate: 1998-07-28 15:43:19
Subject: Re: [GENERAL] Postgres vs commercial products
Previous:From: Matt McClureDate: 1998-07-28 15:22:58
Subject: row oids as "foreign keys" in other tables ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group