Database Replication

The Machinery

 replication = shipping changes from primary to standbys.

 three generations of technique:

 1. STATEMENT-BASED:   ship the SQL ("UPDATE ...")
    ✗ nondeterminism: NOW(), random, triggers → replicas diverge
 
 2. ROW-BASED:         ship the row CHANGES (before/after images)
    ✓ deterministic; bigger logs for wide statements

 3. WAL/LOGICAL SHIPPING: ship the write-ahead log itself
    ✓ exact byte-level changes; the modern default (Postgres
      streaming, MySQL binlog, etc.)

 [primary] ──WAL stream──► [replica applies log = identical state]

Sync Modes Revisited for Databases

 ASYNC (default):
   primary commits without waiting → fast writes
   failure window: unshipped transactions LOST on failover
   lag normally ms; spikes under load/failover

 SYNC:
   primary waits ≥1 replica confirm per commit
   zero loss on failover of primary
   cost: commit latency includes replica round trip;
         replica down can BLOCK writes entirely

 SEMI-SYNC: wait for one ack, with timeout fallback to async.
            the pragmatic middle most managed services offer.

 choose by data criticality per DATABASE (not per query):
 payments ledger → sync/semi-sync. analytics feed → async.

Replication Lag Anatomy

 what makes replicas fall behind:

 - write BURSTS exceeding apply rate (bulk imports!)
 - long transactions: replicas can't apply until commit
   (one 10-minute migration freezes ALL replica progress)
 - single-threaded apply on replica (older MySQL) vs
   parallel apply (modern: by-commit-order or by-schema)
 - network saturation between regions

 monitoring: seconds_behind / replay_lag per replica,
 ALERTING before users see staleness — lag is a leading indicator.

Failover Choreography

 primary dies:

 1. DETECT     health checks miss × N (seconds)
 2. ELECT      pick most-caught-up replica
 3. PROMOTE    replica becomes writable; may discard trailing WAL
               (async gap = lost transactions HERE)
 4. REDIRECT   DNS/virtual-IP/proxy → clients reconnect
 5. REBUILD    old primary returns as REPLICA or replaced fresh
               (never auto-rejoin as primary! split brain)

 SPLIT BRAIN prevention: fencing — old primary must be
 provably isolated (STONITH) before new one accepts writes.
 managed services automate this; know WHAT they automate.

Chain vs Star Topologies

 STAR:          primary ──► r1, r2, r3     (all direct)
                simplest; primary ships N copies

 CHAIN/CASCADE: primary ──► r1 ──► r2 ──► r3
                primary ships once; r1 relays
                + less primary load   − deeper worst-case lag

 regional designs chain through a regional hub commonly.

Interview Framing

“Explain your replication setup” scored depth: mechanism named (WAL streaming), sync mode chosen WITH tradeoff sentence, lag monitoring + long-transaction gotcha mentioned, failover steps including split-brain fencing. The detail that separates practitioners: knowing failover’s async gap means recently-committed transactions can vanish — and having chosen that risk deliberately.