Single-Leader Replication

Why this matters: this is what almost every relational deployment runs. Its failure modes — a write bottleneck, a lost leader, and lagging reads — are the ones you will actually debug.

Key takeaway

In primary-secondary replication, one node is the primary (leader). It processes all write operations and propagates updates to secondary nodes (followers). This model is ideal for read-heavy workloads: scale by adding followers and distributing reads among them.

The model

All updates go to the primary; reads can be served by the primary or any secondary.

Two consequences follow immediately:

The primary becomes a bottleneck for writes — adding followers scales reads and does nothing for write throughput.
If the primary fails, asynchronous replication may cause inconsistency, because recent updates might not have reached the secondaries.

When the primary fails

A secondary is appointed as the new primary, which speeds recovery of the original. There are two approaches:

Approach	How it works	Trade-off
Manual	An operator decides which node becomes primary and notifies all secondaries	Slow — a human is in the recovery path — but deliberate and safe
Automatic	Secondaries detect the primary has failed and elect a new one through leader election	Fast, but risks acting on a false positive

The three replication methods

How do the changes actually get to the followers? Three approaches, each with a specific failure mode.

Method	What ships	Used by	Key weakness
Statement-based (SBR)	The SQL statements themselves	Older MySQL versions	Nondeterministic functions diverge
Write-ahead log (WAL) shipping	Low-level byte changes	PostgreSQL, Oracle	Tightly coupled to engine internals
Logical (row-based)	Row-level data changes	PostgreSQL, MySQL	More verbose than WAL

Statement-based replication

The primary executes SQL statements — INSERT, UPDATE — and writes them to a log file. Secondaries read this log and re-execute the statements.

Best use case: workloads with many reads and few writes, where read requests can be distributed across followers to reduce load on the leader.

Drawback: nondeterministic functions such as NOW() generate different values on different nodes, causing data divergence.

Write-ahead log (WAL) shipping

Transactions are written to a log file on disk before execution. That log records low-level byte changes rather than SQL statements.

WAL ensures consistency for nondeterministic functions — the bytes are already decided — and aids crash recovery. Its weakness is that it is tightly coupled to the database engine's internal structure, which makes version upgrades across a cluster complicated: you generally cannot have a leader and follower running different engine versions, so rolling upgrades become difficult.

Logical (row-based) replication

Captures changes at the row level — which column values changed in which row.

Because it captures the logical data change rather than physical disk bytes or SQL statements, it is flexible and compatible across different engine versions. That makes it the method of choice when you need rolling upgrades or want to replicate into a different system entirely (a data warehouse, a search index, a change-data-capture stream).

The problems asynchronous replication creates

Asynchronous replication creates replication lag between primary and secondaries, with two consequences:

Problem	What happens
Data loss	If the primary fails before propagating writes, those updates are lost
Inconsistent reads	A user writes data and immediately reads from a lagging follower, making it appear the data was lost

The solution: read-your-own-writes

If a user is accessing data they can modify — such as their own profile — route those reads to the leader to ensure strong consistency. For read-only or less consistency-sensitive data, follower replicas serve the reads.

This requires a mechanism to determine whether the data may have been recently modified, without issuing an extra consistency check. Profile data in a social network is usually writable only by the account owner, which gives a simple rule:

Always read the user's own profile from the leader; read any other user's profile from a follower.

Cons: this increases load on the primary node — the very node that is already the write bottleneck.