Write-ahead logging (WAL)

A write-ahead log (WAL) is an append-only file in which a database records every change before it changes the data itself. Because the log reaches disk first, a commit costs one sequential write instead of many random page writes, and after a crash the database rebuilds whatever was lost by replaying the log. The same log then doubles as the feed for replicas, point-in-time backups and change data capture.

Picture it first: the scorer’s book

At a cricket ground, the official scorer writes down every ball the moment it is bowled: “over 14, ball 3, four runs”. A crew updates the giant scoreboard when they can, often a few balls behind.

Nobody worries when the board lags or goes dark, because the scorebook has every ball in order. Each panel carries a tag, “correct up to ball 14.2”, so the crew knows which balls to apply. Every few overs the crew catches up and the scorer draws a line: nothing above it needs replaying. When the third umpire overturns a wicket, the scorer erases nothing; he writes a correcting entry. And radio commentators copy the scorebook, not the board.

Map it back: the scorebook is the write-ahead log, the ball number is the log sequence number (LSN), the panels are data pages, the tag on each panel is the page LSN, the line is a checkpoint, the correcting entry is a compensation log record, and the commentators are replicas and change-data-capture consumers.

The rule, precisely

The write-ahead rule has two halves:

1. Log before data. A changed page may be written to disk only after every log record describing its changes is durable. PostgreSQL’s buffer manager flushes the WAL up to a page’s LSN before writing the page.
2. Log before acknowledgement. A transaction has committed when its commit record, and everything before it, is durable. Only then may the database reply “COMMIT ok”.

Nothing else has to be on disk at commit. Reads and writes walks through the full write path and torn-page protection; this page is about the log itself.

Why is a log so much cheaper? Say a wallet transfer changes four pages: two wallet rows, a ledger page and an index page. Writing them at commit means four random 8 KB writes, 4 × 8 KB = 32 KB, across files that each need a flush. The log needs four records of roughly 100 bytes plus a commit record: about 500 bytes, appended to one file and flushed once, 64× fewer bytes. And when a page is finally written, that one write absorbs every commit that touched it meanwhile.

Steal and force: what the log buys the buffer pool

The ARIES paper by C. Mohan and colleagues (1992), the blueprint for most modern recovery code, frames a buffer manager with two questions:

• Steal? May a page changed by an uncommitted transaction be written to disk, say to free a buffer?
• Force? Must every page a transaction changed be on disk before it commits?

If uncommitted changes can reach disk (steal), recovery may have to undo them. If committed changes may exist only in memory at the crash (no-force), recovery must redo them.

PostgreSQL is a twist. It steals too, yet keeps no undo log: an uncommitted row version on disk is harmless because its transaction is never marked committed, so no snapshot sees it. MVCC turns undo into a visibility check.

Inside the log: records and LSNs

What a record holds

ARIES lists the fields; real engines vary the details:

• LSN, a number that only grows. In PostgreSQL it is a byte offset into the WAL, printed like 3/9F2A1C08, so subtracting two LSNs gives the bytes between them.
• Transaction ID and prevLSN, the same transaction’s previous record: a backward chain that rollback follows.
• Page ID, the page the change applies to.
• Redo data to reapply the change and, in engines that undo through this log, undo data to reverse it.

PostgreSQL keeps WAL in pg_wal as 16 MB segment files of 8 kB pages, each record protected by a CRC-32C checksum. InnoDB keeps redo in #innodb_redo as 32 files, each 1/32 of innodb_redo_log_capacity, with the checkpoint LSN in a file header (MySQL’s redo log page).

A transfer in an ARIES-style log might look like this (a simplified illustration, not a real dump):

LSN   txn  prevLSN  type    page         redo                   undo
1000  T42  -        UPDATE  wallets/p7   asha 90000 → 40000     asha 40000 → 90000
1072  T42  1000     UPDATE  wallets/p12  bilal 1000 → 51000     bilal 51000 → 1000
1150  T42  1072     INSERT  ledger/p88   slot 14 = (tr_9a1, …)  remove slot 14
1236  T42  1150     COMMIT  -            -                      -

The page LSN makes redo idempotent

Every data page stores the LSN of the last record applied to it, its page LSN. That one number does two jobs. It enforces the rule (a page with page LSN 260 may be written only once the log is durable through 260), and it makes replay safe. During recovery a record is applied only if the page’s LSN is lower than the record’s; otherwise the change is already there and the record is skipped.

