4.3 WAL, Checkpoint, and Crash Recovery

The buffer pool permits changes in memory before batch writeback, so a crash can leave only some changes on disk. Write-Ahead Logging (WAL) records the information needed for recovery, allowing commit to finish before every dirty page reaches storage. This lesson separates log insertion, durability, and acknowledgment, then compares how PostgreSQL and InnoDB handle redo and incomplete transactions.

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 numbers identify distinct events, not a required total order from 1 to 5; data-page writeback may occur after commit acknowledgment.

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. State that window in the service contract.

Therefore, the statement "WAL must be flushed before modifying a buffer page" is inaccurate. Memory modification and WAL construction can occur before the durable flush; 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.

These three phases describe the ARIES recovery model. It should not be misrepresented as PostgreSQL or InnoDB directly replicating the same three-phase implementation in full.

PostgreSQL Recovery and MVCC

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. PostgreSQL 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 that acknowledged durable commits remain, uncommitted rows are invisible, constraints hold, and enabled checksums pass;
6. Perform an independent restore from backup plus archived WAL files;
7. Measure actual recovery time and compare it against the RTO.

A commit may become durable while its acknowledgment is lost in the network. Reconcile such unknown outcomes using transaction or business IDs after recovery; no acknowledgment does not prove no commit.

kill -9 tests a process crash. It does not fully simulate torn writes, device-cache loss, or a machine losing power. 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.