How Write-Ahead Logging Keeps Databases Crash-Safe

Quick Answer

Write-ahead logging (WAL) means a database records every change as an append-only log entry, and fsyncs that entry to disk, before it ever modifies the actual data pages. If the process crashes mid-update, the data pages might be in an inconsistent state, but the log has a complete, ordered record of what was supposed to happen, so startup recovery can replay committed transactions forward and discard anything that never reached a commit marker. We built a real Python simulation of exactly this, including a genuine crash-mid-transaction scenario, and show the actual output.

The problem write-ahead logging solves

Say a database needs to update a row that lives on some data page on disk. The naive approach is: read the page, modify the bytes in memory, write the page back. That sounds fine until you ask what happens if the process (or the machine) dies in the middle of that write — say, after the first half of the page's bytes have hit disk and the second half haven't. You're left with a page that is neither the old version nor the new version. It's garbage, and worse, you often can't even tell that it's garbage.

The fix databases converge on is almost always the same shape: before touching the real data page at all, append a small, self-contained record to a separate, append-only log file describing exactly what change is about to happen, and make sure that record is durably on disk (via fsync) before doing anything else. Only after that log record is safely down can the actual data page be modified — and critically, the data page modification itself doesn't need to be durable immediately, because if it's lost, the log has everything needed to redo it.

This is the entire idea behind the name: the log is written ahead of the data. It flips the order of operations from "risk corrupting the real data first" to "risk nothing, because the log is the source of truth, and the data pages are just a cache of what the log says should be true."

The commit protocol: BEGIN, intent records, COMMIT

A transaction's log entries typically look like a small block: a BEGIN marker, one or more records describing the actual changes ("set balance_A to 70", "set balance_B to 80"), and a final COMMIT marker. The COMMIT marker is the actual durability boundary — it's the single byte that separates "this transaction definitely happened, guaranteed" from "this transaction may or may not have happened, treat it as if it didn't."

This is a deliberate, load-bearing design choice, not an implementation detail. It means recovery logic can be almost embarrassingly simple: scan the log, and for every block of records that ends in a COMMIT marker, make sure the data reflects those changes (redo them if the data page writes never made it to disk). For any block that doesn't end in a COMMIT marker — because the crash happened before it could be written — just skip it. You don't need to actively "undo" anything in the data, because nothing in the data was ever required to have been written yet in the first place.

It's also worth noting why append-only logging is fast, not just safe: sequential appends to the end of a file are about as cheap as disk I/O gets, with no seeking, versus the random-access pattern of updating scattered data pages in place. WAL's safety mechanism and WAL's performance benefit come from the same property: sequential writes.

Simulating a real crash, in real Python

Here's an actual simulation: a tiny two-account "database", a log-before-data write path, and two scenarios run for real — one where the transaction's log gets fully written (including the COMMIT marker) before a crash, and one where the crash happens before the COMMIT marker is ever appended.

DATA = {"balance_A": 100, "balance_B": 50}

def apply_entry(data, entry):
    parts = entry.split()
    if parts[0] == "SET":
        data[parts[1]] = int(parts[2])

def replay_log(data, lines):
    pending = []
    applied = []
    for line in lines:
        if line == "BEGIN":
            pending = []
        elif line == "COMMIT":
            for e in pending:
                apply_entry(data, e)
                applied.append(e)
            pending = []
        else:
            pending.append(line)
    # anything still in `pending` here was never committed - discard it
    return applied, pending

Scenario 1 — log fully written, then crash, then restart:

Before: {'balance_A': 100, 'balance_B': 50}
Log written BEFORE data file was touched (WAL rule).
Simulating a crash immediately after the log was fsynced, before data pages were flushed.
Data file still shows old values: {'balance_A': 100, 'balance_B': 50}
Restart: replaying log...
  Replayed and applied: ['SET balance_A 70', 'SET balance_B 80']
Data file after replay: {'balance_A': 70, 'balance_B': 80}
Result: transaction correctly completed (redo) from the log. PASS

Scenario 2 — crash before the COMMIT marker was ever appended:

Before: {'balance_A': 100, 'balance_B': 50}
Log at time of crash (no COMMIT marker): ['BEGIN', 'SET balance_A 70', 'SET balance_B 80']
Restart: replaying log...
  Discarding uncommitted entries (no COMMIT marker found): ['SET balance_A 70', 'SET balance_B 80']
  Replayed and applied: []
Data file after replay: {'balance_A': 100, 'balance_B': 50}
Result: uncommitted transaction correctly rolled back / ignored. Data untouched. PASS

Both are genuine runs of the same recovery function on two different log contents. Scenario 1 is redo: the log proves the transaction committed, so recovery makes sure the data matches, even though the data page write itself never happened before the crash. Scenario 2 is an implicit rollback: because the data was never touched in the first place (only the log was being written), "rolling back" costs nothing — recovery just has to recognize the transaction as incomplete and leave the data alone.

Checkpointing: the log can't grow forever

If every committed transaction just accumulates in the log and nothing ever removes old entries, the log grows without bound and a crash recovery scan would take longer and longer over the life of the database. The fix is checkpointing: periodically, the database takes all the data-page changes implied by committed log entries and actually, durably writes them into the real data file. Once that's done, every log entry older than the checkpoint is now redundant — the data file itself already reflects them — so the log can be truncated or recycled up to that point.

This is why recovery after a crash doesn't have to replay a database's entire history since creation: it only needs to replay from the last checkpoint forward. Checkpointing is the mechanism that keeps the cost of crash recovery bounded, even though the log itself is what keeps recovery correct.

Real systems built on exactly this

PostgreSQL's WAL (the files in pg_wal/) is precisely this mechanism: every change to a heap page or index is logged before the page itself is flushed, and PostgreSQL's physical streaming replication works by literally shipping that WAL stream to replicas, which replay it to stay in sync — replication and crash recovery reuse the same log.

SQLite offers WAL mode as an alternative to its default rollback-journal mode: instead of writing old page contents to a rollback journal before overwriting them in place, new pages are appended to a separate WAL file, and readers can keep reading the old, consistent database file while a writer's changes sit in the WAL, which also happens to make concurrent reads and writes play together better.

MySQL's InnoDB storage engine has a redo log (the ib_logfile files) that works the same way for its own pages, with the added twist of a doublewrite buffer to protect against partial page writes at the storage layer itself, since redo logging protects against lost updates, not against a half-written page coming back as physically corrupted bytes.

The part that's actually expensive: fsync

Appending to a log is cheap. Making sure the operating system has actually pushed those bytes to physical storage — calling fsync() — is the genuinely expensive step, because it has to wait for the storage device to acknowledge the write, bypassing any volatile write caches that would otherwise lie about durability. A database doing an fsync per transaction commit can be throughput-limited by how many fsyncs per second the storage can sustain, which is why group commit exists: batch the commit records of several transactions that happen to be committing around the same time into a single fsync, so the fixed cost of the sync is amortized across many transactions instead of paid once per transaction.

It's also worth being honest about what WAL does not give you for free: it doesn't eliminate the cost of eventually writing data pages to disk (checkpointing still has to happen and still costs I/O), and on a system with a storage device that lies about having synced data (some cheap SSDs and misconfigured virtual disks do this), WAL's guarantees evaporate, because the entire scheme assumes fsync means what it says.

Frequently Asked Questions