4.3 WAL, Checkpoint, and Crash Recovery

A sudden power outage leaves some data pages written to disk while others remain in memory. When the system restarts, the archive city must determine which modifications should be preserved.

The buffer pool enables data pages to be modified in memory first, then written back in batches. The cost is that a crash might leave the system in a state where "some pages are written, others aren't." Write-Ahead Logging (WAL) establishes a recoverable trail using an ordered log, allowing commit latency to not wait for all dirty pages to be flushed to disk.

Five Events That Cannot Be Mixed Together

1. transaction modifies an in-memory page
2. engine generates/inserts a WAL record
3. WAL is flushed to a durable boundary
4. dirty data page is written back to storage
5. transaction commit acknowledgment is sent

The core write-ahead rule: Before any data page is durably persisted, the WAL record that enables redoing that modification must already be written to stable storage.

For commits that guarantee synchronous durability, the commit record and any required prior WAL records must reach the product-defined durable boundary before the client receives a success response. Configurations allowing asynchronous or lower durability can offer better performance but introduce a larger crash loss window, this must be explicitly stated in the service contract.

Therefore, the statement "WAL must be flushed before modifying a buffer page" is inaccurate. The modification of an in-memory page and the construction of the WAL can occur in parallel; what is constrained is the relative order of WAL flush, data-page flush, and commit acknowledgment.

Why WAL

Data pages are often scattered across multiple files, whereas WAL (Write-Ahead Log) is append-oriented. During a commit, changes are appended sequentially and grouped for flush, typically more efficient than synchronously writing back all modified pages.

After a crash:

• Changes recorded in the durable WAL but not yet present in data pages can be REDOed;
• Handling of incomplete transactions depends on the engine’s undo or MVCC design;
• Checkpoints define the recovery starting point, but are not equivalent to "all transactions have completed and all dirty pages have been flushed to stable storage."

LSN and pageLSN

Log Sequence Number identifies the position or order of a log entry. Different storage engines use different encodings; in PostgreSQL, LSN represents the byte position within the Write-Ahead Log (WAL), allowing for meaningful comparisons of distances. Do not assume that LSNs across all systems correspond to fixed, immutable offsets within a file.

In ARIES-style designs, page-level records often include a pageLSN. When the LSN of a redo record is less than or equal to the pageLSN, it indicates that the corresponding page may already contain the required data. However, actual redo conditions still require verification of page identity, record type, and engine-specific checks.

ARIES's Three-Phase Recovery Model

ARIES's classic recovery process consists of three phases:

1. Analysis: Reconstruct the transaction table and dirty-page table from checkpoint data, identifying which transactions are winners and which are losers;
2. Redo: Replay transaction history from the earliest required recLSN, including actions from uncommitted transactions, restoring the database to its physical state at the time of the crash;
3. Undo: Reverse the operations of the losers along the log backward chain, writing Compensation Log Records to ensure that if the recovery process itself crashes, it can continue from where it left off.

ARIES commonly integrates with steal/no-force buffer management strategies:

• Steal: Uncommitted dirty pages may be written back to disk, requiring undo information to maintain consistency;
• No-force: Commit operations do not force all data pages to be written back immediately, necessitating redo information to reconstruct state.

This three-phase model serves as a foundational mental framework for understanding recovery algorithms. It should not be misrepresented as PostgreSQL or InnoDB directly replicating the same three-phase implementation in full.

PostgreSQL Goes Beyond a Simple ARIES Three-Phase System

PostgreSQL's Write-Ahead Logging (WAL) provides roll-forward REDO for changes to data files. During crash recovery, it replays the WAL to restore pages to a consistent state. Uncommitted transaction tuple versions are marked invisible by the transaction status and MVCC visibility rules, and are later reclaimed through mechanisms like VACUUM, this system does not perform classic ARIES physical UNDO on every failed transaction during recovery.

PostgreSQL also addresses the risk of torn pages. Full-page writes record a complete page image upon the first modification after a checkpoint (subject to configuration and specific record types), enabling recovery to repair pages that were only partially written.

Official documentation:

• PostgreSQL Write-Ahead Logging
• PostgreSQL WAL Configuration
• PostgreSQL WAL Internals

InnoDB's Redo and Undo

