Database Replication: How Production Systems Stay Fast and Available When One DB Fails

TL;DR

Database replication = copy data from one Primary server to one or more Replicas. Primary handles writes. Replicas absorb read traffic and stand by for failover. It's the cheapest way to (1) scale reads, (2) survive a node crash, (3) run safe maintenance, and (4) move backups off the hot path. The hard part isn't turning it on — it's living with replica lag, failover, and split-brain risk.

The flow in one paragraph

App writes go to the Primary. The Primary streams its change log (Postgres WAL, MySQL binlog) to each Replica. Replicas replay the log and serve read queries. If the Primary dies, an HA controller promotes a Replica. That's the entire mental model — the rest is tradeoffs.

Why replication matters

Without replication, a single database does everything: every read, every write, every backup, every analytics query. As traffic grows, that machine becomes the bottleneck and the single point of failure. One slow report can starve the checkout flow. One crash takes the whole product down.

• Scale reads — point browsing, search, dashboards at replicas; keep the primary for writes.
• Higher availability — a replica can be promoted in seconds when the primary dies.
• Disaster recovery — a cross-region replica is your recovery target.
• Safer maintenance — fail over, patch the old primary, fail back.
• Lighter primary — backups, ETL, analytics all run on replicas.

Primary vs Replica

The roles are not symmetric:

• Primary — accepts INSERT / UPDATE / DELETE. Source of truth. Streams changes downstream.
• Replica — read-only copy. Serves SELECT. Can be promoted when the primary fails.

Most production setups run 1 primary + N replicas. Multi-leader and leaderless (Dynamo-style) topologies exist, but they push conflict resolution onto your application — only worth it for global write workloads.

A concrete example

An e-commerce platform with one primary and three replicas:

• Order placement and payment → Primary (writes must be durable and ordered).
• Product catalog, search, recommendations → Replicas.
• Admin dashboards and BI reports → dedicated reporting replica so heavy queries never touch the primary.
• Nightly backup → taken from a replica, primary stays at full throughput.

The checkout path stays fast because 90% of traffic — browsing — never reaches the primary.

Synchronous vs Asynchronous

This is the central tradeoff: do we wait for replicas before telling the user the write succeeded?

In Postgres you can flip mode per transaction via synchronous_commit. MySQL exposes the same idea through semi-sync plugins and Group Replication. CockroachDB, YugabyteDB, and Spanner sidestep the choice by using Raft/Paxos to make synchronous replication the default without the manual failover wiring.

Challenges most tutorials skip

Replication helps a lot. It also adds problems you didn't have before:

• Replica lag — replicas fall behind under write bursts, network jitter, or long-running queries holding ACCESS EXCLUSIVE locks. Stale reads are a real bug class. Mitigation: batch large writes, monitor pg_stat_replication.replay_lag, route consistency-critical reads back to the primary.
• Failover complexity — Postgres has no native automatic failover. You bolt on Patroni + etcd, pg_auto_failover, or repmgr. MySQL gets Orchestrator or MHA. Each has its own quorum and fencing model.
• Split-brain — two nodes both believe they are primary, both accept writes, and now you have divergent histories that are painful to merge. Prevention requires consensus (etcd/Consul), witness nodes, and fencing tokens. Routing layers like ProxySQL alone are not enough — they have no quorum and no LSN view.
• The primary is still a single write target — replication scales reads, not writes. To scale writes you need sharding.
• Replication is not backup — a DROP TABLE replicates in milliseconds. Keep PITR snapshots.
• Monitoring is mandatory — track LSN gap, replay lag, WAL receive vs apply, and primary connection counts. Silent lag is worse than loud failure.

What's next on your roadmap

If you're running a single instance today, the typical scaling ladder looks like this:

1. Add one async read replica — instant read scale and a backup target.
2. Add automated failover — Patroni / pg_auto_failover / RDS Multi-AZ.
3. Move to semi-sync — bound the data-loss window without paying full sync latency.
4. Cross-region replica — disaster recovery and lower latency for distant users.
5. Shard or migrate to a Raft-native DB — when the primary's write capacity is the wall.

Replication improves systems. It does not remove architecture thinking — it just changes which problems you're allowed to have.

Sources: PostgreSQL HA docs, Tiger Data: Postgres replication best practices, GeeksforGeeks, Walmart Global Tech.