Postgres Free Space Map: Why Deletes Don't Shrink Tables

Why doesn't Postgres delete free disk space?

You ran DELETE FROM events WHERE created_at < now() - interval '90 days'. It reported 40 million rows deleted. You ran VACUUM ANALYZE events afterward, like a responsible adult. Then you checked:

📖 Read the full guide: Postgres Free Space Map: Why Deletes Don't Shrink Tables

▶ Watch the video walkthrough: Free Space Maps: Why Deletes Don't Shrink Files
https://www.youtube.com/watch?v=zo3ZF0yOGzE

SELECT pg_size_pretty(pg_relation_size('events'));

Same size as before. The disk alert is still firing, and someone's already typing "should we just run VACUUM FULL."

Short answer: the space is still inside the file. VACUUM handed it to the table's free space map (FSM) so future inserts into that table can reuse it. Postgres only shortens the file when the pages at the physical end are empty and vacuum can grab a brief exclusive lock. That's the whole story — everything below tells you whether what you're looking at is a healthy free-space pool you should leave alone, or a genuine leak.

One thing worth internalizing before anything else, because it kills half the bad decisions people make here: space reclaimed into a relation's FSM belongs to that relation forever. It never goes back to the operating system, and it never goes to another table, until the file is truncated or rewritten.

What DELETE actually does to a page

DELETE doesn't remove tuple data. It stamps the tuple's xmax with the deleting transaction's XID and commits. The bytes stay exactly where they were. Index entries still point at that heap TID. Any snapshot older than your delete can still walk the index, land on the tuple, check visibility, and read it.

That constraint is what makes everything else follow. Nothing can be freed while a snapshot might still need the row, so physical reclaim is a separate job, done later, by vacuum.

You can watch this happen. In one session:

BEGIN;
DELETE FROM events WHERE id = 12345;
-- don't commit yet

In another:

SELECT ctid, xmin, xmax FROM events WHERE id = 12345;

With pageinspect installed, you'll see the tuple's t_xmax populated while lp_flags still says the line pointer is normal. The row is marked, not gone.

The Postgres FSM fork: a third file next to your table

Every heap relation, and most indexes, can have a free space map stored as a separate fork. Postgres keeps three forks for a normal table on disk: the main fork (just the relfilenode number), the FSM fork (<relfilenode>_fsm), and the visibility map (<relfilenode>_vm).

Find them:

SELECT pg_relation_filepath('events');
--  base/16384/24601

On the box: ls -la $PGDATA/base/16384/24601* shows 24601, 24601.1, 24601.2 (1 GB segments of the main fork), plus 24601_fsm and 24601_vm.

From SQL, without shell access:

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;

This is also where a lot of "the size didn't change" confusion comes from. pg_relation_size() reports only the main fork by default. pg_table_size() adds FSM, VM and TOAST. pg_total_relation_size() adds indexes. If your purge freed a pile of TOAST chunks and you're measuring pg_relation_size, you're checking the wrong number.

How the map is structured

The FSM is deliberately cheap: one byte per heap page. Free space on that page is rounded down to a multiple of BLCKSZ/256 (32 bytes), so the value is approximate by design — a page with 8,191 free bytes and a page with 8,160 free bytes report the same value. Nobody needs byte-exact free space here; they need "enough room for this tuple, roughly."

The bytes form a binary tree inside each 8 kB FSM page, roughly 4000 leaf slots per page, with upper nodes holding the maximum of their children. A search for "a page with at least 400 bytes free" descends from the root, always following a branch whose max is big enough.

The overhead math is nice: one byte per 8192-byte page is 1/8192 of the table, about 128 KB of FSM per GB of heap. A 400 GB table carries roughly a 50 MB FSM. Nobody has ever been paged about FSM size.

FSM updates aren't WAL-logged — the values are hints. If a crash leaves the map stale, nothing breaks, because the inserting backend pins the actual page and rechecks real free space before using it.

Since PostgreSQL 12, relations smaller than 4 pages (HEAP_FSM_CREATION_THRESHOLD) get no FSM at all. Inserts just probe existing pages directly. If you query the FSM on a tiny table and get nothing back, that's why — not a bug.

How space gets back into the map, and how inserts find it

The sequence inside a vacuum pass over a heap page:

