0Pricing
PostgreSQL Performance & Query Optimization · Lesson

The Visibility Map and Index-Only Scans

Keep the visibility map fresh so the planner can serve index-only scans without heap fetches.

The Visibility Map and Index-Only Scans is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Index Scans Still Touch the Heap

In PostgreSQL, a normal index scan finds matching rows in the index, but it cannot trust the index alone to know whether each row is visible to your transaction. MVCC stores visibility information (xmin/xmax) only in the heap tuple, not in the index entry.

  • So for every index match, the executor must do a heap fetch to check visibility.
  • These random heap accesses dominate the cost of an index scan, especially on a large table.

The visibility map (VM) exists to let PostgreSQL skip that heap fetch when it is provably safe, enabling an index-only scan.

What the Visibility Map Stores

The visibility map is a compact bitmap stored alongside each table (in a _vm fork). It holds two bits per heap page:

  • all-visible: every tuple on the page is visible to all current and future transactions.
  • all-frozen: every tuple on the page is frozen (used to skip pages during anti-wraparound vacuum).

For index-only scans only the all-visible bit matters. If a heap page's all-visible bit is set, the planner knows any tuple it points to on that page is visible, so it can answer from the index entry alone.

Who Sets the All-Visible Bit

The all-visible bit is set by VACUUM (including autovacuum). When vacuum processes a heap page and finds that all tuples are visible to everyone and there is no dead tuple needing removal, it sets the all-visible bit for that page.

  • Inserts, updates, and deletes clear the bit for the affected page.
  • The bit only gets set again when vacuum revisits the page.

Consequence: a table that is written frequently but vacuumed rarely will have a stale visibility map, and index-only scans will silently degrade into ordinary index scans with heap fetches.

-- Force a vacuum so the VM bits get set for an existing table
VACUUM (VERBOSE) orders;

-- See how many heap pages are currently marked all-visible / all-frozen
SELECT relname,
       relpages,
       pg_relation_size(oid) AS heap_bytes
FROM pg_class
WHERE relname = 'orders';

Inspecting VM Coverage with pg_visibility

The pg_visibility extension lets you measure exactly how much of a table is marked all-visible. This is the single most useful diagnostic for index-only-scan health.

  • pg_visibility_map_summary('tbl') returns counts of all-visible and all-frozen pages.
  • Compare those counts against relpages to get a coverage ratio.

Low coverage on a table you expect to serve index-only scans is the red flag: the VM is stale and needs vacuuming.

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT c.relname,
       c.relpages,
       v.all_visible,
       v.all_frozen,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

Requirements for an Index-Only Scan

For the planner to choose an index-only scan, three things must line up:

  • The index must cover every column the query needs (in the index key or as an INCLUDE payload).
  • The query must reference only those covered columns in SELECT, WHERE, ORDER BY, etc.
  • Enough of the table's pages must be marked all-visible so the saved heap fetches outweigh the index scan.

Even a perfectly covering index will fall back to heap fetches if the VM is stale. Coverage and freshness are equally necessary.

-- A covering index for: SELECT customer_id, status WHERE customer_id = ?
CREATE INDEX idx_orders_cust_status
    ON orders (customer_id) INCLUDE (status);

Reading the Plan: Heap Fetches

The proof that the VM is doing its job is in EXPLAIN (ANALYZE, BUFFERS). An index-only scan node reports a Heap Fetches counter.

  • Heap Fetches: 0 means every matching row came from a page marked all-visible — the ideal case.
  • A large Heap Fetches count means many pages were not all-visible, so the scan paid the random-heap cost anyway.

Watch this number after a heavy write burst: it will spike until the next vacuum re-sets the VM bits.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customer_id, status
FROM orders
WHERE customer_id = 42;

-- Look for:
--   Index Only Scan using idx_orders_cust_status on orders
--     Heap Fetches: 0

Demo: Stale VM Causes Heap Fetches

You can reproduce the degradation deterministically. Insert rows, run the index-only query, and watch Heap Fetches jump because the freshly inserted pages have their all-visible bit cleared.

  • Right after the insert, the new pages are not all-visible, so Heap Fetches > 0.
  • After an explicit VACUUM, the bits are re-set and Heap Fetches drops back to 0.

This is exactly the silent regression that hits write-heavy tables in production.

