Skip to content

Replication

Replication is the foundation of almost everything you expect from a serious database: high availability, read scaling, migration without downtime, geographic distribution and recovery in another region. It is also the mechanism that degrades most quietly — a replica stopped for weeks keeps answering queries, with stale data.

This page covers the mechanisms. The full per-engine topologies live in MySQL, PostgreSQL and ClickHouse.

Replication topologies: asynchronous, semi-synchronous quorum, cascading and logical

Choosing a topology is choosing an RPO and a cost per transaction — not a preference.

Physical or logical

Physical replication Logical replication
What travels Blocks / records from the transaction log Changed rows, at operation level
Fidelity A byte-for-byte copy of the primary Selective: chosen tables and columns
Versions Requires the same major version Tolerates different versions
Target Read-only Can take writes of its own
Cost Lower Higher, per applied row
Typical use High availability, failover standby Migration, version upgrades, extraction for BI

A production cluster usually uses both: physical for the failover replicas, logical to migrate, upgrade a version or feed an external consumer.

Commit modes

The mode defines the RPO — how much data it is acceptable to lose.

Mode COMMIT acknowledgement RPO Cost
Asynchronous Immediate; the primary doesn't wait for the replica Seconds of data at risk Lowest write latency
Semi-synchronous / quorum After at least one replica confirms receipt Near zero A few milliseconds per transaction
Strictly synchronous After the replica applies it Zero Highest; the replica enters the critical path
Across regions Same, with the replica in the other region Near zero, survives the loss of a region The metropolitan link latency

The most common arrangement: one semi-synchronous replica to guarantee the data and one asynchronous replica for reads and reporting, so reporting never sits in the write critical path.

A replica is not a backup

Replication faithfully copies mistakes too. A DROP TABLE on the primary reaches the replica in milliseconds. Against human error and logical corruption, what protects you is backup with point-in-time recovery.

Topologies

  • Primary with replicas — the standard. One write source, several read sources.
  • Cascading — a replica feeds other replicas. Relieves the primary when there are many consumers, at the cost of adding lag at each hop.
  • Across regions — a replica in br-sp-1 and br-sp-2, interconnected by Lan2Lan links or encrypted tunnels. See Multi-Region MySQL and Multi-Region PostgreSQL.
  • Distributed multi-write — no single primary, with per-range consensus. That is the CockroachDB model, and it solves local writes in several regions — a problem classic replication does not solve.

Read routing

Having a replica does not scale reads on its own: the application has to use it. Routing belongs in the proxy layer, not scattered through the code:

  • ProxySQL (MySQL) and PgBouncer with separate endpoints (PostgreSQL) expose -rw and -ro.
  • Reporting and export queries always go to a replica.
  • Reads that need data just written (read-your-writes) stay on the primary, or use a mechanism that waits for a replication position.

Lag: what to monitor

Lag is the health metric of replication, and it needs more than one angle:

Metric What it reveals
Delay in seconds The business view: how old the replica's data is
Distance in bytes / log position Whether the replica is progressively falling behind
State of the replication processes A replica stopped on an error — the worst case, because the delay in seconds can even look stable
Retained log space on the primary The primary keeps log for lagging replicas; a stuck replica can fill the primary's disk

Frequent causes of lag: an oversized batch write on the primary, a replica with weaker hardware, single-threaded change application, a long transaction open on the replica, or saturated I/O.

A lag alert needs two thresholds

One for the delay the business accepts (say, 30 seconds) and one for lag growing over several minutes — because a replica drifting away slowly eventually reaches the point of needing a rebuild.

Rebuilding a replica

When a replica falls behind past the retained log, or shows divergence, it is rebuilt — not "repaired":

  1. remove the replica from read routing;
  2. recreate it from a physical backup or a recent snapshot;
  3. resume replication from the recorded position (GTID/LSN);
  4. wait for lag to reach zero;
  5. verify consistency with a sample checksum;
  6. return it to read routing.

On Kubernetes this cycle is driven by the operator; on virtual machines, by a script versioned in the runbook.

Failover in one sentence

A replica only counts as protection if promotion is reliable: detection by more than one observer, fencing of the old primary, promotion of the most advanced replica and redirection through the proxy. The details are in MySQL, PostgreSQL and in the disaster recovery plan.