Stackbook Logo
data-stateestablished · medium operational burden

Write-Ahead Log

Also known as: wal, redo-log, commit-log

Intent

Guarantee durability and atomicity by recording every change to an append-only log before applying it to the main data structures.

Problem

Updating data pages in place is not atomic, so a crash mid-write can leave the store corrupted or lose acknowledged writes. Flushing every changed page on each commit is too slow. The system needs fast, durable commits and a way to recover to a consistent state.

Forces

  • •Sequential log appends are faster than scattered page writes
  • •Acknowledged commits must survive crashes
  • •Recovery time should be bounded
  • •Fsync cost trades durability against latency

Solution

Overview

The write-ahead log is the foundation of durability in PostgreSQL, MySQL InnoDB, SQLite, Kafka and most databases. The rule is simple: the log record must reach disk before the change it describes.

How it works

Commit path and recovery in outline.

BEGIN
  append WAL record (LSN 1042: set balance=90)
  fsync WAL            <- durable here
  update page in memory (dirty)
COMMIT acknowledged
background: write dirty pages, checkpoint at LSN 1042

crash -> start from checkpoint LSN
      -> redo records after it, undo uncommitted

Group commit batches several transactions into one fsync to raise throughput without weakening durability.

Log records carry a log sequence number (LSN) that orders all changes. Each data page remembers the LSN of its last change, so recovery can skip records already reflected on disk. Physical logging records page-level byte changes, while logical logging records row-level operations and is what replication and CDC consume. Durability is configurable: synchronous commit waits for fsync, while asynchronous commit acknowledges earlier and risks losing the last few milliseconds of commits on a crash. Choose per workload and document the choice.

Pitfalls

  • Turning off fsync or using disks with volatile write caches that lie about durability.
  • Forgetting that replication slots or archivers pin old log segments.
  • Placing the log on the same slow device as data, so commits queue behind page writes.
  • Setting checkpoints too far apart, making crash recovery take minutes.

Key takeaways

  • Log first, apply second, acknowledge after the log is durable.
  • Checkpoints bound recovery time and allow log recycling.
  • The same log enables replication, CDC and point-in-time recovery.
  • Monitor log volume and the oldest retained segment.

✓ When to Use

  • •Any storage engine that updates state in place
  • •Replicating changes to replicas or downstream consumers
  • •Systems needing point-in-time recovery

✗ When Not to Use

  • •Purely ephemeral caches where loss is acceptable
  • •Immutable append-only stores where the data file is itself the log
  • •Tiny single-writer tools where atomic file rename is enough

Pros

  • +Durable commits with sequential I/O
  • +Crash recovery to a consistent state
  • +Log can feed replication and change data capture

Cons

  • −Extra write for every change
  • −Fsync latency sits on the commit path
  • −Log growth must be managed with checkpoints and archiving

Cost Profile

Infrastructure

Low - needs a fast, ideally separate, log device

Operational

Medium - manage retention, archiving and disk space

Cognitive

Medium - recovery semantics are subtle

Failure Modes

  • Log disk fills and the database stops accepting writes

  • Disabling fsync gives speed but loses acknowledged commits on power failure

  • A stalled replica holds back log recycling and exhausts disk

Real-World Examples

Alternatives

    Related Patterns

    • replication
    • cdc

    Competency Domains

    data statereliability ops