What deleting rows does to a PostgreSQL table
A table that shrinks in rows does not shrink on disk. Everyone who has run PostgreSQL for a while knows this in general. I wanted the actual numbers, because "VACUUM reclaims space" means something narrower than it sounds.
Short answer: I deleted every second row of a 2,000,000-row table. The table was 137 MB before, 137 MB after the DELETE, 137 MB after VACUUM, and 68 MB after VACUUM FULL.
Setup
PostgreSQL 18.6, default settings, autovacuum on. 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM. Sizes are pg_table_size. Times are wall clock for the statement, median of three runs, each on a freshly built table.
Deleting half the rows
| step | table size |
|---|---|
| 2M rows, after VACUUM ANALYZE | 137 MB |
after DELETE FROM b1 WHERE id % 2 = 0 | 137 MB |
after VACUUM b1 | 137 MB |
after VACUUM FULL b1 | 68 MB |
The DELETE marks rows dead. VACUUM makes their space reusable, so the next million inserts will fill those gaps instead of growing the file. It does not hand the space back to the operating system, with one exception: empty pages at the very end of the file get cut off. I deleted every second row, so every page kept half its rows and nothing at the end was empty.
VACUUM FULL writes a new copy of the table and drops the old one. That is why it gets the space back, and also why it takes a lock that blocks reads and writes while it runs.
DELETE or TRUNCATE to empty a table
| time | WAL written | |
|---|---|---|
DELETE FROM d1 (1M rows) | 3.5 s | 125 MB |
TRUNCATE d1 | 0.05 s | 44 kB |
DELETE touches and logs every row. TRUNCATE gives the table a new, empty file. The difference grows with the table, and the WAL column matters if you have replicas or WAL archiving: 125 MB of WAL is 125 MB that has to travel.
TRUNCATE is not a drop-in replacement. It takes an ACCESS EXCLUSIVE lock, it does not fire ON DELETE triggers, and it refuses to run on a table that other tables reference with foreign keys unless you truncate those too or add CASCADE.
Updates and fillfactor
An update in PostgreSQL writes a new version of the row. If the new version fits on the same page and no indexed column changed, it can skip the indexes entirely. That is a HOT update.
I updated a non-indexed column on all 200,000 rows of a table, three times, once with the default fillfactor of 100 and once with 90, which leaves 10% of each page empty at load time.
| HOT updates | table after | index on k after | |
|---|---|---|---|
| fillfactor 100 | 64 of 600,000 | 61 MB | 10.4 MB |
| fillfactor 90 | 127,353 of 600,000 | 47 MB | 9.0 MB |
With full pages almost nothing could stay on its page. With 10% free, about a fifth of the updates did, and the table ended 24% smaller.
What surprised me
The HOT numbers were lower than I expected for fillfactor 90. Three full passes over every row use up the free space faster than a real workload that updates a few rows at a time, and autovacuum probably had no chance to clean up between passes. On a table where updates are spread out, the share of HOT updates should be higher. I did not measure that here.
What I did not test
- Autovacuum doing its work between updates.
pg_repackor the new REPACK command in PostgreSQL 19 as alternatives to VACUUM FULL.- Tables with TOAST data, where some of the space lives in a separate table.
- Other hardware. Compare the ratios, not the seconds.
Reproduce it
run2.py and results2.json. Sections 5, 6 and 11 of the script.