Tag Archives: PostgreSQL Vacuum

Visibility Map (VM) in PostgreSQL

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

Free Space Map (FSM) in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Free Space Map (FSM) in PostgreSQL

Table of Contents

A. What is the Free Space Map (FSM)?

When you run an INSERT or UPDATE, how does PostgreSQL know which page on disk has enough empty space to fit the new row?

Scanning through gigabytes of table pages from disk to find empty spots would be terribly slow. Instead, PostgreSQL maintains a lightweight binary tree called the Free Space Map (FSM) for every table and index.

B. How PostgreSQL Stores Data (Pages & Blocks)

PostgreSQL splits table files on disk into chunks called Pages (or Blocks). By default, each page is 8 KB (8192 bytes).

  • Each page can hold multiple rows (tuples).
  • Each row has an address called a ctid written as (page_number, item_number). For example, (0, 1) means Page 0, 1st row.
  • The FSM records roughly how many bytes of free space remain on each 8 KB page.

1. Prerequisites & Extension Setup

To inspect the Free Space Map directly inside PostgreSQL, enable the official pg_freespacemap extension.

-- Enable extension
postgres=# CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
CREATE EXTENSION
postgres=#

2. Create Demo Table & Insert Data

We will create a table and insert 10,000 rows. We explicitly set the storage of description to PLAIN to disable TOAST compression so each row consumes ~200 bytes directly on the 8 KB page.

-- 1. Create a simple table
postgres=# DROP TABLE IF EXISTS demo_table;
DROP TABLE
postgres=# CREATE TABLE demo_table (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT
);
CREATE TABLE
postgres=#


-- 2. Disable compression to maintain exact byte sizes
postgres=# ALTER TABLE demo_table ALTER COLUMN description SET STORAGE PLAIN;
ALTER TABLE
postgres=#

-- 3. Insert 10,000 rows (~200 bytes per row)
postgres=# INSERT INTO demo_table (name, description)
SELECT
    'Name_' || generate_series,
    repeat('x', 200)
FROM generate_series(1, 10000);
INSERT 0 10000
postgres=#


-- 4. Check total disk size
postgres=# SELECT pg_size_pretty(pg_relation_size('demo_table')) AS table_size;
 table_size
------------
 2504 kB
(1 row)

postgres=#

-- 5. Check total 8 KB pages (blocks)
postgres=# SELECT pg_relation_size('demo_table') / 8192 AS total_pages;
 total_pages
-------------
         313
(1 row)

postgres=#

3. Inspect the Physical Files (Main, FSM, VM)

PostgreSQL splits table data on disk into multiple companion files (called forks).

-- Find the physical file path and relation filenode
postgres=# SELECT pg_relation_filepath('demo_table');
 pg_relation_filepath
----------------------
 base/5/65851
(1 row)

postgres=#

If you check your PostgreSQL data directory in the terminal, you will see three forks for this relation:

# Run in terminal inside $PGDATA
[postgres@pgdb01 ~]$ ls -la /pgData/pgsql17/data/base/5/65851*
-rw-------. 1 postgres postgres 2564096 Sep 23 17:44 /pgData/pgsql17/data/base/5/65851
-rw-------. 1 postgres postgres   24576 Sep 23 17:44 /pgData/pgsql17/data/base/5/65851_fsm  <-----------
-rw-------. 1 postgres postgres    8192 Sep 23 17:44 /pgData/pgsql17/data/base/5/65851_vm
[postgres@pgdb01 ~]$

65851        # Main data file (heap blocks/pages)
65851_fsm    # Free Space Map (tracks free bytes per block)
65851_vm     # Visibility Map (tracks all-visible / frozen blocks)

4. Check FSM Before Any Modifications

Let’s inspect the FSM to see how much room is available on each block.

-- Check free space in the first 20 pages
postgres=# SELECT
    blkno,
    avail AS free_space_bytes
