Home / Databases / PostgreSQL

DatabasesHow-toIntermediate

Transformation: putting PgBouncer in front of PostgreSQL to end connection storms

Hundreds or thousands of short-lived application connections waste PostgreSQL memory and CPU. Add PgBouncer in transaction pooling mode, size the pools, find the session features that break, and roll it out without an outage.

Deepak Bhardwaj4 min read

Applies toPostgreSQL 13–17, PgBouncer 1.21+ (self-managed); built-in PgBouncer on Azure Flexible Server and RDS Proxy as managed alternatives

Why pool

Each PostgreSQL connection is a process with its own memory. 2,000 mostly idle connections from autoscaled app pods cost RAM and make the server slower even when they do nothing. A pooler keeps a few dozen real server connections and shares them.

Signs you need it:

  • max_connections raised again and again; "too many clients" errors.
  • SELECT count(*), state FROM pg_stat_activity GROUP BY state; shows mostly idle.
  • Spikes of connection setup when apps scale out (serverless, Kubernetes).

1. Pooling modes

Mode Server connection held for Compatible with
session Whole client session Everything, but little saving
transaction One transaction Most apps; session features break (see below)
statement One statement Autocommit-only workloads

Use transaction mode for most apps.

2. Install and configure

; /etc/pgbouncer/pgbouncer.ini
[databases]
app = host=10.0.2.10 port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; or auth_query for looking up passwords in PostgreSQL
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 10
server_idle_timeout = 300
max_prepared_statements = 200      ; protocol-level prepared statements in transaction mode (1.21+)

Pool size: total server connections ≈ number of databases × users × default_pool_size. Start with roughly 2–4× the number of CPU cores on the database server for the busiest pool, and measure.

3. What breaks in transaction mode

Feature Problem Fix
SET / SET search_path outside a transaction Leaks to other clients or is lost SET LOCAL inside the transaction, or set at role/database level
Session advisory locks Released on another connection or never Use transaction-level advisory locks
LISTEN/NOTIFY Needs a dedicated session Direct connection for listeners
Temporary tables across transactions Disappear / visible to others Create and drop within one transaction
SQL-level PREPARE Not tracked Use protocol-level prepared statements (driver) with max_prepared_statements
WITH HOLD cursors Session-bound Avoid or use session pool

Search the app code for SET , pg_advisory_lock, LISTEN, CREATE TEMP.

4. Roll out without an outage

  1. Deploy PgBouncer (two instances behind a load balancer or one per app host/sidecar).
  2. Point one service or a canary at port 6432; watch errors.
  3. Reduce application-side pool sizes (each app pod doesn't need 50 connections anymore).
  4. Move remaining services.
  5. Lower max_connections on PostgreSQL afterwards, which frees memory for shared_buffers/work_mem.

5. Monitor

psql -h pgb01 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;" -c "SHOW STATS;"
Metric Watch for
cl_waiting Clients waiting for a server connection, so the pool is too small or queries are too slow
maxwait Longest wait in seconds
sv_active vs pool size Saturation
avg_query_time / avg_xact_time Slow transactions hold connections

Export with a PgBouncer Prometheus exporter for dashboards.

Managed alternatives

Platform Option
Azure Database for PostgreSQL Flexible Built-in PgBouncer (server parameter pgbouncer.enabled), port 6432
Amazon RDS/Aurora RDS Proxy (also helps with failover)
Kubernetes PgBouncer as a deployment, or the operator's pooler (e.g. CloudNativePG Pooler)

Tip

Long transactions are the enemy of pooling. One BEGIN left open by a buggy code path holds a server connection until it ends. Set idle_in_transaction_session_timeout on the server.

Checklist

  • Transaction mode chosen; session features found and fixed
  • Pool sizes set from measurements
  • Canary rollout, then all services
  • App pool sizes and server max_connections reduced
  • SHOW POOLS metrics monitored

Related fixes