The Problem It Solves
A standard MySQL or PostgreSQL primary/replica setup gives you additional read capacity — but only if your application actually sends reads to the replicas. Most applications connect to a single database endpoint and send all traffic there. The replicas sit idle.
Manually splitting traffic requires the application to maintain two connection pools (one for writes, one for reads) and route queries explicitly. This is error-prone, adds complexity to every database call, and is impossible to retrofit into legacy applications without significant refactoring.
A database proxy solves this transparently.
How Routing Rules Work
The proxy inspects the first keyword of each incoming SQL statement and routes it based on configurable rules:
INSERT,UPDATE,DELETE,REPLACE,TRUNCATE,CREATE,ALTER,DROP→ Primary (Writer)SELECT→ Replica pool (Readers), distributed round-robin or by weight
In ProxySQL, this is configured via hostgroups and query rules: the primary and each replica are registered in mysql_servers under separate hostgroup IDs (say, 10 for the primary and 20 for replicas), then two query rules match on the leading SQL keyword — one routing INSERT/UPDATE/DELETE/etc. to the primary's hostgroup, the other routing SELECT to the replica hostgroup.
The Replication Lag Caveat
Replicas are asynchronous by default. A write committed to the primary may take milliseconds to seconds to appear on a replica. If your application writes a record and immediately reads it back, the read may be routed to a replica that has not yet received the write — and the application will see stale data or a missing row.
The solution is to add a high-priority exception rule for queries that must read fresh data, routing them to the primary instead of the replica pool. This is the most important nuance in read/write splitting design — knowing which reads are safe to serve from replicas and which are not.