MVCC in PostgreSQL

What is it: Multi-Version Concurrency Control maintains multiple versions of each row simultaneously. Readers see a consistent snapshot without blocking writers; writers proceed without blocking readers.

Why: Without MVCC, readers need shared locks (preventing writers), and writers need exclusive locks (preventing readers). Under high concurrency, this serializes all access. MVCC eliminates reader-writer contention entirely.

Row versioning: Every heap tuple has hidden system columns:

  • xmin: transaction ID (XID) that created this version
  • xmax: XID that deleted/updated this version (0 if current)

On UPDATE: old row gets xmax = current_xid, new row gets xmin = current_xid. Both coexist in the heap.

graph LR v1["Row version 1<br/>xmin=100, xmax=105<br/>(created by T100, superseded by T105)"] v2["Row version 2<br/>xmin=105, xmax=0<br/>(created by T105, still current)"] v1 -->|"UPDATE by T105"| v2 r1["Reader with snapshot at T102"] -.->|"sees v1 (xmax=105 not yet committed at T102)"| v1 r2["Reader with snapshot at T110"] -.->|"sees v2 (v1's xmax=105 IS committed by now)"| v2

Two readers, running concurrently against the same row, correctly see different versions — neither blocks the other, and neither blocks T105's update. This is the entire payoff of MVCC: the visibility rule (xmin committed-and-before-my-snapshot, xmax not-yet-committed-or-zero) is just arithmetic on these two columns, checked per-reader, with no lock contention between readers and writers at all.

Visibility rules: a row version is visible to transaction T if xmin is committed and predates T's snapshot, AND xmax is 0 or not yet committed at T's snapshot time.

Vacuum: dead tuples accumulate because MVCC never overwrites in place. VACUUM reclaims dead tuple space. autovacuum runs automatically. Without it: table bloat, index bloat, XID wraparound (catastrophic — PostgreSQL enters read-only mode to prevent data corruption after ~2 billion transactions).

Trade-offs: MVCC causes write amplification (every UPDATE writes a new version) and requires vacuum. In exchange, read throughput is maximized — no read locks ever needed.

Real-world usage: ValkeyDB uses a single-threaded model to avoid the complexity of MVCC. Redis avoids it by never allowing concurrent access to the same key — the event loop serializes all commands.

Common pitfall

A long-running transaction (an analytics query left open for an hour, or an application bug that never commits) holds its snapshot open the entire time — every dead tuple created after that snapshot started must be kept around, because that old transaction might still need to see it, even after VACUUM runs. This is the most common real-world cause of table bloat despite autovacuum running normally: check pg_stat_activity for long-idle-in-transaction sessions before assuming vacuum itself is misconfigured.