Physical, logical or physiological

What goes into the redo data has three classic answers:

• Physical: “these bytes of page 7 are now …”, or a whole page image. Replay is mechanical, but records are large and tied to the page layout. PostgreSQL’s full-page images are physical.
• Logical: “run this operation”, such as a statement or a row change. Tiny and readable by other software, but replay must be deterministic and in order. MySQL’s binary log is logical.
• Physiological: “on page 7, set slot 3’s balance to 40000”: physical to a page, logical inside it, so the page can still be reorganised. ARIES calls it page-oriented redo; PostgreSQL’s ordinary records and InnoDB’s redo (“encoded in terms of records affected”, says the manual) work this way.

Redo only, or redo plus undo?

PostgreSQL’s WAL is redo-only. Its documentation calls WAL recovery roll-forward, “also known as REDO”. Row versions are stamped with a transaction ID whose status lives in pg_xact. A transaction cut off by a crash never gets a commit status, so its versions stay invisible; the source code notes that an abort record never needs flushing, since a crashed transaction is presumed aborted. VACUUM reclaims the dead versions later: trivial rollback, paid for with cleanup.

InnoDB keeps redo and undo. It updates rows in place, so it first writes the old values to an undo log, which serves rollback and MVCC reads. Undo logs live on ordinary pages in undo tablespaces (two by default), so changes to them are redo-logged too; the manual exempts only temporary-table undo, which crash recovery never needs. After a crash, redo rebuilds data and undo pages alike, then undo rolls back what never committed.

SQLite ships both styles: its rollback journal saves original pages (undo); its WAL mode appends changed pages (redo).

Crash recovery, the ARIES way

ARIES recovers in three passes, starting from the last checkpoint, whose record lists the running transactions and the dirty pages, each with its recLSN: the first change that might not be on disk yet.

1. Analysis. Scan forward from the checkpoint to the end of the log, updating both tables. Transactions with a commit record are winners; the rest are losers. The smallest recLSN becomes RedoLSN.
2. Redo: repeat history. Scan forward from RedoLSN and reapply every record whose LSN is greater than its page’s LSN, for all transactions, losers included. The database is now exactly as it was at the crash, and every page LSN is truthful, which the next pass relies on.
3. Undo. Roll back the losers in one backward sweep, always taking the highest LSN still to undo and following prevLSN links. Each reversal is logged as a compensation log record (CLR), which is redo-only and carries an UndoNxtLSN pointing at the next record to undo.

CLRs make a crash during recovery boring: the next restart redoes the CLRs like any record, and undo resumes from the latest UndoNxtLSN instead of reversing a change twice. ARIES also checkpoints without pausing work (fuzzy checkpoints): a begin record, then an end record holding both tables, while transactions run.

What PostgreSQL and InnoDB actually do

PostgreSQL reads pg_control to find the latest checkpoint record, takes its redo point, and replays WAL forward to the end, restoring torn pages from their full-page images. There is no undo pass. On a primary, clients cannot connect until replay finishes.

InnoDB discovers tablespaces, then applies redo from the checkpoint LSN before accepting connections. Then it opens for business while a background thread rolls back transactions that were neither committed nor in XA PREPARE state; new sessions may wait on their locks. The manual warns that rollback can take three or four times as long as the transaction ran: a batch update killed after 10 minutes may need 30 to 40 to undo.

For both, recovery time is dominated by how much log lies after the last checkpoint, plus, in InnoDB, unfinished transactions.

Checkpoints: bounding the replay

A checkpoint flushes dirty pages so that everything before some LSN is safely in the data files; older log can then be recycled, and recovery starts there.

PostgreSQL checkpoints every checkpoint_timeout (default 5 minutes) or when WAL approaches max_wal_size (default 1 GB, a soft limit), whichever comes first, spreading the page writes over checkpoint_completion_target of the interval (default 0.9). The redo point is where the checkpoint started, since pages changed during the slow flush may not have reached disk. Checkpointing often means less WAL to replay, but more page flushes and more full 8 KB page images (one per page per checkpoint); checkpointing rarely is the reverse.

