Master-replica and multi-master replication

Relational database scaling fundamentals

Single-node relational database management systems (RDBMS) like PostgreSQL and MySQL are constrained by the physical limits of a single server: disk I/O operations per second (IOPS), memory buffer pool capacity, CPU core count, and connection limits.

To scale beyond a single machine, we decouple reads and writes through data replication.

---

1. Master-replica (leader-follower) replication

In a master-replica topology:

• Primary (leader): Serves reads and accepts all writes (INSERT, UPDATE, DELETE). Each change is first recorded in a log: PostgreSQL's write-ahead log (WAL) is flushed to disk before the data files are changed; MySQL replicas copy the binary log (binlog).
• Read replicas (followers): Copy the primary's log and replay it, then serve read-only queries (SELECT). If the primary goes offline, the system can keep serving reads until a replica is promoted.

Adding replicas adds read capacity, but not linearly: every replica must replay every write, so a write-heavy primary can bog the replicas down, and more replicas mean more to replicate and more replication lag. On some systems the primary applies writes with many threads while a replica replays them with one.

Replication synchronization modes

---

2. Replication lag anomalies & mitigations

Under asynchronous replication, network jitter or heavy batch updates cause replication lag ($t_{\text{lag}}$). This creates classic distributed consistency anomalies:

A. Read-your-writes

• Anomaly: A user updates their profile avatar, reloads the page, but reads from a replica that has not received the update. The user sees their old avatar.
• Solutions:
  • Route that user's reads to the primary for a short time (e.g. 5 seconds) after any write they make.
  • Remember the position of the user's last write in the replication log and only read from a replica that has applied it. In MySQL, record the write's global transaction identifier (GTID) and call WAIT_FOR_EXECUTED_GTID_SET on the replica first.
  • In PostgreSQL, synchronous_commit = remote_apply makes each commit wait until the synchronous standbys have replayed it, so reads there see it.

B. Monotonic reads

• Anomaly: A user reads from Replica A (little lag) and sees a new comment, then refreshes and hits Replica B (more lag), and the comment disappears: time appears to go backwards.
• Solution: Send each user's reads to the same replica (for example, chosen by hashing user_id), so successive reads never come from a replica that is further behind.

---

3. Multi-master (active-active) replication and split-brain

In multi-master topologies, more than one node accepts writes, and the masters coordinate with each other on writes.

• Write conflicts: If two masters accept conflicting updates to the same row at the same time, the conflict must be resolved later. A data store can only apply simple policies such as last-write-wins; the application, which knows what the data means, can merge versions instead (Amazon's Dynamo, for example, keeps conflicting versions, tracked with vector clocks, and lets the application reconcile them). Conflicts become more common as you add write nodes and as latency between them grows.
• Trade-off: Most master-master systems are either loosely consistent (violating ACID) or have higher write latency because the masters synchronize.
• Split-brain: If a network partition separates the masters (or a failover goes wrong), two nodes can each believe they are the leader and accept writes that conflict.
• Fencing tokens: One defense: the lock or leader-election service hands out a number that increases every time leadership changes. The storage layer remembers the highest number it has seen and rejects writes carrying an older one, so a deposed leader cannot overwrite newer data.