Date: Sun, 26 Nov 2006 19:01:35 -0600
From: "Jim C. Nasby" <jim@nasby.net>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: When to vacuum a table?
Message-ID: <20061127010135.GK39519@nasby.net>
References: <1164540257.7902.0.camel@panoramix>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1164540257.7902.0.camel@panoramix>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Sun, Nov 26, 2006 at 12:24:17PM +0100, Joost Kraaijeveld wrote:
> Hi,
> 
> 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?

Most of the time I just turn autovac on, set the scale factors to
0.2/0.1 and the thresholds to 300/200 and turn on vacuum_cost_delay
(usually set to 20). That's a pretty decent setup for most applications.
It also doesn't hurt to run a periodic vacuumdb -av and look at the tail
end of it's output to make sure you have adequate FSM settings.

The exception to that rule is for tables that are very small and have a
lot of churn; I'll vacuum those by hand very frequently (every 60
seconds or better).
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)