Team "adds replication for performance." Two months later: saturated master, replica idle at 4% CPU, budget doubled. Other scenario: "automatic" failover switches — app still writes to old dead master IP — 20 min downtime.
Replication solves one well-framed problem:
- Availability (survive master death)
- Read scale (SELECT offload)
- Reporting (analytics without killing prod)
- Geo proximity (local read — weak consistency)
Choosing "replica because pro" without read routing = cost + complexity without gain.
Goal → architecture matrix
| Goal | Pattern | Pitfall |
|---|---|---|
| HA failover | Primary + sync/semi-sync standby + Patroni | Untested split-brain |
| Read scale | Async replicas + read/write split proxy | Lag UX post-write |
| Live backup | Delayed replica | Not substitute tested backup |
| Geo read | Regional replica | Consistency conflicts |
Replica is not backup — logical corruption replicates too.
Replication lag and web UX
After signup:
// POST master → redirect GET /profile
// GET on replica → user not visible yet if 2s lag
Solutions:
- Read-your-writes: sticky master N seconds post-write.
- Proxy: ProxySQL, PgBouncer smart routing.
- CQRS: accept eventual consistency in UI.
MySQL vs PostgreSQL (ops view)
MySQL: async/semi-sync binlog; read replicas; MHA/Orchestrator failover.
PostgreSQL: streaming replication; hot standby read; Patroni + etcd failover.
Managed (RDS, Cloud SQL, OVH DB): Multi-AZ ≠ read replica — read product docs. For hosting choice, see VPS vs cloud and the directory.
The peak: replication doubles data — not availability without tests
Here is what the "high availability" slide forgets to show.
Budget: quarterly failover drill > third unused replica.
Decide and move forward without blind spots
Before adding a replica, state the goal in one sentence:
- State goal: HA, read scale, reporting or geography.
- Measure read/write ratio on app routes.
- Route SELECTs explicitly if read scale.
- Monitor lag + alerts beyond business threshold.
- Test documented failover at least quarterly.
A replica without read routing or failover drill is a budget line, not insurance. Cross-read PostgreSQL EXPLAIN to optimize the master before duplicating it.
Frequently asked questions
Does read replica automatically speed up my site?
Only if you explicitly route SELECTs to the replica and load is read-heavy. If everything hits the master, the replica stays idle and useless for performance.
What is replication lag?
Delay between master commit and replica visibility. A few seconds of lag can temporarily hide data a user just wrote from their next page load.
Sync vs async replication?
Sync limits data loss but increases write latency. Async improves write performance at the cost of loss risk if the master dies before propagation.
Is automatic failover plug-and-play?
No. It needs an orchestrator, regular tests and split-brain handling. DNS or VIP must switch to the new master — nothing is automatic without config and drill.
Replicating without goal means paying twice for the same badly indexed query on the master.