Checkpoints & Backups

Your database has been running for two years. Its log is enormous.

A crash happens. Does it really replay two years of history to start up?

Why & what

It would, without checkpoints. And "restarts in six hours" is not an acceptable database.

A checkpoint is a marker saying "everything before this point has been written to the data files." Recovery only needs to replay the log from the last checkpoint.

Checkpoints handle crashes. They don't handle a dead disk — for that you need a copy elsewhere.

A backup is a copy of the database stored separately, so you can rebuild after losing the storage itself.

How it works

1. The database checkpoints periodically. It flushes pending changes to the data files and writes a marker in the log.
2. A crash happens later. Recovery starts at the last checkpoint, not the beginning of time. Everything before it is already safely in the data files. Restart takes seconds instead of hours.
3. Now the disk itself dies. Checkpoints are useless — they lived on the dead disk. You need the backup.
4. Three backup styles: oFull — everything, every time. Slow to take, simple and fast to restore. oIncremental — only what changed since the last backup of any kind. Fast to take; restoring means replaying the full plus every increment in order. oDifferential — everything changed since the last full backup. Middle ground: bigger than incremental, but restore needs only two pieces.
5. Pick by which pain you prefer. Nightly incrementals are cheap to take and painful to restore. Full backups are the reverse. Most real setups combine them: weekly full, daily incremental.

Notice: a checkpoint shortens recovery — a backup survives losing the disk.

Common confusion

A checkpoint is not a backup. A checkpoint lives inside the same database on the same disk and only shortens crash recovery. A backup is a separate copy that survives the disk dying. Losing this distinction is a reliable way to fail a question.

Second: an untested backup isn't a backup. The failure everyone actually hits isn't a missing backup — it's a backup nobody ever tried restoring, discovered to be corrupt on the worst possible day. Saying this out loud in an interview lands well, because it's what operations people care about.

Interview angle

1. "What is a checkpoint and why does it exist?" — A marker showing the log has been applied up to that point, so recovery replays only from there rather than from the beginning.
2. "Difference between full, incremental and differential backups?" — Full copies everything; incremental copies changes since the last backup; differential copies changes since the last full. Trade-off is take-time versus restore-time.
3. "Is a checkpoint a backup?" — No. A checkpoint shortens crash recovery on the same disk; a backup survives losing that disk.

Recap

Checkpoints mark how far the log has been applied so crash recovery is short, while backups are separate copies that survive losing the storage full, incremental and differential trading take-time against restore-time.