Connection Pooling for PostgreSQL Under Real Concurrency

Here's a number worth staring at. In a benchmark Andres Freund ran while working on Postgres connection scalability, one active connection doing simple reads managed 33,457 transactions per second on an otherwise idle server. Add 10,000 connections that do nothing at all, and the same single connection dropped to 14,496 TPS. Nothing else changed. The idle connections weren't running queries. They were just there.

That's the part of connection pooling most teams never think about. They treat pool size as a knob on one service: bump max from 10 to 50 because the API felt slow, ship it, move on. But Postgres doesn't see your services. It sees one flat pile of backend processes, and every service, every replica, every cron job, and every half-finished rolling deploy adds to the same pile.

So let's do this properly. Not "what should max be," but "how do you split one database's real capacity across everything that talks to it, and keep it split when the cluster autoscales at 3pm on a Friday."

What a Postgres connection actually costs

Postgres uses a process-per-connection model. Every client connection gets its own backend process, forked by the postmaster, with its own slot in shared memory and its own row in pg_stat_activity. That's very different from a thread-per-connection database, and it's why the question "how many connections can Postgres handle?" has a more interesting answer than you'd expect.

There are three separate costs, and people usually blame the wrong one.

Memory is smaller than folklore says. You'll read "10MB per connection" in a lot of blog posts. Freund's analysis on the Citus blog measured it properly and found the per-connection overhead is below 2 MiB when the server is configured sensibly, with huge_pages on. That's not free, but it's not the reason your database fell over either. (The shared-memory side is also allocated up front: max_connections sizes the lock tables and process slots at startup, which is one reason changing it needs a restart.)

Snapshots were the real bottleneck, and they got fixed. Every statement takes a snapshot to decide which row versions it can see. Before Postgres 14, building that snapshot meant walking the state of every backend, including the idle ones. In Freund's CPU profile, with one active connection next to 5,000 idle ones, the bottleneck sat squarely in GetSnapshotData(). His fix landed in Postgres 14, and the before/after is dramatic: with 48 active connections and 10,000 idle, throughput went from 369,845 TPS to 1,115,740 TPS on the same hardware (2x Xeon Gold 5215).

Active concurrency is the cost that never goes away. Here's the thing Postgres 14 didn't change and can't change: if 400 connections are all running queries at the same time on a 16-core box, they're fighting over 16 cores, the same buffer pool, the same locks, and the same disk queue. Context switching goes up, cache hit rates go down, lock waits pile on. More concurrent work past the point of saturation doesn't make the database do more. It makes every query slower.

That last point is the whole article in one sentence. Idle connections are now cheap. Active connections are still expensive. Your pooling strategy should be about capping active concurrency at the database, not about hoarding slots.

The formula everyone quotes, and what it really means

The HikariCP wiki's "About Pool Sizing" page is one of the most-cited pieces of writing on this topic, and it opens with a result from Oracle's Real-World Performance group: reducing the connection pool size alone, with no other change, cut application response times from around 100ms to around 2ms. Smaller pool, faster app.

Then it gives its starting-point formula:

connections = ((core_count * 2) + effective_spindle_count)

A few details matter here and get lost in the retelling:

• core_count is the database server's physical cores, not the app server's, and not hyperthreads.
• effective_spindle_count is how many disk operations can be in flight usefully. On SSD or NVMe, people typically plug in 1 as a starting point. The "extra" connections beyond cores only exist to cover time a backend spends blocked on I/O.
• The wiki's own example is a 4-core server with one disk: (4 * 2) + 1 = 9, rounded to 10. It claims that setup comfortably serves 3,000 front-end users running simple queries at 6,000 TPS.

And the wiki is explicit that this is a starting point, not a law. Treat it as an order-of-magnitude anchor.

The detail that matters most here: that formula tells you the size of the pool for the whole database. It does not tell you what to put in one service's config. If you have a 16-core database, the formula says somewhere around 33 connections should be actively working at once. If you have eight services each running six replicas, and each replica's pool is sized "by the formula," you've just told the database it should expect 1,584 of them. That's how teams end up quoting HikariCP correctly and still melting their database.

