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

2026-08-02

You deleted a million rows and the file is exactly the same size

The ticket usually reads like this: "Purged 40 million rows from events last night. pg_relation_size is unchanged. df -h is unchanged. Is the DELETE broken?"

▶ Watch on YouTube: Free Space Maps: Why Deletes Don't Shrink Files

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

The DELETE is fine. Postgres did exactly what it promises. What you freed is space inside the file, not space from it, and the difference between those two things is where most capacity planning for Postgres goes wrong — and it's the whole reason the postgres free space map exists.

I covered the short version of this on the MyDBA channel if you want the ten-minute walkthrough. This is the longer written version: the exact SQL, the truncation thresholds down to the constant names, the diagnostics for when nothing gets reclaimed at all, and the decision rule for when a rewrite is actually worth it.

Where the space actually goes

Postgres is an MVCC system, so a DELETE does not erase anything. It stamps the tuple's xmax with the deleting transaction ID. The row becomes invisible to snapshots taken after that transaction commits, but every byte of it still sits on the heap page exactly where it was.

Those bytes stay until VACUUM (or opportunistic page pruning) removes them. And VACUUM is not free to remove them whenever it likes. A dead tuple can only be reclaimed once it is older than the oldest snapshot any transaction on the system could still need. That boundary is the xmin horizon, and it is the single most important concept in this whole area. One long-running transaction holds it back for everybody. This is the mechanism behind why deletes don't free disk space in Postgres — the row is gone, the bytes aren't.

So there are three states, not two:

1. Live — visible, occupying space, doing its job. 2. Dead — invisible to everyone, still occupying space, waiting on the horizon. 3. Reusable — physically removed by VACUUM, page defragmented, space published as available.

"I deleted rows" only gets you to state 2. Getting to state 3 requires VACUUM. And getting the bytes back to the filesystem requires something else entirely, which we'll get to.

What the Free Space Map actually is

Every heap relation can have a Free Space Map stored in its own fork on disk. On the filesystem you'll see the main fork as <relfilenode>, the FSM as <relfilenode>_fsm, and the visibility map as <relfilenode>_vm. Three files, one relation.

The FSM stores one byte per heap page. A byte holds 256 values and the default page is 8kB, so free space is tracked in 32-byte increments (BLCKSZ/256) and always rounded down. A page with 4090 bytes free reports as 4064. This rounding is deliberate: the FSM is an advisory index, not an accounting ledger.

Structurally it's a three-level tree of FSM pages, each holding a binary tree of 4096 leaf slots. One FSM page therefore covers 4096 heap pages, about 32MB of heap at default block size. Run the numbers on a 100GB table and the FSM fork lands in the low tens of megabytes. It is noise.

Two details worth knowing:

Since PG 12, heaps of four pages or fewer (HEAP_FSM_CREATION_THRESHOLD) get no FSM fork at all. Postgres just checks those few pages directly. This keeps the hundreds of tiny lookup tables in a normal schema from each carrying a fork.
The FSM is not WAL-logged. Its contents can be stale after a crash, and can be out of date on a standby. That's safe, because the FSM is only a hint: the inserting backend takes the buffer lock and re-checks the page's actual free space. If the hint was wrong, it moves on to another page or extends the relation.

One more thing worth saying plainly, because I see it confused in half the blog posts on this topic: the function that reads the FSM is pg_freespace(), from the pg_freespacemap extension. It is not part of pageinspect. pageinspect is the one with heap_page_items(), page_header(), bt_page_stats() and friends, which read page internals directly. Different extensions, different jobs. Easy mistake, and both are worth having installed on a diagnostic box.

The lifecycle, in order

The sequence people get wrong is which step publishes the space.

1. DELETE stamps xmax. Nothing is freed. The FSM is untouched. 2. VACUUM runs, confirms the tuples are older than the xmin horizon, removes them, defragments the page so free space is contiguous, and then writes the FSM. During long vacuums the upper FSM levels are refreshed periodically rather than only at the end, so a multi-hour vacuum on a huge table starts feeding inserts before it finishes. 3. INSERT or non-HOT UPDATE consults the FSM for a page with enough room before extending the relation. 4. Steady state. The table stops growing because writes recycle into the holes.