1. Prune the page — dead tuples whose xmax is older than the vacuum's horizon get their line pointers turned into LP_UNUSED (or LP_DEAD first, if indexes still reference them).
2. Defragment — live tuples slide together so the free space is contiguous.
3. RecordPageWithFreeSpace() writes the new byte into the FSM leaf.
4. Periodically during the run, and at the end, fsm_vacuum refreshes the upper levels so searches can actually find those pages. The periodic refresh fires every 8 GB of blocks, so on a huge table you don't have to wait for the whole vacuum to finish before inserts start reusing space.

On the insert side, RelationGetBufferForTuple() asks the FSM for a page with at least the tuple's length plus the space reserved by fillfactor. It tries a cached target block first — locality matters, the last page you inserted into is probably still in shared buffers. Only when the FSM search comes up empty does it extend the relation, which is the moment the file actually grows.

So: after a big delete plus vacuum, your file is full of holes, the FSM knows about them, and the next few hundred million inserts fill them in without the file growing a byte. That's the system working correctly.

Checking free space with pg_freespacemap (not pageinspect)

This trips people up constantly, so let's be blunt: pg_freespace() ships in the pg_freespacemap extension, not pageinspect. pageinspect has fsm_page_contents(), which dumps the raw binary tree of a single FSM page — a debugging tool for people patching the storage layer. If you want "how much reusable space does this table have," you want pg_freespacemap.

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

-- how many pages have any reusable space at all
SELECT count(*) FILTER (WHERE avail > 0) AS pages_with_space,
       count(*)                          AS total_pages
FROM pg_freespace('events');

-- distribution, in 1 kB buckets
SELECT width_bucket(avail, 0, 8192, 8) AS bucket,
       count(*)                        AS pages,
       pg_size_pretty(sum(avail)::bigint) AS total_free
FROM pg_freespace('events')
GROUP BY 1 ORDER BY 1;

-- the headline number: reusable bytes vs file size
SELECT pg_size_pretty(sum(avail)::bigint)               AS reusable,
       pg_size_pretty(pg_relation_size('events'))       AS on_disk,
       round(100.0 * sum(avail) / pg_relation_size('events'), 1) AS pct_free
FROM pg_freespace('events');

Two caveats when pointing this at an index. Index FSMs track only whole free pages, so pg_freespace() on a btree returns 0 or 8192 per page, never in between. And btree pages emptied by vacuum are marked deleted, not immediately recyclable — they can only be reused once no concurrent scan could still need them, which in practice usually means the space becomes available during a later vacuum. If you vacuum a heavily-deleted index and the FSM shows almost nothing free, run vacuum again before concluding the index is broken.

Also: tables under four pages have no FSM entries at all, so a zero-row result means "no FSM," not "no free space."

Vacuum truncate trailing pages: the rules nobody reads

Regular VACUUM can shrink the file, but only by chopping empty pages off the physical end. A free page in the middle stays a free page in the middle, recorded in the FSM, forever.

should_attempt_truncation() won't even try unless the number of potentially freeable trailing pages is at least 1000 pages (8 MB) or at least 1/16th of the relation's page count. On a 412 GB table with about 54 million pages, 1000 pages clears the bar easily — the real gate is whether the tail is empty at all.

If it decides to try, lazy_truncate_heap() needs an AccessExclusiveLock on the table. It acquires it conditionally, retrying for up to 5 seconds, and abandons the truncation the instant another backend starts waiting behind it on that lock. On a busy OLTP table this means truncation frequently just doesn't happen, quietly, forever. It then rescans the tail under the lock to confirm the pages are still empty before cutting.

You can turn this off. Per command: VACUUM (TRUNCATE false) events;. Per table: ALTER TABLE events SET (vacuum_truncate = off);. People do this on purpose, because the AccessExclusiveLock is WAL-logged and replayed on hot standbys, where it cancels running queries. If you have a reporting replica and a table that truncates a few pages every hour, you'll get mysterious query cancellations that trace back to autovacuum on the primary.

A demo you can paste into psql

CREATE TABLE fsm_demo (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload text NOT NULL
);

INSERT INTO fsm_demo (payload)
SELECT repeat('x', 40) FROM generate_series(1, 1000000);

VACUUM ANALYZE fsm_demo;
SELECT pg_size_pretty(pg_relation_size('fsm_demo')), relpages
FROM pg_class WHERE relname = 'fsm_demo';
-- roughly 85-90 MB, ~11,000 pages

Delete half the rows, scattered evenly, so no page ends up completely empty:

DELETE FROM fsm_demo WHERE id % 2 = 0;
VACUUM fsm_demo;

SELECT pg_size_pretty(pg_relation_size('fsm_demo'));
-- unchanged: still ~85-90 MB

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
SELECT count(*) FILTER (WHERE avail > 0) AS pages_with_space,
       pg_size_pretty(sum(avail)::bigint) AS reusable
FROM pg_freespace('fsm_demo');
-- ~11,000 pages, ~40 MB reusable

Prove the space is real by inserting 500k rows back:

INSERT INTO fsm_demo (payload)
SELECT repeat('y', 40) FROM generate_series(1, 500000);

SELECT pg_size_pretty(pg_relation_size('fsm_demo'));
-- still ~85-90 MB. The file did not grow.

Now delete the tail instead:

DELETE FROM fsm_demo WHERE (ctid::text::point)[0]::bigint > 4000;
VACUUM fsm_demo;

SELECT pg_size_pretty(pg_relation_size('fsm_demo'));
-- now it shrinks, to roughly 4000 pages ≈ 32 MB

Same number of deleted rows in both cases. Completely different disk outcome. That's the whole lesson in one script.

Postgres table bloat after delete: healthy or a leak?

The decision rule I use:

Healthy. A table with steady churn — a queue, a sessions table, an upsert-heavy dimension — will plateau where the free space in the FSM matches the working set of in-flight dead tuples. It'll sit at maybe 15–25% free and stay there. Reclaiming that with VACUUM FULL just means the table grows straight back to the same size, having taken an exclusive lock for the privilege.

A leak. A one-time purge on a table whose future insert rate will never consume the freed space. Or a table where autovacuum can't keep up and the free-space fraction is climbing month over month.

Get the numbers:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- exact, but full scan; use on a replica or a maintenance window
SELECT * FROM pgstattuple('events');

-- sampled, skips all-visible pages, fast enough for a 400 GB table
SELECT * FROM pgstattuple_approx('events');

Then do the reabsorption arithmetic. If pg_freespace says 180 GB reusable and the table normally grows 2 GB/month, it'll take 90 months to refill. That's a leak — reclaim it. If it grows 40 GB/month, you're four and a half months from steady state — leave it alone. Jump straight to pg_repack on a table that would have plateaued in six weeks anyway, and you've spent a lot of I/O and temporarily doubled your disk footprint for nothing.

Check whether vacuum is even running:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),1) AS pct_dead,
       last_vacuum, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC LIMIT 20;

Remember the default trigger: autovacuum fires when dead tuples exceed a threshold of 50 plus 0.2 times the estimated row count. On a 500 million row table that's 100 million dead tuples before anything happens. Set a per-table scale factor instead of living with that.

One more thing worth knowing on PG 16 or older: vacuum's dead-TID array was capped at 1 GB of maintenance_work_mem, about 178 million TIDs. Exceed that on a giant purge and vacuum makes multiple passes over every index. PG 17 replaced that array with TidStore, removing the cap and compressing TIDs. If your quarterly purge vacuum takes eleven hours, that's often why.

War story: when the FSM never got fed

A cluster I inherited had a package upgrade that left a mismatched shared library for an extension listed in shared_preload_libraries. Backend startup for autovacuum workers failed. Every worker, cluster-wide, died on launch.

This went on for 19 days. Nobody noticed, because the only autovacuum monitoring in place was on transaction ID age, and the anti-wraparound machinery kept enough attention on the oldest relations that the wraparound metric looked survivable. Dead tuple counts were collected but not alerted on.

Meanwhile the main ingest table, taking roughly 60 million inserts and updates a day, grew to 177 GB, the overwhelming majority dead tuples that were never pruned and never recorded in the FSM. Every insert extended the relation because the FSM had nothing to offer.

The part that actually caused the outage: the bloat propagated into the backups. Every full and differential backup carried the dead bytes, because at the file level they're just pages. The backup bucket that also held WAL archives filled up. WAL archiving stopped. pg_wal started growing on the primary. That's when someone finally looked.

The fix was thirty seconds — restart, workers started, autovacuum caught up over the next two days. The lesson took longer: monitor autovacuum liveness, not just dead-tuple counts. Alert on max(now() - last_autovacuum) across your top tables, and alert on autovacuum worker launch failures in the log. A dead-tuple alert tells you a table is bloating. A liveness alert tells you why, and catches all of them at once.

