Database Replication & Read Replicas
Copy one DB's data to other servers so that reads are shared and, if one server falls, another takes over.
12.1 Why is it used?
- A single DB server cannot handle all the reads; sending reads to read replicas spreads the load.
- If the primary server crashes, promoting a replica keeps the site running (high availability).
- A replica in a different region gives that region's users fast reads and gives you disaster recovery.
- Run reports and analytics on a replica so that the production DB does not slow down.
12.2 How it works
- Primary-Replica (master-slave): all writes go to the primary, which sends the changes (binlog/WAL) to the replicas; reads are served from the replicas.
- Multi-master (multi-primary): more than one node can accept writes; conflict resolution is needed (last-write-wins, etc.).
- Leaderless: there is no master, the client writes/reads on several nodes, and quorum (W + R > N) gives consistency: Cassandra, Dynamo.
- Synchronous replication: the primary does not commit until a replica confirms; no data loss, but slow.
- Asynchronous replication: the primary commits immediately and the replica copies later; fast, but some data loss is possible on a crash.
- Semi-sync: commit only after at least one replica has received the change; the middle path (MySQL semi-sync).
- Replication lag: a replica can be a few ms or seconds behind the primary, so a stale read is possible.
- Failover: when the primary goes down a replica is promoted: automatic (Patroni, RDS Multi-AZ, Orchestrator) or manual.
- Read-your-writes: for a short while after a write, serve that user's reads from the primary so that replication lag does not make their own data look missing.
- 1.Users: lakhs of reads + a few bookings
- 2.ProxySQL routes by query: SELECT -> replica, INSERT/UPDATE -> primary
- 3.Primary MySQL: writes only (booking + final seat check)
- 4.Changes go to 5 read replicas via async binlog / WAL (lag 100-500 ms)
- 5.Sync standby (Multi-AZ, second AZ): promoted within 1-2 min if the primary crashes
Right after a booking, show "my booking" from the primary (read-your-writes); a replica may still show a seat as available even though it is already booked
- 1.Synchronous: app write -> primary copies to replica -> replica ack -> only then commit OK (no data loss, but slow writes)
- 2.Asynchronous (default): app write -> primary commit OK -> copy later (fast, but the last few writes can be lost on a crash)
- 3.Semi-sync (MySQL): app write -> primary copies -> at least 1 replica has received it -> commit OK (the middle path)
12.3 Tools
| Tool | Type | How it is used |
|---|---|---|
| MySQL replication | Binlog-based replication | Async, semi-sync or Group Replication; read replicas are very common. |
| PostgreSQL streaming replication | WAL-based replication | Sync or async standby; automatic failover with Patroni. |
| AWS RDS / Aurora | Managed DB | Multi-AZ sync standby for failover, read replicas (up to 15 in Aurora) for reads. |
| MongoDB Replica Set | Primary-secondary | Automatic election, durability via write concern majority. |
| Cassandra | Leaderless multi-DC | Tuned with the replication factor and consistency level (ONE, QUORUM, ALL). |
| ProxySQL / pgpool-II | DB proxy | Automatically route writes to the primary and reads to the replicas. |
12.4 Comparison: Single-leader vs Multi-leader vs Leaderless
| Point | Single-leader (Master-Slave) | Multi-leader (Multi-master) | Leaderless (Quorum) |
|---|---|---|---|
| Where writes go | Only on the primary | On several primaries | On any node |
| Write conflicts | Do not occur | Occur and must be resolved | Occur and are resolved with versioning/LWW |
| Consistency | Strong on the primary, lag on replicas | Eventual | Tunable (W + R > N) |
| Failover | A replica has to be promoted | Another leader already exists | No failover needed |
| Complexity | Simple | High | Medium |
| Examples | MySQL, PostgreSQL, MongoDB | MySQL Group Replication multi-primary, Galera, CouchDB | Cassandra, DynamoDB internals, Riak |
| Best for | Most apps, read-heavy | Multi-region writes | Always-on, write-heavy, multi-DC |
12.5 When to choose which
- Single-leader + read replicas: for 90% of apps; simple and reliable.
- Sync replication (or Multi-AZ): when not even one transaction may be lost: payments, banking.
- Async replication: for cross-region replicas and read scaling, where a little lag is acceptable.
- Multi-leader: when users in different regions write locally and latency matters.
- Leaderless (Cassandra): when you want no single point of failure and writes are very heavy.
12.6 Real-world example
Read replicas in an IRCTC-like booking system (an illustrative scenario)
- At Tatkal time lakhs of people check train availability: very many reads, few bookings (writes).
- Only booking writes go to one primary MySQL/PostgreSQL.
- Reads such as "seat availability" and "PNR status" are routed to 5 read replicas (via ProxySQL).
- With async replication a replica can be 100-500 ms behind, so the final seat check happens on the primary.
- Right after booking, the user is shown their booking from the primary (read-your-writes).
- If the primary crashes, the Multi-AZ sync standby is promoted within 1-2 min, with no data loss.
12.7 Interview tips
- Reading from a replica right after a write is a mistake: the user will not see their own data (replication lag).
- Treating a replica as a backup is a mistake: if a DELETE is run by accident it is deleted on the replica too; take a separate backup.
- Not monitoring replication lag is a mistake (Seconds_Behind_Source, pg_stat_replication).
- Interview tip: state the sync vs async trade-off (durability vs latency) clearly. Numbers: async replica 100-500 ms behind, Multi-AZ failover 1-2 min.