By default, Postgres can leave your changed rows out of its table files for five minutes or more. Commit a payment, pull the power cable one second later, and when the server comes back, the payment is still there.
This post walks through how that works: the write-ahead log (WAL). It's the same idea in Postgres, SQLite, MySQL and almost every serious database. If you prefer video, here's the animated version:
The unplug test
We'll use one example throughout. Asha sends Ravi ₹500. To the database that's two changes: subtract 500 from Asha's balance, add 500 to Ravi's.
Those changes happen in memory first, because memory is fast. But memory is volatile. Worse, imagine the power dies after the first change reaches disk but before the second one does: Asha has lost ₹500 and Ravi never got it.
So a database has to promise two things:
- All or nothing. A transfer happens completely or not at all.
- Durable. Once it says "committed", the transfer survives a crash.
Why not just write the tables?
The obvious fix is to write every changed page into the table files on every commit, and wait for the disk. It's slow and it still isn't safe.
Postgres stores tables in 8 KB pages. Asha's row and Ravi's row live on different pages, and every index on those tables has its own pages too. One small transfer can touch four or five pages scattered across the disk. That's random I/O, on every commit. And if the power dies halfway through that list, some pages are new, some are old, and nothing records which is which.
The one rule: log first, data later
Write-ahead logging replaces all of that with one rule:
Before you change a data file, write down what you're about to change in a log, and make sure the log is on permanent storage.
For our transfer the log gets three short records: asha -500, ravi +500, and a commit record. The moment the commit record is on disk, the transfer is durable. The table pages can stay in memory and be written whenever it's convenient.
Inside the log
In Postgres the log is a series of files in pg_wal, 16 MB each by default. Every record gets a log sequence number (LSN), which is just a byte position in the log, and it only goes up.
Each data page remembers the LSN of the last change applied to it. That gives Postgres one more rule: a dirty page (changed in memory) can't be written to disk until the log is flushed at least up to that page's LSN. The log is always ahead of the data.
Why writing twice is faster
This is the part that surprises people. Writing everything twice (once to the log, later to the tables) beats forcing every table page to disk on every commit.
The table writes were scattered. The log is written sequentially: every new record goes on the end. So at commit time there's only one file to sync, the log. The scattered page writes still happen, but later, in the background, batched, where no user is waiting on them.
fsync: the real promise
Calling write() doesn't mean the data is on disk. The operating system usually just copies it into its page cache. To force it down to storage, the database calls fsync, which blocks until the OS says the data is on the device.
So a commit really means: append the commit record, fsync the log, and only then tell the client "done".
There are more caches below the OS: disk controllers and the drives themselves. The Postgres docs warn that many consumer drives and SSDs keep writes in a cache that doesn't survive a power failure, and some ignore flush commands. The log is only as honest as the disk under it.
Recovery: replay the log
When the unplugged server boots, it runs crash recovery before accepting a single query. It reads a small control file, pg_control, which points at the last checkpoint, then scans the log forward and redoes every change it finds. If a page's LSN is already past a record, that change is skipped, so nothing is applied twice.
Asha's transfer has a commit record on disk, so both halves are replayed and Ravi gets his ₹500.
If the power had died before the commit record reached the log, the changes might even be replayed onto the pages, but Postgres treats the transaction as aborted, so nobody ever sees them. All or nothing.
Checkpoints
If the log grew forever, recovery would replay all of it and the disk would fill up. So Postgres runs checkpoints: flush every dirty page to the table files, then write a checkpoint record. Everything before it is safely in the tables, recovery can start there, and old log files can be recycled.
By default a checkpoint starts every 5 minutes, or sooner if the log is about to pass 1 GB. That's where the "five minutes" at the top comes from. Checkpoint more often and recovery is faster; less often and you do less write work in normal running.
Torn pages
There's one crash the log alone can't fix. An 8 KB page is sixteen 512-byte sector writes. Lose power in the middle and you get a torn page: half new, half old, neither version. The log's small change records can't repair that.
Postgres handles it with full page writes: the first time a page is modified after a checkpoint, the whole page image goes into the log. Recovery restores that clean copy first, then replays the later changes on top.
Group commit
fsync is the expensive step. When a thousand users commit at once, they don't need a thousand of them: their commit records sit next to each other at the end of the log, and one fsync makes all of them durable. The busier the system, the more commits ride on each flush.
Trading durability for speed
Some workloads don't need every commit to wait for the disk. With synchronous_commit = off, the client is told "done" before the log is flushed, and the WAL writer flushes it shortly after. It wakes every 200 ms by default, and the worst-case window of lost commits is three times that, about 600 ms.
A crash can lose those last few commits, but it can't corrupt the database. Recovery still replays a clean, consistent log. You choose per transaction: Asha's payment keeps synchronous commit, a page-view counter can go async.
The same log, everywhere
SQLite has a WAL mode too: changes are appended to a -wal file, a commit is a commit record, readers don't block the writer, and it checkpoints automatically at 1,000 pages (about 4 MB). And in Postgres, the same log powers point-in-time recovery and streaming replicas.
Three takeaways
- A commit is durable when its log record is on disk, not when the tables change.
- Sequential log writes are what make that cheap.
- Checkpoints keep recovery short.
Watch the full animated breakdown: https://youtu.be/mhpTQN13bGI
Sources: the PostgreSQL docs on WAL, reliability, WAL configuration and async commit, and SQLite's WAL page. Balances and LSNs in the images are illustrative.
Practice designing systems like this yourself at systemdesignlab.in.
Top comments (0)