System Design

Database Replication

Keeping synchronized copies of data across multiple nodes to increase availability, fault tolerance, and read capacity — with clear trade-offs between durability, write latency, and read consistency.

Intent

Replication keeps synchronized copies of data across multiple nodes so that, if one node fails, data isn't lost and the system keeps operating — and, as an added benefit, so read load can be distributed across the copies.

A single-node database is a single point of failure: if the server goes down, the whole system becomes unavailable. If the disk fails without a recent backup, data is lost. Replication solves both problems by keeping copies of the data on other servers — the replicas — which can take over the service if the main node fails, and which can serve reads while the primary processes writes.

Problem

Production systems have two fundamental requirements that a single database node can't guarantee at the same time:

  • High availability: the database must stay accessible even during hardware failures, maintenance, or upgrades. With a single node, any failure means downtime.
  • Data durability: data confirmed to the client must not be lost in case of failure. A single disk, no matter how reliable, has a non-zero probability of failing.
  • Insufficient read capacity: in read-heavy workloads (typical of web applications), a single node may not have enough throughput to serve all reads at the expected latency.

How it works

Primary/Replica (Master/Slave)

The most common replication model: one node is designated primary (or master) and receives all writes. Changes are propagated to the replicas via a replication log (WAL in PostgreSQL, binlog in MySQL). Replicas serve reads. 

  Application
  │                  │
  │ Writes           │ Reads
  ▼                  ▼
  ┌─────────┐      ┌──────────┐  ┌──────────┐
  │ Primary │─────►│ Replica 1│  │ Replica 2│
  └─────────┘      └──────────┘  └──────────┘
       │ replication log
       └──────────────────────────────────────►
                                          Application [Reads]

  Every write: Primary
  Every read: Replica 1 or Replica 2 (or Primary if needed)

Separating reads and writes onto different nodes is one of the simplest ways to increase read throughput without sharding. A primary with two replicas triples the system's read capacity.

Synchronous vs asynchronous replication

