ProjekinAja. Software Engineering Stage 6 · Judgment Lesson 6.4
● Stage 6 · Lesson 6.4

Data at Scale: Replication, Backups, and Durability

A backup that has never been restore-tested is a prayer — sync vs async replication, failover, and the one unforgivable sin: losing user data.

⏱ ~20 min read 💾 Data engineering 🔥 Durability

🎯 By the end you'll understand

How read replicas work and why replication lag creates the read-your-own-writes bug. The sync vs async replication tradeoff in full — lag costs latency, async risks loss. What failover actually costs (it is not instantaneous). Why backups without restore drills are not backups. And exactly which acknowledged writes survive a primary failure under each replication mode — which the widget makes visceral.

KEY INSIGHTDurability is not presence — it is recoverability

"I have three weeks of experiment runs." Fine. Now: if this machine dies in the next ten minutes, which of those runs can you recover, and how long will that take?

Your database is running. The disk is spinning. The queries return. None of that tells you whether your data is durable. Durability is a statement about the future — specifically about recovery — and the only way to know your backup works is to have restored from it. Everything else is optimism.

🔑 Hold onto this

A database that is not replicated is a single point of failure. A replica that lags is a source of stale reads and a window of potential loss. A backup that has never been restored is a file whose contents you cannot trust. Replication handles availability; backups handle recoverability from corruption or deletion. You need both, and both require periodic proof that they work.

CORE CONCEPTRead replicas and the replication lag problem

The most common first step when a database is under read pressure is to add a read replica: a second server that receives a stream of changes from the primary and applies them, so that read queries can be offloaded. The primary handles writes; replicas handle reads. Simple, and it works — until you encounter replication lag.

Replication lag is the delay between when a write commits on the primary and when it is visible on a given replica. On a healthy, lightly-loaded setup it might be a few milliseconds. Under heavy write load, a slow network, or a replica that is catching up after maintenance, it can stretch to seconds or minutes. For most analytics queries this is fine. For certain patterns it is a silent correctness bug.

⚠ The read-your-own-writes bug, live

