You own a 4 TB relational OLTP database with a two-hour nightly low-traffic window. Design its backup strategy in terms of backup types, and explain the tradeoffs behind your choices.

## Start from the constraints Four numbers drive the design: dataset size (4 TB), daily change volume, the two-hour window, and how long a full restore is allowed to take. The first three are known or measurable; the fourth is a business decision. Everything below follows from them. ## Why logical dumps cannot be the base layer A 4 TB logical dump would take many hours to produce, hold an MVCC snapshot open the whole time (bloating the primary), and — worse — restore in a multiple of that, because restore rebuilds every index and revalidates every constraint. Whatever the tolerated downtime is, a logical restore of this database will not meet it. Logical stays in the design, but as a secondary artifact. ## The base layer: physical, full + incremental Physical backups copy data files at IO speed and restore without rebuilding indexes, so they are the only type that scales here. - **Weekly full**: even at good throughput, 4 TB is a long read. Run it at the weekend, from a replica so the primary's IO and cache are untouched. - **Daily incrementals**: only changed blocks, so a nightly run of a fraction of the dataset fits the two-hour window comfortably. - **Chain length**: seven links by Saturday. Restore means full + up to six incrementals applied in sequence, and any damaged link truncates recovery. If the measured restore time is unacceptable, either shorten the cycle (twice-weekly fulls), or use synthetic fulls where the backup system merges the chain server-side so restore is always a two-step operation. Differentials are the middle option: bigger nightly artifacts, but restore is always full + latest. On a database whose daily change rate is low relative to size, differentials stay small for most of the week and buy real restore simplicity. ## The portability layer: logical Keep a weekly or monthly logical dump — ideally schema-only plus per-table dumps of the important tables, or a full dump taken from a restored copy rather than from production. It buys three things physical backups cannot: recovery of a single dropped or corrupted table without restoring 4 TB, a path across major versions and platforms, and an artifact a human can inspect. It is cheap precisely because it is infrequent. ## Where the backups are taken and stored - **Source**: a dedicated replica. Backup load then never competes with user traffic, and a saturated backup does not degrade the application. - **Destination**: object storage, with at least one copy in a separate region and, ideally, a separate account with object-lock/immutability. A backup an attacker or a runaway script can delete is not a backup; ransomware scenarios are the reason immutability is now table stakes. - **Compression and parallelism**: both shorten the window and shrink storage; both cost CPU on the backup host, which is fine when that host is a replica. ## Retention A grandfather-father-son shape: daily incrementals kept a few weeks, weekly fulls a few months, monthly fulls for the compliance horizon. Expiry must be chain-aware — deleting a full silently invalidates every incremental that depends on it. Longer retention is not only about disaster: silent logical corruption is often discovered days later, and retention is what decides whether a clean copy still exists. ## The tradeoffs to state explicitly - **Window vs restore time**: incrementals optimise the nightly window; fulls and differentials optimise the incident. You cannot maximise both, so decide which failure the business fears more. - **Storage cost vs recovery breadth**: longer retention and more copies cost money and buy protection from late-discovered corruption. - **Backup load vs freshness**: taking backups from a replica removes production load but couples backup freshness to replication health, which must then be monitored. - **Simplicity vs optimisation**: every clever mechanism — synthetic fulls, split dumps, tiered storage classes — is one more thing to be broken at 3 a.m. Prefer the simplest scheme that fits. ## What makes the plan credible Numbers, not adjectives: measured full-backup throughput, measured incremental size, and a measured full restore on representative hardware. Until a restore of this exact artifact set has been timed end to end, every duration in the plan is an estimate — and cold storage tiers, throttled downloads and single-threaded restore steps are where estimates usually break.