DEV Community

Philip McClarence
Philip McClarence

Posted on

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

TL;DR

  • DELETE doesn't remove anything. It stamps xmax on the tuple header and moves on.
  • VACUUM turns dead tuples into reusable space and records the per-page availability in the relation's _fsm fork.
  • A heap file can only shrink from the tail. Free space in the middle gets recycled, never returned.
  • Truncation needs more than 1000 trailing empty pages (or 1/16 of the relation), plus an AccessExclusiveLock it will wait about five seconds for. On a busy table those conditions rarely line up.
  • Recycled space is usually the correct outcome. Rewriting a 200 GB table to reclaim 12 GB you'll re-fill in a week is a bad trade.

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

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

Why doesn't DELETE free disk space in Postgres?

Disk alert on a primary at 78% and climbing. The events table was the obvious offender, so I deleted about 10 million rows older than the retention cutoff, watched the transaction commit, and ran pg_relation_size() expecting a number that started with a smaller digit.

Same number. Byte for byte.

The on-call engineer had already escalated by then, on the reasonable theory that a delete which frees no space is a delete that didn't happen. It happened. The space just hadn't gone anywhere yet, and after VACUUM it still wouldn't, because of where in the file those rows lived.

One clarification up front: pg_freespace() ships in the pg_freespacemap contrib extension, not pageinspect. Easy to conflate since you install both from the same contrib package and use them for the same kind of forensics.

Where the space actually goes

DELETE writes the deleting transaction id into the tuple header's t_xmax field. The row stays physically in its 8kB page, occupying its bytes, until VACUUM or opportunistic page pruning reclaims it, and only once no live snapshot could still need to see it.

Let's watch it. Build a table:

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

INSERT INTO events (created_at, payload)
SELECT now() - (g || ' seconds')::interval, md5(g::text)
FROM generate_series(1, 1000000) g;

VACUUM ANALYZE events;

SELECT pg_size_pretty(pg_relation_size('events')) AS main,
       pg_relation_size('events') / 8192 AS pages;
Enter fullscreen mode Exit fullscreen mode 
  main   | pages
---------+-------
 86 MB   | 11040
Enter fullscreen mode Exit fullscreen mode

Now look inside page 0 with pageinspect:

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, lp_flags, lp_len, t_ctid, t_xmin, t_xmax
FROM heap_page_items(get_raw_page('events', 0)) LIMIT 3;
Enter fullscreen mode Exit fullscreen mode 
 lp | lp_flags | lp_len | t_ctid | t_xmin | t_xmax
----+----------+--------+--------+--------+--------
  1 |        1 |     69 | (0,1)  | 894102 |      0
  2 |        1 |     69 | (0,2)  | 894102 |      0
  3 |        1 |     69 | (0,3)  | 894102 |      0
Enter fullscreen mode Exit fullscreen mode

Delete every tenth row and look again:

DELETE FROM events WHERE id % 10 = 0;   -- 100000 rows
Enter fullscreen mode Exit fullscreen mode 
 lp | lp_flags | lp_len | t_ctid | t_xmin | t_xmax
----+----------+--------+--------+--------+--------
  1 |        1 |     69 | (0,1)  | 894102 |      0
 10 |        1 |     69 | (0,10) | 894102 | 894331
Enter fullscreen mode Exit fullscreen mode

lp_flags = 1 still, lp_len still 69, t_ctid still points at itself. The row is intact, just stamped. After VACUUM events; the line pointer goes to LP_UNUSED:

 lp | lp_flags | lp_len | t_ctid | t_xmin | t_xmax
----+----------+--------+--------+--------+--------
 10 |        0 |      0 |        |        |
Enter fullscreen mode Exit fullscreen mode

And the file size:

  main   | pages
---------+-------
 86 MB   | 11040
Enter fullscreen mode Exit fullscreen mode

Unchanged, which is correct. That slot on the page is free space now — it just hasn't left the file.

The FSM is a real file on disk

Every heap and index relation except hash indexes carries a Free Space Map as a separate fork next to the main data file:

SELECT pg_relation_filepath('events');
Enter fullscreen mode Exit fullscreen mode 
 base/16384/24576
Enter fullscreen mode Exit fullscreen mode 
$ ls -la $PGDATA/base/16384/24576*
-rw------- 1 postgres postgres 90439680 Aug  2 03:12 24576
-rw------- 1 postgres postgres    40960 Aug  2 03:12 24576_fsm
-rw------- 1 postgres postgres     8192 Aug  2 03:12 24576_vm
Enter fullscreen mode Exit fullscreen mode

The FSM stores one byte per heap page in a three-level tree, so it costs a rounding error relative to the relation it describes. Size the forks from SQL:

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,
       pg_size_pretty(pg_table_size('events'))           AS table_size,
       pg_size_pretty(pg_total_relation_size('events'))  AS total;
Enter fullscreen mode Exit fullscreen mode 
 main  |  fsm  |     vm     | table_size | total
