How to Choose a Database Backup Frequency Based on How Much Data You Can Afford to Lose
Database backup frequency should be set by measuring two numbers: how much data you can afford to lose (your recovery point objective, or RPO) and how long a restore takes (your recovery time objective, or RTO). Everything else, the default daily cron job, the vendor's recommended schedule, the habit you inherited, is guesswork until those two numbers exist. This article is about turning them into a concrete interval, a retention window, and the mechanics that make the schedule actually hold.
Start from the recovery point objective, not the default schedule
Write down the RPO as a duration. If a disk dies at 14:37, what is the oldest state you would accept coming back? One hour? Fifteen minutes? A day? That duration is your maximum backup interval, and in practice you should aim for half of it, because a backup taken at 14:00 and one taken at 14:30 both have to be valid for the interval to mean anything. The RTO is the other half of the decision. A backup you can take every five minutes but cannot restore in under an hour is not a five minute RPO in any useful sense.
Do not guess the RPO from how the application feels. Look at what the data is. A ledger or an order table usually implies a tight RPO because a lost hour means lost money that someone will notice. A log archive or an analytics warehouse often tolerates a day. If different tables in the same database have different tolerances, you have two backup jobs, not one compromise.
Choosing a database backup frequency for each engine
The mechanism differs by engine, and the mechanism is what determines the floor on your interval. A logical dump via pg_dump or mysqldump takes a consistent snapshot but holds locks or a long read transaction and scales with database size, so it rarely works as a high frequency tool. Physical backups and write-ahead log or binary log shipping are what let you go below the dump interval.
On PostgreSQL, the practical pattern is a periodic base backup plus continuous WAL archiving. The base backup can run daily or weekly; the WAL archive carries you between base backups at whatever granularity you configure. Check what the server is actually doing with:
psql -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('archive_mode','archive_command','wal_level');"
name | setting | unit
------------------+---------+------
archive_mode | on |
archive_command | test ! -f /backup/wal/%f && cp %p /backup/wal/%f |
wal_level | replica |
If archive_mode is off, your real RPO is the time between base backups no matter what the documentation says. On MySQL or MariaDB the equivalent is binary log retention and a periodic physical backup with xtrabackup or a logical dump. The binary log expiry is controlled by binlog_expire_logs_seconds (or expire_logs_days on older versions), and that value is your ceiling on how far back you can recover point in time:
mysql -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';"
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_bin | ON |
| binlog_expire_logs_seconds| 604800|
+---------------------------+-------+
Match the retention window to the restore path
Retention is a separate decision from frequency, and confusing the two is the most common mistake. Frequency answers "how fresh is the newest copy"; retention answers "how far back can I go if the problem is not a dead disk but bad data". A backup taken every fifteen minutes with a one day retention cannot help you when someone runs an incorrect UPDATE on Friday and the mistake is found on Monday. Decide the retention window by asking how long it takes a data problem to be noticed, then add margin.
Retention also has to match what you can restore. If you keep thirty days of full logical dumps, you are storing thirty copies of the whole database and each restore is a full reload. If you keep one base backup plus thirty days of incremental logs, storage is far smaller but the restore is base plus replay, which is slower and more error prone. Test both on a real copy of the data before you commit to a window. A retention policy you have never restored from is a guess.
Make the schedule explicit in configuration
Put the interval and retention in a config file the backup tool reads, not in a shell history. The shape below is generic but the keys are the ones that matter: how often, how many copies, and when to prune. Whatever tool you use, it should have an equivalent.
[backup]
interval = "15m"
retain_count = 96
retain_days = 30
verify_after_backup = true
prune_after_backup = true
target = "s3://backups/db-primary"
The retain_count and retain_days pair is deliberate: keep enough copies to cover a short outage at fine granularity, and enough days to cover slow discovery of bad data. The verify_after_backup flag matters because a backup that fails silently is worse than no backup, since it gives you false confidence. If your tool does not verify, add a restore test to the schedule and treat a failure as a page.
Verify the interval with a restore, not with logs
The only proof that a database backup frequency is correct is a restore that produces the state you expected. Pick a timestamp, restore to it, and compare a few rows against the source. Do this on a schedule, not once. A simple check is to restore the newest backup into a scratch instance and run a query you know the answer to:
pg_restore -d scratch /backup/base/latest.dump
psql -d scratch -c "SELECT count(*), max(created_at) FROM orders;"
count | max
-------+---------------------
48211 | 2024-06-11 14:36:02
If the max(created_at) is much older than the time the backup should have been taken, the schedule is not doing what you think. Compare it to the wall clock at backup time, not to now, because the gap between backup and restore is expected.
What to do next
Write the RPO and RTO down in one sentence each, then check the actual configuration on every database you run: archive_mode and archive_command on PostgreSQL, binary log expiry on MySQL, and the equivalent on whatever else is in the fleet. If the configured interval is longer than the RPO you wrote down, shorten it or accept the gap deliberately and tell the people who own the data. Then restore one backup into a scratch instance and confirm the numbers. That single exercise will tell you more about your backup frequency than any default schedule ever will.
