How would you design a retention and rotation policy for database backups - which tiers to keep, how long to keep each, and how retention interacts with the window over which you can still recover the database to an arbitrary past moment?

## Start from scenarios, not from storage cost Retention answers one question: *how far back might we need to go, and at what granularity?* Different failures have wildly different detection latencies: - A failed migration or a bad `DELETE` is noticed in minutes to hours. It needs **fine granularity**, not depth - the ability to land on the second before the damage. - Slow data corruption or a subtly wrong batch job may go unnoticed for weeks. It needs **depth** at coarse granularity - a clean weekly copy from before the regression started. - Legal discovery, audit, or a contractual retention clause needs **depth measured in years** at very coarse granularity, and often immutability. A single flat policy ("keep 30 days") serves none of them well: it is too shallow for compliance and too expensive for the fine granularity nobody needs at day 29. ## A workable tiering A common shape, often called grandfather-father-son: | Tier | Cadence | Retention | Log retained? | Purpose | |---|---|---|---|---| | Operational | daily full or incremental | 7-14 days | yes, complete | arbitrary-moment recovery from recent mistakes | | Medium | weekly full | 4-12 weeks | no | late-discovered corruption, quarter-end questions | | Long | monthly or quarterly full | 1-7 years | no | regulatory, contractual, forensic | The operational tier is the expensive one because it retains the whole log stream; the long tier is cheap per copy but must live off-site and, for the highest assurance, under write-once retention. ## How retention constrains the fine-grained recovery window The ability to recover to an arbitrary past moment is bounded by **the oldest usable base backup plus an unbroken run of archived log from that backup forward**. Two consequences follow, and both are the classic interview trap: 1. **Expiring the log truncates the window from the far end.** If you keep 14 days of log but 90 days of fulls, the 30-day-old full can only be restored to the exact moment it was taken, not to any point after it - the log needed to roll forward is gone. That is a legitimate design (a coarse restore point), but it must be a deliberate choice, not a surprise. 2. **Expiring a base backup orphans its log.** Log segments older than the oldest retained base backup are unusable and should be expired with it; otherwise you pay to store bytes that can never be replayed. ## Dependency-safe expiry With incremental or differential backups, an archive is not a standalone object - it is a node in a chain. Deleting a full backup silently invalidates every incremental and differential built on it. Rules: - Expire by **chain**, never by file age. A good backup tool does this natively; a hand-rolled `find -mtime +N -delete` cron does not, and it is a recurring source of "we had 60 days of backups and none of them restored". - Retention counts should be expressed in **complete restorable sets**, e.g. "always keep at least 3 restorable full-chains", so a failing backup job cannot cause expiry to erode you down to zero good copies. - Never let expiry run **before** the newest backup has been verified. Otherwise a corrupted new backup plus an eager expiry removes the last good one. ## Rotation, immutability, and cost Rotation is the mechanical side: which copies move to colder or remote storage and when. A typical flow keeps the operational tier on fast same-region storage, copies weeklies and monthlies to a remote account with object-lock retention matching the retention period, and lets storage lifecycle rules transition older objects to archival classes. Two cautions: archival storage classes have **retrieval latency and cost** that must be reflected in your recovery-time expectations for those tiers, and object-lock retention must be set to at least the policy period or lifecycle rules will happily delete what compliance requires. ## Operating the policy - Encode retention in the backup tool's configuration, not in ad-hoc scripts, so expiry understands dependencies. - Alert on both directions: too few restorable sets (recovery risk) and unexpected growth (expiry silently failing). - Re-verify after every expiry cycle; expiry bugs are invisible until a restore. - Review the policy whenever data volume, regulation, or the recovery-point objective changes - retention is where the objective becomes a bill. The answer an interviewer wants: tiers keyed to detection latency, a fine-grained window bounded by log retention, expiry that respects backup chains, retention counted in restorable sets, and verification after expiry.