Why Doesn't a Database Lose Data After a Crash? WAL Explained

A database may be changing many records when the machine suddenly loses power. Some changes may already be present in the database files, some may still be in memory, and others may be only partially written. Without a recovery strategy, the resulting data could be inconsistent: one part of an operation might be visible while another part is missing.

Write-Ahead Logging, usually called WAL, is a central idea for handling this problem. Its basic rule is simple:

Record a description of a change in a log before writing the corresponding change to the main database storage.

That ordering gives the database something reliable to consult after a crash. If the main data files do not fully reflect an operation, the database can replay the log and bring the files forward. The log acts as a durable history of intended changes, while checkpoints provide known progress markers and help control how much history must be examined.

This article explains the core idea, the crash-recovery process, and the role of checkpoint truncation.

The Problem: Updates Can Be Interrupted​

Imagine a transaction that changes a database record. At a high level, the database has to perform two related jobs:

1. Decide what change should happen.
2. Make that change durable in storage.

The second job is more complicated than it may appear. Database pages are often handled in memory before they are written to persistent storage. A process may update an in-memory page, but the operating system or storage device may not have written that page to its final location yet. A crash can happen at any point in this sequence.

For example, suppose an operation changes a value from 100 to 80. Several outcomes are possible when the system stops unexpectedly:

• The value is still 100 in the database file because the updated page never reached storage.
• The value is already 80 because the page was written successfully.
• A larger operation changed several related records, but only some of those records reached storage.
• The database process stopped after preparing a change but before completing the normal operation.

The dangerous case is not simply "the latest value is missing." The more serious issue is that related changes can be left in different states. A multi-step operation may require all of its changes to be treated as one consistent unit. If a crash leaves only part of that unit in the database files, the database needs a way to repair the state.

Consider a concrete example: transferring money between two accounts. The operation requires two changes:

1. Deduct the amount from account A.
2. Add the amount to account B.

If a crash occurs between these two steps and only the first change reaches storage, the money disappears from the system. If only the second change reaches storage, money appears from nowhere. Neither outcome is acceptable. The database must have a mechanism to ensure that either both changes are applied or neither is applied—or that recovery can complete the operation.

The Central WAL Rule​

WAL solves this ordering problem by separating the record of a change from the database pages that contain the change.

A simplified sequence looks like this:

1. Create a log record describing the change.
2. Write the log record to the log.
3. Ensure the log record is available for recovery.
4. Update the database page, in memory or on storage.
5. Later, write the updated page to its normal database location.

The important constraint is that step 2 comes before step 5. The database must not write a changed data page to its final location before the log contains enough information to reproduce or account for that change.

This is the meaning of "write-ahead." The log is written ahead of the data pages.

The log is not necessarily a second copy of every complete database page. It is a sequential record of changes or actions, depending on the database design. For the purposes of the crash-recovery idea, the important property is that the log contains sufficient information to reconstruct the required state after an interruption.

The WAL rule can be formalized as an invariant:

A database page must never become newer than the log information available for recovering that page.

In other words, the log may be ahead of the pages, but the pages must not get ahead of the log. This ordering is not a performance optimization—it is a correctness requirement.

Why the Log Helps After a Crash​

Consider a change whose log record has been written, but whose data page has not yet been written. A crash occurs. When the database restarts, the data page may still show the old value. That does not necessarily mean the change has been lost. The recovery process reads the log, finds the recorded change, and replays it against the database state.

In simplified form:

Before crash:
  Log:  [change A recorded]
  Data: [change A not yet applied]

After recovery replay:
  Log:  [change A recorded]
  Data: [change A applied]

The log bridges the gap between the operation that was recorded and the data page that had not yet caught up.

The reverse situation is also important. A data page may have been written after its corresponding log record was created. That is safe because the log already exists. If recovery encounters the same log record again, the database can determine whether the change has already been reflected in the data page or apply the operation in a controlled way. The exact recovery rules vary by implementation, but the WAL ordering gives recovery a dependable sequence of information.

