The write-ahead log

Two claims about write-ahead logging usually arrive together, and they sound like they cannot both be true: it makes a database safer, and it makes it faster. Safer is easy to believe, because there is now a second copy of every change. Faster is not, because there is now a second copy of every change. The way out is that the two writes are not the same size, do not go to the same place, and do not have to happen at the same moment.

Why writing it twice is faster than writing it once#

Take a transaction that updates three rows, and suppose those three rows happen to live on three different pages. Making that transaction durable without a log means finding each of those pages wherever it sits in the data file, writing it, and waiting for the storage to confirm all three before the client hears the word “committed”. Three scattered writes, three seeks, one after another, on the critical path of every single commit. Where those pages sit is decided by the storage engine, and B-trees and LSM-trees are the two families engines choose between.

With a log, what gets written on that path instead is a description of each change — which page, which offset, what it now says — appended to the end of one file. The records are contiguous, they are small, and one flush covers the whole transaction no matter how far apart the pages were. The pages themselves stay dirty in memory and get written later, whenever the server finds it convenient. That deferral is where the second saving comes from: if the same hot page is modified forty times before it is finally written out, it is written once. The no-log version would have written it forty times.

The rule that holds all of this together is an ordering, and the ordering is the entire guarantee. The log record describing a change must reach stable storage before the changed page does. Not before the change happens in memory — before the page itself is written out. That is what “write-ahead” names, and it is why a crash in the gap between the two is survivable: the data file still holds the old version of the page, and the log already says what to do to it.

What a checkpoint actually bounds#

Nothing said so far puts a limit on two quantities, and both of them grow without one: how much log a recovery has to read, and how long the log has to be kept. Deferring page writes indefinitely means that at any moment an arbitrarily old change might still exist only in the log — so recovery would have to start at the beginning of it, and no segment could ever be thrown away. A database that has been up for a year would take a year’s worth of replay to come back.

A checkpoint is what cuts that off. The server flushes the dirty pages it is holding, and records a position in the log — the redo point — such that every change before that position is guaranteed to be in the data file already. Recovery reads the last completed checkpoint record, finds the redo point it named, and starts there. Not at the beginning of the log, and not at the end of it.

Two consequences follow, and both of them show up in real systems. The first is a knob rather than a fact: checkpoint more often and there is less outstanding work to redo, so startup after a crash is shorter — but the server also spends more of its running life flushing dirty pages. Postgres’s own configuration documentation frames it exactly as that balance, shorter recovery against the increased cost of flushing more often. Whichever direction you turn it, you are trading startup time against steady-state cost, and there is no setting that gives you both.

The second is why log files do not accumulate forever. Once a checkpoint has completed, the segments in front of its redo point are no longer needed to recover from a crash, and can be recycled or removed. That is the whole mechanism behind a WAL directory that stays roughly the same size — and, when it does not stay the same size, the thing to go looking for is whatever is holding those segments back.

There is one more thing a checkpoint starts, and it explains a write pattern that otherwise looks like a fault. A page write can be torn: the operating system or the drive stops partway through, and what lands on disk is a mix of old bytes and new. A redo record saying “change these twelve bytes at this offset” cannot repair that page, because it assumed the rest of the page was intact and it is not. So the first time a page is modified after a checkpoint, the server can write the entire page image into the log, giving recovery a known-good copy to lay down before it replays anything on top. In Postgres that is full_page_writes, and it is a separate mechanism from page checksums, which detect damage rather than repair it. The visible effect is that log volume spikes just after each checkpoint and tails off between them — which is also the other half of the trade above, since checkpointing more often makes the spikes more frequent.

Acknowledged but not durable#

The failure worth knowing about is a commit that returned before its log record was durable. The client got its acknowledgement, the application moved on and did something irreversible on the strength of it, and the record it depended on was in a buffer that a power cut emptied. There is no error, no warning, no slow query. Everything looks correct right up until the machine stops without asking, and then some number of transactions that the application is certain happened did not happen.

There are two ways to end up there, and both of them are configuration rather than a bug. The first is telling the database not to wait. Postgres has synchronous_commit; MySQL’s InnoDB has innodb_flush_log_at_trx_commit. Both control how much log processing has to finish before the server reports success, and turning either one down trades recently committed transactions for lower commit latency. MySQL’s documentation is direct about the direction: set innodb_flush_log_at_trx_commit to 0 and you can lose some of the latest committed transactions, and while InnoDB tries to flush the log once a second anyway, that flush is not guaranteed.

Here is the distinction most write-ups flatten, and it is the one that decides how frightened to be. Postgres documents asynchronous commit as a risk of data loss, not of data corruption. After a crash the server replays the log up to the last record that was actually flushed, and because transactions are replayed in commit order, what comes back is a self-consistent database that is simply missing its last few transactions — the same state you would have if those transactions had been cleanly aborted. The write-ahead ordering was never broken. You lost the tail of the log, not the shape of the table.

The second route breaks the ordering itself, and that is the one that corrupts. If the drive or the controller reports a flush as complete while the data is still in a volatile write cache, then “log first, page second” is a story the database is telling itself: the two writes can reach the platter in either order, or one of them not at all. Postgres’s reliability documentation is blunt that this is a real hazard and names consumer SATA drives and many SSDs as likely to carry exactly that kind of cache. Now a crash can leave a data page that was written while its log record never landed, and recovery has nothing to rebuild it from. Same silence as the first case, considerably worse ending.

Both look identical from the outside — the machine came back and something is wrong — so it is worth being able to tell them apart before you need to. One of the two is a setting somebody chose, and settings are readable from the running server rather than guessed at from an article about them, including this one.

SHOW synchronous_commit;
SHOW fsync;
SHOW full_page_writes;
SHOW wal_sync_method;

Four answers, in the order they matter: whether commit waits for the flush, whether the flush is issued at all, whether torn pages are covered, and which call is used to do it. On MySQL the first of those is SELECT @@innodb_flush_log_at_trx_commit; instead.

The log is the second stop on the persistence roadmap. It opens one step earlier, at how a row physically sits on a page, and goes on from here to storage engines, indexes and locking.