📡Replication and read replicas
Ship the log, replay the story
Streaming replication applies the primary’s write-ahead log on standbys, PostgreSQL WAL, MySQL binlog, SQL Server log shipping, so replicas can serve read traffic and backups. Unless you wait on synchronous quorum, followers are eventually consistent: great for dashboards, risky for read-your-writes UX right after a commit.
🕸️Topologies
Fan-out, DR, and who applies what.
| Topology | Typical use |
|---|---|
| 1 primary + N replicas | Scale reads, offload backups, keep a warm DR copy, watch primary WAL generation. |
| Cascading replica | Reduce fan-out from the primary; chain through a hub region when cross-cloud. |
Send read to replica (application-level)
-- JDBC / ORM route: replica=true
SELECT pg_is_in_recovery() AS is_replica;| is_replica |
|---|
| true |
Replication lag gauge (PostgreSQL)
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;| lag_seconds |
|---|
| 0.8 |
Invisible staleness
- Sending users to async replicas immediately after writes they expect to see.
- Filling the primary disk when replicas stall and WAL cannot be recycled.
- Assuming sequences stay gap-free across failover, surprise duplicate or skipped IDs.
Route, measure, fail over deliberately
- Expose lag metrics with paging thresholds; heartbeat tables for end-to-end replay delay.
- Document when traffic is primary-only vs replica-safe; test quarterly failover with realistic load.
- Use proxy or DNS patterns that flip cleanly when leadership changes.
Logical vs physical
Logical replication ships row changes, flexible for upgrades and subsets, but can hit conflicts in multi-writer setups. Physical streaming is simpler for single-primary HA pairs; know which story your stack uses.
TL;DR
Replication copies changes from a primary to standby nodes for HA and read scaling. Physical streaming (WAL shipping) mirrors byte-level log records, low overhead, tight failover on single-primary clusters. Logical replication decodes row changes, useful for upgrades, subsets, and cross-version feeds but needs conflict rules if multiple writers exist. Lag and consistency are operational metrics, not SQL keywords.