Write-Ahead Logging (WAL).

🔌The scenario

Your Postgres box loses power in the middle of a big UPDATE. You reboot, terrified. But every committed transaction is intact, and the half-finished one vanished cleanly. No corruption. How?

The answer is WAL — the single most important durability trick in databases.

🧠The core idea

Rule: never change the data files first. Write your intent to an append-only log first, flush THAT to disk, and only then touch the real pages.

"Write-ahead" = the log write happens ahead of the data write.

Commit sequence:

1. Append the change to the WAL (a sequential append — fast).
2. fsync() the WAL so it's truly on disk.
3. Return "committed" to the client.
4. Later, lazily, flush the modified pages to the real data files.

Die after step 2 but before step 4? Recovery replays the WAL and rebuilds the pages. Die before step 2? The txn was never durable, so losing it is correct.

⚡Why it's fast, not just safe

Data pages are scattered across disk → random I/O (slow). The WAL is one file you only append to → sequential I/O (fast). WAL turns many random writes into one sequential write + deferred batched flushes. Durability AND throughput.

That's why fsync config matters in Postgres:

• synchronous_commit = on → wait for WAL fsync before ack (safe)
• synchronous_commit = off → ack early, risk losing the last few hundred ms on a crash (faster, small data-loss window)

🔁It's everywhere, under different names

• Postgres: WAL
• MySQL/InnoDB: redo log
• SQLite: WAL mode / rollback journal
• Filesystems (ext4, NTFS): journaling
• Raft / Kafka: the replicated log IS the WAL

WAL is also what powers point-in-time recovery and streaming replication — replicas just replay the primary's WAL stream.

🕳️Gotchas people hit

• The WAL grows until a CHECKPOINT flushes dirty pages and recycles old segments. Misconfigured checkpoints → disk fills or brutal recovery times.
• fsync=off is NOT the same as synchronous_commit=off. The first disables durability entirely (corruption on crash); the second only relaxes commit acking. Never set fsync=off in prod.
• A slow WAL fsync becomes your commit-latency floor. Put the WAL on fast storage.

🎯Your exercise

In psql run: SHOW synchronous_commit; and SHOW wal_level;

Then reason it out: if you set synchronous_commit = off and pull the plug 100ms after a commit acks, what exactly do you lose — and what do you still NOT lose? (Hint: think durability vs consistency. A crash never corrupts you here; you just lose recently-acked-but-not-yet-flushed commits.)