PostgreSQL MVCC & Write-Ahead Log

Each PostgreSQL row has hidden system columns: xmin (transaction ID that created it) and xmax (transaction ID that deleted it). A query's snapshot sees rows where xmin is at most the snapshot transaction ID and xmax is null or greater than the snapshot ID. This means old row versions coexist with new ones on the same heap page. Readers never block writers; writers never block readers. The trade-off is storage bloat from multiple row versions.

Before any data page is modified, the change is recorded in WAL — a sequential append-only file. On crash, PostgreSQL replays WAL from the last checkpoint to reconstruct any in-flight transactions. Sequential WAL writes are orders of magnitude faster than random data page writes, so most of PostgreSQL's write performance comes from WAL efficiency. WAL segments (16MB by default) are archived for point-in-time recovery.

MVCC's cost is dead row versions — old versions that no transaction can see anymore. VACUUM reclaims this space by marking dead tuples as reusable and updating the visibility map. AUTOVACUUM runs VACUUM automatically based on a dead-tuple threshold (default: 20% of rows). Failure to vacuum causes table bloat, index bloat, and eventually transaction ID wraparound — a critical failure mode that freezes the database.

PostgreSQL primary servers stream WAL records to replica servers in near-real-time. Replicas apply WAL records to stay in sync, typically with less than 100ms lag. Replicas can serve read-only queries, distributing read load. Failover promotes a replica to primary — with synchronous_standby_names, you can guarantee zero data loss promotion. Tools like Patroni automate this failover with etcd-based leader election.

PostgreSQL supports four isolation levels: Read Uncommitted (same as Read Committed in Postgres), Read Committed (default — each statement sees a fresh snapshot), Repeatable Read (snapshot taken at transaction start, prevents non-repeatable reads), and Serializable (SSI algorithm detects and aborts serialization anomalies). Serializable Snapshot Isolation in Postgres provides true serializability without locking, unique among major databases.