Configuring PgBouncer for Read-Only Connection Pools

Problem statement: Your application connects to a PostgreSQL replica through PgBouncer, but writes are landing on the replica, sessions are leaking state across clients, or backend connections are exhausted — and you need a step-by-step path to a correct, production-hardened read-only pool configuration.

Symptom Identification

Look for these signals in production before concluding PgBouncer is misconfigured:

• ERROR: cannot execute INSERT in a read-only transaction — writes are reaching the replica. Either default_transaction_read_only is not enforced on the replica, or a service is bypassing PgBouncer and writing directly.
• SHOW POOLS shows waiting > 0 with active pinned at default_pool_size — the pool is saturated. Common cause: pool_mode = session holding backend connections hostage through idle ORM periods.
• Stale query results or state = idle in transaction rows in pg_stat_activity — server_reset_query is missing, letting session state leak between clients.
• FATAL: remaining connection slots are reserved for non-replication superuser connections on the replica — default_pool_size is set too high relative to max_connections on the replica.
• Replication lag spiking while read throughput is nominal — WAL replay is being starved; check max_standby_streaming_delay and replica CPU.

Root Cause Analysis

These failures share three underlying causes:

1. PgBouncer does not enforce read-only semantics by default. It is a connection multiplexer, not a query router. Without explicit default_transaction_read_only = on at the replica level (or at the role level), write-capable application roles will succeed in executing DML on the replica’s connection until the replica itself rejects the write.

2. pool_mode = session is inappropriate for ORM-driven workloads. Session mode pins one backend connection to one client TCP connection for the full lifetime of the session. Modern application servers open connection pools with idle connections that stay alive for minutes — this holds backend connections on the replica even when no queries are running, causing starvation under moderate concurrency. Transaction mode releases the backend connection immediately after each transaction, multiplexing many more clients over the same backend pool.

3. Missing server_reset_query allows state leakage. When PgBouncer reuses a backend connection for a new client, any session state from the previous client — SET overrides, advisory locks, temporary tables, prepared statements — is still present unless DISCARD ALL is issued between uses.

Understanding the connection pool architecture for read replicas and the broader connection routing and pooling strategies for your stack is a prerequisite before sizing these parameters.

Architecture: PgBouncer in the Read Path

The diagram below shows the recommended topology: a dedicated PgBouncer instance fronts one or more replicas, isolated from the primary write path.

Step-by-Step Resolution

Step 1: Validate replica readiness

Before routing any traffic through PgBouncer, confirm all target replicas are in hot_standby mode and streaming WAL without significant lag.

# Confirm the replica accepts connections
pg_isready -h replica-lb.internal -p 5432

# Check standby state and WAL positions
psql -h replica-lb.internal -c "
  SELECT pg_is_in_recovery(),
         pg_last_wal_receive_lsn(),
         pg_last_wal_replay_lsn(),
         EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds;"

Verify these settings in postgresql.conf on every replica before proceeding:

hot_standby = on
wal_level = replica
max_standby_streaming_delay = 30s

If replication lag is above your SLA threshold, do not route traffic to that replica yet.

Inline verification: pg_is_in_recovery() must return t. lag_seconds above 30 in steady state indicates a replica under WAL replay pressure.

Step 2: Define the read-only database alias

Add a dedicated alias in /etc/pgbouncer/pgbouncer.ini. The alias must point only to replica endpoints — never mix primary hostnames in this mapping.

PgBouncer does not natively distribute connections across a comma-separated host list. Use a DNS round-robin name or a TCP load balancer VIP as the host value to distribute load across multiple replicas. This keeps PgBouncer’s config stable when replica IPs change.

[databases]
# Read-only alias — resolves to replica LB only
appdb_ro = host=replica-lb.internal port=5432 dbname=appdb

Inline verification: psql -h 127.0.0.1 -p 6432 -d appdb_ro -U app_read_user -c "SELECT inet_server_addr();" should return a replica IP, not the primary’s.

Step 3: Configure pool mode and connection limits

