Replication

Keeping copies of the same data on several nodes: leader-follower, multi-leader, leaderless, and the lag your product has to tolerate.

Syntaxleader --(WAL / binlog stream)--> follower; synchronous = commit waits for N follower acks

Replication keeps copies of the same data on more than one node. It buys three things: read throughput, survival of a node loss, and lower latency for readers near a copy. It does not buy write throughput or storage headroom — every replica still takes every write and still stores every row. That is what sharding is for.

Three topologies

Leader-follower (single-leader). One node accepts writes and ships a log of them — PostgreSQL's WAL over streaming replication, MySQL's binary log — which the followers apply. No write conflicts are possible, so this is the default and almost always the right interview answer.
Multi-leader. More than one node accepts writes, as in two regions that both serve local traffic, or an offline client that syncs later. Writes get cheaper and conflicts become yours to resolve.
Leaderless. The client (or a coordinator) writes to several replicas and reads from several, as in Amazon's Dynamo. Availability is the point; you pick consistency with the quorum sizes.

Synchronous or asynchronous

Asynchronous means the write returns as soon as the leader has it. Synchronous means the leader waits for acknowledgements first, which turns a replica being slow into your writes being slow. Both databases make this a per-commit setting rather than a topology: PostgreSQL has synchronous_commit plus synchronous_standby_names, MySQL has the semi-synchronous replication plugin.

What trips people up

Replication lag is a product requirement, not an operational detail. A user who posts a comment and is then served by a lagging follower sees it vanish. Decide per read path whether stale is acceptable, and route the ones that are not.

Example

// Read-your-writes across a PostgreSQL leader and its async replicas.
// After a write, remember how far the leader got; serve that session from a
// replica only once the replica has replayed past that point.
import pg from 'pg';

const leader = new pg.Pool({ host: 'db-leader' });
const replicas = [
  new pg.Pool({ host: 'db-replica-1' }),
  new pg.Pool({ host: 'db-replica-2' }),
];

async function setBio(session, userId, bio) {
  const c = await leader.connect();
  try {
    await c.query('UPDATE profiles SET bio = $1 WHERE user_id = $2', [bio, userId]);
    // pg_current_wal_lsn() is the leader's current write position. It was
    // named pg_current_xlog_location() before PostgreSQL 10.
    const { rows } = await c.query('SELECT pg_current_wal_lsn() AS lsn');
    session.minLsn = rows[0].lsn; // the watermark this session must read at or past
  } finally {
    c.release();
  }
}

const SELECT_BIO = 'SELECT bio FROM profiles WHERE user_id = $1';

async function getBio(session, userId) {
  // No recent write in this session: any replica is fine.
  if (!session.minLsn) return (await replicas[0].query(SELECT_BIO, [userId])).rows[0];

  for (const r of replicas) {
    // pg_last_wal_replay_lsn() is how far this replica has replayed.
    // Cast to pg_lsn: compared as text, '1/A0' and '1/100' sort wrongly.
    const { rows } = await r.query(
      'SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn AS caught_up',
      [session.minLsn],
    );
    if (rows[0].caught_up) return (await r.query(SELECT_BIO, [userId])).rows[0];
  }
  // Nobody has caught up yet: pay the leader rather than show stale data.
  return (await leader.query(SELECT_BIO, [userId])).rows[0];
}

When to use it

A reporting dashboard is saturating the primary and its queries can tolerate data a few seconds old.
Deciding whether a promoted replica is allowed to lose the last few acknowledged transactions.
A user updates their profile and the next page load is served by a lagging replica showing the old value.
Serving European readers from a replica in Frankfurt while all writes still go to one leader.

More examples

Synchronous replication, and the shorthand for it

Synchronous replication is a durability setting per commit, not a property of the cluster: synchronous_commit is a normal parameter, so SET LOCAL synchronous_commit = 'remote_apply' can make one payment write wait while a page-view counter in the same service does not. remote_apply is also the setting that makes replica reads read-your-writes safe without the LSN plumbing in the main example, at the cost of waiting for WAL replay.

# postgresql.conf on the leader.
# Nothing is synchronous until a standby is named here. The default is empty,
# which means every commit returns without waiting for any replica.
synchronous_standby_names = 'ANY 1 (r1, r2, r3)'

# The three forms, and what each one means:
#   'r1, r2'            shorthand -- identical to FIRST 1 (r1, r2)
#   'FIRST 1 (r1, r2)'  priority: wait for r1 if it is up, otherwise r2
#   'ANY 1 (r1,r2,r3)'  quorum: whichever one replies first
# The bare list is the pre-9.6 syntax and is still supported. The explicit
# FIRST num_sync form arrived in PostgreSQL 9.6, which was also the first
# release to support more than one synchronous standby; quorum commit with
# ANY arrived in PostgreSQL 10.

# How far a commit waits before returning:
#   off          do not even wait for the local disk flush
#   local        local flush only, standbys not waited for
#   remote_write standby received it and handed it to its OS
#   on (default) standby flushed it to durable storage
#   remote_apply standby replayed it, so a read there already sees it
#                (added in 9.6)
synchronous_commit = on

MySQL semi-synchronous, and the variables that were renamed

Semi-synchronous replication means the source waits for a replica to confirm it has received and logged the event, not that it has applied it. That is enough to stop a failover losing acknowledged writes, and not enough to stop a replica serving a stale read, so it solves durability without solving consistency. Reach for it when losing the last few transactions on promotion is unacceptable.

-- MySQL's equivalent is a plugin rather than a setting. On the source:
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_wait_for_replica_count = 1; -- acks to wait for

-- And on each replica:
-- INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
-- SET GLOBAL rpl_semi_sync_replica_enabled = 1;

-- These names arrived in MySQL 8.0.26 with new versions of the plugins. The
-- older plugins (rpl_semi_sync_master / rpl_semi_sync_slave) expose
-- rpl_semi_sync_master_enabled and
-- rpl_semi_sync_master_wait_for_slave_count instead, and are deprecated.
-- Install one pair or the other: the new plugin does not expose the old
-- variable names, so tooling written against them silently reads nothing.

Multi-leader, and the conflict it creates

What you sign up for when two regions both accept writes, or when a mobile client queues edits offline and syncs later. The write path gets simpler and conflict resolution becomes application logic. Design the data so a merge rule exists before reaching for last-write-wins, because the losing write in LWW is gone and nothing in the system reports that it happened.

// Two leaders accepted a write to the same row. Something has to decide.

// Last-write-wins. Simple, and it throws one of the two edits away with no
// record that it existed.
function lastWriteWins(local, incoming) {
  // Comparing wall clocks across machines is the classic mistake here: clock
  // skew picks the winner. Prefer a counter the writer owns, and keep the
  // node id only as a deterministic tie-break.
  if (incoming.version > local.version) return incoming;
  if (incoming.version < local.version) return local;
  return incoming.nodeId > local.nodeId ? incoming : local;
}

// A shape that does not need a winner: record adds and removes separately and
// merge by union, so both edits survive. Tag lists, read receipts and
// shopping carts are usually expressible this way. An account balance is not.
function mergeTags(a, b) {
  const added = new Set([...a.added, ...b.added]);
  const removed = new Set([...a.removed, ...b.removed]);
  return { added, removed, value: [...added].filter((t) => !removed.has(t)) };
}

Discussion

Be the first to comment on this lesson.