InnoDB uses redo logs to roll forward committed or possibly persisted changes, and employs undo information to manage incomplete transactions and support MVCC (Multi-Version Concurrency Control). Together, background rollback and purge operations, the doublewrite buffer, and checkpointing ensure durability and recovery.

The roles of redo, undo, and binary logs are distinct:

• redo: enables storage engine crash recovery;
• undo: provides old versions required for transaction rollback and consistent reads;
• binary log: supports server-level replication and point-in-time recovery (PITR), with specific formats and configurations.

Do not treat all three logs as part of a single WAL (Write-Ahead Log) system, and do not infer that durability of one log implies synchronization of others or their replicas.

Official references:

• MySQL InnoDB Redo Log
• MySQL InnoDB Undo Logs
• MySQL InnoDB Recovery

What Does a Checkpoint Do?

A checkpoint records a recovery boundary and advances the progress of dirty pages and metadata toward recoverability. Different algorithms may implement fuzzy checkpoints, allowing transactions and page updates to continue without requiring a full world pause or the immediate writing of all dirty pages.

Frequent checkpoints increase write pressure; overly sparse checkpoints prolong recovery time, expand WAL retention, and grow the dirty working set. Tuning must consider the following interrelated factors:

WAL generation rate
checkpoint frequency and reason
checkpoint write and sync duration
dirty page volume
recovery objective
replication and archive retention policies
storage latency and burst behavior

Writing checkpoints too aggressively can result in periodic I/O spikes. Many engines smooth out writes within the checkpoint interval to mitigate this effect.

Group commit

Commit records from multiple transactions can be combined and flushed to a single WAL (Write-Ahead Log) operation:

T1 commit record --\
T2 commit record ----> one durable flush -> acknowledge T1/T2/T3
T3 commit record --/

This approach amortizes the latency of flush operations without compromising synchronous durability. The throughput benefit depends on concurrency, device latency, and the scheduler behavior; under low concurrency, fewer commits are eligible for grouping.

fsync and hardware boundaries

After a database request issues a flush, reliability depends on the entire storage stack fulfilling its responsibilities correctly: the filesystem, kernel, hypervisor, controller, device cache, and power-loss protection. If a device falsely reports that a flush has completed, the WAL protocol cannot recover the unpersisted bytes through software alone.

Do not claim crash durability in benchmarks that disable fsync or full-page protection. If testing a lower-durability mode, the results must clearly label and separately report the potential loss or corruption boundary.

Crash recovery is not equal to backup

WAL (Write-Ahead Logging) can restore the database state prior to a crash, provided that state was durably persisted. However, it cannot alone defend against:

• User accidental deletion that has already been committed;
• Complete destruction of storage or media;
• Loss of both log and data files;
• Application-level logical write errors;
• An attacker who deletes or encrypts all copies;
• Silent data corruption that goes undetected.

True reliability in recovery requires base backups or snapshots, WAL archiving, point-in-time recovery, isolated failure domains, regular restore drills, and clearly defined RPO (Recovery Point Objective) and RTO (Recovery Time Objective) values.

Safe Recovery Experiment

Perform this experiment only on disposable instances and replicated data:

1. Write committed transactions with sequence numbers;
2. Retain one open, uncommitted transaction;
3. Simulate a crash using the documentation-supported crash simulation, rather than shutting down the instance via power failure;
4. Restart the instance and record the recovery log;
5. Verify committed rows, uncommitted rows, constraints, and checksums;
6. Perform an independent restore from backup plus archived WAL files;
7. Measure actual recovery time and compare it against the RTO.

kill -9 Test process crashes only, do not fully simulate torn writes, device cache loss, or full system power loss. Each failure mode must be validated separately.

Chapter Summary

Pages and the buffer pool improve read and write efficiency in real-time operations. The B+ tree provides an ordered access path, enabling efficient range queries and data retrieval. The Write-Ahead Log (WAL) ensures crash consistency by guaranteeing that dirty page writes are safely recorded before being flushed to disk. Only after clearly understanding the boundaries and roles of these three components can we meaningfully discuss the next chapter: how LSM-trees transform random writes into memtable updates, WAL logging, and sorted runs, and how the cost of these operations is deferred to compaction.