Skip to content
Tutorials Center
My learning

Topics

Lesson 47 of 79 ยท Recovery

Checkpoints, ARIES and Backups

Learn fuzzy checkpoints, the three ARIES recovery phases (analysis, redo, undo), LSNs, point-in-time recovery and backup types, with live Python simulations.

With write-ahead logging, a database can always rebuild a correct state from its log. But a server that has been running for months has a log that is gigabytes long. Replaying all of it after every crash would take hours. Checkpoints solve this by recording a safe starting point, and ARIES, the recovery algorithm behind most commercial and open-source engines, shows how to use that starting point in three well-defined passes.

Crash recovery only helps when the disk survived. For disks that did not, you need backups plus the archived log, which also give you point-in-time recovery. This lesson covers all three: checkpoints, ARIES, and backups.

Why checkpoints

Bound recovery time Do not replay the log from day one.
Remember the state Which transactions run, which pages are dirty.
Fuzzy, not frozen Work continues while the checkpoint is written.
1 / 2

Checkpoints

A sharp checkpoint would stop all work, write every dirty page to disk, and note in the log that everything before this point is on disk. Recovery would then begin at the checkpoint. It is simple, but stopping a busy database is unacceptable.

A fuzzy checkpoint lets transactions continue. The system writes a checkpoint record that contains two tables, a snapshot of the system's memory at that moment:

  • The active transaction table (ATT): each running transaction, its status and its most recent log record (lastLSN).
  • The dirty page table (DPT): each page that has unflushed changes in the buffer pool, together with its recLSN, the LSN of the first log record that made it dirty since it was last written.

The checkpoint is "fuzzy" because the tables describe a moment that is already over by the time they are written. The system also stores the checkpoint's position in a small, well-known master record, so recovery knows where to begin. Background writers then flush old dirty pages gradually, which keeps the DPT small and the redo work short.

ARIES: Algorithm for Recovery and Isolation Exploiting Semantics

ARIES rests on three principles:

  1. Write-ahead logging. As before.
  2. Repeating history during redo. Recovery first re-applies everything that was logged, including updates of transactions that will be rolled back, so the database returns to its exact state at the crash.
  3. Logging changes during undo. Every undo step writes a compensation log record (CLR). A CLR is redo-only and contains an undoNext pointer to the next record of that transaction to undo, so a crash in the middle of recovery never undoes the same step twice.

Each data page stores a pageLSN, the LSN of the last log record applied to it. This one number makes redo safe to repeat: if pageLSN >= recordLSN, the change is already on the page and is skipped.

flowchart LR
    C["Crash"] --> A["1. Analysis<br/>from last checkpoint<br/>forward"]
    A --> R["2. Redo<br/>from smallest recLSN<br/>forward"]
    R --> U["3. Undo<br/>losers, newest<br/>record first"]
    U --> N["Normal work resumes"]

After a crash, ARIES makes one forward pass to learn the state, one forward pass to repeat history, and one backward pass to undo the losers.

The three passes

1. Analysis. Starting at the last checkpoint, rebuild the ATT and DPT by scanning forward to the end of the log. An update record adds its transaction to the ATT (with its record as the new lastLSN) and adds its page to the DPT if absent (the record's LSN becomes the recLSN). A commit marks the transaction committed, and an end record removes it. At the end, the ATT holds the transactions that must be rolled back (losers), and the DPT holds a superset of the pages that might be out of date on disk.

2. Redo. Start at the smallest recLSN in the DPT and scan forward. For each update or CLR record, redo it unless the page is not in the DPT, or the record is older than the page's recLSN, or the page's pageLSN already covers the record. Redoing means re-applying the change and setting the page's pageLSN.

3. Undo. Roll back all losers together. Repeatedly take the largest LSN among their lastLSNs, undo that record, write a CLR, and continue with the record's prevLSN (or the CLR's undoNext if you reach one). When a transaction has nothing left to undo, write an END record.

The simulator below runs ARIES on a small crash. Pages hold one value each. T2 committed, while T1 and T3 were still running. Page P1 and page P4 were written to disk early (steal), while P2 and P3 were not.

