PgBouncer vs ProxySQL for Read Replica Routing

Problem statement: You want a single component that both pools connections and sends reads to replicas, you have narrowed it to PgBouncer or ProxySQL, and you need to know which one actually does what you think — because these two tools are not substitutes for each other and choosing on reputation alone leads to a topology that cannot do the job.

Symptom Identification

The confusion usually surfaces as one of these production observations:

• You deployed PgBouncer expecting it to “send reads to the replica” and every SELECT still lands on the primary. SELECT pg_is_in_recovery() through the pool returns f.
• You tried to configure ProxySQL in front of PostgreSQL and hit protocol errors during handshake, or query rules that never match because the digests differ from MySQL’s.
• You have PgBouncer pooling correctly but no way to exclude a lagging replica — there is no lag setting anywhere in pgbouncer.ini.
• Someone proposed “just add ProxySQL” to a Postgres stack and the team cannot find a hostgroup concept that maps cleanly onto streaming replicas.

Each of these is the same underlying mismatch: treating a connection pooler and a query router as interchangeable. They occupy different layers of the connection routing and pooling strategy and solve different halves of the problem.

Root Cause Analysis

PgBouncer is a connection pooler, not a router. Its entire job is to multiplex many client connections onto a small number of backend connections so PostgreSQL — where each backend is a heavyweight OS process — does not run out of connection slots. It reads just enough of the protocol to manage authentication and transaction boundaries. It never parses the statement body, so it has no basis on which to decide read versus write. A PgBouncer [databases] entry maps one alias to exactly one host. There is no “if SELECT” branch anywhere in its model.

ProxySQL is a query router that happens to also pool. Built for the MySQL wire protocol, it parses every incoming statement, computes a digest, and matches that digest against an ordered list of mysql_query_rules. Each rule can send matching traffic to a hostgroup — a named set of backends. A writer hostgroup holds the primary; a reader hostgroup holds the replicas. Its monitor module continuously polls each backend’s Seconds_Behind_Source and can shun a replica whose lag exceeds max_replication_lag, so routing is lag-aware out of the box. This is a fundamentally richer model than PgBouncer’s, and it is MySQL-shaped.

The consequence: on PostgreSQL, PgBouncer gives you pooling but no splitting; on MySQL, ProxySQL gives you both. They are not competitors — they are answers to different questions on different engines. The deeper survey of that decision space lives in choosing a read replica routing proxy.

Architecture

The diagram contrasts the two data paths. On the left, PgBouncer forwards a connection to a single pre-chosen endpoint — the application already decided read or write by picking an alias. On the right, ProxySQL inspects the SQL and picks the hostgroup itself.

When PgBouncer Fits

Choose PgBouncer when your problem is connection count, not routing. That is the common case on PostgreSQL: hundreds of application workers, each with an idle-heavy pool, overwhelming the primary’s max_connections. PgBouncer in transaction mode collapses that fan-in to a bounded backend pool. If your application (or an ORM router) already knows which queries are reads, you simply expose a read alias pointing at a replica load-balancer and a write alias pointing at the primary. Detailed tuning for the read-only side is in configuring PgBouncer for read-only connection pools.

PgBouncer is also the right answer when you want minimal moving parts and predictable, near-zero per-query overhead. Because it does not parse SQL, it adds essentially no latency to the query path.

What PgBouncer will never do: split reads from writes for you, exclude a lagging replica, or balance across replicas within one alias. Load-balancing across multiple replicas requires a DNS round-robin name or a TCP load balancer as the alias host.

When ProxySQL Fits

Choose ProxySQL when you are on MySQL and want the proxy itself to make routing decisions. Its query rules let you route by table, by comment hint, or by statement shape; its hostgroups model writer and reader pools directly; and its monitor gives you lag-aware reader selection without any application code. This is the tool that most closely matches the intuition of “a proxy that sends my reads to replicas.”

ProxySQL earns its keep when routing policy needs to change without redeploying the application — rules live in a runtime-loadable table, so you can add a rule that pins a hot analytical query to a dedicated replica in seconds. It also pools connections, which matters less on MySQL’s thread-per-connection model than on PostgreSQL but still helps under high churn.

The cost is operational weight: ProxySQL is a stateful, configuration-rich service with its own admin interface, and every query pays a parse-and-match cost. That cost is low but non-zero, and misordered rules are a classic source of silently misrouted reads. See the proxy-layer read/write splitting guide for rule-ordering pitfalls.