-------+-------+------------+------------+--------
 86 MB | 40 kB | 8192 bytes | 86 MB      | 108 MB
Enter fullscreen mode Exit fullscreen mode

Three functions, three meanings, and people mix them up constantly. pg_relation_size is one fork of one relation. pg_table_size adds the FSM, visibility map and the TOAST relation. pg_total_relation_size adds indexes. TOAST tables are separate relations with their own FSM and their own independent bloat, so when a table with wide text or jsonb columns refuses to shrink, check it directly:

SELECT reltoastrelid::regclass,
       pg_size_pretty(pg_relation_size(reltoastrelid))
FROM pg_class WHERE oid = 'events'::regclass;
Enter fullscreen mode Exit fullscreen mode

Two other properties worth internalising. FSM updates are not WAL-logged, so after a crash the map can be stale; Postgres treats it as a hint and later vacuums correct it. And since PG12 there's no FSM fork at all for relations under four pages, which are handled with an in-memory local map instead. pg_freespace on a tiny table returning nothing is not a bug.

Looking inside it with pg_freespacemap 

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
Enter fullscreen mode Exit fullscreen mode

Total reusable space:

SELECT pg_size_pretty(sum(avail)::bigint) AS free,
       count(*) FILTER (WHERE avail > 0)  AS pages_with_space,
       count(*)                           AS pages
FROM pg_freespace('events');
Enter fullscreen mode Exit fullscreen mode 
  free   | pages_with_space | pages
---------+------------------+-------
 8113 kB |            11039 | 11040
Enter fullscreen mode Exit fullscreen mode

There's the 100k deleted rows: 8 MB of space, spread across essentially every page in the table. Histogram:

SELECT (avail / 512) * 512 AS bucket, count(*)
FROM pg_freespace('events') GROUP BY 1 ORDER BY 1;
Enter fullscreen mode Exit fullscreen mode 
 bucket | count
--------+-------
      0 |     1
    512 | 10982
   1024 |    57
Enter fullscreen mode Exit fullscreen mode

The avail values always come back as multiples of 32. With an 8kB block size the FSM rounds free space down to BLCKSZ/256, because 256 categories are packed into one byte. It's approximate by design.

Run the same query against a B-tree index and you'll get a very different shape: for B-trees the FSM tracks whole recycled pages, so you see 0 or a full page and nothing between.

Why the file only shrinks from the tail

A relation file is an array of 8kB blocks, and every index entry and every ctid addresses a row by (block number, line pointer). You cannot punch a hole in the middle and slide the remaining blocks down without rewriting every index in the database that points into that table. So vacuum's only available shrink move is lopping off empty pages at the physical end, and only if they're completely empty.

Which explains the two delete patterns:

Scattered deletes (WHERE id % 10 = 0, or "delete the cancelled orders"): free space everywhere, tail still occupied, zero shrink. All recycled.

Tail deletes on an append-ordered table: the physical end goes empty and truncation becomes possible.

Same table, same VACUUM events command, wildly different outcome depending only on where the deleted rows happened to live. Continuing the demo:

DELETE FROM events WHERE id > 700000;   -- 270000 rows, the newest ones
VACUUM (VERBOSE) events;
Enter fullscreen mode Exit fullscreen mode 
INFO:  table "events": truncated 11040 to 7728 pages
Enter fullscreen mode Exit fullscreen mode 
  main   | pages
---------+-------
 60 MB   |  7728
Enter fullscreen mode Exit fullscreen mode

The scattered delete earlier and this one removed comparable row counts. Only this one moved the needle on pg_relation_size, because these rows sat at the physical end of the file instead of scattered through the middle.

The truncation rules nobody puts in the release notes

VACUUM doesn't even attempt truncation unless the freeable trailing pages exceed 1000 pages or one-sixteenth of the relation, whichever is smaller. Below that it leaves the tail alone. On a small table that's a low bar; on a 50 GB table it means tens of thousands of contiguous empty pages at the end before truncation is even considered.

If the threshold is met, vacuum truncate in Postgres needs an AccessExclusiveLock. It requests it conditionally for a bounded window (five seconds in current sources) and gives up rather than blocking. Worse for your purposes: it re-checks during the truncation and aborts partway through if another backend queues behind the lock. On a table with steady query traffic, this is why the shrink you saw in staging never happens in production. Each subsequent vacuum tries again — which also means a table too busy to truncate at 2pm might truncate cleanly overnight when load drops.

Those constants live in vacuumlazy.c and are implementation detail. Treat them as "current behaviour," not contract — they've shifted across major versions before.

You can watch it live:

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;
Enter fullscreen mode Exit fullscreen mode 
  pid  | relid  |          phase          | heap_blks_scanned | heap_blks_total
-------+--------+-------------------------+--------------------+-----------------
 20114 | events | truncating last block   |              11040 |           11040
Enter fullscreen mode Exit fullscreen mode

When VACUUM can't free anything at all

The other failure mode: you vacuum, and n_dead_tup doesn't move. VACUUM VERBOSE will tell you why:

