Connection Pooling: Stop Burning Postgres Connections

FATAL: sorry, too many clients already is one of those errors that never fires during development. It waits until you've scaled to three replicas, added a cron worker, and shipped a traffic spike — then everything that touches the database starts failing at once. The fix is connection pooling, and the reason it works comes down to one fact about PostgreSQL that surprises people the first time they hear it.

Why Postgres connections are expensive

Every PostgreSQL connection is a full operating-system process, forked by the postmaster. Not a thread, not a lightweight coroutine — a process, with its own memory for sort buffers, hash tables, and catalog caches. Each one costs real RAM (commonly estimated in the single-digit megabytes, more once work_mem gets exercised by real queries), and the planner and lock manager do more coordination work as the process count grows.

That's why max_connections defaults to a modest 100 and why cranking it to 5000 doesn't make your database faster — it makes it slower, because a database serving 5000 processes spends its time context-switching instead of executing queries. The PostgreSQL docs on connection settings are worth a read before you touch that knob: https://www.postgresql.org/docs/current/runtime-config-connection.html

The counterintuitive truth: your database has a maximum useful concurrency determined by CPU cores and disk, and it's far lower than you think. The HikariCP project's pool-sizing analysis is the classic reference here — their rule of thumb is roughly connections = (core_count × 2) + effective_spindle_count, which for a typical small instance lands somewhere around 10-20 *total* useful connections: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

Everything past that number isn't parallelism. It's a queue wearing a parallelism costume.

Layer 1: the application-side pool

The first pooling layer lives in your app. Instead of opening a connection per request, you hold a small set of long-lived connections and check them out per query. In Node with pg:

import pg from 'pg'

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // hard cap for THIS process
  idleTimeoutMillis: 30_000,     // release idle connections
  connectionTimeoutMillis: 5_000 // fail fast instead of hanging
})

export const query = (text, params) => pool.query(text, params)

Three settings matter more than the rest:

• max — the cap per process. This is the number you'll multiply later, so keep it small. 5-10 is plenty for most web workloads.
• idleTimeoutMillis — without it, a pool that spiked to max during a burst holds those connections forever.
• connectionTimeoutMillis — when the pool is exhausted, you want a fast, loggable error, not requests hanging until the client gives up.

Every serious database library has the same knobs under different names: HikariCP's maximumPoolSize, SQLAlchemy's pool_size + max_overflow, Go's SetMaxOpenConns. The node-postgres pooling docs cover the rest of the API: https://node-postgres.com/features/pooling

The multiplication problem

Here's where teams get burned. The app-side pool caps connections *per process* — but production runs many processes.

connections used = replicas × pool max (+ every sidecar job that touches the DB)

Say your API runs 4 replicas with max: 10. That's 40. Add a background worker with its own pool of 10 — 50. A nightly cron opens 5 more. A migration needs one. Someone opens psql to debug. You're at 57 against a limit you probably thought was comfortable.

Now add autoscaling: your HPA doubles replicas under load, exactly when the database is already busiest, and the connection count doubles with it. Serverless makes this pathological — every function instance is its own "process" with its own pool, and a traffic spike can mint hundreds of instances in seconds.

Do this arithmetic *before* the incident. Concretely: on PandaStack, a free-tier managed database has a 50-connection limit, Pro allows 300, and Premium 1000. Three free-tier replicas with max: 10 uses 30 of 50 — fine, with headroom for migrations and a debugging session. Ten replicas at max: 10 does not fit, and no amount of retry logic will fix arithmetic.

Always leave headroom: Postgres reserves a few connections for superusers, and you will eventually need to get into the database *during* the incident. A pool sized to consume 100% of the limit locks you out of your own emergency exit.

Layer 2: PgBouncer-style server-side pooling

App-side pools can't coordinate across processes — replica A doesn't know replica B exists. A server-side pooler like PgBouncer sits between all your app processes and Postgres, accepting thousands of cheap client connections and funneling them through a small set of real ones.

A minimal config:

[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

Your apps connect to PgBouncer on 6432 as if it were Postgres; Postgres only ever sees 20 backends. The magic — and the sharp edges — live in pool_mode:

• session: a client keeps a real connection for its whole session. Fully compatible with every Postgres feature, but barely pools — mostly it just makes connect/disconnect cheap.
• transaction: a client borrows a real connection only for the duration of each transaction. This is where the big multiplexing wins come from, and it's the mode most people mean by "PgBouncer-style pooling."
• statement: borrows per statement. Breaks multi-statement transactions; niche.

Transaction mode's compatibility trade-offs are the part you must actually read before enabling it. Because consecutive transactions from one client may run on *different* backend connections, anything that assumes session state breaks: session-level SET commands, advisory locks held across transactions, LISTEN/NOTIFY, temp tables that outlive a transaction. Protocol-level prepared statements were historically broken too — PgBouncer added support for tracking them in transaction mode in version 1.21, but you need max_prepared_statements configured and a recent version. The feature matrix in the official docs is the authoritative list: https://www.pgbouncer.org/features.html

If your ORM leans on session state, either fix those call sites or run session mode and accept the smaller win.

Sizing: smaller than your instincts say

Pool sizing has one governing idea: queue in the pool, not in the database. Fifty queries arriving at a 10-connection pool means 40 wait briefly in application memory — cheap, bounded, observable. Fifty queries arriving at a database with 50 backends on 4 cores means the database itself thrashes, and *every* query gets slower, including the ones that were fine.

A sane starting point for a small-to-mid Postgres instance:

• Total real connections (PgBouncer default_pool_size, or sum of app pools): 15-25
• Per-process app pool max: 5-10
• Then load test and adjust on evidence, not vibes.

Teams that drop their pool from 100 to 20 routinely see throughput go *up*, because the database stops context-switching and starts finishing queries. It feels wrong. The benchmarks in the HikariCP write-up show why it isn't.

Monitoring: catch it before the FATAL

One query tells you where you stand:

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state
ORDER BY count DESC;

Watch two signals: total count creeping toward your limit, and a pile of idle in transaction sessions — those are connections an app checked out, started a transaction on, and then wandered off to await an HTTP call. They hold locks and a pool slot while doing nothing, and they're almost always an application bug (do the slow work *outside* the transaction). Setting idle_in_transaction_session_timeout gives you a backstop.

The short version

1. 1Postgres connections are processes. Useful concurrency ≈ cores × 2, not max_connections.
2. 2Cap every app pool, then multiply by replicas — the total must fit under your limit with headroom for migrations and humans.
3. 3When replicas multiply beyond that, put PgBouncer in transaction mode in front, and read the compatibility list first.
4. 4Queue in the pool, not the database. Smaller pools are usually faster.
5. 5Watch pg_stat_activity before it watches you.

If you'd rather start with a managed Postgres that's wired into your app via DATABASE_URL and has its connection limit stated up front, you can spin one up at https://pandastack.io.