INSERT INTO orders (customer_id, status)
SELECT 42, 'NEW' FROM generate_series(1, 50000);

-- Heap Fetches will be high here (new pages not all-visible)
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

VACUUM orders;

-- Now Heap Fetches should be back near 0
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

Tuning Autovacuum to Keep the VM Fresh

The durable fix is to make autovacuum run often enough on hot tables. The key per-table knobs:

  • autovacuum_vacuum_scale_factor — fraction of the table that must change before a vacuum is triggered. Lower it on large, churny tables.
  • autovacuum_vacuum_threshold — a flat floor of changed rows.
  • autovacuum_vacuum_insert_scale_factor / _insert_threshold — added in PG13, these trigger vacuums on insert-only tables, which previously never got vacuumed and so never had their VM set.

Per-table overrides via ALTER TABLE ... SET are preferred over global changes.

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_vacuum_insert_threshold = 1000
);

The Insert-Only Table Trap

Before PostgreSQL 13, append-only tables (logs, events, time-series) were a classic index-only-scan failure: autovacuum is driven by dead tuples, and pure inserts create none, so vacuum never ran and the VM stayed empty.

  • Result: index-only scans on these tables always paid full heap fetches.
  • PG13's insert-based autovacuum triggers fixed the default behavior.

On older versions, the workaround is a scheduled VACUUM (e.g. via cron) so the all-visible bits get set after each batch load.

-- Pre-PG13 workaround: vacuum the append-only table after each batch load
-- (run on a schedule)
VACUUM (FREEZE) events;

-- FREEZE also sets all-frozen bits, helping anti-wraparound vacuum later

Long Transactions Hold the VM Hostage

Even aggressive autovacuum cannot mark a page all-visible if some old transaction might still need to see (or might have created) tuples on it. A long-running transaction or an old replication slot holds back the xmin horizon.

  • Vacuum cannot advance past that horizon, so it cannot set all-visible bits for recently changed pages.
  • Symptom: VM coverage stays low and Heap Fetches stay high no matter how often you vacuum.

Hunt down idle-in-transaction sessions and stale replication slots — they are a common hidden cause of failed index-only scans.

-- Find the oldest transaction holding back the xmin horizon
SELECT pid,
       state,
       now() - xact_start AS xact_age,
       backend_xmin,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Verifying the Whole Loop

Put the pieces together into a repeatable health check for any table you expect to serve index-only scans:

  • Confirm a covering index exists for the hot query.
  • Measure VM coverage with pg_visibility_map_summary.
  • Run EXPLAIN (ANALYZE, BUFFERS) and check Heap Fetches is low.
  • If coverage is low: tune autovacuum, kill long transactions, or schedule manual vacuums.

The goal is a stable state where Heap Fetches stays near zero between vacuums rather than spiking after every write burst.

-- One-shot coverage + size snapshot for a candidate table
SELECT c.relname,
       c.relpages,
       v.all_visible,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS n_indexes
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

Quick Check

An index-only scan on a write-heavy table shows a high Heap Fetches count right after a bulk insert, even though a fully covering index exists. What is the most direct cause and fix?

Recap

Index-only scans depend on the visibility map, not just on having a covering index.

  • The VM's all-visible bit lets the executor skip the heap fetch; it is set by VACUUM and cleared by any write to the page.
  • Measure freshness with pg_visibility_map_summary and verify with Heap Fetches in EXPLAIN (ANALYZE, BUFFERS).
  • Keep the VM fresh by tuning autovacuum (including insert-based triggers for append-only tables) and by eliminating long-running transactions and stale replication slots that pin the xmin horizon.

Coverage plus freshness together are what keep Heap Fetches at zero.

Frequently asked questions

Is the “The Visibility Map and Index-Only Scans” lesson free?

Yes — the full text of “The Visibility Map and Index-Only Scans” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “The Visibility Map and Index-Only Scans”?

Keep the visibility map fresh so the planner can serve index-only scans without heap fetches. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “The Visibility Map and Index-Only Scans” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Tuple Visibility, xmin, and xmax
  2. HOT Updates and Heap-Only Tuple Chains
  3. The Visibility Map and Index-Only Scans
  4. WAL Generation and Write Amplification
← Back to PostgreSQL Performance & Query Optimization