Re: Patch proposal - parameter to limit amount of FPW because of hint bits per second

On Sun, Mar 20, 2022 at 12:44 PM Michail Nikolaev
<michail(dot)nikolaev(at)gmail(dot)com> wrote:
> So, I was thinking about a way to avoid such downtimes. What is about
> a patch to add parameters to limit the number of FPW caused by LP_DEAD
> bits per second? It is always possible to skip the setting of LP_DEAD
> for future time. Such a parameter will make it possible to spread all
> additional WAL traffic over time by some Mbit/s.
>
> Does it look worth its implementation?

The following approach seems like it might fix the problem in the way
that you hope for:

* Add code to _bt_killitems() that detects if it has generated an FPI,
just to set some LP_DEAD bits.

* Instead of avoiding the FPI when this happens, proactively call
_bt_simpledel_pass() just before _bt_killitems() returns. Accept the
immediate cost of setting an LP_DEAD bit, just like today, but avoid
repeated FPIs.

The idea here is to take advantage of the enhancements to LP_DEAD
index tuple deletion (or "simple deletion") in Postgres 14.
_bt_simpledel_pass() will now do a good job of deleting "extra" heap
TIDs in practice, with many workloads. So in your scenario it's likely
that the proactive index tuple deletions will be able to delete many
"extra" nearby index tuples whose TIDs point to the same heap page.

This will be useful to you because it cuts down on repeated FPIs for
the same leaf page. You still get the FPIs, but in practice you may
get far fewer of them by triggering these proactive deletions, that
can easily delete many TIDs in batch. I think that it's better to
pursue an approach like this because it's more general.

It would perhaps also make sense to not set LP_DEAD bits in
_bt_killitems() when we see that doing so right now generates an FPI,
*and* we also see that existing LP_DEAD markings are enough to make
_bt_simpledel_pass() delete the index tuple that we want to mark
LP_DEAD now, anyway (because it'll definitely visit the same heap
block later on). That does mean that we pay a small cost, but at least
we won't miss out on deleting any index tuples as a result of avoiding
an FPI. This second idea is also much more general than simply
avoiding FPIs in general.

--
Peter Geoghegan