Back to roadmap
Module 6 · Relational Data at ScaleDay 05425 min

SQL Replication

Streaming and logical replication compared.

← PreviousNext →
Day 054

SQL Replication

Primary
service
Replica
datastore
Replica
datastore
Signal path
Primary + replicas
Primary
service
WAL
Replica
datastore
Primary
service
WAL
Replica
datastore
Memory hook

SQL Replication: streaming and logical replication compared

Mental model

shape data so reads and writes stay honest

Design lens

Synchronous replicas zero data loss but slow writes.

Recall anchors
StreamingLogicalFailover

Why it matters

Streaming replication ships the write-ahead log (WAL) byte-for-byte to replicas. Logical replication ships row-level changes for chosen tables, useful for selective sync, cross-version, or cross-vendor.

Deep dive

Streaming: same-version, identical schema, fast and bulky.

Logical: subset of tables, version-mismatched, can transform.

Failover risks: split-brain (two primaries) — prevent with fencing or consensus (e.g., Patroni).

Demo / scenario

Promote a replica during a primary outage.

  1. Detect primary unhealthy.
  2. Stop writes (fencing) to old primary.
  3. Promote replica with most recent LSN.
  4. Reconfigure clients to new primary.

Tradeoffs

  • Synchronous replicas zero data loss but slow writes.
  • Async replicas may lose tail of WAL.
  • Manual failover is reliable but slow; automation needs careful fencing.

Diagram

WALWAL
Primary
Replica
Replica
Primary + replicas.

Mind map

Check yourself

Loading quiz…

Sources & further reading

← PreviousNext →