Connection pooling and PgBouncer

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

After scaling to 40 pods, Postgres hits 'too many clients' and latency rises. Best fix?

Options
Hint 1 of 1

Multiply pods by pool size.

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Put PgBouncer in transaction mode in front and shrink per-pod pools

Correct. Thousands of client connections share a small server pool; total server connections stay bounded.

Each Postgres connection is a server process, and 40 pods × their pools exceed what it can serve well. PgBouncer in transaction mode lends a few server connections to many clients, one transaction at a time.

Why not the others?
  • B Raise max_connections to 4,000. Each connection is a process; contention gets worse.
  • C Open a new connection per query. Connection setup is expensive and still unbounded.
  • D Add a read replica. Doesn't fix connection count on the primary for writes.

30-second answer

Each PostgreSQL connection is a separate server process with its own memory, and throughput peaks at a fairly small number of active connections, roughly a few per CPU core, then falls as they compete. A pool reuses a fixed set of connections across requests. With many app instances, add PgBouncer: in transaction mode it lends a server connection only for the length of a transaction, so thousands of clients can share a few dozen.

Practise saying it · 35 s. No recording, just a timer

Explain connection pooling.

Say your answer out loud, then check which of these points you covered:

  • Connection = process
  • Throughput peaks at few active connections
  • Pools reuse connections
  • PgBouncer transaction mode
  • Session-state caveats

Deep dive

Total connections = instances × pool size; 50 pods × 20 is 1,000, which is too many for most servers.

Transaction mode breaks session state: session-level advisory locks, SET without LOCAL, LISTEN, and (on older PgBouncer) prepared statements.

Pool timeouts surface as latency; measure wait time for a connection, not just query time.

Serverless functions need an external pooler because each instance opens its own connections.

See who's connected and what they're doing Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
SELECT count(*) > 0 FROM pg_stat_activity
WHERE backend_type = 'client backend';
SHOW max_connections;

Common mistakes

  • Raising max_connections to 2,000 instead of pooling.
  • Holding a pooled connection during slow non-database work.
  • Using session features through a transaction-mode pooler.

Senior take & follow-ups

Explain the process-per-connection cost, the multiplication across instances, and the transaction-mode caveats.

Follow-up questions

Think of your answer first, then open the hint.

  1. What breaks in transaction pooling?

    Show hint

    Anything that relies on session state across transactions.

  2. How big should one pod's pool be?

    Show hint

    Small (often 5 to 20) and sized so the total fits the server.

Related questions

Suggest a fix if something here is wrong or out of date.