For stateless, high-throughput read workloads, pool_mode = transaction is the correct choice. It releases the backend connection immediately after each transaction commits or rolls back, multiplexing many more application clients over a bounded set of backend connections.

Size default_pool_size against the replica’s max_connections:

max_safe_pool_size = (replica_max_connections - superuser_reserved_connections) / active_pgbouncer_instances

Setting default_pool_size above this ceiling triggers FATAL: remaining connection slots are reserved for non-replication superuser connections on the replica.

[pgbouncer]
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300
client_idle_timeout = 0
server_lifetime = 3600

For a full sizing reference, see the connection pool architecture for read replicas sizing matrix.

Inline verification: SHOW POOLS; in the PgBouncer admin console should show waiting = 0 under normal load. If waiting > 0 persists, default_pool_size is too low or server_idle_timeout is causing premature disconnections.

Step 4: Configure authentication for read-only roles

Create a dedicated userlist.txt containing only read-only PostgreSQL roles. Never reuse primary write credentials in this file.

Generate SCRAM-SHA-256 hashes from the primary:

SELECT rolpassword FROM pg_authid WHERE rolname = 'app_read_user';

Write the result to /etc/pgbouncer/userlist.txt:

"app_read_user" "SCRAM-SHA-256$:$:"
"reporting_svc" "SCRAM-SHA-256$:$:"

For legacy MD5 authentication: "username" "md5<hash>".

Restrict file permissions — PgBouncer will refuse to start if userlist.txt is world-readable:

chmod 600 /etc/pgbouncer/userlist.txt
chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt

Inline verification: Attempt a connection as a role that does NOT appear in userlist.txt — it must fail with FATAL: password authentication failed.

Step 5: Enforce read-only isolation at the replica and proxy layer

Two complementary controls are needed — neither alone is sufficient.

On the replica (postgresql.conf):

-- Enforce globally on the replica
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

Setting this at the replica level is the authoritative control. It catches connections that bypass PgBouncer entirely (direct psql, monitoring agents, misconfigured ORMs).

On PgBouncer (for session state hygiene):

server_reset_query = DISCARD ALL

DISCARD ALL clears prepared statements, session variables, temporary tables, and advisory locks between client sessions. Do not attempt to enforce default_transaction_read_only inside server_reset_query via SET — DISCARD ALL executes last and would undo it. Use the postgresql.conf approach instead.

Inline verification:

-- Through the PgBouncer endpoint — both must pass
SELECT pg_is_in_recovery();           -- must return t
SHOW default_transaction_read_only;   -- must return on

Step 6: Reload and validate end-to-end

Apply the configuration without dropping active client connections:

kill -HUP $(pgrep pgbouncer)

# Confirm reload
tail -n 20 /var/log/pgbouncer/pgbouncer.log | grep "reloading config"

Run the full validation suite through the PgBouncer read endpoint:

-- Confirms you are connected to a replica
SELECT pg_is_in_recovery();

-- Confirms read-only enforcement is active
SHOW default_transaction_read_only;

-- Confirms which replica is serving the connection
SELECT inet_server_addr() AS backend_ip, current_user;

-- Confirm a write is correctly rejected
INSERT INTO pg_temp.test_write (id) VALUES (1);
-- Expected: ERROR: cannot execute INSERT in a read-only transaction

Check pool health in the PgBouncer admin console (connect to the pgbouncer database on port 6432):

SHOW POOLS;
-- active <= default_pool_size, waiting = 0, server_idle should be < default_pool_size

SHOW STATS;
-- avg_query_time rising sharply indicates replica CPU contention

Configuration Snippet

Minimal production-ready pgbouncer.ini for a dedicated read pool:

[databases]
# Read-only alias — DNS name or LB VIP resolving to replicas only
appdb_ro = host=replica-lb.internal port=5432 dbname=appdb

[pgbouncer]
# Transaction mode: releases backend connection after every txn
pool_mode = transaction

