pg_visibility

examine the visibility map (VM) and page-level visibility info

https://www.postgresql.org/docs/current/pgvisibility.html

Overview

Package	Version	Category	License	Language
`pg_visibility`	`1.2`	STAT	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
6960	`pg_visibility`	No	Yes	No	Yes	No	No	-

Related	`pageinspect` `toastinfo` `pagevis` `pg_freespacemap` `pg_buffercache` `pgstattuple` `pgrowlocks` `pg_wait_sampling` `pg_show_plans` `old_snapshot`

Version

PG18	PG17	PG16	PG15	PG14
1.2	1.2	1.2	1.2	1.2

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION pg_visibility;

Usage

pg_visibility: examine the visibility map

pg_visibility provides functions to examine and verify the visibility map (VM), which tracks which pages contain only tuples visible to all transactions.

Functions

Single page visibility:

-- VM bits for a specific block
SELECT * FROM pg_visibility_map('my_table', 0);
-- Returns: all_visible, all_frozen

-- VM bits plus the page's PD_ALL_VISIBLE flag
SELECT * FROM pg_visibility('my_table', 0);
-- Returns: all_visible, all_frozen, pd_all_visible

All pages visibility:

-- VM bits for every page
SELECT * FROM pg_visibility_map('my_table');
-- Returns: blkno, all_visible, all_frozen

-- VM bits plus PD_ALL_VISIBLE for every page
SELECT * FROM pg_visibility('my_table');
-- Returns: blkno, all_visible, all_frozen, pd_all_visible

Summary:

SELECT * FROM pg_visibility_map_summary('my_table');
-- Returns: all_visible (count), all_frozen (count)

Corruption Detection

-- Find tuples on all-frozen pages that aren't actually frozen
SELECT * FROM pg_check_frozen('my_table');

-- Find tuples on all-visible pages that aren't actually all-visible
SELECT * FROM pg_check_visible('my_table');

If either function returns rows, the visibility map is corrupt.

Repair

-- Truncate the visibility map (forces full VACUUM rebuild)
SELECT pg_truncate_visibility_map('my_table');
-- Then run: VACUUM my_table;

Access

Functions require superuser or pg_stat_scan_tables role. pg_truncate_visibility_map requires superuser.