A researcher submits a job: POST /jobs hits the primary and commits row 9142. The browser immediately follows with GET /jobs/9142 — load-balanced to a replica that is 2 seconds behind. The replica returns 404. "The system lost my job." It didn't. The replica just hasn't seen it yet. This is read-your-own-writes inconsistency, a direct consequence of routing reads to a lagging replica. The standard mitigation is to route reads for a just-written resource back to the primary, or to use a token (the write's LSN) that the replica must have reached before serving the request.

Replication lag is easy to observe. PostgreSQL exposes it on the primary via pg_stat_replication:

psql — check lag on primary
SELECT
    application_name,
    state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- typical healthy output
-- application_name | state  | write_lag | flush_lag | replay_lag
-- replica-1        | streaming | 00:00:00.003 | 00:00:00.004 | 00:00:00.006

Three lag measures, each representing a different point in the replica's pipeline: writing to its WAL, flushing to durable storage, and replaying the change into its data files. replay_lag is what your queries see. If that number is climbing under load, your replicas are falling behind and stale reads are accumulating.

💡 Lag spikes on replica restart

When a replica restarts after a crash or maintenance window it must replay all WAL segments since it was last current. During catch-up it is maximally stale. Traffic routed to it during this window will see old data. Production systems typically wait for the replica's lag to fall below a threshold before returning it to the read pool.

CORE CONCEPTSync vs async replication: the lag-versus-loss tradeoff

Every replicated system must answer one question: does the primary wait for the replica to confirm a write before acknowledging the client? That single decision determines your exposure to data loss and your write latency, and there is no free lunch.

Mode Primary waits for replica? Write latency Data loss on primary failure Replica lag
Asynchronous No — commits immediately Local disk only (~1–5 ms) Up to the lag at failure time Can drift seconds to minutes
Synchronous Yes — blocks until replica confirms Local + network RTT (~5–30 ms) Zero (confirmed writes are durable) Always ≤ 0 (by definition)
Semi-sync (1-of-N) Yes — waits for any one replica One network RTT Zero (for writes past the sync replica) Sync replica: none; others: can drift

Synchronous replication gives you the durability guarantee but pays it in write latency: every commit now includes a network round-trip to the replica. In PostgreSQL this is configured per transaction via synchronous_commit; in MySQL Group Replication it is called "single-primary mode with after-sync acknowledgment." At inter-datacenter distances (10–50 ms RTTs), synchronous replication can cut write throughput dramatically. That is the honest cost.

Most production systems choose asynchronous replication and accept the replication window — the seconds or milliseconds of acknowledged writes that could be lost if the primary fails before they propagate. For an experiment tracker logging robot telemetry, losing three seconds of data might be acceptable. For a financial transaction log, it is not. The question is not "which mode is correct" but "what is the maximum acceptable data loss for this workload?" — measured in time (RPO: Recovery Point Objective).

📖 RPO and RTO

RPO (Recovery Point Objective): how much data loss is acceptable, measured in time. "We can lose at most 1 minute of writes." This drives your replication and backup frequency.
RTO (Recovery Time Objective): how long the service can be down during recovery. "We must be serving reads within 30 seconds of a primary failure." This drives your failover automation and replica readiness.

SEE ITReplication failover simulator

The widget below runs a simulated write stream to a primary with one replica. You choose the replication mode and the moment you kill the primary. The simulator then shows precisely which acknowledged writes survive — and runs a timed restore drill from a backup to reveal what the backup was actually missing.

⚙ Interactive · Replication failover & restore drill

Pick a replication mode, watch writes flow, kill the primary at any moment, then run the restore drill.

Primary
● Running
LSN: 0
Replica
○ Streaming
LSN: 0
Backup
📦 Nightly
Snapshot LSN: 0

Write durability — each block is one acknowledged write. ■ durable (on replica)   ■ lost (primary only, async window)   ■ not yet written

Waiting to start…
Writes acknowledged
0
Writes on replica
0
Writes lost on kill
0
Restore drill gap
—

Click "Start writes" to begin the write stream. Kill the primary at any moment.

The async experiment is the visceral one. Watch writes tick up — each one acknowledged to the "client." Kill the primary mid-stream. The red blocks are writes that were acked but never reached the replica; they are gone. Under synchronous mode that window shrinks to zero: the primary would not have sent the acknowledgment until the replica confirmed. The restore drill then surfaces the second gap: the backup snapshot was taken hours ago, so even after you restore from it and apply the replica's WAL, you discover what the backup alone would have been missing. This is why "we have a backup" and "we have tested our backup" are different sentences.

IN PRACTICEFailover honestly: what the cutover actually costs

When the primary fails, the work is not instantaneous even if your monitoring catches it in five seconds. A realistic failover sequence:

1

Detect the failure

Your health check polls the primary. With a default interval of 10 seconds and two consecutive failures required, detection alone takes 10–20 seconds. Aggressive checking (2 s intervals) reduces this but generates false positives. During detection, writes to the primary are queuing or timing out; clients see errors.

2

Elect and promote the replica

In PostgreSQL with Patroni or repmgr, or in MySQL Group Replication, promotion is automated — typically 5–15 seconds. The replica runs pg_promote() (or equivalent), transitions from recovery to read-write mode, and starts accepting writes. During this window the replica is catching up on any unflushed WAL from the primary. No new writes are accepted yet.

3

Redirect traffic

DNS TTL or a proxy (PgBouncer, HAProxy, AWS RDS Proxy) must point at the new primary. If your TTL is 300 seconds and you did not plan for this, every client holds a stale DNS cache pointing at the dead primary for five more minutes. In practice: use a proxy, not DNS, for failover routing, and keep TTLs low for database endpoints.

4

Handle in-flight transactions

Any transaction that was open on the old primary when it died is gone — you will not get a rollback or a commit. Clients must retry. Your application must be idempotent on writes, or your users will see duplicate side effects when they click "submit" a second time.

5

Rebuild the standby

You now have one node. The old primary — if it is eventually recoverable — must be rebuilt as a replica of the new primary before you are back to redundancy. Until then, a second failure means a restore-from-backup event. Most teams discover this gap when the second failure happens during the chaos following the first.

A well-orchestrated failover with automation typically takes 30–60 seconds of total downtime. Without automation, or across regions, it can be 5–20 minutes. That is the honest RTO for most setups. If your SLA promises "99.9% availability" (8.7 hours downtime/year) this is fine. If it promises "99.99%" (52 minutes/year), a single unplanned failover consumes most of your budget.

⚠ Split-brain: the failure mode nobody mentions until it happens

Suppose the primary doesn't crash — it just becomes unreachable to the replica due to a network partition. Your automation promotes the replica to primary. Now both nodes believe they are the primary and both accept writes. When the network recovers, you have two divergent write histories. This is split-brain, and resolving it requires deciding which writes to discard. The solution is a quorum mechanism (e.g., etcd in Patroni, or a third "witness" node that breaks ties) so that the old primary fences itself — refuses further writes — before the new primary is promoted. Every automated failover system must have a fence; those that don't eventually cause split-brain.

IN PRACTICEBackups and restore drills: the only proof

Replication is not a backup. This is worth saying plainly because teams conflate them. A replica replicates everything — including a DROP TABLE you ran by mistake, or corruption caused by a software bug. A backup is a point-in-time snapshot you can restore from before the corruption event. If you only have replication, accidental deletes propagate to all replicas instantly. If you only have backups, your RTO is hours, not minutes.

A complete durability strategy needs both, and it needs a third thing: proven restore procedures. The failure modes in backup systems are almost always discovered during restore:

The standard approach is a scheduled restore drill: every month (or quarter, depending on risk tolerance), spin up a fresh environment, restore from backup, run a validation suite, and time the operation. This gives you three things the backup alone does not: confidence the backup is readable, a measured RTO (the drill told you it takes 4 hours — now you know), and an up-to-date runbook.

restore_drill.sh — skeletonrun monthly in CI
#!/usr/bin/env bash
# 1. Spin up a temporary Postgres instance
docker run -d --name drill-pg -e POSTGRES_PASSWORD=drill \
    postgres:16

# 2. Restore base backup from object store
aws s3 cp s3://tracelab-backups/base.tar.gz /tmp/base.tar.gz
pg_restore -h 127.0.0.1 -p 5432 -U postgres \
    --no-owner -d tracelab /tmp/base.tar.gz

# 3. Apply WAL archive to target PITR timestamp
recovery_target_time="$(date -d 'yesterday 23:59' '+%Y-%m-%d %H:%M:%S')"
pg_waldump --check-wal-continuity /tmp/wal/

# 4. Run validation queries — schema, row counts, app startup
psql -h 127.0.0.1 -U postgres -d tracelab \
    -f tests/restore_validation.sql

# 5. Record timing, alert if > RTO threshold
echo "Restore drill completed in ${SECONDS}s"
if [[ "$SECONDS" -gt 14400 ]]; then
    alert "Restore drill exceeded 4-hour RTO"
fi

# 6. Tear down
docker rm -f drill-pg
💡 3-2-1 rule

The classic backup guideline: 3 copies of the data, on 2 different media types, with 1 copy offsite. For a cloud deployment this is typically: the live database, a replica in a second availability zone, and a backup in a different region's object store. The point is not the specific numbers — it is the discipline of asking "what single event could destroy all my copies simultaneously?" and eliminating that scenario.

For point-in-time recovery (PITR) specifically, you need both a base backup and a continuous WAL (Write-Ahead Log) archive. PostgreSQL's archive_command copies each completed WAL segment to an archive location; you can then restore the base backup and replay WAL segments up to any timestamp. The gotcha: if a single WAL segment is missing from the archive, recovery stops at that gap. Test the archive continuity, not just the backup.

TRACELABTraceLab at fleet scale: the durability audit

TraceLab v5 serves a 10,000-robot fleet. Run telemetry is arriving continuously — roughly 50 writes per second. The single-node Postgres instance from Stage 4 is approaching its write limit, and the first hard question of durability judgment has arrived: what can we afford to lose?

The answer shapes every configuration choice. For TraceLab the stakeholder view is:

This gives different RPO targets for different data: zero-loss for metadata (use synchronous replication for that table/schema), 5-second window for telemetry (async is fine), regenerable for aggregates (no replication needed, backup suffices). This tiering is the judgment call — it is how you avoid paying synchronous-replication latency on every 100 Hz write while still guaranteeing the result you actually care about.

postgresql.conf — tiered syncTraceLab v5 primary config
# Default: async for high-volume telemetry
synchronous_commit = off

# Per-session override for critical metadata writes
# application sets: SET synchronous_commit = on;
# before writing to runs.metadata

# Standby target: replica-1 must confirm before flush
# (used when synchronous_commit = on in session)
synchronous_standby_names = 'FIRST 1 (replica-1)'

# WAL archive for PITR
archive_mode = on
archive_command = 'aws s3 cp %p s3://tracelab-wal/%f'

# Backup schedule: pg_basebackup nightly at 02:00 UTC
# Retention: 7 daily, 4 weekly, 3 monthly

The TraceLab v5 milestone also includes the restore drill added to CI. It runs weekly (faster feedback than monthly for a system this active), spins a Docker Postgres instance, restores last night's base backup plus the WAL archive, runs tests/restore_validation.sql (checks row counts, schema version, and that the last 100 runs are queryable), and pages the on-call if it takes more than 30 minutes. The first time we ran the drill we discovered the WAL archive had been silently failing for 11 days due to an expired IAM role. The backup existed. The PITR capability did not. The drill found it; a real incident would have been catastrophic.

WEAKNESSWhere this bites

✗ Treating replication lag as a constant

Teams measure replication lag once, see 20 ms, and call it solved. Lag is not a constant — it is a function of write load, network conditions, replica I/O, and how recently the replica restarted. Under a write spike (end-of-day batch, a burst of 10,000 robots logging simultaneously) lag can jump from 20 ms to 10 seconds in minutes. Read-your-own-writes bugs that "never happen in dev" surface under production load spikes. Monitor lag continuously; alert when it exceeds your RPO threshold, not when it exceeds some arbitrary 1-second ceiling you set last year.

✗ Counting on failover without testing it

Automated failover systems (Patroni, AWS RDS Multi-AZ, Google Cloud SQL HA) work — when they have been tested. They have also failed in documented ways: etcd quorum loss preventing fencing, DNS TTL outliving the cutover, a replica that was behind by 45 minutes being promoted because the "better" replica's health check was misconfigured. The only way to know your failover works is to run it deliberately in production (chaos engineering) or in a staging environment that faithfully mirrors your production topology. "We have Patroni" is not a disaster plan; "we ran a failover drill last Tuesday and it took 34 seconds" is.

✗ Single-region backups

An S3 bucket in us-east-1 holding backups for a database in us-east-1 is not offsite storage — it is the same regional failure domain. AWS has had S3 outages in single regions that lasted hours. If your primary database fails because of a regional event, your backup might be unavailable at precisely the moment you need it. Store at least one backup copy in a different region, and test restoring from it. The cost is trivial (cross-region S3 replication is cents per GB for most workloads); the failure of not doing it is not.

KEY INSIGHTHow it connects

  • Reliability Patterns (6.3) — retries, circuit breakers, and load shedding are what your application does while the primary is unreachable; they buy you time for failover to complete without cascading failures.
  • Technical Debt (6.5) — TraceLab's CSV-based storage from v0 was eventually replaced by Postgres; this lesson is what happens when that Postgres instance gets large enough to matter. Debt in the data layer compounds because the blast radius of a data loss is your entire history.
  • Data Modeling (4.5) — schema choices upstream affect which tables need synchronous replication and which can afford async; the separation of telemetry from metadata in TraceLab v5 required the schema design from that lesson.
  • Observability (3.6) — replication lag, WAL archive status, and backup completion are exactly the kind of operational metrics that belong in your dashboards; the monitoring patterns from Stage 3 apply directly here.

The durability work in this lesson is where Stage 6's theme — judgment — is most literal. There is no correct answer to "how much data loss is acceptable." There is only the question honestly asked and answered, and then the configuration that enforces the answer. Every team that has lost data wished they had asked it earlier.

GLOSSARYKey terms

Replication lag
The delay between a write committing on the primary and becoming visible on a replica. Measured in time; can range from milliseconds to minutes under load.
RPO (Recovery Point Objective)
The maximum amount of data loss, measured in time, that is acceptable. Drives backup frequency and replication mode choice.
RTO (Recovery Time Objective)
The maximum acceptable time for service to be restored after a failure. Drives failover automation and standby readiness.
Synchronous replication
A replication mode where the primary blocks the write acknowledgment until at least one replica confirms durability. Zero data loss on primary failure; higher write latency.
WAL (Write-Ahead Log)
The sequential log of all database changes. In PostgreSQL, replicas stream and replay the WAL; PITR restores replay it to a target timestamp.
PITR (Point-in-Time Recovery)
Restoring a database to any specific timestamp by combining a base backup with the WAL archive from that point. Requires a continuous, gap-free WAL archive.
Split-brain
A failure mode where two nodes simultaneously believe they are the primary and both accept writes, producing divergent histories. Resolved with fencing and quorum mechanisms.
Restore drill
A scheduled test of the complete backup restore procedure, producing a measured RTO and confirming backup integrity and continuity.

ACTIVE RECALLCheck your understanding

Answer in your own words first, then reveal.

1Your application writes a record and immediately reads it back. The read returns "not found." The write definitely committed. What is the most likely cause?›

The read was routed to a replica that hasn't yet received the write — this is the read-your-own-writes inconsistency caused by replication lag. The write committed on the primary; the replica is behind. The fix is to route the read back to the primary for resources just written, or use a mechanism (like waiting for the replica's LSN to advance past the write's LSN) before serving the read from a replica.

2You have async replication and the primary dies. The replica was 8 seconds behind. What happens to the 8 seconds of acknowledged writes?›

They are lost. The primary acknowledged those writes to clients before the replica received them. With the primary gone, there is no way to recover them. The replica becomes the new primary with its own state — 8 seconds behind. This is the cost of async replication: an accepted window of potential data loss equal to the replication lag at the moment of failure. Synchronous replication would have prevented this by refusing to ack the writes until the replica confirmed them.

3What is split-brain, and what prevents it in a production failover system?›

Split-brain occurs when a network partition makes the primary unreachable to the replica, the replica is promoted to primary, and then the network recovers — leaving two nodes both accepting writes. Prevention requires fencing: the old primary must be prevented from accepting writes before the new primary is promoted. This is done via a quorum mechanism (e.g., a third node in etcd that must grant a lease before promotion is allowed) so the old primary loses its lease and rejects writes before the new primary is activated.

4You have nightly backups and a streaming replica. What critical capability requires both — and what specific failure in the backup would silently break it?›

Point-in-time recovery (PITR) requires a base backup plus a continuous WAL archive. The critical failure is a gap in the WAL archive: if even one WAL segment is missing, replay stops at the gap and you cannot recover past it. The backup file itself may be intact and valid — pg_restore succeeds — but you discover the gap only when you try to apply the WAL to reach your target timestamp. This is why a restore drill that only tests "can we restore the base backup?" is insufficient; the drill must also verify WAL continuity and test PITR to a recent timestamp.

Next: when the system outlives its authors — technical debt and legacy code →