What is Write Ahead Logging (WAL)

Write-Ahead Logging (WAL) is a standard technique in databases to ensure data integrity and durability. The core idea is simple: before any change is applied to the actual data files, the change is first written to a log. If the system crashes, the database recovers by replaying the log.

The concept evolved from early research on transaction processing, particularly IBM's System R project in the mid-1970s, which introduced logging, checkpoints, and recovery. The paper that formalized it is ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging, still the reference design for most commercial engines.

The Theory in One Paragraph#

Why a database needs redo, undo, or both comes down to two buffer-management choices. Steal: may the engine flush a dirty page to disk before the transaction commits? If yes, a crash can leave uncommitted changes on disk, so you need undo to remove them. No-force: may the engine commit without flushing the changed pages? If yes, a crash can lose committed changes, so you need redo to replay them. Every serious engine chooses steal + no-force for performance, which is why ARIES prescribes both undo and redo logging. The interesting divergence is where each engine keeps its undo information, and one engine on this list gets away with keeping none at all.

SQL Server#

SQL Server integrates both roles into a single transaction log. Committed work is replayed from the log during recovery (redo); in-flight work at crash time is rolled back from the same log (undo).

The operational surface is the recovery model: FULL keeps the log until it is backed up (enabling point-in-time restore, and enabling the log to eat the disk when backups stall), while SIMPLE truncates at checkpoints. Since SQL Server 2019, Accelerated Database Recovery (ADR) adds a persisted version store that makes both crash recovery and ROLLBACK of a huge transaction near-instant, retiring one of the classic SQL Server pager alerts: the hours-long recovery after killing a runaway update.

Oracle#

Oracle splits the two roles into separate structures: redo logs record every change for replay, and undo segments in the undo tablespace store prior row versions for rollback. The undo segments do double duty: they also serve consistent reads, which is why a long-running query on a busy system can fail with the famous ORA-01555: snapshot too old when the undo it needs has been recycled. Archived redo logs are the basis of Oracle's point-in-time recovery and Data Guard replication.

MySQL (InnoDB)#

InnoDB follows the same split: a redo log (#innodb_redo files) for crash recovery and undo logs in undo tablespaces for rollback and MVCC reads.

Two things bite operators in practice. First, rolling back is not free: ROLLBACK (or killing a long transaction) walks the undo chain and can take as long as the original work, single-threaded, while locks stay held. Second, MySQL actually maintains two logs: InnoDB's redo log for crash recovery and the server-level binlog for replication and point-in-time recovery, kept consistent through an internal two-phase commit. That duo is why sync_binlog and innodb_flush_log_at_trx_commit are always tuned as a pair.

PostgreSQL#

PostgreSQL is the interesting outlier: its WAL is redo-only. There is no undo log anywhere in the system.

It gets away with this because MVCC in Postgres never updates a row in place: an UPDATE writes a new tuple version and leaves the old one. If a transaction aborts, nothing needs to be undone; the transaction is simply marked aborted in pg_xact, and every tuple it wrote is treated as invisible by all future snapshots. Crash recovery replays WAL forward from the last checkpoint and stops. Full stop, no undo pass.

Two practical consequences follow directly:

• ROLLBACK is instant, regardless of how much the transaction wrote. Aborting a 100-million-row update in Postgres returns in milliseconds; the same abort in InnoDB or Oracle grinds through undo. This regularly astonishes people migrating from MySQL.
• The garbage is deferred, not avoided. Those invisible dead tuples sit in the heap until VACUUM reclaims them. Postgres traded synchronous undo for a background cleanup debt, which is the root of its most-criticized operational behavior.

Beyond recovery, Postgres leans on WAL harder than any other engine here: streaming replication, point-in-time recovery, and logical decoding (the basis of CDC tools like Debezium) are all consumers of the same WAL stream.

SQLite#

SQLite's default journaling is the inverse of WAL: a rollback journal that saves the original pages before modifying the database file in place (undo-style). Its optional WAL mode flips to the append-style design: changes go to a separate WAL file, a commit is a record in that file, and a checkpoint later folds the WAL back into the main database.

The reason to flip the switch is concurrency, not durability: in WAL mode readers no longer block the writer and the writer no longer blocks readers, which is why virtually every server-side SQLite deployment (and every "SQLite in production" blog post) runs WAL mode.

Summary#

Why It Matters to You#

WAL sounds like an internals topic until you notice that most day-two database operations are really WAL operations in disguise. Replication is log shipping. Point-in-time recovery is log replay. CDC pipelines tail the log. The fsync/synchronous_commit/innodb_flush_log_at_trx_commit knobs that trade durability for latency are all about when the log hits disk. And the rollback-cost column above decides how gracefully your database survives the day someone kills a six-hour migration halfway through.

Put simply: you can operate a database for years without reading a data file format, but you cannot operate one for a month without touching its log.