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