Your real pool size is a multiplication

Let's make it concrete. The following is a hypothetical system, but the shape will look familiar: four workloads on one Postgres primary with 16 physical cores and max_connections = 200.

Workload	Replicas (steady)	Pool max per replica	Connections at steady state
api (Node, pg)	12	10	120
worker (Go, pgxpool)	6	16	96
reporting	2	10	20
cron + migrations + humans with psql	varies	varies	~5
Total			241

It's already over 200 at steady state, and nothing is misbehaving. Each team picked a reasonable-looking number. Two of those numbers are literally library defaults: pg.Pool defaults max to 10, and pgxpool defaults MaxConns to the greater of 4 or runtime.NumCPU(), so a worker pod on a 16-vCPU node gets 16 without anyone deciding that.

Now add the two things that make this worse at the worst possible moment.

Rolling deploys. A Kubernetes Deployment's maxSurge defaults to 25%. During a rollout, old and new pods coexist, so your 12 API replicas can briefly be 15. That's 30 more connections, and they show up exactly when you're deploying, which is also when you're least prepared to debug FATAL: sorry, too many clients already.

Autoscaling. The HPA scales on CPU or custom metrics. It has no idea your database has a connection budget. When traffic spikes, it'll happily add pods, and each new pod can fill its whole pool as soon as traffic reaches it. The database gets more connections at the exact moment it's getting more load.

And don't forget the slots you can't use. Postgres reserves superuser_reserved_connections (default 3) for superusers, and since Postgres 16 there's also reserved_connections (default 0) for roles granted pg_use_reserved_connections. Those exist so you can still get in and fix things when the app has eaten every other slot. Keep them. Your app budget is max_connections minus those reservations minus whatever replication, monitoring, and migration tooling need.

So the honest formula for a direct-to-Postgres setup is:

sum over services of (pool_max * max_replicas * (1 + surge))
    + ops_and_tooling
    <= max_connections - superuser_reserved_connections - reserved_connections

Notice it uses max_replicas, the HPA ceiling, not today's replica count. If you size against the steady state, the budget holds right up until you need it.

Size each service with Little's Law, not vibes

If the database-wide number comes from cores, where does the per-service number come from? From how long each request actually holds a connection.

Little's Law says the average number of items in a system equals arrival rate times time in the system: L = lambda * W. For a connection pool, L is how many connections are busy on average, lambda is how often the service checks one out, and W is how long it keeps it.

Take a worked example, again with hypothetical numbers. Say one API replica handles 200 requests per second at peak, and each request holds a connection for about 15ms (query time plus the round trips in between, from checkout to release):

busy connections = 200 req/s * 0.015 s = 3

Three connections busy on average. A pool of 10 per replica gives you a lot of headroom for bursts and slow queries. A pool of 50 gives you nothing except the ability to hurt the database when things go wrong.

The useful insight isn't the arithmetic. It's that W is the variable you control, and it's usually the one that's out of whack. If that same service holds a connection for 150ms instead of 15ms, it needs ten times the connections for the same traffic. And the most common reason W balloons isn't slow SQL. It's code like this:

