Configuring PgBouncer for Read-Only Connection Pools

1. Architectural Prerequisites & Topology Validation

Verify PostgreSQL Replica State

Before introducing PgBouncer into the read path, confirm that all target replicas are operating in hot_standby mode and are fully synchronized with the primary. Asynchronous lag or paused WAL replay will cause stale reads and application-level consistency violations.

Validate replica readiness and streaming status:

# Verify connectivity and standby mode acceptance
pg_isready -h <replica_host> -p 5432

# Confirm replication state on the replica
psql -h <replica_host> -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"

Ensure postgresql.conf on all replicas enforces:

hot_standby = on
wal_level = replica
max_standby_streaming_delay = 30s

Confirm Read-Only Transaction Defaults

Applications must not assume implicit read-only isolation when connecting through a proxy. PostgreSQL defaults to default_transaction_read_only = off. Without explicit enforcement at the proxy or session level, write-capable roles will silently route INSERT/UPDATE statements to the replica, triggering immediate ERROR: cannot execute INSERT in a read-only transaction failures.

Align with Existing Pooling Topology

Understand how Connection Routing & Pooling Strategies handle endpoint abstraction before deployment. Misaligned DNS TTLs, overlapping VIPs, or dual-proxy configurations will create routing loops or split-brain connection states during initial rollout. Map application connection strings explicitly to the new PgBouncer read endpoint prior to cutover.

2. PgBouncer Configuration for Read-Only Routing

Database Mapping in pgbouncer.ini

Define a dedicated read-only alias in the [databases] section. This alias must resolve strictly to replica hostnames or IPs. Do not mix primary endpoints in this mapping.

[databases]
appdb_ro = host=replica1,replica2 port=5432 dbname=appdb

Pool Mode Selection for Read Workloads

For stateless, high-throughput read workloads, pool_mode = transaction is mandatory. session mode pins backend connections to a single client for the duration of the TCP session, causing severe connection exhaustion under modern ORM connection churn. Transaction mode releases the backend connection immediately after the transaction commits or rolls back, maximizing replica utilization.

Connection Limits & Timeout Tuning

Size pool parameters against replica max_connections and expected application concurrency. Reference the Connection Pool Architecture for Read Replicas sizing matrix when calculating default_pool_size.

[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

Failure Mode Warning: Setting default_pool_size higher than (replica_max_connections - superuser_reserved_connections) / active_app_instances will trigger FATAL: too many connections for role or FATAL: remaining connection slots are reserved for non-replication superuser connections.

3. Step-by-Step Configuration Implementation

Step 1: Define Read-Only Database Alias

Append the [databases] mapping to /etc/pgbouncer/pgbouncer.ini. Ensure the host parameter uses comma-separated replica IPs for round-robin load distribution. Avoid using DNS hostnames in production to prevent resolver-induced latency spikes during failover.

Step 2: Configure Authentication File

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

# /etc/pgbouncer/userlist.txt
"app_read_user" "md5"
"reporting_svc" "md5"

Set file permissions strictly: chmod 600 /etc/pgbouncer/userlist.txt && chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt.

Step 3: Apply Session-Level Read Enforcement

Enforce read-only isolation at the proxy layer to prevent accidental writes from misconfigured ORMs or ad-hoc query tools. Add the following to the [pgbouncer] section:

server_reset_query = SET default_transaction_read_only = ON; DISCARD ALL;

DISCARD ALL is non-negotiable. It clears prepared statements, session variables, and temporary tables, preventing state leakage between pooled connections.

Step 4: Reload & Validate

Apply configuration without dropping active client connections:

kill -HUP $(pgrep pgbouncer)

Verify the reload succeeded:

tail -f /var/log/pgbouncer/pgbouncer.log | grep "reloading config"

4. Symptom Identification & Root Cause Analysis

Symptom: Unexpected Write Failures on Read Pools

Indicators: Application logs show ERROR: cannot execute INSERT/UPDATE/DELETE in a read-only transaction. Root Cause: Missing server_reset_query enforcement, or an application connection pool bypassing PgBouncer and connecting directly to the replica with a superuser role that overrides default_transaction_read_only.

Symptom: Connection Starvation & Queue Buildup

Indicators: SHOW POOLS reveals waiting values consistently > 0, active at default_pool_size, and rising client_wait_time. Root Cause: pool_mode = session holding backend connections hostage during long-lived idle periods, or server_lifetime/server_idle_timeout misconfigured, causing premature backend disconnects and connection thrashing.

Root Cause: Missing Session Reset or Misaligned Pool Mode

Indicators: pg_stat_activity shows state = 'idle in transaction' or state = 'active' with query containing SET or PREPARE statements lingering across unrelated client queries. Root Cause: Omission of DISCARD ALL in server_reset_query. PgBouncer reuses the backend connection, carrying over uncommitted transaction states or session-level GUCs from the previous client.

5. Mitigation & Validation Runbook

Immediate Mitigation Steps

  1. Verify server_reset_query contains both SET default_transaction_read_only = ON; and DISCARD ALL;.
  2. If pool_mode = session was accidentally deployed, switch to transaction and issue RELOAD.
  3. Monitor PgBouncer logs for LOG: pooler error or FATAL: too many connections.

Query Routing Validation

Execute the following through the PgBouncer read endpoint to confirm routing and isolation:

-- Must return 't' (true)
SELECT pg_is_in_recovery();

-- Must return 'on'
SHOW default_transaction_read_only;

-- Verify connection routing
SELECT inet_server_addr() AS backend_ip, current_user;

Performance Baseline Verification

Cross-reference PgBouncer pool metrics against replica capacity:

-- PgBouncer Admin Console
SHOW POOLS;
-- Verify: active <= default_pool_size, waiting = 0, server_idle_timeout respected

-- PostgreSQL Replica
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
SELECT client_addr, state, wait_event_type FROM pg_stat_activity WHERE state = 'idle in transaction';

Monitor replication lag to ensure read throughput does not outpace WAL replay:

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds;

6. Rollback Procedure & Safety Nets

Configuration Reversion

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

cd /etc/pgbouncer/
git checkout HEAD -- pgbouncer.ini userlist.txt

Graceful Connection Drain

Apply the reverted configuration and drain stale connections safely:

# Reload config
kill -HUP $(pgrep pgbouncer)

# If immediate drain is required, use resume mode to stop accepting new clients while finishing active transactions
pgbouncer -R /etc/pgbouncer/pgbouncer.ini
# Wait for active connections to drop, then issue SHUTDOWN if necessary

Primary Routing Restoration

Temporarily bypass the read pool by updating application connection strings to point directly to the primary endpoint. This prevents cascading timeouts while the replica pool stabilizes.

  1. Update environment variables/secret manager: DB_HOST=<primary_host>
  2. Deploy application configuration change.
  3. Verify pg_is_in_recovery() returns f on the new endpoint.
  4. Once replica synchronization and PgBouncer metrics stabilize, restore the read-only routing alias and redeploy.