How to Understand and Use the pg_visibility Extension in PostgreSQL

PostgreSQL is known for its robust storage architecture and advanced visibility mechanisms. One such useful tool for database administrators (DBAs) is the pg_visibility extension. This extension helps in analyzing the visibility map (VM) and understanding which pages or tuples are visible to all transactions, which is crucial for database maintenance and performance optimization.

Let’s explore everything about pg_visibility—from installation to practical usage—and also understand the underlying concepts such as page structure, FSM, and VSM.

What is pg_visibility?

The pg_visibility extension allows you to inspect the visibility information of table pages in PostgreSQL.

It is particularly useful when you want to:

• Diagnose issues related to tuple visibility.
• Understand how autovacuum and vacuum maintain your data files.
• Identify pages with frozen or unfrozen tuples.
• Explore the visibility map (VM), which PostgreSQL uses to track which pages have only tuples visible to all transactions.

In short, pg_visibility helps DBAs to peek “under the hood” of PostgreSQL storage and visibility internals.

Installing the Extension

Before using the extension, make sure it is available in your PostgreSQL setup.

Step 1: Check Availability

SELECT * FROM pg_available_extensions WHERE name = 'pg_visibility';

If it exist, you get a result like this

     name      | default_version | installed_version |                            comment                             
---------------+-----------------+-------------------+----------------------------------------------------------------
 pg_visibility | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
(1 row)

Step 2: Install the Extension

CREATE EXTENSION pg_visibility;
CREATE EXTENSION

If you are using the postgres source code, then try the commands below

cd postgres_directory/postgres/contrib/pg_visibility
make
Sudo make install

Step 3: Verify Installation

postgres=# \dx+ pg_visibility
     Objects in extension "pg_visibility"
              Object description               
-----------------------------------------------
 function pg_check_frozen(regclass)
 function pg_check_visible(regclass)
 function pg_truncate_visibility_map(regclass)
 function pg_visibility_map(regclass)
 function pg_visibility_map(regclass,bigint)
 function pg_visibility_map_summary(regclass)
 function pg_visibility(regclass)
 function pg_visibility(regclass,bigint)
(8 rows)

Now, the extension is ready to use.

Understanding PostgreSQL Page Structure

Before using pg_visibility, it’s important to understand how PostgreSQL stores data physically.

Each table in PostgreSQL is stored as one or more files on disk inside the data directory.

Each file is divided into fixed-size pages, and each page is 8 KB by default.

A PostgreSQL page has three main sections:

1. Page Header – Contains metadata like LSN (log sequence number), checksum, and pointers to free space.
2. Item Pointer Array (Line Pointer) – An array pointing to tuples (rows) stored in the page.
3. Tuple Data Area – The main content where actual row data is stored.

This structure allows PostgreSQL to efficiently manage updates, visibility, and MVCC (Multi-Version Concurrency Control).

FSM, VM, and VSM Explained

PostgreSQL uses additional maps to track free and visibility information for each table:

1. FSM (Free Space Map)

• Tracks how much free space is available on each page.
• Helps the system quickly find pages with space for inserting new rows.
• Stored in a separate file named like <relfilenode>_fsm.

Example: 1259_fsm.

2. VM (Visibility Map)

• Tracks which pages contain only tuples visible to all transactions.
• Used by VACUUM to skip pages that don’t need cleanup.
• Stored in a file like <relfilenode>_vm.

3. VSM (Visibility Storage Map)

• This term often refers to internal handling or visibility-related metadata.
• Together with FSM and VM, PostgreSQL ensures efficient space reuse and cleanup.

If you look at your PostgreSQL data directory, you might find files like:

1259 > main table data file ( example )

1259_fsm > free space map file

1259_vm > visibility map file

If you want to get more details about this file , try this query with this relid

select * from pg_stat_user_tables where relid = 1259;

You get result like this

