Re: Deleting older versions in unique indexes to avoid page splits

On Wed, Nov 25, 2020 at 10:41 AM Peter Geoghegan <pg@bowt.ie> wrote:
> We have promising tuples for bottom-up deletion. Why not have
> "promising heap blocks" for traditional LP_DEAD index tuple deletion?
> Or if you prefer, we can consider index tuples that *don't* have their
> LP_DEAD bits set already but happen to point to the *same heap block*
> as other tuples that *do* have their LP_DEAD bits set promising. (The
> tuples with their LP_DEAD bits set are not just promising -- they're
> already a sure thing.)
>
> This means that traditional LP_DEAD deletion is now slightly more
> speculative in one way (it speculates about what is likely to be true
> using heuristics). But it's much less speculative than bottom-up index
> deletion. We are required to visit these heap blocks anyway, since a
> call to _bt_delitems_delete() for LP_DEAD deletion must already call
> table_compute_xid_horizon_for_tuples(), which has to access the blocks
> to get a latestRemovedXid for the WAL record.
>
> The only thing that we have to lose here is a few CPU cycles to find
> extra TIDs to consider. We'll visit exactly the same number of heap
> blocks as before. (Actually, _bt_delitems_delete() does not have to do
> that in all cases, actually, but it has to do it with a logged table
> with wal_level >= replica, which is the vast majority of cases in
> practice.)
>
> This means that traditional LP_DEAD deletion reuses some of the
> bottom-up index deletion infrastructure. So maybe nbtree never calls
> table_compute_xid_horizon_for_tuples() now, since everything goes
> through the new heapam stuff instead (which knows how to check extra
> TIDs that might not be dead at all).

Attached is v10, which has this LP_DEAD deletion enhancement I
described. (It also fixed bitrot -- v9 no longer applies.)

This revision does a little refactoring to make this possible. Now
there is less new code in nbtdedup.c, and more code in nbtpage.c,
because some of the logic used by bottom-up deletion has been
generalized (in order to be used by the new-to-v10 LP_DEAD deletion
enhancement).

Other than that, no big changes between this v10 and v9. Just
polishing and refactoring. I decided to make it mandatory for tableams
to support the new interface that heapam implements, since it's hardly
okay for them to not allow LP_DEAD deletion in nbtree (which is what
making supporting the interface optional would imply, given the
LP_DEAD changes). So now the heapam and tableam changes are including
in one patch/commit, which is to be applied first among patches in the
series.

--
Peter Geoghegan