Read/write splitting can be implemented in application code, in the database driver, or in a connection pooler/proxy. Compare those layers, and describe what commonly breaks when a team turns splitting on.

## The decision the layer has to make For each unit of work, choose primary or replica. Getting it right requires knowing two things the SQL text alone doesn't tell you: does this unit of work write, and does it need fresh data? Every design differs in how it learns those. ## Layer 1 — application / framework The code declares intent. In Spring, `@Transactional(readOnly = true)` plus a routing datasource; in other stacks, an explicit `readReplica()` handle or a repository split. Rails, Django and friends offer equivalent "connected to reading role" blocks. - **Strength:** intent is declared, not inferred. A reviewer can see that a checkout balance check runs against the primary. It composes with read-after-write policy: the same layer that knows the user just wrote can force primary reads. - **Weakness:** coverage. Every read path must be classified, and forgetting one is a silent bug that only shows up as stale data. Retrofitting into a large codebase is real work, and libraries that open their own connections escape it. ## Layer 2 — driver / client library The driver routes based on the connection's read-only flag or by inspecting statements. MySQL Connector/J and several JDBC drivers support replica-aware URLs. - **Strength:** central, little application code. - **Weakness:** it still has to guess when the application doesn't set the read-only flag, and it inherits every hazard of statement sniffing below. ## Layer 3 — proxy / pooler ProxySQL, pgpool-II, MaxScale, HAProxy with lag checks, RDS Proxy, Vitess. The proxy parses SQL and applies rules: `SELECT` → replica, everything else → primary, with exceptions for transactions and pattern-matched queries. - **Strength:** no application change; centralised lag-aware routing, replica health checks, and failover handling; consistent behaviour across many services and languages. - **Weakness:** it infers intent from text. And an important practical note — **PgBouncer does not do read/write splitting**; it is a connection pooler only. Teams routinely assume otherwise. ## The breakage list **Transactions.** A transaction is the unit, not the statement. If `BEGIN; SELECT; UPDATE; COMMIT;` has its SELECT sent to a replica and its UPDATE to the primary, you have destroyed atomicity and isolation. Correct implementations pin the whole transaction to one node based on its first statement or its declared read-only flag — which means a read-only-declared transaction that later writes must fail loudly rather than silently re-route. **Reads that are actually writes.** `SELECT ... FOR UPDATE` and `FOR SHARE` take locks and must run on the primary. `SELECT nextval('seq')` advances a sequence. `SELECT my_function(...)` may insert, update, or call an external system. Statement sniffing sees "SELECT" and gets all three wrong. **Session state.** Temporary tables, `SET`/session variables, advisory locks, server-side prepared statements, `LISTEN`/`NOTIFY`, and cursors all live on one connection on one node. Splitting means a session's state may not be where the next statement lands. This compounds with transaction-level pooling, which recycles connections between transactions and destroys session state regardless of splitting. **Autocommit mode.** In autocommit, each statement is its own transaction and routing per statement is legal — so behaviour differs between an ORM running in autocommit and one wrapping everything in explicit transactions. Many surprises trace back to this difference. **Read-after-write.** Turning on splitting exposes every unclassified read-after-write path at once. Pair the rollout with a stick-to-primary-after-write policy or causal tokens. **Failover and topology change.** After a promotion the old primary may accept connections as a read-only standby; a router that hasn't noticed sends writes there and gets errors, or worse, sends reads to a node that is being rebuilt. Routing needs authoritative topology discovery and quick, correct failover behaviour, plus connection-pool sizing per node so a replica loss doesn't overwhelm the primary. **Observability.** Instrument, per node, the query rate, error rate, lag, and how many transactions were routed each way. Without that, a split that silently stopped working (everything back on the primary, or reads going to a badly lagging replica) is invisible. ## A defensible recommendation Do the routing where intent lives — the application — and use the proxy for what it is genuinely good at: connection pooling, replica health checks, lag-based eviction, and failover. Roll out per endpoint, starting with obviously safe reads (reports, search, listing pages), and keep a kill switch that sends everything back to the primary. Treat "which reads may be stale" as a product decision recorded in code, not as a regex in a proxy config.