4.4Failure recovery and logging

For a DBA preparing for a sudden power loss or disk failure on a database server, deciding "which logging approach, checkpoint interval, and recovery procedure to use" is an important judgment that balances the durability guarantee against recovery time (RTO). Logging too much increases write overhead, while spacing checkpoints too sparsely lengthens recovery time after a failure. This section builds the judgment needed to decide which recovery approach to apply for each failure type.

4.4.1The WAL (write-ahead logging) principle

• The WAL (write-ahead logging) principle is to write the content of a change—as a log record (before-image and after-image)—to non-volatile storage before the change is actually applied to the database's data pages on disk. Keeping this order means that even if a failure occurs before the change is fully applied to the data itself, as long as the log survives, the change can be reproduced or undone, forming the foundation for guaranteeing both durability and consistency.
• The WAL principle is precisely what lets a DBMS improve write performance by deferring (buffering) the writes to the data pages themselves and applying them in bulk—as long as the log is reliably written first, safety is not compromised even if applying changes to the data pages themselves lags somewhat, since the log can restore them after a failure. Because writing to the log itself is mostly sequential append writes, it can be done faster than data-page updates, which require random I/O.

4.4.2Checkpoints and UNDO/REDO

• A checkpoint is the operation of reliably applying the log's content up to a certain point to the data itself and recording that point. During failure recovery, only the log entries from the most recent checkpoint onward need to be read, rather than from the beginning of the log, which substantially shortens the time needed for recovery. Narrowing the interval between checkpoints speeds up recovery but increases the I/O load of the checkpoint operation itself, affecting performance during normal operation.
• Failure recovery is performed via a combination of UNDO (rollback) and REDO (redo/roll-forward). REDO re-applies from the log any committed changes recorded in the log since the checkpoint but not yet reflected in the data itself (this is roll-forward). UNDO undoes the changes of transactions that started after the checkpoint but were not committed—i.e., were still in progress at the moment of failure (this is rollback). Many DBMSes combine the two in the order "REDO (re-apply forward) then UNDO (roll back the incomplete portion)."

Suppose a DBA for a core business system is verifying the recovery procedure from a sudden power loss on the database server (a system failure). Because this type of failure leaves the disk itself intact but loses the buffer contents held in memory, the DBA first checks the log position at the most recent checkpoint and reads only the log from that point through the moment of failure. Next, the DBA identifies, from within the log, changes that were committed but not yet reflected in the data itself (the data pages), and rolls them forward via REDO (re-application)—thanks to the WAL principle (the log is written first), this reproduces the committed changes without loss. The DBA then identifies transactions that began after the checkpoint but reached the failure without ever committing, and rolls them back via UNDO (undo), removing the half-finished updates from the database. A common mistake to avoid here is the reasoning that "REDO and UNDO should converge to the same final state regardless of which is done first, so the order does not matter"—performing UNDO first risks mistakenly rolling back a later committed change that has not yet been applied by REDO, so the standard recovery procedure must strictly follow the order of completing REDO first (finishing the application of all committed changes) and only then performing UNDO (rolling back the incomplete portion). Note also that this is the recovery procedure for a system failure in which the disk itself is intact; for a media failure, in which the disk itself is damaged, the log alone cannot recover the database, and a separate procedure—restoring from a backup and then rolling forward via the log—is required instead.

4.4.3Section summary

• WAL is the principle of writing the log before the data itself, forming the foundation for both the durability guarantee and improved write performance
• A checkpoint limits recovery to reading only the log since the most recent one, shortening recovery time
• For a system failure, complete REDO (re-applying committed changes) first, then perform UNDO (undoing the incomplete portion)—a media failure requires restoring from a backup first