Crash recovery

On this page 7

Crash recovery is the automatic process by which a database engine, after an unexpected stop such as a power loss, operating system panic or killed server process, brings its data files back to a consistent state that contains every committed transaction and none of the uncommitted ones. In backup & recovery for databases, it is the first and fastest line of defense, because it needs no backup and no operator action: it works from files the engine already keeps on disk.

Crash recovery is what makes the durability and atomicity guarantees of ACID transactions real. It differs from a database restore or point-in-time recovery, which rebuild data from backups after media loss, corruption or human error.

How crash recovery uses the write-ahead log

Crash recovery relies on the write-ahead logging rule: every change is described in a sequential log, and that log record must reach durable storage before the modified data page is written. A commit is acknowledged only after its log records are flushed, so the log always holds enough information to redo committed work and undo unfinished work.

Data pages are changed in memory in the buffer pool and written to disk lazily, which is efficient but means the data files on disk are rarely consistent at the moment of a crash. Some pages contain changes from uncommitted transactions, and some committed changes exist only in the log. Recovery reconciles the two.

Most relational engines follow a scheme derived from ARIES, a well-known recovery algorithm that organizes the work into three phases.

PhaseWhat it doesResult
AnalysisScans the log from the last checkpoint to find dirty pages and active transactionsList of work to redo and undo
RedoReapplies logged changes to pages that lack them, including uncommitted onesDisk state matches the moment of the crash
UndoRolls back transactions that never committed, writing compensation recordsOnly committed work remains
The three phases of ARIES-style crash recovery

Each data page stores the log sequence number of the last change applied to it. During redo, the engine compares that number with the log record and skips changes already present, which makes redo idempotent: if the server crashes again during recovery, the process can simply start over.

Checkpoints and recovery time

A checkpoint limits how much log the engine must read after a crash. At a checkpoint, the engine records which pages are dirty and flushes some or all of them, so recovery can begin from that point instead of from the start of the log.

Recovery time therefore depends mainly on the volume of log generated since the last checkpoint and on the speed of the storage. The following table shows the parameters that matter most.

ParameterEffect on recoveryTrade-off
Checkpoint intervalShorter interval means less log to replayMore frequent page writes during normal work
Log volume per secondHeavy write load lengthens redoDriven by workload, not configuration
Buffer pool sizeLarger pools can hold more dirty pagesFaster normal operation, longer worst-case redo
Storage latencyRandom page reads dominate redo timeFast SSDs shorten recovery considerably
Long-running transactionsLarge undo work at startupBatch jobs should commit in smaller units
Parameters that influence crash recovery time

Typical recovery takes seconds to a few minutes on well-tuned systems, but it can stretch much longer after a crash during a large bulk load. Engines expose settings for this trade-off, for example checkpoint_timeout and max_wal_size in PostgreSQL or innodb_log_file_size in older MySQL releases. Some engines open the database for use after redo and before undo finishes, rolling back old transactions in the background to shorten downtime.

Crash recovery in common database engines

All major relational engines implement crash recovery, but their log names and mechanics differ. Knowing the local terminology helps administrators read startup messages and tune the right settings.

EngineRecovery logNotable behavior
PostgreSQLWrite-ahead log (WAL)Redo only; old row versions make undo unnecessary
MySQL with InnoDBRedo log plus undo logsDoublewrite buffer protects against torn pages
SQL ServerTransaction logAnalysis, redo and undo per database
Oracle DatabaseOnline redo logs plus undo tablespaceInstance recovery runs automatically at startup
SQLiteRollback journal or WAL fileRecovery happens on the next connection
Crash recovery mechanisms in widely used engines

The MySQL binary log is not used for InnoDB crash recovery itself; it serves replication and point-in-time recovery. During startup, however, InnoDB and the binary log are reconciled so that transactions prepared under two-phase commit end up either in both or in neither.

What crash recovery cannot fix

Crash recovery protects only against loss of volatile state, meaning memory contents and in-flight writes. It assumes that the log and data files themselves are intact, so it offers no protection when the storage is lost or the data is wrong for logical reasons.

FailureHandled by crash recoveryRequired method
Power loss or server crashYesAutomatic restart
Killed database processYesAutomatic restart
Failed or destroyed diskNoRestore from physical backup or replica
Accidental DELETE or DROPNoPoint-in-time recovery
Silent page corruptionNoRestore plus checksum verification
Failures and the right recovery method

The most dangerous misconfiguration is storage that acknowledges writes before they are durable. Disabled fsync, volatile write caches without battery or capacitor protection, and some virtualized disks can lose log records the engine believes are safe. Recovery then completes without error yet silently loses committed transactions or leaves corrupted pages.

Operational practices that support reliable recovery

Reliable crash recovery is mostly a matter of honest storage and tested assumptions. Administrators can verify the behavior long before a real outage.

  • Keep synchronous commit and fsync enabled on production systems unless data loss is explicitly acceptable.
  • Use storage with power-loss protection and verify that write caches honor flush requests.
  • Enable page checksums so corruption is detected rather than replayed.
  • Size checkpoints to meet a target recovery time, then measure it.
  • Test by cutting power or killing the server in a staging environment under realistic load.
  • Monitor startup logs for recovery duration and warnings.

A non-technical view

Imagine a cashier who writes every sale in a notebook before putting money in the drawer. If the lights go out mid-sale, the cashier can read the notebook afterward, finish the sales that were completed and cancel the half-finished one. The drawer ends up correct.

Crash recovery works the same way. The database keeps a running diary of every change and, after a sudden stop, uses that diary to tidy up its files. Nothing that was confirmed is lost, and nothing half-done is kept. It does not help, however, if the notebook itself burns, which is why backups still matter.

A concrete example

An online retailer runs PostgreSQL on a server that loses power during a busy evening when about 1,200 orders per minute are being written. Several hundred transactions are in flight, and many recently committed orders exist only in the write-ahead log, not yet in the data files.

When power returns, the server starts and PostgreSQL detects that it was not shut down cleanly. It reads the log from the last checkpoint, about 4 GB of WAL, and replays it in roughly 90 seconds on NVMe storage. Every order whose customer saw a confirmation is present, and the unfinished transactions leave no trace.

The administrators confirm the outcome with order counts against the payment provider. Because the team had earlier tested power cuts in staging and chosen drives with power-loss protection, the incident ends with zero lost orders and under three minutes of downtime, without touching a single backup.