Write-Ahead Logging: How Databases Don't Lose Your Data

I used to think database durability was some kind of magic. You write a row, you get a "success" back, and somehow that data survives even if someone yanks the power cord a second later. How does that even work? Turns out the answer is stupidly simple once you see it: write it down before you touch anything else. That's it. That's the whole trick. It's called write-ahead logging, or WAL, and it's one of those ideas that's so obvious in hindsight that you kind of feel dumb for not thinking of it yourself.

Let's actually dig into it.

The problem we're solving

Say you're updating a row in a table. Under the hood, that update probably touches a B-tree page sitting somewhere in memory, and eventually that page needs to get flushed to disk. Writing a full page to disk isn't instant, and it isn't atomic in any meaningful sense either. If your process crashes, or the machine loses power, or the disk controller has a bad day right in the middle of that write, you can end up with a page that's half old data and half new data. Garbage, basically. Corrupted.

Now imagine that page held index entries for a hundred different rows. One crash at the wrong moment and your index is toast, even for rows that had nothing to do with the transaction that was running.

So the naive approach of "just write your changes directly to the data files" is a disaster waiting to happen. You need some way to recover to a known good state after a crash, no matter when the crash happens.

The trick: log first, apply later

Here's the idea. Before you touch the actual data pages, you write a small, simple record describing the change you're about to make, to a separate append-only file. Something like:

LSN: 4821
TXN: 102
TABLE: accounts
ACTION: UPDATE
ROW: id=57
OLD: balance=500
NEW: balance=300

That LSN up there is a "log sequence number," basically a monotonically increasing counter so the log entries have a strict order. You'll see why that matters in a bit.

This log record gets appended to the WAL file, and only after that write is confirmed to be safely on disk does the database go and actually modify the in-memory page (and eventually flush that page to disk, on its own schedule, whenever it feels like it).

Why does this help? Because appending a small record to the end of a file is a much simpler, faster, and more reliable operation than modifying a random page somewhere in a big B-tree file. You're not seeking around, you're not overwriting existing bytes, you're just tacking bytes onto the end. That's about as close to atomic as you can get on most filesystems, especially if you follow it up with an fsync.

And here's the key insight: if the log entry made it to disk, you have everything you need to redo that change, even if the actual data page never got updated before the crash. The log is the source of truth. The data files are just a cache that's allowed to lag behind.

What actually happens on crash recovery

This is where it clicks for most people. Let's say the database crashes right after step 3 below:

Log record written and fsynced: "set balance=300 for id=57"
Transaction committed (this is basically just another log record: "TXN 102 committed")
In-memory page updated
CRASH before the page gets flushed to disk

When the database restarts, it doesn't know or care what state the data files are in. It just opens up the WAL and starts replaying from the last checkpoint (more on checkpoints in a second). It sees the record for txn 102, sees that it committed, and goes "okay, balance should be 300 for id=57" and reapplies that change to the data page. Crash handled. No data lost, even though the change never made it to the actual table file before the crash.

Now flip it around. Say the crash happens between step 1 and step 2, before the commit record gets written:

Log record written: "set balance=300 for id=57"
CRASH

On restart, the database sees a log record for an uncommitted transaction. It knows this transaction never finished, so it just throws that change away during recovery. Nothing to redo, nothing to worry about. The row keeps its old value like the transaction never happened at all.

This is the whole durability guarantee in a nutshell. As long as the log record for a commit hit disk, the transaction is durable, period, even if literally nothing else got written yet. And as long as an uncommitted transaction's records are ignorable during replay, nothing partial ever leaks into your actual data.

Redo and undo, briefly

Real WAL implementations usually need to handle two directions:

Redo: reapplying changes from committed transactions that never made it to disk before a crash. This is the case we just walked through.
Undo: rolling back changes from transactions that were in progress but never committed, in case some of their changes did partially make it into the data pages before the crash.

Postgres, for example, keeps enough info in its WAL to redo everything up to the last consistent point, and it also tracks which transactions were still open so it can effectively ignore or roll back their effects. Different databases split this responsibility differently (MySQL's InnoDB, for instance, leans a lot on its own undo log structure alongside the redo log), but the core idea of "log the intent first, sort out the actual page state during recovery" stays the same everywhere.

Okay but doesn't the log grow forever?

Yeah, it would, if nothing ever cleaned it up. This is where checkpoints come in.

A checkpoint is basically the database saying "alright, I've confirmed that everything before this point in the log has actually been applied to the data files on disk. I don't need to replay anything before here anymore." Once that's true, older WAL segments can be archived or deleted (or reused, in the case of things like Postgres's circular WAL segment files).

Checkpointing is a tradeoff. Checkpoint too often and you're constantly flushing dirty pages to disk, which hurts write throughput. Checkpoint too rarely and your WAL balloons in size, and more importantly, crash recovery takes longer because there's more log to replay before the database can come back online. Most systems let you tune this, because there's no universally correct answer, it depends on how much recovery time you can tolerate versus how much steady-state write overhead you're okay with.

The fsync part actually matters a lot

I glossed over this earlier but it deserves its own callout: none of this works if you don't actually force the log record to physical disk before telling the client "your transaction committed."

Modern OSes and disks love to lie to you about durability unless you're careful. A write() call can return successfully while the data is still sitting in an OS page cache or even a disk's onboard write cache, neither of which survive a power loss. That's why databases explicitly call fsync (or use O_DIRECT and other tricks) on the WAL file before considering a commit durable. It's also why "disable fsync for better performance" is one of those settings that looks amazing in benchmarks and then absolutely ruins someone's week when the power blips.

This is also why WAL gives you a really clean way to reason about the durability/performance tradeoff. Group commit, for instance, is a common optimization where the database batches up several transactions' commit records and fsyncs them together in one go, instead of doing a separate fsync per transaction. You lose a tiny bit of latency per individual commit but you gain a lot of throughput, since fsync calls aren't cheap.

Why not just write directly to the data files, but carefully?

You might wonder why we bother with a separate log at all instead of just being really careful about how we write to the actual data files. A few reasons this doesn't work well in practice:

Data pages tend to be large (think 4KB, 8KB, sometimes bigger), and a single transaction usually only changes a small part of one. Writing the whole page every time you touch a tiny bit of it is wasteful. Appending a small log record describing just the change is way cheaper.

Data files also involve random I/O, jumping around to different pages depending on which rows changed. Sequential appends to a log file are much friendlier to spinning disks, and even on SSDs they tend to be faster and simpler to make crash-safe.

And separating "record the intent" from "apply the change" means the database can batch and reorder the actual page writes however it wants for efficiency, without ever putting durability at risk, because the log already has the ground truth.

A rough mental model to keep

If none of the details above stick, keep this one sentence: the log is what actually happened, and the data files are just an optimization to make reads fast. Everything else, redo, undo, checkpoints, group commit, is just machinery built around protecting and cleaning up that one log.

Once you see it that way, a lot of other database behavior starts making sense too. Why replication in systems like Postgres is often just "ship the WAL to another machine and replay it there." Why some databases let you point-in-time-recover to any moment, because the WAL is a full history, not just a snapshot. It's the same idea showing up over and over, just applied to slightly different problems.

Anyway, next time you write a row and get a happy "commit successful" back, you can picture what actually happened under the hood: a tiny, boring line of text got appended to a file, and that boring line of text is the only reason your data is still there after the next crash.