Connection pooling and PgBouncer: 400 clients, PostgreSQL accepts 100 Intermediate

The problem

A Node.js ordering API runs 20 pods, each with a pg connection pool set to max: 20. At peak load, the cluster needs 400 connections.

PostgreSQL has max_connections = 100. When the autoscaler adds pods, the log shows FATAL: sorry, too many clients already, SQLSTATE 53300 (too_many_connections), while database CPU is still idle.

Is raising max_connections to 500 the right fix? If not, where does the pool go, how big is it, and what do you give up?

What to choose (summary first)

Situation	Choose	Condition
Sum of pool max (at the maximum pod count) fits under the slot count	App-side pool only	Leave slots for migrations, monitoring
Many pods, clients idle most of the time	PgBouncer transaction mode	The app does not rely on session state
Need session SET, LISTEN, session advisory locks	PgBouncer session mode or a dedicated connection	Accept that each open client holds a backend

Core concepts

Why PostgreSQL connections are expensive

PostgreSQL forks one process per connection. The process lives until the client disconnects, even while the connection just sits idle in a pool.

Some resources, including shared memory, are sized by max_connections; changing it requires a restart.

According to the PostgreSQL wiki, past the saturation point extra active connections just compete for CPU, locks and work_mem, so throughput drops. 400 clients need far fewer backends.

Two pool layers

	App-side pool (pg.Pool)	PgBouncer
Consolidates	Requests within one pod	Clients from every pod
PostgreSQL backends	Each pooled connection = one backend	Many clients share few backends
Default	max = 10	default_pool_size = 20 per user/database pair

The two layers are used together: pods keep pg.Pool but point it at PgBouncer.

Loading diagram…

Three pooling modes

The difference is how long a server connection stays bound to a client:

Mode	Bound until	PostgreSQL features
session (default)	The client disconnects	Full
transaction	The transaction ends	Loses session-bound features
statement	The statement ends	Like transaction, and multi-statement transactions are forbidden

Statement mode is rarely used; PgBouncer names PL/Proxy as its main target. In session mode, 400 open clients still need 400 backends.

How it works

How do 3 clients share 2 backends, and what leaks between them?

Transaction mode: a server connection belongs to a client for one transaction, then returns to the pool for another client

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

Client A opens a transaction: PgBouncer assigns server S1 to A. S2 is still free in the pool.

State after each step:

1. A and B BEGIN: S1 belongs to A, S2 to B; no idle server left.
2. C sends a statement: cl_waiting = 1. PostgreSQL does not know C exists.
3. A COMMITs: S1 returns to the pool; the A → PgBouncer connection stays open.
4. C gets S1, along with everything A left on that session (SET, session advisory locks, PREPARE).
5. A’s next transaction gets whichever server is free first, not necessarily S1.

In transaction mode only transaction scope can be trusted. PgBouncer lists the features that do not work:

Feature	Transaction mode	Replace with
SET/RESET	No	SET LOCAL inside the transaction
LISTEN	No	A dedicated connection (NOTIFY still works)
WITH HOLD cursors	No	Cursors inside a transaction, keyset pagination
SQL PREPARE/DEALLOCATE	No	Protocol-level prepared statements
Session advisory locks	No	pg_advisory_xact_lock, see advisory locks
Temp tables with PRESERVE/DELETE ROWS	No	ON COMMIT DROP

Protocol-level prepared statements (queries with a name in pg) work in transaction mode since PgBouncer 1.21.0 when max_prepared_statements is non-zero; since 1.24.0 the default is 200.

PgBouncer restores a few startup parameters per client (application_name, TimeZone, client_encoding…), but by default not search_path or statement_timeout (these can be added via track_extra_parameters).

Operational details

PgBouncer parameters:

• pool_mode: default session.
• default_pool_size: max server connections per user/database pair, default 20.
• max_client_conn: max clients, default 100; when raising it, also check the file descriptor limit.
• server_idle_timeout: closes servers idle longer than 600 seconds (default).
• max_db_connections: per-database cap regardless of user, default 0 (unlimited).
• server_reset_query (DISCARD ALL) only runs in session mode, unless server_reset_query_always is enabled.

PostgreSQL 17: superuser_reserved_connections defaults to 3, so regular roles get at most 97 slots. Hitting that gives remaining connection slots are reserved for roles with the SUPERUSER attribute; running out entirely gives sorry, too many clients already. Both are 53300 (visible with verbose logging).

Choosing the pool size

There is no universal formula. The PostgreSQL wiki suggests a number of active connections around (core_count * 2) + effective_spindle_count, with a warning: the formula is old, not analyzed for SSDs, and must be measured on the real system.

Start small, increase gradually and measure latency. The sum of all pools (each PgBouncer instance has its own) plus admin connections must stay below the regular-role slot count.

Observe through the admin console (the pgbouncer database):

• SHOW POOLS: cl_waiting > 0 for a sustained period with maxwait climbing means the server is overloaded or the pool is too small. Check CPU and locks before growing the pool.
• sv_idle always high: the pool is oversized. SHOW STATS: avg_wait_time (microseconds waiting to be assigned a server).

Real-world example

20 ordering pods through one PgBouncer

20 pods point at PgBouncer and keep max: 20. PgBouncer: pool_mode = transaction, max_client_conn = 500, default_pool_size = 40. PostgreSQL keeps max_connections = 100: 40 for the app, the rest as headroom.

