PostgreSQL Connection Pools: Budget Across Replicas

PostgreSQL connection pooling is a capacity decision across your entire application fleet. A pool size of ten looks modest inside one process. Across twenty replicas, background workers, and an overlapping deployment, it describes a very different system.

I would start a pool-sizing review by drawing every process that can open a connection. Include scheduled jobs, migration runners, support tools, and old replicas draining during a rollout. The useful number is the maximum plausible total, not the setting in the web application's configuration file.

Calculate a budget before tuning

Consider an illustrative database connection budget of 180, with 30 reserved for administration and non-web workloads. If a deployment may temporarily run 15 web processes, a per-process maximum of ten consumes the remaining 150. These numbers are invented for the example; they are not a PostgreSQL sizing recommendation.

The arithmetic is:

web connection allowance = total budget - reserved allowance
per-process ceiling <= web allowance / peak process count

That ceiling is only a constraint. It is not proof that opening every allowed connection produces good performance. Start with a measured workload and ask how much simultaneous database work the service needs to complete its latency objective.

I would record normal and peak process counts separately. Autoscaling and rolling deployments should use the same budget model. Otherwise the moment designed to improve availability can become the moment every new process competes for database access.

Measure pool wait independently

A request can be slow before its first SQL statement runs. Instrument the interval from requesting a client to receiving one, then measure transaction duration separately. Also track checked-out clients, idle clients, and waiting requests where the driver exposes those measurements.

A proposed diagnosis table looks like this:

These are investigation prompts, not automatic diagnoses. Correlate them with database activity and a request trace before changing the limit.

Make ownership of a checked-out client obvious

The node-postgres documentation requires returning checked-out clients, including after errors, and offers pool.query for individual queries that do not need an explicitly held client. See its pooling guide.

An illustrative ownership pattern is:

const client = await pool.connect();
try {
  return await loadReportInputs(client, tenantId);
} finally {
  client.release();
}

The helper here is application-specific and not implemented in this snippet. It should not retain the client after returning. If it starts a transaction, it must also own commit and rollback behavior; releasing a client does not substitute for resolving the transaction.

For a report that queries several tables and then renders a PDF, I would release the database client before the rendering phase whenever consistency requirements permit. The rendering process should not occupy a connection simply because both activities belong to one HTTP request.

Test an exception in the middle of the helper. Afterward, a new request must still acquire a client. A happy-path load test alone will not expose every release leak.

Understand what an external pooler changes

PgBouncer's session mode assigns a server connection for a client's session. Transaction mode assigns one for a transaction and therefore changes which session features applications can rely on. Its feature table documents compatibility, including the configuration-dependent support for protocol-level prepared plans. Read the PgBouncer feature map rather than repeating a blanket rule about prepared statements.

Before enabling transaction pooling, inventory session-level settings, temporary tables, advisory locks, and notification listeners. I would create a compatibility test that executes the same business operation across repeated transactions and checks that no hidden session assumption survives only by luck.

Also distinguish client connections to the pooler from actual database connections behind it. Lower backend connection counts do not prove that request queues are healthy. A pooler can move the queue to a different layer; the latency still belongs to the user.

Design overload behavior deliberately

Set an acquisition timeout appropriate to the request deadline and decide what the caller sees when no connection is available. In my proposed design, interactive requests fail with a recoverable service error instead of waiting longer than the client can use the result. Background jobs use a separately bounded concurrency policy.

Run a controlled overload experiment: cap the pool, increase incoming work, and observe queue growth and recovery. Then repeat with one deliberately slow operation. The goal is to learn whether one workload can monopolize capacity needed by another.

Use EXPLAIN diagnosis to investigate slow database work before increasing connection limits. The cursor pagination example shows how an endpoint can bound the work it asks the database to perform.

I would raise a pool limit only after explaining where the extra connections come from, what measured bottleneck they address, and how the fleet remains within its budget during a deployment.