Write-Ahead Log Explained: How Databases Survive Crashes

A write-ahead log is the reason you can pull the power cord on a database server and get your committed data back. The database writes a description of every change to an append-only log and flushes that log to disk before it touches the real data files. If it crashes halfway through, it replays the log on restart. That one rule, log first and data second, is the core of durability in PostgreSQL, MySQL’s InnoDB, SQLite in WAL mode and most other serious databases.

I’ll use PostgreSQL for the examples, since its documentation is explicit about the mechanism, but the idea carries across engines.

The rule in one sentence

PostgreSQL’s documentation puts it this way: changes to data files “must be written only after those changes have been logged”, meaning after the WAL records describing them have been flushed to permanent storage. Everything else follows from that.

Take a simple UPDATE accounts SET balance = balance - 100 WHERE id = 7. Here’s what happens:

1. The database changes the page in memory (the buffer cache). The page is now “dirty”.
2. It appends a WAL record describing the change to the log buffer.
3. On COMMIT, it flushes the WAL up to that record to disk and tells the client the commit succeeded.
4. Later, in the background, a checkpoint writes the dirty page to its place in the data file.

Notice what’s missing: at commit time, the data file isn’t touched. The client got its “committed” answer while the actual table page still sits in RAM. That sounds reckless until you see recovery.

What recovery does after a crash

Say the server loses power between steps 3 and 4. The data file still holds the old balance. The WAL on disk holds the record of the change. On restart, the database finds the last checkpoint, reads the log forward from there and re-applies every change that hadn’t reached the data files. The documentation calls this roll-forward recovery, or REDO.

Transactions that hadn’t committed when the power died have log records too, but no commit record. Recovery doesn’t make their effects visible, so they vanish. You never see a half-finished transfer where the money left one account and never arrived in the other.

Why this is faster, not slower

Intuition says doing extra writes should cost you. In practice WAL reduces the disk work done at commit, for two reasons from the PostgreSQL docs:

• Only the log needs flushing. Without a log, a commit would need every data page it changed flushed to disk. Those pages are scattered all over the files. The log is written sequentially, and sequential flushes are much cheaper than random ones.
• One fsync can cover many transactions. When many small transactions commit at once, a single flush of the log file may commit all of them. This is called group commit, and it’s a big part of why a busy database doesn’t drown in fsync calls.

The sequential-versus-random gap is largest on spinning disks. On SSDs it’s smaller but still there, and the group-commit benefit stays the same.

Checkpoints keep the log from growing forever

If recovery had to replay the log from the beginning of time, restarts would take days. A checkpoint is a bookmark: the database flushes all dirty pages up to a certain log position, records that position, and everything before it is no longer needed for crash recovery. Older WAL segments can then be recycled or archived.

Checkpoint settings are a real tuning lever. Checkpoint too rarely and recovery after a crash takes longer and the WAL directory grows. Checkpoint too often and you pay for write bursts and extra full-page writes. If you see periodic latency spikes on a write-heavy Postgres, look at the checkpoint logs before blaming the query. Our guide to Postgres query optimization for application developers covers the query side of that investigation.

Three things the same log gives you for free

Because every change passes through the log, the log becomes a general-purpose change record.

Point-in-time recovery. The PostgreSQL docs describe it directly: archive the WAL, and you can restore an earlier physical backup and replay the log “just as far as the desired time”. That’s how you undo a bad DELETE from 2:14 p.m. without losing everything after your last nightly backup.

Streaming replication. A replica connects to the primary, receives the WAL stream and replays it. Replicas are, in effect, databases that are permanently in recovery mode.

Change data capture. With logical decoding, tools read the WAL and turn row changes into events. That’s what Debezium does, and we walk through it in change data capture explained. It’s also the safest foundation for the outbox pattern, because the event feed and the transaction commit can’t disagree.

The settings that matter most

Setting	What it controls	Trade-off
synchronous_commit	Whether a commit waits for its WAL flush	off lowers latency but can lose the last few commits in a crash
wal_level	How much detail goes into the log	logical enables CDC, at the cost of larger WAL
max_wal_size	How much WAL can accumulate between checkpoints	Higher means fewer checkpoints but longer crash recovery
fsync	Whether PostgreSQL forces data to disk at all	Turning it off risks corruption. Don’t.

The one worth understanding is synchronous_commit = off. It can be set per transaction, which makes it a useful tool: telemetry inserts, session heartbeats and other data you can afford to lose a second of can skip the flush wait, while payments keep the default. The database stays consistent either way, because the loss is whole transactions, never torn ones.

Where WAL bites in practice

• Disk fills up. A replica that falls behind, or an abandoned replication slot, makes the primary hold on to WAL it can’t recycle. This is a classic cause of a full disk on a production primary. Monitor slot lag.
• Bulk loads create a lot of log. A large UPDATE rewrites rows and writes a matching volume of WAL. Batch it, and think about how it interacts with connection pooling and replicas that must replay it.
• Durability depends on the disk lying less than you’d think. If a storage layer acknowledges a flush it hasn’t performed, no database can save you. Check what your cloud volume or RAID controller promises about write caches.

What to remember

The write-ahead log turns “did my write survive?” into a question about one sequential file. Commit means the log record is on disk. Recovery means replay the log. Replication, PITR and CDC mean someone else reads the log. When you next tune a database or design a pipeline around one, ask where the log is, who reads it, and what happens when it can’t be recycled.