Write-Ahead Log (WAL): The Foundation of Crash Recovery and Durability

I once spent a weekend recovering a PostgreSQL database that lost 15 minutes of transactions because the WAL disk filled up. That weekend taught me more about durability, fsync semantics, and checkpointing than any textbook ever could. The Write-Ahead Log is the single most important piece of your database's crash recovery story, and understanding it deeply separates engineers who build reliable systems from those who just hope they work.

Every database you use—PostgreSQL, MySQL, SQLite, MongoDB, Cassandra—uses a WAL (or its equivalent, the journal or commit log). Kafka's entire architecture is a distributed WAL. If you understand WAL, you understand how data survives crashes, how replication works, and how point-in-time recovery is possible.

The Fundamental WAL Protocol

The WAL protocol has three rules that every system must follow:

1. Write to the log first: Before modifying a data page, append a redo record to the log
2. Flush the log to disk: Ensure the log record is durable (fsync) before applying the change
3. Apply the change: Modify the data page in memory or on disk

This guarantees that even if the system crashes mid-operation, you can replay the log to recover.

Crash Scenarios

REDO and UNDO Logging

There are two flavors of WAL records:

PostgreSQL uses REDO-only WAL (it stores the new page image). MySQL InnoDB uses REDO + UNDO. The tradeoff is WAL size vs. recovery flexibility.

PostgreSQL WAL: Streaming Replication and PITR

PostgreSQL's WAL is the most sophisticated implementation in open-source databases. It enables three critical features:

WAL Segments and LSN

PostgreSQL writes WAL in 16 MB segments. Each record has a Log Sequence Number (LSN)—a byte offset into the WAL stream. Every data page stores the LSN of the last WAL record that modified it. During recovery, PostgreSQL replays WAL records with LSN greater than the page's LSN.

Streaming Replication

Standby replicas connect to the primary and continuously receive WAL records. PostgreSQL supports three replication modes:

In synchronous replication, the primary waits for at least one standby to acknowledge receiving the WAL before reporting commit to the client. This guarantees zero data loss.

Point-in-Time Recovery (PITR)

PITR lets you restore a database to any point in time by:

1. Restoring a base backup (full data directory snapshot)
2. Replaying all WAL segments up to the target time or LSN

This is how you recover from "DROP TABLE" at 2:30 PM—restore the backup from 2:00 AM and replay WAL up to 2:29:59 PM.

Kafka: The Distributed WAL

Kafka takes the WAL concept and makes it the entire system. A Kafka topic partition is just a WAL (called a "log")—an ordered, immutable sequence of records appended to the end.

Kafka's WAL Properties

Kafka fsync Configuration

Kafka's durability is controlled by the acks setting:

Unlike PostgreSQL which fsyncs on every commit, Kafka batches fsync calls periodically (controlled by log.flush.interval.ms). This gives Kafka absurdly high throughput—millions of messages per second on a single broker—at the cost of a small window of potential data loss.

fsync Semantics and Ordering Guarantees

The WAL's durability guarantee ultimately rests on one system call: fsync (or fdatasync on Linux). Understanding what fsync actually promises is critical.

What fsync Guarantees

• Ordering: All writes issued before fsync are on disk before the call returns
• Durability: Data survives power loss and OS crash
• Atomicity: fsync of a file ensures metadata (size, timestamps) is also flushed

What fsync Does NOT Guarantee

• Ordering between files: fsync on file A doesn't order writes to file B (use sync_file_range for ordering)
• Storage controller cache: Some RAID controllers and SSDs lie about fsync (battery-backed write cache mitigates this)
• Filesystem crash consistency: If you write to multiple files, fsync each one or use a journaling filesystem

Batched fsync and Group Commit

PostgreSQL uses group commit to amortize fsync costs across multiple concurrent transactions:

Checkpointing

The WAL grows forever unless you periodically truncate it. Checkpointing is the process of:

1. Flushing all dirty data pages (modified by WAL records) to disk
2. Recording the REDO point—the LSN from which recovery must start
3. Truncating WAL segments before the REDO point

Checkpoint Frequency Tradeoff

PostgreSQL's checkpoint configuration:

WAL in PostgreSQL vs Kafka vs LevelDB

While all three use the WAL concept, their implementations differ dramatically based on their workload:

LevelDB / RocksDB WAL

LevelDB and RocksDB use a WAL called the "log" (confusingly, the same term Kafka uses). Each write is appended to the current WAL file. When the WAL reaches a threshold, the memtable is flushed to an SSTable, and the WAL is recycled.

WAL in MySQL InnoDB

