Day 03 · System Design
Database Replication & Read/Write Splitting
Scale reads and survive failures by copying data to replicas and routing queries by type.
01Overview & core concept
As an application grows, database reads typically outnumber writes by a wide margin (often 10:1 or 100:1). Database replication copies data from a designated primary (leader) node to one or more replica (follower) nodes. Read/write splitting then directs all mutating queries (INSERT, UPDATE, DELETE) to the primary, while routing read-only queries (SELECT) across the pool of read replicas.
Replication
Copies data from the primary to replicas, providing redundancy for failover.
Read/Write Splitting
Scales read throughput horizontally by spreading SELECT queries across replicas while the primary handles writes.
02Architecture & request flow
┌────────────────────────┐
│ Application │
└───────────┬────────────┘
│ SQL Queries
▼
┌────────────────────────┐
│ Database Proxy / Router│ (e.g., ProxySQL, Vitess)
└─────┬────────────┬─────┘
Writes │ │ Reads (Load Balanced)
┌────────────────────┘ └────────────────────┐
▼ ▼
┌──────────────────┐ Replication Stream (Binlog) ┌──────────────────┐
│ Primary (RW) ├─────────────────────────────────────►│ Replica 1 (RO) │
│ (Handles Writes) │ └──────────────────┘
└──────────────────┘ ┌──────────────────┐
│ Replica 2 (RO) │
└──────────────────┘Replication synchronization modes
Asynchronous Replication
The primary commits and immediately acknowledges the client without waiting for replicas, which pull changes from the replication log (MySQL binary log, PostgreSQL WAL). Advantage: minimal write latency. Risk: replication lag can cause stale reads, and data may be lost if the primary crashes before log propagation.
Synchronous Replication
The primary writes locally and blocks until all replicas confirm the write. Advantage: strong consistency and zero data loss on failover. Risk: write latency equals the slowest replica, and write availability drops if any single replica fails.
Semi-Synchronous Replication
The primary waits for at least one replica to acknowledge receipt and persistence of the transaction log before returning success. Balances durability with write latency.
03Real-world example: Shopify & GitHub MySQL Architecture
Proxy Routing with ProxySQL / Vitess
Applications send queries through an intermediate SQL-aware proxy layer that parses query ASTs (Abstract Syntax Trees) to automatically route read queries to healthy replicas and transactional writes to the primary.
Automated Failover via Orchestrator
If the primary becomes unresponsive, an orchestration daemon (like GitHub's Orchestrator) coordinates leader election, promotes the most up-to-date replica, re-points other replicas to the new leader, and updates DNS/ProxySQL topology routing within seconds.
04Architectural trade-offs
| Dimension | Single Primary Instance | Primary + Asynchronous Read Replicas |
|---|---|---|
| Read Throughput | Bound by single-host CPU/memory/IOPS. | Scales linearly with the number of replicas added. |
| Write Latency | Low and predictable. | Minimal impact (in async mode), but the primary still handles 100% of writes. |
| Read Consistency | Immediate consistency (read-your-writes). | Eventual consistency; replicas can lag behind the primary by milliseconds to seconds. |
| Operational Overhead | Low. | High (monitoring replication lag, schema migration synchronization, failovers). |
Failure modes & mitigations
Replication Lag & Read-Your-Own-Writes Inconsistency
A user updates their profile and immediately refreshes; the write goes to the primary but the read hits a lagging replica and shows old data. Mitigation: pin the user's reads to the primary for a brief window (e.g., 5 seconds) after a write, or track transaction log offsets (GTID / LSN tokens) and route to replicas that have caught up.
Split-Brain Scenario
Network partitions cause two nodes to believe they are the primary, accepting conflicting writes. Mitigation: use consensus algorithms (Raft/Paxos) or fencing tokens to ensure a strict quorum before electing a new primary.
05Interview question & approach
How would you design the database tier for an e-commerce platform where users must immediately see their newly placed orders on their order history page, but overall read traffic is 50x higher than write traffic?
Separation of Concerns
Use a primary database for all checkout/order creation transactions and asynchronous read replicas for browsing product catalogs and search queries.
Read-After-Write Consistency Solution
Session routing: store a timestamp or transaction ID in the user's session cookie on order submission, and if currentTime - lastOrderTimestamp is under a threshold, route order history reads to the primary. Alternatively, use CQRS + event streaming: produce an OrderCreated event to Kafka and update a local view model or cache (Redis) that serves the user's immediate view.
Replication Health & Lag Monitoring
Describe how the proxy layer measures replication lag (via Seconds_Behind_Master or heartbeat tables) and automatically evicts replicas from the read pool if their lag exceeds a predefined threshold (e.g., > 500ms).