Re: Is regular vacuuming with autovacuum needed?

On Mon, Aug 16, 2010 at 4:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Smith <greg(at)2ndquadrant(dot)com> writes:
>> 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.
>
> But you really have to do that *anyway*, if you're not going to turn
> autovac off.
>
> I think the case where you want to combine cron-driven vacuum with
> autovac is where, having made sure autovac is dialed down enough to not
> present performance issues, you find that it can't keep up with the
> required vacuuming.  Then you need to do some not-hobbled vacuuming
> during your maintenance windows.  Eventually probably autovac will have
> some understanding of maintenance windows built-in, but it doesn't yet.

If the global values aren't vacuuming that table enough, then it seems
apropos to change the autovacuum_vacuum_threshold value to some lower
value for that table.

(And it seems to me that if autovac never kicks in until 10% of a
table's "dead," that's not nearly aggressive enough, possibly even
with the global value! Given the 8.4 visibility map changes,
shouldn't autovac be a tad more aggressive, when it should be spending
little time on non-dead material? If the visibility map is doing its
job, then the global threshold can be set pretty low, no?)

I'm not quite sure what to think maintenance windows would look
like... I see them having at least two distinct forms:
a) A maintenance that is particularly for vacuuming, where factors
would get dialed down to encourage vacuuming tables that mayn't have
been hit lately;
b) A maintenance where it is expected that Things Are Being Changed,
where it might be pretty well necessary to shut off autovac so it
doesn't interfere with DDL work.
--
http://linuxfinances.info/info/postgresql.html