Write-Ahead Logs: How Databases and Distributed Systems Guarantee Durability

Every database that guarantees durability is making you a promise: if a write returns success, that write survives a crash. The mechanism behind that promise, in PostgreSQL, SQLite, etcd, Kafka, and essentially every serious storage system, is the write-ahead log (WAL).

The idea is deceptively simple. Before mutating any data file, append a description of that mutation to a sequential log. The log is the ground truth. The data files are a materialized view of it. If the process crashes mid-write, recovery replays the log to reconstruct consistent state.

That simplicity hides a lot of real engineering. Fsync semantics, group commit, checkpointing intervals, log compaction, WAL archiving for point-in-time recovery: each of these has measurable production consequences. This article covers how WAL actually works, what a minimal implementation looks like, and what you need to monitor and tune in production.

Why Sequential Writes Beat Random Writes

The core insight behind WAL is that sequential writes to a log are dramatically faster than random writes to arbitrary data file locations, especially on spinning disk. Even on SSDs, sequential writes are more predictable and friendlier to the underlying flash translation layer.

When you commit a transaction that touches ten rows spread across five B-tree pages, the naive approach writes those five pages to disk before returning. With WAL, you write a single sequential log record describing those ten row mutations, then acknowledge the commit. The dirty pages still get written eventually, but that can happen asynchronously as part of a checkpoint.

This is the fundamental performance bargain. You trade random I/O at commit time for sequential I/O at commit time plus periodic checkpoint I/O that can be batched and scheduled.

The Structure of a WAL Record

A WAL record contains everything needed to replay or undo a mutation. The exact format varies by system, but the conceptual fields are consistent:

interface WALRecord {
  lsn: bigint;          // Log Sequence Number: monotonically increasing position
  transactionId: number;
  type: "INSERT" | "UPDATE" | "DELETE" | "COMMIT" | "ABORT" | "CHECKPOINT";
  relation: string;      // table or resource name
  prevLSN: bigint;       // pointer to previous record in same transaction
  data: {
    before?: Record<string, unknown>;  // for UPDATE/DELETE: old values (undo)
    after?: Record<string, unknown>;   // for INSERT/UPDATE: new values (redo)
  };
  checksum: number;      // detect partial writes or corruption
}

The Log Sequence Number (LSN) is critical. It is a monotonically increasing identifier for each record’s position in the log. In PostgreSQL, LSNs are byte offsets within the WAL stream, expressed as a pair of 32-bit hex segments (e.g., 0/16B4A30). The LSN determines ordering and is how replication standby servers track how far they have consumed from the primary.

The prevLSN field creates a per-transaction linked list. During recovery, the system can walk backward through a transaction’s records to undo it without scanning the entire log.

A Simplified WAL Implementation

Here is a simplified implementation that captures the core ideas: append-only write, fsync for durability, and sequential replay for recovery.

import * as fs from "fs";
import * as path from "path";

interface WALEntry {
  lsn: number;
  transactionId: number;
  type: string;
  payload: unknown;
  checksum: number;
}

function computeChecksum(data: string): number {
  let hash = 5381;
  for (let i = 0; i < data.length; i++) {
    hash = ((hash << 5) + hash) ^ data.charCodeAt(i);
  }
  return hash >>> 0;
}

class WriteAheadLog {
  private fd: number;
  private currentLSN: number = 0;
  private walPath: string;

  constructor(walPath: string) {
    this.walPath = walPath;
    this.fd = fs.openSync(walPath, "a");
    this.currentLSN = this.recoverLSN();
  }

  append(transactionId: number, type: string, payload: unknown): number {
    const lsn = ++this.currentLSN;
    const entry: WALEntry = {
      lsn,
      transactionId,
      type,
      payload,
      checksum: 0,
    };
    const serialized = JSON.stringify(entry);
    entry.checksum = computeChecksum(serialized);
    const line = JSON.stringify(entry) + "\n";

    fs.writeSync(this.fd, line);
    // fdatasync flushes data to durable storage without flushing metadata.
    // On Linux this is slightly cheaper than fsync, and sufficient for WAL.
    fs.fdatasyncSync(this.fd);

    return lsn;
  }

  replay(fromLSN: number, handler: (entry: WALEntry) => void): void {
    const content = fs.readFileSync(this.walPath, "utf-8");
    const lines = content.split("\n").filter(Boolean);

    for (const line of lines) {
      const entry: WALEntry = JSON.parse(line);
      if (entry.lsn <= fromLSN) continue;

      const { checksum, ...rest } = entry;
      const expected = computeChecksum(JSON.stringify({ ...rest, checksum: 0 }));
      if (checksum !== expected) {
        throw new Error(`WAL corruption detected at LSN ${entry.lsn}`);
      }

      handler(entry);
    }
  }

