Write-Ahead Logs: How Your Database Actually Saves Data
When COMMIT returns, your row usually isn't in the table file yet. It's a few bytes appended to a log. That log is why a database survives power loss, why replicas and point-in-time restores work, and why a forgotten replication slot can fill your disk. Here's how a write-ahead log works.
COMMIT Returned OK. Then the Power Went Out.
Your app runs UPDATE accounts SET balance = balance - 10 WHERE id = 'A', then the matching credit to account B, then COMMIT. The database says OK. Half a second later the machine loses power.
When it comes back, you expect both balances to show the transfer. Not one of them, not neither: both. That's the D in ACID, and most of us never ask how the database keeps that promise.
Here's the surprising part. When COMMIT returned, the database probably hadn't written either account's row to its table file. Those pages were still sitting in memory. What it had written was a short description of the change, appended to the end of a file called the write-ahead log. That log is how the database survives a crash, and it ends up doing a lot of other jobs too.
Why Not Just Write the Table File?
The obvious design is to change the data where it lives: find the page on disk, modify it, fsync, and return. It fails in three ways.
The I/O is random. Databases store data in fixed-size pages (8 KB in PostgreSQL, 16 KB in InnoDB). A transaction that touches an account row, two index entries, and an audit table dirties four pages in four different places on disk. Flushing all of them on every commit means several random writes and several fsyncs per transaction. Even on an SSD, fsync is by far the slowest step in a commit.
Several pages can't be written atomically. The transfer changes two pages. If power fails after page A is written and before page B is, the table shows money that left one account and never arrived in the other. The file system has no way to write two pages as one unit.
A single page can tear. An 8 KB database page is written as several smaller device sectors. If power fails partway through, you can end up with a page that's half old and half new, which is corruption that no row-level logic can repair.
An append-only file of records describing every change to the database. The rule it's named after: a change must be written to the log and made durable before the modified data page is allowed to reach disk. A transaction is committed once its commit record is durable in the log, even if none of its data pages have been written yet. PostgreSQL calls it the WAL, MySQL's InnoDB calls it the redo log, and SQLite has a WAL mode. They all rely on the same idea.
The log fixes all three problems. Appending to the end of one file is sequential I/O, which is the cheapest write a disk can do. A commit needs one fsync of the log no matter how many pages the transaction touched. And because the log fully describes every change, a crash between page writes doesn't matter: on restart, the database replays the log and finishes the job.
The Life of a Committed Write
Change the page in memory
The database loads the page into its buffer pool (its in-memory page cache) and modifies it there. The page is now dirty: memory and disk disagree. Nothing has touched disk yet.
Append a log record
It writes a record describing the change, such as "on page 4412, slot 7, balance went from 100 to 90," into an in-memory WAL buffer. Every record gets a Log Sequence Number (LSN), a position in the log that only ever increases. The dirty page remembers the LSN of the last record that touched it.
On COMMIT, flush the log, and only the log
It appends a commit record, writes the WAL buffer to the log file, and calls fsync. When fsync returns, the transaction is durable. The client gets OK. The dirty data pages are still only in memory, and that's by design.
Write data pages later, obeying the rule
Eventually a background writer or a checkpoint writes the dirty page to its data file. Before doing so, it checks the page's LSN and makes sure the log is flushed at least that far. That check is the "write-ahead" rule: the log always reaches disk before the data it describes.
Try it below. Commit a transaction and watch the data file stay stale while the WAL gets the record. Crash, then recover, and watch the log rebuild what memory lost. Then turn off fsync on commit and do it again.
Some things worth trying in the simulator:
- Commit, crash, recover. The data file never saw the change, but the committed transaction comes back, because recovery redoes it from the WAL.
- Update, flush the log, crash without committing. The uncommitted records are on disk. Recovery reads them and skips them, since there's no commit record. Logs routinely contain uncommitted work, and that's fine.
- Turn fsync off, commit, crash. The client was told the transaction committed. After recovery it's gone.
Checkpoints: Putting a Bound on Recovery
If data pages only ever reached disk lazily, recovery would have to replay the log from the day the database was created. Checkpoints prevent that. A checkpoint writes every dirty page to the data files, then records a marker in the log that says: everything before this point is already in the data files.
Recovery starts from the last checkpoint. That gives you two things: restart time is bounded, and log segments older than the checkpoint can be recycled (unless something else still needs them, which comes up later).
The trade-off is I/O. Checkpoint often and restarts are quick, but you write more, since a page updated a hundred times between checkpoints is written once, while frequent checkpoints write it many times. Checkpoint rarely and writes are cheap, but the WAL grows larger and crash recovery takes longer.
| Frequent checkpoints | Infrequent checkpoints | |
|---|---|---|
| Crash recovery time | Short: little log to replay | Long: minutes of log to replay |
| Write I/O | Higher: hot pages rewritten often | Lower: many updates coalesce into one page write |
| WAL disk usage | Small | Large |
| PostgreSQL extra cost | More full-page images in the WAL (see torn pages below) | Fewer full-page images |
| How to get there in PostgreSQL | Lower checkpoint_timeout (default 5 min) or max_wal_size (default 1 GB) | Raise them |
⚠️ Checkpoint spikes are a real latency problem
Writing gigabytes of dirty pages at once can saturate the disk and stall foreground queries. PostgreSQL spreads the writes across the checkpoint interval (checkpoint_completion_target, default 0.9) for this reason. If your p99 latency has a sawtooth pattern with a five-minute period, look at checkpoints first. In PostgreSQL, log_checkpoints = on will tell you how long each one took and how much it wrote.
Crash Recovery: Redo, and Sometimes Undo
The classic design is ARIES (IBM, 1992), and most engines follow some version of it. After a crash, recovery makes up to three passes:
Analysis
Read the log forward from the last checkpoint to work out which transactions were in flight and which pages might be dirty.
Redo: repeat history
Replay every logged change after the checkpoint, including changes from transactions that never committed, so each page reaches exactly the state it was in at the moment of the crash. The page LSN makes this idempotent: if a page on disk already has an LSN at or past the record, that change is already there and gets skipped.
Undo
Roll back the transactions that never committed, using before-images from the log (or InnoDB's separate undo logs). This is needed because the engine was allowed to write an uncommitted page to disk to free memory, which is called a steal policy.
Engines differ on that third step. InnoDB does undo, as ARIES describes. PostgreSQL skips it. Its MVCC design keeps old row versions next to new ones and records each transaction's fate in a separate commit log (pg_xact), so a row written by a transaction that never committed is simply invisible. There's nothing to undo; vacuum removes the dead rows later. The simulator above is redo-only in the same spirit. It skips uncommitted records instead of applying and then reversing them.
💡 Torn pages need their own fix
Replaying a log record on top of a half-written page doesn't help, because the record assumes the page was intact. PostgreSQL handles this with full_page_writes: the first time a page is modified after a checkpoint, the whole page image goes into the WAL, so recovery can restore it before applying later changes. InnoDB uses a doublewrite buffer: pages are written to a scratch area first, then to their real location. Both fixes cost extra writes, and both exist for the same reason.
The Durability Knob You Can Turn
Everything above assumes that commit waits for fsync. That wait is the most expensive part of a small transaction, so every major engine lets you trade durability for speed. The key detail: turning off the fsync wait costs you durability, not consistency. After a crash you lose the last fraction of a second of commits, but the database is never corrupted, because the log is still written in order and replayed in order.
| Engine | Setting | Default | Relaxed option and what you lose |
|---|---|---|---|
| PostgreSQL | synchronous_commit | on | off: commit returns before fsync; a crash can lose up to 3 × wal_writer_delay (about 600 ms) of commits |
| MySQL InnoDB | innodb_flush_log_at_trx_commit | 1 (flush every commit) | 2: write to OS each commit, fsync about once per second, survives a mysqld crash but not an OS crash. 0: write and fsync once per second |
| SQLite (WAL mode) | synchronous | FULL | NORMAL: still consistent, but the last commits can roll back after power loss |
| Redis (AOF) | appendfsync | everysec | no: the OS decides when to flush; always: fsync every write, slow but safest |
PostgreSQL's version is especially useful because you can set it per transaction. Keep durable commits for payments, and use SET LOCAL synchronous_commit = off for page-view counters and analytics events, where losing half a second of data after a crash doesn't matter.
BEGIN;
SET LOCAL synchronous_commit = off; -- only this transaction
INSERT INTO page_views (path, viewed_at) VALUES ('/pricing', now());
COMMIT; -- returns without waiting for fsync
BEGIN; -- back to the default: on
UPDATE accounts SET balance = balance - 10 WHERE id = 'A';
UPDATE accounts SET balance = balance + 10 WHERE id = 'B';
COMMIT; -- waits until the WAL is on diskBefore reaching for these settings, remember that engines already amortize fsync with group commit. When many transactions commit at around the same time, one fsync of the log covers all of their commit records. That's why durable commit throughput scales with concurrency much better than a one-fsync-per-commit calculation suggests. Under real load, durable commits often cost less than you'd expect.
🔴 fsync is only as honest as your storage
The whole design assumes that when fsync returns, the bytes will survive power loss. Consumer drives with volatile write caches, some virtualized disks, and misconfigured RAID controllers can acknowledge a flush that hasn't reached stable storage. Server-grade SSDs have power-loss protection for this reason. There's also a software side. In 2018, PostgreSQL developers found that on Linux, a failed fsync could clear the error, so a retried fsync would report success even though the data had never been written ("fsyncgate"). PostgreSQL now panics and recovers from the WAL instead of retrying. Durability is a property of the whole stack, not of one setting.
The Log Is the Database
Once every change exists as an ordered stream of records, you can do much more with that stream than crash recovery. A lot of features you use every day are the WAL put to other uses:
| Feature | How it uses the WAL |
|---|---|
| Streaming replication | Ship WAL records to a replica, which replays them continuously, the same way it would during crash recovery |
| Point-in-time recovery | Restore a base backup, then replay archived WAL up to a chosen timestamp, for example just before someone ran DELETE without a WHERE clause |
| Change data capture | Logical decoding turns WAL records into row-level change events (Debezium, logical replication, outbox relays) |
| Crash recovery | The original job: redo from the last checkpoint |
This is also why replica lag is measured in WAL position: a replica is a database that's always recovering, a few records behind its primary.
⚠️ The WAL can fill your disk
The database keeps WAL segments that anyone might still need. A replica that's offline, an abandoned logical replication slot, or a failing WAL archive command all pin segments, and the WAL keeps growing until the disk is full and the database stops accepting writes. On PostgreSQL, monitor pg_replication_slots for inactive slots and the size of pg_wal, and consider max_slot_wal_keep_size (PostgreSQL 13+) so that one stuck consumer can't take down the primary.
What This Means for Your Decisions
If the server crashed right after COMMIT returned, could you afford to lose this transaction?
Key Takeaways
- A commit is durable when its log record is durable, not when the table file is updated. Data pages reach disk lazily, and that's intentional.
- The write-ahead rule is the whole trick: log first, data later. Sequential log appends replace scattered random page writes, and one fsync covers the entire transaction.
- Checkpoints put a bound on recovery. They trade extra write I/O for faster restarts and less WAL on disk, and they can cause periodic latency spikes.
- Recovery redoes history, then removes what never committed: undo in InnoDB, MVCC invisibility in PostgreSQL. Uncommitted records in the log are normal.
- Relaxing fsync on commit loses recent commits, not consistency. Use it deliberately, per transaction where possible, for data you can afford to lose.
- fsync is a promise your hardware has to keep. Durability depends on the drive, the controller, and the kernel, not just the database setting.
- The WAL powers replication, point-in-time recovery, and CDC, and anything consuming it can pin segments until the disk fills. Monitor replication slots.
References
- PostgreSQL documentation: Write-Ahead Logging, plus the WAL configuration chapter on checkpoints
- Mohan et al., ARIES: A Transaction Recovery Method (ACM TODS, 1992), the paper most recovery designs descend from
- MySQL: innodb_flush_log_at_trx_commit
- SQLite: Write-Ahead Logging
- PostgreSQL wiki: Fsync Errors, background on fsyncgate
- Database Transactions: The Isolation Level You're Probably Not Setting, which covers the I in ACID