Step 4 is the payoff, and it's why a high-churn table plateaus at some size instead of growing forever. That plateau is not bloat. That plateau is your working set plus whatever slack the churn rate demands.

See it yourself

Run this on a scratch database. Every line is copy-paste.

-- ~1GB heap
CREATE TABLE fsm_demo AS
SELECT g AS id,
       repeat('x', 200) AS payload
FROM generate_series(1, 4000000) g;

SELECT pg_size_pretty(pg_relation_size('fsm_demo')) AS heap;
--  heap
-- --------
--  1002 MB

Now delete half the rows, scattered evenly so no run of pages empties completely:

DELETE FROM fsm_demo WHERE id % 2 = 0;
-- DELETE 2000000

SELECT pg_size_pretty(pg_relation_size('fsm_demo')) AS heap;
--  1002 MB   <- unchanged, as expected

Vacuum, then measure again:

VACUUM (VERBOSE) fsm_demo;
-- INFO:  vacuuming "public.fsm_demo"
-- INFO:  table "fsm_demo": removed 2000000 dead item identifiers in 128206 pages
-- ...
-- tuples: 2000000 removed, 2000000 remain, 0 are dead but not yet removable

SELECT pg_size_pretty(pg_relation_size('fsm_demo')) AS heap;
--  1002 MB   <- still unchanged

Half the rows are gone and the file has not moved a byte. Now look at the forks and at what the FSM actually knows:

SELECT pg_size_pretty(pg_relation_size('fsm_demo','main')) AS main,
       pg_size_pretty(pg_relation_size('fsm_demo','fsm'))  AS fsm,
       pg_size_pretty(pg_relation_size('fsm_demo','vm'))   AS vm;
--  main    | fsm    | vm
-- ---------+--------+--------
--  1002 MB | 344 kB | 40 kB

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

SELECT count(*)                            AS pages_with_space,
       pg_size_pretty(sum(avail)::bigint)  AS reusable_lower_bound
FROM pg_freespace('fsm_demo')
WHERE avail > 0;
--  pages_with_space | reusable_lower_bound
-- ------------------+----------------------
--            128206 | 489 MB

The distribution matters more than the sum:

SELECT width_bucket(avail, 0, 8192, 8)     AS bucket,
       count(*)                            AS pages,
       pg_size_pretty(sum(avail)::bigint)  AS free_in_bucket
FROM pg_freespace('fsm_demo')
GROUP BY 1
ORDER BY 1;
--  bucket | pages  | free_in_bucket
-- --------+--------+----------------
--       4 | 128206 | 489 MB

Every page holds roughly half its capacity free. No page is empty, which is why nothing can be truncated. Remember that avail is the FSM's rounded-down category, so treat that 489 MB as a lower bound on reusable space, not a precise figure.

Now put the rows back and watch the file hold still:

INSERT INTO fsm_demo
SELECT g * 2, repeat('y', 200) FROM generate_series(1, 1000000) g;

SELECT pg_size_pretty(pg_relation_size('fsm_demo')) AS heap;
--  1002 MB   <- two million rows written, zero file growth

That is the FSM doing its job. If you monitor raw table size and page someone when it stops falling, you are paging on a healthy system.

Why files only shrink from the tail

Plain VACUUM can return space to the OS, but only under narrow conditions, and this is really a question of when vacuum will truncate a table in Postgres at all.

Truncation only removes a run of completely empty pages at the physical end of the relation. Free space in the middle is never given back by plain VACUUM. One live tuple on the last page and the entire tail stays. Unless your deletes happened to empty out a contiguous block right at the end of the file, they won't shrink it.

Before it even tries, should_attempt_truncation() checks whether the potentially freeable trailing space exceeds REL_TRUNCATE_MINIMUM (1000 blocks, so 8MB) or one sixteenth of the relation's page count (REL_TRUNCATE_FRACTION = 16). Below that, VACUUM leaves it alone. Not worth the disruption.

If it does try, lazy_truncate_heap() needs an ACCESS EXCLUSIVE lock — the strongest lock in Postgres, blocking every reader and writer. It takes it conditionally in a retry loop and gives up after roughly five seconds rather than blocking your queries. Having got the lock, it re-verifies the tail is still empty (another backend may have extended into it while VACUUM was scanning). And if any backend starts waiting behind that lock mid-truncate, VACUUM abandons the truncation and releases.

