Course contentsShow
← System Design
Lesson 222 of 1,9196. Database Scaling: Read ReplicasFree lesson

Proxy-Based Read-Write Splitting

Using database proxies like ProxySQL or MaxScale to automatically route queries based on statement type.

Proxy-Based Read-Write Splitting

What You'll Learn

You'll discover how specialized database proxies automatically route queries to the right database servers without requiring application code changes.


The Core Idea

In Application-Level Connection Management (lesson 221), your application code had to decide whether each query should go to the primary or a replica. Proxy-Based Read-Write Splitting moves that responsibility to a middleware layer—a database proxy that sits between your application and your databases.

Tools like ProxySQL (for MySQL) or MaxScale automatically inspect incoming SQL queries and route them intelligently:

  • Write queries (INSERT, UPDATE, DELETE) → Primary database
  • Read queries (SELECT) → Read replicas

Your application connects to the proxy as if it were a single database. The proxy handles all the routing logic behind the scenes.

Why Use a Proxy?

Centralized logic: Instead of every microservice implementing routing rules, one proxy handles it for all connections.

Zero application changes: Legacy apps can gain read-write splitting without code modifications—just point them to the proxy.

Advanced features: Proxies can provide connection pooling, query caching, lag-aware routing, and even query rewriting.

Real-World Analogy

Think of a restaurant host who reads your order slip and directs you:

  • "Pickup order?" → Routes you to the takeout counter (replica)
  • "Reservation change?" → Routes you to the manager's desk (primary)

You don't need to know the restaurant's internal structure—the host handles routing.


Key Takeaway:
Proxy-based read-write splitting uses middleware like ProxySQL or MaxScale to automatically route queries based on their type (read vs. write), eliminating the need for application-level routing logic and simplifying database scaling.