Query Routing Strategies: Application Level vs Proxy Based

Query Routing Fundamentals
Query routing is the logic that decides where each database operation executes: the primary for writes and consistent reads, or replicas for operations tolerating staleness. Good routing maximizes replica utilization while maintaining correctness for operations requiring fresh data.
The core decision: does this query need the absolute latest data? Writes always go to primary. Reads split based on consistency requirements. User-facing reads after writes need primary or same-session consistency. Background analytics can safely read from replicas.
Application-Level Routing
Application-level routing puts decisions in your service code. The application maintains separate connection pools—one for the primary (writes and consistent reads), one for replicas (stale reads acceptable). Code explicitly chooses which pool based on the operation.
This approach offers fine-grained control. You annotate each query with its consistency requirement. Critical paths use primary; background jobs use replicas. The tradeoff is complexity: developers must understand consistency implications and correctly tag every query. Mistakes route consistent-read queries to stale replicas.
Proxy-Based Routing
A database proxy sits between applications and databases, intercepting queries and routing them automatically. Proxies parse SQL to distinguish reads from writes. All writes and transactions route to primary; standalone SELECTs route to replicas.
Proxies centralize routing logic, freeing applications from connection management. They can implement sophisticated policies: route to least-loaded replica, avoid lagging replicas, enforce read-after-write consistency. The tradeoff is an additional network hop and a critical single point of failure if not deployed with high availability.
Hybrid Approaches
Production systems often combine approaches. Application code marks queries with consistency requirements (strong, eventual, session). A proxy or driver library interprets these hints, routing to appropriate targets. This separates business logic (what consistency do I need) from infrastructure (which replica is healthy).
Connection poolers with routing awareness reduce overhead. Instead of applications managing multiple pools, the pooler accepts hints and routes internally. This centralizes configuration while allowing per-query control.
Routing Decision Factors
Effective routing considers multiple factors: query type (read/write), consistency requirement, replica lag, replica load, and geographic proximity. A read marked as eventual-consistency should still avoid a replica 30 seconds behind—thats likely unhealthy, not just eventually consistent.

💡 Key Takeaways

✓Application level routing offers maximum flexibility for consistency policies but requires every service team to implement and maintain query classification, failover handling, and topology tracking logic

✓Proxy based routing adds 0.5 to 3 milliseconds of latency per query due to the extra network hop, and the proxy becomes a single point of failure requiring its own high availability architecture

✓Production proxies must parse SQL to classify queries, track transaction boundaries, monitor per replica lag and saturation metrics, and implement circuit breakers to handle replica failures without cascading overload

✓Amazon Aurora abstracts routing with separate writer and reader endpoints, where the reader endpoint automatically load balances across up to 15 replicas within a region while applications choose endpoint per query

✓Shopify uses database aware proxies to handle flash sale traffic spikes, implementing transaction pinning to primary, lag aware replica selection, and automatic fallback when routing uncertainty is detected

📌 Interview Tips

1Application level: Your Python service maintains two connection pools. After a user updates their profile, you set a session flag and route the next 3 read queries to the write pool (primary) to ensure read after write consistency, then revert to the read pool.

2Proxy based: ProxySQL or Vitess sits between your app and MySQL. It parses every query, routes SELECT statements to healthy replicas with lag under 100ms, and automatically pins entire transactions to primary if it sees BEGIN or any non SELECT statement.

3Hybrid approach: Your service connects to Aurora writer endpoint for all writes and post write reads, and connects to Aurora reader endpoint for timeline and catalog queries that tolerate 50ms staleness. Aurora handles load balancing across replicas internally.

← Back to Read Replicas & Query Routing Overview