WAL, MVCC & Crash Recovery

PostgreSQL survives crashes and serves concurrent users by keeping two under-the-hood promises at the same time: committed changes are recoverable, and readers can see a consistent snapshot while writers keep working. The two core mechanisms are WAL — the write-ahead log — and MVCC — multiversion concurrency control.

The browser can honestly show row-version archaeology with xmin, xmax, and ctid. It cannot honestly pull the plug, replay crash recovery, or run two sessions at once, so those parts stay structural: real concepts, no fake demos.

WAL: write the log before trusting the page

22.1 showed heap pages and index pages. A write changes those pages, but PostgreSQL does not rely on every changed data page reaching storage immediately. Instead, it writes a record of the change to the write-ahead log first.

The recovery idea is simple:

1. A transaction changes rows in memory.
2. PostgreSQL records enough information in WAL to redo those changes.
3. A commit is considered durable only according to what the commit setting waits for.
4. If the server crashes before changed data pages are all written, recovery starts from a checkpoint and replays WAL records after it.

A checkpoint is a recovery landmark: PostgreSQL flushes dirty pages and writes a checkpoint record so crash recovery has a recent starting point. Checkpoints do not replace WAL. They limit how much WAL must be replayed after a crash.

That is the boundary for this lesson: WAL and checkpoints are not visible as trustworthy browser drama. A command existing is not proof that a power loss was survived. What we can inspect live is the other half of the story: the row versions WAL helps protect.

Keep the responsibilities separate. WAL is about durability: can PostgreSQL reconstruct committed work after a crash? MVCC is about visibility: which row version should this statement see while other transactions are changing data? Checkpoints are about recovery distance: how far back must PostgreSQL start replaying WAL? They cooperate, but they are not interchangeable features.

MVCC: one logical row, several physical versions

MVCC lets PostgreSQL keep multiple versions of a row so statements can read a snapshot while writes create newer versions. Ordinary readers and writers often pass each other instead of blocking, but writers still conflict with writers, and explicit locks still behave like Section 12 taught.

PostgreSQL exposes a few system columns that let you see the shape:

• xmin — the transaction ID that inserted this row version.
• xmax — the transaction ID that deleted this row version, or 0 for an undeleted visible version in this simple case.
• ctid — the physical location of this row version inside the table, written as (page,item).

Create one order and inspect those columns:

CREATE TABLE mvcc_orders (
  order_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  amount numeric(8,2) NOT NULL
);
 
INSERT INTO mvcc_orders (status, amount)
VALUES ('placed', 42.00);
 
SELECT order_id,
       status,
       amount,
       ctid::text AS ctid,
       xmin::text AS xmin,
       xmax::text AS xmax
FROM mvcc_orders
ORDER BY order_id;

CREATE TABLE
INSERT 0 1
 order_id | status | amount | ctid  | xmin | xmax
----------+--------+--------+-------+------+------
        1 | placed |  42.00 | (0,1) | 756  | 0
(1 row)

The exact transaction ID is not the lesson; the pattern is. This visible row version was inserted by the transaction shown in xmin, has not been deleted (xmax = 0), and currently lives at the physical location shown in ctid. Do not use ctid as an application key. It is a physical row-version location, and updates can move it.

UPDATE creates a new visible version

Section 8's safety ritual still applies: rehearse the row before you mutate it. Then update and return the system columns from the row version PostgreSQL makes visible after the update:

SELECT order_id, status, amount
FROM mvcc_orders
WHERE order_id = 1;
 
UPDATE mvcc_orders
SET status = 'paid'
WHERE order_id = 1
RETURNING order_id,
          status,
          amount,
          ctid::text AS ctid,
          xmin::text AS xmin,
          xmax::text AS xmax;
 
SELECT order_id,
       status,
       amount,
       ctid::text AS ctid,
       xmin::text AS xmin,
       xmax::text AS xmax
FROM mvcc_orders
ORDER BY order_id;

 order_id | status | amount
----------+--------+--------
        1 | placed |  42.00
(1 row)
 order_id | status | amount | ctid  | xmin | xmax
----------+--------+--------+-------+------+------
        1 | paid   |  42.00 | (0,2) | 757  | 0
(1 row)
 order_id | status | amount | ctid  | xmin | xmax
----------+--------+--------+-------+------+------
        1 | paid   |  42.00 | (0,2) | 757  | 0
(1 row)

The order did not become a new business entity; it is still order_id = 1. Under the hood, though, the visible row version changed. The new version has a new xmin and a new ctid. The older version is no longer visible to this statement's snapshot, but it may still matter to an older snapshot in another transaction.

That is the core MVCC trade: writes create versions instead of overwriting every reader's world in place. Snapshots decide which version a statement is allowed to see.

DELETE marks a version; VACUUM eventually cleans

DELETE also gets a rehearsal. This time the returned row version shows a nonzero xmax, because the delete transaction marks that version as deleted:

SELECT order_id, status, amount
FROM mvcc_orders
WHERE order_id = 1;
 
DELETE FROM mvcc_orders
WHERE order_id = 1
RETURNING order_id,
          status,
          amount,
          ctid::text AS ctid,
          xmin::text AS xmin,
          xmax::text AS xmax;
 
SELECT count(*) AS visible_orders
FROM mvcc_orders;

 order_id | status | amount
----------+--------+--------
        1 | paid   |  42.00
(1 row)
 order_id | status | amount | ctid  | xmin | xmax
----------+--------+--------+-------+------+------
        1 | paid   |  42.00 | (0,2) | 757  | 758
(1 row)
 visible_orders
----------------
              0
(1 row)

The table now has zero visible orders. But "not visible" is not the same as "all physical traces are immediately gone." PostgreSQL cannot remove old row versions while some snapshot might still need them. VACUUM exists to clean up row versions that are no longer visible to any active transaction and to make table space reusable. Autovacuum does that maintenance in normal databases; manual VACUUM is a maintenance command, not a correctness ritual after every delete.

synchronous_commit: what COMMIT waits for

The live lab can show the setting, not the durability consequence. synchronous_commit controls what a commit waits for before reporting success. With the usual setting, PostgreSQL waits for the local WAL record to be flushed. With off, commit can return before that local flush, trading lower commit wait for the risk that a crash loses very recent committed transactions.

SET LOCAL changes the setting only inside the current transaction:

SHOW synchronous_commit;
 
BEGIN;
SET LOCAL synchronous_commit = off;
SHOW synchronous_commit;
COMMIT;
 
SHOW synchronous_commit;

 synchronous_commit
--------------------
 on
(1 row)
BEGIN
SET
 synchronous_commit
--------------------
 off
(1 row)
COMMIT
 synchronous_commit
--------------------
 on
(1 row)

That is syntax and scope, not a crash test. The durable lesson is the trade: waiting for WAL flush makes commits safer against local crash; not waiting can reduce commit latency but widens the tiny window where a reported commit may be lost if the server crashes.

WAL explains how committed changes can be recovered; MVCC explains how snapshots and row versions coexist while the system runs. Next, 22.4 zooms out from one PostgreSQL server to replicas, partitions, and distributed systems.