On a busy OLTP table, all of that adds up to: truncation essentially never fires. Not because Postgres is bad at it, but because a busy table almost never presents five uncontested seconds and a genuinely empty tail at the same time. That is by design, and it's the right trade.

You can also turn it off deliberately:

ALTER TABLE events SET (vacuum_truncate = false);
-- or, per command:
VACUUM (TRUNCATE false) events;

The common reason is replication. The AccessExclusiveLock gets replayed on hot standbys, where it will cancel standby queries. If you run reporting on a replica and the primary's truncations keep killing it, disabling truncation on the noisy tables is a reasonable trade.

The silent killers

Everything above assumes VACUUM can actually remove the dead tuples. When people tell me VACUUM ran, reported success, and reclaimed nothing, the xmin horizon is stuck open by one of:

a long-running transaction, even an idle one, holding a snapshot open
a session that's idle in transaction and forgot to commit
an abandoned or lagging replication slot
an orphaned prepared transaction from a two-phase commit that never resolved
hot_standby_feedback = on, which deliberately propagates a standby's horizon back to the primary so standby queries don't get cancelled — at the cost of pinning dead tuples on the primary for as long as that replica query runs

Three diagnostics I run in that order:

-- 1. Who is holding the oldest snapshot?
SELECT pid, state, backend_xmin,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- 2. Replication slots holding the horizon back
SELECT slot_name, slot_type, active, xmin, catalog_xmin,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST;

-- 3. Orphaned prepared transactions
SELECT gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY prepared;

If backend_xmin on some connection is days old, or a slot shows active = false with a stale xmin, you've found your reclaimed-nothing VACUUM. The fix is behavioral, not a config setting: kill the stuck session, drop the dead slot, resolve or roll back the prepared transaction.

The tell in VACUUM VERBOSE is the line N are dead but not yet removable. A large number there means you have a horizon problem, not a vacuum problem, and no amount of tuning autovacuum_vacuum_cost_limit will help.

If you want the dead-tuple ratio and last-autovacuum timestamp for every table in the estate on one screen rather than querying node by node, that's the kind of thing the MyDBA health check pulls together — it reads n_dead_tup against n_live_tup and flags tables where the ratio has drifted while last_autovacuum has gone stale.

War story: 19 days and a 177GB table

Last month I picked up a cluster where the backup bucket had filled. Not a disk alert on the database host. A backup storage alert.

The root cause was small and stupid: a shared library referenced by a function on one table had gone missing after a package upgrade, so autovacuum on that table failed every time it started. The failure itself was survivable. What made it expensive is what happened around it. The launcher kept retrying, wraparound-priority work took precedence elsewhere, and the effective consequence was that ordinary vacuuming across the cluster fell behind for 19 days.

Nobody noticed, because nothing looked broken. Queries were fine. Replication was fine. Disk on the primary had headroom — the growth was gradual against an already-large footprint.

Meanwhile an ingest table grew from a steady 8GB to 177GB, most of it dead tuples that no longer had any snapshot pinning them. This is postgres table bloat after delete in its purest form. Every full backup carried that 177GB. Every incremental carried the churn. WAL volume climbed with it. The heap bloat was the cheap part of the bill; the backup and WAL cost landed almost entirely outside the table itself.

The fix was the missing library plus a manual vacuum. The lesson is the ordering: bloat is a monitoring failure before it is a disk failure, and it shows up in backup and WAL cost before it shows up in df. If you only alert on filesystem usage on the primary, you will find out late.

Measuring: reusable space or genuine bloat?

Two different questions, two different tools.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- exact, scans the whole relation
SELECT * FROM pgstattuple('fsm_demo');

-- cheaper estimate, skips all-visible pages (heaps only)
SELECT * FROM pgstattuple_approx('fsm_demo');

free_percent tells you how much of the file is reusable slack. dead_tuple_percent tells you how much is still waiting on VACUUM. High dead-tuple percent means vacuum or horizon trouble. High free percent with low dead-tuple percent means the FSM is holding recycled space, which is either healthy steady state or genuine oversizing.

And for the day-to-day signal:

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

