Choosing a Read Replica Routing Proxy

When you decide to serve reads from replicas, the next question is which component actually decides where each query lands. That decision layer is almost always a proxy or pooler, and the market is confusingly full of tools that all call themselves “proxies” while doing fundamentally different jobs. PgBouncer multiplexes connections but never looks at your SQL. ProxySQL parses every statement and routes on content. HAProxy moves TCP bytes and knows nothing about the protocol on top. Picking the wrong one leaves you either writing to a read-only replica, exhausting connections during failover, or paying for query parsing you never needed. This guide frames the decision space so you can match a tool to your topology within the broader connection routing and pooling strategy for your stack, rather than adopting whatever the last blog post recommended.

Concept Definition and Scope

A “read replica routing proxy” is any component sitting between application connection pools and your database fleet that answers two independent questions: which upstream host serves this connection or query, and how many backend connections does that host actually see. Those two responsibilities — routing and pooling — are orthogonal, and the reason tool selection is hard is that each product solves a different mix of them.

Routing itself decomposes into three sub-problems. Read/write separation decides whether a statement may go to a replica at all. Load distribution picks which replica among the healthy ones. Health gating removes hosts that are down, promoted, or lagging beyond an acceptable window. A tool can be excellent at one and blind to the others. HAProxy distributes load and gates on health superbly but has no concept of read versus write. PgBouncer pools connections brilliantly and gates on almost nothing.

The scope of this page is the proxy tier for read scaling specifically. It does not cover in-process routing, which is handled by ORM middleware for automatic query routing and gives you request-level context a network proxy can never see. Nor does it cover the mechanics of splitting reads from writes once you have chosen a tool — that is the subject of read/write splitting at the proxy layer. Here the goal is narrower: given six candidate tools, understand the axes that separate them and choose.

The six candidates that cover the real decision space are PgBouncer, pgpool-II, and PgCat on the PostgreSQL side; ProxySQL on the MySQL side (with partial PostgreSQL support); HAProxy as a protocol-agnostic L4/L7 balancer; and RDS Proxy as the managed option for AWS-hosted engines.

Mechanism Deep-Dive

The single most important axis is whether the proxy parses SQL. A parsing proxy reads the wire protocol, extracts the statement text, and applies rules to it before choosing an upstream. A non-parsing proxy makes its decision purely from the connection — the destination port, the client IP, or a load-balancing algorithm — and forwards bytes without understanding them.

This distinction cascades into everything else. Only a parsing proxy can send SELECT to a replica and UPDATE to the primary over a single client connection, because only it can tell them apart. A non-parsing proxy forces the application to make that separation upstream, typically by opening two connections to two different ports. The decision tree below walks the axes in the order that eliminates candidates fastest.

The second axis is pooling mode. PgBouncer, PgCat, pgpool-II, and RDS Proxy all multiplex client connections onto a smaller backend pool, which is essential for PostgreSQL where each backend connection is a full OS process. Transaction-level pooling is the mode that unlocks real fan-out; the dedicated read-only pool guide covers why session mode starves replicas under ORM idle patterns. ProxySQL pools connections too, but MySQL’s thread-per-connection model makes pooling less existential than on PostgreSQL. HAProxy does not pool at all — it holds one upstream socket per client socket.

The third axis is lag awareness. ProxySQL’s monitor module polls Seconds_Behind_Source and can shun a replica or lower its weight automatically. pgpool-II has delay_threshold for streaming replication. HAProxy can approximate this only through an external agent check script that reports a replica’s lag as a drain weight. PgBouncer has no concept of lag whatsoever. Where lag gating matters most, see how it connects to detecting and handling replication lag in real time.

Trade-Off Comparison Table

The table below is the core of the decision. Read it column by column against your constraints — engine first, then whether you need SQL-based splitting, then lag and failover needs.

Three conclusions fall out of this matrix. First, no PostgreSQL tool combines best-in-class pooling with best-in-class SQL routing in a single mature process — PgBouncer wins pooling, pgpool-II and PgCat add routing but with more moving parts, so many teams run PgBouncer plus an SQL-aware layer or push routing into the application. Second, ProxySQL is uniquely complete on MySQL: it is the only widely deployed tool that parses, pools, and gates on lag in one binary. Third, HAProxy’s protocol blindness is a feature, not a bug — it is the most reliable failover and health-check engine on the list precisely because it makes no assumptions about the payload, which is why it pairs so naturally with PgBouncer.

RDS Proxy deserves a specific caveat: teams frequently adopt it expecting read/write splitting and discover it only pools and pins connections during failover. For read scaling on RDS you still need reader-endpoint routing or an application-side split in front of it.

Configuration Runbook

These snippets are minimal, correct starting points for the three most common topologies. They are not complete production configs; treat them as the routing-relevant core.

PgBouncer: one alias per endpoint (no splitting)

PgBouncer cannot split, so you expose two aliases and let the application choose. Reads target a replica load-balancer VIP, writes target the primary.

[databases]
appdb_rw = host=primary.db.internal port=5432 dbname=appdb
appdb_ro = host=replica-lb.internal port=5432 dbname=appdb

[pgbouncer]
pool_mode = transaction
max_client_conn = 800
default_pool_size = 30
server_reset_query = DISCARD ALL

ProxySQL: parse and route on MySQL

ProxySQL routes by matching the statement digest to a hostgroup. Hostgroup 10 is the writer, 20 is the reader pool. The monitor module shuns readers that exceed the lag threshold.