# Auth file — read-only roles only, chmod 600
auth_file = /etc/pgbouncer/userlist.txt
auth_type = scram-sha-256

# Pool sizing — keep below replica max_connections budget
max_client_conn = 500
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

# Connection hygiene
server_reset_query = DISCARD ALL   ; clears state between clients
server_idle_timeout = 300           ; reclaim idle backends after 5 min
server_lifetime = 3600              ; force-recycle backends hourly
client_idle_timeout = 0             ; let app decide client timeout

# Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

listen_port = 6432
listen_addr = *

# Admin access (restrict in production)
admin_users = pgbouncer_admin

Verification and Rollback

Confirm the fix is working

-- Monitor pool saturation in real time
SHOW POOLS;

-- Monitor actual backend connections on the replica
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Confirm no idle-in-transaction leak
SELECT client_addr, state, wait_event_type, query_start
FROM pg_stat_activity
WHERE state = 'idle in transaction';

-- Monitor replication lag while load is running
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds;

Revert safely if something goes wrong

If routing instability or connection exhaustion occurs, revert to the last known-good configuration immediately:

cd /etc/pgbouncer/
git checkout HEAD -- pgbouncer.ini userlist.txt
kill -HUP $(pgrep pgbouncer)

For an online restart that hands off sockets without dropping clients, use PgBouncer’s -R flag:

pgbouncer -R /etc/pgbouncer/pgbouncer.ini

The old process exits once existing clients disconnect naturally.

If the replica pool is unrecoverable, temporarily bypass it by routing application connections directly to the primary:

1. Update environment variable: DB_HOST=<primary_host> and DB_NAME=appdb (write alias)
2. Deploy the configuration change.
3. Verify with SELECT pg_is_in_recovery(); — must return f on the primary.
4. Once replica metrics and PgBouncer pool health stabilize, restore the read-only alias and redeploy.

Edge Cases and Gotchas

Cascading replication and hot_standby_feedback

When a replica replicates from another replica (cascading topology), hot_standby_feedback = on on the downstream replica propagates its oldest transaction snapshot back to the upstream. This prevents the upstream from vacuuming rows the downstream still needs, but it can cause table bloat on the primary if the downstream replica falls far behind. If you are pooling connections to a cascading replica, monitor pg_stat_replication on the upstream for slot lag in addition to WAL receive lag on the downstream.

SET statements and transaction mode

In pool_mode = transaction, PgBouncer releases the backend after each transaction, so any SET command applied within a transaction boundary is gone by the next query. This catches ORM drivers that issue SET search_path, SET application_name, or SET timezone at connection time — those settings will not persist between queries. The correct fix is to configure these at the role level (ALTER ROLE app_read_user SET search_path = myschema, public;) or in postgresql.conf. Never rely on session-level SET in transaction pooling mode.

Multi-region deployments and DNS TTL

In multi-region setups, replica-lb.internal may resolve to region-local replicas via split-horizon DNS. PgBouncer resolves the hostname at startup and caches it. If the replica behind the DNS name changes (failover, maintenance), PgBouncer will not re-resolve until the backend connection is recycled. Set server_lifetime short enough (e.g., 600–1800 seconds) to ensure stale cached IPs are periodically flushed. For zero-downtime replica rotation, prefer a VIP or a load balancer with active health checks over raw DNS.

FAQ

Related

← Back to Connection Pool Architecture for Read Replicas

• Avoiding Connection Exhaustion During Replica Failover — PgBouncer limits, circuit-breaker patterns, and TCP keepalive tuning to survive replica promotion events without cascading timeouts.
• Implementing Read/Write Splitting at the Proxy Layer — how proxy-layer tools route SELECT vs DML at the protocol level, and where PgBouncer fits in a split-routing topology.
• Detecting and Handling Replication Lag in Real Time — metrics and query patterns to monitor WAL replay lag on the replicas PgBouncer is pooling to.
• Connection Routing & Pooling Strategies — the parent section covering DNS-based routing, proxy-layer splitting, ORM middleware, and sticky session management.