Re: Vacuum and Transactions

On T, 2005-10-04 at 00:26 -0400, Rod Taylor wrote:
> As I understand it vacuum operates outside of the regular transaction
> and if you stop it (SIGTERM, or pg_cancel_backend()) some of the work it
> accomplished will be kept when it rolls back.
> 
> For large structures with a ton of dead entries (which I seem to have a
> case), running vacuum takes long enough that high-churn structures begin
> to experience difficulties.
> 
> Is it reasonable to cancel and restart the vacuum process periodically
> (say every 12 hours) until it manages to complete the work? It takes
> about 2 hours to do the table scan, and should get in about 10 hours of
> index work each round.

It seems that the actual work done by LAZY VACUUM is not rolled back
when you kill the backend doing the vacuum (though VACUUM is quite hart
to kill, and may require KILL -9 to accomplis, with all the downsides of
kill -9).

So, yes, as a last resort you can kill VACUUM (or rather limit its
lifetime by "set statement_timeout = XXX") and get some work done in
each run. It only makes sense if the timeout is big enough for vacuum to
complete the first scan (collect dead tuples) over the heap and then do
some actual work. For table with 3 indexes the timeout must be at least
(1.st heap scan + 3 indexscans with no work + some portion of 2nd
(cleanuout) heap scan )  to ever get the table completely cleaned up.

> The vacuum ignores vacuum transaction concept looks handy right now.

There is a patch for 8.1 in PATCHES list (postponed to 8.2 :( ). This
can be backported to 8.0 quite easily.

-- 
Hannu Krosing <hannu@skype.net>