Visibility Map (VM) in PostgreSQL

Visibility Map (VM) in PostgreSQL

Table of Contents

1. What is the Visibility Map?

The Visibility Map (VM) is a tiny companion file stored alongside every PostgreSQL table. It tracks two boolean flags per 8 KB page:

  • all_visible: Are all rows on this page visible to all active transactions? If yes, PostgreSQL can skip visiting the heap page entirely during index-only scans.
  • all_frozen: Are all rows on this page frozen (their transaction IDs replaced with a special “FrozenXID”)? Frozen pages never need to be vacuumed for transaction ID wraparound prevention.

2. Prerequisites & Extension Setup

-- Enable extensions for VM and page-level inspection
CREATE EXTENSION IF NOT EXISTS pg_visibility;
CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

3. Create Demo Table & Insert Data

-- create a demo table
postgres=# DROP TABLE IF EXISTS demo_table;
DROP TABLE
postgres=# CREATE TABLE demo_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    status TEXT DEFAULT 'active'
);
CREATE TABLE
postgres=#


-- Insert 10,000 rows
postgres=# INSERT INTO demo_table (name, status)
SELECT
    'User_' || g,
    'active'
FROM generate_series(1, 10000) g;
INSERT 0 10000
postgres=#

postgres=# SELECT pg_relation_filepath('demo_table');
 pg_relation_filepath
----------------------
 base/5/65897
(1 row)

postgres=#

[postgres@pgdb01 ~]$ ls -la /pgData/pgsql17/data/base/5/65897*
-rw-------. 1 postgres postgres 524288 Sep 23 20:09 /pgData/pgsql17/data/base/5/65897
-rw-------. 1 postgres postgres  24576 Sep 23 20:09 /pgData/pgsql17/data/base/5/65897_fsm
-rw-------. 1 postgres postgres   8192 Sep 23 20:09 /pgData/pgsql17/data/base/5/65897_vm
[postgres@pgdb01 ~]$

4. Check VM Before VACUUM

Right after inserting data, no pages have been “cleaned” by VACUUM yet. The VM should show everything as not visible.

-- Check VM status for the first 20 pages
postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'demo_table';
  relname   | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------+-------------+-----------------+--------------+------------------
 demo_table |             |                 |              |
(1 row)

postgres=# 
postgres=# SELECT
    blkno,
    all_visible,
    all_frozen
