What are Read Replica Routing Strategies?
Learn how read replica routing splits writes and reads, handles replication lag, and avoids stale read-your-writes results.
Expected Interview Answer
Read replica routing strategies are the rules an application or proxy uses to decide which database server handles each query — sending writes to the primary and distributing reads across one or more replicas — with common strategies including simple write/read splitting, replica-aware load balancing, and read-your-own-writes consistency routing.
The simplest strategy is static write/read splitting: every INSERT/UPDATE/DELETE goes to the primary, every SELECT goes to a replica pool. A more careful strategy adds session or request-level stickiness so that a user who just wrote data reads from the primary (or a replica confirmed caught up) for a short window, avoiding the "read your own write and see stale data" problem caused by replication lag. Advanced setups monitor replica lag and health, removing a lagging or unhealthy replica from rotation automatically, and can route by query type or latency requirement — analytics queries to a dedicated reporting replica, latency-sensitive reads to the geographically closest healthy replica.
- Distributes read load away from the primary, improving overall throughput
- Read-your-writes routing avoids showing users stale data after they write
- Lag-aware routing automatically excludes unhealthy or delayed replicas
- Query-type routing isolates heavy analytics from latency-sensitive reads
AI Mentor Explanation
A cricket board sends all official scorecard updates to one master scorer, but lets fans check the score from any of several nearby scoreboard displays around the stadium, each fed a copy of the update moments later. If a fan just reported a boundary themselves, the app makes sure they see the master scorer’s live view rather than a display that has not caught up yet. Read replica routing works the same way: writes always go to the primary, reads are spread across replicas, and read-your-writes logic avoids showing a user stale data right after they wrote it.
Step-by-Step Explanation
Step 1
Route writes to the primary
All INSERT, UPDATE, and DELETE statements are always directed to the single primary server.
Step 2
Distribute reads across replicas
A load balancer or driver-level router spreads SELECT queries across the healthy replica pool.
Step 3
Monitor replica lag and health
Continuously check each replica’s replication lag and remove lagging or unhealthy replicas from rotation.
Step 4
Handle read-your-writes cases
Route reads that immediately follow a write from the same session to the primary or a confirmed caught-up replica.
What Interviewer Expects
- Understanding of basic write-to-primary, read-from-replica splitting
- Awareness of the read-your-own-writes staleness problem and its fix
- Knowledge of lag-aware or health-aware replica selection
- Ability to discuss trade-offs like eventual consistency on replicas
Common Mistakes
- Routing all traffic to replicas without accounting for replication lag
- Not solving the read-your-own-writes stale-data problem
- Ignoring replica health checks, sending traffic to a failing replica
- Assuming replica routing is only a driver concern with no application implications
Best Answer (HR Friendly)
“Read replica routing is about deciding which server answers each database request: writes always go to the primary, while reads get spread across replica servers to reduce load. The tricky part is making sure a user who just wrote data does not read a stale replica right after, and making sure lagging or unhealthy replicas get removed from rotation automatically.”
Code Example
-- Writes always target the primary connection
INSERT INTO Orders (customer_id, total) VALUES (101, 250.00);
-- Reads are routed to a replica pool by default
SELECT * FROM Orders WHERE customer_id = 101;
-- executed against replica_pool[round_robin_index]
-- Read-your-writes: immediately after a write in the same session,
-- route the next read to the primary instead of a replica
-- to avoid showing data that has not replicated yet.
SELECT * FROM Orders WHERE order_id = 555; -- routed to primaryFollow-up Questions
- How would you detect and exclude a lagging replica automatically?
- What is the read-your-own-writes consistency problem and how do you solve it?
- How does replica routing differ for OLTP versus analytics workloads?
- What happens to read routing during a primary failover?
MCQ Practice
1. In basic read replica routing, where do write statements go?
Writes must go to the primary since replicas are typically read-only copies kept in sync via replication.
2. What problem does read-your-own-writes routing solve?
Because replicas may lag behind the primary, routing a user’s immediate follow-up read to the primary avoids showing stale results.
3. What should a well-designed replica router do with a replica that develops high lag?
A lag-aware router excludes unhealthy or significantly lagging replicas from serving reads until they catch up.
Flash Cards
What is read replica routing? — Directing writes to the primary and distributing reads across replica servers.
What is the read-your-own-writes problem? — A user reading stale data from a replica immediately after writing to the primary.
How do you fix read-your-own-writes staleness? — Route the immediate follow-up read to the primary or a confirmed caught-up replica.
What should lag-aware routing do? — Automatically exclude replicas with high replication lag from serving reads.