PostgreSQL’s pg_visibility: Peeking Under the Hood of Table Storage
When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level—information that directly affects vacuum efficiency and index-only scans.
Installing the extension
pg_visibility ships with the server source but is not installed automatically. Enable it in a database with:
CREATE EXTENSION pg_visibility;No restart is required, and the new functions are available immediately.
What the extension shows
pg_visibility() returns one row per heap page in a table and reports:
- blkno – block number (the page’s position in the file)
- all_visible – every tuple on the page is visible to all transactions, so the visibility map bit is set
- all_frozen – every tuple is frozen and immune to wrap-around cleanup
- has_dead – at least one dead tuple remains
Because these values come straight from the table file—not catalog statistics—they provide an exact picture of on-disk reality.
Summary at a glance
If you only need high-level counts—useful when scripting checks—call pg_visibility_map_summary():
SELECT * FROM pg_visibility_map_summary('schema.table_name');The function returns total heap pages, how many are all-visible, and how many are all-frozen. These numbers tell you instantly whether autovacuum is keeping up or whether a large batch operation left many pages dirty.
Practical uses
Diagnosing page bloat
If you suspect dead tuples after heavy UPDATE or DELETE activity, run:
SELECT blkno
FROM pg_visibility('schema.table_name')
WHERE has_dead;A sizable result confirms many pages still carry dead rows, suggesting a manual VACUUM or even VACUUM FULL. Because PostgreSQL stores tuples in fixed-size pages, any dead space that cannot be reused becomes bloat and slows sequential scans.
Explaining a missing index-only scan
An index-only scan needs every referenced heap page set all-visible. To see where the visibility map falls short, execute:
SELECT blkno
FROM pg_visibility('schema.table_name')
WHERE NOT all_visible;Any returned block forces the planner to visit the heap, preventing the lighter plan. A normal VACUUM (or tighter autovacuum thresholds) will usually clear these gaps.
Tuning autovacuum freeze settings
Transactions older than autovacuum_freeze_max_age must not see unfrozen tuples, or the entire database pauses for freeze. By tracking all_frozen counts before and after a workload spike, you can adjust autovacuum_freeze_min_age and related settings so tuples are frozen long before wrap-around risk.
Spot-checking a single page
Need to drill into one troublesome block? Pass the block number:
SELECT * FROM pg_visibility('schema.table_name', blkno := 1234);This is especially helpful when analysing HOT-update chains or verifying whether a suspected corruption is logical (tuple state) rather than physical (checksum failure).
Performance considerations
pg_visibility scans the table directly, so on large relations it can generate noticeable I/O. Use it during focused investigations rather than embedding it in recurring jobs. Day-to-day monitoring usually relies on VACUUM VERBOSE output, autovacuum logs, and pg_stat_user_tables.
Closing thoughts
pg_visibility offers a direct, page-level view of heap health. By pairing its output with routine vacuum statistics, you can verify whether maintenance keeps pace with workload, explain planner decisions, and schedule targeted cleanup before bloat becomes measurable. For anyone responsible for PostgreSQL performance tuning, it is a reliable microscope when the usual metrics leave questions unanswered.