-[ RECORD 1 ]----------+---------------------------------
relid                  | 3535427
schemaname             | public
relname                | Operator
seq_scan               | 0
last_seq_scan          | 
seq_tup_read           | 0
idx_scan               | 0
last_idx_scan          | 
idx_tup_fetch          | 0
n_tup_ins              | 0
n_tup_upd              | 0
n_tup_del              | 0
n_tup_hot_upd          | 0
n_tup_newpage_upd      | 0
n_live_tup             | 0
n_dead_tup             | 0
n_mod_since_analyze    | 0
n_ins_since_vacuum     | 0
last_vacuum            | 
last_autovacuum        | 
last_analyze           | 2025-11-10 09:51:50.856101+05:30
last_autoanalyze       | 
vacuum_count           | 0
autovacuum_count       | 0
analyze_count          | 3
autoanalyze_count      | 0
total_vacuum_time      | 0
total_autovacuum_time  | 0
total_analyze_time     | 3
total_autoanalyze_time | 0

pg_visibility Functions Explained

Let’s explore each function provided by the pg_visibility extension.

1. pg_visibility(regclass)

Displays visibility details for each page of a relation (table).

SELECT * FROM pg_visibility('employees');

You get result like this

 blkno | all_visible | all_frozen | pd_all_visible 
-------+-------------+------------+----------------
     0 | t          | f          | t
     1 | t          | f          | t
(1 row)

This shows which pages have all-visible or all-frozen tuples.

2. pg_visibility(regclass, bigint)

Checks visibility for a specific page number.

SELECT * FROM pg_visibility('employees', 1);

This inspects visibility of page number 1 in the employees table.

3. pg_check_visible(regclass)

Checks whether all tuples that are marked all-visible are actually visible to all transactions.

SELECT * FROM pg_check_visible('employees');

This helps identify inconsistencies between the visibility map and actual data.

4. pg_check_frozen(regclass)

Verifies that all tuples marked as frozen are indeed frozen.

Useful for understanding VACUUM FREEZE behavior.

SELECT * FROM pg_check_frozen('employees');

5. pg_truncate_visibility_map(regclass)

Clears the visibility map for a relation, forcing PostgreSQL to rebuild it.

SELECT pg_truncate_visibility_map('employees');

This is mostly for diagnostic or testing purposes.

6. pg_visibility_map(regclass)

Displays the current visibility map.

SELECT * FROM pg_visibility_map('employees');

7. pg_visibility_map(regclass, bigint)

Shows the visibility map details for a specific page.

SELECT * FROM pg_visibility_map('employees', 2);

8. pg_visibility_map_summary(regclass)

Gives a summarized report of the visibility map.

SELECT * FROM pg_visibility_map_summary('employees');

You can use this to see how many pages are marked all-visible or all-frozen.

How pg_visibility Helps DBAs

The pg_visibility extension is especially useful for database administrators who need deep insights into table storage and vacuum efficiency. It helps in:

• Identifying pages skipped by vacuum due to visibility map errors.
• Verifying frozen state of tuples before major upgrades.
• Debugging visibility-related corruption or inconsistencies.
• Understanding how autovacuum interacts with page-level visibility.

For example, if you suspect autovacuum is skipping too many pages, pg_visibility_map_summary() will show how many pages are marked as all-visible or all-frozen, helping you diagnose issues.

Real-World Example

Let’s test with a small table:

CREATE TABLE emp (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO emp (name)
SELECT 'Employee_' || g
FROM generate_series(1,10000) g;
VACUUM emp;
SELECT * FROM pg_visibility_map_summary('emp');

Output might show:

all_visible | all_frozen 
-------------+------------
           1 |          0
(1 row)

This means all tuples are visible to everyone, but not all are frozen yet.

Conclusion

The pg_visibility  extension serves as a powerful window into PostgreSQL’s internal storage engine, allowing you to inspect how data visibility works at the page and tuple level. It provides deep insights into how VACUUM, autovacuum, and tuple freezing operate behind the scenes, ensuring transaction consistency and storage efficiency.

By understanding PostgreSQL’s 8 KB page structure, along with the Free Space Map (FSM) and Visibility Map (VM) files, DBAs and developers can use `pg_visibility` to detect bloat, monitor dead tuples, and optimize table performance. Mastering these tools helps maintain a clean, efficient, and high-performing PostgreSQL database.