PostgreSQL Connection Pool Sizing: The Formula That Works

Almost every question about connection pooling eventually collapses into one practical number: how big should the pool be? Get pool sizing wrong in one direction and you meet FATAL: sorry, too many clients already during a traffic spike. Get it wrong in the other direction - a pool that's far too large - and you quietly make the database slower under load, for reasons most people never suspect. This post gives you the sizing formula that actually works, the PgBouncer math behind it, and the two things you have to understand first for the numbers to make sense: why Postgres connections are so expensive, and the transaction-versus-session-mode choice that decides how much a pooler can multiplex.

If you just want the short answer: a good starting point for the pool that talks to Postgres is roughly (cpu_cores x 2) + 1 connections - often a low-tens number, not the hundreds people reach for. The rest of this post explains why that formula holds, how to translate it into PgBouncer's default_pool_size and max_client_conn, and when you can skip a dedicated pooler entirely.

Why PostgreSQL runs out of connections

Every PostgreSQL connection is backed by its own operating-system process on the server, with its own memory. That design makes Postgres robust and isolated, but it also makes connections expensive and finite. A typical instance is configured for a few hundred connections via max_connections, and each idle connection still costs memory. So the ceiling is lower than people assume - you don't need thousands of concurrent queries to hit it, just thousands of mostly-idle connections that someone forgot to close.

Two patterns blow through that ceiling fast:

• Connection-per-request code. An app that opens a new connection for every HTTP request and doesn't reuse a pool will stack up connections under load.

• Serverless sprawl. On Lambda, Vercel functions, or Cloudflare Workers, each concurrent invocation is effectively its own process. A thousand concurrent invocations, each opening even one connection, is a thousand connections - a stampede the database can't absorb.

What a connection pooler actually does

