Postgres Free Space Map: Why DELETE Doesn't Shrink Tables

DELETE Freed Nothing: Understanding the Postgres Free Space Map

Someone deletes a million rows on a Friday afternoon, commits, checks pg_total_relation_size, and gets the same number back. Then they open a ticket saying VACUUM is broken.

VACUUM is fine. The mental model is wrong.

I recorded a three-minute version of this over on MyDBA if you just need the headline. This article goes further: the actual page mechanics, the FSM fork on disk, the source-level truncation thresholds, and the decision table for when you genuinely have to get the disk back.

The million-row delete that freed nothing

Here is the whole thing, reproducible on a laptop.

CREATE TABLE events (
  id      bigint PRIMARY KEY,
  payload text
);

INSERT INTO events
SELECT g, repeat('x', 100) FROM generate_series(1, 2000000) g;

SELECT pg_size_pretty(pg_relation_size('events'))       AS heap,
       pg_size_pretty(pg_total_relation_size('events')) AS total;

  heap  |  total
--------+---------
 270 MB | 313 MB

Now delete half of it, from the head of the table:

DELETE FROM events WHERE id <= 1000000;
-- DELETE 1000000
COMMIT;

SELECT pg_size_pretty(pg_relation_size('events')) AS heap;

  heap
--------
 270 MB

Identical. Run VACUUM (VERBOSE) events and check again:

  heap
--------
 270 MB

Still identical. Nothing is broken. The space was freed inside the file, not from the file. VACUUM found a million dead tuples, removed them, and wrote a note in a side file saying which pages now have room. That side file is the Free Space Map, and it is the thing almost nobody looks at when they should.

Why delete doesn't free disk space in Postgres

DELETE does not erase anything. It writes the deleting transaction's id into the tuple header's xmax field and moves on. The tuple's bytes stay exactly where they were, byte for byte, in whichever 8 KB page they landed in.

They have to. MVCC means another transaction with an older snapshot might still be entitled to see that row version. Until no snapshot in the cluster can see it, the tuple is dead but not removable, and Postgres will not touch it.

That distinction matters more than most of the vacuum tuning knobs people reach for:

• Dead: xmax is set and committed. The row is gone as far as your queries are concerned.
• Dead and removable: no running transaction, replication slot, or standby is holding back the removal horizon, so VACUUM can physically reclaim the space.

Only the second one produces free space. VACUUM removes the tuple data, compacts what remains in the page, and marks the line pointers (4 bytes each, at the top of the page) as unused so future tuples can reuse the slots. Trailing unused line pointers get trimmed. Then the page has real free space, and VACUUM records how much.

If you want to watch this at the byte level, pageinspect is the module for it:

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('events', 0))
LIMIT 5;

--  lp | t_xmin | t_xmax | t_ctid
-- ----+--------+--------+---------
--   1 |    881 |      0 | (0,1)
--   2 |    881 |      0 | (0,2)
--   3 |    881 |   1042 | (0,3)   <- deleted, xmax set
--   4 |    881 |      0 | (0,4)
--   5 |    881 |      0 | (0,5)

That row at lp = 3 has a nonzero t_xmax. Its bytes are untouched. Run VACUUM and query again: lp_len drops to 0 and lp_off drops to 0 for that slot. The pointer survives so nothing downstream has to be renumbered; the data does not.

The FSM fork: Postgres' third file per table

Every relation on disk is more than one file. The main fork holds your rows. Alongside it sit <relfilenode>_fsm (the Free Space Map) and <relfilenode>_vm (the visibility map). Unlogged tables also get an _init fork. You can see all three sitting next to each other:

SELECT pg_relation_filepath('events');
-- base/16401/24580

$ ls -la base/16401/24580*
-rw------- 1 postgres postgres 283115520 Aug  3 09:02 24580
-rw------- 1 postgres postgres     98304 Aug  3 09:02 24580_fsm
-rw------- 1 postgres postgres     16384 Aug  3 09:02 24580_vm

SELECT pg_size_pretty(pg_relation_size('events', 'main')) AS main,
       pg_size_pretty(pg_relation_size('events', 'fsm'))  AS fsm,
       pg_size_pretty(pg_relation_size('events', 'vm'))   AS vm;

  main  |  fsm  |  vm
--------+-------+--------
 270 MB | 96 kB | 16 kB

pg_total_relation_size bundles all of that plus indexes and TOAST, which is why it moves in ways pg_relation_size does not.

The FSM's internals are worth knowing, because they explain its limits:

