Re: Vacuum thoughts

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> You keep ignoring the problem of removing index entries. To vacuum an
> individual page, you need to be willing to read in (and update) all
> index pages that reference the tuples-to-be-deleted.

Hm. If the visibility information were stored in the index then that could be
postponed and done when the index page was read or when it was flushed.

So when you do a sequential scan it marks any tuples it finds as dead as free
in the free space map. And when you do an index scan it marks any index
entries it finds dead as free in the free space map.

That wouldn't eliminate the need for vacuum though. It would be pretty easy to
delete chunks of tuples and never look at those index values again. Especially
since deleting them might not even bring the index pages in at all.

That said, I guess if you never look at the pages again, the wasted space in
them isn't having any performance penalty anyways.

> The latter point is really the crux of the problem. The point of having
> the VACUUM process is to keep maintenance work out of the critical path
> of foreground queries. Anything that moves even part of that
> maintenance work into the critical path is going to be a net loss.

I could envision a system where the process is guaranteed a certain number of
empty pages. Whenever it grabs one the last page on the LRU is queued to be
cleaned. It doesn't have to actually get cleaned and flushed in the critical
path unless the transaction starves of ready-to-use pages. If it does it's
probably a batch transaction anyways and should switch to reusing the same
pages over and over to avoid flushing everything from the shared pages.

--
greg