04 — ACID, Transactions, and MVCC: How Postgres Stays Correct

"Transactions just work" was my ACID mental model, which made every concurrency surprise feel like a glitch. Writing it down pinned the two mechanisms everything else is built on: Multi-Version Concurrency Control gives each transaction its own consistent snapshot of the data so readers never block writers, and the Write-Ahead Log records every change before it lands so a crash becomes a replayable ledger, not a disaster. [1][2] Once those two clicked, the rest of the DBA playbook stopped feeling like a list of chores and started looking like consequences of this design.

ACID, recast as engineering commitments

ACID is the contract, but the letters are not equally obvious. The two I had to think hardest about were Isolation and Durability, because they're where the real machinery lives [3]:

• Atomicity — a transaction is all-or-nothing. Either every statement commits or none does. No half-applied writes.
• Consistency — a transaction moves the database from one valid state to another, respecting all constraints and triggers.
• Isolation — concurrent transactions appear to execute serially; one doesn't see another's intermediate, uncommitted results. The strictest level is Serializable, but the default in Postgres is Read Committed.
• Durability — once committed, the change survives a crash. This is what the WAL buys.

Atomicity and consistency are mostly enforced by transaction boundaries and constraints. Isolation and durability are where Postgres spends its real engineering, via MVCC and the WAL respectively.

Transactions: the all-or-nothing boundary

A transaction is a group of statements wrapped in BEGIN ... COMMIT (or ROLLBACK to abort) [4]. Inside it, either everything sticks or nothing does:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second UPDATE fails, the first is undone — the money doesn't vanish. Savepoints let me carve out sub-boundaries: SAVEPOINT pre_validation lets a later error roll back to that point without aborting the whole transaction. The isolation level (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE) controls how much this transaction sees of others running alongside it.

MVCC: readers don't block writers

The model I had to internalize is Multi-Version Concurrency Control [1]. When a transaction updates a row, Postgres doesn't overwrite the row — it writes a new version and marks the old one with the transaction ID that created it and the one that superseded it. Every transaction gets a snapshot: it can see only the row versions that were committed before the transaction (or statement, under Read Committed) started.

The payoff is that readers never block writers, and writers never block readers. A long analytic query can run for minutes against a snapshot while inserts and updates stream in, and neither side waits. The cost is the dead row versions — old versions no longer visible to any transaction — which is exactly why VACUUM exists: to reclaim that space and update the planner's statistics [1].

The WAL: durability as a replayable ledger

Durability is bought by the Write-Ahead Log (WAL) [2]. The rule is simple and absolute: before a changed data page is written to disk, the log record describing that change is written and flushed to the WAL. The WAL is an append-only sequence of records — every insert, update, delete, and schema change, in the order it happened.

Why "write-ahead": if Postgres wrote data pages first and the log second, a crash between the two would leave changed data on disk with no record of how it got there — unrecoverable. By logging first, the recovery story becomes clean:

On restart after a crash, Postgres replays the WAL forward (redoing committed changes that hadn't reached the data files) and rolls back any transactions that never committed. The result is a consistent state with no committed change lost [2]. The same WAL is what streaming replication ships to standbys — the standby applies the same log and stays in sync.

Query processing: how a statement becomes a plan

Tucked alongside these mechanisms is query processing — the path a SQL statement takes from text to result [7]. Parsing checks syntax; the planner/optimizer picks an execution strategy (which join order, which index, sequential scan vs. index scan) based on table statistics; the executor runs the plan and returns rows. The planner's quality is why EXPLAIN and statistics matter so much — a bad plan turns a millisecond query into a minute. The notes on indexes and EXPLAIN come back to this machinery.

How I use this

Three habits fall out of these mechanisms. First, I wrap multi-statement logical units in explicit BEGIN/COMMIT so the all-or-nothing guarantee is mine, not accidental. Second, when I see slow reads under heavy writes, I check the isolation level and the autovacuum settings — MVCC dead rows bloat tables and indexes if vacuuming falls behind, and the symptom is exactly creeping read latency. Third, I treat the WAL as the source of truth for durability and recovery: a backup without the archived WAL is a backup that can only restore to one point in time, which is often not enough. The combination — snapshot isolation plus a replayable log — is what makes Postgres feel trustworthy under concurrency and crash.

References

[1] PostgreSQL Global Development Group, "Introduction to MVCC," 2024. [Online]. Available: https://www.postgresql.org/docs/current/mvcc-intro.html

[2] PostgreSQL Global Development Group, "Reliability and the Write-Ahead Log," 2024. [Online]. Available: https://www.postgresql.org/docs/current/wal-intro.html

[3] Retool, "What is an ACID compliant database?," 2023. [Online]. Available: https://retool.com/blog/whats-an-acid-compliant-database/

[4] PostgreSQL Global Development Group, "Transactions," 2024. [Online]. Available: https://www.postgresql.org/docs/current/tutorial-transactions.html

[5] Wikipedia, "Multiversion concurrency control," 2024. [Online]. Available: https://en.wikipedia.org/wiki/Multiversion_concurrency_control

[6] Hevo Data, "Working With Postgres WAL Made Easy 101," 2023. [Online]. Available: https://hevodata.com/learn/working-with-postgres-wal/

[7] InterDB, "Query Processing in PostgreSQL," 2024. [Online]. Available: https://www.interdb.jp/pg/pgsql03.html