Write-ahead log

A write-ahead log (WAL) is an append-only file where a database records every change before applying it to its main data structures. If the process crashes halfway through updating pages on disk, it replays the log on restart and ends up in a consistent state.

#Why it works

• Appending to a log is sequential I/O, the cheapest kind of write. Updating pages in place is random I/O.
• A transaction is durable once its log records are flushed (fsync) to disk. The data pages can be written later, lazily, in batches (checkpoints).
• Replaying the log after a crash redoes committed changes that hadn't reached the pages yet, and ignores uncommitted ones.

1. append "UPDATE accounts id=7 balance 100→70" to the WAL
2. fsync the WAL                      ← the commit is durable from here
3. acknowledge the client
4. later: write the modified page to the data file

#The log does more than crash recovery

• Replication: PostgreSQL streams its WAL to replicas, which apply the same records in the same order (leader–follower replication).
• Point-in-time recovery: a base backup plus archived WAL lets you restore to any moment, such as just before an accidental DELETE.
• Change data capture: tools like Debezium read the log to publish every committed change as an event, which is how many systems feed search indexes and caches reliably (transactional outbox).

#Costs and settings

Every commit waits for an fsync, which is why databases batch commits from concurrent transactions (group commit). Turning off synchronous flushes (synchronous_commit = off in PostgreSQL) makes commits faster at the risk of losing the last few hundred milliseconds of acknowledged transactions on a crash — sometimes acceptable for non-critical data, never for money.

LSM-tree engines use the same idea: the log protects the in-memory memtable until it's flushed (B-trees vs LSM-trees).