Message-ID: <4C6996CB.9090808@2ndquadrant.com>
Date: Mon, 16 Aug 2010 15:51:39 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Peter Koczan <pjkoczan@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: Is regular vacuuming with autovacuum needed?
References: <AANLkTik63hP0v1wpstYJptr_ixiHaMjCF3tNpK+5XOZW@mail.gmail.com>
	<19036.1281983716@sss.pgh.pa.us>
In-Reply-To: <19036.1281983716@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> On versions where autovacuum is on by default, I would certainly
> recommend trying to use only autovacuum.  cron-driven vacuum still
> has some uses but they are corner cases.
>   

Corner cases implies something a bit more rare than I'd consider the 
case here.  Consider a server where you know you have a large table that 
ends up with 5% dead rows each day.  This will cause autovacuum to kick 
in to clean up about every 4 days, at the defaults where 
autovacuum_vacuum_scale_factor = 0.2.

When it does finally get to that table, it's going to have a fairly 
large amount of work to do.  If that happens during peak load time on 
your server, you may find that a painful shock.

In that situation, it's completely reasonable to manually vacuum that 
table each day during a known slow period, late at night for example.  
Then it will never get to where it's so bloated that a hefty autovacuum 
kicks in at an unpredictable time.

The other alternative here is to just tune autovacuum so it runs really 
slowly, so it won't kill responsiveness during any peak period.  While 
in theory that's the right thing to do, this is much harder to get 
working well than what I just described.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us