DEV Community

Philip McClarence
Philip McClarence

Posted on

Why Postgres DELETE Doesn't Free Disk Space (FSM Explained)

You ran a DELETE, checked df, and the disk didn't move. That's not a bug — it's the free space map doing exactly what it's designed to do.

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

Why Postgres DELETE Doesn't Free Disk Space (FSM Explained)

Short answer: DELETE marks rows dead but leaves the bytes in the heap page. VACUUM doesn't remove those bytes either — it makes them reusable and records them in the table's free space map (FSM) so future inserts can land there. The file only shrinks when VACUUM can truncate a completely empty tail, which on a busy table almost never happens. If you delete and re-insert at similar volumes, the file size you're looking at isn't bloat — it's working capital.

Here's the full picture, with the psql output so you can confirm it on your own database.

The 60-second reproduction 

CREATE TABLE t_scattered (
  id      int PRIMARY KEY,
  payload text NOT NULL
);

INSERT INTO t_scattered
SELECT g, repeat('x', 100)
FROM generate_series(1, 1000000) g;

SELECT pg_size_pretty(pg_relation_size('t_scattered')) AS heap;
Enter fullscreen mode Exit fullscreen mode 
   heap
---------
 143 MB
(1 row)
Enter fullscreen mode Exit fullscreen mode

Now delete 90% of it with a predicate that hits every page, not a contiguous range:

DELETE FROM t_scattered WHERE id % 10 <> 0;
VACUUM VERBOSE t_scattered;
Enter fullscreen mode Exit fullscreen mode 
INFO:  vacuuming "app.public.t_scattered"
INFO:  finished vacuuming "app.public.t_scattered": index scans: 1
pages: 0 removed, 18282 remain, 18282 scanned (100.00% of total)
tuples: 900000 removed, 100000 remain, 0 are dead but not yet removable
Enter fullscreen mode Exit fullscreen mode 
SELECT pg_size_pretty(pg_relation_size('t_scattered')) AS heap;
Enter fullscreen mode Exit fullscreen mode 
   heap
---------
 143 MB
(1 row)
Enter fullscreen mode Exit fullscreen mode

Byte for byte identical. pages: 0 removed is the line that tells you why.

Where the space actually went

Two different things get confused constantly.

Dead tuples are row versions no snapshot can see anymore but that still occupy bytes in the page. Free space is what those bytes become after VACUUM strips the dead payload and compacts the page. VACUUM converts the first into the second — it does not remove bytes from the file.

Every heap has up to three forks on disk: the main fork (your data), the _fsm fork (free space map), and the _vm fork (visibility map). Look at all of them:

SELECT pg_size_pretty(pg_relation_size('t_scattered','main')) AS main,
       pg_size_pretty(pg_relation_size('t_scattered','fsm'))  AS fsm,
       pg_size_pretty(pg_relation_size('t_scattered','vm'))   AS vm,
       pg_size_pretty(pg_table_size('t_scattered'))           AS table_sz,
       pg_size_pretty(pg_total_relation_size('t_scattered'))  AS total;
Enter fullscreen mode Exit fullscreen mode 
  main  |  fsm  |  vm  | table_sz | total
--------+-------+------+----------+--------
 143 MB | 48 kB | 8192 bytes | 143 MB | 165 MB
Enter fullscreen mode Exit fullscreen mode

pg_relation_size() gives you only the main fork by default. pg_table_size() adds TOAST and the other forks. pg_total_relation_size() adds indexes. When someone reports a size discrepancy, nine times out of ten they compared two different functions.

What the free space map actually stores

The FSM keeps one byte per heap page. One byte can't express 8192 possible values, so free space is bucketed at BLCKSZ/256 = 32 bytes with the default 8 kB block size. Everything it reports is approximate and rounded down.

Those bytes form a tree of FSM pages. Each FSM page holds a binary tree whose leaves map to individual heap pages, and every upper node stores the maximum of its children — that's why "find me a page with 200 bytes free" is a top-down descent instead of a scan. One FSM page covers roughly 4,000 heap pages, about 32 MB of heap, and three levels address any relation you'll realistically build. That's why the fork above is 48 kB against 143 MB of heap — a few hundredths of a percent.

Two details that matter operationally:

  • The FSM isn't WAL-logged. It's a hint. If it's stale after crash recovery or on a standby, an inserting backend that finds a page fuller than advertised just fixes the entry and moves on. Nothing corrupts — you only pay a wasted page visit.
  • Since PG 12, no FSM fork exists for heaps smaller than four pages. Tiny tables are probed directly. pg_freespace() on them returns nothing useful, which trips people up testing this on a 10-row table.

