Re: optimizing vacuum truncation scans - Mailing list pgsql-hackers

On Mon, Apr 20, 2015 at 10:18 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 4/20/15 1:50 AM, Jeff Janes wrote:

    For that matter, why do we scan backwards anyway? The comments don't
    explain it, and we have nonempty_pages as a starting point, so why
    don't we just scan forward? I suspect that eons ago we didn't have
    that and just blindly reverse-scanned until we finally hit a
    non-empty buffer...


nonempty_pages is not concurrency safe, as the pages could become used
after vacuum passed them over but before the access exclusive lock was
grabbed before the truncation scan.  But maybe the combination of the
two?  If it is above nonempty_pages, then anyone who wrote into the page
after vacuum passed it must have cleared the VM bit. And currently I
think no one but vacuum ever sets VM bit back on, so once cleared it
would stay cleared.

Right.

In any event nonempty_pages could be used to set the guess as to how
many pages (if any) might be worth prefetching, as that is not needed
for correctness.

Yeah, but I think we'd do a LOT better with the VM idea, because we could immediately truncate without scanning anything.

Right now all the interlocks to make this work seem to be in place (only vacuum and startup can set visibility map bits, and only one vacuum can be in a table at a time).  But as far as I can tell, those assumption are not "baked in" and we have pondered loosening them before.  

For example, letting HOT clean up mark a page as all-visible if it finds it be such.  Now in that specific case it would be OK, as HOT cleanup would not cause a page to become empty (or could it?  If an insert on a table with no indexes was rolled back, and hot clean up found it and cleaned it up, it could conceptually become empty--unless we make special code to prevent it) , and so the page would have to be below nonempty_pages.  But there may be other cases.

And I know other people have mentioned making VACUUM concurrent (although I don't see the value in that myself).

So doing it this way would be hard to beat (scanning a bitmap vs the table itself), but it would also introduce a modularity violation that I am not sure is worth it.  

Of course this could always be reverted if its requirements became a problem for a more important change (assuming of course that we detected the problem)