A pooler like PgBouncer (or Supabase's Supavisor, or pgcat) sits between your application and PostgreSQL. Your app opens lots of cheap connections to the pooler; the pooler maintains a small set of real, expensive connections to Postgres and multiplexes your traffic across them. A thousand app connections can share a pool of, say, twenty backend connections, because at any given instant most of them aren't actually running a query. The pooler is essentially a traffic controller that keeps the expensive resource - real Postgres connections - busy and bounded.

A pooler doesn't make Postgres faster. It makes a small number of connections serve a large number of clients, which is exactly what you need when you have far more clients than the database can hold.

Transaction mode vs session mode - the choice that matters

PgBouncer offers a few pooling modes, but two matter in practice, and picking the wrong one causes subtle bugs.

Session mode

In session mode, a client holds a backend connection for the entire duration of its session - from connect to disconnect. This is the most compatible mode: everything that relies on connection-level state works normally, including SET settings, LISTEN/NOTIFY, session-level advisory locks, and named prepared statements. The downside is lower multiplexing - one client ties up one backend connection for as long as it's connected, so you get less of the pooling benefit.

Transaction mode

In transaction mode, a backend connection is assigned to a client only for the duration of a single transaction, then returned to the pool. This gives you maximum multiplexing - ideal for serverless and high-concurrency apps - because a connection is held for milliseconds, not minutes. The trade-off: because you might get a different backend connection for the next transaction, anything that depends on session state breaks. That includes session-level SET, LISTEN/NOTIFY, and - the classic footgun - server-side named prepared statements.

The prepared-statement gotcha (a true story shape)

Here's the bug that catches everyone. Many drivers and ORMs use named prepared statements under the hood (Prisma is a well-known example). In transaction mode, prepared statement s0 might be created on one backend connection and then reused on a different one that's never heard of it - producing errors like prepared statement "s0" already exists (SQLState 42P05) or prepared statement "s0" does not exist. The fix is to tell your driver to disable named prepared statements when talking through a transaction-mode pooler (for example, the pgbouncer=true flag on the connection string for some clients), or to use the pooler only for the runtime app and the direct connection for migrations and admin tasks.

A practical rule for which connection to use where

• Runtime app traffic: use the pooler (transaction mode for serverless, session mode for long-running servers that need session features).

• Migrations, schema changes, pg_dump, seeds: use the direct (non-pooled) connection. These need session state and prepared statements, and they're not high-concurrency.

Connection pool sizing: the formula that actually works

Pool sizing is where intuition misleads almost everyone. The instinct is 'more traffic, so make the pool bigger'. But the pool that matters most - the one from your pooler (or app) to Postgres - should be sized to the database's ability to do work in parallel, not to how many clients are knocking. Once every CPU core is busy running a query, adding more concurrent connections doesn't do more work; it just adds lock contention, context-switching, and memory pressure. Past that point a bigger pool makes throughput worse.

The widely-used starting formula, popularised by the PostgreSQL wiki and the HikariCP project, is:

connections = (cpu_cores x 2) + effective_spindle_count
​
# On modern SSD/NVMe systems the spindle term is small, so the
# practical rule of thumb collapses to:
​
connections = (cpu_cores x 2) + 1

So a 4-core Postgres instance wants a backend pool in the neighbourhood of 9 connections, not 90. That feels far too small until you internalise the key insight: a pool of 9 that stays busy will out-throughput a pool of 100 that spends its time fighting over the same cores. Start near the formula, then measure and adjust - if CPU is underused and queries are waiting on the pool, nudge it up; if CPU is pinned and latency is climbing, it's already too big.

Turning the number into PgBouncer settings

PgBouncer exposes a few knobs, and the sizing formula maps onto them directly. The three that matter:

• default_pool_size - the number of real backend connections PgBouncer opens to Postgres per (user, database) pair. This is where your (cores x 2) + 1 number goes. It defaults to 20.

• max_client_conn - how many application connections PgBouncer will accept out front. Because pooled client connections are cheap, this is deliberately large, often in the thousands.

• reserve_pool_size - a small emergency pool that kicks in when a regular pool is exhausted, to absorb short bursts.

The hard constraint to respect: the sum of every pool's default_pool_size across all (db, user) pairs must stay below Postgres's own max_connections, with 10-20 connections left as headroom for superuser access, replication, and monitoring. If PgBouncer is allowed to open more backend connections than Postgres permits, you've just moved the 'too many clients' error one layer down instead of fixing it.

A worked example

A single 4-core Postgres instance serving one application database and one role:

# postgresql.conf
max_connections = 100          # plenty of headroom above the pool
​
# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app
​
[pgbouncer]
pool_mode = transaction        # max multiplexing for a web app
default_pool_size = 20         # backend conns to Postgres (near (4x2)+1, rounded up for burst)
max_client_conn = 2000         # cheap app-side connections out front
reserve_pool_size = 5          # short-burst headroom

That configuration lets up to 2,000 application connections share a pool of ~20 real backend connections - and 20 sits comfortably under the database's 100-connection ceiling. Two thousand clients, twenty expensive resources, one database that never sees 'sorry, too many clients already'.

Sizing the pool inside your application

The pooler isn't the only pool - your app has one too, and the right size depends entirely on your runtime:

• Long-running server (Express, a worker): one shared pool per process, with a modest max (think 10-20), reused across all requests. Size the total across all processes to fit under the database or PgBouncer ceiling.

• Serverless function: a tiny pool per instance (1-2 connections), with a transaction-mode pooler doing the real multiplexing in front of Postgres. A thousand warm instances each holding 1-2 connections is exactly the stampede a pooler exists to absorb.

The mistake to avoid is creating a new client or pool on every request or every cold start. Create the pool once at module scope, reuse it everywhere, attach an error handler, and close it cleanly on shutdown.

Do you even need a pooler?

If you run a single long-lived server with a sensible shared pool and your concurrency is modest, you may never hit the ceiling - a pooler adds a component you don't need. You almost certainly do need one if you're serverless, if you run many app instances, or if you've already met the 'too many clients' error. The honest answer is: pool inside your app first, add a dedicated pooler when your deployment model multiplies connections beyond what the database can hold.

Where Swyftstack fits

Pooling is one of those things that's easy to get wrong and tedious to operate. Swyftstack ships managed PostgreSQL with pooling configured for you, so you can pick transaction or session mode without standing up and tuning PgBouncer yourself. You still get a standard connection string and real, unmodified Postgres - the pooler is just handled. If you're building on Next.js or another serverless runtime, that means the 'too many clients' error is one less thing you have to design around.

The takeaway

Connection pooling exists because Postgres connections are heavyweight and your app wants to open lots of them. A pooler multiplexes many cheap client connections onto a few real backend ones. Use transaction mode for serverless (and disable named prepared statements), session mode when you need connection-level features, the direct connection for migrations, and a modest, reused pool everywhere. Get that right and you'll never see 'sorry, too many clients already' again.