Connection pooling, and why you ran out of connections

`FATAL: sorry, too many clients already`. Every team meets this message eventually, usually at the worst time, and the instinct is to raise `max_connections` and move on. That works for about a week.

The reason it's a trap is worth understanding, because it's a consequence of a deliberate architectural decision in Postgres rather than an arbitrary limit someone picked.

One process per connection

When a client connects, the Postgres postmaster forks a new backend process to serve it exclusively, for the connection's entire life. Not a thread — a full operating system process with its own memory.

The upside is robustness: a backend that crashes takes down one connection, not the server. The downside is cost. Each backend has a baseline memory footprint of a few megabytes, plus whatever `work_mem` its queries allocate for sorts and hashes, and all of them contend on shared structures like the lock table and the buffer pool.

-- What is actually connected, and doing what
SELECT state, count(*), max(now() - state_change) AS oldest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY count DESC;

-- The usual answer looks like this:
--   idle in transaction | 180 | 00:14:22   <-- your actual problem
--   idle                |  40 | 00:00:31
--   active              |   6 | 00:00:00

That output is the whole story in most incidents. Six connections are doing work. A hundred and eighty are holding a transaction open while doing nothing, each pinning a process, each potentially holding locks and preventing vacuum from cleaning up dead rows.

Why raising the limit makes things worse

Throughput against a Postgres instance rises with concurrency up to roughly the number of CPU cores, plateaus, and then falls. Past the plateau you're not adding capacity, you're adding context switching, lock contention and cache pressure.

So a database configured for 500 connections, with 500 clients actually working, is slower than the same database serving the same workload through 20 connections. The queries queue either way — the question is whether they queue cheaply in a pool or expensively inside the database, competing for the same CPUs.

This is why the fix is a pool rather than a bigger limit.

Sizing the pool

The starting formula that has held up well in practice:

connections ≈ (cores × 2) + effective_spindle_count

# On a 2-vCPU database instance with SSD storage:
#   (2 × 2) + 1 = 5 connections
#
# Not a typo. Five is enough to saturate two cores with query work.
# The instinct to set 100 comes from nowhere in particular.

Two adjustments matter. First, the pool size is per application instance — four instances with a pool of 10 each is 40 connections to the database, and it's the total that has to fit. Second, if your queries are I/O-bound rather than CPU-bound, you can go somewhat higher, because backends waiting on disk aren't consuming a core.

// node-postgres: the settings that matter
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                        // per instance — multiply by instance count
  idleTimeoutMillis: 30_000,      // release idle connections; see note below
  connectionTimeoutMillis: 10_000, // fail fast rather than hanging forever
});

That `idleTimeoutMillis` is worth setting deliberately, and not only for connection accounting. A pool that holds connections open indefinitely makes a database look permanently in use — which matters if you run databases that suspend when idle, because a pool's keepalive traffic is enough to prevent them ever idling. It's the same class of bug as an uptime monitor keeping an app awake.

When the pool has to live outside the app

In-process pools work when you have a countable number of long-lived instances. They fail completely in three situations.

• Serverless functions. Each concurrent invocation is potentially its own process with its own pool, and there can be hundreds. A pool of 5 becomes 500 connections during a traffic spike — the pool is not restraining anything because there is no shared process to restrain.
• Autoscaled instances. Same arithmetic, slower. The pool size that's correct at three instances is a database outage at thirty.
• Many small services. Twenty services each with a modest pool add up, and no single team owns the total.

The answer is a pooler in front of the database — PgBouncer being the standard — that multiplexes many client connections onto few database connections. Clients connect to the pooler cheaply; the pooler maintains a small, stable set of real backends.

Transaction mode, and what it takes away

PgBouncer's leverage comes from transaction pooling: a real database connection is assigned to a client only for the duration of a transaction, then returned. Ten thousand mostly-idle clients can share fifty backends, because at any instant only fifty are inside a transaction.

The catch is that anything relying on session state across statements breaks, because your next statement may land on a different backend entirely.

• Session-level `SET` statements — search_path, timezone, application_name — don't persist.
• `LISTEN`/`NOTIFY` doesn't work; there's no stable session to deliver to.
• Advisory locks taken at session scope are unreliable. Use transaction-scoped variants instead.
• Server-side prepared statements are the big one, and cause the most confusing failures.
• Temporary tables don't survive between statements.
• `WITH HOLD` cursors don't work.

# The error everyone hits with an ORM behind PgBouncer
ERROR: prepared statement "s1" already exists

# Fixes, in order of preference:
#  1. PgBouncer 1.21+ supports protocol-level prepared statements —
#     set max_prepared_statements to a non-zero value.
#  2. Disable them client-side:
#     postgres://...?pgbouncer=true          (Prisma)
#     prepare: false                          (postgres.js)
#     ?prepared_statement_cache_size=0        (asyncpg / SQLAlchemy)

Session pooling mode keeps all of that working, but assigns a connection for the client's whole session — which gives you back roughly the multiplexing of no pooler at all. Transaction mode is where the benefit is, and knowing its limitations before you deploy is much better than discovering them from an error message in production.

A short diagnostic sequence

1. Query `pg_stat_activity` grouped by state. If `idle in transaction` dominates, you have an application bug, not a capacity problem — find the code path holding a transaction across a network call.
2. Count your real connections: instances × pool size, plus every migration runner, cron job, admin tool and analytics connector. The forgotten ones are usually where the surprise is.
3. Compare that total against `max_connections`, leaving headroom for superuser connections and maintenance.
4. Set `idle_in_transaction_session_timeout` and `statement_timeout` on the database so a stuck client can't hold resources indefinitely.
5. Only then consider a pooler — and if you add one, pick transaction mode and turn off client-side prepared statements at the same time, rather than debugging it later.

Almost every 'we need a bigger database' conversation that starts with connection errors ends somewhere in that list instead. The connections were never the constraint; something was holding them open, and the pool was hiding it.