Learn how databases keep copies in sync across nodes, manage replication lag, and handle the trade-offs between consistency, availability, and performance.
Simulate writes to primary and observe how replicas fall behind under lag. Watch what happens when you read from a lagging replica.
| Concern | Async Replication | Sync Replication |
|---|---|---|
| Write latency | Low (fire and forget) | Higher (+RTT to replica) |
| Durability | Data loss on primary crash | Zero data loss (at least 1 replica has it) |
| Read staleness | Possible (lag can be seconds) | None (replica is always current) |
| Availability | High (primary can write even if replicas down) | Lower (primary blocks if sync replica is down) |
| Best for | Social feeds, analytics, low-criticality reads | Financial data, inventory, anything requiring strong consistency |
| Pattern | Guarantee | Trade-off | Use When |
|---|---|---|---|
| Async replication | Eventual consistency | Risk of reading stale data | Read replicas for analytics, dashboards |
| Semi-sync replication | At-least-one replica has write | Slightly higher write latency | MySQL semi-sync: no data loss on failover |
| Sync replication | Strong consistency | Availability reduced if replica fails | Financial txns, config stores |
| Multi-primary | Eventual (with conflict resolution) | Conflict complexity | Multi-region active-active |
# postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024 # MB
synchronous_commit = on # change to 'remote_write' for semi-sync
# recovery.conf on replica (PostgreSQL 12+: postgresql.conf)
primary_conninfo = 'host=primary port=5432 user=replicator'
recovery_target_timeline = 'latest'
# Python: route writes to primary, reads to replica
import psycopg2
from contextlib import contextmanager
import time
PRIMARY_DSN = "postgresql://user:pass@primary:5432/db"
REPLICA_DSN = "postgresql://user:pass@replica:5432/db"
# Track per-user write timestamps for read-your-writes
user_write_times: dict[str, float] = {}
CONSISTENCY_WINDOW = 2.0 # seconds
def get_read_conn(user_id: str):
"""Route reads: primary if user wrote recently, else replica."""
last_write = user_write_times.get(user_id, 0)
if time.time() - last_write < CONSISTENCY_WINDOW:
return psycopg2.connect(PRIMARY_DSN) # read-your-writes
return psycopg2.connect(REPLICA_DSN)
def write(user_id: str, query: str, params=None):
"""All writes go to primary; record timestamp for consistency."""
with psycopg2.connect(PRIMARY_DSN) as conn:
conn.cursor().execute(query, params)
conn.commit()
user_write_times[user_id] = time.time()
def read(user_id: str, query: str, params=None):
"""Reads use replica unless user wrote recently."""
conn = get_read_conn(user_id)
with conn:
cur = conn.cursor()
cur.execute(query, params)
return cur.fetchall()