Re: New IndexAM API controlling index vacuum strategies

On Mon, Mar 8, 2021 at 10:57 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Yes, I agree that it's good to postpone this to a future release, and
> that thinking through the consequences is not so easy.

The current plan is to commit something like Masahiko's
skip_index_vacuum.patch for Postgres 14. The latest version of that
patch (a reduced-scope version of Masahiko's patch without any changes
to MaxHeapTuplesPerPage) is available from:

https://postgr.es/m/CAD21AoAtZb4+HJT_8RoOXvu4HM-Zd4HKS3YSMCH6+-W=bDyh-w@mail.gmail.com

The idea is to "unify the vacuum_cleanup_index_scale_factor feature
from Postgres 11 with the INDEX_CLEANUP feature from Postgres 12".
This is the broader plan to make that "unification" happen for
Postgres 14:

https://postgr.es/m/CAH2-WzkYaDdbWOEwSSmC65FzF_jRLq-cxrYtt-2+ASoA156X=w@mail.gmail.com

So, as I said, any change to MaxHeapTuplesPerPage is now out of scope
for Postgres 14.

> One possible
> consequence that I'm concerned about is sequential scan performance.
> For an index scan, you just jump to the line pointer you want and then
> go get the tuple, but a sequential scan has to loop over all the line
> pointers on the page, and skipping a lot of dead ones can't be
> completely free. A small increase in MaxHeapTuplesPerPage probably
> wouldn't matter, but the proposed increase of almost 10x (291 -> 2042)
> is a bit scary.

I agree. Maybe the real problem here is that MaxHeapTuplesPerPage is a
generic constant. Perhaps it should be something that can vary by
table, according to practical table-level considerations such as
projected tuple width given the "shape" of tuples for that table, etc.

Certain DB systems that use bitmap indexes extensively allow this to
be configured per-table. If you need to encode a bunch of TIDs as
bitmaps, you first need some trivial mapping from TIDs to integers
(before you even build the bitmap, much less compress it). So even
without VACUUM there is a trade-off to be made. It is *roughly*
comparable to the trade-off you make when deciding on a page size.

What I really want to do for Postgres 14 is to establish the principle
that index vacuuming is theoretically optional -- in all cases. There
will be immediate practical benefits, too. I think it's important to
remove the artificial behavioral differences between cases where there
are 0 dead tuples and cases where there is only 1. My guess is that
99%+ append-only tables are far more common than 100% append-only
tables in practice.

> It's also a little hard to believe that letting almost
> 50% of the total space on the page get chewed up by the line pointer
> array is going to be optimal. If that happens to every page while the
> amount of data stays the same, the table must almost double in size.
> That's got to be bad.

I think that we should be prepared for a large diversity of conditions
within a given table. It follows that we should try to be adaptive.

The reduced-scope patch currently tracks LP_DEAD line pointers at the
heap page level, and then applies a count of heap blocks with one or
more LP_DEAD line pointers (could be existing or just pruned by this
VACUUM) to determine a count of heap pages. This is used to determine
a threshold at which index vacuuming should be forced. Currently we
have a multiplier constant called SKIP_VACUUM_PAGES_RATIO, which is
0.01 -- 1% of heap blocks. Of course, it's possible that LP_DEAD line
pointers will be very concentrated, in which case we're more
aggressive about skipping index vacuuming (if you think of it in terms
of dead TIDs instead of heap blocks we're aggressive, that is). The
other extreme exists too: LP_DEAD line pointers may instead be spread
diffusively across all heap pages, in which case we are unlikely to
ever skip index vacuuming outside of cases like anti-wraparound vacuum
or insert-driven vacuum to set VM bits.

The next iteration of the high-level "granular vacuum" project (which
will presumably target Postgres 15) should probably involve more
complicated, qualitative judgements about LP_DEAD line pointers in the
heap. Likewise it should care about individual needs of indexes, which
is something that Masahiko experimented with in earlier drafts of the
patch on this thread. The needs of each index can be quite different
with bottom-up index deletion. We may in fact end up adding a new,
moderately complicated cost model -- it may have to be modelled as an
optimization problem.

In short, I think that thinking more about the logical state of the
database during VACUUM is likely to pay-off ("heap blocks vs dead
tuples" is one part of that). VACUUM should be a little more
qualitative, and a little less quantitative. The fact that we
currently don't stuff like that (unless bottom-up index deletion
counts) is not an inherent limitation of the design of VACUUM. I'm not
entirely sure how far it can be pushed, but it seems quite promising.

> The whole thing would be more appealing if there
> were some way to exert exponentially increasing back-pressure on the
> length of the line pointer array - that is, make it so that the longer
> the array is already, the less willing we are to extend it further.
> But I don't really see how to do that.

There are also related problems in the FSM, which just doesn't care
enough about preserving the original layout of tables over time. See
for example the recent "non-HOT update not looking at FSM for large
tuple update" thread. I believe that the aggregate effect of
inefficiencies like that are a real problem for us. The basic design
of the FSM hasn't been touched in over 10 years. There are non-linear
effects in play, in all likelihood. "Rare" harmful events (e.g. silly
space reuse in the heap, unnecessary page splits from version churn)
will tend to cause irreversible damage to locality of access if
allowed to occur at all. So we need to recognize those heap pages
where it's possible to preserve a kind of pristine state over time.
Heap pages that are subject to constant churn from updates exist --
most apps have some of those. But they're also usually a small
minority of all heap pages, even within the same heap relation.

--
Peter Geoghegan