pgpool-II vs PgBouncer for PostgreSQL Read Scaling

Problem statement: you need a PostgreSQL proxy in front of a primary and several replicas, and the two obvious candidates turn out to solve almost disjoint problems.

The comparison is frequently framed as “which proxy is better”, which has no answer, because pgpool-II load-balances reads and PgBouncer pools connections and neither does much of the other’s job. This page establishes which problem you actually have and what follows. It sits under choosing a read replica routing proxy.

Symptom Identification

The two problems have distinct signatures, and it is worth being sure which one you have.

A pooling problem looks like:

  • FATAL: sorry, too many clients already or remaining connection slots are reserved.
  • pg_stat_activity full of idle connections that are not doing anything.
  • Connection counts scaling with application instances rather than with request rate.
  • High CPU on the database attributable to connection setup and process management.

A routing problem looks like:

  • The primary’s CPU dominated by SELECT, while replicas are underused.
  • Adding replicas produced no relief on the primary.
  • The application has a single connection string and no way to express “this is a read”.

They are independent: you can have both, either, or neither. Solving the wrong one produces no improvement and a new component to operate.

Root Cause Analysis

PgBouncer is a connection pooler and deliberately not a router. It sits between clients and one PostgreSQL target per database entry, multiplexing many client connections onto few server connections. In transaction pooling mode a server connection is returned to the pool at every transaction boundary, which is what lets thousands of clients share tens of backends. It does not parse SQL, so it cannot know whether a statement is a read.

That does not mean PgBouncer cannot support read scaling — it means the decision has to come from somewhere else. The standard pattern is two pool entries pointing at different hosts, with the application choosing which to connect to. Routing lives in the application; pooling lives in PgBouncer.

pgpool-II is a router that also pools. It parses each statement, classifies it, and load-balances reads across configured standbys according to weights. This is genuine read/write splitting with no application change, which is exactly what teams want when the application cannot be modified.

Its costs follow from the parsing. Every statement is interpreted, which adds latency proportional to complexity and brings the same classification edge cases every SQL-aware router has — data-modifying CTEs, SELECT ... FOR UPDATE, volatile functions, statements inside transactions. And pgpool-II’s process-per-connection model makes it a weaker pooler than PgBouncer at high client counts.

So the tools are complementary rather than competing. pgpool-II in front decides where; PgBouncer behind each node decides how many connections. The cost is two components in the path and two places to look during an incident.

Three topologies and where each decision is made Three arrangements of application, proxies and databases. In the first, the application chooses a read or write pool and PgBouncer pools each; routing is an application decision. In the second, pgpool-II parses each statement and load-balances reads across standbys with no application change, but its pooling compresses connections less effectively. In the third, pgpool-II routes and a PgBouncer in front of each database node pools, giving both properties at the cost of two components in the path. PgBouncer only — the app routes app pgb: write pgb: read primary replica + best pooling − app must know about replicas pgpool-II only — the proxy routes app pgpool-II parses SQL primary replica + no app change − weaker pooling, parser edge cases combined — routing and pooling app pgpool-II pgb pgb primary replica + both properties − two components in the path The choice is not which proxy is better; it is which decision you want made where. If the application can be changed, the first row is the simplest thing that works.

Trade-off Comparison

Capability PgBouncer pgpool-II
Connection pooling Excellent — transaction mode compresses thousands of clients to tens of backends Present, weaker — process-per-connection limits compression
SQL-aware read/write split None — does not parse statements Yes, with configurable classification
Load balancing across replicas None Yes, weighted per backend
Lag-aware routing No Yes, via delay_threshold
Automatic failover No Yes — overlaps with an HA cluster manager
Query caching No Yes, in-memory result cache
Latency added Very low — forwards bytes Low but higher; scales with statement complexity
Memory per client connection Very small Larger — a process per connection
Operational surface Small, one config file Large, many interacting features
Typical role The pooling tier The routing tier

The last row is the practical summary. Choosing between them as alternatives is a category error in most deployments; choosing which decisions you want made where is the real question, and it usually resolves to PgBouncer as a pooler with routing decided either in the application or by a router in front.

Step-by-Step Resolution

Step 1 — Identify which problem you actually have

sql
-- Pooling problem: are connections the constraint?
SELECT count(*)                                            AS total,
       count(*) FILTER (WHERE state = 'idle')              AS idle,
       current_setting('max_connections')::int             AS max_conns,
       round(100.0 * count(*) /
             current_setting('max_connections')::int)      AS pct_used
  FROM pg_stat_activity WHERE backend_type = 'client backend';

