Visibility map

What the visibility map is

Every table in PostgreSQL has an associated visibility map file stored as a separate fork on disk. The file is remarkably compact: two bits per heap page. For a table with 100,000 pages (roughly 800 MB of data), the visibility map is about 25 KB. A great deal of useful knowledge, occupying almost no space at all.

The two bits encode three possible states for each page:

• Neither bit set — the page may contain tuples that are not visible to all transactions. This is the default state for newly written or recently modified pages.
• All-visible — every tuple on the page is visible to every active transaction. VACUUM sets this bit after confirming the page has no dead tuples and no tuples that are only visible to a subset of transactions.
• All-visible and all-frozen — every tuple on the page is visible to all transactions and has a frozen transaction ID. This means the page needs no future freeze processing.

-- Each table has an associated visibility map file
-- e.g. for a table in file 16384, the VM is 16384_vm

-- Two bits per heap page:
--   Bit 0: all-visible  — every tuple on this page is visible to all transactions
--   Bit 1: all-frozen   — every tuple on this page has a frozen transaction ID

Writes to a heap page — INSERT, UPDATE, or DELETE — immediately clear the all-visible bit for that page. Only VACUUM can restore it. The arrangement is strict: any modification invalidates the assurance, and only a proper inspection reinstates it.

Why the visibility map matters

The visibility map earns its keep across three distinct areas of PostgreSQL's operation:

• Index-only scans — when PostgreSQL executes an index-only scan, it needs to confirm that each row it reads from the index is actually visible to the current transaction. Instead of fetching the row from the heap to check, it consults the visibility map. If the page is all-visible, every row on it is guaranteed visible, and the heap fetch is skipped entirely.
• VACUUM skip — during a normal vacuum pass, PostgreSQL consults the visibility map to skip pages that are already all-visible. If a page has not been modified since the last vacuum, there is nothing to clean. Good housekeeping includes knowing which rooms do not need attention today. This makes vacuum dramatically faster on tables where only a small fraction of pages see writes.
• FREEZE skip — during a freeze pass, VACUUM can skip pages that are already all-frozen. Freezing is the process of replacing aging transaction IDs with a permanent "frozen" marker to prevent transaction ID wraparound. Skipping already-frozen pages reduces the cost of anti-wraparound vacuums on large, mostly-static tables.

Visibility map and index-only scans

An index-only scan is only truly "index-only" when the visibility map cooperates — and this is where the behind-the-scenes work pays off. PostgreSQL's MVCC model means that a row stored in the index might belong to a transaction that has been rolled back, or might be a dead version that has not yet been vacuumed. The index alone cannot distinguish live rows from dead ones.

The visibility map solves this. When every tuple on a heap page is visible to all transactions, there is no ambiguity — every index entry pointing to that page is valid. PostgreSQL can return the data from the index leaf pages without touching the heap at all.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, order_total
FROM orders
WHERE customer_id BETWEEN 100 AND 200;

-- Index Only Scan using idx_orders_covering on orders
--   Index Cond: ((customer_id >= 100) AND (customer_id <= 200))
--   Heap Fetches: 0        ← visibility map says all pages are all-visible
--   Buffers: shared hit=5
--   Planning Time: 0.09 ms
--   Execution Time: 0.12 ms

When the visibility map is stale — because writes have occurred since the last VACUUM — PostgreSQL falls back to heap fetches for the affected pages. The query plan still shows "Index Only Scan", but the Heap Fetches counter reveals how many rows required a trip to the table.

-- Same query, but the table was recently bulk-updated and not yet vacuumed
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, order_total
FROM orders
WHERE customer_id BETWEEN 100 AND 200;

-- Index Only Scan using idx_orders_covering on orders
--   Index Cond: ((customer_id >= 100) AND (customer_id <= 200))
--   Heap Fetches: 847      ← 847 rows required a trip to the heap
--   Buffers: shared hit=5 read=214
--   Planning Time: 0.09 ms
--   Execution Time: 3.41 ms

-- Fix: run VACUUM to update the visibility map
VACUUM orders;

A high Heap Fetches count on an index-only scan is a clear signal: the table needs VACUUM. Once vacuum runs and updates the visibility map, those heap fetches drop to zero and the scan performs as intended. The difference between 0.12 ms and 3.41 ms is the difference between a well-kept house and one that has been neglected since the last inspection.

Monitoring visibility map health

If I may suggest two approaches to confirm that the visibility map is being properly maintained.

pg_stat_user_tables

The built-in statistics view does not expose the visibility map directly, but it shows you the signals that indicate whether it is current: dead tuple counts, modification counts, and when VACUUM last ran.

-- Check how many modifications have occurred since the last vacuum
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_mod AS modifications_since_analyze,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Tables with high n_dead_tup counts or a stale last_vacuum timestamp likely have a partially stale visibility map. If those tables serve index-only scans, the heap fetch penalty is real and worth investigating.

pg_visibility extension

For direct inspection of the visibility map, the pg_visibility extension exposes per-page visibility status. It ships with PostgreSQL as a contrib module and is available on most managed platforms.

-- Install the pg_visibility extension
CREATE EXTENSION IF NOT EXISTS pg_visibility;

-- Check per-page visibility status for a table
SELECT
  blkno,
  all_visible,
  all_frozen
FROM pg_visibility('orders')
LIMIT 10;

-- Summary: fraction of pages that are all-visible and all-frozen
SELECT
  count(*) AS total_pages,
  count(*) FILTER (WHERE all_visible) AS visible_pages,
  count(*) FILTER (WHERE all_frozen) AS frozen_pages,
  round(100.0 * count(*) FILTER (WHERE all_visible) / count(*), 1) AS visible_pct,
  round(100.0 * count(*) FILTER (WHERE all_frozen) / count(*), 1) AS frozen_pct
FROM pg_visibility('orders');

The summary query is the most useful: it tells you what fraction of the table's pages are all-visible and all-frozen. A table at 95% visible pages is a well-maintained establishment — very few heap fetches on index-only scans. A table at 40% has been left unattended for rather longer than is advisable. A VACUUM pass will close the gap.

How Gold Lapel relates

Gold Lapel's covering index recommendations depend on the visibility map being healthy. A covering index enables an index-only scan in principle, but the scan only delivers its full performance benefit when the visibility map confirms that the relevant pages are all-visible. Recommending a covering index for a table with a chronically stale visibility map would be rather like laying out fresh linen in an unswept room — technically correct, but missing the point.

When Gold Lapel identifies a query pattern that would benefit from a covering index, it checks the table's vacuum health as part of the recommendation. If the visibility map is stale — indicated by high heap fetch counts on existing index-only scans or a large dead tuple backlog — Gold Lapel flags the table for autovacuum tuning alongside the index recommendation. The index and the vacuum configuration work as a pair. One attends to the visible; the other ensures the groundwork is sound.