Reading it yourself: pg_freespacemap, not pageinspect

A slip that shows up everywhere: pg_freespace() ships in the pg_freespacemap contrib extension, not pageinspect. Pageinspect gives you heap_page_items() and friends; the FSM reader is its own extension.

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;

SELECT * FROM pg_freespace('t_scattered') ORDER BY blkno LIMIT 5;
Enter fullscreen mode Exit fullscreen mode 
 blkno | avail
-------+-------
     0 |  7168
     1 |  7168
     2 |  7136
     3 |  7168
     4 |  7136
(5 rows)
Enter fullscreen mode Exit fullscreen mode

Every value is a multiple of 32. There's also a two-argument form, pg_freespace('t_scattered', 42), for a single block. By default both are restricted to superusers and roles with the privileges of pg_stat_scan_tables.

The per-page view is noise on a big table. Bucket it:

SELECT CASE
         WHEN avail = 0            THEN 'full'
         WHEN avail < 200          THEN '< 200 B'
         WHEN avail < 2048         THEN '200 B - 2 kB'
         WHEN avail < 6144         THEN '2 - 6 kB'
         ELSE                           '> 6 kB (nearly empty)'
       END AS bucket,
       count(*) AS pages,
       pg_size_pretty(sum(avail)::bigint) AS reusable
FROM pg_freespace('t_scattered')
GROUP BY 1 ORDER BY 2 DESC;
Enter fullscreen mode Exit fullscreen mode 
        bucket         | pages | reusable
-----------------------+-------+----------
 > 6 kB (nearly empty) | 18280 | 125 MB
 2 - 6 kB              |     2 | 9584 bytes
Enter fullscreen mode Exit fullscreen mode

125 MB of recycled space sitting inside a 143 MB file, invisible to df.

Why the table file only shrinks from the tail

Plain VACUUM can only truncate completely empty pages at the physical end of the file. Free space in the middle is recycled forever and never handed back to the OS — there's no compaction pass, no page migration. A single live row in the last page pins the entire file length, no matter how much dead space is scattered through the rest of it.

To truncate, VACUUM must hold an AccessExclusiveLock. It requests it conditionally with a timeout, gives up instead of blocking, and aborts the truncation scan the moment another backend starts waiting on that lock. On a table under constant traffic, that race is lost more often than won.

It also won't bother unless the freeable tail is at least 1,000 pages or one-sixteenth of the relation, whichever is smaller in practice.

One more thing for replicated setups: that truncation lock replays on standbys, where it can cancel running queries. If you run read-heavy hot standbys, ALTER TABLE ... SET (vacuum_truncate = off) or VACUUM (TRUNCATE off) (both PG 12+) is a legitimate trade — you give up occasional tail reclaim to stop the cancellations.

Making it shrink on purpose 

CREATE TABLE t_tail (id int PRIMARY KEY, payload text NOT NULL);
INSERT INTO t_tail SELECT g, repeat('x',100) FROM generate_series(1,1000000) g;

DELETE FROM t_tail WHERE id > 100000;   -- the physical tail
VACUUM VERBOSE t_tail;
Enter fullscreen mode Exit fullscreen mode 
INFO:  finished vacuuming "app.public.t_tail": index scans: 1
pages: 16453 removed, 1829 remain, 18282 scanned (100.00% of total)
tuples: 900000 removed, 100000 remain, 0 are dead but not yet removable
Enter fullscreen mode Exit fullscreen mode 
 pg_size_pretty
----------------
 14 MB
Enter fullscreen mode Exit fullscreen mode

Same row count deleted, same VACUUM — this time pages: 16453 removed. The only difference is physical position.

Now the payoff. Go back to the scattered table and insert 900,000 fresh rows:

INSERT INTO t_scattered
SELECT g, repeat('x',100) FROM generate_series(2000001, 2900000) g;

SELECT pg_size_pretty(pg_relation_size('t_scattered'));
Enter fullscreen mode Exit fullscreen mode 
 pg_size_pretty
----------------
 143 MB
Enter fullscreen mode Exit fullscreen mode

Zero growth. The FSM handed every one of those inserts a recycled page. That 143 MB was never wasted — it was working capital.

When bloat is real vs. just recycled space

The rule that matters: is the table going to re-fill?