MySQL InnoDB uses a REDO log (its name for WAL) combined with an UNDO log for MVCC (Multi-Version Concurrency Control). The REDO log stores physical page-level changes; the UNDO log stores logical rollback information.

InnoDB REDO Log Architecture

innodb_flush_log_at_trx_commit Settings

The difference between 1 and 2 is the difference between "zero data loss" and "at most 1 second of data loss." For most web applications, setting 2 is acceptable and provides a 5-10x write throughput improvement.

Doublewrite Buffer

InnoDB has an additional protection: the doublewrite buffer. Before writing a page to its final location, InnoDB writes a copy to the doublewrite buffer. This prevents torn pages—situations where only part of a 16 KB page is written due to a crash.

Physical vs Logical WAL

WAL implementations fall into two categories: physical and logical logging.

PostgreSQL uses physical WAL. MySQL supports both: physical REDO log + logical binlog. The binlog is used for replication and PITR because it can be replayed on a different MySQL version.

Hybrid Logical-Physical Logging (MySQL Binlog)

MySQL's binary log (binlog) records logical changes at the row level:

### UPDATE `db`.`users`
### WHERE
###   @1=42 /* id */
###   @2='old@email.com' /* email */
### SET
###   @2='new@email.com' /* email */

This format is schema-independent and can be replayed on replicas with different hardware or MySQL versions.

WAL Archiving Strategies

WAL archiving separates the durability concern from the capacity concern. The live WAL is on fast local storage (NVMe). The archive is on cheaper, durable storage (S3, HDFS).

PostgreSQL's archive_command is a simple shell command that runs after each WAL segment is filled:

archive_command = 'aws s3 cp %p s3://my-bucket/wal/%f'

ARIES Recovery Algorithm

The ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) recovery algorithm is the theoretical foundation used by most WAL-based systems. It has three phases:

Phase 1: Analysis

Scan the WAL from the last checkpoint to find:

• Dirty pages (modified but not yet flushed)
• In-progress transactions (started but not committed)

Phase 2: REDO

Replay all REDO records from the REDO point forward. This is idempotent—replaying a change that was already applied is safe because the page LSN is checked.

Phase 3: UNDO

Rollback uncommitted transactions by applying UNDO records. The UNDO actions are also logged to the WAL to ensure idempotent recovery (a process called "compensation log records" or CLRs).

Compensation Log Records (CLRs)

When the UNDO phase rolls back a transaction, it writes CLRs to the WAL. This ensures that if the system crashes during UNDO, recovery can resume from where it left off rather than redoing the entire undo. CLRs are the mechanism that guarantees idempotent recovery.

Observability and WAL Monitoring

In production, you need to monitor WAL health to prevent the disaster I described in the introduction.

PostgreSQL Specific Monitoring

Run these queries proactively:

-- Check WAL generation rate
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024 / 1024 AS wal_mb;

-- Check replication lag
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- Check checkpoint stats
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time
FROM pg_stat_bgwriter;

WAL Tradeoffs and Optimizations

The fsync Cost

fsync is expensive—typically 1-10 ms per call. On spinning disks, it can be 10+ ms as the disk must physically flush its cache. On SSDs, it's 1-3 ms but still blocks the calling thread.

WAL Compression

PostgreSQL and Kafka support WAL compression. This reduces I/O and storage at the cost of CPU:

WAL on Separate Disk

For maximum performance, put the WAL on a separate physical disk or NVMe device. This prevents:

• WAL writes competing with data page reads for I/O bandwidth
• WAL fsync waiting on data file writeback

Synchronous vs Asynchronous WAL Writes

WAL File System Choices

The filesystem hosting the WAL matters more than you might think:

Filesystem optimization tips:

• Use noatime mount option (skip access time updates)
• Use a separate filesystem for WAL (tune inode size for many small WAL files)
• Consider O_DIRECT for WAL writes (PostgreSQL: wal_sync_method = open_datasync)
• Disable filesystem journaling on the WAL volume if using battery-backed RAID (trade durability for performance)

WAL in Cloud Environments

When running databases in the cloud, WAL placement requires special consideration:

Summary

Summary

The Write-Ahead Log is the foundation of durability in every storage system that matters. The rule is simple—write to the log before modifying data—but the implementations range from PostgreSQL's feature-rich WAL to Kafka's high-throughput distributed log to LevelDB's minimalist approach.

Understanding WAL gives you a mental model for how data survives crashes, how replication works, and how point-in-time recovery operates. Whether you are configuring PostgreSQL's checkpoint intervals, tuning Kafka's fsync settings, or designing your own storage engine, the WAL is the foundation you build on.

"A database without a WAL is like flying without a parachute. It works great until it doesn't, and then recovery is catastrophic." — That database admin who learned the hard way