How Write-Ahead Logs Prevent Data Loss During Crashes

The short answer

Quick answer: A write-ahead log (WAL) is an append-only file where a database records every change before applying it to the actual data files. When a transaction commits, the database only needs to flush that small log record to disk; the data pages themselves can be written later. If the server crashes, the database reads the log on restart, redoes any committed changes that had not reached the data files, and undoes any incomplete ones. The rule is simple: never change the data on disk until the description of the change is safely in the log.

Also Read: Database Sharding Explained: Scaling Past One Machine - How To's

The problem: crashes in the middle

A single transaction may change several pages: a table row here, two index entries there. Those pages live in memory and are written to disk at different times. A crash can strike at any moment, leaving:

• Some pages updated and others not, so the table and its index disagree.
• A torn page: a page that was only half written when power failed.
• A lost commit: the client was told "saved", but the change existed only in memory.

Writing every changed page to disk synchronously at every commit would be safe but extremely slow, because those pages are scattered all over the disk.

The idea: log first, apply later

The write-ahead log solves this by separating durability from updating the data files.

1. A transaction changes pages in memory. These are now "dirty".
2. For each change, the database appends a log record describing it.
3. On COMMIT, the database flushes the log to disk with fsync.
4. It tells the client the transaction is committed.
5. Later, in the background, dirty pages are written to the data files.

As the PostgreSQL documentation puts it, changes to data files must be written only after those changes have been logged. If that holds, you never need to flush data pages at commit time, because the log can rebuild them.

Each log record carries a log sequence number (LSN), and each data page remembers the LSN of the last change applied to it. That lets recovery tell which changes a page already contains.

Why this is faster, not slower

Writing everything twice sounds wasteful. It is the opposite:

• The log is sequential. Appending to the end of one file is the fastest kind of disk write. Updating data pages in place means many scattered writes. Sequential I/O is dramatically cheaper on hard drives and still better on SSDs; see SSD vs HDD.
• Log records are small. A record describes the change, not the whole page.
• One flush serves many transactions. With group commit, the database flushes the log once for a batch of transactions that committed at about the same time.
• Data pages are written lazily. A page changed a hundred times may be written to disk once.

What fsync really does

When a program writes to a file, the operating system normally keeps the data in memory and writes it out later. See how file systems work. A power cut would lose it.

fsync asks the operating system to push the file's data all the way to stable storage and not return until it is done. It is the single most important, and most expensive, call in a database: it is what turns "written" into "durable". It crosses into the kernel as a system call and waits on the hardware.

This is also where durability settings come from. Databases let you relax the flush, for example by flushing once a second instead of at every commit. Throughput rises, and a crash can lose the last moments of acknowledged transactions.

Checkpoints: keeping the log short

If the log only ever grew, recovery after a crash would have to replay everything since the database was created. Checkpoints prevent that:

1. Write all dirty pages to the data files.
2. Record in the log that a checkpoint completed at a given position.
3. Log records older than that position are no longer needed for crash recovery and can be recycled or archived.

After a crash, recovery starts from the most recent checkpoint. More frequent checkpoints mean faster recovery but more I/O during normal operation.

Crash recovery

On restart after an unclean shutdown, the database:

1. Finds the last checkpoint.
2. Redo: reads the log forward from there and reapplies each change whose LSN is newer than the page's LSN. Operations are designed so that replaying them is safe even if they were already applied.
3. Undo: rolls back changes from transactions that never committed. Some databases, including PostgreSQL, do not need this step, because their versioning scheme simply leaves those row versions invisible.

When recovery finishes, the data is exactly as if every committed transaction completed and every uncommitted one never happened. That is the "A" and "D" of ACID.

Also Read: Why Your Database Needs Connection Pooling

Torn pages are handled by logging a full copy of a page the first time it changes after a checkpoint (PostgreSQL's full-page writes) or by writing pages twice (InnoDB's doublewrite buffer).

The log is useful for much more

Once you have an ordered record of every change, other features follow naturally:

• Replication. Ship the log to another server and replay it there. This is how streaming replicas stay up to date; see how database replication works.
• Point-in-time recovery. Restore a base backup, then replay archived log up to any chosen moment, such as just before an accidental DROP TABLE.
• Change data capture. Decode the log into a stream of row changes to feed caches, search indexes and data warehouses.

The same idea elsewhere

"Write down what you are about to do, then do it" is one of the most widely reused ideas in systems design.

Frequently asked questions

What is a write-ahead log?

An append-only record of changes that a database writes and flushes before modifying its data files, so that committed work can be recovered after a crash.

Does a WAL slow down writes?

It usually speeds them up. A small sequential log write replaces many random data-page writes at commit time.

What is a checkpoint?

The point at which the database has written all changed pages to the data files, so earlier log records are no longer needed for crash recovery.

Can I still lose data with a WAL?

Not committed data, provided the log is flushed at commit and the storage honours the flush. If you relax flushing for speed, or the disk itself is lost, you can. That is what replication and backups are for.

Conclusion

The write-ahead log is what lets a database say "committed" and mean it. By writing a compact description of each change to a sequential log before touching the data files, databases get crash safety and better performance at once, and gain replication and point-in-time recovery almost as side effects.

Related articles

• What ACID Really Means and Why It Matters
• How Database Replication Works
• How File Systems Store Your Files on Disk
• B-Trees vs LSM Trees: Why Databases Store Data Differently

Sources and further reading

• Write-Ahead Logging, PostgreSQL documentation
• Write-Ahead Logging, SQLite documentation