Write-Ahead Log

The Write-Ahead Log (WAL) is a sequential, append-only, on-disk log of every change a database (or any state machine) intends to make, recorded before the corresponding data pages, indexes, or in-memory structures are modified. The discipline — usually summarized as “log first, modify after” — gives you two enormous practical benefits in one move: durability (committed changes survive a crash because the log can replay them) and performance (sequential log writes are one to two orders of magnitude faster than the random-access page writes they replace). WAL is the substrate underneath effectively every modern transactional system: PostgreSQL’s pg_wal, MySQL InnoDB’s redo log, SQLite’s WAL mode, the durability layer of LSM Tree engines like RocksDB and LevelDB, the Raft log in etcd and CockroachDB, the binlog/oplog in MySQL and MongoDB. It is the canonical answer to the interview question “how does a database survive a crash?” and the building block that lets you reason about replication, recovery, and CDC. Understanding WAL means understanding two cardinal rules — the log-before-modify rule and the flush-log-at-commit rule — and the recovery algorithm (canonically, ARIES) that those rules enable.

1. Plain-Language Definition

Imagine a database as a giant stack of file pages on disk that hold the actual rows of every table. A transaction wants to change row 42 in table orders. The naive approach is to grab the relevant page from disk, mutate it in memory, and write it back. Two things go wrong.

First, what happens if the machine loses power between writing some of the changed pages and not others? A transaction that updates a row and an index might write the row’s page successfully, fail to write the index page, and on restart the database is corrupt — the index points to the old version, the data has the new version, and there is no way to figure out what was supposed to happen. Crash recovery is an unsolved problem in this design.

Second, every transaction’s writes are scattered randomly across the disk: page 17 here, page 42,000 there, page 123 over there. Even with a buffer pool batching them, the eventual flushes to disk are random-access I/O, which on a spinning disk costs ~10ms per seek and on an SSD costs SSD wear and dramatically lower throughput than sequential writes.

The WAL solves both problems with a single twist. Before changing any data page, the database writes a description of the intended change — “transaction 82173 changed orders row 42 from (status='pending') to (status='shipped')” — to a sequential log file. Only after that log record is durably on disk does the database modify the data page in memory; the data page is flushed to disk later, on its own schedule, in batches. The transaction is considered committed the moment its commit record is forced to the log, regardless of whether the data pages have been written. If the machine crashes, recovery replays the log: any committed changes whose data pages did not make it to disk are reapplied; any uncommitted changes that did make it to disk are undone. The log is the canonical record of truth, and the data pages are essentially a cache.

This is what “log first, modify after” means. The log captures intent; the data files capture result. The two stay in sync via background flushing and crash recovery.

2. Mechanism

flowchart TB
    APP[Application Transaction] -->|"BEGIN; UPDATE..."| TX[Transaction Manager]
    TX -->|"1. write log record<br/>(redo + undo info)"| LOGBUF[In-Memory Log Buffer]
    TX -->|"2. modify page<br/>in buffer pool"| BUFFER[Buffer Pool<br/>dirty pages]
    TX -->|"3. on COMMIT:<br/>force log buffer to disk"| WAL[(Write-Ahead Log<br/>on durable storage)]
    BUFFER -.->|"async background<br/>flush (checkpoint)"| DATA[(Data Files<br/>on disk)]
    WAL -.->|"on crash recovery"| REPLAY[Replay log forward<br/>then undo uncommitted]
    REPLAY --> DATA

Diagram: the WAL discipline. A write enters as a log record (1) and a page modification (2) at the same time, but only the log record needs to be on durable storage at commit (3). The data file flush is asynchronous. Recovery replays the log to rebuild the data file state.

The two cardinal rules of WAL, traceable to the ARIES paper (Mohan et al. 1992 sources), are:

Rule 1 — Log-before-modify (the “WAL rule” proper). A modified data page must not be flushed to disk before the log record describing that modification has been forced to the log. Equivalently: for any dirty page in the buffer pool, the log record corresponding to its dirtying change must already be durable. This rule is what makes recovery possible. If the data page reaches disk first and the system crashes before the log record is written, you have a permanent on-disk modification with no log entry telling you why — recovery cannot undo or even identify the change.

Rule 2 — Flush-log-at-commit. When a transaction commits, its commit record must be durably on disk before control returns to the client. This rule is what makes commits durable — the client’s view of “transaction succeeded” must imply the change cannot be lost. The two rules together are sufficient: rule 1 ensures recovery can undo any partial writes; rule 2 ensures recovery will not roll back transactions the user was told succeeded.

A WAL record is small and stylized. It carries (PostgreSQL example, docs):

• A Log Sequence Number (LSN) — a monotonically increasing 64-bit identifier, conceptually the offset of the record within the global log.
• The transaction ID (XID) that produced the change.
• The resource manager (heap, btree, gin, …) that owns the change, so the right replay routine is invoked.
• A change descriptor — typically both a redo image (how to reapply the change) and an undo image (how to roll it back), although some systems (PostgreSQL) keep the undo information implicitly via Multi-Version Concurrency Control rather than in the log.

In write-path terms, the database keeps an in-memory log buffer. Each operation appends a log record to this buffer. Commit calls fsync() on the underlying log file, which forces the OS page cache and the disk’s own write cache to durable media. Modern databases use group commit: multiple concurrent transactions piggyback on a single fsync call, amortizing its cost across many transactions. Without this optimization, fsync rate would cap commit throughput at perhaps 100–1000 commits/sec; with it, throughput scales much higher.

The data pages, meanwhile, sit in the buffer pool as dirty pages. They are flushed by a background writer during ordinary operation, and forced to disk during a checkpoint. A checkpoint walks the buffer pool, flushes every page modified since the last checkpoint, and writes a checkpoint record to the log saying “everything before LSN X is now reflected on disk.” Once a checkpoint completes, the log records from before the checkpoint are no longer needed for crash recovery and may be archived or recycled.

2.4 Group Commit and Commit Pipelining

In a naive WAL implementation, every commit calls fsync() on the log file synchronously. Each fsync is a round-trip to durable storage — historically ~10ms on spinning disks, ~1ms on enterprise SSDs, ~0.1ms on NVMe with power-loss-protected (PLP) caches. If commits are processed sequentially and each requires its own fsync, commit throughput is bounded by 1 / fsync_latency — perhaps 100/sec on spinning disks, 1000/sec on SSDs.

Group commit breaks this bound by amortizing one fsync across many commits. The mechanism: when a transaction calls commit, it appends its commit record to the log buffer and then waits on a condition variable. A single dedicated thread (sometimes called the “log writer” or “WAL writer”) periodically wakes, fsyncs the log buffer, and signals everyone waiting. Multiple transactions piggyback on a single fsync; throughput scales as commits-per-fsync rather than fsyncs-per-second. With a group size of 50, a 10ms fsync delivers 5,000 commits/sec from a single disk.

Commit pipelining is a related optimization: while one fsync is in flight, additional commits accumulate in the next batch, so disk and CPU are kept in parallel motion. PostgreSQL since 9.2 uses both group commit and commit pipelining; MySQL InnoDB has had group commit since 5.6 (MySQL docs — InnoDB group commit).

A subtle implementation point: group commit only helps if multiple transactions are concurrent. Single-threaded workloads (one client doing one transaction at a time) see no benefit because there is never more than one commit waiting. This is why fsync rate is sometimes the real throughput ceiling when transactions are inherently serialized (e.g., batch jobs that loop BEGIN; UPDATE; COMMIT;), and why those workloads benefit from explicit batching at the application layer.

2.5 Recovery (ARIES, sketched)

When the database starts after a crash, it does not know which dirty pages made it to disk and which were lost. It relies entirely on the WAL. ARIES (Mohan et al. 1992) defines a three-pass recovery algorithm:

1. Analysis pass. Scan forward from the most recent checkpoint to the end of the log, building a table of in-flight transactions (those without a commit or abort record at end-of-log) and a list of pages that were dirty at the time of the crash.
2. Redo pass. Scan forward again, replaying every log record (committed or not) whose effect is not already reflected on the on-disk page (determined by comparing the page’s stored LSN to the log record’s LSN). After this pass, the data pages reflect every change that ever made it into the log.
3. Undo pass. Scan backward, undoing the changes of every transaction that was in-flight at the time of the crash, using the undo information in the log.

After all three passes, the database is in a consistent state reflecting exactly the transactions that committed before the crash, and nothing else.

PostgreSQL implements a simplified ARIES variant: redo only, no undo (because Multi-Version Concurrency Control keeps old row versions in the heap rather than in the log). MySQL InnoDB implements full ARIES-style redo+undo. SQLite’s WAL mode is a different beast — the WAL file replaces the database file’s modified pages until a checkpoint copies them back, so readers consult both the WAL and the main file (SQLite WAL docs).

2.6 Checkpoints and the Recovery Time Objective

Recovery time depends linearly on the amount of WAL between the last checkpoint and the crash. A database that checkpoints every 5 minutes will have at most 5 minutes of WAL to replay; one that checkpoints every hour, up to an hour. Production systems tune the checkpoint interval to balance two costs: more frequent checkpoints reduce recovery time but cause more I/O on the data files (because more dirty pages must be flushed); fewer checkpoints reduce ongoing I/O cost but increase the worst-case recovery time and the size of the on-disk WAL.

Postgres exposes the relevant knobs as checkpoint_timeout (default 5 minutes), max_wal_size (default 1GB; checkpoint forced when crossed), and checkpoint_completion_target (default 0.9, meaning try to spread the checkpoint’s I/O across 90% of the interval rather than dumping it all at once). InnoDB exposes innodb_io_capacity, innodb_max_dirty_pages_pct, and similar parameters with similar semantics.

A subtle interaction: an aggressive checkpoint policy that flushes many pages at once can cause checkpoint storms — periods of multi-second I/O bursts where the data files saturate the disk, foreground latency spikes, and the database appears frozen to applications. Modern databases use fuzzy checkpointing — instead of stopping the world, dirty pages are flushed gradually by a background writer (bgwriter in Postgres) so the checkpoint itself just records a marker rather than flushing pages. This smooths the I/O profile but does not eliminate the underlying cost; the integral of dirty-page-flush rate over time still equals the rate at which pages are being dirtied.

3. Origins

The fundamental idea — log changes before applying them — is older than relational databases. It appears in the System R papers from IBM (Gray et al., late 1970s) and in Jim Gray’s foundational text Transaction Processing: Concepts and Techniques (Gray & Reuter 1992), which remains the encyclopedia on the topic.

The canonical reference for crash-recovery via WAL is the 1992 ACM TODS paper “ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging” by C. Mohan and colleagues at IBM Almaden (Mohan et al. 1992). The paper defined the redo-undo recovery algorithm that nearly every commercial DBMS now implements (often with database-specific optimizations) — IBM DB2, Microsoft SQL Server, Oracle, MySQL InnoDB, and indirectly PostgreSQL all owe their recovery design to ARIES.

Two design choices in ARIES were ahead of their time and remain the default: fine-grained locking (page-level or row-level rather than table-level), and physiological logging (log records describe a logical operation on a physical page, e.g., “insert this row at this slot on this page” rather than “the page now looks like this”). Physiological logging makes log records small and replay efficient.

In the open-source era, PostgreSQL added WAL in version 7.1 (2001), replacing an earlier shadow-paging design that was correct but performance-poor. MySQL InnoDB has had a redo log essentially since its inception (early 2000s, originally Innobase Oy). SQLite added WAL mode in version 3.7.0 (2010) as an alternative to its older rollback-journal approach, dramatically improving concurrent read/write performance (SQLite WAL docs).

The pattern’s reach extends well past traditional relational databases. The LSM Tree engines that power most modern key-value stores (LevelDB, RocksDB, Cassandra, ScyllaDB, HBase) all maintain a WAL — typically called a “commit log” — to provide durability for the in-memory MemTable that has not yet been flushed to SSTables. The Raft consensus algorithm (Ongaro & Ousterhout 2014) explicitly models its log as the WAL of a replicated state machine: clients propose commands, leaders replicate the log entries to followers, and the state machine applies entries only after a quorum has durably logged them. etcd and CockroachDB store this Raft log on disk using the same fsync discipline as a traditional database.

3.1 Why Sequential Writes Are So Much Faster

The performance argument for WAL — sequential writes are 100x faster than random writes — deserves a closer look because the factor depends heavily on storage media.

On spinning disks, the asymmetry is enormous. A 7200 RPM disk has a typical random-write IOPS of ~100 (limited by seek time ~10ms) but a sequential write throughput of ~150 MB/s. If each random write is a 4KB page, that’s 100 × 4KB = 400 KB/s of random write bandwidth versus 150 MB/s sequential — a 375x asymmetry. WAL turns small random writes into one large sequential write, capturing essentially the full asymmetry.

On commodity SSDs, the asymmetry is smaller but still real. A typical SATA SSD does 20,000-100,000 random IOPS and ~500 MB/s sequential — random write bandwidth at 4KB is 80-400 MB/s, comparable to sequential. The asymmetry shrinks to 1-5x. The WAL benefit on SSDs is mostly about reducing write amplification at the SSD firmware level: the SSD’s flash-translation layer (FTL) must erase blocks before rewriting them, and small random writes within a block force more block erases than large sequential writes. WAL keeps the database-level write pattern friendly to the FTL.

On NVMe SSDs, especially data-center-grade ones with PLP, random and sequential writes can be nearly indistinguishable in throughput. The historical WAL-write-throughput argument weakens. But the correctness argument — sequential append is the only way to make crash recovery tractable — is unchanged regardless of storage technology. Even on a persistent-memory device where write atomicity at byte level is conceivable, modern systems still implement WAL for the recovery story, not just the speed story.

The takeaway: WAL is the right design even on storage where the speed difference has shrunk, because the simplicity of “log first, recover by replay” is what makes durability tractable.

4. Worked Example

Consider a transaction that inserts three rows and updates one. We trace what hits the log, in what order, and what recovery sees if the machine crashes at various points.

BEGIN;
INSERT INTO orders (id, user_id, status) VALUES (1001, 42, 'pending');
INSERT INTO orders (id, user_id, status) VALUES (1002, 42, 'pending');
INSERT INTO order_items (order_id, sku, qty) VALUES (1001, 'BOOK-1', 2);
UPDATE inventory SET stock = stock - 2 WHERE sku = 'BOOK-1';
COMMIT;

The log records produced (PostgreSQL-style, simplified):

LSN 100: BEGIN xid=82173
LSN 101: INSERT heap.orders pageno=17 slot=4   data={1001, 42, 'pending'}     xid=82173
LSN 102: INSERT btree.orders_pk pageno=88 slot=12 key=1001 ptr=(17,4)         xid=82173
LSN 103: INSERT heap.orders pageno=17 slot=5   data={1002, 42, 'pending'}     xid=82173
LSN 104: INSERT btree.orders_pk pageno=88 slot=13 key=1002 ptr=(17,5)         xid=82173
LSN 105: INSERT heap.order_items pageno=33 slot=2 data={1001,'BOOK-1',2}      xid=82173
LSN 106: UPDATE heap.inventory pageno=9 slot=1 old={'BOOK-1',17} new={'BOOK-1',15}  xid=82173
LSN 107: COMMIT xid=82173

The transaction is durable when LSN 107 is on disk, by Rule 2. Note that the corresponding data pages — page 17 of orders, page 88 of the index, page 33 of order_items, page 9 of inventory — may still be sitting dirty in the buffer pool at this point. That is fine; the log records that describe their state are durable, so recovery can rebuild them.

Crash scenario A: the machine dies at LSN 105 (no commit yet). Recovery’s analysis pass sees BEGIN xid=82173 but no COMMIT or ABORT. The redo pass replays LSNs 101–105 onto whichever data pages need them. The undo pass then walks LSNs 105 → 104 → 103 → 102 → 101 and reverses each, producing on-disk state where transaction 82173 never happened. From the application’s perspective, COMMIT never returned, so the user knows the transaction was never confirmed. Consistent.

Crash scenario B: the machine dies right after LSN 107 hits disk but before any data page has been flushed. Analysis pass sees the COMMIT record. Redo pass replays LSNs 101–106, applying each to the data pages (which are currently the pre-transaction versions read from disk). Undo pass has nothing to undo. After recovery, the data pages reflect all four operations as committed. The application was told COMMIT succeeded; the database delivered on that promise. Consistent.

Crash scenario C: the machine dies after LSN 107 and after a checkpoint that flushed page 17 (containing the new orders) but not page 9 (containing the inventory update). Analysis pass sees the COMMIT. Redo pass walks LSNs 101–106. For LSN 101 it checks page 17’s stored page-LSN: the page on disk has LSN 101 (or higher) recorded, so the redo is skipped — already applied. For LSN 106 it checks page 9: the page on disk has its pre-transaction LSN (say, 67), which is less than 106, so redo applies the inventory update. Database is consistent.

The page-LSN trick — every data page records the LSN of the most recent log record that modified it — is the core of efficient redo. Without it, redo would have to apply every log record blindly and trust idempotence; with it, redo applies only what is actually missing.

4.1 Compensation Log Records and Partial Rollback

ARIES introduced a clever device to make undo crash-safe: the Compensation Log Record (CLR). When undo applies the inverse of a log record (say, undoing an insert), it writes a CLR to the log describing the inverse action. The CLR has a prev_lsn pointer that skips over the original record, so a subsequent crash during recovery does not redo the now-undone change.

The mechanism makes “undo” itself idempotent and crash-safe. If recovery is interrupted partway through the undo phase and restarted, it picks up where it left off rather than redoing already-completed undo work. This is why ARIES can handle crashes during recovery — a feature that simpler logging schemes do not have.

CLRs also enable partial rollback (savepoints in SQL). A transaction can SAVEPOINT s1, do more work, ROLLBACK TO s1, and proceed with the savepoint as the effective starting point. The implementation walks the log writing CLRs for everything done after s1, leaving the transaction in the savepoint state. Without CLRs, partial rollback would be much more complex.

4.2 Why Postgres Has No Undo Log

Unlike InnoDB and Oracle, Postgres does not have a separate undo log. The reason is its Multi-Version Concurrency Control design: when an UPDATE happens, Postgres does not modify the existing row in place; it writes a new tuple and marks the old one as superseded. The old tuple remains in the heap until vacuum reclaims it. This means undo is implicit — to roll back a transaction, Postgres just marks the new tuples as aborted (via the transaction status in pg_xact); the old tuples are still present and immediately visible again.

This design has consequences good and bad. The good: no undo log management, simpler recovery, “writers don’t block readers” comes naturally. The bad: dead tuple accumulation requires aggressive vacuuming, and a long-running transaction’s “snapshot” pins the heap full of obsolete tuples (Postgres’s famous bloat problem). The interaction with WAL is that Postgres’s WAL records carry only the redo information; the undo information is implicit in the heap’s MVCC structure.

InnoDB’s design splits responsibilities differently: the latest version of each row lives in the clustered B-tree (so reads are fast); old versions live in the undo log. Reads that need an older snapshot walk the undo log to materialize the old version. The trade is that InnoDB’s heap stays compact (no MVCC bloat) but the undo log itself can grow large under long-transaction load.

5. Variants and Implementations

System	Log Component	Notes
PostgreSQL	pg_wal/ directory (formerly pg_xlog/ before v10)	16 MB segment files; redo-only design; logical replication and CDC are layered on top via logical decoding.
MySQL InnoDB	ib_logfile0/ib_logfile1 (redo log) + ibdata (undo log)	Full redo+undo; configurable innodb_log_file_size; group commit; fsync controlled by innodb_flush_log_at_trx_commit.
MySQL Server	binlog (statement or row format)	Separate from InnoDB’s redo log; binlog is for replication and CDC, not for crash recovery. The two-log architecture is famously confusing.
SQLite WAL mode	<db>-wal file	WAL replaces (rather than supplements) the rollback journal. Readers consult both the main DB file and the WAL; writers append to the WAL; checkpoints fold WAL contents back into the main file.
RocksDB / LevelDB	*.log files in the database directory	One WAL per column family (RocksDB); flushed when a memtable is flushed to an SSTable; can be disabled per-write for benchmarks (unsafe for production).
Apache Cassandra	commitlog/	Per-node WAL covering all column families; flushed periodically via commitlog_sync (periodic or batch). The node’s MemTable contents are durable via the commit log until flushed as SSTables.
MongoDB WiredTiger	journal files	Configurable journal commit interval (default 100ms); applications can call j: true write concern for synchronous fsync per write.
etcd	Raft WAL	Each Raft log entry is fsynced before being acknowledged; the WAL is the durable form of the replicated state-machine command stream.
CockroachDB	RocksDB WAL + Raft log	Per-range Raft log on top of per-node RocksDB; durability rides on RocksDB’s WAL.
Apache Kafka	the broker log itself	The whole purpose of a Kafka broker is “be a durable WAL with consumers”; segment files, offset indexes, fsync semantics.

The naming confusion between redo log, undo log, WAL, journal, commit log, and transaction log is endemic. They are all variants of the same idea. Concretely:

• Redo log typically means the log that lets you reapply committed changes after a crash (PostgreSQL WAL, InnoDB redo log).
• Undo log means the log that lets you roll back uncommitted changes (InnoDB undo log; PostgreSQL stores this implicitly in the heap via MVCC).
• WAL is the broad architectural term that subsumes both, plus the discipline of writing the log before the data.
• Journal is the same idea, mostly used by SQLite and filesystems (ext4, XFS, NTFS all have journals).
• Commit log is the LSM-tree community’s name for the same thing.
• Transaction log is Microsoft’s name for SQL Server’s WAL.
• Binlog is MySQL’s replication log, separate from the redo log; it captures the SQL-level (or row-level) changes for downstream consumers.

6. Real-World Examples

PostgreSQL streaming replication. The replica connects to the primary and continuously consumes WAL records via the streaming-replication protocol, applying them to its own data files. The primary’s WAL is the only thing the replica sees — it never reads the primary’s tables directly. This is the cleanest example of WAL-as-replication-stream and is what every Postgres “high availability” architecture is built on (Postgres docs — Streaming Replication).

InnoDB’s group commit. When dozens of transactions commit at the same instant, InnoDB batches their log writes into a single fsync, dramatically improving throughput. This optimization, present in essentially every modern DBMS, is one reason commit rates of 50,000+/sec are achievable on commodity SSDs.

etcd’s Raft log. Every write to etcd becomes a Raft log entry, fsynced on a quorum of nodes before the value becomes visible to readers. The Raft log is etcd’s WAL, and etcd uses BoltDB’s mmap for its actual key-value state — but the durability and ordering guarantees come entirely from the WAL.

Cassandra commit log + memtable architecture. Writes append to the commit log (sequential, fast), update an in-memory memtable, and return success. SSTable flushes happen later, asynchronously. On crash, the commit log replays into the memtable. This is functionally identical to a Postgres WAL + buffer pool, just with different terminology.

SQLite WAL mode in mobile apps. Almost every iOS and Android app that stores data locally uses SQLite, and modern best practice is to enable WAL mode (PRAGMA journal_mode=WAL). The reason: WAL mode allows readers and writers to operate concurrently without blocking each other (in legacy rollback-journal mode they did), which dramatically improves UI responsiveness on mobile.

Apache Kafka as a WAL service. Confluent and others have argued explicitly that Kafka is a write-ahead log offered as a network service. The semantics — durable append, monotonic offsets, sequential reads, fsync at the broker — are identical to a traditional database WAL, scaled out across multiple machines via partitioning and replication.

Point-in-time recovery (PITR). Postgres’s archive_command ships completed WAL segments to remote storage (S3, GCS, NFS) as they are filled. Combined with periodic base backups, this enables recovery to any committed state — “give me the database as it was at 14:32:17 yesterday.” The mechanism: restore the most recent base backup before the target time, then replay archived WAL up to the recovery target. PITR is foundational to disaster recovery for nearly every Postgres deployment of consequence and is a recurring source of operational incidents when archive shipping fails silently.

Logical decoding for CDC. Postgres’s logical decoding (since 9.4, 2014) is a higher-level interpretation of the WAL: instead of replaying physical page-level changes, the logical decoder produces row-level events with column names. This is the foundation of Change Data Capture tooling like Debezium. The fact that Postgres exposes its WAL not just as a binary blob for streaming replication but as a structured event stream is a major architectural innovation that enabled the broader CDC ecosystem.

6.1 The InnoDB Two-Log Architecture

A peculiarity of MySQL is that it has two logs: InnoDB’s redo log (ib_logfile0/ib_logfile1) and the server-level binary log (binlog). They serve different purposes — the redo log is for crash recovery within InnoDB; the binlog is for replication to other MySQL servers and for downstream consumers like Debezium.

The two logs are kept consistent via a two-phase commit protocol internal to MySQL: when a transaction commits, it (1) prepares in InnoDB (writing prepare records to the redo log and fsyncing), (2) writes to the binlog and fsyncs it, (3) commits in InnoDB (writes commit record to redo log; this fsync may or may not be required depending on settings). If the server crashes between step 2 and 3, recovery sees the binlog has the commit but InnoDB has only the prepare; recovery completes the InnoDB commit. If the crash is between step 1 and 2, recovery sees InnoDB prepared but binlog empty; recovery rolls back InnoDB.

This two-phase design has been a fertile source of bugs and corner-case tuning controversies for two decades. The settings sync_binlog and innodb_flush_log_at_trx_commit interact in subtle ways; production deployments typically set both to 1 (full durability) and accept the throughput cost. Reducing either creates windows where the two logs can diverge after a crash, with corresponding replication and CDC consequences.

7. Tradeoffs

The fundamental tradeoff WAL makes is two writes per change instead of one: every modification is logged and eventually applied to the data file. This sounds wasteful but is overwhelmingly profitable because the log write is sequential and small while the data file write happens later, in batches, on its own schedule. You buy crash safety and write-throughput gains in exchange for log-storage overhead and the operational complexity of managing log retention and checkpoints.

The fsync cost is genuine. Each transaction that sets synchronous_commit=on (Postgres) or innodb_flush_log_at_trx_commit=1 (InnoDB) waits for an fsync — historically ~1ms on enterprise SSDs, ~10ms on spinning disks, somewhat lower on NVMe with PLP (power-loss-protected) caches. Group commit mitigates this by amortizing one fsync across many transactions. Some workloads choose to relax durability — Postgres synchronous_commit=off returns commits before fsync, accepting the loss of perhaps 200ms of recent commits on crash; InnoDB’s value of 2 for innodb_flush_log_at_trx_commit does similar. These knobs are fundamental to the durability/throughput tradeoff.

A second tradeoff is log storage overhead. The WAL grows until checkpoints retire its older segments. In high-write workloads the WAL can occupy gigabytes of disk space and compete for I/O bandwidth with the data files. Configuring checkpoint frequency well is a significant ops concern: too frequent, and checkpoint I/O storms; too infrequent, and recovery is slow and the WAL grows large.

A third tradeoff is recovery time. The longer the WAL between the last checkpoint and the crash, the longer recovery takes, because every record in that range must be replayed. Aggressive checkpointing reduces recovery time at the cost of write amplification on the data files. Most production systems tune for a few seconds of recovery in the worst case.

A fourth tradeoff is synchronous replication’s effect on commit latency. Streaming replication can be configured as asynchronous (commits don’t wait for replicas; data loss possible on primary failure) or synchronous (commits wait for at least one replica’s confirmation; no data loss but commit latency increases by the replica round-trip). Many high-availability designs choose synchronous replication despite the latency cost specifically to make the WAL durability guarantee survive primary failure. The relationship between WAL fsync semantics, replication acknowledgment, and the application’s view of “commit” is the heart of distributed-database availability tuning.

8. Pitfalls

1. fsync is the silent performance ceiling. Your “10,000 TPS” benchmark with a SATA SSD is suspiciously high until you check whether fsync was actually hitting platters. Many SSDs, especially consumer-grade, lie about fsync — they acknowledge before data hits durable media unless they have a power-loss-protection capacitor. The 2018 paper Protocol-Aware Recovery for Consensus-Based Storage (Alagappan et al., FAST ‘18) and its bibliography are sobering on this. For production durability, use enterprise SSDs with PLP.

2. WAL retention can fill disk silently. A long-running replica or CDC consumer holding open a replication slot prevents Postgres from recycling WAL. The primary’s pg_wal/ grows until disk fills, at which point the database refuses writes and may crash. Mitigation: monitor replication-slot lag (pg_replication_slots); set max_slot_wal_keep_size (Postgres 13+) to bound the retention; alert before slots block recycling.

3. Uncommitted data persisted in the WAL must still be undone. Rule 1 means the data page may have been written to disk while the transaction was in-flight (because the buffer manager evicted the dirty page). If the transaction then aborts, recovery must undo it on disk. This is why ARIES has the undo phase, why InnoDB has an undo log, and why Postgres relies on MVCC to make the row “visible only to the failed transaction.” Forgetting this leads people to underestimate the WAL’s role — they think it’s just “redo for committed work,” but it’s also the source of undo information.

4. Replication lag on followers is bounded by WAL apply rate. If the primary writes WAL faster than the follower can apply it, the follower falls progressively behind. This is the classic “replication lag” failure mode. A single bad query on the primary that produces gigabytes of WAL (large UPDATE without WHERE, big batch insert) can stall replication for hours. Mitigation: use logical replication for selective subset replication; chunk large mutations; alert on pg_stat_replication.replay_lag.

5. Checkpoint storms. If checkpoints are too infrequent, when one finally runs it must flush a huge backlog of dirty pages. The resulting I/O storm can starve foreground workloads. Mitigation: spread checkpoints over time (checkpoint_completion_target in Postgres); tune bgwriter_* parameters so dirty pages drain continuously rather than waiting for the checkpoint.

6. The WAL is the source of truth for replication AND for CDC AND for crash recovery — and all three uses can conflict. A CDC consumer with a stale slot prevents WAL recycling (pitfall 2). A standby’s recovery_min_apply_delay (intentional replication delay for HA scenarios) keeps WAL segments around longer. A long backup that uses pg_basebackup with WAL streaming holds WAL during the backup window. Operations engineers managing all three simultaneously must understand the shared dependency.

7. fsync EIO on a failed write was historically silent on Linux. The “fsyncgate” debate of 2018 (LWN coverage) revealed that some Linux filesystems would clear an EIO error after the first fsync that observed it, leaving subsequent fsyncs returning success despite data loss. PostgreSQL explicitly panic-restarts on EIO now to avoid silent corruption. The lesson: WAL correctness depends on the OS and storage stack honoring fsync semantics, and that has not always been the case.

8.5 The LSM Tree Connection in Detail

A point worth elaborating: LSM Tree engines (RocksDB, LevelDB, Cassandra, ScyllaDB, HBase) all maintain a WAL, even though their on-disk data structure is different from a B-tree. The reason is that the LSM tree’s MemTable — the in-memory buffer that absorbs incoming writes — is not durable. A power failure between “write to MemTable” and “MemTable flush to SSTable” would lose data. The WAL closes that gap.

The LSM-tree WAL discipline is identical to a Postgres WAL: append the change to the log, fsync at commit, then update the MemTable. When the MemTable is flushed to disk as an immutable SSTable, the corresponding WAL segment can be retired (because the SSTable is now the durable record).

This means LSM trees do not eliminate the fsync cost; they just push it to the WAL. The frequent claim “LSM trees are fast because they avoid random I/O” is true for the data I/O but not for the WAL fsync, which is identical to a B-tree engine. RocksDB exposes a disableWAL option for benchmarks that gives spectacular numbers — at the cost of correctness on crash. Production deployments leave the WAL on.

The interaction also means an LSM-tree engine has two logs in some sense: the WAL for crash recovery, and the SSTable history (newer SSTables shadowing older ones) for serving reads at consistent points in time. The two are independent; neither can be eliminated.

8.6 Synchronous vs Asynchronous Replication

Once you accept that the WAL is the canonical record, replication is just “ship the WAL to other nodes.” But there’s a critical timing question: when the primary commits a transaction and returns success to the client, has the WAL reached the replicas?

Asynchronous replication says no — the primary fsyncs locally and returns success immediately; the replica catches up in the background. This is the fastest mode but means a primary failure can lose committed transactions that had not yet shipped.

Synchronous replication says yes — the primary waits for at least one replica’s confirmation before returning success. This eliminates data loss on primary failure but adds the replica round-trip to commit latency (typically 1-10 ms within a region, 50-200 ms cross-region).

Postgres exposes this as synchronous_commit (per-transaction or default) and synchronous_standby_names (which replicas must confirm). Production designs choose based on the application’s data-loss tolerance: payment systems and financial ledgers tend toward synchronous; analytics replicas tend toward asynchronous. The choice is per-transaction in some systems, allowing latency-sensitive transactions to commit asynchronously while data-critical ones wait synchronously.

A subtle point: synchronous replication does not give you cross-replica linearizability without additional bookkeeping. A read on the synchronous replica can still observe an older state if it lands before the replica has applied the transaction whose commit was already acknowledged. True linearizable reads require either reading from the primary or using a consensus protocol (Raft) where reads also go through the log.

9. Common Interview Discussion Points

“How does a database survive a crash?” The expected answer walks through WAL: log records produced before page modifications, fsync at commit, recovery replays the log forward, undoes uncommitted transactions. Mentioning ARIES by name signals serious depth.

“Why are commits limited by fsync?” Because Rule 2 says the commit record must reach durable storage before commit returns. Each fsync is a synchronous round-trip to the storage device. Group commit batches multiple commits into one fsync; that’s the standard mitigation. Discussing the difference between fsync(), fdatasync(), and O_DSYNC opens earns extra points.

“How does this connect to replication?” Replicas consume the leader’s WAL stream — the WAL is the replication channel. Postgres streaming replication is literally “tail the primary’s WAL over a TCP connection.” This is why WAL fsync semantics matter so much: they define what replicas can durably reflect.

“How is this related to LSM trees?” LSM Tree engines use a WAL (often called the commit log) to provide durability for the in-memory memtable. Once a memtable is flushed to an immutable SSTable, the corresponding WAL segments can be recycled. This is functionally identical to a Postgres WAL providing durability for the buffer pool — the data structure being protected differs, the discipline does not.

“What’s the difference between InnoDB’s redo log and the binlog?” A canonical interview question. The redo log provides durability and crash recovery; the binlog provides replication and CDC. They are written at different times (redo on each modification, binlog at commit) and serve different consumers. The two-phase commit between them is a famous source of MySQL bugs and tuning headaches.

“Why does etcd / Raft use a WAL?” The Raft log is conceptually the WAL of a replicated state machine. Each entry must be durably logged on a quorum of nodes before it is committed; the replicated WAL provides consensus’s durability guarantee. The connection to single-machine WAL is exact.

“What happens to the data files when WAL is corrupted?” They become un-recoverable: the database knows the data files are some state but cannot tell which committed transactions are reflected. Production WAL is therefore archived to remote storage (Postgres archive_command), so recovery can replay from a base backup + archived WAL even if local WAL is destroyed. Point-in-time recovery (PITR) is the application of this to “give me the database as of 14:32 yesterday.”

“How much WAL does a typical workload generate?” A useful sanity-check question. As a rough rule of thumb, a Postgres workload with 1,000 small transactions/sec generates ~1-10 MB/s of WAL depending on transaction size; a write-heavy workload at 10,000 TPS generates 50-200 MB/s. WAL volume scales roughly linearly with write throughput plus a constant for the per-transaction overhead. Estimating WAL volume is essential for sizing archive storage, replica catch-up budgets, and CDC consumer scaling.

“How does WAL relate to consensus protocols like Raft?” Raft’s replicated log is the WAL of a state machine. Each state-machine command is appended to the log, replicated to a quorum, then applied. The fsync discipline is identical: a Raft log entry is durable on a quorum before it is considered committed. etcd’s WAL is precisely a Raft log persisted to disk. The mental model “WAL = ordered durable record of state changes” carries over from single-node databases to consensus systems with no semantic change.

“What is the relationship between WAL and snapshots?” A snapshot of the database (e.g., pg_basebackup) captures the data files at some moment but not necessarily a transactionally consistent moment, because the data files are constantly being mutated by ongoing transactions. The WAL is what bridges the gap: take a snapshot, record the WAL position at the start, ship both, recover by restoring the snapshot and replaying WAL forward to the end position. The snapshot + WAL pair forms a consistent backup; neither alone suffices.

“Is the WAL the same as the binlog?” No, in MySQL specifically: InnoDB’s redo log is the WAL (for crash recovery within InnoDB); the binlog is a separate, server-level log used for replication and external CDC. They are written via two-phase commit. Postgres has only one log (the WAL) which serves all purposes (recovery, replication, CDC via logical decoding). This naming inconsistency between the two databases trips up almost everyone the first time.

9.5 Recovery Performance and Parallel Redo

Recovery time depends on three things: WAL volume since the last checkpoint, the speed of the storage during replay, and the degree of parallelism in the redo phase. Single-threaded redo (the historical default in Postgres) replays records one at a time; modern Postgres versions (14+) added some parallel-recovery capabilities via recovery_init_sync_method. InnoDB has had parallel redo for longer, scanning the redo log and grouping records by page so per-page replay can run in parallel.

The ceiling on parallel redo is conflict — two records that touch the same page must be applied in order. In practice, most workloads have sufficient page-level parallelism for parallel redo to help (different transactions touch different pages). The benefit is most pronounced on systems with deep WAL backlog after long downtime, where serial redo would take hours but parallel redo finishes in minutes.

9.1 The Difference Between Logical and Physical Logs

A common follow-up question: what’s the difference between Postgres’s WAL and MySQL’s binlog, given both seem to do similar things?

Physical logs record changes at the storage level — “page 17 of file orders.dat, byte offset 4096, change these 80 bytes from X to Y.” Postgres’s main WAL is physical. Replication via physical WAL produces a byte-for-byte copy of the source database; you cannot use it to replicate to a different database engine, a subset of tables, or a transformed schema. But it is the cheapest possible replication: just copy the bytes.

Logical logs record changes at the row level — “table orders, primary key 777, status field changed from ‘pending’ to ‘shipped’.” MySQL’s row-format binlog is logical. Postgres’s logical decoding (since 9.4) produces a logical interpretation of the physical WAL on the fly. Logical logs can be filtered, transformed, and consumed by heterogeneous targets (different databases, search indexes, message queues). They cost more to produce (the encoding is heavier) and to consume (more complex application logic), but enable all the modern data-integration patterns.

Most production databases now offer both: physical for in-engine replication and HA, logical for CDC and external integrations. The two flow from the same underlying record-keeping; the difference is in what level of abstraction the consumer sees.

9.2 The fsync Lie and What It Means in Practice

A repeated theme in WAL discussions is that fsync correctness is not what most people think. Several known issues:

1. Consumer SSDs without PLP can lose data on power failure even if fsync returned successfully. The drive’s internal cache holds writes that were ack’d to the host; a power loss before the cache flushes loses them. Enterprise SSDs with PLP (super-capacitors that flush the cache on power loss) eliminate this. Always use PLP-equipped drives for production WAL.

2. The Linux fsync error semantics changed over time (fsyncgate 2018). Before kernel 4.13 or so, an fsync that observed an EIO (write error from a previous async write) would return EIO once and then “forget” the error; subsequent fsyncs returned 0. Postgres learned about this in 2018 and now PANICs on EIO to avoid silent corruption.

3. Some filesystems and configurations buffer fsync. Mounting a filesystem with nobarrier (or running ext4 with data=writeback) acknowledges fsync without actually forcing data to durable media, dramatically improving benchmarks but invalidating durability guarantees.

The consequence: production WAL durability requires verifying the entire stack — drive, controller, filesystem, kernel — actually honors fsync. The standard test is to pull power on a running database under load and verify that no committed transactions are lost. Many production systems have never been tested this way, and discover the failure during their first real outage.

9.6 The WAL as a Replication Pipe — A Last Word

The most consequential property of WAL — beyond crash recovery, beyond performance — is that it gives every change in the database a canonical, totally-ordered, durable representation. Every higher-level system that needs to react to database changes — replicas, search indexes, caches, analytics warehouses, audit logs — can subscribe to that representation rather than building its own bespoke change-tracking.

This is why the modern data ecosystem is so dependent on WAL. Logical-decoding-based CDC, streaming replication, point-in-time recovery, time-travel queries (Postgres 16+‘s logical replication of historical data), forensic auditing — all of them are downstream consumers of “the WAL exists and can be read.” Without WAL, each of these would have to be implemented separately and inconsistently. With WAL, they are all variations on “tail the log, transform the records.”

The architectural lesson generalizes beyond databases. Any system that maintains state and might need to be observed by external consumers should consider exposing a WAL-shaped event stream. Modern microservice patterns (event sourcing, CQRS, saga orchestration) are essentially this insight applied at the application level: the application keeps its own log of state transitions, and downstream systems consume that log. The database WAL is the canonical example, but the pattern is universal.

10. See Also

• LSM Tree — uses a WAL (commit log) for memtable durability
• B+ Tree — the page-based storage that databases historically WAL-protect
• Multi-Version Concurrency Control — alternative to logged undo for rollback in Postgres
• Change Data Capture — the modern consumer of the WAL stream, downstream of the database
• Distributed Log System Design — Kafka and friends, structurally identical to a network-attached WAL
• Distributed SQL Database System Design — replicates a WAL across nodes via consensus
• Distributed Key Value Store System Design — every shard has its own local WAL
• Leader-Follower Replication Architecture — the leader’s WAL is the replication channel
• Raft — defines a replicated WAL of state-machine commands
• Two-Phase Commit — uses the WAL to record prepare/commit decisions durably
• ACID Transactions — durability (the D) is delivered by the WAL
• Event Sourcing Pattern — application-level analog of a WAL: append events, derive state
• SWE Interview Preparation MOC
• Major System Designs MOC