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.
| Phase | What it does | Result |
|---|---|---|
| Analysis | Scans the log from the last checkpoint to find dirty pages and active transactions | List of work to redo and undo |
| Redo | Reapplies logged changes to pages that lack them, including uncommitted ones | Disk state matches the moment of the crash |
| Undo | Rolls back transactions that never committed, writing compensation records | Only committed work remains |
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.
| Parameter | Effect on recovery | Trade-off |
|---|---|---|
| Checkpoint interval | Shorter interval means less log to replay | More frequent page writes during normal work |
| Log volume per second | Heavy write load lengthens redo | Driven by workload, not configuration |
| Buffer pool size | Larger pools can hold more dirty pages | Faster normal operation, longer worst-case redo |
| Storage latency | Random page reads dominate redo time | Fast SSDs shorten recovery considerably |
| Long-running transactions | Large undo work at startup | Batch jobs should commit in smaller units |
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.
| Engine | Recovery log | Notable behavior |
|---|---|---|
| PostgreSQL | Write-ahead log (WAL) | Redo only; old row versions make undo unnecessary |
| MySQL with InnoDB | Redo log plus undo logs | Doublewrite buffer protects against torn pages |
| SQL Server | Transaction log | Analysis, redo and undo per database |
| Oracle Database | Online redo logs plus undo tablespace | Instance recovery runs automatically at startup |
| SQLite | Rollback journal or WAL file | Recovery happens on the next connection |
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.
| Failure | Handled by crash recovery | Required method |
|---|---|---|
| Power loss or server crash | Yes | Automatic restart |
| Killed database process | Yes | Automatic restart |
| Failed or destroyed disk | No | Restore from physical backup or replica |
| Accidental DELETE or DROP | No | Point-in-time recovery |
| Silent page corruption | No | Restore plus checksum verification |
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.