postgres://internals

VACUUM & Bloat

// the problem

MVCC's superpower — lock-free reads — has a bill attached. Every UPDATE writes a new row version and leaves the old one behind; every DELETE just marks a row dead. Nothing is erased in place. So who cleans up the corpses, and why does an idle transaction on the other side of the office make it impossible? Meet VACUUM.

Churn the table below and watch dead tuples pile up. Then run VACUUM and see what it does — and what it doesn't. Open a long-running transaction and watch cleanup stall.

fig.01 · dead tuples & vacuum1 slot = 1 tuple version
live
6
dead
0
reclaimable
0
held back
0
file slots
6
bloat
0%
1
2
3
4
5
6

update/delete → dead tuples pile up (bloat) · VACUUM frees them for reuse (file stays big) · VACUUM FULL rewrites and shrinks

Two things to notice. First, VACUUM turns dead slots into reusable ones but the file doesn't shrink — the space is handed back to the table for future rows, not to the operating system. Second, when a long transaction is open, dead tuples that died after it go held back: VACUUM can't touch them, because that old transaction's snapshot might still need to see them.

See bloat for real

Let's bloat an actual table. Create it, note its size, then update every row once and measure again:

sql · live postgresrun me first — then watch the size
⌘/ctrl + enter

The file roughly doubled — 5,000 live rows plus 5,000 dead versions, all on disk. That's bloat: space that's allocated but holds nothing useful.

VACUUM reclaims space; it doesn't give it back

Run VACUUM and check the size again:

sql · live postgreseditable — run it
⌘/ctrl + enter

Same size! VACUUM found the dead tuples and recorded their space in the free space map so future inserts and updates can reuse it — but it left the file as big as it was. For a table with steady churn that's exactly what you want: the bloat plateaus and the reused space keeps it from growing forever.

To actually return space to the OS you need VACUUM FULL, which rewrites the whole table compactly:

sql · live postgreseditable — run it
⌘/ctrl + enter

Now it's back to its lean size.

// gotcha · VACUUM FULL takes an exclusive lock

VACUUM FULL rewrites the table into a new file, which requires an ACCESS EXCLUSIVE lock — nothing can read or write the table while it runs, and it needs room for a second copy. On a large production table that's an outage. Prefer letting plain (auto)vacuum keep bloat flat; reach for tools like pg_repack when you truly must shrink online.

The cleanup horizon (and the long-transaction trap)

VACUUM can only remove a dead tuple once no running transaction could still see it. It computes an oldest-needed xid — the horizon — and keeps anything newer. A single long-open transaction pins that horizon in the past, so dead tuples across the whole instance (every database in the cluster) can't be cleaned up.

// gotcha · idle-in-transaction is the classic bloat bomb

An app that opens a transaction and forgets to commit — an idle in transaction connection — will silently block vacuuming everywhere. Bloat balloons, queries slow down, and the cause is a connection doing nothing. Set idle_in_transaction_session_timeout and watch pg_stat_activity for old xact_start times.

autovacuum does this for you

You rarely run VACUUM by hand. autovacuum wakes up when a table has accumulated enough dead tuples (by default, ~20% of the table) and vacuums it in the background. Tuning it — making it run more often on hot tables, not less — is one of the highest-impact things a Postgres operator does. A table that outpaces autovacuum is the usual story behind mysterious, ever-growing bloat.

Freezing and transaction-id wraparound

VACUUM has a second job. Transaction ids are 32-bit, so they wrap around after ~4 billion. To keep old rows visible forever, VACUUM freezes them — stamps them as “infinitely old” so their xmin no longer matters. If freezing falls too far behind, Postgres forces increasingly aggressive “anti-wraparound” vacuums and, in the worst case, stops accepting writes to protect your data. It's rare, but it's why freezing (and autovacuum keeping up) matters at scale.

// note · HOT updates avoid some of this

Recall from the B-tree lesson: if an UPDATE changes no indexed column and the new version fits on the same page, Postgres does a Heap-Only Tuple update — no index churn, and the dead version can be cleaned by lightweight HOT pruning without a full VACUUM. A good fillfactor leaves room for it.

Your turn

The churn table (from the first cell) has been bloated by heavy updates.

// challengewrite it yourself

The churn table is bloated. Rewrite it to return the wasted space to the OS, then report its size in bytes.

⌘/ctrl + enter
// challengewrite it yourself

Bloat is dead versions, not extra rows. Show that churn still has exactly 5000 live rows.

⌘/ctrl + enter

// what you now understand

  • 01Every UPDATE/DELETE leaves a dead tuple; MVCC never overwrites in place, so dead versions accumulate as bloat.
  • 02VACUUM marks dead-tuple space reusable via the free space map but does NOT shrink the file; steady churn makes bloat plateau.
  • 03VACUUM FULL rewrites the table to actually return space to the OS, but takes an ACCESS EXCLUSIVE lock — an outage on big tables.
  • 04VACUUM can only remove tuples older than the cleanup horizon; a long-open (idle-in-transaction) session pins it and blocks cleanup across the whole instance.
  • 05autovacuum runs vacuuming automatically on dead-tuple thresholds — tuning it to keep up is a core operational skill.
  • 06VACUUM also freezes old rows to prevent 32-bit transaction-id wraparound, which can otherwise halt writes.

// self-test

You run VACUUM on a badly bloated table, but `pg_relation_size` doesn't drop. Is something broken?

// self-test

Your database's bloat is growing and autovacuum seems to do nothing, even on tables being vacuumed. What should you check first?

// go deeper

nextWAL & Durability