MIME-Version: 1.0
References: <165764161026.666.13998783616221095418@wrigleys.postgresql.org>
 <CAH2-WzkNTEUyC=Q6dFQanirKcKJtxb52Nh2nLwDUX10DGd-sWg@mail.gmail.com>
 <CAKFQuwZ7=V8yXQB00KRNgDfQ=_8WMcw9WFmqYnhETfWENzC-6A@mail.gmail.com>
 <CAH2-Wz=ydag6Nn5taDfXB7UK1g53+wY5SJMS5Ka-42LOxQa1XA@mail.gmail.com>
 <CAKFQuwZrvHrCUEk4SNKKVoX5RDuv-rVXn1sf=2ChC6ug9QAVoQ@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwZrvHrCUEk4SNKKVoX5RDuv-rVXn1sf=2ChC6ug9QAVoQ@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 9 Nov 2022 16:34:04 -0800
Message-ID: 
 <CAH2-WznKttnTi2kcRM3TRu=iuX-RUoKhSw4xuScV7-C7-eD2bA@mail.gmail.com>
Subject: Re: 64.4.2. Bottom-up Index Deletion
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: hus.mhd@gmail.com, pgsql-docs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-WznKttnTi2kcRM3TRu%3DiuX-RUoKhSw4xuScV7-C7-eD2bA%40mail.gmail.com>
Precedence: bulk

On Wed, Nov 9, 2022 at 4:06 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> Ok, so the "most recent live tuple" - this in an update, there has to be =
one.  Can that live tuple that is being updated ever be removed by this pro=
cess?

No.

> I'm not sure what the long-running transaction has to do with this though=
 - we are in an update so we've locked an active tuple and that is the only=
 tuple this coexistence is talking about.

We cannot remove "recently dead" tuples, which are definitely dead
(they were rendered garbage by a transaction that has already
committed), but are still required by some snapshots. We know that
they're bound to become garbage that is safe to remove sooner or later
-- but it's not safe just yet.

> You lost me here.  Which tuple is "it" - the one we are updating got dele=
ted under us?

No. We delete any and all tuples that are found to be deletable. We
don't care about the true reasons at that point. We're perfectly
content to be the beneficiary of a certain kind of dumb luck if things
go that way (though we'll typically see only a few garbage tuples that
originate from DELETEs or aborted inserting xacts). In the end, we're
really just ranking heap blocks, and then visiting them in the hopes
of finding that we can avoid the page split. We're choosing among heap
blocks, not index tuples (once we visit a heap block we'll delete any
index tuples that point to the heap block and are seen to be eligible
to be deleted).

> So, the page that the update has to be placed on gets picked for cleanup =
but the actual fetching of rows and such doesn't actually care what that tu=
ple is.  Though its ancestors are likely to be picked.

Yeah, heap pages pointed to by relatively many duplicate index tuples
tend to be visited first.

So-called "dumb luck" is more likely than you'd think. There often
won't be very many heap pages in total -- in which case we're bound to
stumble upon any garbage tuples that weren't actually rendered garbage
by an updater in passing.

> Knowing for certain whether the cleanup scope is "just prior versions of =
my update" or "any old versions on the page my update just happened to be p=
laced on" seems user-facing worthy.

We don't particularly care about what the incoming updater that
happens to have triggered the bottom-up deletion pass was doing. It's
not special. We don't particularly favor the row being updated over
other similar looking rows on the same leaf page. We'll usually have
to visit 2 or 3 heap pages total (per bottom-up pass) in any case.

> I'm almost thinking the last sentence "Each new tuple within each index..=
..for a short period of time..." is doing more harm than good.  Removing it=
 makes things clearer.

I'm unsure. Does anybody else have an opinion on this?

> Bottom-up index deletion is an incremental version churn deletion process=
, triggered by an anticipated "version churn page split".  This only happen=
s.....(the rest can probably remain the same, though I suggest moving the n=
ote to the end of the section)."

Probably the most important point is that bottom-up deletion
compensates for cases where the HOT optimization wasn't used, though
only for those indexes that weren't logically modified by the UPDATE.
It more or less takes over from HOT. But it doesn't necessarily help
at all with DELETEs (not reliably).

> Adding "no-longer-visible" at the start should hopefully be a sufficient =
reminder for someone familiar with MVCC; or a clue that more reading of pre=
-requisite features is needed before understanding this internal part of th=
e system.

Yeah, it's not clear what the prerequisites for reading this material
should be. It is part of the B-Tree internals chapter, though, so it
is very advanced material.

--=20
Peter Geoghegan