Write-Ahead Logging: How Databases Survive a Crash

The Core Rule: Log Before You Leap

The entire write-ahead logging protocol boils down to one non-negotiable rule: before any change is made to the actual data files on disk, a record describing that change must first be written to a sequential, append-only log file and confirmed durable — meaning it has been flushed all the way to physical storage, not just sitting in an operating system buffer. This is where the name comes from: the log entry is written ahead of the real modification. A transaction is never considered committed until its log record is safely on disk, even though the corresponding pages in the main data files might still reflect the old, pre-change values for quite a while afterward. This ordering guarantee is the single load-bearing assumption underneath almost every database durability claim you have ever relied on. If the log write happens first and is confirmed durable, the database has everything it needs to reconstruct the change later, regardless of what happens to the slower, messier process of updating the actual data pages. Break this ordering — flush the data page first and the log second — and the whole safety model collapses, because a crash between those two steps would leave a modified data file with no record explaining what changed or whether it was ever supposed to be permanent.

Crash Recovery: Replaying the Log

The payoff of the write-ahead rule arrives the moment something goes wrong. If the database crashes at any point — power loss, process kill, operating system panic — it does not need to guess what state its data files are in. On restart, the recovery process reads forward through the log starting from the last known-good checkpoint, and replays every record it finds. For any transaction whose commit record made it into the durable log, recovery redoes the change, reapplying it to the data files in case it had not been written there yet. For any transaction that was in progress but never reached a commit record, recovery undoes whatever partial changes it made, using earlier log information to roll the data back to its pre-transaction state. This redo-undo pass is deterministic and mechanical: the log is a complete, ordered history of intent, so recovery is really just careful bookkeeping rather than guesswork. The result is that a database can crash at literally any instant — mid-transaction, mid-checkpoint, mid-anything — and still come back up in a state that is exactly as if only the committed transactions had happened, nothing more and nothing less. That guarantee is what lets applications trust a commit acknowledgment without worrying about the physical mess underneath.

Why Sequential Writes Are the Performance Win

Write-ahead logging is not just a safety mechanism, it is also a clever performance trick disguised as one. A database's real data files are organized into pages scattered across disk by table, index, and row location, so updating them directly means constantly seeking to different, unpredictable locations — random writes, which are comparatively slow, especially on spinning disks and even to a lesser degree on flash storage. The log, by contrast, is append-only: every new record is simply tacked onto the end of the current log file, which means the storage device can write it as one fast, sequential stream. Sequential writes can be dramatically faster than random writes because they avoid seek overhead and let the underlying storage batch and stream data efficiently. Write-ahead logging exploits this gap directly: since durability only requires the sequential log write to complete, the database can defer the slower, scattered updates to the actual data file pages until later, batching many of them together and writing them more efficiently in bulk. The client gets a fast, durable commit the instant the log record is flushed, while the expensive random-write work against the real data files happens lazily in the background, out of the critical path of every transaction.

Checkpointing: Bounding the Log

If a database only ever appended to its log and never touched the data files, two problems would appear: the log would grow forever, and a crash late in the database's life would require replaying an enormous history since the very first write. Checkpointing solves both. Periodically, the database pauses to actually apply all the logged changes up to a certain point directly into the main data files, bringing them fully up to date with everything the log describes so far. Once that work is done and confirmed durable, everything in the log before that checkpoint is no longer needed for recovery — it describes changes that are now permanently reflected in the data files themselves, so those older entries can be discarded or archived. The checkpoint effectively becomes the new starting line for recovery: after a crash, replay only needs to walk the log from the most recent checkpoint forward, not from the beginning of time. This is a direct trade-off knob for database administrators: checkpointing more frequently means shorter recovery times after a crash but adds overhead from the extra data-file writes, while checkpointing less frequently reduces that overhead but leaves a longer log to replay if something goes wrong.

Real Systems Built on Write-Ahead Logging

Write-ahead logging is not a theoretical curiosity, it is the backbone of nearly every production-grade storage system in use today. PostgreSQL implements it literally under the name WAL, streaming every change to WAL segment files before touching table pages, and using that same log stream to power replication and point-in-time recovery. MySQL's InnoDB storage engine uses an equivalent structure called the redo log, which serves the identical purpose of guaranteeing that committed transactions survive a crash even if the buffer pool holding modified pages in memory is lost. Beyond relational databases, the same core idea reappears in modern filesystems under the name journaling — ext4, NTFS, and APFS all keep a small log of pending metadata (and sometimes data) operations so that an interrupted disk write during a power failure does not corrupt the filesystem's internal structures. Distributed systems and message queues borrow the pattern too, using append-only logs as the source of truth that everything else can be rebuilt from. Wherever you find a system promising that your data survives a sudden crash, there is very likely a write-ahead log quietly doing the real work underneath.

Frequently asked questions

Try it live

Everything above runs in your browser — open Write-Ahead Logging: How Databases Survive a Crash and change the parameters while it is running. Nothing is installed, nothing is uploaded, the whole model lives in one tab.