-- Routing problem: is the primary doing read work?
SELECT round(100 * sum(total_exec_time) FILTER (WHERE query ~* '^\s*select')
             / nullif(sum(total_exec_time), 0)) AS pct_time_in_selects
  FROM pg_stat_statements;

Inline verification: high pct_used with a high idle count is a pooling problem; high pct_time_in_selects on the primary is a routing problem. They are independent.

Step 2 — Check whether the application can route itself

python
# If the application can express "this is a read", PgBouncer alone suffices
# and you avoid a SQL-parsing component entirely.
READ_DSN  = "postgresql://app@pgbouncer:6432/app_read"
WRITE_DSN = "postgresql://app@pgbouncer:6432/app_write"

Modern drivers and ORMs generally can — the patterns are in ORM middleware for automatic query routing. pgpool-II’s core value is for applications that cannot be changed.

Inline verification: a test connecting to each DSN reports the expected pg_is_in_recovery() value.

Step 3 — Configure the tool you chose

ini
# pgbouncer.ini — two pool entries, two targets, no parsing.
[databases]
app_write = host=primary.internal  port=5432 dbname=app pool_size=20
app_read  = host=replica-1.internal port=5432 dbname=app pool_size=50

[pgbouncer]
pool_mode = transaction          # what makes the compression possible
max_client_conn = 2000           # sized against the APP fleet
default_pool_size = 20           # sized against the DATABASE
server_lifetime = 300            # recycle so the fleet re-balances
server_check_query = SELECT 1
server_tls_sslmode = require
ini
# pgpool.conf — SQL-aware routing across a primary and two standbys.
backend_hostname0 = 'primary.internal'
backend_port0 = 5432
backend_weight0 = 0              # 0 = writes only; no reads land here
backend_flag0 = 'ALWAYS_PRIMARY'

backend_hostname1 = 'replica-1.internal'
backend_weight1 = 1
backend_hostname2 = 'replica-2.internal'
backend_weight2 = 1

load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

# Lag-aware: a standby further behind than this receives no reads.
sr_check_period = 5
delay_threshold = 10000000       # bytes of WAL, not seconds

# Leave failover to the HA cluster manager — two authorities is a split-brain risk.
failover_on_backend_error = off

Inline verification: SHOW POOLS; on PgBouncer shows sv_active well below cl_active; SHOW POOL_NODES; on pgpool-II shows a non-zero select_cnt on each standby.

Step 4 — If combining, put pgpool-II in front

text
app  →  pgpool-II (routes)  →  PgBouncer per node (pools)  →  PostgreSQL
ini
# pgpool points at the PgBouncers, not at the databases directly.
backend_hostname0 = 'pgb-primary.internal'
backend_port0 = 6432
backend_hostname1 = 'pgb-replica-1.internal'
backend_port1 = 6432

Inline verification: on each database node, SELECT count(*) FROM pg_stat_activity shows a small, stable number — the PgBouncer pool size — rather than a number tracking application instances.

Step 5 — Verify routing and pooling separately

sql
-- Routing: ask the server, not the proxy.
SELECT pg_is_in_recovery();                 -- t on a replica

-- Pooling: compression ratio, measured at the database.
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';

Inline verification: reads report t, writes report f, and backend connection counts are bounded by pool size rather than by client count.

Configuration Snippet

ini
# pgbouncer.ini — a production baseline with the settings that matter most.
[databases]
app_write = host=primary.internal  port=5432 dbname=app pool_size=20
app_read  = host=replica-1.internal port=5432 dbname=app pool_size=50

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

# Transaction mode is the point of PgBouncer. Session mode gives up nearly
# all the compression and should only be used if the app needs session state
# — advisory locks, LISTEN/NOTIFY, or SET without SET LOCAL.
pool_mode = transaction

# Two ceilings with different jobs:
#   max_client_conn   — sized against the APPLICATION fleet
#   default_pool_size — sized against the DATABASE's max_connections
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3

# Recycle connections so the read fleet re-balances rather than pinning.
server_lifetime = 300
server_idle_timeout = 60

# Fail fast rather than queueing forever — a fast failure feeds a circuit
# breaker; a slow queue starves one.
query_wait_timeout = 10
client_login_timeout = 5

server_tls_sslmode = require

The two ceilings are the settings most often set to the same value by mistake, and they are answering different questions. max_client_conn exists to absorb far more client connections than the database could hold — that absorption is the product. default_pool_size must respect the database’s own limit, and critically it is per pool per PgBouncer instance: three PgBouncers each configured for 20 will open 60 backends against one replica, so the sum is what must fit under max_connections.

