Write-Ahead Logging

Every time you commit a bank transfer, place an online order, or save a document, a database promises you something remarkable: that data will not vanish, even if the server loses power a millisecond after you press Submit.

How can it make that promise? Flushing every changed data page to disk on every commit would work, but a random write to disk is slow — far too slow for a busy database. Write-ahead logging (WAL) is the elegant escape hatch: instead of rewriting scattered data pages immediately, the database first appends a compact description of the change to a sequential log file and flushes that. Sequential writes are much faster than random ones, and once the log record is on stable storage, the commit is durable by definition.

The rule is simple to state: the log record must reach disk before the corresponding data-page change can be declared committed. Everything else in crash recovery flows from that one invariant. If the system crashes, the database replays the log forward (REDO) to reconstruct committed changes and rolls back uncommitted ones — restoring a consistent state with no human intervention.

WAL is not exotic research; it is the proven engineering backbone of PostgreSQL, MySQL InnoDB, SQLite, and virtually every other ACID-compliant database in production today.

This simulation shows a tiny database with two account balances. Use the buttons to build a transaction step by step: each operation is first appended to the Write-Ahead Log before touching the data pages. Then crash the system at any point and watch the recovery process replay the log — committing what was durable and discarding what was not.

<div class="wal-container">
  <div class="panels">
    <div class="panel">
      <div class="panel-title">{{panel_wal}}</div>
      <div id="log-entries" class="log-area">
        <div class="log-empty">{{log_empty}}</div>
      </div>
    </div>
    <div class="panel">
      <div class="panel-title">{{panel_mem}}</div>
      <div class="data-area">
        <div class="account">
          <span class="acct-label">{{account_a}}</span>
          <span class="acct-value" id="mem-a">$1000</span>
        </div>
        <div class="account">
          <span class="acct-label">{{account_b}}</span>
          <span class="acct-value" id="mem-b">$500</span>
        </div>
        <div class="dirty-note" id="dirty-note" style="display:none">{{dirty_note}}</div>
      </div>
      <div class="panel-title mt">{{panel_disk}}</div>
      <div class="data-area">
        <div class="account">
          <span class="acct-label">{{account_a}}</span>
          <span class="acct-value" id="disk-a">$1000</span>
        </div>
        <div class="account">
          <span class="acct-label">{{account_b}}</span>
          <span class="acct-value" id="disk-b">$500</span>
        </div>
      </div>
    </div>
  </div>
  <div class="status-bar" id="status-bar">{{status_ready}}</div>
  <div class="btns">
    <button id="btn-begin" type="button">{{btn_begin}}</button>
    <button id="btn-debit" type="button" disabled>{{btn_debit}}</button>
    <button id="btn-credit" type="button" disabled>{{btn_credit}}</button>
    <button id="btn-commit" type="button" disabled>{{btn_commit}}</button>
    <button id="btn-crash" type="button" class="danger" disabled>{{btn_crash}}</button>
    <button id="btn-recover" type="button" class="recover" style="display:none">{{btn_recover}}</button>
    <button id="btn-reset" type="button" class="ghost">{{btn_reset}}</button>
  </div>
</div>

