From: Greg Smith <greg@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Koczan <pjkoczan@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is regular vacuuming with autovacuum needed?
Date: Mon, 16 Aug 2010 15:51:39 -0400
Message-ID: <4C6996CB.9090808@2ndquadrant.com> (raw)
In-Reply-To: <19036.1281983716@sss.pgh.pa.us>
References: <AANLkTik63hP0v1wpstYJptr_ixiHaMjCF3tNpK+5XOZW@mail.gmail.com>
<19036.1281983716@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: greg@2ndquadrant.com, tgl@sss.pgh.pa.us, pjkoczan@gmail.com
Subject: Re: Is regular vacuuming with autovacuum needed?
In-Reply-To: <4C6996CB.9090808@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox