Indexes under MVCC, and maintenance

Do index entries point at rows or at versions, and where does the upkeep go?

Indexes under MVCC: do entries point at rows or at versions?

When rows have versions (MVCC), an index entry has to say which version it refers to. This is where much real production pain lives.

PostgreSQL index entries point at versions. Consequences already seen in Index anatomy: an index-only scan must check the visibility map, because the entry does not say whether your snapshot can see that version. Old versions leave dead index entries, producing index bloat (an index larger than its live content needs) until vacuum removes them. And every update that creates a new version would need new entries in every index, which is the entire reason HOT updates exist.
InnoDB index entries point at primary keys and keep old versions elsewhere (in an undo log). It pays a different cost: the second descent through the clustered index.

The general principle:

It is the relocation-versus-indirection choice again, now caused by versions instead of by page moves.

The mechanism of MVCC itself (how snapshots and visibility rules work) is a subject on its own; what matters for indexing is only that index entries reference versions or keys, not "the row", and each choice has a bill.

Maintenance work: where the janitor goes

Three kinds of upkeep recur, whatever the structure.

Merge / compaction of immutable segments (inverted indexes, LSM-based indexes): the "collector" that pairs with the indirection of querying several segments at once.
Tombstones and rebuilds (graph vector indexes, posting lists): mask now, clean later.
Rebuild, reorganize, refresh statistics: an index decays (page splits, bloat, stale statistics, a clustering that no longer matches), and rebuilding it is periodic janitorial work. The planner's choices are only as good as the statistics.

The boundary where an index becomes a materialized view. A covering index is a materialized projection (Index anatomy). Push the idea further (more columns, pre-joined or pre-aggregated rows) and it becomes a materialized view (a stored query result). Where the line sits depends on what you must keep consistent and who maintains it: the index is maintained by the database as part of each write; a materialized view often is not.

Case, resolvedConstructed case · Priya at Parcelo

16 shards, and an email search that asks every one

With parcels split across 16 shards, email search had to ask every shard and its p99 suffered, a separate email lookup table returned 'not found' for just-created parcels, and deleted parcels lingered in vector search for days. Where it started: Approximation and lifecycle →

The slow email search. She understands the cost: a local email index means a 16-way scatter-gather, with a 15% chance per request of meeting a slow shard. The team decides to keep the local index (writes stay cheap and consistent) for staff search, which is infrequent and tolerates a delay, but adds a global email index for the customer-facing "find my parcel" feature, which is on the hot path.
"Created but not found." The global index is updated asynchronously. She cannot remove the consistency boundary, only place it. She picks: the customer-facing page, immediately after creation, reads from the primary shard by parcel_id (which it knows), and only later searches use the email index. She also shows a "just created" message so users are not surprised by a delay of a few seconds.
Ghost results from deleted vectors. The vector index tombstones deletes. She adds a filter at query time that checks a "deleted" flag from the main table, and schedules a rebuild when more than about 20% of the entries are tombstones. (The 20% figure is her own team's choice, not a general rule.)