Python ARIES analysis, redo and undo

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the trace. The analysis pass finds T1 and T3 as losers and four dirty pages. Redo starts at LSN 1 (the smallest recLSN) and skips the updates that the disk already has (P1 and P4, which were stolen) but reinstalls P2 (T2 committed) and P3 (T1's update, redone even though T1 will be undone, because ARIES repeats history). Undo then walks T3 and T1 backwards, restoring P4, P3 and P1 and writing CLRs. The final state contains the committed T2 change and none of the others.

Point-in-time recovery

Crash recovery brings the database to the moment of the crash. Point-in-time recovery (PITR) brings it to any chosen moment, which is how you recover from a human mistake such as DELETE FROM orders without a WHERE. It needs two things: a base backup of the data files and a continuous archive of the log from that backup onward. To recover, you restore the base backup and then replay the archived log up to (but not including) the moment before the mistake.

Python Point-in-time recovery from a backup plus an archive

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Stopping at 10:04 gives 150 orders; replaying the entire archive reproduces the accidental deletion. PostgreSQL, MySQL, Oracle and SQL Server all offer this workflow in their own forms (continuous WAL archiving, binary logs, archived redo logs, log backups). Those are server features: their commands cannot run in a browser, so they are described but not executed here.

Backups

A backup protects against media failure, disasters and human error. The main distinctions:

Dimension Options Notes
Scope Full, differential, incremental Full copies everything. Differential copies everything changed since the last full backup. Incremental copies everything changed since the last backup of any kind.
Content Physical vs logical A physical backup copies data files or blocks (fast to take and restore, tied to the same engine version and platform). A logical backup exports the data as SQL statements or rows (portable across versions, slower to restore).
Availability Cold, warm, hot Cold means the database is shut down. Hot means it keeps serving users while the backup runs; this needs the log to make the copy consistent.

Restoring from incrementals requires the last full backup plus every incremental after it, in order. Differentials need only the last full plus the latest differential, at the cost of larger backups over time.

Two numbers define a backup strategy: the recovery point objective (RPO), how much recent data you can afford to lose, and the recovery time objective (RTO), how long a restore may take. An RPO of seconds requires log archiving or replication, not nightly backups. A common rule is to keep three copies of the data, on two kinds of media, with one copy off-site, and to test restores regularly: a backup that has never been restored is only a hope.

SQLite shows the logical/physical idea in miniature. Its command-line shell and SQL can produce a consistent copy of a live database:

SQL
VACUUM INTO 'backup-2026-11-01.db';   -- writes a compacted copy of the open database
TEXT
sqlite3 app.db ".backup 'backup.db'"   -- the shell's online backup command
sqlite3 app.db ".dump" > backup.sql    -- logical backup: SQL text that rebuilds the data

These need a database file, so they are read-only examples here. For the server-side commands and a practical checklist, see Backup and Maintenance.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does a fuzzy checkpoint record contain?

    The checkpoint stores the ATT and DPT so analysis can start there instead of at the beginning of the log. Data pages are not copied; they are flushed gradually in the background.
  2. 2. Why does ARIES redo the updates of transactions that will be rolled back anyway?

    Repeating history makes the database identical to its state just before the crash. Undo then rolls back the losers like it would in normal operation.
  3. 3. What is the purpose of a compensation log record (CLR)?

    A CLR is redo-only and carries an undoNext pointer, so if recovery crashes and restarts, work already undone is skipped.
  4. 4. Which backup type needs the last full backup and ALL later backups to restore?

    Each incremental backup contains only changes since the previous backup, so the chain is needed. A differential contains all changes since the last full backup.
  5. 5. To undo an accidental DELETE from two hours ago on a server database, you typically use

    That is point-in-time recovery: restore a backup taken before the mistake and replay the log only until the chosen moment.

Summary

  • A fuzzy checkpoint writes the active transaction table and dirty page table into the log without stopping work, bounding how much log recovery must read.
  • ARIES recovers in three passes: analysis rebuilds the tables, redo repeats history (skipping changes whose pageLSN shows they are applied), undo rolls back losers and logs compensation records.
  • LSNs tie everything together: pageLSN on pages, prevLSN in records, recLSN in the dirty page table, undoNext in CLRs.
  • Point-in-time recovery restores a base backup and replays the archived log up to a chosen moment.
  • Backups differ by scope (full, differential, incremental), content (physical or logical) and availability (cold, warm, hot); plan them from your RPO and RTO and test the restore.

Next: Shadow Paging Recovery.