How would you choose a checkpoint interval or recovery-time target for a production database, and what are you trading off in each direction?

## Frame it as a requirement, not a knob The wrong version of this answer is a number ('five minutes'). The right one starts from what the system owes its users, because the checkpoint interval is the main lever converting *steady-state cost* into *recovery time*, and only the business can say which side matters more. Two requirements bound it: - **Recovery time objective (RTO)** for an unplanned restart: how long may the database take to come back after a crash? - **Cost budget**: how much extra write I/O and log volume the storage can absorb without hurting the latency the application actually sees. Note what is *not* on the list: durability and data loss. Committed data survives because the log is flushed at commit; the checkpoint interval does not change the recovery point objective at all. Candidates who conflate the two are making a serious error. ## Deriving the interval from the RTO Recovery time is dominated by scanning and replaying the log from the recovery start point to the end, plus undoing uncommitted transactions, plus the time for the system to become useful again with a cold cache. A usable method: 1. Measure the log generation rate under peak write load (MB/s). 2. Measure replay throughput empirically - restore a backup, kill the process under load, and time the restart. Replay is typically *slower* than logging, because it is random page reads against a cold buffer pool rather than sequential appends. 3. Compute the outstanding log an interval implies, divide by replay throughput, add undo time and warm-up. 4. Leave real margin - crashes happen at the worst moment, which is peak load, and a cold cache after restart means the first minutes of service are slow even after recovery formally completes. The conclusion is usually 'much shorter than intuition suggests' for write-heavy systems and 'the default is fine' for read-heavy ones. ## Pricing the shorter interval Three costs rise as the interval shrinks: **Write amplification of data pages.** The deferred-write design lets a page absorb many updates between flushes. Halve the interval and a hot page is flushed about twice as often, coalescing half as many updates each time. On a workload with a small, extremely hot working set this factor is large. **Log volume from full-page images.** Where torn-page protection works by logging a complete page image on the first modification of that page after each checkpoint, every checkpoint resets each page to 'first touch'. Halving the interval can therefore nearly double the full-image traffic. This surprises people, because it means *more frequent checkpoints can make the log bigger*, not smaller - and a bigger log means more replication traffic, bigger archives, and longer replay per unit time. **Continuous flush pressure.** More frequent flushing competes with the foreground workload for I/O; paced properly it is a hum rather than a spike, but the capacity is still consumed. ## Pricing the longer interval - Recovery grows toward minutes or tens of minutes, and the system is *down* for all of it. - More dirty data accumulates, so each checkpoint has a larger burst to deliver, worsening latency percentiles unless pacing is well configured. - Log space and archive lag grow, and any stalled reclaim point becomes an incident faster. ## Things that break the simple model - **The interval is only an upper bound on the recovery start point.** Replay begins at the oldest change among pages still dirty, so a page that has stayed dirty for a long time drags the start point backwards regardless of the interval. Engines flush long-dirty pages preferentially for exactly this reason, and it is why measured recovery time can exceed the arithmetic. - **Volume triggers.** Most engines also start a checkpoint when a threshold of log has been produced. Under peak write load this fires before the timer, so the *effective* interval is shorter than configured - and it is a signal the sizing assumptions no longer hold. - **Replicas change the calculus.** With a fast automatic failover to a standby, unplanned downtime is a failover, not a recovery, so the RTO pressure on the primary's checkpoint interval largely disappears and you can optimize for steady-state cost. This is the architectural answer a principal is expected to reach: HA topology dominates checkpoint tuning. - **Planned restarts are not crashes.** A clean shutdown ends with a full checkpoint, so maintenance restarts are fast regardless of the interval; only crashes pay. ## Validating and revisiting Make the choice testable: run a real crash-and-recover drill on production-scale data at production write rates, measure it, and keep measuring after workload growth. Watch how much log was replayed, how long undo took, and how long p99 latency stayed degraded after startup while the cache re-warmed. If recovery time is dominated by the cold cache rather than by replay, the checkpoint interval is no longer the lever and warm-up or failover is. ## The answer in one line Set the longest interval that meets a measured recovery objective, price the shortening in write amplification and full-page-image log volume, and recognize that with a good failover story the whole tradeoff shifts toward steady-state cost.