Why Your Bank Transfer Survives a Power Cut (Write-Ahead Logging Explained)

01 / 06

The half-finished problem

A bank transfer is not one operation. It is at least two: debit account A, credit account B. The database has to touch two different rows, possibly on two different pages of a data file. If the system dies after the first write but before the second, the data on disk is in a state that never should have existed.

This is not hypothetical. Power supplies fail. Operating systems panic. Disks get unplugged. A server that processes thousands of transactions per second will, given enough time, crash in the middle of one. The question is not whether it happens. The question is what the database does about it when it reboots.

02 / 06

Write the plan before you act

The fix is almost disappointingly simple. Before the database touches any real data, it writes down what it intends to do in a separate, append-only file on disk. That file is the write-ahead log (WAL).

For the thousand-dollar transfer, the WAL entry might look like: txn 7041: A -= 1000, B += 1000. The database writes this entry to disk first, calls fsync to make sure it has physically landed on the storage device (not just sitting in an OS buffer), and only then starts modifying the actual account balances.

The name says the whole thing: write ahead. The log goes first. The real changes follow. That ordering is the entire guarantee.

03 / 06

What happens on crash and reboot

Say the power cuts right after the WAL entry is saved but before either account balance is updated. On reboot, the database opens the WAL and scans it. It finds transaction 7041 marked as committed but sees that the actual data pages were never written. So it replays the plan: subtract a thousand from A, add a thousand to B. The balances end up correct, as though the crash never happened.

Now consider the opposite case: the crash happens before the WAL entry even finishes writing. On reboot the database finds an incomplete log record and simply discards it. Neither account was touched, so both balances stay at their original values. The transfer never happened, but no money was lost. The user can try again.

Either the whole thing finishes, or none of it does. That is the atomicity guarantee, and the WAL is how databases deliver it without any magic.

04 / 06

ARIES: the algorithm that standardised recovery

The idea of logging before writing goes back decades, but the version most modern databases use was formalised by C. Mohan and colleagues at IBM in a 1992 paper called ARIES (Algorithms for Recovery and Isolation Exploiting Semantics), published in ACM Transactions on Database Systems.

ARIES recovery works in three passes. First, an analysis phase scans the log to figure out which transactions were active when the crash happened and which data pages might be dirty. Second, a redo phase replays every logged change from the oldest relevant point forward, restoring the database to its exact pre-crash state. Third, an undo phase rolls back any transaction that was still in progress and never committed.

The order matters. Redo before undo, always. And the undo actions themselves get logged (as compensation records), so if the system crashes again during recovery, the next recovery can pick up where it left off without repeating work. It is recovery that can survive its own failure.

05 / 06

Checkpoints: keeping the log from growing forever

If the WAL kept every record since the database was created, recovery after a crash would mean replaying the entire history of every transaction. On a busy system, that could take hours.

So databases periodically perform a checkpoint: they flush all dirty pages from memory to the main data files, then write a marker in the WAL that says "everything before this point is safely on disk." After a checkpoint, recovery only needs to replay log entries written after the marker. Old WAL segments before the checkpoint can be recycled or deleted.

The tradeoff is straightforward. Checkpoint often and recovery is fast, but you spend more I/O flushing pages during normal operation. Checkpoint rarely and normal writes are cheaper, but a crash means a longer replay. PostgreSQL lets you tune this with checkpoint_timeout and max_wal_size. SQLite does it automatically when the WAL file reaches a configurable threshold.

06 / 06

Where every serious database uses this

PostgreSQL writes every change to its WAL before touching heap pages; the same log powers streaming replication and point-in-time recovery. SQLite added a WAL mode in version 3.7.0 (2010) that replaced its older rollback journal, giving it concurrent readers alongside a single writer for the first time. MySQL's InnoDB engine uses a redo log that follows the same write-ahead principle.

The pattern appears outside relational databases too. Cassandra and HBase log to a commit log before writing to their memtables. etcd, the key-value store behind Kubernetes, persists every Raft proposal to a WAL. Even filesystems use the idea: ext4's journaling mode is a write-ahead log for metadata changes.

The reason it is everywhere is that the alternative is worse. Without a WAL, a database has two options after a crash: either lose the last few seconds of committed work, or run a full consistency check that can take longer than the downtime itself. Neither is acceptable when someone's money or medical record is on the line.