The difference between the two modes defines the trade-off between durability and latency: 

  SYNCHRONOUS REPLICATION

  1. Client sends a write to the Primary
  2. Primary replicates to at least one Replica
  3. Replica confirms receipt
  4. Primary confirms the write to the client

  ┌────────┐ ──write──► ┌─────────┐ ──replicate──► ┌─────────┐
  │ Client │            │ Primary │                 │ Replica │
  └────────┘ ◄──ack──── └─────────┘ ◄──────ack───── └─────────┘

  RPO = 0 (zero loss of confirmed data)
  Cost: write latency includes the round trip to the replica


  ASYNCHRONOUS REPLICATION

  1. Client sends a write to the Primary
  2. Primary confirms the write to the client immediately
  3. Primary replicates to the Replicas in the background

  ┌────────┐ ──write──► ┌─────────┐
  │ Client │            │ Primary │ ──replicate (async)──► ┌─────────┐
  └────────┘ ◄──ack──── └─────────┘                        │ Replica │
                                                            └─────────┘

  RPO > 0 (data replicated with delay can be lost if the Primary crashes)
  Benefit: minimal write latency (doesn't wait for the replica)

Most systems default to asynchronous replication (MySQL and PostgreSQL by default) for latency reasons. Synchronous replication — or semi-synchronous, which waits for confirmation from at least one replica — is used where durability is critical, such as in financial systems.

Replication lag

In asynchronous replication, there's always a delay between a write being confirmed on the primary and the data becoming available on the replicas. That delay is the replication lag.

Under normal conditions, the lag is milliseconds. But under high load, during maintenance operations, or with replicas in distant availability zones, the lag can grow to seconds or minutes — and the application doesn't know unless it explicitly monitors it.

Failover

When the primary goes down, a replica needs to be promoted to primary. Failover can be:

  • Manual: an operator decides which replica to promote and updates the configuration. Safer, but slower — minutes of downtime.
  • Automatic: an orchestration process (e.g., Patroni for PostgreSQL, MHA for MySQL, or RDS/Cloud SQL's native mechanism) detects the failure and promotes the most up-to-date replica. Seconds of downtime, but risk of split-brain if the failure detection is a false positive.

Multi-primary (Multi-master)

Multiple nodes accept writes simultaneously. Increases write availability — if one primary goes down, the others keep operating — but introduces the problem of write conflicts: two nodes receive different writes for the same record at the same time.

Conflict resolution is inherently complex: timestamp-based (the most recent write wins, with the risk of losing the other one), application-defined (the application defines the merge logic), or last-write-wins (simple, but potentially destructive). Multi-primary should be avoided unless there's a real need — such as geographic replication where each region needs to accept writes locally.

When to use

  • High availability and failover: any production system that can't tolerate prolonged downtime during hardware failures should use replication. It's the minimum requirement for a meaningful availability SLA.
  • Read-heavy workloads: when the ratio of reads to writes is high (typical in web applications), distributing reads across replicas increases throughput without the complexity of sharding.
  • Online backups without impacting the primary: running heavy backups directly on the replica prevents the backup process from consuming primary resources and affecting production latency.
  • Reporting and analytics: long analytical queries can be directed to a dedicated replica, isolating the OLAP workload from the OLTP workload on the primary.

When to avoid

  • Multi-primary, unless there's a specific need: resolving simultaneous write conflicts is expensive to implement correctly. Systems that seem to need multi-primary can often be redesigned with sharding or with Primary/Replica and accept eventual consistency.
  • As a substitute for sharding: replication increases read capacity, but not write capacity — all writes still go through the primary. If the bottleneck is writes, replication doesn't solve it.

Pros and cons

Pros

  • High availability: failure of the primary doesn't mean data loss or prolonged downtime — a replica takes over.
  • Read scalability: read throughput grows linearly with the number of replicas.
  • Durability: data exists on multiple nodes and geographies, drastically reducing the risk of permanent loss.
  • Workload isolation: dedicated replicas for analytics or backups protect the primary from the heaviest queries.
  • Geographic latency: replicas in regions close to users reduce read latency.

Cons

  • Eventual consistency: reads from replicas can return stale data if replication lag is high.
  • The write bottleneck persists: all writes go through the primary — replication doesn't solve write overload.
  • Operational complexity: monitoring replication lag, managing failover, and maintaining multiple nodes add operational overhead.
  • Split-brain risk in automatic failover: an incorrect failure detection can lead two nodes to consider themselves primary at the same time.
  • Infrastructure cost: each replica is an additional server with a cost proportional to the primary.

Common pitfalls

1. Read-your-writes: reading from a replica right after writing

A user updates their profile. The write goes to the primary and is confirmed. The user is redirected to the profile page — which reads from a replica. If replication lag is 200 ms, the page shows the old profile. The user thinks the update failed, tries again, and now the write is duplicated.

Solutions: sticky reads (direct reads to the primary for a short period after the same user's write), read from the primary for sensitive operations (logged-in user's profile, balances), or propagate the write's timestamp and only read from replicas that have already reached that point (a technique natively supported by some databases).

2. Silent replica lag

Replication lag has grown to minutes, but the application keeps serving reads from the replicas without knowing it. Dashboards show hours-old data, uniqueness validations pass on records that were already created on the primary, and inventory reports show quantities that were already sold.

Monitoring replication lag is mandatory. Configure alerts for lag above a threshold acceptable to the business (e.g., 5 seconds). In situations of high lag, consider temporarily directing reads to the primary even if that increases load.

3. Split-brain in automatic failover

The primary suffers a network failure that makes it unreachable by the orchestrator but not by the database. The orchestrator detects the failure and promotes a replica. Now two nodes consider themselves primary: the original (which keeps receiving writes from clients that still have a connection to it) and the new one (which also accepts writes). The two versions of the data diverge, and manual reconciliation can be destructive.

The solution is fencing: before promoting the replica, the orchestrator ensures the old primary can no longer accept writes — whether by revoking its credentials, blocking its network, or using STONITH (Shoot The Other Node In The Head). Never rely solely on failure detection to avoid split-brain.

4. Heavy queries on replicas blocking replication

In some databases (notably old MySQL versions with MyISAM, or specific PostgreSQL configurations), long analytical queries on a replica can block the application of replication events coming from the primary. Lag increases while the heavy query runs. When the query finishes, the replica needs to process the accumulated backlog.

Solution: use dedicated analytics replicas with specific configurations (e.g., transaction_timeout, or isolation via logical replication in PostgreSQL) that prevent long queries from blocking the replication process.

Related architectures and patterns

Sharding and replication are complementary techniques: replication increases availability and read capacity within each shard, while sharding distributes writes across multiple primaries. Large-scale systems frequently combine both — each shard has its own set of primary and replicas.

The distinction with horizontal scalability is important: replication is horizontal scale for reads, not for writes. To scale writes horizontally, sharding is necessary. The two concepts are frequently confused because both involve multiple database nodes.

Caching and read replicas are often used together: the cache absorbs the most frequent reads (high hit rate), and the replicas serve the reads that pass through the cache (misses). The combination maximizes read throughput without overloading any single component — and reduces the number of replicas needed, lowering infrastructure cost.