Read/Write Splitting at the Proxy Layer

Problem Framing

Every team operating read replicas eventually faces the same routing question: how do you direct SELECT queries to replicas and writes to the primary without scattering infrastructure decisions throughout application code? Without a centralised answer, routing logic ends up duplicated across services, inconsistently applied at ORM configuration points, and invisible to the operators who need to tune it during incidents.

Proxy-layer splitting solves this by inserting a dedicated intermediary between the application tier and the database nodes. The proxy owns the routing policy. Applications connect to a single endpoint, the proxy inspects each statement, and the primary and replicas remain topology details the application never needs to know about. The SLA risks this mitigates are concrete: stale reads from missed lag thresholds, connection storms when a replica fails, and split-session inconsistency when a transaction spans multiple nodes.

This page covers how proxy-layer splitting works internally, the configuration patterns that actually hold up in production, and the failure modes teams consistently encounter when they get the details wrong.

What Proxy-Layer Splitting Is (and Is Not)

Proxy-layer splitting is the practice of routing database traffic at a stateful intermediary — ProxySQL, PgBouncer, HAProxy, or Envoy — rather than inside the application driver or ORM. The proxy operates at either Layer 4 (TCP stream multiplexing, no SQL inspection) or Layer 7 (SQL-aware parsing of statement intent).

Layer 4 proxies (HAProxy in mode tcp) balance connections by health and weight. They cannot inspect SQL, so splitting requires separate frontend ports: one for writes, one for reads. Applications must explicitly choose the right port or DNS alias.

Layer 7 proxies (ProxySQL, pgpool-II) parse the SQL stream, classify queries, and route each statement independently — all within a single client connection. This is transparent splitting: one connection string, automatic routing. The tradeoff is parsing overhead and additional failure surface (a proxy bug can misclassify a query).

This is distinct from ORM middleware for automatic query routing, which performs similar classification inside the application process, and from connection pool architecture decisions about how many connections back each replica node.

How It Works: Proxy Rule Evaluation Internals

When a client sends a query, the proxy executes the following steps internally:

1. Statement receipt. The proxy buffers the incoming bytes on the client-facing socket. For Layer 7 proxies, the MySQL or PostgreSQL wire protocol is decoded to extract the query string.
2. Rule evaluation. Rules are evaluated in priority order. ProxySQL uses an ordered list of mysql_query_rules rows; the first matching rule (lowest rule_id) wins if apply=1 is set. Without apply=1, evaluation continues to subsequent rules — a common misconfiguration that allows a catch-all to override earlier specific rules.
3. Transaction state check. Before accepting a read-routing decision, the proxy checks session state. If the session is inside an open transaction (BEGIN detected, or autocommit=0), the destination is forced to the primary hostgroup regardless of the query type.
4. Hostgroup selection. The classified query is forwarded to the target hostgroup. Within the hostgroup, the proxy applies a load-balancing algorithm (leastconn, round-robin, or weight-based) to select the specific backend node.
5. Health filter. Nodes marked OFFLINE_SOFT or SHUNNED (failing consecutive health checks) are excluded from selection. If no healthy node exists in the replica hostgroup, the proxy applies the configured fallback — either routing to primary or returning a routing error.

Prepared statement tracking is an additional concern: PREPARE creates a statement handle on a specific backend connection. EXECUTE must reach the same backend or the handle is invalid. ProxySQL tracks prepared statement IDs per connection and respects this constraint, but only when multiplexing=0 for that session or when mysql-handle_unknown_charset=1 is set. Getting this wrong silently breaks applications that use PREPARE/EXECUTE extensively.

Trade-off Comparison

Key observations:

• ProxySQL is the most capable option for MySQL/MariaDB but has no native PostgreSQL support. Teams running PostgreSQL at scale typically reach for pgpool-II or application-level routing.
• PgBouncer alone does not perform query routing. It is a connection multiplexer. Pairing it with pgpool-II adds routing but significantly increases operational surface.
• HAProxy with dual frontend ports is the simplest architecture and sufficient when the application already uses separate connection strings for reads and writes — as it would when implementing ORM middleware routing.

Configuration Runbook

ProxySQL (MySQL/MariaDB)

-- 1. Define hostgroups: 10 = primary, 20 = replicas
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES
(10, '10.0.1.5',  3306, 500),   -- primary
(20, '10.0.1.10', 3306, 500),   -- replica-1
(20, '10.0.1.11', 3306, 500);   -- replica-2

