🌊 Postgres Connection Pooling With PgBouncer and AI

The error that pages you is always the same: FATAL: sorry, too many clients already. Postgres has a hard ceiling on connections, each one costs real memory, and a modern app stack with dozens of pods each running their own connection pool will blow through that ceiling effortlessly. The instinct is to raise max_connections, which is almost always the wrong move — Postgres does not scale well to thousands of connections, and you just trade a connection error for a memory or context-switch problem. The right answer is a connection pooler in front of the database. AI is a good guide through PgBouncer’s pool modes and sizing math, but it can’t see your live pool stats. You feed it SHOW POOLS; it tells you what’s saturated.

This is how I size and debug PgBouncer without thrashing the config.

Why pooling, and why not just raise max_connections

Every Postgres connection is a backend process with its own memory. A few hundred is fine; a few thousand mostly idle connections waste memory and add scheduling overhead, and the active ones contend. PgBouncer multiplexes many client connections onto a small set of real server connections, so the database sees a stable, small pool while the app thinks it has plenty. The whole point is to keep the number of real Postgres backends low and busy.

Pick the pool mode carefully — this is where people get burned

PgBouncer has three modes, and choosing wrong causes subtle, awful bugs.

• session — a server connection is tied to a client for its whole session. Safest, least efficient.
• transaction — the server connection is returned to the pool after each transaction. The sweet spot for most web apps.
• statement — returned after each statement. Fastest, but breaks multi-statement transactions; rarely worth it.

The catch: transaction mode breaks session-level features — prepared statements (in some drivers), SET that’s expected to persist, advisory locks, LISTEN/NOTIFY. I describe my app’s behavior to AI explicitly and have it flag what will break.

My app uses transaction-mode PgBouncer in front of Postgres 16. The driver uses server-side prepared statements and we use session-level SET search_path after connecting. Tell me exactly which of these break under transaction pooling and what the fix is — driver setting, PgBouncer config, or app change. Be specific about prepared statements.

The model is good here because these gotchas are well-documented. I still verify by actually exercising the path under transaction mode in staging, because driver behavior varies.

Size the pool from real concurrency, not hope

The sizing math is counterintuitive: you usually want fewer server connections than you’d guess. A pool of 20-40 active connections often outperforms 200, because Postgres throughput peaks when active backends are near your core count and degrades past it.

[databases]
proddb = host=127.0.0.1 port=5432 dbname=proddb

[pgbouncer]
pool_mode = transaction
default_pool_size = 25        ; server conns per user/db pair
max_client_conn = 2000        ; clients PgBouncer will accept
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60

Our Postgres box has 16 cores. Peak concurrency is ~40 in-flight queries with average duration 8ms. Recommend default_pool_size and max_client_conn, and explain why a smaller server pool can give higher throughput than a larger one. Show the reasoning on active backends vs. cores.

AI lays out the queueing reasoning clearly. I verify against load — the only number that matters is measured throughput and latency at the chosen pool size.

Debug exhaustion from the pooler’s own stats

PgBouncer exposes an admin console with the exact stats you need. When connections feel starved, this tells you whether clients are waiting for a server connection (pool too small) or whether the pool is fine and the app is leaking connections.

psql -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

The columns that matter: cl_waiting (clients queued with no server connection available) and sv_active / sv_idle. High cl_waiting with all servers active means the pool is too small or queries are too slow. I paste SHOW POOLS and SHOW STATS to AI and ask it to diagnose:

Here’s SHOW POOLS output. cl_waiting is 180 and climbing, sv_active is 25, sv_idle is 0. Is this pool saturation or slow queries? Walk me through how to tell the difference and what to check next on the Postgres side.

A high cl_waiting with a saturated server pool sends you two directions at once: either bump the pool a little, or — more often — find the slow queries holding server connections too long, which loops you back to plan debugging on the database itself.

Watch the database side too

PgBouncer’s job is to keep Postgres connection counts sane, so confirm it’s working from Postgres’s view:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

If you still see hundreds of backends, PgBouncer isn’t actually in the path for some clients — a classic misconfiguration where one service connects directly to 5432 and bypasses the pooler.

The division of labor

AI explains pool modes, does the sizing math, and flags the transaction-mode gotchas that cause the nastiest bugs — all things it reasons about reliably. What it can’t do is see your SHOW POOLS or your real concurrency, so the sizing always gets validated under load and the gotchas get exercised in staging. Pooling is also a security boundary worth getting right; if your Postgres is reachable beyond the pooler, start with hardening Redis and Postgres against exposure. The rest of the database material is in the Postgres guides, and my PgBouncer-tuning prompts live in the prompt library.