INFO:  vacuuming "public.events"
INFO:  table "events": found 0 removable, 4192883 nonremovable row versions
DETAIL:  3910442 dead row versions cannot be removed yet, oldest xmin: 894102
Enter fullscreen mode Exit fullscreen mode

Dead but not removable means something in the cluster is holding the snapshot horizon back. Find it:

SELECT 'backend' AS src, pid::text AS id, backend_xmin::text AS xmin,
       now() - xact_start AS age, state, left(query, 60) AS q
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name, xmin::text, NULL, active::text, restart_lsn::text
FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
UNION ALL
SELECT 'prepared', gid, transaction::text, now() - prepared, NULL, NULL
FROM pg_prepared_xacts
ORDER BY xmin::text::bigint;
Enter fullscreen mode Exit fullscreen mode

Usual suspects, in the order I find them: an idle in transaction session from an ORM that forgot to commit, an inactive logical replication slot nobody dropped after decommissioning a consumer, hot_standby_feedback = on with a replica running a 40-minute report, and once, memorably, a prepared transaction from a two-phase commit that had been abandoned for eleven days.

Bloat, or working set?

The decision rule I use: compare the free space the FSM reports against the table's insert rate.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple_approx('events');
Enter fullscreen mode Exit fullscreen mode

pgstattuple scans the whole table and gives exact live/dead counts and free_percent; pgstattuple_approx samples and skips all-visible pages, which is the only version I'll run on anything over 50 GB during the day.

If the table writes 2 GB a day and the FSM shows 6 GB free, that's three days of runway and you should do nothing. Reuse is free. Rewriting is not. If the table shrank permanently (one-time purge, an archived tenant, a retention policy change), the space is stranded and worth reclaiming. Watching n_dead_tup and free space trend over weeks rather than eyeballing it once is the actual skill here; whatever you monitor with is fine, MyDBA is what I happen to use for the dead-tuple trendlines.

Actually getting the disk back: VACUUM FULL vs pg_repack

Method Lock Extra disk Online? Prereqs When I reach for it
TRUNCATE ACCESS EXCLUSIVE, brief none no all rows go Staging, or genuine full wipes
DROP partition ACCESS EXCLUSIVE, brief, scoped to partition none effectively partitioned table Always, if I planned ahead
VACUUM FULL ACCESS EXCLUSIVE, whole rewrite ~2x table + indexes no none Maintenance windows only
CLUSTER ACCESS EXCLUSIVE, whole rewrite ~2x no an index to order by When I want physical ordering too
pg_repack brief exclusive at start and swap ~2x yes PK or unique index on NOT NULL cols Default choice on production
pg_squeeze brief at swap ~2x yes logical decoding, extension installed When I want it automated by policy

VACUUM FULL and CLUSTER rewrite into a new relfilenode and hand the space back cleanly, at the cost of blocking every reader and writer for the duration, with no way to cancel gracefully once the rewrite is underway. I almost never run VACUUM FULL on production during business hours. A 400 GB table takes long enough that "brief interruption" becomes an incident report.

Partition dropping is the only strategy that makes purges genuinely free, because a DROP just unlinks a relfilenode. No vacuum, no truncation threshold, no lock negotiation, and the rest of the table is untouched. If you're deleting by date range on a schedule and you aren't partitioned by date range, that's the actual fix.

The knobs worth knowing

vacuum_truncate = off (per table, PG12+). Truncation takes an AccessExclusiveLock that gets replayed on standbys, where it cancels running read queries. If you have a reporting replica and a table that truncates regularly, this is the fix — the tradeoff is you keep the trailing empty pages forever:

ALTER TABLE events SET (vacuum_truncate = off);
Enter fullscreen mode Exit fullscreen mode

autovacuum_vacuum_scale_factor on huge tables. The default 0.2 means a 500M-row table waits for 100M dead tuples. Set it per table to something like 0.01, or use autovacuum_vacuum_max_threshold where available.

fillfactor below 100 on update-heavy tables, to leave room for HOT updates on the same page — less bloat generated in the first place.

autovacuum_naptime does nothing for you when the horizon is stuck. Vacuum running more often just means failing more often, faster.

What I'd do with your alert

  1. Measure all three sizes: pg_relation_size, pg_table_size, pg_total_relation_size, plus the TOAST relation separately.
  2. Check the xmin horizon with the union query above. If something's holding it, nothing else matters until it's cleared.
  3. Run the pg_freespace histogram. Is the free space at the tail or scattered?
  4. Project reuse: free bytes divided by daily insert bytes. Under a week of runway, stop here.
  5. Otherwise: pg_repack if it has a primary key, partition redesign if this is going to recur.

Honest closing opinion: for most tables the right answer is to leave the space alone. Bloat monitoring turns into busywork the moment you start treating "free space exists" as a defect. The number that matters is whether the table's footprint is growing over a month, not whether pg_relation_size dropped after last night's purge.

Top comments (0)