This is why the log is sometimes called the "source of truth" for recovery. When the database restarts, it does not trust the current state of the data files. Instead, it consults the log to determine what work must be done.

A Crash-Recovery Walkthrough​

Suppose a database performs three changes:

L1: Change record A from 100 to 80
L2: Change record B from 50 to 70
L3: Change record C from 200 to 150

The database writes these records to its WAL. It then begins transferring the corresponding changes to the normal data pages. Before all pages are written, the machine crashes.

At restart, the database does not assume that the data files are a complete and current representation of the last activity. Instead, it examines the log and determines what work must be replayed.

A simplified recovery sequence is:

1. Find the relevant portion of the log (often starting from the last checkpoint).
2. Read log records in their recorded order.
3. Reapply changes that are not reflected in the data files.
4. Produce a consistent database state.
5. Continue normal operation.

If L1 and L2 had already reached the data pages but L3 had not, recovery can leave the first two changes as they are and replay the third. If the system cannot immediately distinguish which changes reached storage, the recovery design must still make replay safe and controlled. The WAL records provide the information needed to make that decision.

The key idea is not that every operation is magically completed before a crash. The key idea is that the database has a durable record from which it can reconstruct the intended state.

Let's trace through a more detailed example:

Before crash:
  Log contains:     L1, L2, L3
  Data file state:  L1 applied, L2 applied, L3 not yet applied
  Memory buffers:   L3 change prepared but not flushed

Crash occurs.

Recovery process:
  1. Read checkpoint: "All changes through L2 are safe."
  2. Scan log from L3 onward.
  3. Find L3: "Change record C from 200 to 150."
  4. Check data file: record C is still 200.
  5. Apply L3: change record C to 150.
  6. Database is now consistent.

Consistency After an Interrupted Operation​

A database operation may involve multiple related changes. For example, an abstract transfer might reduce one value and increase another. If the system stops between those two updates, the database could be left with only one side of the operation unless it has a recovery mechanism.

WAL supports consistency by giving recovery an ordered record of the operation. The database can use that record to replay the required changes in the appropriate sequence. The result is that the main data files do not have to be perfectly up to date at every instant, as long as the log is ahead of them and recovery can use it.

This leads to an important distinction:

• The log records what recovery needs to know.
• The data files eventually reflect the recorded changes.
• A crash may interrupt the second step without destroying the first.

That distinction is why a database can tolerate data pages lagging behind the most recent activity. The lag is recoverable because the log preserves the missing information.

Consider the transfer example again:

Operation: Transfer $100 from account A to account B

Log records:
  L1: BEGIN TRANSACTION
  L2: Deduct $100 from account A (balance: 900 -> 800)
  L3: Add $100 to account B (balance: 500 -> 600)
  L4: COMMIT

Scenario 1: Crash after L2 is logged but before L2 is applied to data
  Recovery replays L2 and L3, ensuring both sides of the transfer complete.

Scenario 2: Crash after L2 is applied to data but before L3 is logged
  This cannot happen under WAL because L3 must be logged before L2 is applied.

Scenario 3: Crash after both L2 and L3 are logged but before both are applied
  Recovery replays both, ensuring consistency.

The WAL rule prevents scenario 2, which would be the dangerous case.

Why Writing the Log First Matters​

Suppose a database violated the WAL rule and wrote a changed data page before recording the change in the log. A crash could occur immediately after the page write but before the log write. After restart, the database might observe a changed page with no corresponding log record.

That creates a difficult situation. Recovery has no dependable history for the change. It may not know:

• Whether the change was complete.
• Which related changes should accompany it.
• Whether the operation should be replayed.
• Whether the page contains a valid new state or an incomplete one.

Writing the log first avoids this ordering gap. Once a data page can contain a new version of a change, the log already contains the recovery information for that change.

This is why the WAL rule is sometimes called the "write-ahead" principle. The log must be written and made durable before the data page is written. If the system crashes after the data page write but before the log write, recovery has no information about what the new value means or whether it is part of a larger operation.

The consequences of violating this rule are severe:

1. Silent corruption: The database might not detect that a page contains a partial or invalid change.
2. Inconsistency: Related changes might be left in incompatible states.
3. Unrecoverability: There would be no way to determine the correct state after a crash.

Therefore, the WAL rule is not optional or a performance consideration. It is a fundamental correctness requirement.

WAL Is a History, Not the Final Database​

It is useful to think of the WAL as a timeline rather than as the database itself. The normal data files represent the current materialized state. The WAL represents changes that have occurred or that recovery must account for.

At a particular moment, the two can be at different positions:

Log position:   120
Data position:   95

This means the log contains information beyond what has been installed in the data pages. That is acceptable under WAL. Recovery can use positions 96 through 120 to catch the data files up.

As the database writes more pages, the data position advances. Eventually, the difference between the log and the data files becomes smaller. However, the log cannot normally be discarded simply because some pages have been written. The database needs a reliable point from which it knows that older log records are no longer required for recovery.

That is where checkpoints become important.

What a Checkpoint Does​

A checkpoint is a recorded progress point in the database's recovery process. It indicates that the database has reached a state where certain earlier log records have been incorporated into the main data files, or otherwise no longer need to be considered for the relevant recovery work.

A simplified checkpoint process might look like this:

1. Identify the current recovery position (e.g., log record 95).
2. Ensure required changes before that position are represented in the data files.
3. Flush all dirty pages to storage.
4. Record a checkpoint marker in the log (e.g., "Checkpoint at L95").
5. Treat older log history as no longer needed for future recovery.

The exact mechanics differ among database systems, but the purpose is consistent: establish a known boundary in the log.

Checkpoints serve several critical functions:

Limiting Recovery Scope​

Without checkpoints, recovery after a crash might need to scan the entire log from the beginning of time. If a database has been running for weeks, that could mean replaying millions of log records. Checkpoints establish a boundary, so recovery can start from the most recent checkpoint instead.

Enabling Log Truncation​

Once a checkpoint is established, the database knows that all changes before the checkpoint are safely represented in the data files. The log records before the checkpoint can be discarded, preventing the log from growing without bound.

Providing a Known Safe State​

A checkpoint represents a point where the database is known to be consistent. If recovery must start from a checkpoint, it begins from a known good state and only needs to replay the changes that occurred after the checkpoint.

Checkpoint Truncation​

Checkpoint truncation is the cleanup step that removes or releases log history that is no longer needed for recovery. The word "truncation" should be understood as a storage-management action, not as a loss of unprocessed database changes. The database can discard old log records only after it has established that those records are safely represented by the database state and are no longer required for crash recovery.

A simplified timeline looks like this:

WAL:         [old records][records after checkpoint]
Checkpoint:                 ^
After cleanup:              [records after checkpoint]

The old prefix can be removed because the checkpoint provides a recovery boundary. If a crash occurs after the checkpoint, recovery can begin from that boundary rather than from the beginning of the database's entire history.

Checkpoint truncation provides two practical benefits:

1. Bounded log growth: the WAL does not grow without limit when old history is no longer useful.
2. Faster recovery: restart can focus on recent records instead of replaying all historical activity.

The order matters. Truncating log records before they are safely unnecessary would destroy information that recovery might need. Therefore, checkpointing and truncation are tied to the WAL guarantee: first establish that earlier work is safe, then release the corresponding history.

Consider a concrete example:

Time 1: Database starts, log is empty.
Time 2: Write L1, L2, L3 to log. Apply to data files.
Time 3: Checkpoint at L3. Record "Checkpoint: L3" in log.
Time 4: Write L4, L5 to log. Apply to data files.
Time 5: Checkpoint at L5. Record "Checkpoint: L5" in log.
Time 6: Truncate log. Remove L1, L2, L3. Keep L4, L5, and checkpoint marker.
Time 7: Crash occurs.
Time 8: Recovery starts from checkpoint at L5, replays L4 and L5.

Without truncation, the log would contain L1 through L5. With truncation, it contains only L4 and L5, saving storage and reducing recovery time.

