Why it matters

MVCC is why 'readers don't block writers' in modern DBs. Understanding it makes concurrent behavior predictable and helps tune vacuum/GC.

Advertisement

The architecture

Each row has version metadata: xmin (created by tx X), xmax (deleted by tx Y).

Snapshot: at transaction start, capture set of committed transactions. Row visible if xmin in snapshot and xmax not in snapshot.

MVCC mechanismMulti-version rowsxmin + xmaxSnapshot per txconsistent viewVacuum/GCreclaim dead versionsLong-running transactions block vacuum; can cause bloat
MVCC visibility rules.
Advertisement

How it works end to end

Read: iterate rows, check visibility per snapshot. Fast enough that reads don't need locks.

Write: create new row version. Old version still visible to older transactions.

Vacuum (Postgres) or purge (Oracle): background process removes dead versions no transaction can see.