Restore speed is where database theory meets the ugly truth of storage latency, CPU contention, and “wait, which backup is the real one?” panic. Your RTO isn’t a spreadsheet number. It’s the time between the first pager and the moment your app stops behaving like a haunted vending machine.
If you need RTO under 15 minutes, you don’t “optimize restores.” You design the entire backup+recovery path as a production system: predictable, measurable, rehearsed, and brutally boring.
What “RTO under 15 minutes” actually means
RTO is the time to restore service, not the time to “finish restore.” If your service needs the database and your database needs connection pooling, DNS, secrets, and schema compatibility, all of that is in-scope. A 9-minute database restore is a 30-minute outage if you still have:
- A 12-minute WAL/binlog catch-up because you forgot you’re doing PITR.
- A 6-minute cache warm-up because your app “discovers” performance again from cold.
- A 10-minute human delay because restore steps live in someone’s head.
For RTO under 15 minutes, assume you don’t have time to think. You must be able to execute a pre-baked decision tree, with pre-provisioned capacity, and you must know (not hope) that the backup is valid.
The uncomfortable math
15 minutes is 900 seconds. If your physical data directory is 800 GB, you’re not restoring it from object storage at 200 MB/s. Not unless physics is also on your on-call rotation.
So hitting 15 minutes usually means one of these architectures:
- Hot standby / replica promotion (restore = promote + redirect).
- Snapshot rollback (ZFS/LVM/EBS snapshot restore and attach).
- Physical backup restore to pre-warmed nodes (data transfer is local or already staged).
- Small enough dataset that a true restore fits in the window (rare in grown-up systems).
Logical dumps for large systems are for migrations and archaeology. They are not your 15-minute RTO plan.
Restore paths: MariaDB vs PostgreSQL (what’s fast, what’s not)
The fastest restore is usually “don’t restore”
For both MariaDB and PostgreSQL, the fastest recovery is promotion of a replica that’s already caught up. If you can meet your RPO with async replication, fine. If you can’t, pay for synchronous replication or accept a larger blast radius.
Physical vs logical: the fork in the road
Logical restore (mysqldump/mariadb-dump, pg_dump) is slow because it replays SQL, rebuilds indexes, and forces the database to do tons of CPU work that was already done once. It also tends to be single-thread bottlenecked in the wrong places.
Physical restore (MariaDB: Mariabackup; PostgreSQL: pgBackRest, pg_basebackup, file-level restore) is fast because you mostly copy files, then do recovery replay.
MariaDB restore speed profile
- Fast path: Mariabackup physical backup, prepared ahead, copy-back on fast storage, then InnoDB crash recovery / redo apply on start.
- Where it hurts: prepare step (if deferred), copy-back I/O, and InnoDB recovery if logs are huge or fsync behavior is conservative.
- PITR: apply binary logs (binlog) after restoring base backup. That can be fast or painfully serial depending on write volume and single-threaded apply limitations in your setup.
PostgreSQL restore speed profile
- Fast path: restore physical backup (pgBackRest restore or basebackup), start instance, replay WAL to consistency or to a target (PITR), promote.
- Where it hurts: WAL replay speed (I/O and CPU), restore command throughput (fetching WAL segments), and checkpoint/redo configuration.
- PITR: can be very fast if WAL archive is local and parallelized; can be miserable if WAL is remote and serialized or if recovery target is far back.
Opinionated guidance: if you need RTO under 15 minutes, design for promotion or snapshot rollback. If compliance demands “restore from backup,” then at least use physical backups and stage them on the target storage before you need them.
Facts and history that matter in restore speed
- PostgreSQL’s WAL has been central since the 1990s; crash safety and PITR were baked into the architecture early, and recovery is fundamentally “replay logs until consistent.”
- InnoDB became the default MySQL storage engine in 5.5, and MariaDB inherited that lineage. Restore speed is largely “how fast can InnoDB make itself consistent again.”
- pg_dump is deliberately logical and portable; it’s excellent for migrations and version jumps, and terrible for tight RTO at scale.
- MariaDB’s Mariabackup is a fork of Percona XtraBackup, built for hot physical backups without stopping writes. The “prepare” phase exists because it needs to make the backup consistent.
- PostgreSQL’s replication slots prevent WAL removal while a consumer needs it; great for durability, terrible if you forget about an abandoned slot and your disk fills during a crisis.
- MySQL/MariaDB replication historically struggled with parallel apply in certain workloads; modern improvements help, but binlog apply speed is still a common RTO spoiler.
- Copy-on-write filesystems (ZFS, btrfs) changed the game for fast rollback restores—when used correctly. When used incorrectly, they also changed the game by inventing new ways to saturate IOPS.
- PostgreSQL 9.6 improved WAL replay and vacuum behavior in ways that indirectly help recovery times under write-heavy load (less bloat, less churn, better background behavior post-restore).
- MariaDB and PostgreSQL both depend on fsync semantics; your restore speed can be dominated by storage durability guarantees more than database code.
The restore bottlenecks you can’t wish away
1) Moving bytes: bandwidth and IOPS
Physical restore is a file copy problem plus recovery replay. If your data is on HDD or network storage with unpredictable latency, your RTO is basically a weather forecast.
For under 15 minutes, you want:
- SSD/NVMe for the database volume.
- Local staging of the most recent base backup.
- Predictable sequential throughput and enough random IOPS for WAL/redo apply.
2) Recovery replay: WAL vs redo log behavior
PostgreSQL recovery replays WAL. MariaDB/InnoDB replays redo and applies changes to data pages. Both are sensitive to:
- How many dirty pages must be reconciled.
- Checkpoint frequency and size of log buffers.
- fsync rate and write amplification.
3) CPU and compression choices
Compression is a classic trap: it helps network and storage, then quietly murders recovery CPU. If you compress backups hard, you must be sure restore hosts have the CPU headroom and you can parallelize decompression.
Joke #1: Compression is like a gym membership—buying it feels productive, using it during an emergency feels personal.
4) “Restore” is not “usable”
Even after the database starts, you may still be dead in the water:
- PostgreSQL: long crash recovery or replay; then autovacuum storms; then cache cold-start.
- MariaDB: InnoDB buffer pool is cold; adaptive hash index warms; replication rebuilds; queries time out due to plan instability.
5) Metadata: privileges, extensions, and drift
Logical restores fail loudly when extensions or roles are missing. Physical restores fail quietly when your config diverges, your instance comes up with different settings, and your application sees behavior changes under load.
One paraphrased idea from Werner Vogels (Amazon CTO): Design systems assuming things fail; reliability comes from preparation and automation, not hope.
Fast diagnosis playbook
This is the on-call sequence when restore time is blowing up and you need to find the bottleneck in minutes, not in a postmortem.
First: are you restoring bytes or replaying logs?
- If you are still copying files: you have a bandwidth/IO path problem.
- If the DB is “starting” but not “ready”: you have a recovery replay or checkpointing problem.
- If it’s “ready” but the app is down: you have a connectivity, credentials, or warm-up problem.
Second: check storage latency, not throughput
Restores often do bursts of small writes. Latency kills you first. You can have a huge MB/s number and still lose minutes on fsync.
Third: verify whether PITR is the time sink
PITR is non-negotiable for some orgs. Fine. But it must be engineered: WAL/binlog archived locally, parallel fetch, and a tested “how far back are we going” decision.
Fourth: confirm you’re not self-sabotaging with configuration
During restore/recovery, some settings should be temporarily more aggressive, then reverted. If you never planned that, you’re stuck with defaults that optimize for safety over speed (which is fair, but doesn’t meet your RTO).
Fifth: validate the backup integrity early
If your backup is corrupted, every minute you spend “tuning” is a minute you’re not failing over to a replica or snapshot. Validate quickly, switch strategy quickly.
Practical tasks: commands, outputs, decisions
These tasks are meant to be run during planning and during an incident. Each includes: command, what output means, and what decision you make next. Use them as a runbook spine.
Task 1: Measure raw disk throughput (sequential) on the restore target
cr0x@server:~$ sudo fio --name=seqread --filename=/var/lib/postgresql/fio.test --size=4G --bs=1M --iodepth=16 --rw=read --direct=1 --numjobs=1
seqread: (groupid=0, jobs=1): err= 0: pid=22341: Thu Dec 31 10:11:12 2025
read: IOPS=1650, BW=1650MiB/s (1730MB/s)(4096MiB/2481msec)Meaning: High BW indicates sequential copy won’t be your bottleneck. If you see <300 MiB/s on SSD/NVMe expectations, you’re on the wrong class of storage or sharing it badly.
Decision: If BW is low, stop planning “restore from scratch.” Plan replica promotion or snapshot-based restore.
Task 2: Measure fsync-heavy latency (random write) on the restore target
cr0x@server:~$ sudo fio --name=randwrite --filename=/var/lib/mysql/fio.test --size=2G --bs=4k --iodepth=64 --rw=randwrite --direct=1 --numjobs=4 --time_based --runtime=30
randwrite: (groupid=0, jobs=4): err= 0: pid=22410: Thu Dec 31 10:12:02 2025
write: IOPS=42000, BW=164MiB/s (172MB/s), lat (usec): avg=610, max=22000Meaning: Avg latency in the hundreds of microseconds is decent. Milliseconds under load will stretch crash recovery and WAL replay.
Decision: If latency is bad, reduce concurrent recovery pressure (throttle restore jobs), move WAL/binlog to faster volume, or fail over to a node with better storage.
Task 3: Check PostgreSQL recovery state and replay progress
cr0x@server:~$ sudo -u postgres psql -c "select pg_is_in_recovery(), now() as ts, pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"
pg_is_in_recovery | ts | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn
-------------------+-------------------------------+-------------------------+------------------------
t | 2025-12-31 10:13:11.552781+00 | 4A/9B2C1F20 | 4A/9B29A4D8
(1 row)Meaning: Receive LSN ahead of replay LSN means replay is behind (CPU/I/O bound). If receive LSN is NULL, you’re not receiving WAL (archive/stream issue).
Decision: If replay lags far behind, check disk latency and recovery settings; if not receiving WAL, fix archive retrieval or replication connectivity.
Task 4: Inspect PostgreSQL logs for recovery bottlenecks
cr0x@server:~$ sudo tail -n 30 /var/log/postgresql/postgresql-16-main.log
2025-12-31 10:12:58.123 UTC [21011] LOG: starting PostgreSQL 16.2 on x86_64-pc-linux-gnu
2025-12-31 10:12:58.456 UTC [21011] LOG: entering standby mode
2025-12-31 10:13:05.990 UTC [21011] LOG: redo starts at 4A/9A000028
2025-12-31 10:13:11.770 UTC [21011] LOG: consistent recovery state reached at 4A/9B0000A0
2025-12-31 10:13:11.771 UTC [21011] LOG: restored log file "000000010000004A0000009B" from archiveMeaning: “restored log file … from archive” repeating slowly suggests archive fetch is the bottleneck (network, object store latency). If redo starts and then stalls with checkpoints, storage is hurting.
Decision: If archive fetch is slow, stage WAL locally or enable parallel WAL retrieval via your backup tooling.
Task 5: Check pgBackRest restore and WAL archive health
cr0x@server:~$ sudo -u postgres pgbackrest info
stanza: prod
status: ok
cipher: none
db (current)
wal archive min/max (16): 000000010000004A00000090/000000010000004A000000A1
full backup: 2025-12-31 03:00:02+00
size: 612.3GB, repo size: 201.7GB
backup reference list: Meaning: Recent full backup exists, WAL archive range is present. If status is not ok or WAL min/max is stale, your PITR might be impossible.
Decision: If WAL archive isn’t healthy, stop promising PITR. Promote a replica or restore to the last valid point.
Task 6: Identify PostgreSQL checkpoint pressure (post-restore performance cliff)
cr0x@server:~$ sudo -u postgres psql -c "select checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_backend, stats_reset from pg_stat_bgwriter;"
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_backend | stats_reset
------------------+-----------------+--------------------+-----------------+-------------------------------
112 | 87 | 98122310 | 442190 | 2025-12-31 09:40:00+00
(1 row)Meaning: High checkpoints_req suggests forced checkpoints (often due to max_wal_size too small or write bursts). That can slow recovery and early traffic.
Decision: Increase max_wal_size and tune checkpoint_timeout/checkpoint_completion_target for smoother I/O—especially on recovery nodes.
Task 7: Check MariaDB InnoDB recovery behavior and crash recovery time clues
cr0x@server:~$ sudo tail -n 40 /var/log/mysql/error.log
2025-12-31 10:11:22 0 [Note] InnoDB: Starting crash recovery.
2025-12-31 10:11:22 0 [Note] InnoDB: Reading tablespace information from the .ibd files...
2025-12-31 10:12:10 0 [Note] InnoDB: 5.6.0 started; log sequence number 987654321
2025-12-31 10:12:10 0 [Note] InnoDB: Completed initialization of buffer pool
2025-12-31 10:12:54 0 [Note] InnoDB: Crash recovery finished.Meaning: If crash recovery takes minutes, that’s part of your RTO. Longer times often correlate with large redo logs, dirty page volume, or slow fsync.
Decision: If crash recovery is consistently long, tune checkpointing/flush behavior and ensure redo logs and data live on low-latency storage.
Task 8: Validate MariaDB physical backup readiness (prepared vs not)
cr0x@server:~$ sudo mariabackup --prepare --target-dir=/backups/mariadb/full-2025-12-31
[00] 2025-12-31 10:14:03 completed OK!Meaning: “completed OK” means the backup is now consistent and ready for fast copy-back. If you delay prepare until incident time, you just spent your RTO on paperwork.
Decision: Always keep the latest backup already prepared (or prepared immediately after creation) if you’re chasing sub-15-minute restores.
Task 9: Restore MariaDB from prepared backup (copy-back) and verify ownership
cr0x@server:~$ sudo systemctl stop mariadb
cr0x@server:~$ sudo rm -rf /var/lib/mysql/*
cr0x@server:~$ sudo mariabackup --copy-back --target-dir=/backups/mariadb/full-2025-12-31
[00] 2025-12-31 10:15:44 completed OK!
cr0x@server:~$ sudo chown -R mysql:mysql /var/lib/mysql
Meaning: Copy-back success is necessary but not sufficient. Wrong ownership will prevent startup or cause odd permission failures.
Decision: If ownership/SELinux/AppArmor is wrong, fix it now. Don’t “see if it starts.” It won’t, and you’ll lose time.
Task 10: Confirm MariaDB is accepting connections and not stuck in recovery
cr0x@server:~$ sudo systemctl start mariadb
cr0x@server:~$ mysql -uroot -e "select @@version, @@innodb_flush_log_at_trx_commit, @@sync_binlog\G"
@@version: 10.11.6-MariaDB
@@innodb_flush_log_at_trx_commit: 1
@@sync_binlog: 1Meaning: Server is up and configs are visible. If connection hangs, check error log for crash recovery or fsync stalls.
Decision: For emergency restore nodes, you may temporarily relax durability (see later) if your org accepts it—then revert.
Task 11: Estimate PostgreSQL restore time by measuring decompression and copy
cr0x@server:~$ time sudo -u postgres pgbackrest --stanza=prod restore --type=immediate --target-action=promote
real 6m42.118s
user 2m10.044s
sys 1m02.991sMeaning: “real” is the wall clock. If user time is huge, decompression/encryption is CPU bound. If sys time is huge, kernel and I/O are the story.
Decision: CPU-bound: add cores, change compression, parallelize. I/O-bound: faster disks, local repo, fewer network hops.
Task 12: Check whether PostgreSQL is waiting on WAL archive retrieval
cr0x@server:~$ sudo -u postgres psql -c "select now(), wait_event_type, wait_event, state, query from pg_stat_activity where pid = pg_backend_pid();"
now | wait_event_type | wait_event | state | query
------------------------------+-----------------+------------+--------+-------------------------------------
2025-12-31 10:17:12.118+00 | Activity | WALRead | active | select now(), wait_event_type, ...
(1 row)Meaning: WALRead wait during recovery often indicates I/O or archive fetch slowness.
Decision: If WAL is remote, bring archive closer or switch to streaming from a live source; if local, investigate disk latency.
Task 13: Spot a forgotten PostgreSQL replication slot that’s bloating WAL (pre-incident hygiene)
cr0x@server:~$ sudo -u postgres psql -c "select slot_name, active, restart_lsn from pg_replication_slots;"
slot_name | active | restart_lsn
---------------+--------+-------------
analytics_etl | f | 4A/01000000
(1 row)Meaning: Inactive slot holds WAL back. Over time, that inflates WAL volume, which inflates PITR time and can fill disks at the worst moment.
Decision: If the consumer is dead, drop the slot after confirming it’s safe. Then fix your consumer management.
Task 14: Check MariaDB binlog availability for PITR and the clock you’re racing
cr0x@server:~$ mysql -uroot -e "show binary logs;"
Log_name File_size
binlog.000221 104857600
binlog.000222 104857600
binlog.000223 51239871Meaning: Binlogs exist and sizes suggest active rotation. If logs are missing because retention is too short, PITR is a fantasy.
Decision: Set binlog retention based on the maximum PITR window you claim. Then monitor it like it’s production (because it is).
Task 15: Validate restore success by checking core invariants (PostgreSQL)
cr0x@server:~$ sudo -u postgres psql -c "select count(*) from pg_class; select now();"
count
-------
4182
(1 row)
now
-------------------------------
2025-12-31 10:18:55.11002+00
(1 row)Meaning: You’re not proving correctness, but you’re proving the catalog is readable and the server responds. Add application-level checks next.
Decision: If any query errors with corruption messages, stop. Switch to replica promotion or a different backup immediately.
Task 16: Validate restore success by checking core invariants (MariaDB)
cr0x@server:~$ mysql -uroot -e "show engine innodb status\G" | head -n 25
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-12-31 10:19:21 0x7f2c4c1fe700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 srv_active, 0 srv_shutdown, 245 srv_idleMeaning: InnoDB is up. You can also see if it’s still doing heavy background work. If it’s thrashing, expect slow app behavior even though “restore is done.”
Decision: Consider temporarily reducing app traffic or enabling read-only mode until the system stabilizes, depending on your service model.
Three mini-stories from corporate life
Mini-story 1: The outage caused by a wrong assumption
The company was mid-migration: a monolith on MariaDB, a new service on PostgreSQL. The SRE team had a clean RTO goal and a messy reality. Everyone agreed “we have backups,” which is the corporate equivalent of saying “we have umbrellas” while standing in a hurricane.
The assumption was simple: logical dumps are “good enough” because the dataset “isn’t that big.” Nobody had actually timed a restore end-to-end. The on-call runbook said: fetch the latest dump from object storage, restore, apply migrations, point the app.
Then a storage incident nuked a primary volume. The team started the dump restore. It chugged along, CPU pinned, disk lit up, and the clock kept moving. Index creation dominated. Then the app migrations ran and added more locks and more time. After an hour, they had a database. After another chunk of time, they had something the app could use. The RTO wasn’t missed by minutes; it was missed by an entire meeting.
The postmortem wasn’t about a heroic tweak. It was about admitting that dumps are not a restore strategy at scale. They implemented physical backups, built a warm standby, and started timing restores monthly. The fix was mostly unglamorous. That’s why it worked.
Mini-story 2: The optimization that backfired
A different org was proud of their storage efficiency. They compressed everything aggressively: base backups, WAL archives, the lot. Costs went down. Dashboards looked tidy. Finance was briefly delighted.
During a real incident, they restored PostgreSQL from a compressed backup to a node with fewer cores than production. On paper it was “fine,” because that node only existed for emergencies. In reality, decompression became the choke point. The restore took long enough that the team started “optimizing” live: changing compression settings mid-stream, restarting processes, and generally turning a predictable procedure into interpretive dance.
They got the database back, but their RTO target was gone. The biggest sting: the storage savings were real, but tiny compared to outage cost and reputation damage. They rebalanced: moderate compression, more parallelism, and emergency restore nodes sized for restore workloads, not steady-state workloads.
Joke #2: Nothing says “we’re prepared” like discovering your disaster-recovery box is basically a potato with a network cable.
Mini-story 3: The boring but correct practice that saved the day
A payments-adjacent company ran both MariaDB and PostgreSQL for different services. Their most valuable habit wasn’t a tool. It was a ritual: every week, one engineer performed a restore rehearsal into an isolated environment, using the same commands the incident runbook required.
They tracked three times: time to provision compute and storage, time to restore base backup, and time to reach “app healthy.” They also tracked the failure reasons—permissions, missing WAL segments, schema drift, broken secrets—and fixed them with the kind of calm cruelty only a recurring drill can inspire.
When an incident hit (a corrupted volume plus a bad kernel update, because reality enjoys combos), the restore wasn’t perfect. But it was practiced. They executed the playbook, saw WAL archive lag immediately, switched to replica promotion, and were back inside the window.
No one wrote a heroic Slack message afterward. That’s how you know it worked.
Common mistakes: symptoms → root cause → fix
This is the section you read when the restore is “almost done” for the fifth time.
1) Symptom: restore starts fast, then crawls at 5–10% CPU
- Root cause: I/O latency and fsync stalls, often due to shared storage or burst credits exhausted.
- Fix: Move data and WAL/redo to low-latency SSD/NVMe; reduce noisy neighbors; verify IOPS budget; consider snapshot rollback or replica promotion.
2) Symptom: PostgreSQL stuck “restoring log file … from archive” slowly
- Root cause: WAL archive retrieval bottleneck (remote object store latency, serial fetching, or throttled network).
- Fix: Use a local repo/cache for WAL, enable parallel WAL retrieval in your backup tooling, or stream from a live upstream if available.
3) Symptom: MariaDB startup takes forever after copy-back
- Root cause: InnoDB crash recovery is applying redo slowly; often log settings and storage latency combine to punish you.
- Fix: Keep backups prepared; ensure redo log and data are on fast storage; review innodb_flush_log_at_trx_commit and sync_binlog policy for the recovery node if acceptable.
4) Symptom: “backup restored” but application errors explode
- Root cause: Restore got the DB up, but not the environment: wrong users, missing extensions, config drift, or stale DNS/connection strings.
- Fix: Automate post-restore smoke tests; manage roles/extensions via configuration management; enforce immutable configs for restore targets.
5) Symptom: PITR fails halfway through with missing WAL/binlog
- Root cause: Retention too short, archiving not monitored, or a gap created during a prior incident.
- Fix: Monitor archive continuity; alert on missing segments; set retention to match your promised recovery window; practice restores to prove it.
6) Symptom: restores are fast in tests, slow in real incidents
- Root cause: Tests run on empty hosts and quiet storage; incidents happen during contention and degraded modes.
- Fix: Test restores under load and with realistic I/O contention; reserve restore capacity; keep “recovery nodes” sized and warm.
Checklists / step-by-step plan
Step-by-step: getting to sub-15-minute RTO (the practical path)
- Pick your recovery strategy by dataset size. If your data directory is large enough that copying it takes longer than 10 minutes, stop pretending. Use replica promotion or snapshots.
- Standardize on physical backups for both engines. MariaDB: Mariabackup. PostgreSQL: pgBackRest (or equivalent physical tooling). Dumps become secondary.
- Stage the most recent base backup locally. Your restore node should not be fetching hundreds of gigabytes across a best-effort network during an incident.
- Keep backups “restore-ready.” For MariaDB, that means prepared backups. For PostgreSQL, it means verified manifests/checksums and an archive that actually contains what you need.
- Engineer PITR as a first-class system. WAL/binlog archiving must be monitored, retained, and tested. Treat gaps as Sev-2, not “we’ll fix later.”
- Provision recovery nodes for restore workload. Restore is bursty: high I/O, high CPU (decompression), lots of metadata operations. Size for that, not average QPS.
- Separate data and log volumes when it helps. PostgreSQL: keep WAL on fast storage. MariaDB: log files and data benefit from low latency; separation can reduce contention.
- Write a one-page runbook with a decision tree. “If WAL archive is slow, promote replica.” Make it executable at 3 a.m.
- Automate service cutover. DNS/VIP updates, connection strings, pooler reloads. Your RTO dies in the glue steps.
- Rehearse monthly with a timer. Track the three times: base restore, recovery to consistency, app healthy. Fix the biggest time sink first.
Configuration tactics that usually help (and when to be careful)
These are not universal “make it fast” knobs. They are levers. Pull them with intention, and ideally only on recovery nodes or during recovery windows.
- PostgreSQL: Ensure WAL archive retrieval is fast (local cache); tune checkpoint settings for your workload; avoid tiny max_wal_size on write-heavy systems; size shared_buffers sensibly (too big can slow restart due to more dirty buffers behavior patterns).
- MariaDB: Keep backups prepared; avoid pathological flush settings that create huge crash recovery; ensure buffer pool warm-up strategy if you rely on it; consider delaying some durability only if your risk posture allows.
FAQ
1) Which restores faster: MariaDB or PostgreSQL?
Neither “wins” universally. PostgreSQL physical restore plus WAL replay is extremely predictable when WAL archive is healthy and local. MariaDB physical restore can be very fast, but InnoDB recovery behavior and binlog apply can surprise you. Your storage and your PITR strategy decide the winner more than the engine brand does.
2) Can I hit 15-minute RTO with mysqldump or pg_dump?
Only for small datasets and simple schemas. Once indexes and constraints are significant, logical restore time balloons. For strict RTO, use physical backups, snapshots, or replica promotion.
3) What’s the single biggest factor in restore time?
For physical restores: storage latency (fsync-heavy random I/O) and log replay throughput (WAL/redo). For PITR: archive retrieval speed and continuity.
4) How do snapshots compare to physical backups?
Snapshots (EBS/ZFS/LVM) can be the fastest “restore” if they’re crash-consistent and your database tolerates it (it usually does with recovery). They’re also easy to misuse: snapshotting the wrong volume set, or forgetting that you need consistent snapshots across data and WAL volumes.
5) Should WAL/binlog be on separate disks?
Often yes, when it reduces contention and gives logs lower latency. But separate disks don’t fix slow disks. One fast NVMe can beat two mediocre network volumes every day of the week.
6) Does compression help restore speed?
It helps transfer time and storage footprint, but can hurt restore time if CPU-bound or single-threaded. Moderate compression with parallelism is usually the sweet spot for RTO-focused systems.
7) How do I prove my RTO is real?
Time a full rehearsal: provision → restore → recovery/PITR → app health checks → cutover. Do it under realistic load conditions and with the same people who will be on call.
8) What should I monitor to protect restore speed?
WAL/binlog archive continuity, backup success and validation, replication lag, storage latency, disk free space (including WAL growth), and “restore rehearsal time” as a first-class SLO.
9) If I can promote a replica, do I still need backups?
Yes. Replicas protect against some failures (node death). Backups protect against other failures (operator error, data corruption, bad deployments, ransomware). RTO comes from replicas; recovery from bad data comes from backups.
Next steps you can do this week
- Time a restore for both MariaDB and PostgreSQL in a staging environment, end-to-end, with a stopwatch. Record where time goes: copy, prepare, WAL/binlog replay, app readiness.
- Run the storage latency tests (fio random write) on your actual recovery nodes. If latency is bad, your RTO target is fiction until storage changes.
- Validate PITR inputs: check WAL archive range (PostgreSQL) or binlog retention (MariaDB). Set alerts for gaps.
- Switch your runbook to a decision tree: “restore from backup” vs “promote replica” vs “snapshot rollback.” Make the tradeoffs explicit.
- Schedule a monthly restore rehearsal and treat failures as production incidents. If you can’t restore on a Tuesday, you won’t restore on a Sunday.
RTO under 15 minutes isn’t a tuning exercise. It’s an operations culture choice: physical backups, staged artifacts, fast storage, and rehearsals that make the boring path the default. Do that, and restores become a procedure. Skip it, and restores become a story.