🔄 MVCC & Vacuum
How PostgreSQL Manages Concurrent Access Without Read Locks
PostgreSQL uses Multi-Version Concurrency Control (MVCC) so readers never block writers and writers never block readers. Every row has hidden system columns — xmin (the transaction that created it) and xmax (the transaction that deleted or updated it). An UPDATE doesn't modify in-place: it marks the old row dead and inserts a new version. The tradeoff? Dead tuples accumulate and need cleanup — that's what VACUUM does.
📦 Table Heap: Tuple Lifecycle
Every row version lives in the heap. INSERT creates, UPDATE copies + kills, DELETE kills.
💡 Why Not Update In-Place?
If PostgreSQL modified rows directly, concurrent readers would see half-written data. Instead, the old version stays untouched (readers on older snapshots still see it), and the new version is written elsewhere. This is the fundamental MVCC tradeoff: no read locks, but dead tuples pile up. MySQL InnoDB takes a different approach — it updates in-place and stores old versions in an undo log.
👁️ Snapshot Isolation: Who Sees What?
Each transaction gets a snapshot at BEGIN. It can only see rows committed before its snapshot.
Two sessions will operate concurrently. Watch how snapshot isolation ensures each sees a consistent view.
💀 Dead Tuples & Table Bloat
Updates and deletes leave dead tuples behind. Without VACUUM, your table grows forever.
🧹 Regular VACUUM
Scans the table and marks dead tuples asreusable — future INSERTs can reclaim that space. But it never shrinks the file. A table that grew to 10 GB stays 10 GB on disk, even if most rows are deleted. Runs concurrently with reads and writes.
🔨 VACUUM FULL
Rewrites the entire table into a new file, reclaiming all dead space. The file actually shrinks. But it takes anexclusive lock — no reads or writes during the entire operation. Use only when regular VACUUM isn't enough and you can afford downtime.
🧹 VACUUM In Action
Watch VACUUM scan the heap, identify dead tuples, and mark space as reusable.
⚙️ Autovacuum Thresholds
Autovacuum triggers when: dead tuples ≥ threshold + scale_factor × total rows
Autovacuum will trigger when 2,050 dead tuples accumulate in this table.
💣 Transaction ID Wraparound
PostgreSQL uses 32-bit transaction IDs. At ~4 billion, they wrap around — and disaster strikes.
❄️ What Is Freezing?
When VACUUM freezes a tuple, it replaces itsxminwithFrozenTransactionId (2), a special value meaning "visible to everyone, forever." This resets the tuple's age to 0. Without freezing, old tuples would eventually have their xmin wrap around past the current txn ID — making committed data suddenly invisible. PostgreSQL will force a shutdownrather than let this happen, refusing all new transactions until you manually run VACUUM FREEZE.