Blog Details

blog
about

The Connection Pool Is Your Bottleneck, Not Your Database

The Connection Pool Is Your Bottleneck, Not Your Database

Most connection pool incidents get diagnosed backwards. The alert says the database is unavailable. The dashboard shows a database at eleven percent CPU with nothing in pg_stat_activity running longer than forty milliseconds. Both readings are accurate, and together they point at the layer nobody instrumented: the queue of requests waiting for a connection that will never come free.

That gap between an idle database and an application that cannot get work done is the whole story. The database was never the constraint.

Every connection is a process

Postgres does not hand out a lightweight handle when a client connects. It forks a backend. That backend holds a shared memory slot, a private work area, and an entry in the scheduler. On a typical instance it costs somewhere between five and ten megabytes before it has executed a single query.

So max_connections = 500 is not a promise that five hundred clients can query at once. It is a promise that five hundred processes will exist and compete for the same eight cores. Push past a couple of hundred and throughput falls while latency rises, which is the signature of a system spending more time coordinating than working.

The arithmetic people reach for first is max_connections / number_of_app_servers, and it is why so many pools are the wrong size. It sizes the pool against the database's limit instead of against the work. Fifty connections per pod across twelve pods gets you six hundred connections into a database that handles two hundred well, and the first autoscale event to twenty pods finishes the job.

Size the pool to the work

A better starting point comes from queuing arithmetic. Take the arrival rate in transactions per second, multiply by the p95 transaction latency in seconds, and you get the concurrency the workload actually needs. Round up, add headroom, then watch what the CPU does at that level.

If one backend sustains 300 transactions per second at three milliseconds, the required concurrency is under one. Twelve backends at that pace is 3,600 transactions per second, well past what most commerce applications ask for on their best day. This is why the old rule of thumb, roughly cores * 2 + effective_io_concurrency, keeps turning up in tuning guides. Postgres does its best work with a small number of busy backends and gets measurably worse as you add more.

The shape that holds up in production is a small server-side pool behind a pooler, a generous limit on client connections, and a hard cap on what reaches Postgres. That inverts the usual instinct. You want to constrain the pool and let clients wait, because a queue with a timeout fails predictably, and a database accepting a connection it cannot service fails in ways nobody can reproduce.

The queue is the metric nobody collects

PgBouncer's SHOW POOLS reports two numbers that explain most incidents of this kind: cl_waiting, the clients waiting for a server connection, and sv_idle, the server connections sitting unused. The useful information lives in how they combine.

When both are climbing, the pool is short on capacity for the current query mix, or the queries have gotten slower, and the missing index is usually the cheaper fix. When sv_idle and cl_waiting sit at zero with sv_active high, the pool is saturated with real work, so add read replicas or shorten the queries. Zero waiting beside a high idle count means the pool is oversized, which is a line on the bill rather than a bug.

The fourth combination looks impossible and gets blamed on the database. One BEGIN without a matching commit keeps a backend out of rotation for as long as the client leaves it open. Put that inside a request handler that catches an exception and skips the rollback, and you have a leak that grows by one connection per error. The database reports idle connections and no load. The application reports timeouts. Neither graph is wrong.

Two alerts cover most of this: cl_waiting > 0 for more than five seconds, and idle in transaction older than thirty seconds. Pair them with statement_timeout and idle_in_transaction_session_timeout set on the server, so a stuck session dies instead of accumulating.

Transaction pooling takes away session state

Transaction pooling is the standard answer to connection exhaustion in containerized and serverless deployments, and it is the right default. It is also a different contract with the database. In transaction mode, a client's session is a fiction: each transaction may land on a different backend, so anything stored at session scope does not survive.

Named prepared statements are the classic casualty. A statement prepared on backend three gets executed on backend seven, and Postgres answers prepared statement "stmt_7" does not exist. PgBouncer 1.21 and later added protocol-level support through max_prepared_statements, which tracks the named statements a client sends and replays them onto whichever backend it assigns. That setting defaults to zero, so an upgraded pooler does not help until somebody changes the config. It also has nothing to do with SQL-level PREPARE, which still fails.

