PostgreSQL Concurrency Under the Hood: MVCC, Isolation Levels, and Row Locks

1. Multi-Version Concurrency Control (MVCC)

PostgreSQL never overwrites a row in place when you UPDATE it. Instead it writes a new row version (a new tuple) with updated xmin (the transaction ID that created it), and marks the old version's xmax with the transaction ID that superseded it. Both versions physically coexist on disk until vacuum reclaims the old one, which is what lets readers and writers proceed concurrently without blocking each other — a reader sees a consistent snapshot of the rows visible as of its transaction's start, regardless of concurrent writers mutating the same rows.

This is why PostgreSQL readers never block writers and writers never block readers under normal MVCC visibility rules — a stark contrast to lock-based concurrency models. The cost is paid elsewhere: every UPDATE is really an INSERT-plus-mark-dead, every DELETE just marks a tuple dead without removing it, and the table physically grows with every write until autovacuum's VACUUM process scans it and reclaims space occupied by tuples no longer visible to any active transaction.

This is also the mechanical root of table bloat discussed in the EXPLAIN ANALYZE article of this series: a table with heavy UPDATE/DELETE traffic and vacuum falling behind (because it's disabled, misconfigured, or blocked by a long-running transaction holding back the vacuum horizon) accumulates dead tuples that Seq Scans and even index scans must skip over, inflating both table size and query cost with rows that no live transaction can even see.

A long-running transaction is dangerous precisely because of this mechanism: as long as it's open, PostgreSQL cannot vacuum away any tuple version that transaction's snapshot might still need to see, even if that transaction never touches the table in question. A single idle-in-transaction connection left open for hours on a busy database can silently stall vacuum across the entire instance, which is why `idle_in_transaction_session_timeout` and monitoring `pg_stat_activity` for old transactions are standard operational practice.

2. Isolation Levels: Dirty Reads, Non-Repeatable Reads, and Phantom Reads

The SQL standard defines four isolation levels via which anomalies they prevent: Read Uncommitted (allows dirty reads — seeing another transaction's uncommitted changes), Read Committed (prevents dirty reads but allows non-repeatable reads — the same query run twice in one transaction can return different results if another transaction commits in between), Repeatable Read (prevents non-repeatable reads but standard-allows phantom reads — new rows matching a WHERE clause appearing on a re-query), and Serializable (prevents all of the above, behaving as if transactions ran one at a time).

PostgreSQL doesn't implement Read Uncommitted at all — it silently upgrades it to Read Committed, because MVCC makes true dirty reads architecturally impossible without deliberately breaking snapshot isolation. Read Committed is PostgreSQL's default: each individual statement within a transaction gets its own fresh snapshot, so two SELECTs in the same transaction can see different committed states if a concurrent transaction commits between them.