Visibility map, all-visible and all-frozen

The visibility map is a small file beside every table holding two bits for each of its heap pages. The all-visible bit says every row version on that page is visible to every current transaction, so nothing on it is dead and nothing needs checking. The all-frozen bit says the page additionally contains no unfrozen transaction id. Vacuum sets both, and a bit is cleared the moment anything writes to the page it describes. Nothing else in PostgreSQL is this cheap to consult.

What the two bits let the server skip

All-visible is a permission to not look at a page. It is consulted twice, by two unrelated parts of the server, for two different reasons. Vacuum reads it to skip pages it has already cleaned, which is why a routine pass over a mostly-static table finishes in a fraction of the time the table’s size suggests. The planner reads it through the executor so that an index scan can return a row without going to the heap to check whether that row is visible, which is the entire mechanism behind an index-only scan.

All-frozen is a permission to not look at a page even during an anti-wraparound pass. Without it, the pass that advances the table’s frozen id has to read every page in the table no matter how old and untouched; with it, the pass reads only the pages that have changed since the last freeze. On a large append-mostly table this is the difference between a freeze that takes minutes and one that takes hours.

The property that surprises people is how easily the bits go away. One update to one row clears the all-visible bit for the whole page containing it, and the page stays uncleared until vacuum next visits. A table taking a light, uniformly scattered write load can therefore have a nearly empty map while looking almost static from the outside.

Counting the pages a pass can skip

The pg_visibility extension reads the map directly, one row per page.

CREATE EXTENSION pg_visibility;
CREATE TABLE ship_leg (id int PRIMARY KEY, label text);
INSERT INTO ship_leg SELECT g, repeat('x', 40) FROM generate_series(1, 20000) g;
VACUUM (FREEZE, ANALYZE) ship_leg;
SELECT count(*) AS pages,
       count(*) FILTER (WHERE all_visible) AS all_visible_pages,
       count(*) FILTER (WHERE all_frozen)  AS all_frozen_pages
FROM pg_visibility_map('ship_leg');

CREATE EXTENSION
CREATE TABLE
INSERT 0 20000
VACUUM
 pages | all_visible_pages | all_frozen_pages 
-------+-------------------+------------------
   187 |               187 |              187
(1 row)

Every page is marked both ways, because the table was written once and then frozen, which is the best case and also the shape of a partition that has stopped receiving rows. Run the same count after a scattered UPDATE and the visible total falls much faster than the number of rows touched would suggest, since the unit is the page and not the row.

pg_class.relallvisible is the cheaper approximation of the same thing and needs no extension, but it is a planner estimate refreshed by vacuum rather than a live count, so it can be confidently wrong about a table that has been written to since.

Keeping the map full

A map that keeps emptying is a vacuum frequency question, and the treatment that covers thresholds, scale factors and the per-table overrides worth setting is the guide on autovacuum and table bloat. If the reason you care is a query plan that stopped being index-only, start instead from the index-only scan, where the heap fetch counter is the thing to read.