MVCC, Dead Tuples & Autovacuum.

🎬The scene

Friday. Your Postgres events table holds 2M live rows but the disk says 41 GB. Queries that used to take 4ms now take 900ms. You run:

DELETE FROM events WHERE created_at < now() - interval '30 days';
-- DELETE 18000000

Disk usage after: 41 GB. Unchanged. Queries: still slow. What?

🧬What actually happened

Postgres never overwrites or erases a row in place. Every UPDATE and DELETE is a versioning operation:

• UPDATE = write a NEW row version, mark the old one dead
• DELETE = just mark the row dead

That's MVCC. Each row version carries xmin (the transaction that created it) and xmax (the one that killed it). Your query sees a version only if xmin committed before your snapshot and xmax hasn't. This is why readers never block writers — the old version is still physically sitting there for anyone whose snapshot still needs it.

So your DELETE didn't remove 18M rows. It wrote 18M tombstones. The bytes stay until VACUUM reclaims them.

🔎Confirm it in one query

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

 events | 2000000 | 18000000 | 2026-08-11 03:12

Two million live rows, eighteen million dead ones, and autovacuum hasn't run in two weeks. That ratio is your latency: a sequential scan reads every dead tuple too, then throws it away. You're paying I/O for garbage.

🧹What VACUUM does — and doesn't

VACUUM marks dead space reusable by future inserts in the same table. It does NOT return disk to the OS (except trailing empty pages). To actually shrink the file you need VACUUM FULL — which takes an ACCESS EXCLUSIVE lock and rewrites the whole table. On a 41 GB production table that's an outage. Use pg_repack instead: same result, no long lock.

Normal VACUUM is the one you want running constantly. It's cheap and online.

⚠️The three things practitioners get wrong

1. "Autovacuum handles it." Its default trigger is

threshold + 0.2 × n_live_tup.
On a 50M-row table that's 10M dead tuples before it lifts a finger. Big, churny tables need a per-table override:

 ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);

2. A single long-running transaction blocks ALL cleanup. If an idle-in-transaction session or a 6-hour analytics query holds an old snapshot, VACUUM cannot remove any version newer than it — even in unrelated tables. Check:

 SELECT pid, state, now()-xact_start AS age, query
 FROM pg_stat_activity
 WHERE state <> 'idle' ORDER BY xact_start LIMIT 5;

Nine times out of ten, "vacuum isn't working" is really "someone left a transaction open."

3. DELETE-then-VACUUM is the expensive way to expire data. Deleting 18M rows writes 18M tombstones plus WAL plus index churn. Partition by month and DROP TABLE events_2026_06 — it's an instant metadata operation, no dead tuples at all.

💡 Bonus: this also explains why count(*) is slow in Postgres. It can't keep one true count — different transactions legitimately see different numbers of rows.

🏋️Your exercise

On any Postgres you have (local is fine):

CREATE TABLE t AS SELECT g AS id, repeat('x',200) AS pad FROM generate_series(1,200000) g;
SELECT pg_size_pretty(pg_total_relation_size('t'));
UPDATE t SET pad = repeat('y',200);
SELECT pg_size_pretty(pg_total_relation_size('t'));

The table roughly doubles from an UPDATE that changed no row count. Now run VACUUM t; and check the size again — then run a second identical UPDATE and watch it stay flat instead of doubling again.

Question to sit with: why did the size stay flat the second time, even though VACUUM never gave the disk back?