MVCC, Dead Tuples, and VACUUM in PostgreSQL

Everything in this module (isolation levels, non-blocking reads, snapshots) rests on one mechanism: multi-version concurrency control. MVCC is why a SELECT never waits for a writer, why Repeatable Read can show a stable snapshot, and why Postgres accumulates the bloat we discussed in the indexing module. Understanding it ties the whole concurrency story together and explains a category of production problems that otherwise look like magic.

This is part 23 and the last of the concurrency module in the PostgreSQL Masterclass, following SELECT FOR UPDATE.

The core idea: never overwrite in place

Most people picture an update as changing a row's value in place. Postgres doesn't do that. When you update a row, it writes a brand new version of the row and marks the old version as expired. When you delete a row, it marks the existing version as expired without removing it. The old versions stick around.

Each row version carries hidden system columns, xmin and xmax, recording the transaction that created it and the transaction that expired it. A transaction sees a given version only if it was created by a transaction visible to its snapshot and not yet expired as of that snapshot. That visibility check, run against every row version, is the entire basis of isolation.

Why reads never block writes

Now the earlier claim makes sense. When a writer updates a row, it creates a new version but leaves the old one in place. A concurrent reader with an earlier snapshot simply sees the old version, which is still there, untouched. The reader doesn't wait, because the data it needs still exists. The writer doesn't wait for the reader, because it's writing a separate new version.

This is the big advantage of MVCC over lock-based concurrency: readers and writers coexist without blocking. The price is that those old versions accumulate and have to be cleaned up, which is where dead tuples and VACUUM enter.

Dead tuples and their cost

A row version that's expired and no longer visible to any running transaction is a dead tuple. It's just wasted space, but until it's cleaned up it has real costs:

• It takes disk in the table and in every index.
• Queries read past it. A sequential scan or index scan still has to skip dead tuples, so a table full of them scans slower even though the live data is small.
• It's the bloat from the maintenance article, and the reason a heavily updated table can grow far beyond the size its live rows justify.

A table where every row is updated frequently generates dead tuples constantly. Left unmanaged, it degrades steadily.

VACUUM: reclaiming dead tuples

VACUUM is the cleanup process. It scans for dead tuples that no running transaction can still see and marks their space reusable, so future inserts and updates fill it instead of growing the table. It also updates the visibility map that makes index-only scans possible, and does other bookkeeping.

Regular VACUUM doesn't lock the table against reads and writes, so it runs safely alongside normal traffic. It usually doesn't shrink the file on disk; it makes space reusable internally, which is what you want for steady state. Reclaiming disk back to the OS needs VACUUM FULL or pg_repack, as covered earlier.

Autovacuum, and the long-transaction trap

You rarely run VACUUM by hand because autovacuum does it automatically, triggering when a table has accumulated enough dead tuples. For most workloads it just works. Two things break it, and both cause real incidents.

The first is a very write-heavy table where autovacuum can't keep pace with the default settings. The fix is tuning autovacuum to run more aggressively on that specific table, which we cover in the operations module.

The second is more insidious: a long-running or idle-in-transaction transaction. VACUUM can only remove a dead tuple if no transaction can still see it. An old transaction that's been open for an hour holds a snapshot that might still need those old versions, so VACUUM cannot clean anything newer than that transaction's start, across the whole database. One forgotten transaction (a stuck connection, an abandoned BEGIN, a crashed worker holding a transaction open) blocks cleanup everywhere, and dead tuples pile up until disk fills. This is why the "keep transactions short" rule from the start of the module matters so much, and why monitoring for idle in transaction sessions is worth doing:

SELECT pid, state, age(now(), xact_start) AS txn_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Any session sitting idle in transaction for a long time is a threat to vacuuming, and idle_in_transaction_session_timeout can automatically terminate them.

Transaction ID wraparound, briefly

There's one more reason VACUUM is not optional. Transaction IDs are a finite 32-bit number, and VACUUM "freezes" old rows to keep them visible as that counter wraps around. If autovacuum is prevented from running long enough, a database can approach wraparound, at which point Postgres forces increasingly urgent measures to protect data. You'll likely never hit this if autovacuum is healthy, but it's the ultimate reason you can't just turn vacuuming off, and it's another argument against transactions that stay open for days.

MVCC is the elegant core of PostgreSQL's concurrency: keep old versions around so readers and writers never block, and clean up what's no longer visible. The whole module builds on it. Isolation is a visibility rule over row versions, locks coordinate writers to the same version, and VACUUM is the housekeeping that keeps the version pile from burying you. Keep transactions short, keep autovacuum healthy, and the machinery stays invisible, which is exactly how you want it.

That completes the transactions and concurrency module. Next, we open the advanced-features module with JSONB, PostgreSQL's answer to storing and querying semi-structured data.