On Tue, Jun 23, 2026 at 1:53 PM Matthias van de Meent <[email protected]> wrote: > On Tue, 23 Jun 2026 at 17:57, Kirk Wolak <[email protected]> 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). > 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. 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. What it boils down to is that the same TID must never exist in any 2 index tuples in the same index simultaneously. Currently, amcheck cannot detect that condition because checking every index tuple against every other one is inconvenient and expensive. However, offering such a check would be useful; it wouldn't need to rely on heap visibility information, as the simple fact that the same TID appears a second time always indicates index corruption. -- Peter Geoghegan