The Hybrid PostgreSQL Topology

On PostgreSQL you frequently want both capabilities, and since no single mature tool does both cleanly, you compose them. The standard hybrid: an SQL-aware or port-aware routing layer decides read versus write, and PgBouncer sits behind it purely for pooling.

                 reads  -> HAProxy :5433 -> PgBouncer(_ro) -> replica pool
app  ->  router  writes -> HAProxy :5432 -> PgBouncer(_rw) -> primary

Here the application (or an ORM router) sends reads to one HAProxy port and writes to another. HAProxy health-checks role state and drains a promoted node; PgBouncer behind each port bounds the backend connection count. This gives you read scaling and connection multiplexing, at the price of two components instead of one. The port-based split mechanics are compared against ProxySQL in the HAProxy versus ProxySQL guide.

Configuration Snippet

PgBouncer — two aliases, transaction pooling, one host each:

[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 = 1000
default_pool_size = 40
server_reset_query = DISCARD ALL
server_lifetime = 1800

ProxySQL — writer/reader hostgroups, lag threshold, read rule after the FOR UPDATE carve-out:

INSERT INTO mysql_servers (hostgroup_id, hostname, max_replication_lag)
VALUES (10, 'primary.db.internal', 0),
       (20, 'replica-a.db.internal', 3),
       (20, 'replica-b.db.internal', 3);

-- Locking reads must go to the writer; ordinary SELECTs to readers
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT.*FOR (UPDATE|SHARE)', 10, 1),
       (200, 1, '^SELECT', 20, 1);

-- Monitor credentials so lag polling works
UPDATE global_variables SET variable_value='monitor'
  WHERE variable_name IN ('mysql-monitor_username');

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

Verification and Rollback

Confirm PgBouncer is routing reads to a replica:

-- through the appdb_ro endpoint
SELECT pg_is_in_recovery();   -- must return t
SELECT inet_server_addr();    -- must be a replica IP, not the primary

Confirm ProxySQL is routing SELECTs to the reader hostgroup:

SELECT hostgroup, digest_text, count_star
FROM stats_mysql_query_digest
WHERE digest_text LIKE 'SELECT%'
ORDER BY count_star DESC LIMIT 10;
-- SELECT rows must show hostgroup 20, not 10

To roll back a bad ProxySQL rule without a restart, deactivate it and reload from the admin interface:

UPDATE mysql_query_rules SET active=0 WHERE rule_id=200;
LOAD MYSQL QUERY RULES TO RUNTIME;

For PgBouncer, revert the config file and send HUP; existing clients are not dropped.

Edge Cases and Gotchas

ProxySQL rule ordering silently sends reads to the writer

Rules evaluate in rule_id order and the first match with apply=1 wins. If a broad ^SELECT rule sits before the FOR UPDATE carve-out, locking reads meant for the primary leak to a read-only replica and fail. Always place the most specific rules first, and verify with stats_mysql_query_digest after any change.

PgBouncer transaction mode breaks session-scoped SET

Because transaction pooling releases the backend after each transaction, a SET issued by an ORM at connect time does not persist. On a read replica this bites when drivers set search_path or application_name. Configure those at the role level with ALTER ROLE ... SET, not per session — the same gotcha detailed in the read-only pool guide.

Neither tool gives you read-your-writes on its own

ProxySQL’s lag threshold bounds fleet-wide staleness but does not know that this session just wrote. PgBouncer knows nothing about lag at all. For a user who must see their own write immediately, add LSN/GTID tracking in ORM middleware or pin the post-write read to the primary. Proxy-level lag gating and session-level causal consistency are different guarantees.

FAQ

Related

← Back to Choosing a Read Replica Routing Proxy

• HAProxy vs ProxySQL for Read/Write Splitting — the split-by-port versus split-by-SQL comparison that completes the PostgreSQL hybrid picture.
• Configuring PgBouncer for Read-Only Connection Pools — pool mode, auth, and read enforcement for the PgBouncer read alias used here.
• Implementing Read/Write Splitting at the Proxy Layer — rule ordering and split mechanics for ProxySQL and similar routers.
• ORM Middleware for Automatic Query Routing — where to add the session-level consistency neither proxy provides.
• Connection Routing & Pooling Strategies — the parent section framing pooling versus routing responsibilities.