40 is a starting point; re-measure against cl_waiting and latency.

Demo: PostgreSQL 17 (max_connections = 20) + PgBouncer 1.25.2, transaction mode, default_pool_size = 5

examples/compose.connection-pooling-pgbouncer.yml runs a separate PostgreSQL and edoburu/pgbouncer:v1.25.2-p0 (host port 6433). The pgbench script:

# inside the postgres container; table demo_connection_pooling.orders already created
printf "BEGIN;\nINSERT INTO demo_connection_pooling.orders(total) VALUES (100);\nSELECT pg_sleep(0.05);\nCOMMIT;\n" > /tmp/tx.sql
pgbench -h pgbouncer -U hub -d hub -n -f /tmp/tx.sql -c 50 -j 4 -T 10

1. 50 clients, 5 backends (SHOW POOLS and pg_stat_activity mid-run):

 database | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
 hub      |         5 |         45 |         5 |       0 | transaction
 usename | application_name | count   -- on PostgreSQL
 hub     | pgbench          |     5
number of failed transactions: 0 (0.000%)
tps = 94.028315 (without initial connection time)

5 backends, each transaction holding about 50 ms: a ceiling of roughly 100 tps. Numbers vary between runs.

2. SET leaks to another client (two separate psql invocations):

-- client A
SELECT pg_backend_pid();                 -- 111
SET search_path TO demo_connection_pooling;
SET statement_timeout = '1ms';
-- client B, new connection
SELECT pg_backend_pid();                 -- 111
SHOW statement_timeout;                  -- 1ms
SELECT pg_sleep(0.01);
ERROR:  canceling statement due to statement timeout

While another client holds S1, A’s next statement runs on backend 110 and sees the default search_path: A loses its own settings.

3. Exceeding max_connections (with PgBouncer’s 5 backends already open):

$ pgbench -h localhost ... -c 30      # direct to PostgreSQL
FATAL:  sorry, too many clients already
pgbench: error: could not create connection for client 15
$ pgbench -h pgbouncer ... -c 100     # through PgBouncer
number of failed transactions: 0 (0.000%)

Cleanup: DROP SCHEMA demo_connection_pooling CASCADE, then down -v.

Trade-offs

Pros

• Hundreds of clients share a few dozen backends.

• The queue lives in PgBouncer (cl_waiting) instead of 53300 errors.

< slot="cons">

• Transaction mode drops session features; state-leak bugs often only show under load.

• One more network hop and one more component that needs HA.

• Long transactions or idle-in-transaction sessions hold server connections, and the whole pool waits.

Cons

Production checklist

1. Multiply the maximum pod count × pool max, and compare with the regular-role slots.
2. Audit the code before transaction mode: SET (switch to SET LOCAL), LISTEN, session advisory locks, PREPARE, WITH HOLD, temp tables.
3. Check PgBouncer ≥ 1.21 and max_prepared_statements if the driver uses named prepared statements.
4. Raise max_client_conn to the real client count; set default_pool_size/max_db_connections so the total stays under max_connections minus headroom.
5. Jobs that need a session (advisory locks, LISTEN) use a dedicated connection.
6. Set idle_in_transaction_session_timeout.
7. Alert when cl_waiting > 0 is sustained or maxwait climbs.

Common mistakes

The hardest bug to spot:

SET through transaction mode: leaks to another client or gets lost

✕ Common

await pool.query("SET statement_timeout = '2s'");
await pool.query('SELECT * FROM orders WHERE id = $1', [id]);

✓ Prefer

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query("SET LOCAL statement_timeout = '2s'");
  await client.query('SELECT * FROM orders WHERE id = $1', [id]);
  await client.query('COMMIT');
} catch (e) { await client.query('ROLLBACK'); throw e; }
finally { client.release(); }

Why

SET LOCAL expires when the transaction ends, exactly when PgBouncer takes the server connection back.

The second trap:

Raising max_connections to 500 to make 53300 go away

The error disappears, but up to 500 processes compete for CPU, locks and work_mem; at peak load every query slows down. Consolidate clients with a pooler instead.

Misconception	Consequence	What to do
Session mode reduces backends	400 open clients still need 400 backends	Transaction mode
default_pool_size is a PgBouncer-wide cap	Several user/database pairs exceed the slots	max_db_connections
High cl_waiting means grow the pool	A saturated server gets slower	Check CPU, locks, slow queries first

Self-check

1. Why does client B see statement_timeout = 1ms even though it never ran SET?
2. The 50-client demo only reaches about 94 tps. What would you change to increase it, and when would that not help?
3. Two users on the same database, default_pool_size = 60, max_connections = 100. What happens when both pools fill up?

Summary

Key takeaways

• Each PostgreSQL connection is a process; too many active backends reduce throughput.
• An app-side pool only consolidates within one pod; many pods need an external pooler like PgBouncer.
• Transaction mode assigns servers per transaction: SET, LISTEN, session locks and WITH HOLD do not work.
• Choose pool size by measuring; watch cl_waiting and maxwait.

References

• PostgreSQL 17 — Architectural Fundamentals: one process per connection.
• PostgreSQL 17 — Connections and Authentication: max_connections, reserved slots.
• PgBouncer Features, Configuration, Usage, Changelog: modes, features, defaults, SHOW POOLS columns, version milestones.
• PostgreSQL Wiki — Number Of Database Connections: why many connections slow things down.