Recovery Replay in More Detail​

Recovery replay can be understood as moving the database from a checkpoint state toward the latest valid log position.

Suppose a checkpoint records that the database is consistent through log record L100. Later, the database writes records through L140, but a crash occurs. On restart, the recovery process can begin with the checkpoint state and examine records L101 through L140.

For each record, recovery conceptually asks:

• What change does this record describe?
• Has the corresponding data already been updated?
• If not, what must be applied?
• Does applying it preserve the required ordering and consistency?

The replay continues until the database reflects the recoverable log state. The data files may have been partially updated before the crash, so recovery must handle a mixture of already-applied and not-yet-applied changes. WAL gives the process an ordered source of truth for the missing work.

This is why recovery is often described as replaying the log. It takes the recorded sequence of changes and uses that sequence to repair the materialized database state.

Let's trace through a detailed recovery scenario:

Checkpoint state: L100 (all changes through L100 are in data files)
Log contains: L101, L102, L103, L104, L105

Before crash:
  L101: Applied to data
  L102: Applied to data
  L103: Logged but not applied
  L104: Logged but not applied
  L105: Logged but not applied

Crash occurs.

Recovery process:
  1. Read checkpoint: "Start from L100."
  2. Scan log from L101 onward.
  3. L101: Check if applied. Yes, already in data. Skip or verify.
  4. L102: Check if applied. Yes, already in data. Skip or verify.
  5. L103: Check if applied. No, not in data. Apply L103.
  6. L104: Check if applied. No, not in data. Apply L104.
  7. L105: Check if applied. No, not in data. Apply L105.
  8. Recovery complete. Database is consistent.

The recovery process must be idempotent, meaning it is safe to apply a change multiple times. This is important because recovery cannot always determine with certainty whether a change was partially applied before the crash. By making replay idempotent, the database ensures that replaying a change that was already applied does not cause problems.

The Relationship Between Durability and Consistency​

The title question asks why a database does not simply lose data after a crash. WAL addresses that question by preserving a durable record of changes before the slower or more interruptible data-page writes complete.

There are two related ideas here:

• Durability: once a change is considered safely recorded, it can survive a process or machine interruption through the log.
• Consistency: recovery uses the recorded order and content to bring the database back to a coherent state.

WAL does not mean that every imaginable failure is irrelevant, nor does it mean that an application can ignore backups or storage behavior. Its specific contribution is an ordering strategy and a recovery history. Under the assumptions of the database's storage and recovery design, that history lets the system reconstruct changes that were not fully installed when the crash occurred.

This distinction is important for engineering discussions. "The database writes to a log first" is not merely a performance trick. It is a correctness rule. The log must be made sufficiently durable for the guarantees the database promises; otherwise, both the log and the data pages could disappear together and recovery would have nothing to replay.

For example, if the log is stored in memory and never flushed to disk, a power loss would destroy both the log and the data pages. The database would have no recovery information. Therefore, a real WAL implementation must ensure that the log is written to persistent storage (disk) before the database considers a change durable.

A Practical Mental Model​

A useful mental model is a delivery system with two locations:

• The log is the shipment manifest.
• The data pages are the shelves where the items ultimately belong.

Before putting an item on a shelf, the system records it in the manifest. If the warehouse loses power after the manifest is updated but before the shelf is updated, workers can consult the manifest and finish the placement later. A checkpoint is a verified inventory boundary. Once the inventory confirms that all older shipments are correctly placed, the old manifest pages can be discarded.

This analogy captures the essential ordering:

Manifest first -> shelf update later -> verified checkpoint -> old records released

The analogy also explains why the log may temporarily contain more information than the data files. That extra information represents work that has been recorded but not yet fully installed.

Extending the analogy:

• Crash during shelf update: The manifest still has the record. Workers can finish the job.
• Crash during manifest update: The shelf is unchanged. The job is not recorded, so it is not attempted.
• Crash after both are updated: Everything is consistent.
• Crash after checkpoint: Workers only need to check the manifest since the last checkpoint, not the entire history.

