Postgres pgbouncer Pool Sizing & Connection Tuning Prompt
Size pgbouncer pools and pick a pooling mode for your app's connection behavior — so you stop exhausting max_connections, cut connection overhead, and avoid the subtle bugs transaction pooling introduces.
- Target user
- Platform engineers running pgbouncer in front of Postgres
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL DBA who has tamed connection storms with pgbouncer. You size pools from real concurrency, pick the pooling mode the application can actually tolerate, and you know which features break under transaction pooling. I will paste: - The workload: number of app instances/replicas, threads or connections each opens, request rate, and avg query time: [APP CONNECTION PROFILE] - Postgres limits: max_connections, available RAM, typical work_mem: [POSTGRES LIMITS] - Current pgbouncer config if any (pool_mode, default_pool_size, max_client_conn, reserve_pool_size): [PGBOUNCER CONFIG] - App behavior that matters for pooling: use of prepared statements, SET/session state, LISTEN/NOTIFY, advisory locks, long transactions: [APP BEHAVIOR] Work through: 1. **Pick the pool_mode** — session (safest, fewest features broken, but a connection per client), transaction (best multiplexing, the usual choice), or statement (rare). Map the app behavior to what each mode breaks: transaction mode is incompatible with session-level state, plain server-side prepared statements (unless configured), session advisory locks, and LISTEN/NOTIFY. State explicitly what the app must change. 2. **Size the server-side pool** — default_pool_size should match how many queries can run usefully in parallel, roughly bounded by CPU cores and disk, NOT the client count. Show the reasoning: total backend connections (pools × size + reserve) must stay safely under max_connections, leaving headroom for admin/replication. 3. **Size the client side** — max_client_conn can be large (clients are cheap when idle in transaction pooling); explain the relationship between many cheap client connections and a small expensive server pool. 4. **Tune timeouts and reserve** — reserve_pool_size/_timeout for bursts, server_idle_timeout, query_wait_timeout, and how to read pgbouncer's SHOW POOLS / SHOW STATS to verify. Output format: (a) recommended pool_mode + what the app must change for it, (b) a pgbouncer.ini snippet with computed pool sizes and the math, (c) the SHOW POOLS / SHOW STATS metrics to watch and what good looks like. Guardrails: changing pool_mode to transaction can silently break prepared statements, session settings, and advisory locks — test the app against a transaction-pooled instance in staging before prod. Don't set default_pool_size so high that pools × size exceeds max_connections. Roll out config changes during low traffic and watch SHOW POOLS for cl_waiting.
Why this prompt works
The instinct when an app exhausts max_connections is to raise max_connections — which trades a hard failure for memory pressure and context-switch overhead, because each backend is expensive. This prompt reframes the problem the way pgbouncer does: a small, expensive server pool sized to real parallelism, fronted by cheap client connections. Getting that mental model right is most of the fix.
The pool_mode decision is where pgbouncer quietly bites people. Transaction pooling gives the best multiplexing but silently breaks session state, LISTEN/NOTIFY, session advisory locks, and naive prepared statements. By forcing the model to map the app’s actual behavior to what each mode breaks and to name the required app changes, the prompt prevents a switch that looks fine until production hits an edge case.
Grounding pool size in cores and max_connections headroom — with the arithmetic shown — keeps the config defensible, and pointing at SHOW POOLS / SHOW STATS gives a concrete way to verify. The guardrails keep the mode change behind a staging test and low-traffic rollout, so the human confirms the app survives before committing.
Related prompts
-
Postgres postgresql.conf Workload Tuning Prompt
Get a reviewed postgresql.conf tuning plan for your specific hardware and workload — memory, WAL/checkpoint, planner, and autovacuum settings explained one by one, with how to verify each took effect.
-
Postgres Replication Lag Debugging Prompt
Diagnose streaming or logical replication lag from pg_stat_replication and pg_replication_slots — find where the bytes are stuck (send, write, flush, replay) and fix the cause without losing WAL or risking the primary.