• One byte per heap page. That byte encodes free space in 32-byte granularity (8192 / 256 = 32). So the numbers you read are approximate and rounded down. A page with 1,000 bytes free reports 992.
• A binary max-heap tree. With the default 8 kB block size each FSM page has a fanout of roughly 4,000 slots, and three levels address any relation Postgres can physically hold. Finding a page with enough room is a walk down the tree, not a scan.
• Cheap. One FSM page covers on the order of 4,000 heap pages, so the fork costs roughly 1/4000th of the main fork. About 25 MB of FSM for a 100 GB table.
• Not WAL-logged. The map can be stale or slightly wrong after a crash. That is safe by design: a backend re-checks the candidate page's actual free space before inserting, and VACUUM rewrites the values anyway.
• Absent on tiny tables. Since PostgreSQL 12, no FSM is created for heaps smaller than four pages (below HEAP_FSM_CREATION_THRESHOLD). Those are probed directly.

Treat the FSM as a hint, not a ledger. It is optimised for being fast and roughly right.

Reading the FSM yourself with pg_freespacemap

One correction first, because I see this get mixed up constantly: the extension is pg_freespacemap, and its functions are pg_freespace(regclass) and pg_freespace(regclass, blkno). pageinspect is a different module, for page-level internals like heap_page_items. Different extensions, different jobs.

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

SELECT * FROM pg_freespace('events') ORDER BY blkno LIMIT 5;

Immediately after the DELETE and before VACUUM:

 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0

After VACUUM events:

 blkno | avail
-------+-------
     0 |  8128
     1 |  8128
     2 |  8128
     3 |  8128
     4 |  8128

Now aggregate it. This is the query I actually run:

SELECT pg_size_pretty(sum(avail)::bigint)                 AS reusable,
       pg_size_pretty(pg_relation_size('events'))         AS heap,
       count(*) FILTER (WHERE avail > 2048)               AS pages_over_2kb,
       count(*)                                           AS pages
FROM pg_freespace('events');

 reusable |  heap  | pages_over_2kb | pages
----------+--------+----------------+-------
 132 MB   | 270 MB |          17241 | 34483

132 MB of the 270 MB file is empty and ready to be refilled. The file did not shrink because the emptiness is at the head, and heap files only shrink from the tail.

A histogram is often more useful than a total, because a hundred pages with 8 KB free is a very different asset from ten thousand pages with 400 bytes free:

SELECT width_bucket(avail, 0, 8192, 8) AS bucket,
       count(*), pg_size_pretty(sum(avail)::bigint)
FROM pg_freespace('events')
GROUP BY 1 ORDER BY 1;

How an INSERT uses the map

The insert path is short. A backend needs room for the new tuple, asks the FSM for a page with at least that much space plus whatever the table's fillfactor reserves, gets back a candidate block number, pins and re-checks that page's real free space, and writes. Only if nothing qualifies does it extend the relation with a fresh block at the end.

That re-check is why a stale, non-WAL-logged FSM is harmless. The map can lie; the page cannot.

Two consequences people trip over:

Fillfactor changes what counts as usable. At fillfactor = 80, roughly 1,600 bytes per page are held back for future HOT updates and will not be offered to plain inserts. Lowering fillfactor on an insert-only table just wastes disk.

UPDATE-heavy tables reuse differently. A HOT update places the new version on the same page as the old one when there is room and no indexed column changed. That keeps churn local and keeps indexes out of it, often without ever touching the FSM at all. Reserve the space deliberately:

-- config card: storage parameters worth setting explicitly
ALTER TABLE events SET (fillfactor = 90);                    -- UPDATE-heavy tables
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);  -- large tables
ALTER TABLE events SET (vacuum_truncate = false);            -- hot standby protection

Why the Postgres table isn't shrinking after delete

A relation's main fork is an array of fixed-size blocks addressed by block number. Block 4,102 lives at byte offset 4102 × 8192. There is no indirection layer. You cannot punch out the middle and renumber everything after it, because every index entry, every ctid, every buffer tag refers to that block number.

So truncation is tail-only, and it needs a run of completely empty trailing pages. One live tuple in the last page defeats the entire operation, no matter how empty the preceding 40,000 pages are.

The thresholds live in should_attempt_truncation() in vacuumlazy.c, and this is source-level implementation detail that can shift between major versions. Today: VACUUM only bothers attempting truncation when the number of potentially freeable trailing pages exceeds REL_TRUNCATE_MINIMUM (1,000 pages, which is 8 MB at the default block size) or is at least 1/16th of the relation (REL_TRUNCATE_FRACTION). Free 900 empty pages off the end of a large table and VACUUM will not even try.