What Engineers Should Look For in a WAL Design​

When evaluating a database or storage engine that uses WAL, several questions clarify its behavior:

What Exactly Is Written to the Log?​

The log may contain change descriptions, operation information, or another form of recovery record. The crucial question is whether the record contains enough information to perform correct recovery. Some databases log the old and new values, while others log only the new value or a description of the operation.

When Is the Log Considered Durable?​

The WAL idea depends on the log being available after the failure being considered. A design should define when a record has reached the durability level required by its guarantees. For example:

• Is the log written to memory only (not durable)?
• Is the log written to disk but not synced (partially durable)?
• Is the log synced to disk (fully durable)?

The durability level affects the guarantees the database can make.

How Does Recovery Find Its Starting Point?​

Checkpoints provide a boundary that helps recovery avoid scanning unnecessary older history. Understanding checkpoint placement helps explain restart time and log-management behavior. Some databases use a single checkpoint, while others maintain multiple checkpoints or use other recovery markers.

When Can Old Log Records Be Truncated?​

Safe truncation depends on knowing that earlier records are no longer needed. Premature cleanup would undermine recovery, so truncation must follow the checkpoint and persistence rules. Understanding the truncation policy helps predict log growth and storage requirements.

How Are Already-Applied Records Handled?​

Because a crash can happen after some data pages are written but before others, recovery must cope with partial progress. A robust WAL design defines how replay recognizes or safely handles that situation. Some designs use version numbers or timestamps to track which changes have been applied.

These questions turn the abstract phrase "the database uses WAL" into an operational understanding of logging, replay, checkpointing, and cleanup.

Common Misconceptions​

"The Data Page Must Be Written Immediately"​

No. The WAL rule allows the data page to be written later. What must happen first is the creation and sufficient persistence of the log record. The database can delay writing data pages for performance reasons, as long as the log is ahead.

"The Log Is Unnecessary After Every Page Write"​

Not necessarily. The database needs a checkpoint or equivalent recovery boundary before it can safely release older history. Page writes alone do not automatically establish that the older log is unnecessary. A page might be written, but if other related pages have not been written, the log records are still needed for recovery.

"A Checkpoint Means the Log Disappears Instantly"​

A checkpoint records safe progress. Truncation can then remove log history that is outside the required recovery range. The two ideas are related, but a checkpoint is not simply an instruction to erase everything before it without verification. The database must first ensure that all changes before the checkpoint are safely in the data files.

"Recovery Means the Database Starts From Scratch"​

The point of checkpoints is to avoid that. Recovery normally works from a known checkpoint and replays the relevant newer portion of the log. Starting from scratch would mean replaying the entire log from the beginning, which is inefficient and unnecessary.

"WAL Prevents All Data Loss"​

WAL prevents data loss from incomplete writes and process crashes, but it does not prevent data loss from storage device failure, corruption, or other catastrophic events. WAL is one layer of protection; backups and replication are additional layers.

Summary​

Write-Ahead Logging protects database consistency through a carefully enforced order:

1. Record a change in the log.
2. Make the log available for recovery.
3. Write the corresponding database pages later.
4. If a crash occurs, replay the log to complete missing changes.
5. Establish checkpoints as verified recovery boundaries.
6. Truncate old log records once they are no longer needed.

The database files do not have to be perfectly current at every instant. They can lag behind the log because the log preserves the information required to catch them up. That is the heart of WAL: the recovery record moves ahead first, and the main storage follows.

For software engineers, the practical takeaway is straightforward. When a system must survive interrupted writes, do not rely on the current data files alone. Maintain an ordered, durable record of changes, enforce the write-ahead rule, replay that record after failure, and use checkpoints to keep recovery and storage costs manageable.

Understanding WAL is essential for working with databases, designing storage systems, or building any application that must survive crashes without losing data. The principle is simple, but its implications are profound: by recording intentions before executing them, systems can recover from almost any interruption and maintain consistency despite the unreliability of hardware and software.