The problem: committed means committed
When Snip's database says a new link is committed, Snip hands out the short code. If the power fails a millisecond later, the link must still exist when the server comes back. That's durability.
The obvious approach, writing the changed rows straight into their places in the data files before answering, has two problems. It's slow: one transaction may touch pages scattered across several files and indexes, and random writes are the expensive kind. And it's fragile: a crash halfway through writing an 8 KB page can leave it half old, half new.
Write the log first
The write-ahead log (WAL) fixes both. Every change is first described in a record appended to the end of a log file. The rule is in the name: the log is written before the data.
- 01
Change in memory
Rows and index pages are modified in memory, in the buffer cache.
- 02
Append to the WAL
A record describing the changes is appended to the log.
- 03
Flush the log
The log is forced to disk (fsync). Sequential, so fast.
- 04
Reply COMMIT
Now it's durable: the log alone can recreate the change.
- 05
Later: checkpoint
Changed pages are written to the data files in the background.
After a crash, the database starts from the last checkpoint (a point where every change up to there was in the data files) and replays the log from there, redoing every committed change. Changes from transactions that never committed are discarded. Half-written pages are repaired from the log too.
Why it's fast: the commit waits only for a sequential append to one file, the cheapest kind of disk write. The scattered, random page writes happen later, in the background, and many commits' worth of changes to the same page are written once. Busy databases also group commit: one flush covers every transaction that committed in the same millisecond or so.
One log, three more jobs
Once you have an ordered record of every change, it turns out to be useful well beyond crash recovery:
- Replication. Send the log to another server and have it replay the records: that's a replica (04.07 Replication: leaders, followers and lag).
- Point-in-time recovery. Take a full base backup now and then, and archive every WAL file since. To recover
to "10:42 yesterday, just before someone ran the bad
DELETE", restore the base backup and replay the archived log up to 10:41. - Change data capture. Read the log to stream every change to other systems: search indexes, caches, analytics (07.06 The outbox pattern and change data capture).
The log is only as good as your backups
The WAL protects against crashes, not against mistakes, lost disks or lost machines. Those need backups stored elsewhere, which you have actually restored. A backup system that has never been tested is a hope, not a backup.
Seen in production
GitLab
2017GitLab.com database
Under heavy load, GitLab's Postgres replica lagged until the WAL segments it needed had already been removed from
the primary, breaking replication. While re-syncing it, an engineer removed the data directory on the primary
instead of the replica. The regular pg_dump backups had been failing silently (a version mismatch, with alert
emails rejected), and disk snapshots weren't enabled. GitLab restored from an LVM snapshot taken about six hours
earlier, losing roughly 5,000 projects, 5,000 comments and 700 new user accounts.
What we foundA replica fell behind until the WAL it needed was gone; then a recovery mistake met backups that had never been tested.
Postmortem of database outage of January 31about.gitlab.com ↗A Postgres server crashes 2 ms after replying COMMIT to an insert. The new row's data page was never written to the data file. Is the row lost?
Recap
Interview questions
How does a database guarantee that committed data survives a crash?