  private recoverLSN(): number {
    if (!fs.existsSync(this.walPath)) return 0;
    const content = fs.readFileSync(this.walPath, "utf-8");
    const lines = content.split("\n").filter(Boolean);
    if (lines.length === 0) return 0;
    const last: WALEntry = JSON.parse(lines[lines.length - 1]);
    return last.lsn;
  }

  close(): void {
    fs.closeSync(this.fd);
  }
}

This is intentionally simplified. Production implementations use binary encoding (not JSON), pre-allocated segment files to avoid filesystem fragmentation, and CRC32C checksums computed over the raw bytes. But the contract is identical: append returns only after the record is on durable storage, and replay reconstructs state deterministically from any starting LSN.

Checkpointing: Bounding Recovery Time

If the WAL grows unbounded, crash recovery would require replaying every record ever written. Checkpoints prevent this. A checkpoint is a moment where the database guarantees that all dirty pages modified before the checkpoint LSN have been flushed to the data files. After a successful checkpoint, WAL segments before the checkpoint LSN can be recycled.

In PostgreSQL, checkpoint_completion_target (default 0.9) controls how spread out checkpoint I/O is over the checkpoint interval. A target of 0.9 means the database tries to finish writing dirty buffers across 90% of the checkpoint_timeout period rather than all at once. This smooths I/O at the cost of keeping more dirty pages in memory longer.

The checkpoint process itself writes a CHECKPOINT record to the WAL. On startup after a crash, PostgreSQL reads the pg_control file to find the last completed checkpoint LSN, then replays WAL forward from that point. This bounds recovery time to the volume of WAL written since the last checkpoint.

interface CheckpointRecord {
  type: "CHECKPOINT";
  checkpointLSN: number;       // LSN of this checkpoint record
  priorCheckpointLSN: number;  // LSN of the previous checkpoint
  dirtyPagesFlushed: number;   // count of pages written before checkpoint
  walSegmentsRetained: string[]; // segments that must be kept for replication
}

The walSegmentsRetained field is why you cannot always recycle WAL after a checkpoint: replication standbys consume WAL asynchronously. If a standby is lagging, the primary must retain WAL segments back to the standby’s current replay LSN. This is what PostgreSQL’s wal_keep_size and replication slots govern.

Log Compaction vs. Checkpointing

Checkpointing and log compaction solve the same underlying problem (unbounded log growth) but in different contexts.

Databases like PostgreSQL use checkpointing because data has complex page-based structure. The WAL describes mutations to B-tree pages, heap pages, and index structures. Replaying the full WAL rewrites those pages in-place.

Systems like Kafka and etcd treat the log itself as the primary storage, not a recovery mechanism. Kafka’s log compaction retains only the latest value for each key, discarding superseded records. This is not crash recovery; it is log truncation as a feature of the data model.

etcd uses the Raft consensus algorithm, where the log is the distributed commit protocol. etcd snapshots the key-value store state periodically, then truncates the Raft log to the snapshot index. Recovery loads the snapshot and replays only the entries after it. This is structurally identical to database checkpointing, just expressed differently.

The key distinction: in a database, the data files are the canonical state and the WAL is the recovery mechanism. In a log-structured system like Kafka, the log is the canonical state and compaction is housekeeping.

WAL and Replication

WAL enables streaming replication with no additional protocol overhead. PostgreSQL physical replication works by shipping WAL records from primary to standby in real time. The standby applies those records exactly as crash recovery would, just without the crash. The standby’s replay LSN tracks how far behind it is. You can query this directly:

-- On the primary: check how far each standby has replicated
SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

The difference between sent_lsn and replay_lsn is your replication lag in bytes. Monitoring this as a byte count is more actionable than monitoring in seconds, because the translation from bytes to seconds depends on write rate.

Logical replication decodes WAL records into a higher-level change stream (inserts, updates, deletes on specific tables) rather than shipping physical page changes. This allows replicating to heterogeneous targets or subscribing only to specific tables. The tradeoff is that logical decoding has CPU overhead and does not replicate DDL automatically.

The Fsync Cost and Group Commit

Every append in the simplified implementation above calls fdatasync. On a modern NVMe drive, a single fsync takes 50-200 microseconds. If you fsync once per committed transaction, that is your minimum commit latency, and it puts a hard ceiling on single-threaded transaction throughput around 5,000-20,000 TPS.

Group commit is the standard optimization. Instead of fsyncing immediately after each transaction, the WAL writer accumulates records from multiple concurrent transactions and issues a single fsync that covers all of them. All transactions waiting for that fsync complete simultaneously.

PostgreSQL implements this automatically. The wal_writer_delay parameter (default 200ms) and wal_writer_flush_after (default 1MB) control how the background WAL writer batches flushes. Under high concurrency, group commit can increase throughput by 10-50x compared to per-transaction fsyncs, with no loss in durability guarantees.