Then there is the lock. Truncation requires ACCESS EXCLUSIVE on the table. VACUUM acquires it conditionally, with a timeout of about five seconds (VACUUM_TRUNCATE_LOCK_TIMEOUT), and it checks whether anyone has queued behind it. If another backend is waiting, VACUUM abandons or interrupts the truncation rather than stalling your workload.

On a table taking a few hundred queries a second, that check almost never passes. This is the real answer to "why did my nightly VACUUM shrink the table in staging but not in production."

Prove it to yourself by deleting from the tail instead:

DELETE FROM events WHERE id > 1900000;
VACUUM events;

SELECT pg_size_pretty(pg_relation_size('events'));

 pg_size_pretty
----------------
 256 MB

Same number of rows removed. Different end of the file. Different outcome. This is vacuum truncate in Postgres, and it's the mechanism most people mean when they ask why a table isn't shrinking.

When truncation is deliberately disabled

Some teams turn truncation off on purpose, with vacuum_truncate = false on the table or VACUUM (TRUNCATE FALSE) per command.

The usual reason is replication. That ACCESS EXCLUSIVE lock is WAL-logged and replays on hot standbys, where it causes recovery conflicts that cancel running read queries. If your reporting replica keeps dying with "canceling statement due to conflict with recovery" every night at 02:00, autovacuum truncating a hot table is a prime suspect.

The second reason is latency. Truncation holds an exclusive lock while it scans backwards; on a table with tight SLAs, the occasional stall is not worth the megabytes.

The trade-off is honest: you keep the disk allocated forever, and you rely entirely on the FSM to recycle it. For most append-and-expire workloads that is the correct choice.

Worth knowing: when VACUUM enters failsafe mode to outrun transaction id wraparound, it skips index vacuuming and heap truncation entirely. At that point Postgres is optimizing for one thing — advancing the relation's frozen XID before wraparound forces a shutdown. If you are in failsafe territory, disk reclamation is not on the agenda.

Indexes have an FSM too, and they never shrink

B-tree indexes play by similar rules with a stricter ending.

When VACUUM empties a B-tree page, that page is marked deleted. It only becomes recyclable once the deletion is old enough that no scan could still be looking at it. Recycled pages go into the index's own FSM and get reused by future inserts. The index file itself is never truncated.

SELECT count(*) FILTER (WHERE avail > 0) AS free_pages, count(*)
FROM pg_freespace('events_pkey');

You can watch an index sit at 43 MB with half its pages recyclable, forever. REINDEX CONCURRENTLY is the only practical tool for actually shrinking an index, and it needs disk for the new copy while it builds.

Diagnosing table bloat vs. a working set

Here is the rule, phrased so you can paste it into a thread:

Steady relation size with high free space is the FSM doing its job, so leave it alone. Monotonic size growth with high free space means your delete pattern and your insert pattern do not overlap, and that is worth acting on.

The measurements behind it:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple_approx('events');   -- cheap, uses the visibility map
SELECT * FROM pgstattuple('events');          -- exact, full scan, do not run at peak

free_percent and dead_tuple_percent are the two numbers that matter. High free_percent with low dead_tuple_percent means VACUUM is keeping up and the space is banked. High dead_tuple_percent means it is not.

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_all_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC;

And the one that actually settles the argument: record pg_relation_size for your top twenty tables once a day and look at the slope over a month. Free percentage is a snapshot. The trend line is the diagnosis.

I've seen the mismatch show up as a queue-style table too: rows deleted from the head as they're processed, new rows always appended at the tail because arrival order dictated insert order. The FSM correctly logged thousands of empty pages at the front of the file — but every insert landed at the tail regardless, because that's where the application always wrote, and the FSM has no way to route a new tuple backward into space the workload never looks at. The file grew for two years before anyone noticed the free-space sum had grown larger than the live data.

If you would rather not build that trend tracking yourself, the MyDBA health check tracks dead-tuple ratio and table size trend across every database in the estate and flags the tables where growth and free space are climbing together. That combination is the signal, and it is easy to miss when you are looking at one server at a time.

The callout that catches everyone: dead tuples cannot be removed while any transaction, replication slot, or standby with hot_standby_feedback holds back the oldest visible snapshot. A single idle in transaction session from a leaked connection pool will stop VACUUM freeing a single byte, no matter how many rows you deleted. No tuples removed means no FSM entries, ever.