Other things that break or silently change behavior under transaction pooling:

  • Advisory locks taken for a request and released later in the same request, if a commit lands between them.
  • LISTEN and NOTIFY, which need a stable connection for the notification to arrive.
  • SET statements for search paths, time zones, or statement timeouts, which apply to whichever backend happened to serve the previous transaction.
  • Temporary tables and cursors declared WITH HOLD, both of which vanish with the session.
  • Row level security bound to a role set at connect time.

The fix for these is not to abandon pooling. It is to give the handful of code paths that need session state a direct, unpooled connection, and keep them out of the hot request path. A reporting job that holds a cursor is fine. A checkout handler that acquires an advisory lock across three separate transactions is not, and it will pass every test you run on a single connection.

ORM defaults multiply faster than people expect

Application frameworks ship a connection limit that is reasonable for one process and unreasonable for a fleet. Node's default pool of ten sounds harmless until the same image runs eight pods, a worker deployment, two cron jobs, and a migration container. Add read replicas and the count doubles. Nobody writes that number down anywhere, and it changes on every deploy that touches replica count.

With Prisma in front of PgBouncer, the settings that matter are connection_limit=2 on the URL and pgbouncer=true to turn off prepared statement caching. HikariCP, ActiveRecord, Sequelize, and Django all have equivalents. The specific flag matters less than the principle: each process should hold one or two client connections to the pooler, because the pooler is doing the multiplexing, and a process that opens twenty connections has quietly undone the pooler's entire job.

Connection leaks fail on a timescale that makes them hard to attribute. A pool grows from twenty to twenty-two connections over an hour after a deploy, then plateaus, then trips a limit six days later during a traffic spike. The deploy that caused it is buried in a release list. Instrument pool checkout and checkin counts per route and the next one takes minutes to find.

Serverless made the per-invocation pool real

A function platform will happily run a thousand concurrent invocations, and each one that opens a database connection on cold start produces a thousand connection attempts in a burst. Postgres handles that about as well as you would expect. The connection is more expensive to create than the query is to run, and the authentication handshake burns CPU on the backend before any work happens.

The managed answer is a proxy in front of the database, whether that is RDS Proxy, Cloud SQL's connector, or Hyperdrive. The application-side answer is to initialize the client outside the handler so it survives between invocations, cap the per-instance pool at one or two, and never open a connection inside a request. Both are cheap. The default behavior, which is opening a fresh connection per invocation, is the one that produces the outage.

What to change first

Run one pooler per database cluster instead of one per application. Each application gets its own database entry and its own small pool, and the total reaching Postgres stays inside a number you chose on purpose. That single change removes the coordination problem between teams that never talk to each other.

Then set the two timeouts on the server. statement_timeout at something a real user would tolerate, and idle_in_transaction_session_timeout at thirty seconds. Then make the request timeout shorter than the pool checkout timeout, so a request gives up before it starts waiting for a connection it will never get. Load test at peak concurrency rather than average, because pool exhaustion is a tail event and averages hide it completely.

Finally, put pool wait time on the same dashboard as query latency. The two numbers move together, and the ratio between them tells you whether the fix belongs in the query or in the configuration. Teams that skip this step keep resizing pools to compensate for slow queries, and each resize buys a month.

The uncomfortable part

Almost every pool exhaustion incident on a well-built system is a slow query wearing a costume. Adding connections raises the concurrency a bad query can run at, which makes the database slower, which makes the query slower, which makes the pool look smaller. The loop is stable right up until it is not.

The teams that avoid it treat the pool as a fixed budget with an owner, not a number that gets bumped when a page fires. That is a smaller change than it sounds, and it is the difference between a database that degrades and one that disappears.

Send Us a Message