MVCC: How PostgreSQL Handles Concurrency

Multi-Version Concurrency Control

PostgreSQL doesn't use traditional read locks. Instead, every row update creates a new version of the row. Old versions stick around until no transaction can possibly need them, then VACUUM cleans them up. Result: readers don't block writers, writers don't block readers. This is the deep reason PostgreSQL scales reads so well.

What MVCC means in practice

Two readers querying the same table never wait on each other.
A reader sees the snapshot as of when its transaction (or query, depending on isolation) began — not affected by concurrent writers.
Two writers updating the same row block each other (one waits, then sees the result of the other).
Dead row versions accumulate; VACUUM cleans them periodically.

The dead-tuple cost

Every UPDATE in PostgreSQL is "create a new version + mark the old one dead." Heavily-updated tables accumulate dead tuples until VACUUM reclaims the space. In extreme cases this is bloat; we'll see how to monitor it in the Operations track.

Code

MVCC in action·sql

-- Session A
BEGIN;
SELECT name FROM users WHERE id = 7;
-- Returns 'Alice'

-- Session B (concurrent)
BEGIN;
UPDATE users SET name = 'Alicia' WHERE id = 7;
COMMIT;

-- Session A again
SELECT name FROM users WHERE id = 7;
-- Returns 'Alice' (READ COMMITTED) — the snapshot of the SELECT
-- Or, in REPEATABLE READ, definitely 'Alice' until A commits.
COMMIT;

-- Now a fresh transaction
BEGIN;
SELECT name FROM users WHERE id = 7;
-- Returns 'Alicia'
COMMIT;

Inspect transaction visibility·sql

SELECT xmin, xmax, * FROM users WHERE id = 7;
-- xmin: the transaction that created this row version
-- xmax: the transaction that deleted/updated it (0 if still live)

MVCC: How PostgreSQL Handles Concurrency

Multi-Version Concurrency Control

What MVCC means in practice

The dead-tuple cost

Code

External links

Exercise

Progress

Comments 0