Re: Resumable vacuum proposal and design overview

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> On Tue, 2007-02-27 at 10:37 -0600, Jim C. Nasby wrote:
>> ... The idea would be to give vacuum a target run time, and it
>> would monitor how much time it had remaining, taking into account how
>> long it should take to scan the indexes based on how long it's been
>> taking to scan the heap. When the amount of time left becomes less than
>> the estimate of the amount of time required to scan the indexes (and
>> clean the heap), you stop the heap scan and start scanning indexes.

> I do like this idea, but it also seems easy to calculate that bit
> yourself. Run VACUUM, after X minutes issue stop_vacuum() and see how
> long it takes to finish. Adjust X until you have it right.

One problem with it is that a too-small target would result in vacuum
proceeding to scan indexes after having accumulated only a few dead
tuples, resulting in increases (potentially enormous ones) in the total
work needed to vacuum the table completely.

I think it's sufficient to have two cases: abort now, and restart from
the last cycle-completion point next time (this would basically just be
SIGINT); or set a flag to stop at the next cycle-completion point.

It occurs to me that we may be thinking about this the wrong way
entirely. Perhaps a more useful answer to the problem of using a
defined maintenance window is to allow VACUUM to respond to changes in
the vacuum cost delay settings on-the-fly. So when your window closes,
you don't abandon your work so far, you just throttle your I/O rate back
to whatever's considered acceptable for daytime vacuuming.

regards, tom lane