Re: Is regular vacuuming with autovacuum needed?

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