Message-Id: <456AE144.EE98.0025.0@wicourts.gov>
Date: Mon, 27 Nov 2006 12:59:48 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl>,
	<pgsql-performance@postgresql.org>
Subject: Re: When to vacuum a table?
References: <1164540257.7902.0.camel@panoramix>
In-Reply-To: <1164540257.7902.0.camel@panoramix>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

>>> On Sun, Nov 26, 2006 at  5:24 AM, in message
<1164540257.7902.0.camel@panoramix>, Joost Kraaijeveld
<J.Kraaijeveld@Askesis.nl> wrote: 
> 
> Are there guidelines (or any empirical data) available how to
determine
> how often a table should be vacuumed for optimum performance or is
this
> an experience / trial- and- error thing?
 
For most of our databases we use a daily "VACUUM ANALYZE VERBOSE;".  We
grep the results to show large numbers of removable  or dead rows and to
show the fsm numbers at the end.  There are a few small tables with high
update rates which this doesn't cover.  To handle that we set the
autovacuum to 0.2/0.1 and 1/1 on a ten second interval and do a daily
CLUSTER of these tables.  We really don't have many tables which can hit
these autovacuum thresholds in one day.
 
We have databases which are 400 GB with the vast majority of that being
in tables which are insert-only except for a weekly purge of data over a
year old.  We do nightly vacuums on the few tables with update/delete
activity, and a weekly vacuum of the whole database -- right after the
delete of old rows from the big tables.
 
-Kevin