The arithmetic, for an illustrative workload: 10 MB/s of ordinary WAL, 50,000 hot pages changed in every interval (50,000 × 8 KB = 400 MB of images per cycle) and replay at 100 MB/s. The worst crash lands just before a checkpoint finishes, so recovery starts from the previous redo point: about 1 + 0.9 = 1.9 intervals of WAL.

This workload writes 3.4 GB of WAL per 5 minutes, far above the 1 GB default, so with defaults it would checkpoint on WAL volume about once a minute, quietly paying the first row’s image cost. Size max_wal_size from your WAL rate, pick checkpoint_timeout from your recovery budget, and measure replay speed in a restore drill.

InnoDB has the same dial in a different shape. Its redo log has a fixed size, innodb_redo_log_capacity, 100 MB by default since MySQL 8.0.30, and the checkpoint can never lag further behind. At 20 MB/s of redo that is 100 MB ÷ 20 MB/s = 5 s of headroom. When the log fills, InnoDB performs a sharp checkpoint, flushing in a hurry, which the manual calls a temporary drop in throughput. Give it more capacity (resizable at runtime) and page-cleaner bandwidth:

-- MySQL 8.4: redo log size and how far the checkpoint lags behind
SET GLOBAL innodb_redo_log_capacity = 8589934592;   -- 8 GB
SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_%lsn';     -- current, flushed and checkpoint LSNs

Current LSN minus checkpoint LSN is the checkpoint age: the redo a crash right now would replay.

Group commit: sharing the flush

Assume one log flush takes 2 ms on your volume (measure yours with pg_test_fsync). A session that waits for its own flush commits at most 1,000 ms ÷ 2 ms = 500 times per second, however fast the CPU.

Group commit breaks that ceiling. While one flush runs, other transactions append commit records to the log buffer and wait; the next flush carries all of them. If 40 commits pile up per 2 ms flush, throughput becomes 40 × 500 = 20,000 commits per second from the same disk. You pay in latency: a commit that just missed a flush waits for it to end and then for its own, 2 to 4 ms instead of 2.

PostgreSQL gets this for free: sessions queue behind the running flush, a “gangway effect” its docs call significant at high client counts. commit_delay makes the flusher sleep a few microseconds so more records join, but only when at least commit_siblings (default 5) other transactions are active; start at half of pg_test_fsync’s time for one 8 kB flush. MySQL groups binlog commits too, so sync_binlog = N counts commit groups.

Group commit needs concurrency: a single-threaded loader committing every row still pays one flush per row, so batch rows or, where losing the last few is acceptable, commit asynchronously (ACID transactions covers those knobs).

The log as a product

A log of every change, in order, is useful far beyond crash recovery. Each reader goes at its own pace and remembers its own position.

Streaming replicas

PostgreSQL’s physical replication streams WAL records to a standby that replays them continuously, exactly as crash recovery would, and so becomes a byte-for-byte copy (same major version and platform required). Lag, failover and synchronous standbys are covered in data replication.

Point-in-time recovery

Keep a base backup (a copy of the data directory taken while the server runs) plus every WAL segment since, and you can rebuild the database as of any moment in between: restore the backup, replay WAL up to a target. The backup need not even be consistent, because replaying the WAL repairs it. archive_command (or an archive_library module) copies each completed segment somewhere safe; on the restore server, restore_command fetches them back:

# primary: postgresql.conf
wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/ship-wal %p %f'   # must exit 0 only once the copy is durable
archive_timeout = 60                                # switch segments at least once a minute

# restore server: postgresql.conf, plus an empty recovery.signal file
restore_command = '/usr/local/bin/fetch-wal %f "%p"'
recovery_target_time = '2026-10-27 14:29:00+05:30'  # one minute before the bad DELETE

archive_timeout bounds how long a commit on a quiet server sits unarchived, since only completed segments ship. In disaster recovery terms, the backup sets restore time and the archive sets data loss.

Logical decoding and CDC

With wal_level = logical, PostgreSQL decodes WAL into row changes through a replication slot, which is how change data capture tools stream changes into Kafka, a search index or a warehouse without dual writes. Two properties matter. A slot’s position is saved only at checkpoints, so after a crash it may resend changes: consumers must be idempotent. And a slot persists across crashes and knows nothing about its consumer, so it keeps WAL forever if the consumer disappears.

