Re: [GSoC 2026] - B-tree Index Bloat Reduction - Approach & Questions

On Tue, 23 Jun 2026 at 20:16, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Tue, Jun 23, 2026 at 1:53 PM Matthias van de Meent
> <boekewurm+postgres(at)gmail(dot)com> wrote:
> > On Tue, 23 Jun 2026 at 17:57, Kirk Wolak <wolakk(at)gmail(dot)com> wrote:
> > > Back to the question of VACUUM. And where your insight might be trying to help us from making a terrible mistake.
> > > My understanding is that there can be MANY "extra" references in any index to records that have been LONG deleted.
> > > And when the Table is read, those are removed from the result set (I believe it's why index-only scans are limited to
> > > mostly clean tables, freshly vacuumed, etc).
> >
> > Strictly speaking that's right, but indexes like btree, gist, spgist,
> > and hash, all have a guarantee that any tuple only has a single
> > location in the index at any point in time.
>
> Minor clarification, for the benefit of others: the guarantee is that
> the index cannot contain the same heap TID twice (which might point to
> a hot chain with several versions).

Yes, I could have worded that better.

> > Scans may see the same TID pointing to a row (or the same TID pointing
> > to different versions of a row, or TIDs pointing to different versions
> > of different rows in case of tid reclamation through vacuum) several
> > times, but only one of those pointed-to rows can be visible to the
> > scan.
>
> I think that you meant that it can see different TIDs originating from
> the same updated logical row.

It could access and return the same TID twice, on multiple pages, if
the TID is recycled between the page accesses. But, as mentioned, at
most one of the rows indicated by the index' scan mechanism will be
MVCC-visible for the IndexScan executor node.

> A non-hot update can create 2 separate TIDs that point to different
> versions of the same logical row. In that case, both TIDs must be
> returned to the scan (assuming both have index tuple values that
> satisfy the scan keys). This doesn't really matter to the index AM; it
> doesn't know about updates at all.

Except the indexUnchanged flag for aminsert() -which index AMs should
only use as a hint- but more generally, yes that's right.

> What it boils down to is that the same TID must never exist in any 2
> index tuples in the same index simultaneously.

Eventually, indeed, it boils down to this.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)