Write-Ahead Log

A database stores information permanently on a storage medium such as a hard drive. The problem: between the moment a program commands a change and the moment it is actually written to the disk, the power can fail. The data would then be left in an unclear state — half old, half new. A write-ahead log, or WAL for short, solves this problem with a simple rule: before the database changes anything, it first writes an entry into a continuous log. Only then does it modify the actual data. “Write-ahead” literally means “write first” — the log is always one step ahead.

Crash safety in databases

Without a WAL, every crash is a gamble. If a server fails in the middle of a change, the system afterward no longer knows how far it got. Were five of ten steps carried out? Three? None? The result can be unusable or inconsistent.

With a WAL, the answer is always clear. After a restart, the system reads through the log. Entries that are fully present there but missing from the actual data are simply carried out. Entries that were only partially recorded are discarded. This guarantees that the database ends up in a clean state — no matter exactly when the power failed.

In computer science, this behavior is called atomicity: a change is considered either fully carried out or not carried out at all. There is no intermediate state. The WAL is the tool with which databases technically deliver on this guarantee.

How the log is structured and how it works

The log is a simple, continuous file. Each entry describes a planned change: which table is affected, what the old value was, and what new value is to be written. These entries are always appended at the end — never altered in the middle. This makes writing to the log very fast, because no random access to the disk is required.

Only after an entry is safely in the log is the actual data file allowed to be touched. If something then goes wrong, the rule applies: what is in the log was intended. What is not in the log does not count. The recovery process after a crash is called recovery and, in modern databases, runs fully automatically — it usually takes only seconds.

Older entries in the log are eventually no longer needed. They are then deleted or overwritten — a process called checkpointing. In this process, the current state of the data is written completely to disk, so that in the event of the next crash the system only has to recover from this point onward instead of working through the entire log from the beginning.

WAL in products and debates

WAL is not a theoretical concept — it is built into almost every database in use today. PostgreSQL and MySQL, two of the world’s most widely used database systems, have relied on WAL for decades. SQLite, too, the database that runs on practically every smartphone, has had a WAL mode since 2010 that, among other things, significantly speeds up parallel read access.

In tech news, the term often comes up in connection with database outages. When a cloud provider reports after a server outage that “no data was lost,” the WAL is usually the reason for that. The log also plays a central role in replication — the automatic copying of a database to multiple servers at once: the replication server simply reads the primary server’s WAL and replays the same steps.

A common misconception is that WAL makes the database slower because everything is written twice. In practice, the opposite is often true. Writing to the end of a file is far more efficient for hard drives than making targeted changes to individual spots at arbitrary positions. Many databases are faster with a WAL than without one.