MySQL: two logs and an internal two-phase commit

MySQL has two logs that are easy to confuse:

A commit must land in both, so MySQL runs an internal two-phase commit, always on in 8.4: InnoDB prepares the transaction, the server writes and syncs the binlog, then InnoDB commits. After a crash, the server scans the latest binlog, has InnoDB commit every prepared transaction found there, rolls back the rest, and truncates the binlog to its last complete transaction. The binlog decides, so replicas never receive a transaction the source rolled back.

That needs both logs flushed at every commit: innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1, the “double 1” default. Relax sync_binlog and a power cut can drop committed transactions from the binlog, so replicas silently diverge.

WAL beyond relational databases

SQLite. In WAL mode (PRAGMA journal_mode=WAL;) changed pages are appended to a -wal file and a commit is a special commit record; the database file is untouched until a checkpoint copies pages back, automatically once the WAL reaches 1,000 pages. Each reader remembers an “end mark” in the WAL and sees a consistent snapshot, so readers don’t block the writer and the writer doesn’t block readers, though there is still one writer at a time. With PRAGMA synchronous = NORMAL only checkpoints flush, so a power cut can lose recent commits; FULL syncs every commit. WAL mode needs shared memory (no network file systems), and readers that never leave a gap stop checkpoints finishing, so the WAL grows without bound.

LSM engines. RocksDB writes every update to the WAL and the memtable; after a crash the WAL rebuilds the memtable. A WAL file is deleted once every column family has flushed past its last sequence number, and max_total_wal_size forces flushes so a rarely-written column family cannot pin old WALs. Cassandra’s commit log works the same way (see the LSM write path).

The log as the source of truth. Raft keeps a replicated log of commands that every server applies in order (consensus and leader election). Kafka makes the log the product: append-only partitions that consumers read at their own offsets. Amazon Aurora ships only redo to its storage tier, which builds pages itself (database architectures).

Four logs side by side

Going deeper: how WAL goes wrong in production

The WAL disk fills up

If the file system holding pg_wal fills, PostgreSQL does a PANIC shutdown. Nothing committed is lost, but the database stays down until you free space. Never delete files from pg_wal by hand; find out why WAL is being kept (usually one of the next two sections) and fix that.

A forgotten replication slot

A slot holds WAL until its consumer confirms it, and max_slot_wal_keep_size defaults to -1, unlimited (replication settings). Retire a CDC pipeline but not its slot, and at 10 MB/s of WAL the primary keeps 10 MB/s × 86,400 s ≈ 864 GB a day. A logical slot also blocks vacuum of old catalog rows, in extreme cases risking transaction ID wraparound.

-- PostgreSQL: how much WAL each slot is holding back
SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Archiving that fails quietly

PostgreSQL trusts archive_command’s exit status completely. Non-zero means “not archived”, so the server retries forever while segments pile up in pg_wal, invisible to users until the disk fills. The opposite bug is worse: a command that exits 0 without a durable copy (the documentation’s own example is /bin/true) lets the server recycle WAL your archive never got, and you find the gap during a restore. Watch pg_stat_archiver (failed_count, last_failed_time) and run restore drills.

Slow fsync caps the commit rate

Network-attached cloud volumes can take milliseconds per flush: at 3 ms, one session tops out at 1,000 ÷ 3 ≈ 333 commits per second, with the CPU idle. Fixes, in order: batch work into fewer transactions, commit concurrently so group commit helps, give the WAL a low-latency volume, and only then consider asynchronous commit for data you can afford to lose.

WAL spikes after every checkpoint

After each checkpoint, every page’s first change writes a full 8 KB image, so WAL volume (and replication and archive bandwidth) jumps, then decays. The images repair torn pages, so keep full_page_writes on; checkpoint less often, or set wal_compression (off by default; lz4 and zstd when compiled in). You can watch it in psql:

SELECT pg_current_wal_lsn() AS before \gset
UPDATE wallets SET balance_paise = balance_paise + 100 WHERE user_id = 'asha';
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before')) AS wal_written;
-- First run after a checkpoint: roughly 8 kB (a full page image). Run again: a few hundred bytes.

Cheat sheet

How to say it in the interview