* { box-sizing: border-box; }
body { font-family: system-ui, sans-serif; margin: 0; color: #1a2636; }
.wal-container { padding: .2rem 0; }
.panels { display: grid; grid-template-columns: 1fr 1fr; gap: .7rem; margin-bottom: .6rem; }
.panel { background: #f0f4f8; border: 1px solid #cdd9e3; border-radius: 8px; padding: .6rem .7rem; }
.panel-title { font-size: .75rem; font-weight: 700; letter-spacing: .04em; color: #3a5068; text-transform: uppercase; margin-bottom: .4rem; }
.panel-title.mt { margin-top: .7rem; }
.log-area { min-height: 130px; display: flex; flex-direction: column; gap: 3px; }
.log-empty { font-size: .8rem; color: #8a9aaa; font-style: italic; }
.log-entry { font-size: .78rem; font-family: ui-monospace, monospace; padding: 3px 6px; border-radius: 4px; border-left: 3px solid #5a7088; background: #e3eaf0; }
.log-entry.begin { border-color: #2a7abf; background: #ddeefa; }
.log-entry.change { border-color: #e67a00; background: #fdf0df; }
.log-entry.commit { border-color: #1a9e4a; background: #d8f5e5; font-weight: 700; }
.log-entry.replayed { opacity: .65; }
.data-area { display: flex; flex-direction: column; gap: 5px; }
.account { display: flex; justify-content: space-between; align-items: center; background: #fff; border: 1px solid #cdd9e3; border-radius: 6px; padding: 4px 8px; }
.acct-label { font-size: .82rem; font-weight: 600; color: #3a5068; }
.acct-value { font-size: .9rem; font-family: ui-monospace, monospace; font-weight: 700; color: #1a2636; }
.acct-value.changed { color: #e67a00; }
.acct-value.recovered { color: #1a9e4a; }
.dirty-note { font-size: .72rem; color: #c05a00; margin-top: 3px; }
.status-bar { font-size: .88rem; font-weight: 600; min-height: 1.4em; padding: .3rem .4rem; border-radius: 6px; margin-bottom: .5rem; background: #e8eef3; }
.status-bar.ok { background: #d8f5e5; color: #0f6e30; }
.status-bar.warn { background: #fdf0df; color: #7a4400; }
.status-bar.danger { background: #fde8e8; color: #8b0000; }
.status-bar.info { background: #ddeefa; color: #0a4a7a; }
.btns { display: flex; flex-wrap: wrap; gap: .4rem; }
button { font: 600 13px system-ui; padding: .4rem .8rem; border-radius: 7px; border: 1px solid #1d3557; background: #1d3557; color: #fff; cursor: pointer; }
button:disabled { opacity: .4; cursor: default; }
button.ghost { background: #fff; color: #1d3557; }
button.danger { background: #c0392b; border-color: #a93226; }
button.recover { background: #1a9e4a; border-color: #158040; }

// Code not found

Notice the key asymmetry. Without WAL, a crash between writing two data pages leaves the database inconsistent with no way to fix it. With WAL, the log is the ground truth: recovery reads it from the last checkpoint, REDOs every committed transaction, and ignores every incomplete one — guaranteed correct no matter when the crash happened.

The performance story behind WAL comes down to one hardware fact: sequential writes are much faster than random writes on both spinning disks and SSDs. Appending to a log file is sequential; overwriting a random data page is not.

• Log Sequence Numbers (LSNs): every log record gets a monotonically increasing LSN. Data pages store the LSN of the last record that modified them, letting recovery know exactly how far each page has been applied.
• Checkpointing: periodically the database flushes all dirty data pages to disk and writes a checkpoint record in the log. During recovery, only the log after the last checkpoint needs to be replayed — keeping startup time bounded even with a huge log.
• REDO-only vs UNDO/REDO: some systems (SQLite WAL mode) use a simple REDO-only approach — new versions go to the log, old versions stay in the original file. Others (InnoDB) also maintain an UNDO log so that long-running transactions can be rolled back without touching the REDO log. The WAL invariant holds in both designs.
• Group commit: instead of flushing the log after every individual transaction, the database batches many commits into one flush. Throughput rises dramatically with no loss of durability — each transaction still has its log record on disk before the commit acknowledgement goes out.

The result is a system where durability is cheap (one sequential write per commit), recovery is deterministic (replay the log from the last checkpoint), and consistency is guaranteed by construction. This is the engineering achievement that makes ACID transactions practical at scale.

The idea "write intentions to a durable sequential log before acting on them" turns out to be one of the most reusable patterns in systems engineering:

• Relational databases: PostgreSQL, MySQL InnoDB, Oracle, and SQL Server all use WAL (or its direct equivalent) to implement ACID durability. SQLite's WAL mode trades a separate lock for higher read concurrency.
• Distributed consensus (Raft / Paxos): a Raft leader appends log entries and waits for a majority of followers to acknowledge them before committing — the same "log first, apply second" invariant, now across a network of machines. The log is the state machine.
• Change data capture (CDC): tools like Debezium read the database's WAL as a stream of change events, enabling real-time replication, search-index updates, and audit trails without touching application code.
• Event-sourcing architectures: systems like Apache Kafka are essentially append-only logs at their core. Every event is a log record; current state is derived by replaying. The conceptual debt goes straight back to WAL.

See how distributed systems build on the same log idea to coordinate state across many machines — the principle scales remarkably far beyond a single database.

Write-ahead logging is one of those ideas that looks almost too simple to be true: write it down before you do it. Yet that single rule is what lets a database make an ironclad promise — that once it says "committed," your data will survive power failures, kernel panics, and all the other disasters that punctuate the life of a running system.

The log replaces expensive random writes with cheap sequential appends, turns crash recovery into a deterministic replay, and — in distributed form — becomes the backbone of consensus protocols that coordinate thousands of machines. Next time you see a database advertising ACID guarantees, you now know the quiet mechanism doing the heavy lifting: a humble, append-only log that always goes first.