VACUUM & Bloat

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.

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:

DROP TABLE IF EXISTS churn;
CREATE TABLE churn (id int primary key, v int);
INSERT INTO churn SELECT g, g FROM generate_series(1, 5000) g;
SELECT pg_relation_size('churn') AS bytes_before;

UPDATE churn SET v = v + 1;              -- every row gets a new version
SELECT pg_relation_size('churn') AS bytes_after;

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 on its own — it's a maintenance command that can't share a statement with anything else:

VACUUM churn;

Now check the size:

SELECT pg_relation_size('churn') AS bytes_after_vacuum;   -- still ~360 KB

Same size. VACUUM found the 5,000 dead tuples and recorded their slots in the table's free space map so future inserts and updates can reuse them — but it left the file exactly as big as it was. That's the design, not a bug. Prove why it's the right one: churn every row again and the file doesn't grow, because the new versions drop straight into the space the last round freed:

UPDATE churn SET v = v + 1;                 -- 5,000 more dead versions…
SELECT pg_relation_size('churn') AS bytes;  -- …but the file stays the same size

So a healthy table isn't one with no dead tuples — it's one where vacuum reclaims them as fast as churn creates them, holding the live-to-dead ratio flat. Bloat only spirals when dead tuples pile up faster than vacuum can reclaim them.

VACUUM FULL: the one that actually shrinks

To truly return space to the operating system you need VACUUM FULL, which rewrites the entire table into a fresh, compact file (again, on its own):

VACUUM FULL churn;

SELECT pg_relation_size('churn') AS bytes_after_full;   -- back to ~188 KB

Back to its lean size — every dead version gone, the file rebuilt from scratch.

The cleanup horizon (and the long-transaction trap)

VACUUM can only remove a dead tuple once no running snapshot could still need it. Recall the snapshot xmin from the MVCC lesson — the oldest transaction still running. Postgres tracks the smallest xmin across every backend (each one is visible as backend_xmin in pg_stat_activity), and that becomes the cleanup horizon. A dead tuple is removable only if the transaction that deleted it committed before that horizon; otherwise some still-open snapshot might legitimately still see it.

Here's the trap: one long-open transaction pins the horizon in the past — and because its snapshot could need any row that has died since it began, it blocks cleanup across the whole instance (every database in the cluster), not just its own table. Dead tuples pile up everywhere while one connection sits idle.

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, unrelated job. Remember from MVCC that visibility compares transaction ids — but ids are only 32 bits (~4.2 billion), and Postgres treats the id space as a circle: for any transaction, roughly half of it is "in the past" and half "in the future." That works until an id gets old enough that ~2 billion newer transactions have happened — at which point it would flip from looking past to looking future, and a row you inserted years ago would suddenly become invisible. Catastrophe.

The fix is freezing: VACUUM marks very old, still-live tuples as frozen — a flag in the tuple header that means "committed and visible to everyone, forever," so their actual xmin stops mattering and can safely be recycled. Every table tracks how far behind it is with relfrozenxid; age() turns that into "how many transactions since this table was fully frozen":

SELECT relname,
     age(relfrozenxid) AS xids_since_frozen   -- autovacuum forces a freeze near 200 million
FROM pg_class WHERE relname = 'churn';

Autovacuum launches a freeze pass automatically as that age approaches autovacuum_freeze_max_age (200 million by default). If freezing somehow falls ~2 billion behind — usually because autovacuum was disabled or perpetually blocked — Postgres protects your data by refusing new writes until you vacuum. It's rare, but it's the reason "just turn autovacuum off, it's slowing us down" is a career-limiting move.

Your turn

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

SELECT age(relfrozenxid) AS xids_since_frozen
FROM pg_class
WHERE relname = 'churn';

SELECT count(*) AS count FROM churn;