“A database doesn’t write data pages at commit. It appends a small record of each change to the write-ahead log and fsyncs the log before acknowledging, so a commit is one sequential flush that group commit lets concurrent transactions share. Every record has an LSN and every page stores the LSN of its last change, so after a crash the engine replays from the last checkpoint and skips what’s already on disk; InnoDB then undoes unfinished transactions, PostgreSQL just never marks them committed. Checkpoint frequency trades recovery time against extra writes. The same log feeds replicas, point-in-time recovery and CDC slots, which I’d cap and monitor.”

Why is writing to a log faster than writing the pages? The log is a sequential append of ~100-byte records; pages are 8 or 16 KB scattered across files. A commit touching four pages flushes about 500 bytes instead of 32 KB of random writes.

How does a database recover after a crash? From the last checkpoint it replays the log forward, applying a record only if the page’s LSN is older, which repeats history up to the crash. Then transactions without a commit record are undone with undo data and compensation records (InnoDB), or simply stay invisible as aborted (PostgreSQL).

What’s the difference between the redo log and the binlog in MySQL? The redo log is InnoDB’s fixed-size physical log for crash recovery; the binlog is the server’s logical event log for replication and point-in-time recovery. An internal two-phase commit keeps them consistent, and after a crash the binlog decides which prepared transactions commit.

Why did our Postgres disk fill up with WAL? Something still needs the old segments: usually an inactive replication slot (a CDC connector or standby that went away) or a failing archive_command that keeps retrying. Check pg_replication_slots and pg_stat_archiver, fix or drop the culprit, and cap slots with max_slot_wal_keep_size.

Where to go next

• ACID transactions: durability knobs, fsyncgate and the crash after COMMIT.
• Reads and writes: the B-tree and LSM write paths and torn-page protection.
• MVCC and vacuum and bloat: why PostgreSQL needs no undo log, and the cleanup that costs.
• Data replication: shipping the log to followers, lag and failover.
• Change data capture: turning the log into an event stream.
• Disaster recovery: backups, archived WAL, RPO and RTO.
• Kafka and consensus: the log as the system of record.

References and further reading

1. PostgreSQL documentation: Write-Ahead Logging (WAL)postgresql.orgThe core rule (data files change only after the WAL records describing the change are flushed), roll-forward REDO recovery, one fsync committing many transactions, and WAL as the basis of point-in-time recovery.
2. PostgreSQL documentation: WAL internalspostgresql.orgLSNs as byte offsets into the WAL, 16 MB segment files of 8 kB pages, and recovery starting from the checkpoint record named in pg_control.
3. PostgreSQL documentation: WAL configurationpostgresql.orgCheckpoint triggers and defaults (5 min, 1 GB, 0.9), full-page images after each checkpoint, commit_delay and the natural group commit, and why failed archiving or replication slots make pg_wal grow.
4. PostgreSQL documentation: Continuous archiving and point-in-time recoverypostgresql.orgThe archive_command exit-status contract, base backup plus WAL replay, and the PANIC shutdown when the file system holding pg_wal fills up.
5. PostgreSQL source: the transaction system README (src/backend/access/transam)github.comThe page-LSN check before a dirty page is written, LSN comparison during replay, first-change full-page images, and why an abort record never needs flushing.
6. ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging (Mohan et al., ACM TODS, 1992)web.stanford.eduSteal and force definitions, log record fields (PrevLSN, PageID, UndoNxtLSN), page LSNs for idempotent redo, fuzzy checkpoints, and the analysis, redo (repeating history) and undo passes with compensation log records.
7. MySQL 8.4 Reference Manual: InnoDB recoverydev.mysql.comRedo applied before connections are accepted, then incomplete transactions rolled back by a background thread, which can take three or four times as long as they ran.
8. MySQL 8.4 Reference Manual: The binary logdev.mysql.comWhat the binlog is for, sync_binlog = 1 as the safe default, and the always-on internal two-phase commit that keeps the binlog and InnoDB consistent after a crash.
9. SQLite: Write-Ahead Loggingsqlite.orgRollback journal versus WAL mode, readers that don’t block the single writer, automatic checkpoints at 1,000 pages, and checkpoint starvation under constant readers.
10. RocksDB wiki: Write Ahead Log (WAL)github.comEvery write goes to the WAL and the memtable, a WAL is deleted once all column families have flushed past it, max_total_wal_size, and tailing WALs for replication.