Re: New IndexAM API controlling index vacuum strategies

On Thu, Apr 1, 2021 at 8:25 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> I've also found a way to further simplify the table-without-indexes
> case: make it behave like a regular two-pass/has-indexes VACUUM with
> regard to visibility map stuff when the page doesn't need a call to
> lazy_vacuum_heap() (because there are no LP_DEAD items to set
> LP_UNUSED on the page following pruning). But when it does call
> lazy_vacuum_heap(), the call takes care of everything for
> lazy_scan_heap(), which just continues to the next page due to
> considering prunestate to have been "invalidated" by the call to
> lazy_vacuum_heap(). So there is absolutely minimal special case code
> for the table-without-indexes case now.

Attached is v10, which simplifies the one-pass/table-without-indexes
VACUUM as described.

Other changes (some of which are directly related to the
one-pass/table-without-indexes refactoring):

* The second patch no longer breaks up lazy_scan_heap() into multiple
functions -- we only retain the lazy_scan_prune() function, which is
the one that I find very compelling.

This addresses Robert's concern about the functions -- I think that
it's much better this way, now that I see it.

* No more diff churn in the first patch. This was another concern held
by Robert, as well as by Masahiko.

In general both the first and second patches are much easier to follow now.

* The emergency mechanism is now able to kick in when we happen to be
doing a one-pass/table-without-indexes VACUUM -- no special
cases/"weasel words" are needed.

* Renamed "onerel" to "rel" in the first patch, per Robert's suggestion.

* Fixed various specific issues raised by Masahiko's review,
particularly in the first patch and last patch in the series.

Finally, there is a new patch added to the series in v10:

* I now include a modified version of Matthias van de Meent's line
pointer truncation patch [1].

Matthias' patch seems very much in scope here. The broader patch
series establishes the principle that we can leave LP_DEAD line
pointers in an unreclaimed state indefinitely, without consequence
(beyond the obvious). We had better avoid line pointer bloat that
cannot be reversed when VACUUM does eventually get around to doing a
second pass over the heap. This is another case where it seems prudent
to keep the costs understandable/linear -- page-level line pointer
bloat seems like a cost that increases in a non-linear fashion, which
undermines the whole idea of modelling when it's okay to skip
index/heap vacuuming. (Also, line pointer bloat sucks.)

Line pointer truncation doesn't happen during pruning, as it did in
Matthias' original patch. In this revised version, line pointer
truncation occurs during the second phase of VACUUM. There are several
reasons to prefer this approach. It seems both safer and more useful
that way (compared to the approach of doing line pointer truncation
during pruning). It also makes intuitive sense to do it this way, at
least to me -- the second pass over the heap is supposed to be for
"freeing" LP_DEAD line pointers.

Many workloads rely heavily on opportunistic pruning. With a workload
that benefits a lot from HOT (e.g. pgbench with heap fillfactor
reduced to 90), there are many LP_UNUSED line pointers, even though we
may never have a VACUUM that actually performs a second heap pass
(because LP_DEAD items cannot accumulate in heap pages). Prior to the
HOT commit in 2007, LP_UNUSED line pointers were strictly something
that VACUUM created from dead tuples. It seems to me that we should
only target the latter "category" of LP_UNUSED line pointers when
considering truncating the array -- we ought to leave pruning
(especially opportunistic pruning that takes place outside of VACUUM)
alone.

(That reminds me -- the second patch now makes VACUUM VERBOSE stop
reporting LP_UNUSED items, because it is so utterly
misleading/confusing -- it now reports on LP_DEAD items instead, which
will bring things in line with log_autovacuum output once the last
patch in the series is in. This is arguably an oversight in the HOT
commit made back in 2007 -- that work kind of created a second
distinct category of LP_UNUSED item that really is totally different,
but it didn't account for why that makes stats about LP_UNUSED items
impossible to reason about.)

Doing truncation during VACUUM's second heap pass this way also makes
the line pointer truncation mechanism more effective. The problem with
truncating the LP array during pruning is that we more or less never
prune when the page is 100% (not ~99%) free of non-LP_UNUSED items --
which is actually the most compelling case for line pointer array
truncation! You can see this with a workload that consists of
alternating range deletions and bulk insertions that reuse the same
space -- think of a queue pattern, or TPC-C's new_orders table. Under
this scheme, we catch that extreme (though important) case every time
-- because we consider LP_UNUSED items immediately after they become
LP_UNUSED.

[1] https://postgr.es/m/CAEze2WjgaQc55Y5f5CQd3L=eS5CZcff2Obxp=O6pto8-f0hC4w@mail.gmail.com
--
Peter Geoghegan