The WAL Log
Concept. Every update is three writes, in order. The database first appends a tiny diff, the old and new value, to the log, then updates the value in a page held in the RAM buffer, and flushes that 64 MB page to disk later. RAM is volatile, so the log append is what makes the change durable now. The log is durable before the page ever reaches disk, the write-ahead rule, and it makes a crash survivable: the log alone holds enough to rebuild any past version, put a change back, or take it away.
Intuition. Say A is updated from 19 to 22.8. First a small row, A, old 19, new 22.8, appends to the log and is durable at once. Then A's page updates in the RAM buffer. The 64 MB page is big and slow, so it flushes to disk later. If a crash hits in that gap, the log already holds the change.
Follow one update through all three.
The Lifecycle of an Update
Every update is three writes to three places. It appends the diff to the log, updates the value in a page in the RAM buffer, and later flushes that 64 MB page to disk. You already know paging from storage: the buffer holds fixed-size pages, and a page moves to disk as one write. Follow all three below.
Figure 1. One update, three writes in time order. ① Append the diff to the log (violet), durable at once; ② update A's page in the RAM buffer (red) in place, fast but volatile; ③ later, flush the whole 64 MB page to disk (green) as one write, start to end. Steps ① and ② happen now and the flush is deferred, so the log is always durable before the page reaches disk.
Append now, update now, flush later: those three actions are the whole mechanism, and the log always leads.
Inside the WAL Row
The log is append-only. Every write appends one row holding the old value and the new value, and a COMMIT or ABORT record marks where a transaction ends. Each row is tiny, about 40 bytes: a header (log position, type, length) 12 B, the transaction id 4 B, the location 8 B, and the old and new value 8 B each. Nothing is ever overwritten; the log only grows.
Figure 2. The log after two updates: A from 19 to 22.8 and B from 23 to 27.6. Each is one row with the old value (orange) and the new value (green), so the same log carries what it takes to put a change back or take it away. A commit record closes the transaction. These are the rows undo runs against when a transaction aborts, and redo when a crash hits.
Every change is now an old-to-new row. Three questions follow: how do you get any past version from it, why is writing on every change not slow, and why must the log reach disk before the data page?
Any Version, on Demand
Change Tracking opened with a problem: show the exact state at any past moment, without copying the whole database. The WAL log already holds it. Because every row keeps both the old value and the new, you replay the log to any state: stop replaying at a past moment and you have the version that was true then. Forward, you redo the new values; backward, you undo the old ones. The log is the version history, one change at a time.
Figure 3. The log read top to bottom is a full change history. To see the state at any past moment, stop replaying the rows there: just after T1, the version is A=22.8, B=27.6. Because each row carries both values, you can move either way, redoing new values forward or undoing old values back.
Cheap for Two Reasons: Small and Sequential
First, a WAL row is tiny, about 40 bytes against a 64 MB data page. Second, it only appends, one sequential write at the tail of the log, while the data page needs a random I/O to wherever its row lives. The numbers are lopsided. Flushing a 64 MB page to SSD takes about 13 ms (64 MB at 5 GB/s). The WAL flushes in small 64 KB pages, about 13 µs each, roughly a thousand times less; and a row landing in battery-backed NVRAM is durable at RAM speed, about 100 ns, a hundred thousand times less than the page flush.
Figure 4. Without the log (grey), each change is a random I/O to its own scattered 64 MB data page, about 13 ms. With the log (violet), every change is a ~40 byte row appended to one growing file, one fast sequential stream: a 64 KB WAL page costs about 13 µs (~1,000× less), and a row in battery-backed NVRAM is durable in about 100 ns (~100,000× less). Small and sequential beats big and random.
Hardware makes the log even cheaper. Battery-backed NVRAM writes at RAM speed yet survives power loss, so the quick note is durable the instant it lands. Replicating the log to RAM on three or more machines means one crash loses nothing. And sequential SSD appends, the one operation all of this hardware does fastest, are exactly what WAL turns every random update into.
Why the Log Goes First
The update and the flush happen at different times, so a crash can strike between them, and which write reaches disk first decides everything. The log goes first: a crash before the page flushes still leaves the change in the durable log, ready to re-apply. Flip the order and the data changed with no record of why, unrecoverable. That is the write-ahead rule. Surviving an actual crash, replaying the log and undoing the unfinished work, is the next two pages.
Figure 5. Data first (red): if the crash hits before the log row lands, the data changed with no row to replay, an inconsistency recovery cannot fix. Log first (green), the WAL order: a crash before the data page moves leaves the change sitting in the durable log, ready to re-apply. Log-before-data is the only order that keeps the data file recoverable.
Next
Undo for Aborts → One log gives you versions, cheap writes, and a safe order. Its fourth payoff is surviving a crash. Next: undo reads the old values to reverse a transaction that aborts, so it leaves no trace. Redo, its mirror, arrives with the crash.