The tradeoff is commit latency distribution. Under low load, a transaction may wait up to wal_writer_delay for the next batch flush. Under high load, batches form and flush quickly, so average latency stays low but maximum latency can spike if the WAL writer falls behind.

Tradeoffs

Dimension	Consideration	Production Impact
fsync per commit	Strongest durability guarantee, highest latency	Use for financial writes; accept 5-20k TPS ceiling
Group commit	Near-equivalent durability, much higher throughput	Default in most databases; tune batch interval to workload
synchronous_commit = off	No fsync on commit; up to 3x faster; short data loss window on crash	Acceptable for analytics, queues; never for financial records
Checkpoint frequency	More frequent: shorter recovery time, more I/O overhead	Tune checkpoint_timeout and max_wal_size together
WAL compression	Reduces WAL size and I/O at CPU cost	Worthwhile for text-heavy workloads; benchmark first
Replication slots	Prevents WAL recycling until standby consumes; prevents disk exhaustion	Always set max_slot_wal_keep_size; unmonitored slots fill disks

Production Considerations

Monitoring WAL lag. Alert on replication lag in bytes, not seconds. Seconds is a derived metric that changes with write rate; bytes is direct. A standby 100MB behind on a system writing 10MB/s has 10 seconds of lag. The same 100MB behind on a system writing 1MB/s has 100 seconds. The bytes number tells you the blast radius on failover.

Replication slot hygiene. A disconnected replication slot silently prevents WAL recycling. The primary will accumulate WAL until disk fills, at which point it crashes. Monitor pg_replication_slots and alert if any slot’s confirmed_flush_lsn has not advanced in more than 15 minutes.

SELECT
  slot_name,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS retained_bytes
FROM pg_replication_slots
ORDER BY retained_bytes DESC;

Set max_slot_wal_keep_size to a finite value (e.g., 10GB) so that a lagging or dead slot cannot fill your disk. The slot will be invalidated if it falls too far behind, but that is recoverable. Disk exhaustion is not.

WAL archiving for point-in-time recovery. Continuous archiving ships completed WAL segment files to object storage. Combined with a base backup, this lets you restore the database to any point in time, not just the latest state. The archive_command setting in PostgreSQL runs a shell command for each completed segment.

# postgresql.conf
archive_mode = on
archive_command = 'aws s3 cp %p s3://your-wal-archive/%f'
wal_level = replica   # minimum required for archiving

The %p and %f substitutions expand to the full path and filename of the WAL segment. For production, use a tool like pgBackRest or Barman rather than a raw archive_command. They handle retry, checksumming, and catalog management that a one-liner cannot.

Testing recovery. WAL archiving is only as good as your restore test. Automate a weekly restore to a throwaway instance and verify that the data matches a known checkpoint. Teams that do not test restores discover their backup is broken at the worst possible moment.

WAL size management. max_wal_size (default 1GB) is the soft upper limit on WAL accumulated between checkpoints. If write rate is high enough to accumulate more than max_wal_size before a checkpoint completes, PostgreSQL triggers a forced checkpoint. These force checkpoints appear in logs and indicate that your checkpoint interval or checkpoint I/O throughput is insufficient for your write rate. The fix is either a faster disk, more checkpoint_completion_target spread, or a more frequent checkpoint_timeout.

How SQLite Handles WAL Mode

SQLite defaults to a rollback journal, where the old page content is written to a separate journal file before mutating the data file. WAL mode (PRAGMA journal_mode=WAL) inverts this. Writes go to a WAL file; readers continue reading from the original data file concurrently.

SQLite’s WAL mode allows one writer and multiple concurrent readers without blocking each other. Readers see the last committed snapshot. The WAL is merged back into the main database file during a checkpoint (wal_autocheckpoint, default every 1,000 pages). This makes SQLite WAL mode substantially faster for write-heavy workloads and essential for any situation with concurrent readers.

The SQLite WAL file has a fixed-format header and a series of frames, each corresponding to one database page. Frame numbers increase monotonically. Readers lock the WAL to the last committed frame number at the start of each read transaction, ensuring snapshot isolation without blocking writers.

The Mental Model

WAL is a promise: the log entry is permanent before the state change is. Everything else, checkpointing, compaction, replication, point-in-time recovery, follows from that invariant.

When you are debugging a crash recovery failure, look for the last checkpoint LSN in the control file, find the WAL record at that LSN, and walk forward. When you are sizing a replication standby, think in bytes of WAL per second, not queries per second. When you are setting synchronous_commit = off for a background job, understand that you are accepting a window of potential data loss equal to wal_writer_delay.

The details vary by system but the shape is always the same: sequential log first, state mutation second, periodic materialization to bound recovery time. Once you understand that shape, the behavior of every storage system that implements it becomes predictable.