Replication and the Consistency Menu

Replication is how databases scale reads and survive failures — copies of the data on multiple machines, kept in sync. The trade-offs are all about what "in sync" means when the network is unreliable.

Async vs synchronous replication

Async — the primary commits locally and ships WAL (write-ahead log) to replicas after. Fast writes; a replica might be seconds behind. Failover loses the last few seconds of writes.

Sync — the primary waits for a replica to confirm before acknowledging the write. Zero data loss on failover; every write pays the network latency to the replica.

Semi-sync — wait for the replica to receive the WAL (not apply it). Middle ground.

Read-your-own-writes

The trap that bites everyone: you write a comment, it lands on the primary, you refresh, the replica hasn't caught up — your own comment is missing. Solutions:

• Read from the primary for N seconds after your write (per-session stickiness).
• Track the replica's replay position; read from the primary if lag exceeds a threshold.
• Route "reads of things you just wrote" to the primary — the write response carries a cursor.

CAP, translated

When the network partitions, you choose between Consistency (refuse reads on the minority side — the partition is unreachable, respond with errors) or Availability (serve reads from every side — the partitions will disagree until the network heals). You can't have both during a partition. Postgres's default is CP (a partition stops writes); Cassandra/DynamoDB's is AP (stale reads are acceptable).

Field notes

• pg_stat_replication lag on the primary is the metric — monitor it.
• Replication isn't a backup — a DELETE replicates too. Backups are separate.
• Sharding (splitting data by key across multiple primaries) is the last resort — it complicates joins, transactions, and rebalancing. Do it when the data is too big or the write rate too high for one primary, not before.