Tied to Mission: Backend Depth — deepens the snapshot model from Lesson 2 into how Postgres physically stores row versions, and gives the "grows over time" symptom from the baseline assessment a precise mechanism.
An events table keeps 30 days of rows. A nightly job deletes everything older than that, so the number of live rows stays roughly constant. And yet the table's size on disk climbs every week, queries get slower, and no amount of deleting seems to help. You check: the deletes are running. The rows are gone from queries. So where is the space going?
The answer is the same fact that makes Postgres's isolation model work in Lesson 2: Postgres never modifies a row in place, and DELETE doesn't remove anything — not immediately, not by itself.
Postgres implements MVCC (multi-version concurrency control): every logical row can have several versions (tuples) in the table file at once, and each version is tagged with the transactions that created it and superseded it:
xmin — the transaction that created this version.xmax — the transaction that deleted or updated this version (0 = still current).An UPDATE inserts a brand-new version and sets xmax on the old one. A DELETE just sets xmax. The old version physically stays in its page. Whether a reader sees it depends on whether those transactions committed — which is exactly the visibility logic from Lesson 2, now stored per-tuple instead of in an application lock. A transaction that is still in flight must have its uncommitted changes invisible to everyone else, so its old versions have to keep existing until it's over.
Once every snapshot that could still see an old version is gone, that version is dead — invisible to every current and future transaction. Dead tuples are pure garbage in the heap, but nothing removes them as a side effect of the delete. They accumulate page after page, and that accumulation is bloat:
This is the "degrades over weeks" shape: live-row count flat, file size climbing, scans slowing.
The baseline assessment's signature — "degrades over hours, restart clears it" — is an in-memory leak: connection pool, threads, unreclaimed memory. Bloat is the opposite: it lives on disk, in the table file, so a restart does not clear it. If a table is bloated and you bounce the database, it comes back exactly as slow. Knowing which disease you have decides whether restarting helps at all.
VACUUM is the reaper. It scans the heap for dead tuples and marks their space as reusable in the free space map, so future inserts and updates can land there instead of growing the file. It also updates the visibility map, which is what makes index-only scans possible — a detail Lesson 1 mentioned and Lesson 9 will build on.
But notice what VACUUM does not do: it doesn't shrink the file back down. It frees space inside existing pages for reuse; the file on disk stays at its grown size until the freed pages are actually reused. If you want the file physically compacted, that's VACUUM FULL, which rewrites the whole table — and takes an exclusive lock, so you can't run it on a live service without a maintenance window.
Autovacuum is on by default. It wakes a worker when the number of dead tuples passes a threshold: threshold + scale_factor × row_count, which by default means roughly 20% of the table plus 50 dead tuples. So it keeps up with steady delete traffic. Bloat happens when autovacuum falls behind, and the classic causes are worth knowing by name:
BEGIN left open for hours can pin millions of tuples and stall reclamation entirely. This is the single most common cause of unbounded bloat in production.If an UPDATE doesn't change any indexed column, Postgres can chain the new version to the old one within the same page — a heap-only tuple (HOT) update — without inserting a new index entry. That's why updating a non-indexed column (say, a last_seen timestamp) is dramatically cheaper on index maintenance than touching an indexed column. It also means "UPDATE-heavy but index-bloat-free" workloads are the ones HOT updates cover.
-- dead tuples not yet vacuumed, and last vacuum time
SELECT n_dead_tup, last_vacuum, last_autovacuum, vacuum_count
FROM pg_stat_user_tables
WHERE relname = 'events';A persistently large n_dead_tup with a stale last_autovacuum is the bloat signature. For a percentage estimate, the pgstattuple extension reports the fraction of a table's pages that are dead.
On any disposable Postgres: create a small table, insert 100k rows, then run a loop of UPDATEs on a non-indexed column. Between iterations check pg_stat_user_tables (n_dead_tup) and the table size via pg_total_relation_size. Watch dead tuples climb, then run VACUUM and watch n_dead_tup drop to ~0 — and note that the file size barely changes until you reuse the space or run VACUUM FULL. Then open a transaction with BEGIN, run an UPDATE, leave the transaction open, and watch n_dead_tup refuse to drop — that's the long-transaction pin in action.
PostgreSQL's own docs are the authority here: the Concurrency Control chapter covers MVCC and visibility, and Routine Database Maintenance covers autovacuum and tuning. For the source-code-level picture of how versions and vacuum actually work, Hironobu Suzuki's The Internals of PostgreSQL has dedicated chapters on heap tuples and vacuum. Lesson 2's isolation cheat sheet is the right companion for the snapshot semantics that drive all of this.
Now that you know where bloat comes from, the natural next question is how Postgres decides a query's plan based on what it believes about your data — which is exactly Lesson 9.