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
WHEREclause 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).
Connections
- Uses
- ACIDB-TreeClustered IndexDeadlockDeclarative Query LanguageDocument ModelFailoverFull-Text SearchInverted IndexIsolation LevelLeader-Based ReplicationLost UpdateMVCCMaterialized ViewMulti-Leader ReplicationRead CommittedRelational ModelReplication LogSecondary IndexSerializabilitySerializable Snapshot IsolationSnapshot IsolationSynchronous vs Asynchronous ReplicationVertical ScalingWrite-Ahead LogXA Transactions