SELECT pid, state, backend_xmin, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL ORDER BY age DESC LIMIT 5;

SELECT slot_name, active, xmin, catalog_xmin,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

Check these two before you touch anything else.

A table that grew while sitting on 60 GB of free space

An events table, roughly 400 GB, retention job deleting the oldest week every night. Disk climbing about 4 GB a day despite the deletes. The FSM reported 60 GB reusable and the file kept extending anyway.

Two things were happening. First, a logical replication slot for a decommissioned consumer had been inactive for six weeks, pinning the removal horizon. No tuples were removable, so no free space was recorded. We dropped the slot and autovacuum eventually caught up.

Second, and more interesting: after the FSM filled in, growth slowed but did not stop. The old rows being deleted averaged about 200 bytes. The new rows carried a JSONB column that had grown over two years to around 3 KB. The FSM was full of pages offering 1.2 KB, which is genuinely free space and genuinely useless to a 3 KB tuple. Backends asked, got nothing that fit, extended the relation.

The fix was a partitioning redesign, not a VACUUM FULL. Monthly range partitions, drop instead of delete. Space returns instantly and no map is involved.

How to reclaim disk space in Postgres for real

Method	Lock	Extra disk	Online	When I pick it
VACUUM	SHARE UPDATE EXCLUSIVE	none	yes	Always. Default answer. Reclaims into the FSM, may truncate the tail.
TRUNCATE	ACCESS EXCLUSIVE	none	no	Whole-table wipes. New relfilenode, instant, disk back immediately.
DROP / DETACH partition	ACCESS EXCLUSIVE (brief)	none	near	Time-series retention. The correct design.
VACUUM FULL	ACCESS EXCLUSIVE, whole duration	second copy of table + indexes	no	One-off cleanup in a maintenance window on a table you can afford to lock.
CLUSTER	ACCESS EXCLUSIVE	second copy	no	Same cost as VACUUM FULL but you also get physical ordering by an index.
pg_repack	ACCESS EXCLUSIVE briefly at start and swap	roughly double table + indexes	yes	Large hot tables. Needs a PRIMARY KEY or NOT NULL unique index.
REINDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	copy of the index	yes	Index bloat specifically. The only tool that shrinks an index file.

Nine times out of ten the right answer is do nothing. The space is banked and your inserts will spend it.

When it is not: pg_repack is what I reach for on production. It rebuilds the table using triggers and a log table, takes brief exclusive locks at the start and at the final swap, and keeps the table readable and writable in between. The failure modes are real, though. It needs roughly double the space of the table plus indexes while it runs, it needs that PK or unique not-null index, and on tables with extremely high write rates the log table can struggle to drain before the swap. Test the restore path before you run it on the 2 TB table. pg_squeeze covers similar ground using logical decoding instead of triggers.

VACUUM FULL is honest and simple, and if you have a genuine maintenance window it is the lower-risk option. It just needs enough free disk to hold a second copy of everything.

Designing so you never have to reclaim

• Partition by time. Drop partitions instead of deleting rows. This eliminates the entire problem class.
• Batch your deletes. Ten thousand rows at a time with a commit between lets autovacuum keep pace, instead of producing a 40 GB cliff of dead tuples.
• Lower autovacuum_vacuum_scale_factor on large tables. The 20% default means a 500 million row table waits for 100 million dead tuples. Set it to 0.02 or lower per table.
• Set fillfactor on UPDATE-heavy tables so HOT updates stay on-page.
• Police long transactions, abandoned replication slots, and hot_standby_feedback. They pin the removable horizon and defeat every item above.

The short version

• DELETE sets xmax. The bytes stay in the page until VACUUM removes them.
• VACUUM reclaims that space into the relation's FSM fork, one byte per page at 32-byte granularity.
• Inserts consult the FSM, re-check the real page, and only extend the file when nothing fits.
• Heap files shrink from the tail only, when trailing empty pages exceed 1,000 pages or 1/16th of the relation, and only if VACUUM can grab ACCESS EXCLUSIVE within about five seconds.
• Indexes recycle pages into their own FSM but never shrink. That needs REINDEX CONCURRENTLY.
• Long transactions and stale slots stop the whole chain before it starts.

Watch relation size trend, weekly, not free percentage. Flat size with plenty of free space is a healthy table recycling its own storage. Rising size with plenty of free space is the one to investigate.