NotesSystem

PostgreSQL

Also: Postgres · PG

The open-source relational database whose concurrency model is MVCC with versions stored in the table itself, offering Read Committed by default, snapshot isolation as Repeatable Read, and true serializability via SSI.

Also in: Transactions & Isolation › Mvcc · Serializability › In the real world

Design choices

  • MVCC in the heap: every UPDATE writes a new tuple stamped with xmin/xmax; VACUUM reclaims dead tuples and freezes old ones before 32-bit transaction IDs wrap around.
  • Read Committed by default: a new snapshot per statement; an UPDATE that waited for a row lock re-checks its WHERE clause against the new row version.
  • Snapshot Isolation as Repeatable Read: first-committer-wins aborts the second writer of a row ("could not serialize access due to concurrent update", SQLSTATE 40001). Read Uncommitted behaves like Read Committed.
  • Serializable Snapshot Isolation since 9.1 (2011): non-blocking SIRead predicate locks track read-write dependencies and abort one transaction of a dangerous structure.
  • Write-Ahead Log with full-page images, shipped to replicas for physical replication.

Notable trade-offs

  • Heap versioning makes rollback free but causes table and index bloat; churn-heavy tables need tuned autovacuum, and long-open transactions block cleanup.
  • Serializable needs a client retry loop for 40001 errors, and sequential scans take relation-level predicate locks, raising the false-abort rate.
  • Replicas of managed clusters may not preserve snapshot isolation (Jepsen found long forks on RDS Multi-AZ clusters in 2025).