Databases / PostgreSQL storage and MVCC deep dive

2026-10-06

PostgreSQL index-only scans: the visibility map decides whether “only” is true

A covering index contains every column a query needs, yet PostgreSQL may still visit the table heap for every candidate row. That is not a planner glitch: ordinary PostgreSQL indexes do not carry enough MVCC visibility information. The visibility map is the compact proof that lets an index-only scan safely skip the heap—and writes invalidate that proof.

1. The problem: an index has values, not visibility

PostgreSQL uses multi-version concurrency control (MVCC). An update usually creates a new tuple version rather than overwriting the old version in place. Each version carries transaction metadata; whether a tuple is visible depends on the snapshot of the querying transaction. An index entry points to a physical tuple location, but a normal B-tree entry does not encode the complete visibility answer for every possible snapshot.

A conventional index scan therefore has two jobs: find candidate tuple identifiers in the index, then consult the heap tuple to decide whether each candidate is visible and to retrieve any columns absent from the index. Heap visits can be random I/O. A covering index appears to remove the second job: if the query asks for indexed columns only, why not return values directly from the index?

Because returning an old, dead, or not-yet-committed row would be a correctness bug. PostgreSQL needs visibility proof as well as values. It solves this without storing full transaction visibility data in every index entry: a tiny per-heap-page visibility map (VM) certifies pages whose tuples are all visible to every current and future transaction.

2. Heap pages, MVCC, and the visibility map

PostgreSQL stores table tuples in fixed-size heap pages. The VM is a separate fork associated with the table. Each heap page has an all-visible bit and an all-frozen bit. For this lesson, all-visible is the important one: it means every tuple on that page is known visible to all transactions, so no snapshot-specific heap visibility check is needed. All-frozen is a stronger vacuum/transaction-age property used to reduce future freezing work; it is not a synonym for all-visible.

VACUUM can set the all-visible bit after checking a page. A data-modifying operation clears the relevant bit because the previous certification may no longer be valid. This is conservative by design: when uncertain, PostgreSQL reads the heap rather than risk returning an incorrect row. The VM is vastly smaller than the heap—PostgreSQL documents it as four orders of magnitude smaller—so its pages are commonly cached.

The index-only optimization therefore changes the costly lookup pattern, not the correctness model. A VM lookup still occurs, but the map’s compactness and cache behavior make it far cheaper than fetching scattered heap pages. If the bit is clear, the executor falls back to the heap for that candidate; the query remains correct but can lose much of its expected speedup.

3. Executor data flow

There are two distinct gates. First, the access method must be able to return the values the query needs. B-tree can return its indexed columns; some other access methods cannot return original values in the same way. Second, the planner must be able to construct an index-only path from the query’s required columns and predicates. An INCLUDE column can cover output without becoming a search or ordering key.

At execution time, PostgreSQL’s index-only scan reads an index tuple containing key/include values and a tuple identifier (TID). The heap block component of the TID identifies which VM bit to inspect. If all-visible is true, values already in the index can be projected to the caller. If false, the executor fetches the heap tuple and applies snapshot visibility rules before returning a row. The fallback is not an exception or a retry: it is the normal correctness path when the certificate is unavailable.

The executor exposes this distinction in EXPLAIN (ANALYZE, BUFFERS). An Index Only Scan node shows Heap Fetches. Zero means no candidate required a heap visibility check in that execution; a positive count means the VM did not certify some referenced pages. It does not mean that many disk reads necessarily occurred—heap buffers may already be cached. Buffer statistics and latency provide the I/O context.

Why not put visibility in every index tuple?

Visibility depends on transaction snapshots and tuple-version lifecycle. Duplicating enough visibility metadata into every index would enlarge indexes, complicate updates and vacuuming, and still require careful correctness rules as transaction status changes. The VM amortizes one certification across all tuples on a heap page. The cost is coarser granularity: one recently modified tuple can clear a page’s bit and force heap checks for other index entries on that page until VACUUM re-establishes the proof.

4. Why writes and VACUUM change the result

After a write clears an all-visible bit, an index-only scan may still appear in the plan but report heap fetches. Autovacuum eventually scans eligible pages and can set bits again once page contents permit. Thus the performance of the same SQL and index can vary with write churn, vacuum lag, and which heap pages the query touches. This is a physical-state property, not a schema-only property.

