Skip to content

WAL: How Databases Survive a Crash

4 min read2 views[Backend]
Cover image for "WAL: How Databases Survive a Crash"

An application says “saved.” A second later, the database process crashes.

After restart, the record is still there. That feels ordinary because databases have made a difficult promise familiar: some acknowledged changes should survive the loss of the process that made them.

Write-ahead logging is one of the mechanisms behind that promise. Understanding it also explains why a fast write benchmark can conceal a durability tradeoff.

Memory is not durable storage

Imagine a database updating an order status and an index entry. The working pages may be in memory. Writing every changed page immediately would require substantial storage work, and the process could crash after writing only part of the change.

The central WAL rule is ordering: records describing a change reach durable storage before the changed data pages are allowed to do so. Recovery can then redo changes that were logged but not fully reflected in the data files. PostgreSQL documents this as the basis of its WAL recovery mechanism. PostgreSQL WAL introduction

That does not mean WAL is a plain-text transcript of SQL statements. Think of it as the engine's recovery record, in the representation that engine needs.

Follow one transaction

Here is a simplified conceptual timeline, not PostgreSQL's complete internal algorithm:

1. Begin transaction
2. Modify working state and generate recovery records
3. Generate a commit record
4. Make required log records durable
5. Report successful durable commit
6. Write changed data pages as needed

The important observation is that step six need not happen before step five. The log provides the information required to recover the committed change.

If the process crashes between five and six, “the data page was still in RAM” does not imply that the committed change is lost. Recovery can rebuild the relevant state from durable information.

This also separates two questions an application often merges: did the server commit, and did the client receive the response? A connection can fail after step four. The database may have kept its promise even though the client saw a timeout.

Durability has a configuration

PostgreSQL's asynchronous commit option can report success before the transaction's WAL is flushed. A crash can then lose recent acknowledged transactions while preserving database consistency. That is a different tradeoff from turning off mechanisms needed to protect against corruption. PostgreSQL asynchronous commit

So when someone reports impressive write throughput, ask what “completed write” means in that measurement. Was data accepted into process memory, handed to the operating system, flushed locally, or acknowledged by another replica?

Those results may all be useful. They are not interchangeable.

For a hypothetical analytics pipeline, losing a small amount of recent reproducible data might be acceptable. For an application recording an irreversible business decision, it might not be. The requirement should determine the setting.

Checkpoints bound the recovery work

A database cannot let recovery depend on replaying its entire history forever. Checkpointing establishes progress in writing database state and determines where crash recovery can begin, according to the engine's design.

In PostgreSQL, checkpoint behavior interacts with WAL generation and I/O. It is an operational workload, not a magical pause in which the database becomes permanently safe. PostgreSQL WAL configuration

A useful analogy is saving progress in a long calculation while retaining the subsequent steps needed to resume. The actual engine has more bookkeeping than that analogy suggests, including rules for concurrent activity.

Operationally, I would watch how recovery time changes with workload, how much log space is being retained, and whether storage latency changes during checkpoint activity. Those observations matter more than treating a single configuration value as a universal performance fix.

The storage stack still matters

A promise to flush data must ultimately be honored by the operating system and storage hardware. PostgreSQL's reliability documentation discusses volatile caches and partial-page writes, including why its protection mechanisms exist. PostgreSQL reliability

This is why “the database uses WAL” cannot be the complete answer to every failure question. Process failure, power loss, a failed disk, accidental deletion, and corruption are different events with different recovery requirements.

Killing a database process in a disposable test environment is useful. It does not simulate every hardware failure mode.

WAL is not automatically a backup

Suppose the database correctly records a DELETE that an administrator did not intend. Crash recovery should preserve that committed deletion. The database is doing exactly what it was asked to do.

Recovering an earlier business state requires a separate recovery strategy. PostgreSQL's point-in-time recovery uses a base backup together with a continuous sequence of archived WAL, not an arbitrary pile of log files by themselves. PostgreSQL continuous archiving

For an application I operate, I would document two separate drills: restart after a crash, and restore an earlier state into an isolated environment. The first checks automatic recovery. The second checks whether we can recover from a mistake or loss of the original storage.

WAL makes durability efficient by separating logging from later page writes. The practical payoff is knowing which promise a successful commit actually makes—and which recovery jobs still belong to you.

# Comments (0)

No comments yet. Be the first.

0/1000