-- Writers in hg 10, readers in hg 20
INSERT INTO mysql_servers (hostgroup_id, hostname, max_replication_lag)
VALUES (10, 'primary.db.internal', 0), (20, 'replica-a.db.internal', 5), (20, 'replica-b.db.internal', 5);

-- Default: everything to the writer, then carve out reads
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT.*FOR UPDATE', 10, 1),
       (200, 1, '^SELECT', 20, 1);

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

HAProxy: port-based split with a replica health check

HAProxy cannot read SQL, so the application connects to port 5433 for reads and 5432 for writes. An agent-check script on each replica reports pg_is_in_recovery() state so a promoted node is drained from the read backend automatically.

listen pg_write
    bind *:5432
    option httpchk GET /primary
    server primary primary.db.internal:5432 check port 8008

listen pg_read
    bind *:5433
    balance roundrobin
    option httpchk GET /replica
    server replica_a replica-a.db.internal:5432 check port 8008
    server replica_b replica-b.db.internal:5432 check port 8008

The httpchk targets a small sidecar (Patroni’s REST API or a custom xinetd service) that returns 200 only when the node is in the expected role. This is the mechanism compared in depth in the HAProxy versus ProxySQL guide.

Monitoring and Alerting Signals

Whichever proxy you choose, instrument the routing tier as a first-class component — a proxy that silently sends reads to the primary is invisible until the primary saturates.

• Routing distribution. For ProxySQL, SELECT hostgroup, digest_text, count_star FROM stats_mysql_query_digest ORDER BY count_star DESC shows which hostgroup each query pattern actually hit. A SELECT pattern landing on the writer hostgroup is a misrouted rule.
• Pool saturation. PgBouncer’s SHOW POOLS (cl_waiting, sv_active) and ProxySQL’s stats_mysql_connection_pool reveal backend exhaustion before clients time out.
• Health-check flapping. HAProxy’s show stat on the admin socket exposes per-server check status and the count of transitions. Rapid up/down flapping usually means the check interval is shorter than replica GC or checkpoint pauses.
• Lag at routing time. ProxySQL exposes Seconds_Behind_Source per server in mysql_server_ping_log and the monitor tables; alert when a reader is shunned so you learn about degraded fan-out immediately.

Wire these into the same alerting stack you use for the database. A useful cross-check: compare proxy-reported read counts to the replica’s pg_stat_statements calls — a large gap means connections are bypassing the proxy entirely.

Failure Modes and Recovery Steps

1. Reads silently served by the primary

Cause: a non-parsing proxy (PgBouncer, HAProxy) with a misconfigured alias or port, or a ProxySQL rule that fails to match and falls through to the default writer hostgroup.

Recovery:

1. Confirm where reads land: SELECT pg_is_in_recovery() through the read path must return t.
2. For ProxySQL, inspect stats_mysql_query_digest for SELECT patterns on the writer hostgroup and add or reorder rules.
3. For port-based setups, verify the application’s read connection string points at the reader port, not the writer.

2. Promoted replica keeps receiving writes

Cause: after failover, the old primary is gone but the proxy still routes writes to a former replica that has not been promoted, or health checks did not update role state.

Recovery:

1. Ensure the health check queries actual role state (pg_is_in_recovery() / read_only variable), not just TCP reachability.
2. For HAProxy, confirm the agent-check drains the old primary from the write backend within one check interval.
3. Reduce check inter to a value below your failover RTO so stale role state cannot outlast the promotion.

3. Connection exhaustion during failover storms

Cause: every client reconnects simultaneously when a node drops, and a non-pooling proxy passes the full storm to the surviving nodes.

Recovery:

1. Front the fleet with a pooling layer so reconnect storms hit a bounded backend pool. See avoiding connection exhaustion during replica failover.
2. Set reserve_pool_size in PgBouncer to absorb bursts.
3. Cap client-side reconnect concurrency with jittered backoff.

4. Stale reads despite a “lag-aware” proxy

Cause: the proxy’s lag threshold bounds fleet-wide staleness but does not enforce read-your-writes for a session that just wrote.

Recovery:

1. Add session-level LSN/GTID tracking in the application or ORM middleware.
2. Pin the immediate post-write read to the primary, as in sticky session routing.
3. Tighten the proxy lag threshold only to bound the worst case, not to fix causal consistency.

Guides in This Section

Two focused head-to-head comparisons drill into the pairings most teams actually weigh.

PgBouncer vs ProxySQL for Read Replica Routing Why these two are not really substitutes: PgBouncer is a PostgreSQL connection pooler with no read/write splitting, while ProxySQL is a MySQL query router with regex rules, hostgroups, and lag-aware routing. Covers when each fits, the hybrid PostgreSQL topology, and config for both.

HAProxy vs ProxySQL for Read/Write Splitting The split-by-port versus split-by-SQL trade-off. HAProxy separates traffic by backend and relies on the application to send writes and reads to different ports, with health checks driven by pg_is_in_recovery(); ProxySQL parses statements and routes them itself. Compares latency, failover, and operational burden with full configs.

FAQ

Related

← Back to Connection Routing & Pooling Strategies

• Implementing Read/Write Splitting at the Proxy Layer — the mechanics of separating reads from writes once you have picked a tool from this comparison.
• Connection Pool Architecture for Read Replicas — pool sizing and lifecycle that determine how many backend connections each proxy should hold.
• ORM Middleware for Automatic Query Routing — the in-process alternative that sees request context a network proxy cannot.
• Detecting and Handling Replication Lag in Real Time — the lag telemetry that lag-aware proxies consume to gate reader selection.
• Managing Sticky Sessions in Distributed Database Reads — session-affinity patterns that complement proxy routing when causal consistency matters.