pg_visibility

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

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

Overview

ID	Extension	Package	Version	Category	License	Language
6960	pg_visibility	pg_visibility	`1.2`	STAT	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
See Also	pageinspect toastinfo pagevis pg_freespacemap pg_buffercache pgstattuple pgrowlocks pg_wait_sampling pg_show_plans old_snapshot

Packages

PG18	PG17	PG16	PG15	PG14
1.2	1.2	1.2	1.2	1.2

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

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.