// Looks fine. Holds a Postgres connection across an HTTP call.
const client = await pool.connect();
try {
  await client.query('BEGIN');
  const order = await client.query('SELECT * FROM orders WHERE id = $1 FOR UPDATE', [id]);
  const charge = await paymentGateway.charge(order.rows[0]); // 300-2000ms, network
  await client.query('UPDATE orders SET status = $1 WHERE id = $2', ['paid', id]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

That transaction pins a connection (and a row lock) for the full duration of a third-party network call. Under load, the pool drains, and every other request queues behind payment latency. Bumping max doesn't fix it. It just moves the queue into Postgres, where it now also holds row locks. Do the external call outside the transaction, and let the database connection live only as long as the database work.

Postgres has a guardrail for the worst version of this: idle_in_transaction_session_timeout terminates sessions that sit idle inside an open transaction longer than the limit. It's off by default. Turning it on per role is cheap insurance.

Configure the client pool like you mean it

Library defaults are tuned for "works on a laptop." Under real concurrency, a few of them bite hard.

In node-postgres, connectionTimeoutMillis defaults to 0, which means no timeout. When the pool is exhausted, pool.connect() and pool.query() just wait. Forever. Your request timeout fires upstream, the client retries, and now you've got two waiters instead of one. Here's a pool that fails fast instead:

db.ts

import pg from 'pg';

export const pool = new pg.Pool({
  host: process.env.PGHOST,
  database: process.env.PGDATABASE,
  user: process.env.PGUSER,          // one role per service, see below
  max: 10,                           // sized from the budget, not from a hunch
  connectionTimeoutMillis: 2_000,    // fail fast when the pool is empty
  idleTimeoutMillis: 10_000,         // the default, stated on purpose
  maxLifetimeSeconds: 1_800,         // recycle so connections rebalance after failover/scale
  application_name: 'api',           // shows up in pg_stat_activity
});

// An idle client can error (e.g. the server restarted). Without a
// listener, that error is unhandled and can crash the process.
pool.on('error', (err) => {
  console.error('idle pg client error', err);
});

// Export these as metrics. waitingCount > 0 for long = pool is the bottleneck.
export function poolStats() {
  return {
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  };
}

pool.waitingCount is the most underrated metric in Node database code. If it's regularly above zero, requests are queueing for connections inside your app. That's fine in short bursts; it's exactly where you want the queue to be. If it's pinned high, you've either got a W problem (connections held too long) or a genuinely undersized pool, and the Little's Law math tells you which.

On the Go side, the trap is different. database/sql defaults SetMaxOpenConns to 0, which means unlimited. Nothing in your service stops it from opening one connection per concurrent goroutine that touches the database. If you're using pgxpool directly, set the limits explicitly instead of inheriting runtime.NumCPU() from whatever node the pod landed on:

db.go

cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    return nil, err
}
cfg.MaxConns = 16                        // explicit: from the budget, not NumCPU
cfg.MinConns = 2
cfg.MaxConnLifetime = 30 * time.Minute
cfg.MaxConnIdleTime = 5 * time.Minute
cfg.ConnConfig.RuntimeParams["application_name"] = "worker"

pool, err := pgxpool.NewWithConfig(ctx, cfg)

The nested-checkout deadlock

One more client-side footgun, and it's a nasty one because it looks like the database is hung. If a single request can hold two connections at once (for example, it opens a transaction, then calls a helper that does pool.query() outside that transaction), then a pool can deadlock itself: every connection is held by a request that's waiting for a second connection that will never come free.

The HikariCP wiki gives the minimum pool size that avoids this: Tn * (Cm - 1) + 1, where Tn is the max number of concurrent threads and Cm is the max connections any one of them holds at once. With 8 threads each holding up to 3, that's 17. The better fix is to not do it: pass the transaction's client down instead of reaching back into the pool.

Give every service its own role

One cheap move pays off in three places. Instead of every service connecting as app, give each one its own Postgres role.

CREATE ROLE api_svc       LOGIN CONNECTION LIMIT 150;
CREATE ROLE worker_svc    LOGIN CONNECTION LIMIT 100;
CREATE ROLE reporting_svc LOGIN CONNECTION LIMIT 20;

-- Per-role guardrails, applied at session start, no app code needed
ALTER ROLE api_svc       SET statement_timeout = '5s';
ALTER ROLE api_svc       SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE reporting_svc SET statement_timeout = '120s';

1. CONNECTION LIMIT turns the budget into enforcement. A misconfigured reporting deploy can exhaust its own 20 slots and get errors, instead of exhausting the whole database and taking the API down with it.
2. Per-role settings replace per-connection SET. This matters a lot once PgBouncer's transaction mode enters the picture, because SET doesn't survive there. ALTER ROLE ... SET is applied when the server connection starts, so it works through the pooler.
3. Visibility. Combined with application_name, you can finally answer "who is using my connections?" in one query:

SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY count(*) DESC;

If that query shows 180 connections in state idle and 6 in active, you've just learned that your database isn't busy, it's crowded. That's the signal for the next step.

When the math doesn't close: PgBouncer

At some point the budget stops working. You've got too many services, too many replicas, or serverless functions that each want their own connection, and the sum of pools is fundamentally bigger than what Postgres should hold. That's when you put a pooler between the apps and the database.

PgBouncer is the standard answer. It's lightweight (the docs cite about 2 kB of memory per client connection by default), and its key trick is that client connections and server connections are separate numbers. Thousands of app connections can map onto a few dozen real Postgres backends.

The pooling mode decides how aggressively that mapping happens:

• session (the default): a server connection is assigned for the lifetime of the client connection. Safe, fully compatible, and it barely reduces server connections at all if your app pools hold connections open.
• transaction: a server connection is assigned only for the duration of one transaction, then returned. This is where the multiplexing actually happens, and it's what you want for the "many services, one database" problem.
• statement: returned after every statement. Multi-statement transactions aren't allowed. Rarely what you want.

A transaction-mode config for our hypothetical system might look like this:

pgbouncer.ini

[databases]
; per-database cap on real Postgres connections, across all users
appdb = host=10.0.0.5 port=5432 dbname=appdb max_db_connections=60

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 5000        ; app-side connections PgBouncer will accept
default_pool_size = 20        ; server connections per (user, database) pair
reserve_pool_size = 5         ; extra server conns under load...
reserve_pool_timeout = 3      ; ...after a client waited this many seconds
max_prepared_statements = 200 ; protocol-level prepared statements in txn mode

query_wait_timeout = 30       ; client waiting for a server conn gives up
server_idle_timeout = 600
server_lifetime = 3600

Two things worth pointing out. First, default_pool_size is per user/database pair, so the one-role-per-service move from earlier now gives each service its own isolated server-side pool inside PgBouncer. Second, max_db_connections sits on top as a hard ceiling for the whole database, which is exactly the "one shared budget" idea expressed as config.

The app-side pools don't go away. Each pod still keeps a small pool of connections to PgBouncer, and those can be generous, since a client connection to PgBouncer is cheap. The expensive resource, a real Postgres backend, is now rationed in one place.

What transaction mode breaks

Transaction pooling isn't free, and the PgBouncer docs are blunt about it: clients must not rely on session-level state, because consecutive transactions can land on different server connections. Their compatibility table lists what never works in transaction mode:

• SET / RESET (session settings leak or vanish; use ALTER ROLE ... SET or SET LOCAL inside a transaction instead)
• LISTEN (notifications go to whichever backend you happened to be on)
• WITH HOLD cursors
• SQL-level PREPARE / DEALLOCATE
• Temp tables with PRESERVE ROWS or DELETE ROWS (only ON COMMIT DROP is safe)
• LOAD
• Session-level advisory locks (pg_advisory_lock); the transaction-level pg_advisory_xact_lock variants are fine

The session-level advisory lock one deserves a warning, because it fails silently:

Warning
If a job runner uses pg_advisory_lock() for leader election behind PgBouncer in transaction mode, the lock is taken on one server connection and the "unlock" may run on another. The lock can outlive the job, or another instance can grab a different backend and believe it's the leader. Point those workloads at a session-mode pool or straight at Postgres.

A practical pattern is two PgBouncer databases pointing at the same Postgres: one in transaction mode for request/response traffic, one in session mode (per-database pool_mode is allowed in the [databases] section) for migrations, LISTEN-based consumers, and anything that needs session state.

Prepared statements used to be the deal-breaker

For years, the standard advice was "turn off prepared statements if you use PgBouncer in transaction mode," because a statement prepared on one backend doesn't exist on the next. PgBouncer 1.21 changed that: with max_prepared_statements set to a non-zero value, PgBouncer tracks protocol-level prepares and re-prepares them transparently on whichever server connection the client lands on. Through 1.23 that setting defaulted to 0 (off); since 1.24 it defaults to 200.

The catch: it only covers protocol-level prepared statements, the kind drivers send through the extended query protocol. A literal PREPARE foo AS ... in SQL is still invisible to PgBouncer and still breaks. And if you're on a managed pooler or an older PgBouncer, check the version before assuming it works.

PgBouncer has its own ceiling

PgBouncer is single-threaded. One process uses one CPU core, no matter how big the box is. For most setups that's plenty, but a very busy pooler can become the bottleneck itself. Since 1.12 you can run several processes on the same port with so_reuseport = 1 and let the kernel spread client connections across them, and newer versions add peer_id plus a [peers] section so cancellation requests reach the right process.

Here's the gotcha that brings us right back to the start of this article: each PgBouncer process has its own pools. Four processes with default_pool_size = 20 can open up to 80 server connections per user/database pair, not 20. Run PgBouncer as a sidecar in every app pod and you've rebuilt the original multiplication problem with extra steps. The pooler only centralizes the budget if it's actually central, or if you divide the per-process limits by the number of processes.

Watching the budget

Once the pooler is in place, you've got two queues to watch: the one inside each app (waitingCount in node-postgres, Stat() in pgxpool) and the one inside PgBouncer. Connect to the admin console and run:

SHOW POOLS;

The columns that matter are cl_active (clients linked to a server connection, or idle with nothing waiting), cl_waiting (clients waiting for one), sv_active / sv_idle (server connections in use or parked), and maxwait (how long the oldest waiting client has been waiting). A healthy transaction-mode pool under load looks like a lot of clients, a small number of active server connections, and cl_waiting that blips and drains. A maxwait that keeps climbing means the database-side budget really is too small for the work, or, more often, that some transaction is holding its server connection far too long.

On the Postgres side, the pg_stat_activity query from earlier is still your ground truth. The number you care about isn't count(*). It's count(*) WHERE state = 'active', compared against your core count.

Where this leaves you

If I had to compress all of this into one opinion, it's this: raising max_connections is almost never the fix. It's the database equivalent of widening the highway entrance while leaving the bridge the same width. Postgres 14 made idle connections cheap, so a bigger number won't crash you the way it used to, but it also won't give you more throughput, because throughput is bounded by cores and I/O, not slots.

The playbook, in order of how cheap it is:

1. Count what you've actually got: sum pool_max * max_replicas * surge across every service and compare it to the real budget.
2. Shorten W. Get network calls out of transactions, set idle_in_transaction_session_timeout, and fix nested checkouts. This often shrinks the needed pool by more than any tuning.
3. Set explicit client pool limits and a connection-acquire timeout, and graph the waiting count.
4. Give each service its own role with a CONNECTION LIMIT and per-role timeouts.
5. When the sum still doesn't fit, put PgBouncer in transaction mode in front, with max_db_connections as the single shared ceiling, and route session-dependent workloads around it.

Queues are fine. You're always going to have one somewhere. The whole game is choosing where it lives, and the right answer is in your app or your pooler, where waiting is cheap, not inside Postgres, where every extra waiter makes everyone else slower.

---

Originally published at andriiboyko.com.

Comments

[Launch Gate]

The rolling deploy point is the one that bites in practice. During a deploy, old and new replicas both hold a full pool, so peak connections are roughly replicas times pool size times two for a few minutes. Budgeting against steady state passes review and then fails at 3pm. Two things that help: size each service's max so the sum at deploy peak stays under max_connections minus a reserve for superuser, migrations and monitoring, and make the app close idle connections on SIGTERM instead of waiting for the server to drop them. A per-role connection limit in Postgres also stops one runaway service from eating the shared budget.