Steady-state churn (delete a million rows a day, insert a million a day) reaches an equilibrium size. Leave it alone. Running VACUUM FULL on it just means you pay to rebuild the same file the application will re-inflate by Thursday.

A one-time 80% purge on a table that stays small forever is different. That space is stranded — reclaim it.

To tell them apart:

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
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 10;

SELECT * FROM pgstattuple('t_scattered');
Enter fullscreen mode Exit fullscreen mode 
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+-------------------+----------------+---------------------+------------+---------------
 149946368 |      100000 |  10500000 |          7.00 |                 0 |              0 |                0.00 |  131383040 |         87.63
Enter fullscreen mode Exit fullscreen mode

pgstattuple_approx uses the visibility map to skip all-visible pages, so it's much cheaper than a full pgstattuple() scan on a large table.

Watch the tuple-width trap: a page with 3 kB free is worthless if your rows are 4 kB wide. The FSM tracks free bytes, not free capacity for your specific row shape, and its top-down search will simply skip those pages — leaving every new wide row to land on a fresh one.

Four ways to actually get disk back

Option Lock Extra disk Online? When I pick it
VACUUM FULL ACCESS EXCLUSIVE, whole run Full second copy of table + indexes No Small tables, maintenance window. I almost never run it on anything over 50 GB during business hours
pg_repack Brief exclusive at start and end Roughly double Yes Large tables that must stay online. Needs a PK or non-partial unique index on NOT NULL columns
DROP/DETACH+DROP partition Brief, on the partition None Effectively yes Time-series purges — turns a bloat problem into a metadata operation
TRUNCATE ACCESS EXCLUSIVE, fast None No Whole-table wipes. New relfilenode, instant reclaim

For VACUUM FULL vs pg_repack: pick VACUUM FULL when downtime is acceptable and the table is small; pick pg_repack when it isn't and you have the disk headroom for a second copy.

Partitioning is the real fix for retention deletes. Dropping last quarter's partition returns the disk in milliseconds with no VACUUM in the loop at all.

The part everyone forgets: indexes

Index files bloat too, and they effectively never shrink. Empty B-tree pages get recycled through the index's own FSM on a later vacuum, but the file isn't truncated in the general case. REINDEX CONCURRENTLY (PG 12+) is the practical fix. VACUUM FULL rebuilds indexes as part of the table rewrite, which is sometimes the only reason to reach for it.

TOAST tables have their own heap, own FSM, own indexes. If your table has wide text or jsonb columns, pg_relation_size() is lying to you by omission — use pg_total_relation_size().

A real case: 380 GB that should've been 40 GB

A payments table that should have been 40 GB was 380 GB. Autovacuum was running, logging success, removing almost nothing. Every run reported a large number of tuples "dead but not yet removable."

The cause was a replication slot for a reporting standby decommissioned four months earlier. Nobody dropped it. It pinned the xmin horizon, so VACUUM couldn't remove any dead tuple newer than that frozen snapshot. DELETE wasn't leaking anything — the garbage collector was handcuffed. Dropping the slot let the next autovacuum cycle claw most of it back within the hour.

The same failure mode comes from a long-running analytics query, an idle-in-transaction connection from a pooler misconfiguration, or a stale prepared transaction nobody committed.

Checklist for "the disk is full"

  1. Check all four size measures. pg_relation_size(rel,'main'), 'fsm', 'vm', and pg_total_relation_size(rel). Confirm whether it's heap, TOAST, or indexes.
  2. Check the dead tuple ratio and last_autovacuum from pg_stat_user_tables. Autovacuum only fires at threshold 50 plus 0.2 × estimated rows, so a 500M-row table waits for 100M dead tuples unless you lower autovacuum_vacuum_scale_factor on that table specifically. I set it to 0.01 or 0.005 on the big churny ones.
  3. Check the xmin horizon. pg_stat_activity for long transactions and idle-in-transaction sessions, pg_replication_slots for abandoned slots (watch for a stale restart_lsn), and pg_prepared_xacts for orphaned 2PC.
  4. Then decide reuse vs. reclaim using the re-fill question above.

If you want a second opinion on autovacuum settings and slot hygiene across a whole cluster, MyDBA (https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-free-space-map-delete-not-shrinking) runs a free health check that flags this exact class of problem — entirely optional, the queries above get you to the same place on your own.

One line worth keeping: if autovacuum can't run, the FSM never learns about the free space, and your inserts keep extending the file past ground that was already yours.

Top comments (0)