-- 2. Routing rules — evaluated in rule_id order; apply=1 halts further evaluation
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
-- Locking reads must pin to primary (must precede the general SELECT rule)
(10, 1, '^SELECT\s.*\sFOR\s(UPDATE|SHARE)',  10, 1),
-- Standard reads to replicas
(20, 1, '^SELECT\s',                          20, 1),
-- Everything else (writes, DDL, session commands) to primary
(99, 1, '.*',                                 10, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 3. Lag-aware replica exclusion
-- Replicas with Seconds_Behind_Source > 5 s are removed from read hostgroup
UPDATE global_variables SET variable_value = '5000'
  WHERE variable_name = 'mysql-max_replication_lag';

-- Connection timeout: fail fast rather than queuing behind a slow backend
UPDATE global_variables SET variable_value = '1000'
  WHERE variable_name = 'mysql-connect_timeout_server';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Failure-state annotations:

• Without rule 10 (FOR UPDATE pin), a SELECT ... FOR UPDATE hits a replica, returns stale rows, and the subsequent UPDATE on the primary races against a concurrent writer — silent data corruption.
• Omitting SAVE ... TO DISK means rules survive only until the next ProxySQL restart.
• mysql-max_replication_lag=0 (the default) disables lag-aware routing entirely. Set an explicit threshold before going live.

HAProxy (PostgreSQL, dual-port)

# haproxy.cfg — TCP proxy for PostgreSQL primary + replicas

defaults
  mode    tcp
  timeout connect  1s
  timeout client   30s
  timeout server   30s
  maxconn 20000

# Write endpoint: port 5432 → primary only
frontend pg_write
  bind *:5432
  default_backend pg_primary

backend pg_primary
  balance first             # always prefer the first server (primary)
  option tcp-check
  server primary 10.0.1.5:5432 check inter 2s fall 3 rise 2 maxconn 500

# Read endpoint: port 5433 → replicas, fallback to primary
frontend pg_read
  bind *:5433
  default_backend pg_replicas

backend pg_replicas
  balance leastconn
  option tcp-check
  server replica1 10.0.1.10:5432 check inter 2s fall 3 rise 2 maxconn 500
  server replica2 10.0.1.11:5432 check inter 2s fall 3 rise 2 maxconn 500
  # backup: activated only when all non-backup servers are down
  server primary  10.0.1.5:5432  check inter 2s fall 3 rise 2 maxconn 200 backup

Critical notes:

• option httpchk is for HTTP backends. Do not use it for PostgreSQL TCP connections — it sends an HTTP request to the PostgreSQL port, which responds with a protocol error and HAProxy marks the server DOWN.
• The backup keyword on the primary means HAProxy only routes to it when all replica servers have failed their health checks. Without it, HAProxy load-balances reads to the primary alongside replicas, defeating the replica offload goal.
• fall 3 means three consecutive failed checks before a server is marked down. At inter 2s, this gives a 6-second detection window. Tune for your replication lag tolerance.

PgBouncer (statement-mode, read-only replica pool)

; pgbouncer-replicas.ini
[databases]
appdb_read = host=10.0.1.10 port=5432 dbname=appdb
; Add a second entry for replica-2 or use a DNS round-robin hostname

[pgbouncer]
listen_port        = 6433
listen_addr        = *
auth_type          = scram-sha-256
auth_file          = /etc/pgbouncer/userlist.txt

; statement mode: connection is returned to the pool after each statement
; suitable for read-only queries; incompatible with multi-statement transactions
pool_mode          = statement

max_client_conn    = 5000
default_pool_size  = 25       ; connections PgBouncer holds open to the replica
reserve_pool_size  = 5        ; emergency reserve for connection spikes
reserve_pool_timeout = 3      ; seconds before reserve pool activates

; Health checks
server_check_query = SELECT 1
server_check_delay = 10       ; seconds between checks
server_lifetime    = 3600     ; recycle backend connections hourly

PgBouncer in statement mode is incompatible with transactions, SET commands that persist across statements, and prepared statements in the extended query protocol. For connection pool architecture decisions that involve prepared statements or session-level configuration, use transaction mode instead, accepting the trade-off of higher backend connection count.

Monitoring and Alerting Signals

ProxySQL metrics (via the stats schema)

-- Query distribution by hostgroup
SELECT hostgroup, srv_host, Queries, Bytes_sent_net, Latency_us
FROM stats.stats_mysql_connection_pool
ORDER BY hostgroup, Queries DESC;

-- Routing decisions in the last minute
SELECT rule_id, hits FROM stats.stats_mysql_query_rules
WHERE hits > 0
ORDER BY rule_id;

-- Replica lag exposure (connection errors suggest lag-induced exclusions)
SELECT * FROM stats.stats_mysql_global
WHERE Variable_Name IN ('Client_Connections_aborted','Server_Connections_aborted',
                        'Queries_backends_bytes_sent','max_connect_errors');

HAProxy stats endpoint

# Enable the stats socket in haproxy.cfg:
#   stats socket /run/haproxy/admin.sock mode 660 level admin
echo "show stat" | socat /run/haproxy/admin.sock - | cut -d',' -f1,2,5,7,18,19 | column -t -s','
# Fields: pxname, svname, scur (current sessions), status, req_tot, hrsp_5xx

Prometheus rules

groups:
  - name: proxy_splitting
    rules:
      - alert: ReplicaHostgroupEmpty
        expr: proxysql_connection_pool_connections_total{hostgroup="20"} == 0
        for: 30s
        annotations:
          summary: "All replicas excluded from read hostgroup — all reads falling back to primary"

      - alert: ProxySQLHighReadLatency
        expr: proxysql_connection_pool_latency_us{hostgroup="20"} > 50000
        for: 2m
        annotations:
          summary: "Replica read latency > 50 ms — check replication lag and replica health"

      - alert: HAProxyReplicaDown
        expr: haproxy_server_status{proxy="pg_replicas",server!="primary"} == 0
        for: 10s
        annotations:
          summary: "HAProxy replica backend {{ $labels.server }} is DOWN"

Key metrics to track continuously:

• Read/write ratio per hostgroup — a sudden shift (e.g., replica hostgroup drops to 0% of traffic) indicates all replicas failed health checks.
• Connection churn rate — high Server_Connections_aborted in ProxySQL often signals a lag-induced exclusion loop where replicas cycle in and out of the hostgroup.
• Replication lag seconds — feed Seconds_Behind_Source (MySQL) or the delta from pg_stat_replication.replay_lsn (PostgreSQL) into the same dashboard as proxy routing metrics. Lag spikes that exceed mysql-max_replication_lag will show as a read ratio collapse.

Failure Modes and Recovery Steps

1. Split-session routing (reads hit replica mid-transaction)

Root cause: A SELECT query matches the read rule before the proxy processes the preceding BEGIN. This happens when BEGIN arrives in the same TCP segment as the first SELECT and the proxy’s rule evaluator processes them in isolation without flushing session state first. It also occurs in ProxySQL when mysql-forward_autocommit=true and the application relies on implicit transactions.

Diagnosis:

-- ProxySQL: check if any read rules are firing during known transaction workloads
SELECT rule_id, hits FROM stats.stats_mysql_query_rules WHERE rule_id IN (10, 20, 99);
-- If rule 20 (reads to replica) shows unexpected hits during write-heavy periods, session pinning is broken.

Recovery: Set mysql-handle_warnings=1 and mysql-forward_autocommit=0 in ProxySQL global variables. Add an explicit rule with lower rule_id to route ^(BEGIN|START TRANSACTION) to the primary with apply=1 before the generic SELECT rule.

2. Replica health check false positives cycling the hostgroup

Root cause: The health check query (SELECT 1) is timing out due to replica I/O saturation — typically during large replication batch catch-up — causing the proxy to mark the replica SHUNNED. As the replica recovers, rise 2 re-admits it. Under sustained load, this cycles rapidly, causing connection churn and intermittent read errors.

Diagnosis:

# HAProxy: watch server state transitions in real time
watch -n1 "echo 'show stat' | socat /run/haproxy/admin.sock - | grep pg_replicas | cut -d',' -f1,2,18,19"
# Look for rapid UP/DOWN/UP transitions in the status column

Recovery: Increase fall threshold (e.g., fall 5) to require more consecutive failures before marking a server down. Widen inter to 5s to reduce probe frequency during I/O spikes. For ProxySQL, raise mysql-monitor_connect_timeout and mysql-monitor_ping_timeout.

3. SELECT ... FOR UPDATE routed to a replica

Root cause: The FOR UPDATE match pattern in the query rules has a higher rule_id than the generic SELECT rule, or apply=1 is missing on the generic rule, allowing evaluation to fall through.

Diagnosis: Enable ProxySQL query digest logging and inspect digest_text for SELECT ... FOR UPDATE entries showing hostgroup=20:

SELECT digest_text, hostgroup, count_star FROM stats.stats_mysql_query_digest
WHERE digest_text LIKE '%FOR UPDATE%' AND hostgroup = 20
ORDER BY count_star DESC LIMIT 10;

Recovery: Ensure the FOR UPDATE / FOR SHARE rule has a lower rule_id than the generic SELECT rule AND apply=1. After fixing, run LOAD MYSQL QUERY RULES TO RUNTIME and re-run the digest query to confirm zero hits in hostgroup 20.

4. DNS-based failover creates a stale primary endpoint

Root cause: After primary promotion, the proxy’s backend IP (hardcoded or cached DNS) still points to the old primary. New writes go to the demoted node. This is distinct from a health-check failure — the old primary may still be accepting connections (now as a replica) and pass TCP health checks.

Diagnosis: Compare the proxy’s current backend hostname resolution against the actual primary:

# Check where ProxySQL thinks the primary is
mysql -h 127.0.0.1 -P 6032 -u admin -padmin \
  -e "SELECT hostgroup_id, hostname, status FROM mysql_servers WHERE hostgroup_id=10;"

# Confirm current primary via replication status
mysql -h 10.0.1.5 -e "SHOW REPLICA STATUS\G" 2>/dev/null | grep -E 'Replica_IO_Running|Source_Host'

Recovery: After verifying the correct new primary IP, update mysql_servers and reload: LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;. For HAProxy, use set server pg_primary/primary addr <new_ip> via the admin socket, then update haproxy.cfg to survive restarts.

5. PgBouncer statement mode breaking application logic

Root cause: Application uses advisory locks (pg_advisory_lock), SET LOCAL, or multi-statement transactions — all of which require a persistent backend connection for the duration of the session. PgBouncer statement mode returns the connection to the pool after each statement, destroying that session-level state.

Diagnosis: Application errors like ERROR: prepared statement "s1" does not exist or advisory lock acquisition silently returning without blocking are the tell.

Recovery: Switch the affected PgBouncer pool to pool_mode = transaction. This holds the backend connection for the entire client transaction rather than per-statement. For read-only analytics queries, keep a separate statement-mode pool to preserve multiplexing density.

Operational Checklist

• Verify SELECT ... FOR UPDATE routes to the primary, not replicas (run EXPLAIN on a test query via proxy and confirm hostgroup)
• Confirm BEGIN causes session pinning: execute BEGIN; SELECT 1; COMMIT; via proxy and check query digest for hostgroup=10
• Inject artificial replication lag (MySQL: STOP REPLICA SQL_THREAD for 30 s) and verify proxy stops routing reads to the lagging replica
• Simulate all replicas failing health checks and verify 100% of read traffic falls back to primary
• Confirm prepared statements (PREPARE p1 AS SELECT ...) resolve correctly — no does not exist errors under connection churn
• Validate SAVE ... TO DISK was run after all rule changes (restart ProxySQL and verify rules survive)
• Export per-hostgroup connection and query metrics to Prometheus; verify dashboards show split correctly
• Confirm fall/rise thresholds match your replication lag tolerance (too aggressive causes healthy replicas to cycle)
• Document primary-fallback behavior in the runbook; alert on-call when read hostgroup drops to 0 active servers

Related Pages

• How to implement read/write splitting in Spring Data JPA — AbstractRoutingDataSource configuration with @Transactional(readOnly) routing and HikariCP pool setup for primary and replica endpoints.

FAQ

Related

← Back to Connection Routing & Pooling Strategies

• Connection Pool Architecture for Read Replicas — sizing, multiplexing modes, and pool exhaustion patterns that directly interact with proxy hostgroup capacity.
• ORM Middleware for Automatic Query Routing — application-layer alternative to proxy splitting, with trade-offs on consistency guarantees and infrastructure coupling.
• Fallback Strategies When Replicas Fall Behind — the degraded-state logic that determines whether reads reroute to the primary or return errors when replication lag exceeds thresholds.
• Avoiding Connection Exhaustion During Replica Failover — what happens to in-flight proxy connections when a replica disappears from the hostgroup.
• Managing Sticky Sessions in Distributed Database Reads — session affinity patterns that complement proxy-layer splitting for workloads requiring read-your-writes guarantees.