Re: autovacuum not prioritising for-wraparound tables

On Sun, Feb 3, 2013 at 9:25 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
>
> I was able to confirm two cases where this was a consequence of the
> lazy truncate logic which Jan recently fixed, but there are clearly
> other problems which I didn't have much of a grasp on prior to this
> thread.  The only thing I knew for sure was that a bulk load, and
> in particular a pg_dump/restore cycle, was a time bomb without
> VACUUM FREEZE, and users are often reluctant to add time for that
> to their maintenance window.  If we can't load tuples as frozen in
> the first plance, the next best thing would be to nibble away at
> freezing them in the background, so that there is no single big
> hit.  While proposals here seem to deal with very real problems
> which we should fix, I'm not sure that anything here addresses this
> issue.

It seems like we already have all the machinery we need for this.

Don't run VACUUM FREEZE during the maintenance window, instead at the
end of the maintenance window set vacuum_cost_delay to nibble
(whatever integer that translates to on your system) and let 'er rip.
Since vacuum_cost_delay is session settable, it wouldn't interfere
with the setting for other things.

The only real problem I see with this is how to restart it so that it
picks up where it left off if it should get interrupted.

I guess giving vacuumdb new options to set the vacuum_cost settings it
runs under would be a handy feature for doing this.

Cheers,

Jeff