MVCC: How PostgreSQL Manages Concurrency

How can PostgreSQL let many transactions read and write the same table at once without readers blocking writers or writers blocking readers? The answer is MVCC — Multi-Version Concurrency Control. Instead of overwriting a row in place, PostgreSQL keeps multiple versions of it, and each transaction sees the version appropriate to its snapshot. This is the engine behind the isolation levels from the previous lesson.

Under MVCC, an UPDATE does not modify the existing row; it writes a new version and marks the old one as expired. A DELETE marks a row as expired without immediately removing it. Readers continue to see the old version until their snapshot no longer needs it. The dramatic consequence is that reads never block writes and writes never block reads — only two writers to the same row contend.

The trade-off is that obsolete row versions, called dead tuples, accumulate and must be cleaned up, which is the job of VACUUM. Understanding MVCC explains both PostgreSQL's excellent read/write concurrency and its characteristic operational concerns — table bloat and the need for vacuuming — that later lessons address in depth.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.