FROM pg_visibility('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | f           | f
     2 | f           | f
     3 | f           | f
     4 | f           | f
     5 | f           | f
     6 | f           | f
     7 | f           | f
     8 | f           | f
     9 | f           | f
    10 | f           | f
    11 | f           | f
    12 | f           | f
    13 | f           | f
    14 | f           | f
    15 | f           | f
    16 | f           | f
    17 | f           | f
    18 | f           | f
    19 | f           | f
(20 rows)

postgres=# 
postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages,
    count(*) FILTER (WHERE all_frozen) AS frozen_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages | frozen_pages
-------------+---------------+-------------------+--------------
          64 |            0 |                 64 |            0
(1 row)

postgres=#

🔴 All pages are NOT visible. This means every index-only scan will be forced to visit the heap to verify row visibility. This is slow.

5. Run VACUUM & Watch VM Light Up

-- Run VACUUM
postgres=# VACUUM demo_table;
VACUUM
postgres=#

-- Check VM per page all pages visible now
postgres=# SELECT
    blkno,
    all_visible,
    all_frozen
FROM pg_visibility('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | f
     1 | t           | f
     2 | t           | f
     3 | t           | f
     4 | t           | f
     5 | t           | f
     6 | t           | f
     7 | t           | f
     8 | t           | f
     9 | t           | f
    10 | t           | f
    11 | t           | f
    12 | t           | f
    13 | t           | f
    14 | t           | f
    15 | t           | f
    16 | t           | f
    17 | t           | f
    18 | t           | f
    19 | t           | f
(20 rows)

postgres=#

-- Summary after VACUUM
postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages,
    count(*) FILTER (WHERE all_frozen) AS frozen_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages | frozen_pages
-------------+---------------+-------------------+--------------
          64 |            64 |                 0 |            0
(1 row)

postgres=#

✅ All 64 pages are now all_visible = true! VACUUM confirmed that every row on every page is visible to all transactions, and recorded this in the VM.

6. VM Impact on Index-Only Scans

This is where the VM pays off. When all_visible = true, PostgreSQL can answer queries using only the index without ever touching the main table (heap).

-- Create an index on the name column
postgres=# CREATE INDEX idx_demo_name ON demo_table(name);
CREATE INDEX
postgres=#

-- Update planner statistics
postgres=# ANALYZE demo_table;
ANALYZE
postgres=#

-- Run an index-only scan
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM demo_table WHERE name = 'User_500';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_demo_name on demo_table  (cost=0.29..4.30 rows=1 width=9) (actual time=0.044..0.045 rows=1 loops=1)
   Index Cond: (name = 'User_500'::text)
   Heap Fetches: 0
   Buffers: shared hit=1 read=2
 Planning:
   Buffers: shared hit=8 read=1
 Planning Time: 0.243 ms
 Execution Time: 0.067 ms
(8 rows)

postgres=#

Look for these two lines in the output:

 Index Only Scan using idx_demo_name on demo_table
   ...
   Heap Fetches: 0    <-- THIS IS THE VM IN ACTION!

What Heap Fetches: 0 means:

  • The query planner found the matching row in the index.
  • It checked the VM and saw the page is all_visible = true.
  • It skipped visiting the heap entirely because the VM guaranteed the row is visible.
  • Result: faster query, fewer disk reads.

7. Update Rows & Watch VM 

When you modify rows, the affected pages are no longer guaranteed to be all-visible. The VM flags flip back to false.

-- Update the first 1,000 rows

postgres=# UPDATE demo_table SET status = 'inactive' WHERE id BETWEEN 1 AND 1000;
UPDATE 1000
postgres=#

postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages
-------------+---------------+-------------------
          71 |            56 |                15
(1 row)

postgres=# 

-- Check index-only scan performance with dirty pages
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM demo_table WHERE name = 'User_500';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_demo_name on demo_table  (cost=0.29..8.30 rows=1 width=9) (actual time=0.106..0.117 rows=1 loops=1)
   Index Cond: (name = 'User_500'::text)
   Heap Fetches: 2
   Buffers: shared hit=5 dirtied=3
 Planning Time: 0.124 ms
 Execution Time: 0.142 ms
(6 rows)

postgres=#

What changed:

 Index Only Scan using idx_demo_name on demo_table
   ...
   Heap Fetches: 2    <-- Now > 0! Had to visit the heap.

⚠️ Impact: Because User_500 lives on a page that is no longer all_visible, PostgreSQL was forced to fetch the actual heap page to verify the row’s visibility. This adds extra I/O.

8. VACUUM Again to Restore Visibility

-- VACUUM to clean dead tuples and restore VM flags
postgres=# VACUUM demo_table;
VACUUM
postgres=#

postgres=# SELECT
    count(*) AS total_pages,
    count(*) FILTER (WHERE all_visible) AS visible_pages,
    count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages
FROM pg_visibility('demo_table');
 total_pages | visible_pages | not_visible_pages
-------------+---------------+-------------------
          71 |            71 |                 0
(1 row)

postgres=#

-- Index-only scan should be efficient again
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM demo_table WHERE name = 'User_500';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_demo_name on demo_table  (cost=0.29..4.30 rows=1 width=9) (actual time=0.031..0.032 rows=1 loops=1)
   Index Cond: (name = 'User_500'::text)
   Heap Fetches: 0
   Buffers: shared hit=3
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.235 ms
 Execution Time: 0.057 ms
(8 rows)

postgres=#

 Heap Fetches: 0    <-- Back to zero! VM restored.

9. VACUUM FREEZE & the all_frozen Flag

The all_frozen flag is about transaction ID wraparound prevention. When a page is frozen, all its row transaction IDs are replaced with a special FrozenXID (value 2), meaning those rows will be visible to all future transactions forever. Frozen pages never need anti-wraparound vacuuming.


-- Create Demo Table and Insert Data 

postgres=# CREATE TABLE employees (id INT, name TEXT, salary DECIMAL);
CREATE TABLE
postgres=#
postgres=# INSERT INTO employees
SELECT i, 'Employee ' || i, 50000 + (i * 100) FROM generate_series(1, 100000) i;
INSERT 0 100000
postgres=#

-- VACUUM

postgres=# VACUUM employees;
VACUUM
postgres=#

-- Check BEFORE Freezing

postgres=# SELECT
    count(*) FILTER (WHERE all_frozen) AS frozen_pages,
    count(*) FILTER (WHERE NOT all_frozen) AS not_frozen_pages
FROM pg_visibility('employees');
 frozen_pages | not_frozen_pages
--------------+------------------
            0 |              735
(1 row)

postgres=#

-- Freeze all pages
postgres=# VACUUM FREEZE employees;
VACUUM
postgres=#

-- Check again
postgres=# SELECT
    count(*) FILTER (WHERE all_frozen) AS frozen_pages,
    count(*) FILTER (WHERE NOT all_frozen) AS not_frozen_pages
FROM pg_visibility('employees');
 frozen_pages | not_frozen_pages
--------------+------------------
          735 |                0
(1 row)

postgres=#

postgres=# SELECT count(*) AS total_pages, count(*) FILTER (WHERE all_visible) AS visible_pages, count(*) FILTER (WHERE NOT all_visible) AS not_visible_pages, count(*) FILTER (WHERE all_frozen) AS frozen_pages FROM pg_visibility('employees');
 total_pages | visible_pages | not_visible_pages | frozen_pages
-------------+---------------+-------------------+--------------
         735 |           735 |                 0 |          735
(1 row)

postgres=#
postgres=# SELECT blkno, all_visible, all_frozen
FROM pg_visibility('employees')
LIMIT 10;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | t
     1 | t           | t
     2 | t           | t
     3 | t           | t
     4 | t           | t
     5 | t           | t
     6 | t           | t
     7 | t           | t
     8 | t           | t
     9 | t           | t
(10 rows)

postgres=#

✅ All pages are now frozen. These pages will be skipped by future anti-wraparound autovacuum runs, saving significant I/O on large tables.

10. Quick Cheatsheet

ConceptDetail
What is VM?A bitmap tracking all_visible and all_frozen per 8 KB page.
all_visibleAll rows on the page are visible to all transactions. Enables Index-Only Scans with Heap Fetches: 0.
all_frozenAll rows are frozen (transaction IDs replaced). Page is exempt from anti-wraparound vacuum.
When does VM update?VACUUM sets flags to true. INSERT/UPDATE/DELETE set flags to false.
Biggest benefitIndex-Only Scans skip heap I/O entirely when pages are all_visible.
Inspect VMSELECT * FROM pg_visibility('table');
Disk fileStored as <relfilenode>_vm next to the main table file.
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala