# What is Write Ahead Logging (WAL) > What write-ahead logging is, the steal/no-force theory behind it, and how SQL Server, Oracle, MySQL, PostgreSQL, and SQLite each implement redo and undo differently. Tianzhou | 2026-07-12 | Source: https://www.bytebase.com/blog/write-ahead-logging/ --- | Update History | Comment | | -------------- | ------- | | 2024/12/06 | Initial version. | | 2026/07/12 | Corrected the PostgreSQL undo description, added the steal/no-force framing and rollback-cost comparison. | 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](https://en.wikipedia.org/wiki/IBM_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](https://cs.stanford.edu/people/chrismre/cs345/rl/aries.pdf), 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](https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts) (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](https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html). 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](https://www.sqlite.org/wal.html) 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 | DBMS | Redo | Undo | Rollback cost | | ---------- | ---------------------- | --------------------------------------------- | ----------------------------------- | | SQL Server | Transaction log | Same transaction log (version store with ADR) | Proportional; near-instant with ADR | | Oracle | Redo logs | Undo segments (also serve consistent reads) | Proportional to work done | | MySQL | InnoDB redo log | Undo tablespaces (also serve MVCC reads) | Proportional, single-threaded | | PostgreSQL | WAL | None; aborted tuples left dead for VACUUM | Instant | | SQLite | WAL file (in WAL mode) | Rollback journal (in default mode) | Proportional to journal size | ## 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.