Decision in brief
Choose replication for an explicit failure or read-scaling requirement. Prefer asynchronous replicas when the workload tolerates delayed reads and defined data loss on promotion. Consider synchronous acknowledgment when the write-loss budget requires it, and test the added latency and behavior when acknowledgments become unavailable. Keep a separately recoverable backup for accidental or bad writes.

Map the failure to the replication design
Write a recovery time objective and a recovery point objective for loss of a process, a host, or a location. State where the next primary may be promoted and which dependencies could fail with it. A read replica, a standby eligible for promotion, and a logical change consumer serve different jobs.
PostgreSQL streaming replication is asynchronous by default. Synchronous settings define where configured standbys must acknowledge progress before a commit returns. In MySQL asynchronous replication, a replica can be behind the source. MySQL semisynchronous replication adds acknowledgment that a replica received and logged events, while applying those events is a separate step. A timeout can return the source to asynchronous operation, so check whether that fallback fits your write-loss limit. Confirm the commit and read semantics for the exact version and configuration you use.
| Goal | Design to evaluate | Failure to include |
|---|---|---|
| Serve reports with acceptable delay | A read replica and an explicit staleness limit | Pause replay and verify the application routes read-after-write requests to a current source. |
| Recover quickly after primary failure | A promotion-ready standby in a separate failure domain | Remove the primary and time reconnect, promotion, and acceptance of correct writes. |
| Limit loss of acknowledged writes | Synchronous acknowledgment where available and correctly configured | Measure commit p95 during replica loss and reconcile acknowledged writes after promotion. |
| Feed analytics or downstream systems | A logical publication or another scoped change feed | Pause and resume the consumer, then account for duplicates, missing events, and retained logs. |
Two HN accounts that change the failure drill
lukaslalinsky described running sizable self-hosted PostgreSQL systems and emphasized checking that streaming replication keeps working. Give the on-call owner a concrete alarm for stalled replay or a standby that loses its promotion readiness.
exabrial recalled a MySQL split-brain incident that required identifying individual transactions and replaying them against the new primary. Add a rehearsal that proves the old primary is isolated before clients resume writes and that every acknowledged test transaction can be reconciled.
Measure replica lag, promotion, and client recovery
- In an isolated test topology, seed synthetic data and generate timestamped write IDs. Record the acknowledged IDs outside the database and decide how much loss, staleness, and downtime the application can tolerate.
- Under representative load, stop one standby in staging. Measure commit latency, receive and replay lag, and whether the alert arrives within the operator response budget.
- Run the intended failover procedure after isolating the old primary. Time the first healthy application read and write, check client reconnection and background consumers, and reconcile acknowledged IDs. Rebuild or rejoin the old node through the documented procedure.
- Approve only if the old primary stays excluded, no unexplained transaction gap remains, and application-visible downtime and write loss meet target. Stop when two writers can be active or a replica or consumer cannot resume safely.