FROM pg_freespace('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | free_space_bytes
-------+------------------
     0 |               96
     1 |               96
     2 |               96
     3 |               96
     4 |               96
     5 |               96
     6 |               96
     7 |               96
     8 |               96
     9 |               96
    10 |               96
    11 |               96
    12 |               96
    13 |               96
    14 |               96
    15 |               96
    16 |               96
    17 |               96
    18 |               96
    19 |               96
(20 rows)

postgres=#


-- Overall summary of free space across the entire table
postgres=# SELECT
    count(*) AS total_pages,
    avg(avail)::int AS avg_free_space,
    min(avail) AS min_free_space,
    max(avail) AS max_free_space,
    sum(avail) AS total_free_space
FROM pg_freespace('demo_table');
 total_pages | avg_free_space | min_free_space | max_free_space | total_free_space
-------------+----------------+----------------+----------------+------------------
         313 |            111 |             96 |           4864 |            34816
(1 row)

postgres=#

Observation: Most pages show very little available space (96 bytes free). The pages are packed full.

5. Delete Rows to Create Free Space (The FSM Lag)

Now let’s delete 50% of the rows (all even IDs) and immediately check the FSM.

-- Delete 50% of rows
postgres=# DELETE FROM demo_table WHERE id % 2 = 0;
DELETE 5000
postgres=#

-- Verify remaining count (should be 5,000)
postgres=# SELECT count(*) FROM demo_table;
 count
-------
  5000
(1 row)

postgres=#

-- Check FSM IMMEDIATELY after DELETE (before VACUUM)
postgres=# SELECT
    blkno,
    avail AS free_space_bytes
FROM pg_freespace('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | free_space_bytes
-------+------------------
     0 |               96
     1 |               96
     2 |               96
     3 |               96
     4 |               96
     5 |               96
     6 |               96
     7 |               96
     8 |               96
     9 |               96
    10 |               96
    11 |               96
    12 |               96
    13 |               96
    14 |               96
    15 |               96
    16 |               96
    17 |               96
    18 |               96
    19 |               96
(20 rows)

postgres=#

⚠️ Crucial Observation: The FSM still shows the exact same low free space as before! Because of MVCC, DELETE only flags rows as dead. The space has not been reclaimed, and the FSM has not been updated.

6. Run VACUUM & Observe FSM Changes

Running standard VACUUM clears dead rows and registers all newly available space into the FSM.

-- Run VACUUM to reclaim dead space and update FSM
postgres=# VACUUM demo_table;
VACUUM
postgres=#

-- Check FSM per block after VACUUM
postgres=# SELECT
    blkno,
    avail AS free_space_bytes
FROM pg_freespace('demo_table')
ORDER BY blkno
LIMIT 20;
 blkno | free_space_bytes
-------+------------------
     0 |             3936
     1 |             4192
     2 |             3936
     3 |             4064
     4 |             4064
     5 |             4064
     6 |             4064
     7 |             4064
     8 |             4064
     9 |             4064
    10 |             4064
    11 |             4064
    12 |             4064
    13 |             4064
    14 |             4064
    15 |             4064
    16 |             4064
    17 |             4064
    18 |             4064
    19 |             4064
(20 rows)

postgres=#

-- Summary after VACUUM
postgres=# SELECT
    count(*) AS total_pages,
    avg(avail)::int AS avg_free_space,
    min(avail) AS min_free_space,
    max(avail) AS max_free_space,
    sum(avail) AS total_free_space
FROM pg_freespace('demo_table');
 total_pages | avg_free_space | min_free_space | max_free_space | total_free_space
-------------+----------------+----------------+----------------+------------------
         313 |           4072 |           3936 |           6624 |          1274464
(1 row)

postgres=#

Result: Each page now reports substantial free space (roughly 3,936 to 4,064 bytes available per page).

7. Insert New Rows & Watch FSM Guide Placement

Let’s insert 2,000 new rows. Watch how PostgreSQL uses the FSM to place them inside existing pages instead of growing the file.

-- 1. Record current page count
postgres=# SELECT pg_relation_size('demo_table') / 8192 AS pages_before_insert;
 pages_before_insert
---------------------
                 313
(1 row)

postgres=#

-- 2. Insert 2,000 new rows
postgres=# INSERT INTO demo_table (name, description)
SELECT
    'NewName_' || generate_series,
    repeat('y', 200)
FROM generate_series(1, 2000);
INSERT 0 2000
postgres=#

-- 3. Check page count again
postgres=# SELECT pg_relation_size('demo_table') / 8192 AS pages_after_insert;
 pages_after_insert
--------------------
                313
(1 row)

postgres=#

What happened?

  • pages_before_insert and pages_after_insert are identical!
  • The table file did not expand at all.
  • The FSM directed PostgreSQL to pages with available free space, allowing the new rows to be inserted into the existing space.
-- Check free space reduction after inserts
postgres=# SELECT
    count(*) AS total_pages,
    avg(avail)::int AS avg_free_space,
    sum(avail) AS total_free_space
FROM pg_freespace('demo_table');
 total_pages | avg_free_space | total_free_space
-------------+----------------+------------------
         313 |           2489 |           778976
(1 row)

postgres=#

8. Compare VACUUM vs VACUUM FULL on FSM

Let’s see how standard VACUUM differs from VACUUM FULL regarding physical disk size and the FSM.

-- 1. Delete more rows
postgres=# DELETE FROM demo_table WHERE id % 3 = 0;
DELETE 2334
postgres=#

-- 2. Run standard VACUUM
postgres=# VACUUM demo_table;
VACUUM
postgres=#

-- Table size remains the same (space is kept for Postgres reuse, not given back to OS)
postgres=# SELECT pg_size_pretty(pg_relation_size('demo_table')) AS size_after_vacuum;
 size_after_vacuum
-------------------
 2504 kB
(1 row)

postgres=#
postgres=# SELECT sum(avail) AS total_free_after_vacuum FROM pg_freespace('demo_table');
 total_free_after_vacuum
-------------------------
                 1351904
(1 row)

postgres=#

-- 3. Run VACUUM FULL (Rewrites and compacts entire table)
postgres=# VACUUM FULL demo_table;
VACUUM
postgres=#

-- Physical table size shrinks on disk
postgres=# SELECT pg_size_pretty(pg_relation_size('demo_table')) AS size_after_vacuum_full;
 size_after_vacuum_full
------------------------
 1168 kB
(1 row)

postgres=#

-- The FSM free space is reset because the table has been reduced to fewer pages. As a result, PostgreSQL has fewer pages to track for free space.
Note: VACUUM FULL rewrites the entire table into a new file on disk.

postgres=# SELECT coalesce(sum(avail), 0) AS total_free_after_vacuum_full FROM pg_freespace('demo_table');
 total_free_after_vacuum_full
------------------------------
                            0
(1 row)

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

postgres=#
[postgres@pgdb01 ~]$ ls -la /pgData/pgsql17/data/base/5/65859*
-rw-------. 1 postgres postgres 1196032 Sep 23 18:18 /pgData/pgsql17/data/base/5/65859
[postgres@pgdb01 ~]$

Warning: VACUUM FULL rewrites the entire table into a new file on disk. It places an EXCLUSIVE LOCK on the table, blocking all reads and writes until it finishes. Use with caution in production!

9. Quick Cheatsheet

ConceptDescription
What is FSM?A binary tree tracking free space across all 8 KB pages in a table.
PurposeSpeeds up INSERT/UPDATE by finding pages with room instantly.
Who updates it?VACUUM / autovacuum updates the FSM after cleaning dead rows.
Inspection ToolSELECT * FROM pg_freespace('table_name');
Disk FileStored alongside the main table file with the _fsm suffix.
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

VACUUM in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , ,

VACUUM in PostgreSQL

Table of Contents

1. Why Do We Need VACUUM? (The Problem)

Because of  MVCC, PostgreSQL does not directly overwrite a row when you run an UPDATE, and it does not immediately remove the old row data when you run a DELETE.

  • An UPDATE creates a brand-new row and marks the old one as “dead”.
  • A DELETE simply marks the existing row as “dead”.

Over time, these “dead rows” (also called dead tuples) accumulate. This is called table bloat. It wastes disk space and slows down your queries.

VACUUM is PostgreSQL’s garbage collector. It cleans up dead rows so your database stays fast and lean.

2. Setting Up a Test Table

Let’s create a playground table and insert 1,00,00,000 rows to see VACUUM in action.

-- Create a sample items table
postgres=# DROP TABLE IF EXISTS items;
DROP TABLE
postgres=# CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price INT
);
CREATE TABLE
postgres=#

-- Disable autovacuum for one test table, just for DEMO

postgres=# ALTER TABLE items SET (autovacuum_enabled = false);
ALTER TABLE
postgres=#

postgres=# SELECT relname, reloptions FROM pg_class WHERE relname = 'items';
 relname |         reloptions
---------+----------------------------
 items   | {autovacuum_enabled=false}
(1 row)

postgres=#

-- Insert 1,00,00,000 rows

postgres=# INSERT INTO items (name, price)
SELECT
    'Item ' || generate_series,
    (random() * 100)::INT
FROM generate_series(1, 10000000);
INSERT 0 10000000
postgres=#

-- Table physical path
postgres=# SELECT pg_relation_filepath('public.items');
 pg_relation_filepath
----------------------
 base/5/65690
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 144K Sep 22 19:09 /pgData/pgsql17/data/base/5/65690_fsm
-rw-------. 1 postgres postgres 498M Sep 22 19:13 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

3. Creating Table Bloat (Generating Dead Rows)

Now let’s update all 1,00,00,000 rows. This will generate 1,00,00,000 dead rows alongside the 1,00,00,000 new rows.

-- Update every row
postgres=# UPDATE items SET price = price + 10;
UPDATE 10000000
postgres=#

4. Inspecting Dead Rows & Table Size

Let’s check how many dead rows exist and how large the table has become.

-- Check dead row count

postgres=# SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 items   |   10000147 |   10000000
(1 row)

postgres=# 

-- Check physical disk size of the table

postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 272K Sep 22 19:16 /pgData/pgsql17/data/base/5/65690_fsm
-rw-------. 1 postgres postgres 996M Sep 22 19:16 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

5. Standard VACUUM (Reclaiming Space for Reuse)

A standard VACUUM marks the dead rows as free space so future INSERT or UPDATE operations can reuse it, but it won’t release free space to the OS

-- Run standard vacuum
postgres=# VACUUM VERBOSE items;
INFO:  vacuuming "postgres.public.items"
INFO:  finished vacuuming "postgres.public.items": index scans: 1
pages: 0 removed, 127389 remain, 127389 scanned (100.00% of total)
tuples: 10000000 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 2178, which was 0 XIDs old when operation ended
new relfrozenxid: 2176, which is 4 XIDs ahead of previous value
frozen: 48685 pages from table (38.22% of total) had 7643430 tuples frozen
index scan needed: 63695 pages from table (50.00% of total) had 10000000 dead item identifiers removed
index "items_pkey": pages: 54839 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 64.788 MB/s, avg write rate: 69.883 MB/s
buffer usage: 146023 hits, 227350 misses, 245229 dirtied
WAL usage: 455518 records, 245755 full page images, 1191493291 bytes
system usage: CPU: user: 10.29 s, system: 2.81 s, elapsed: 27.41 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65690"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65690": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2178, which was 0 XIDs old when operation ended
new relfrozenxid: 2178, which is 6 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 1.178 MB/s, avg write rate: 1.178 MB/s
buffer usage: 9 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 7245 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
postgres=#


-- Check dead rows again

postgres=# SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 items   |   10000000 |          0  <---- all dead rows deleted.
(1 row)

postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB      <--------- free space won't release to the OS, We can only reuse the unused space.
(1 row)

postgres=#

Important Note on Standard VACUUM:

  • n_dead_tup dropped to 0 (the garbage was cleaned).
  • However, if you check pg_total_relation_size('items'), it will still show 1424 MB.
  • Standard VACUUM does not shrink the file size back to the operating system. Instead, it keeps the empty slots ready inside PostgreSQL so new inserts won’t require growing the file again.
  • Non-blocking: Standard VACUUM allows normal SELECT, INSERT, UPDATE, and DELETE queries to continue running while it works.

6. VACUUM FULL (Shrinking Disk Space to the OS)

If you deleted millions of rows and you must return the unused disk space back to your operating system, you need VACUUM FULL.

-- Check table size
postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB
(1 row)

postgres=#

-- Run VACUUM FULL
postgres=# VACUUM FULL items;
VACUUM
postgres=#

-- Check table size again
postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 712 MB  <--- shrunk back down to original size!, free space released to the OS. 
(1 row)

postgres=#

-- VACUUM FULL rewrites the entire table into a new file on disk.

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 0 Sep 22 19:23 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

postgres=# SELECT pg_relation_filepath('public.items');
 pg_relation_filepath
----------------------
 base/5/65698
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65698*
-rw-------. 1 postgres postgres 498M Sep 22 19:23 /pgData/pgsql17/data/base/5/65698
[postgres@pgdb01 5]$

65690 -- This file no more availble.

Warning: VACUUM FULL rewrites the entire table into a new file on disk. It places an EXCLUSIVE LOCK on the table, blocking all reads and writes until it finishes. Use with caution in production!

7. VACUUM ANALYZE (Optimizing Query Performance)

Whenever data changes significantly, PostgreSQL’s query optimizer needs updated statistics to choose the fastest query plan.

VACUUM ANALYZE cleans up dead rows and refreshes the planner’s statistics in a single command:

-- Clean dead rows + update query planner statistics
postgres=# VACUUM ANALYZE items;
VACUUM
postgres=#

Pro Tip: Always run VACUUM ANALYZE after large bulk imports or mass data updates.

8. Autovacuum: The Background Worker

You don’t need to manually run VACUUM all day. PostgreSQL includes a daemon called Autovacuum that triggers automatically when a table exceeds a dead-tuple threshold (by default, when ~20% of rows are modified).

-- It was disabled for DEMO, enable back to ture.
postgres=# ALTER TABLE items SET (autovacuum_enabled = true);
ALTER TABLE
postgres=#

--- Confirming default autovacuum settings

postgres=# SHOW autovacuum;
 autovacuum
------------
 on
(1 row)

postgres=# SHOW autovacuum_naptime;
 autovacuum_naptime
--------------------
 1min
(1 row)

postgres=# SHOW autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 50
(1 row)

postgres=# SHOW autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 row)

postgres=#

-- Check when autovacuum last cleaned your tables
postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'items';
 relname |         last_vacuum          | last_autovacuum |         last_analyze          | last_autoanalyze
---------+------------------------------+-----------------+-------------------------------+------------------
 items   | 2026-09-22 19:28:22.29634+08 |                 | 2026-09-22 19:28:22.992613+08 |
(1 row)

postgres=#

-- LOAD some data and check again autovacuum last cleaned status

postgres=# UPDATE items SET price = price + 20;
UPDATE 10000000
postgres=#

postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'items';
 relname |         last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze
---------+------------------------------+-------------------------------+-------------------------------+-------------------------------
 items   | 2026-09-22 19:28:22.29634+08 | 2026-09-22 19:40:56.685873+08 | 2026-09-22 19:28:22.992613+08 | 2026-09-22 19:40:58.096743+08
(1 row)

postgres=#

Note: Autovacuum automatically performs VACUUM to clean up obsolete row versions and ANALYZE to maintain optimizer statistics. It does NOT performs VACUUM FULL.

9. Quick Summary Cheatsheet

CommandWhat it DoesReclaims OS Disk?Locks Table?
VACUUMCleans dead rows; marks space reusable for PostgresNoNo (Safe)
VACUUM ANALYZECleans dead rows + updates optimizer statisticsNoNo (Safe)
VACUUM FULLRewrites table completely, shrinks file sizeYesYes (Exclusive lock)
AutovacuumRuns standard vacuum automatically in backgroundNoNo (Safe)
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

MVCC in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

MVCC in PostgreSQL

Table of Contents

1. The Core Concept (Why MVCC?)

MVCC stands for Multi-Version Concurrency Control.

Its primary goal is simple: Readers should never block Writers, and Writers should never block Readers.

Instead of locking a row when someone is updating it (which makes everyone else wait), PostgreSQL keeps multiple versions of the same row active at the same time. Different transactions see different versions depending on when they started.

2. Hidden Columns (xmin & xmax)

Every table you create in PostgreSQL has hidden system columns. The two most important ones for MVCC are:

  • xmin: The Transaction ID that created/inserted this row version.
  • xmax: The Transaction ID that deleted or updated this row version. (If it is 0, the row is still actively alive).
-- To view these columns by explicitly querying them:
SELECT xmin, xmax, * FROM table_name;

3. Create a Test Table

-- Create a simple products table and  insert our first record and see how Postgres assigns the transaction ID.

[postgres@pgdb01 ~]$ psql -c "CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,price INT);"
CREATE TABLE
[postgres@pgdb01 ~]$

-- Insert a row
[postgres@pgdb01 ~]$ psql -c "INSERT INTO products (name, price) VALUES ('Laptop', 1000);"
INSERT 0 1
[postgres@pgdb01 ~]$ 

-- Query the row with xmin and xmax
[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 |    0 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$
  • xmin = 2156: xmin represents the transaction ID that created the row version. xmin = 2156
  • xmax = 0: xmax represents the transaction ID that deleted or updated. xmax = 0 means No one has deleted/updated it yet . It is actively visible to everyone.

4. UPDATE Creates a New Version

Now, let’s see MVCC in action. Open two separate database sessions/terminal windows.

Terminal 1 (The Writer): Start a transaction and update the price, but do not commit yet.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*#

Terminal 2 (The Reader): Check what Terminal 2 sees:

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 | 2157 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$
Internally, PostgreSQL creates a new row version.

Before UPDATE

Row Version A
+----------------+
| id = 1         |
| price = 1000   |
+----------------+


After UPDATE

Old Version                New Version
+----------------+          +----------------+
| id = 1         |          | id = 1         |
| price = 1000   |          | price = 1200   |
| xmax = 2157    |          | xmin = 2157    |
+----------------+          +----------------+
        |                           |
        | old version               | new version
        v                           v
      xmax                         xmin

xmax = old version (xmax refers Dead rows)
xmin = new version
The old row version is not immediately physically removed.
  • The reader still sees the original price of 1000. They are not blocked!
  • The row now has an xmax of 2157. This tells Postgres: “Transaction 2157 is currently changing or deleting this row. If you aren’t Transaction 2157, keep looking at the old data.”
  • Under the hood, Postgres has already written a new row version with a price of 1200 and xmin = 2157, but it is completely invisible to Terminal 2 because transaction 2157 hasn’t committed yet.

5. After COMMIT

Let’s finalize the transaction and see the transition.

Terminal 1 (The Writer): Commit the change.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*# COMMIT;   <------------------ (only run COMMIT)
COMMIT
postgres=#

Terminal 2 (The Reader): Query the table again. What Terminal 2 sees now

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2157 |    0 |  1 | Laptop |  1200
(1 row)

[postgres@pgdb01 ~]$
  • The old row version (price 1000) is now marked as dead(invisible).
  • The new row version (price 1200) is now alive(current row) with xmin = 2157 and xmax = 0.

6. DELETE Doesn’t Actually Delete

In Postgres, a DELETE statement does not physically erase data from the hard drive immediately. It just changes a flag.

postgres=# BEGIN;
BEGIN
postgres=*# DELETE FROM products WHERE id = 1;
DELETE 1
postgres=*# SELECT txid_current();
 txid_current
--------------
         2158
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#


A DELETE Internally, simply marks the existing row as dead.

Before DELETE

+----------------+
| id=1           |
| pirce = 1200   |
+----------------+

After DELETE

+----------------+
| id=1           |
| price = 1200   |
| xmax = 2158    |
+----------------+

Later, VACUUM can remove dead tuples when they are no longer needed by active transactions.

Under the hood, Postgres simply updated the existing row’s xmax value to 2158. Because transaction 2158 is committed, Postgres now knows that this row is dead and shouldn’t be shown to any new queries. The actual space on the disk won’t be freed until we vacuum.

7. Dead tuples and VACUUM

Because UPDATE creates new rows and DELETE just marks them as dead, database will accumulate “dead tuples” (bloat) over time.
In simple way, Since UPDATE and DELETE don’t remove old rows, dead rows pile up. This is called bloat.

Postgres uses a process called VACUUM to find these dead rows, clear them out, and mark that space as reusable for future inserts.

-- 1. Check how many dead rows are lurking a table

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          2   <--------------------
(1 row)

[postgres@pgdb01 ~]$


-- 2. Run a manual vacuum to clean up

[postgres@pgdb01 ~]$ psql -c "VACUUM VERBOSE products;"
INFO:  vacuuming "postgres.public.products"
INFO:  table "products": truncated 1 to 0 pages
INFO:  finished vacuuming "postgres.public.products": index scans: 1
pages: 1 removed, 0 remain, 1 scanned (100.00% of total)
tuples: 2 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2159, which was 1 XIDs old when operation ended
new relfrozenxid: 2159, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed
index "products_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 11.905 MB/s
buffer usage: 61 hits, 0 misses, 8 dirtied
WAL usage: 11 records, 8 full page images, 47529 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65663"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65663": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2160, which was 0 XIDs old when operation ended
new relfrozenxid: 2160, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 22.978 MB/s, avg write rate: 22.978 MB/s
buffer usage: 27 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 8097 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
[postgres@pgdb01 ~]$


-- 3. Check again; dead rows should now be 0

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          0  
(1 row)

[postgres@pgdb01 ~]$

Note: PostgreSQL runs an Autovacuum background process automatically, so you rarely need to run manual vacuums in production.

8. Quick Summary Cheatsheet

To keep MVCC simple, remember these four rules:

OperationWhat Postgres Actually Does
INSERTCreates a row with xmin = Current TXID.
DELETEMark row as dead, does not physically erase it. Sets xmax = Current TXID
UPDATE INSERT new version + DELETE (mark old version as dead).
VACUUMSweeps up the dead rows to reclaim storage space.
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