A plan reports an index-only scan, yet the query still performs a large number of table (heap) fetches. Why can an index entry alone be insufficient to return a row in an MVCC database, and what makes those extra fetches go away?

## The MVCC premise In a multi-version concurrency control engine, writers do not block readers because a modification produces a **new version** of the row rather than overwriting the old one. Each version carries metadata about which transaction made it visible and which transaction (if any) removed it. A reader compares that metadata against its own snapshot to decide whether it may see that version. Old versions stay around until a background cleanup process determines that no snapshot can still need them. ## Why the index cannot decide visibility Indexes store keys and row pointers. They generally do **not** store per-version transaction metadata — duplicating it would mean updating every index on the table whenever a version's status changed, which would destroy write performance. The consequence: an index entry may point to a version that is dead (deleted or superseded) or not yet visible to your snapshot. Returning data straight from an index entry without a check could resurrect deleted rows or expose uncommitted ones. So an "index-only" scan needs some cheaper proof of visibility, or it must fall back to reading the row. ## The PostgreSQL mechanism: the visibility map PostgreSQL keeps a compact bitmap with a couple of bits per table page. One bit means **all-visible**: every tuple on that page is visible to every current and future snapshot. `VACUUM` sets it; any write to the page clears it. During an index-only scan, for each matching entry the executor checks the map bit of the page the entry points into: - bit set → return the values straight from the index entry; - bit clear → fetch the heap tuple and check visibility properly. So the plan node is genuinely "Index Only Scan", and it reports `Heap Fetches: N`. `N` near zero is the win you were aiming for; `N` near the row count means you are paying the ordinary two-step cost plus a wider index. ## Why the map bits are clear - **Recent writes.** A table with continuous updates constantly clears bits. A brand-new or just-bulk-loaded table also has clear bits until vacuumed. - **Vacuum not keeping up.** Autovacuum thresholds scaled to a huge table can leave a hot table under-vacuumed for a long time. - **Something holding an old snapshot.** A long-running transaction, a session sitting idle-in-transaction, an abandoned replication slot, or a prepared transaction left hanging prevents cleanup from advancing, so pages cannot be marked all-visible no matter how often vacuum runs. This is the classic case where the same query was fast last week and is slow now with no schema change. ## The fixes, in order 1. **Find and kill the snapshot holder** — long transactions, idle-in-transaction sessions, stale replication slots. Nothing else works until cleanup can advance. 2. **Make vacuum keep up on that table** — per-table autovacuum settings so a hot table is vacuumed on its own churn rather than a global threshold; a manual vacuum after a bulk load or migration. 3. **Reduce churn on the covered columns** — fewer updates to the table means fewer cleared bits; leaving free space on pages so updates can be handled without touching indexes helps too. 4. **Reconsider the index** — if the table is inherently write-hot, a wide covering index may simply not pay, and a narrower index plus row fetches may be the honest choice. ## Other engines The general principle — visibility information lives with the row, not the index — is not PostgreSQL-specific, but the mechanics are. Engines that store rows in a clustered index keyed by the primary key have a different shape: a secondary-index lookup lands in the clustered index, and versioning is reconstructed from undo information, so the equivalent question becomes when a secondary index alone suffices and when the engine must consult the clustered index or undo log. Present the principle first and label the mechanism you name as engine-specific. ## How to answer Start with the principle: index entries carry keys, not visibility. Then name a concrete mechanism (visibility map, set by vacuum, cleared by writes) and the observable symptom (`Heap Fetches` high in an Index Only Scan node). Then diagnose upstream: what stops pages from being all-visible — write rate versus vacuum rate, and old snapshots. Finish with the honest tradeoff that some tables should not carry a wide covering index at all.