MVCC Demystified: How Postgres Handles Concurrent Transactions

Imagine two users hit your application at the same time. One is reading a report that aggregates every order from the past month. The other is inserting a new order right now. Should the report include that new order? Should the insert wait until the report finishes reading?

PostgreSQL's answer is: neither. Both transactions run at full speed, and each one sees a consistent snapshot of the database as it existed when the transaction started. This is Multi-Version Concurrency Control — MVCC — and it's one of the most important ideas in modern database design.

The core idea: keep old versions around

In a locking-based system, readers block writers and writers block readers. MVCC takes a different approach: instead of preventing conflicts, it avoids them entirely by keeping multiple versions of each row.

This means readers never block writers, and writers never block readers. The only conflict that requires waiting is when two transactions try to update the same row at the same time.

How Postgres stores versions

Every row in Postgres has hidden system columns that control visibility:

• xmin — the transaction ID that created this row version
• xmax — the transaction ID that expired (deleted or updated) this row version, or 0 if the row is still current

SELECT xmin, xmax, * FROM users WHERE id = 1;

When you run an UPDATE, Postgres marks the old row's xmax with the current transaction ID and inserts a brand-new row with the updated values and a fresh xmin. Both versions exist on disk simultaneously.

Snapshot visibility rules

When a transaction reads data, it checks each row version against a simple set of rules to decide if it's visible:

A row version is visible to transaction T if the xmin transaction committed before T's snapshot was taken, and the xmax is either empty (row hasn't been deleted) or was set by a transaction that hadn't committed when T's snapshot was taken.

This is the heart of MVCC. Each transaction carries a snapshot — a record of which transactions were committed at the moment it began — and uses it to filter out row versions it shouldn't see.

The cleanup problem: VACUUM

The trade-off of keeping old versions around is that they accumulate. Once no active transaction can possibly need an old row version, it becomes dead tuple — wasted space on disk.

Postgres runs autovacuum in the background to clean up dead tuples. It scans tables, identifies row versions that no active transaction needs, and marks that space as reusable.

What you should take away

MVCC trades space for concurrency. By keeping old row versions, Postgres lets readers and writers work simultaneously without blocking each other. The cost is disk space and the need for vacuum.

Every row carries its own visibility metadata. The xmin and xmax system columns, combined with transaction snapshots, determine which version of a row each transaction sees.

Autovacuum is not optional. It's the maintenance process that makes MVCC sustainable. Understand it, monitor it, and tune it for your workload.

This article accompanies the video MVCC Demystified on I Know Database.