Re: Thoughts on "killed tuples" index hint bits support on standby - Mailing list pgsql-hackers

On Wed, Apr 8, 2020 at 5:23 AM Michail Nikolaev
<michail.nikolaev@gmail.com> wrote:
> Yes, it is a brilliant idea to use uniqueness to avoid bloating the index. I am
> not able to understand all the code now, but I’ll check the patch in more
> detail later.

The design is probably simpler than you imagine. The algorithm tries
to be clever about unique indexes in various ways, but don't get
distracted by those details. At a high level we're merely performing
atomic actions that can already happen, and already use the same high
level rules.

There is LP_DEAD bit setting that's similar to what often happens in
_bt_check_unique() already, plus there is a new way in which we can
call _bt_delitems_delete(). Importantly, we're only changing the time
and the reasons for doing these things.

> Yes, it is relevant, but I think it is «located in a different plane» and
> complement each other. Because most of the indexes are not unique these days
> and most of the standbys (and even primaries) have long snapshots (up to
> minutes, hours) – so, multiple versions of index records are still required for
> them. Even if we could avoid multiple versions somehow - it could lead to a very
> high rate of query cancelations on standby.

I admit that I didn't really understand what you were trying to do
initially. I now understand a little better.

I think that there is something to be said for calling
_bt_delitems_delete() more frequently on the standby, not necessarily
just for the special case of unique indexes. That might also help on
standbys, at the cost of more recovery conflicts. I admit that it's
unclear how much that would help with the cases that you seem to
really care about. I'm not going to argue that the kind of thing I'm
talking about (actually doing deletion more frequently on the primary)
is truly a replacement for your patch, even if it was generalized
further than my POC patch -- it is not a replacement. As best, it is a
simpler way of "sending the LP_DEAD bits on the primary to the standby
sooner". Even still, I cannot help but wonder how much value there is
in just doing this much (or figuring out some way to make LP_DEAD bits
from the primary usable on the standby). That seems like a far less
risky project, even if it is less valuable to some users.

Let me make sure I understand your position:

You're particularly concerned about cases where there are relatively
few page splits, and the standby has to wait for VACUUM to run on the
primary before dead index tuples get cleaned up. The primary itself
probably has no problem with setting LP_DEAD bits to avoid having
index scans visiting the heap unnecessarily. Or maybe the queries are
different on the standby anyway, so it matters to the standby that
certain index pages get LP_DEAD bits set quickly, though not to the
primary (at least not for the same pages). Setting the LP_DEAD bits on
the standby (in about the same way as we can already on the primary)
is a "night and day" level difference. And we're willing to account
for FPIs on the primary (and the LP_DEAD bits set there) just to be
able to also set LP_DEAD bits on the standby.

Right?

--
Peter Geoghegan