Database Recovery

How a database keeps committed data through crashes, disk loss and human error: the write-ahead log, checkpoints, ARIES-style restart, and backups with point-in-time recovery.

Overview

A database promises that once it says a transaction committed, the change is permanent. That is the D in ACID, durability. Recovery is how it keeps that promise when things break: the process crashes, the machine loses power, a disk dies, or someone runs a DELETE without a WHERE clause.

Two ideas do almost all the work. The write-ahead log (WAL) records every change in an append-only file before the change touches the data files, so after a crash the database can replay the log and rebuild what was lost from memory. Backups plus archived log files let you rebuild a whole database on a new machine, up to any moment you choose.

This chapter explains both, how PostgreSQL, MySQL's InnoDB and SQL Server apply them, and how to choose a backup design by the data you can afford to lose and the time you can afford to be down.

What you will be able to do

  • Explain the write-ahead logging rule and why it makes commits both safe and fast.
  • Walk through crash recovery: checkpoints, redo and undo, and why ARIES repeats history.
  • Explain how real engines differ, including why PostgreSQL has no undo pass.
  • Design backups with point-in-time recovery, and reason about RPO and RTO.
  • Name the failure each tool protects against, and the ones it does not.

Key concepts

  • Write-ahead logging
  • Log sequence number
  • Group commit
  • Steal and no-force
  • Checkpoints
  • Torn pages
  • ARIES: analysis, redo, undo
  • Point-in-time recovery
  • RPO and RTO

Interview questions

  • How does a write-ahead log make commits durable?
  • Explain the three phases of ARIES recovery.
  • Why does PostgreSQL not have an undo phase?
  • How do checkpoints work, and how would you tune them?
  • How does point-in-time recovery work?
  • A developer ran DELETE without a WHERE clause in production. What are your options?
  • What are RPO and RTO, and how do you meet them?
  • What is a torn page, and how do databases handle it?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio