How to Choose a Database Backup Retention Policy That Scales

A database backup retention policy is the rule set that decides how many backups you keep, how long each one lives, and where each copy sits. Getting it right means you keep enough history to survive a bad deploy, a corrupted table, or a slow-building data problem, without paying to store years of snapshots nobody will ever restore.

Most teams start with a single line in a cron job and a directory that grows forever. That works until the disk fills or a restore takes longer than the incident window. A retention policy is really three separate decisions: the recovery points you need, the retention window for each tier, and the storage class each tier lives in. Separate them and the rest follows.

Start From Recovery Objectives, Not From a Number

Two numbers drive everything: your recovery point objective, which is how much data you can afford to lose, and your recovery time objective, which is how long a restore may take. Retention is downstream of both. If you can lose an hour of writes, you need hourly recovery points. If you can lose a day, daily is enough and your storage bill drops sharply.

The retention window should be longer than the slowest failure you expect to detect. A dropped table is noticed in minutes. A bug that quietly writes wrong values into a column may not surface for weeks. That second case is why a policy built only on recent backups fails: by the time you know, every backup inside your short window already contains the corruption. You need at least one tier that reaches back far enough to predate the damage.

A common shape is a tiered policy. Keep frequent recent backups for fast recovery, fewer older ones for the medium term, and sparse long-term copies for compliance or forensic use. Each tier can live on cheaper storage because you restore from it rarely, if ever.

How Retention Is Actually Implemented

With plain files, retention is a naming convention plus a cleanup command. Dump files carry a timestamp, and a script deletes anything older than the window. The risk here is that deletion is unconditional: if your backup job silently fails for a week, the cleanup still runs and you lose the only good copies you had.

Guard against that by pruning only after a successful new backup, and keep a floor on the count. A short shell loop makes the idea concrete:

find /var/backups/db -name 'app-*.sql.gz' -mtime +14 -print
# then, only if today's dump verified:
find /var/backups/db -name 'app-*.sql.gz' -mtime +14 -delete

Run the first form first. Printing what would be deleted before deleting it is the cheapest safety net you will ever add. Note that -mtime counts by file modification time, so a restored or copied file can look newer than it is. If that matters, encode the backup date in the filename and parse it instead of trusting the filesystem.

Managed database engines expose the same idea through configuration. A typical retention setting looks like this:

retention:
  full: 30d
  incremental: 7d
  archive: 365d
  minimum_copies: 3
  prune_after_success: true

The exact keys differ between systems, but the concepts do not: a window per tier, a floor on how few copies you will tolerate, and a rule that pruning never runs ahead of a verified success. Read your engine's documentation for the real directive names rather than copying a snippet from a blog post.

Point in Time Recovery Changes the Math

If your database writes a continuous log of changes, a base backup plus that log lets you restore to any moment inside the log window. This is usually called point in time recovery. It is far more useful than a pile of daily dumps, because you can land just before the bad statement ran rather than at midnight the night before.

With point in time recovery, retention splits in two. The base backups can be few and far between, since each one is only a starting point. The log archive is what determines how far back you can reach, and it is usually the larger and faster-growing of the two. Many teams keep a long log window and a short base backup window, then wonder why storage keeps climbing. Check which side is growing before you shorten anything.

You can confirm the archive is actually advancing by listing the most recent segments and comparing their timestamps to now:

ls -lt /var/lib/db/wal_archive | head -n 5
# -rw------- 1 postgres postgres 16M Jan  1 03:14 0000000100000000000000A1
# -rw------- 1 postgres postgres 16M Jan  1 03:19 0000000100000000000000A2

If the newest file is hours old, the archive is stalled and your effective recovery window is shrinking even though your retention setting says otherwise. Retention settings describe intent. Only the archive directory describes reality.

Where the Copies Live

Retention is not only about time, it is about location. A backup on the same volume as the database protects you from a bad query and nothing else. A backup on the same host protects you from a disk failure and nothing else. You want at least one copy that survives the loss of the entire primary environment.

Storage class matters for cost, but so does restorability. Cold storage that takes hours to retrieve is fine for a compliance copy and useless during an incident. Label each tier by how you would actually use it, not by how cheap it is. If a tier cannot be restored inside your recovery time objective, it does not count toward that objective.

Encryption and integrity checks belong here too. A backup you cannot verify is a hope, not a plan. Store a checksum alongside each file and test it on a schedule. The restore test is the only part of a retention policy that proves the policy works.

Choosing Your Numbers

Work backward from the failure modes you actually have. For a small application, daily full backups kept for a couple of weeks, plus a monthly copy kept for a year, covers most accidents. For anything with continuous writes and a real recovery time objective, add point in time recovery and size the log window to the slowest corruption you expect to detect.

Set a floor on copy count so a run of failures cannot empty your archive. Prune only after a verified success. Keep one copy off the primary host. Then measure: watch the growth rate of each tier for a month before you tune it, because the number that matters is the one you observed, not the one you guessed.

What To Do Next

Write your current policy down as three lines: what recovery points you keep, how long each tier lives, and where each tier is stored. Then restore from the oldest copy in each tier and time it. If a tier fails that test, either fix it or delete it, because a backup tier you cannot restore from is just storage you are paying for. Repeat the restore test on a schedule and keep the results, so the next person who touches the policy is arguing about evidence instead of opinions.