My decision rule is short. Will the table grow back into its current footprint within a normal business cycle? If yes, do nothing. The FSM will recycle it and a rewrite buys you a temporarily smaller file that refills anyway. If no, because you retired a feature or ran a one-off historical purge, then a rewrite is justified.

Also check indexes separately. B-tree pages emptied by VACUUM go on the index's free page list for reuse, not back to the OS, so a heap rewrite doesn't necessarily fix index bloat. REINDEX CONCURRENTLY (PG 12+) does.

Reclaiming for real: VACUUM FULL vs pg_repack and the rest

| Method | Lock | Extra disk | Online? | When it's right | |---|---|---|---|---| | VACUUM | SHARE UPDATE EXCLUSIVE | none | yes | Always. The default answer. | | VACUUM FULL | ACCESS EXCLUSIVE, whole run | full copy of table + indexes | no | Maintenance window, table will not regrow | | CLUSTER | ACCESS EXCLUSIVE, whole run | full copy of table + indexes | no | Same as above, plus you want physical index order | | pg_repack | brief strong locks at start and final swap | full copy of table + indexes | mostly | Large table, no window available | | TRUNCATE | ACCESS EXCLUSIVE, brief | none | no | Whole table goes, no filtering | | Partition DETACH/DROP | brief | none | effectively | Time-series retention |

Two opinions I'll state plainly.

VACUUM FULL on a live OLTP table during business hours is almost always the wrong answer. It holds ACCESS EXCLUSIVE for the entire rewrite, and it needs enough free disk for a second full copy of the table and every index. People reach for it while already short on disk, which is exactly when it can't run. I've seen it turn a bloat ticket into an outage ticket more than once — the lock queues up every subsequent query behind it and the whole app stalls waiting.

Most bloat tickets are really "the working set is that big." Before you plan a rewrite, check free_percent against your steady-state churn. If a table cycles 30% of its rows daily, carrying 30% slack is correct behaviour.

pg_repack gets you the same rewritten result with much shorter locks, by building the new copy alongside the old using a log table and triggers to capture concurrent writes. It still needs the disk headroom for a full copy, so check free space before you start, not after. That's the practical answer to vacuum full vs pg_repack: pick pg_repack whenever you can't afford the outage window.

The free options get forgotten. TRUNCATE creates a new empty file immediately. Dropping or detaching a time partition frees the disk instantly with no rewrite at all, which is why partitioning by time is the standard fix for anyone stuck in a recurring delete-then-reclaim cycle. If you're purging by date every month, you are solving a partitioning problem with a vacuum tool.

The operating rules I use

Alert on ratios, not raw size. Dead-tuple percentage and free_percent, plus last_autovacuum age. Table size going up is normal. Dead-tuple ratio climbing while last_autovacuum stays stale is the actual signal.

Tune autovacuum per table on anything high-churn. The defaults (autovacuum_vacuum_threshold 50 rows plus autovacuum_vacuum_scale_factor 0.2) mean a 200-million-row table waits for 40 million dead rows before autovacuum starts. That is the usual reason free space reaches the FSM far too late.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor  = 0.01,   -- 1% instead of 20%
  autovacuum_vacuum_threshold     = 1000,
  autovacuum_vacuum_cost_delay    = 2,
  autovacuum_vacuum_cost_limit    = 2000,
  autovacuum_analyze_scale_factor = 0.02
);

More on per-table settings in our autovacuum tuning guide, and on alert thresholds in monitoring bloat without false positives.

Treat a one-off purge as the only real justification for a rewrite. Recurring purges are a partitioning conversation.

Watch truncation behaviour on standbys. If replica queries are being cancelled by replayed AccessExclusiveLocks, set vacuum_truncate = false on the offending tables and accept the slightly larger file. Expect the FSM and heap fork sizes to diverge from the primary in that case — that's expected, not a fault.

Check the horizon before you blame VACUUM. backend_xmin, replication slots, prepared transactions. Thirty seconds of queries beats an afternoon of tuning the wrong thing.

If you'd rather not assemble the dead-tuple ratios, bloat estimates and vacuum timestamps across every database by hand, the MyDBA health check does that across the estate and hands back the specific VACUUM or REINDEX CONCURRENTLY command for the tables that actually need one, with the per-table settings already filled in. That's the part I find saves the most time: knowing which of four hundred tables deserves attention this week.