Re: Why do we let autovacuum give up?

On 01/24/2014 11:32 AM, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> Claudio Freire escribió:
>>> If you ask me, I'd like autovac to know when not to run (or rather
>>> wait a bit, not forever), perhaps by checking load factors or some
>>> other tell-tale of an already-saturated I/O system.
>
>> We had a proposed design to tell autovac when not to run (or rather,
>> when to switch settings very high so that in practice it'd never run).
>> At some point somebody said "but we can just change autovacuum=off in
>> postgresql.conf via crontab when the high load period starts, and turn
>> it back on afterwards" --- and that was the end of it.
>
> The hard part of this is that shutting down autovacuum during heavy
> load may be exactly the wrong thing to do.

Yep. In fact, it may be appropriate to limit or stop autovacuum's work
on some big tables, while pushing its activity even higher for small,
high churn tables.

If you stop autovacuum on a message-queue system when load gets high,
you'll get a giant messy bloat explosion.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services