Learn pgBackRest - Scale: Large Databases & Delta Optimization

#Introduction

Everything we've learned works beautifully for small-to-medium databases. But when data crosses into hundreds of gigabytes to terabytes, the rules change: one huge file, many small files, limited bandwidth, and narrow backup windows. In episode 19 we bring pgBackRest to that scale — with measured parallelism, delta optimization, and benchmarks that turn intuition into numbers.

This episode's philosophy: optimization without measurement is an opinion. Every decision — how much process-max, whether a higher compression level is needed, when to use --delta — must be backed by data from your own benchmarks.

#Parallel Backup for Terabyte Data

#Why Large Data Is Different

On a terabyte database, backup time is determined by:

• Number of files: thousands of relations → parallelism helps a lot.
• The largest file size: a single 200 GB relation can only be processed by one process (one file = one process).
• I/O and network: saturate faster than CPU.

#Finding the process-max Sweet Spot

Test progressively on the same database in the same window:

sudo -u postgres pgbackrest --stanza=main --process-max=2 backup --type=diff
sudo -u postgres pgbackrest --stanza=main --process-max=4 backup --type=diff
sudo -u postgres pgbackrest --stanza=main --process-max=8 backup --type=diff

Note the duration of each run (shown in the command output). The typical curve: duration drops then flattens — at the flattening point the bottleneck shifts from CPU to I/O/network, and adding more process-max no longer helps.

#Aligning with Server Resources

A process-max that's too high disturbs the production workload. Two strategies:

• Off-peak scheduling: run backups with a large process-max when traffic is low (episode 10).
• Per-command: a lower --process-max during peak hours, higher during the backup window — without changing the config:

sudo -u postgres pgbackrest --stanza=main --process-max=16 backup --type=full

#Delta Optimization: Fast Restores

#Back to --delta

For large databases, a full restore (copying every file from the repository) can take hours. --delta (episode 8) changes the game: it compares local files against the backup and only overwrites what differs. For a cluster that's mostly intact, this can cut RTO dramatically.

sudo -u postgres pgbackrest --stanza=main --delta restore

#Maximizing the Delta Effect

For --delta to work optimally:

• Don't delete PGDATA first — files that are still good are kept and verified.
• Make sure checksums are on (data_checksums=on) so delta compares with confidence.
• For PITR to a far point, --delta is still used — it only speeds up file copying, not the recovery logic.

#Combining with Standby Backups

In an HA cluster (episode 18), delta restore becomes the trusted weapon for bootstrapping new nodes: restore to the existing PGDATA, then stream a catch-up from the primary. The time to grow a node drops from "hours" to "minutes."

#Benchmark: Duration vs Database Size

#Making a Valid Benchmark

A good benchmark has three traits: a single variable, real data, and repetition. Design a recording table:

CREATE TABLE bench_backup (
  dt timestamptz default now(),
  db_size_gb numeric,
  type text,
  process_max int,
  duration_sec numeric
);

Then record each backup with a query to pg_database_size:

psql -U postgres -tAc \
  "INSERT INTO bench_backup (db_size_gb, type, process_max, duration_sec)
   VALUES (round(pg_database_size('aplikasi')/1e9,2), 'full', 8, $DURATION);"

#Reading the Results

Plot duration against size for each type. The patterns you must understand:

• Full: linear against size — each terabyte adds an almost constant duration.
• Differential/Incremental: duration is determined by the changes, not the total size — that's why the full+diff schedule (episode 10) uses bandwidth far more efficiently.
• Restore: linear against size + WAL replay time; far-away PITR adds time.

Size (GB)  Full (min)  Diff (min)  Restore (min)
  250          18          4            21
  500          36          5            42
 1000          72          6            85

From this data, the RTO estimate in episode 10 can be calculated instead of guessed: if restoring 1 TB takes 85 minutes, your RTO is at least that number.

#A Proven Sequential Optimization

For large databases, the most effective optimization order:

1. Delta backup: reduce data written (episode 7) — the biggest effect.
2. Parallelism: speed up the process (episode 7 + this episode).
3. Compression: shrink the size in the repository (medium level, not extreme).
4. Smart scheduling: full+diff, off-peak (episode 10).
5. Delta restore: reduce RTO (episode 8 + this episode).

#Conclusion

Key takeaways:

• Parallel backup uses many processes; find the flattening point with gradual testing, not guesses.
• High process-max in the off-peak window; lower when production is busy.
• --delta restore cuts RTO for a mostly intact cluster.
• Benchmark: measure duration vs size for full, diff, and restore — keep the history.
• Sequential optimization: delta backup → parallelism → compression → scheduling → delta restore.

In the next episode we'll bring pgBackRest into the container and cloud world: Docker, K8s & cloud integration — the pgBackRest sidecar pattern with the CloudNativePG operator on Kubernetes, Docker images with a mounted config, and backups to S3/GCS/Azure Blob with IAM roles versus static keys. Backups are now part of the platform, no longer a remote server!