Updates can also change index contents, prevent HOT updates when indexed columns change, and create dead tuple versions that vacuum must later reclaim. Those topics interact, but the key distinction here is simpler: visibility-map state gates heap avoidance independently of whether the index contains every projected value. A frequently updated “latest status” table may have a covering index and still show substantial heap fetches. An append-once, rarely modified history table is more likely to maintain all-visible pages.

VACUUM is not equivalent to VACUUM FULL. Ordinary VACUUM can update the VM and reclaim space for reuse while allowing normal reads and writes. VACUUM FULL rewrites the table and takes a stronger lock; it is not a routine fix for index-only scans. Do not run it merely to force a lower heap-fetch count.

5. Reproducible lab: observe the proof being invalidated

Goal: compare an index-only scan before and after a page modification, then see VACUUM restore all-visible status. Prerequisites: PostgreSQL 12 or later and a disposable database where you can create a table. Run the statements in one psql session. The planner settings below are diagnostic only; they make the demonstration choose the path of interest and should not be copied into application configuration.

CREATE TABLE vm_demo (id integer, payload text);
INSERT INTO vm_demo
SELECT g, 'row-' || g FROM generate_series(1, 20000) AS g;
CREATE INDEX vm_demo_cover ON vm_demo (id) INCLUDE (payload);
VACUUM (ANALYZE) vm_demo;

SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT payload FROM vm_demo WHERE id BETWEEN 5000 AND 5010;

Expected observation: the plan should include Index Only Scan, and after VACUUM you should usually see Heap Fetches: 0. Planner estimates and environment can vary; if the planner still chooses another path, enlarge the sample or inspect the plan before drawing conclusions.

UPDATE vm_demo SET payload = payload || '-changed' WHERE id = 5005;
EXPLAIN (ANALYZE, BUFFERS)
SELECT payload FROM vm_demo WHERE id BETWEEN 5000 AND 5010;

VACUUM (ANALYZE) vm_demo;
EXPLAIN (ANALYZE, BUFFERS)
SELECT payload FROM vm_demo WHERE id BETWEEN 5000 AND 5010;

The update clears the all-visible certificate for its heap page. The middle plan should still be eligible for an index-only scan, but should show one or more heap fetches for candidates on the affected page; exact count depends on page layout and version. The final VACUUM should restore the bit if all tuples on the page qualify, making heap fetches fall again.

Variation 1: remove enable_seqscan = off and compare plans at different selectivities. A sequential scan can be cheaper when many rows are needed; index-only capability does not force index use.

Variation 2: update an indexed key rather than only the included value, then inspect the plan and table churn. The VM lesson remains, but index maintenance and possible HOT-update eligibility change too. Reset with DROP TABLE vm_demo when finished.

6. Production trade-offs and debugging

• Design indexes for actual queries. A wide INCLUDE payload increases index size, cache pressure, write amplification, and maintenance. Covering every conceivable column can make writes slower without making scans reliably heap-free.
• Read the execution, not the plan label. Check Heap Fetches, actual rows, loops, shared buffer hits/reads, and execution time. Compare under representative write load, not only immediately after a manual vacuum.
• Check vacuum health before changing the query. Inspect pg_stat_user_tables counters and autovacuum activity; correlate heap fetches with update churn and vacuum lag. A planner that correctly selects Index Only Scan may be telling you the index is structurally suitable while runtime VM coverage is poor.
• Do not infer physical reads from heap fetches. A heap fetch may be satisfied from shared buffers. Use BUFFERS and system-level I/O telemetry to distinguish CPU/visibility work, cached access, and storage reads.
• Remember write amplification. An included column must be maintained in the index when it changes. Larger indexes cost cache, WAL, replication bandwidth, and vacuum work. The read win must repay those costs.
• Use VM inspection carefully. The optional pg_visibility extension exposes visibility-map state for diagnosis. It is observational aid, not a reason to manipulate low-level relation files. Prefer supported SQL, backups, and controlled maintenance.

Common misdiagnosis: “The index has all columns, so heap fetches prove PostgreSQL bug.” Usually the missing fact is that the VM certifies pages, not individual index entries, and recent writes invalidate that certification. Another trap is testing only after VACUUM on an idle copy, then expecting the same result on a high-churn production table. Treat visibility-map coverage as workload-dependent operating state.