Re: [DOCS] HOT - correct claim about indexes not referencing old line pointers

On Fri, Sep 29, 2023 at 10:39 AM James Coleman <jtc331(at)gmail(dot)com> wrote:
> > Old versions of updated rows can be completely removed during normal
> > operation, including SELECTs, instead of requiring periodic vacuum
> > operations. (This is possible because indexes do not reference their page
> > item identifiers.)
>
> But when a HOT update happens the entry in an (logically unchanged)
> index still points to the original heap tid, and that line item is
> updated with a pointer to the new line pointer in the same page.

It's true that the original root heap tuple (which is never a
heap-only tuple) must have its line pointer changed from LP_NORMAL to
LP_REDIRECT the first time pruning takes place that affects its HOT
chain. But I don't think that referring to the root item as something
along the lines of "an obsolescent/old tuple's line pointer" is
particularly helpful. Changing from LP_NORMAL to LP_REDIRECT during
the initial prune isn't terribly different from changing an existing
LP_REDIRECT's redirect-TID link during every subsequent prune. In both
cases you're just updating where the first heap-only tuple begins.

The really important point is that the TID (which maps to the root
item of the HOT chain) has a decent chance of being stable over time,
no matter how many versions the HOT chain churns through. And that
that can break (or at least weaken) our dependence on VACUUM with some
workloads.

--
Peter Geoghegan