query_wait_timeout is the setting that decides how a saturated pool behaves. Left at zero, clients queue indefinitely and a slow replica turns into unbounded latency across the whole fleet. Set to a few seconds, an overloaded pool produces prompt errors that a circuit breaker can act on — the reasoning in avoiding connection exhaustion during replica failover.

What transaction-mode pooling actually buys On the left, two thousand client connections arrive at PgBouncer. In transaction pooling mode they are multiplexed onto twenty server connections, because a server connection is returned to the pool at every transaction boundary. In session pooling mode the same two thousand clients would each hold a server connection for their whole session, far exceeding a typical max_connections limit. The compression ratio is the reason to run a pooler at all. 2 000 client connections from the application fleet … ×2 000 PgBouncer pool_mode = transaction 20 backend connections 100:1 compression the same clients in session mode PgBouncer pool_mode = session 2 000 backends — over max_connections Session mode gives up nearly all the benefit. Use it only when the application genuinely needs session state. The sum of pool_size across every PgBouncer instance is what must fit under the database's max_connections.

Verification and Rollback

Confirm the pooler is compressing:

sql
-- On the database. Should be bounded by pool size, not by client count.
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';
bash
# On PgBouncer: cl_active is clients, sv_active is backends. The ratio is
# the compression you are getting.
psql -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

Confirm pgpool-II is routing, not just forwarding:

bash
psql -h pgpool -p 9999 -c "SHOW POOL_NODES;"
# select_cnt should be non-zero and roughly weighted across the standbys.
# All zeros on the standbys means load_balance_mode is off, or every
# statement is being classified as a write.

Rollback. Both are removable from the path without data risk, and the order matters:

ini
# PgBouncer: point the application back at the database directly.
# Ensure max_connections can absorb the uncompressed client count FIRST,
# or removing the pooler causes the outage it was preventing.

# pgpool-II: point the application at the primary directly. All reads move
# to the primary, so confirm it has the headroom before doing this.

Removing a pooler is the more dangerous of the two, because the connection compression it provided disappears instantly and the database may not be able to accept the resulting client count. Removing a router is safer but concentrates all reads on the primary — verify headroom on whichever node inherits the load before either change.

Edge Cases and Gotchas

Transaction pooling breaks session-scoped features

In transaction mode a client’s statements may run on different backends between transactions, so anything relying on session state breaks: advisory locks, LISTEN/NOTIFY, SET without SET LOCAL, WITH HOLD cursors, and server-side prepared statements without max_prepared_statements configured. Audit for these before switching a pool to transaction mode — the failures are intermittent and confusing.

pgpool-II’s delay_threshold is in bytes, not seconds

It compares WAL byte positions, so its value does not correspond to a time budget in any stable way — the same byte lag is milliseconds on a quiet cluster and seconds during a write burst. If your freshness requirement is expressed in time, this is a poor fit and an application-level or agent-based check is more accurate. The distinction matters for anything relying on routing queries based on data freshness requirements.

Running two failover authorities is worse than one

pgpool-II can perform failover, and so can Patroni or repmgr. Enabling both means two independent systems can decide to promote, which is a split-brain risk rather than redundancy. Choose one authority; if an HA cluster manager is already in place, set failover_on_backend_error = off and let pgpool-II consume the topology rather than change it — the reasoning is in preventing split-brain during automated replica promotion.

FAQ

Can PgBouncer do read/write splitting?

Not by inspecting SQL — it does not parse statements. What it can do is expose separate pool names or ports pointing at different hosts, so the application chooses the read path by connecting to a different target. That is a real and widely used pattern, but the routing decision lives in the application, not in PgBouncer.

Does pgpool-II replace PgBouncer?

Only partially. pgpool-II includes connection pooling, but its per-process model is less effective at compressing thousands of client connections into a small number of backend ones than PgBouncer’s transaction pooling. Many deployments run pgpool-II for routing with PgBouncer behind it for pooling, precisely because each tool’s strength is the other’s weakness.

Is pgpool-II’s automatic failover worth using?

It works, and it overlaps with what a dedicated HA cluster manager does better. Running it alongside Patroni or repmgr means two systems can decide to promote, which is a split-brain risk rather than redundancy. Pick one authority for promotion; if you already run an HA cluster manager, disable pgpool-II’s failover.

Which adds more latency?

pgpool-II, because it parses every statement to decide where it goes, while PgBouncer forwards bytes without interpreting them. In absolute terms both are small — well under a millisecond for typical statements — but pgpool-II’s cost scales with statement complexity and its process-per-connection model makes it heavier under high connection counts.

← Back to Choosing a Read Replica Routing Proxy