Date: Tue, 5 Jun 2007 09:25:46 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-admin@postgresql.org
Subject: Re: the right time to vacuum database?
Message-ID: <20070605132546.GI4818@phlogiston.dyndns.org>
References: <1181039589.643323.59660@r19g2000prf.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1181039589.643323.59660@r19g2000prf.googlegroups.com>
User-Agent: Mutt/1.5.9i

On Tue, Jun 05, 2007 at 03:33:09AM -0700, Charles.Hou wrote:
> how can i know that it's the time to vacuumdb? i set the crontab to
> vacuumdb 3 times in one day. because my database size increase from
> 440MB to 460MB in 8 hours.

You haven't told us enough.  What's the churn on the database, to
begin with?  That's only 20M.  If you replace approximately 20M in 8
hours, then it might be that the FSM is working perfectly, and this
is the overhead that is reasonable to keep around to allow your
database to work perfectly.

Also, have you looked into autovacuum?  It might take some of the
guesswork out of this.

Finally, I expect that most of your churn is in particular tables. 
Those are the ones you want to vacuum frequently.  The rest of them
aren't that interesting.  The way to learn about this is to use
VACUUM VERBOSE, which will give you information about how many pages
it is recovering.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
		--H.W. Fowler