Lesson 9 · Internals & data structures

VACUUM & bloat

The clean-up MVCC can't live without — and the classic "why is my table huge?"

Your win: explain why MVCC requires VACUUM, what VACUUM does, and why autovacuum matters for a write-heavy table like yours.

MVCC's leftovers

Every UPDATE/DELETE leaves a dead tuple — an old version no transaction can see any more (Lesson 6). Left alone, dead tuples accumulate: the table's files grow, pages fill with corpses, and scans read more to find live rows. That's bloat.¹

What VACUUM does

Four jobs (1) Reclaim dead-tuple space for reuse within the table (plain VACUUM; VACUUM FULL rewrites the table to return space to the OS but takes an exclusive lock). (2) Update the visibility map (which pages are all-visible → enables index-only scans, Part 3). (3) Refresh planner statistics (ANALYZE, Part 4). (4) Freeze old transaction ids to prevent txid wraparound.

Autovacuum runs VACUUM automatically once a table's dead-tuple count crosses a threshold — so you usually don't run it by hand, but you must not disable it on a churning table.

A related optimisation: HOT updates

If an UPDATE changes no indexed column and the new tuple fits on the same page, Postgres does a Heap-Only Tuple update: it chains the new version on the page and skips touching the indexes — cheaper, and it lets space be reused without a full VACUUM.²

Anchor — why this matters for you Your tables churn constantly: every soft-delete UPDATE … SET deleted_at = now() and every ON CONFLICT DO UPDATE upsert creates a dead tuple. Without autovacuum, emails / conversation would bloat and slow down. Bonus: a soft-delete that touches only deleted_at (not an indexed column) is a prime HOT candidate — index bloat avoided.

Interview gotcha "I deleted a million rows but the table is still huge on disk." Right — plain DELETE + VACUUM makes the space reusable, it doesn't shrink the file. Only VACUUM FULL (or pg_repack) returns space to the OS, at the cost of a heavy lock.

PostgreSQL — Routine Vacuuming + HOT updates

The operational chapter on VACUUM/autovacuum/freezing, and the storage note on Heap-Only Tuples.

→ postgresql.org/docs — Routine Vacuuming
→ The Internals of PostgreSQL — HOT

Check yourself (from memory)

Q1. VACUUM exists to…

It reclaims dead-tuple space (plus updates the visibility map, stats, and freezes old xids). Plain VACUUM makes space reusable, not smaller-on-disk.

Q2. Dead tuples are created by…

MVCC writes a new version on every UPDATE/DELETE, leaving the old one dead. SELECTs create none.

Q3. Autovacuum is triggered by…

It fires when dead tuples cross a per-table threshold — crucial for churning tables like ours.

Why does MVCC require VACUUM, and what does VACUUM do?

recall, then click to reveal

MVCC never overwrites — every UPDATE/DELETE leaves DEAD tuple versions, which consume space and slow scans ("bloat"). VACUUM reclaims dead-tuple space for reuse within the table (VACUUM FULL rewrites to return space to the OS but locks the table), updates the visibility map (enabling index-only scans), refreshes planner statistics (ANALYZE), and freezes old transaction ids to prevent wraparound. AUTOVACUUM runs it once dead tuples cross a threshold — essential for our soft-delete + upsert churn.

🎓 That's Part 2 — the internals You can now explain how Postgres stores rows (pages/tuples), the big idea (MVCC), how big values live (TOAST), how commits survive crashes (WAL), and MVCC's clean-up (VACUUM). Part 3 builds straight on this: indexes — the side structures that let Postgres skip the sequential scan.

Ready for Part 3 (indexes), or a mixed quiz across Lessons 5–9 first? Ask me.

1. PostgreSQL — Routine Vacuuming.

2. PostgreSQL — Heap-Only Tuples (HOT).