The check I run first on any cluster I haven't seen before:

SELECT max(now() - coalesce(last_autovacuum, last_vacuum, '-infinity'))
FROM pg_stat_user_tables WHERE n_live_tup > 100000;

If that comes back as weeks, stop reading and go check your logs. (There are tools that bundle this kind of check into a standing health report — MyDBA is one I've used for exactly this sort of "what's quietly broken" sweep.)

VACUUM FULL vs pg_repack: giving the disk back

Operation	Lock	Downtime	Extra disk	Returns space to OS
VACUUM	SHARE UPDATE EXCLUSIVE	None	None	Only trailing empty pages
VACUUM FULL	ACCESS EXCLUSIVE, whole run	Full, table unavailable	Second copy of table + new indexes	Yes, fully
CLUSTER	ACCESS EXCLUSIVE, whole run	Full	Same as VACUUM FULL	Yes, plus physical ordering
pg_repack	Brief ACCESS EXCLUSIVE at start and end	Seconds	Roughly 2x table + indexes	Yes
pg_squeeze	Brief, at swap	Seconds	Roughly 2x	Yes
TRUNCATE	ACCESS EXCLUSIVE, instant	Instant	None	Yes, immediately
DROP PARTITION	ACCESS EXCLUSIVE on parent, instant	Instant	None	Yes, immediately

VACUUM FULL rewrites the table into a fresh file and rebuilds every index. It holds ACCESS EXCLUSIVE for the whole operation and needs free disk for a full second copy plus new indexes. Running it on a 400 GB production table during business hours is a resume-generating event. It's the right tool for a 3 GB table at 2 AM.

pg_repack is what I reach for on anything large that has to stay online. It needs a primary key or a not-null unique index — no exceptions, check before you plan the maintenance — and it transiently needs roughly double the table's disk. It builds a copy while a trigger captures changes into an apply log, replays the log, then swaps under a short exclusive lock. Test the swap timing on a replica first.

pg_squeeze does the same job using logical decoding instead of triggers, which means less write amplification on the source table.

TRUNCATE releases disk instantly because it creates a new empty relation file rather than touching rows. If you can afford to lose the whole table's contents, this is always the answer.

DROP PARTITION is TRUNCATE with a business case — which brings us to the real recommendation.

Design so you never need this

Partition by time and drop partitions. DETACH PARTITION then DROP TABLE on a month of data is an instant metadata operation that returns every byte to the OS. No vacuum, no FSM, no lock drama, no 40-minute DELETE. If your purge policy is "keep 90 days," the correct implementation isn't a DELETE statement.

If you can't partition yet:

• Delete in batches — 50k to 100k rows at a time, with a VACUUM between batches. It keeps the dead-TID work bounded and lets the FSM update incrementally.
• Set fillfactor on update-heavy tables (ALTER TABLE t SET (fillfactor = 85)) so HOT updates can stay on-page and churn never touches the indexes.
• Tune autovacuum per table on your big churners: ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000).
• Consider vacuum_truncate = off on tables that cause replica query cancellations.
• Alert on autovacuum worker failures and last_autovacuum age. See above: 19 days.

Cheat sheet

Find the forks

SELECT pg_relation_filepath('t');
SELECT pg_relation_size('t','main'), pg_relation_size('t','fsm'), pg_relation_size('t','vm');

Measure reusable space (needs CREATE EXTENSION pg_freespacemap, not pageinspect)

SELECT pg_size_pretty(sum(avail)::bigint), count(*) FILTER (WHERE avail > 0)
FROM pg_freespace('t');

Truncation thresholds: trailing empty pages must be ≥1000 pages (8 MB) or ≥ relpages/16. The lock is conditional, 5-second timeout, and abandoned if any backend queues behind it.

Size functions: pg_relation_size = main fork only. pg_table_size = + FSM + VM + TOAST. pg_total_relation_size = + indexes.

Rule of thumb: divide reusable bytes by the table's monthly growth. Under six months to reabsorb, that's a working set — leave it alone. Over that, and only if the table won't grow into it, plan a pg_repack.

No FSM at all on heaps under 4 pages since PG 12. Index FSM reports whole free pages only, and freshly deleted btree pages usually aren't recyclable until a subsequent vacuum.

And the one that ends most arguments: freed space goes into that relation's FSM and stays there. It never goes to another table, and it never goes back to the OS until you truncate the tail or rewrite the file.