What is database connection pool sizing, and why is it a cluster-wide budget?

Database connection pool sizing is choosing how many connections each application process may hold open to the database. In a monolith deployed as many web and worker pods, that choice is never local. Every replica runs the same code with the same pool setting, so a value that looks modest in one process gets multiplied by every replica, and by every process inside each pod if you run forked servers or multiple worker processes.

The real quantity is a cluster-wide sum, computed at peak scale:

Total connections = Σ over deployments (max replicas × processes per pod × pool max) + migrations + cron jobs + admin consoles + monitoring agents + replication

Use maximum replicas, not current ones. The autoscaler adds pods precisely when load is highest, and that is also when the database has the least headroom.

Illustrative estimate (assumed numbers, not a benchmark):

  • Web: 20 max replicas × 2 processes × pool max 10 = 400
  • Workers: 10 max replicas × 1 process × pool max 15 = 150
  • Overhead: migrations 5 + cron 10 + admin consoles 5 + monitoring 3 + replication 2 = 25
  • Total at peak: 575

If the database permits 500, this configuration fails during the busiest scale-out, even though it works fine at normal load.

This guide is for teams running one shared database behind autoscaled pods. Doing the arithmetic once, and recomputing it whenever replica limits or pool settings change, prevents recurring "too many connections" incidents. Without it, the same outage tends to come back after every scaling change.

+--------------------------+  +--------------------------+  +---------------+
| Web pods (peak)          |  | Worker pods (peak)       |  | Overhead      |
| 20 replicas x 2 procs    |  | 10 replicas x 1 proc     |  | migrations  5 |
| x pool max 10            |  | x pool max 15            |  | cron jobs  10 |
| = 400                    |  | = 150                    |  | admin       5 |
+------------+-------------+  +------------+-------------+  | monitoring  3 |
             |                             |                | replication 2 |
             |                             |                | = 25          |
             |                             |                +-------+-------+
             |                             |                        |
             +--------------+--------------+------------------------+
                            |
                            v
             +-----------------------------+
             | Cluster-wide sum at peak    |
             | 400 + 150 + 25 = 575        |
             +--------------+--------------+
                            |
                            v
             +-----------------------------+
             | Shared database             |
             | connection limit = 500      |
             | 575 > 500: refused at peak  |
             +-----------------------------+

Illustrative estimate (assumed numbers, not a benchmark): at maximum replicas, each deployment's replicas x processes x pool max plus fixed overhead add up to 575 connections, which is more than the shared database's 500-connection limit.

Who should use a cluster-wide connection budget?

This approach is for any team where more than one process talks to the same Postgres instance and the number of those processes can change without anyone editing the database configuration. In practice that means:

  • Teams running a monolith as separate web and worker deployments against one primary database. The two deployments share a single max_connections ceiling but are scaled and tuned independently.
  • Teams using horizontal autoscaling. If an autoscaler can add replicas, the connection count can grow without a code change or a config review.
  • Teams running multi-process servers (pre-fork web servers, multiple worker processes per container). Each process has its own pool, so the per-pod count is a multiple of the configured pool size.
  • Platform and SRE engineers who own the database and need to tell application teams how many connections they may use.
  • Application developers setting maximumPoolSize or an equivalent setting, who need a number grounded in something other than the library default.

It matters less if you run a fixed, small number of application instances against a dedicated database and the sum is far below the limit. Even then, the arithmetic takes minutes and confirms the margin. Teams already behind a connection pooler such as PgBouncer still need a budget: it applies to the pooler's server-side pool rather than to every application pool directly.

How does connection pool sizing work?

An application connection pool keeps a set of open database connections and lends them to callers. A request thread or job asks the pool for a connection, runs its queries, and returns the connection when it is done. The pool reuses it for the next caller instead of paying the cost of a new TCP connection, TLS handshake, authentication and Postgres backend startup each time.

The pool has a maximum size. When every connection is checked out, new callers wait in the pool's queue. If they wait longer than the configured acquisition timeout, they get an error. Most pools also keep a minimum number of idle connections open so that bursts don't pay connection setup cost, and they retire connections after a maximum lifetime.

On the server side, every pooled connection is a Postgres backend process holding a slot under max_connections, whether it is running a query or sitting idle in the pool. That is why sizing operates at three levels:

  1. Database level: how many queries the server can usefully run at once (the concurrency knee) and the hard ceiling set by max_connections minus reserved slots.
  2. Cluster level: how that capacity is divided between deployments, multiplied out by maximum replicas and processes per pod, plus overhead for migrations, cron, admin and monitoring.
  3. Process level: the pool's maximum size, chosen to match the number of threads or job slots that can hold a connection at once, within the per-process share from level 2.

Sizing works by fixing the number at the top and dividing downward, rather than picking a per-process value and hoping the sum fits. The sections below cover each level in turn: why max_connections is a ceiling, how to compute and split the budget, how to set pool parameters, which multipliers inflate the sum, and which guardrails enforce it.

Why is max_connections in Postgres a ceiling, not a target?

Each Postgres connection is a separate backend process. It carries its own memory for catalog caches, prepared statements and sort or hash buffers sized by work_mem, and a single complex query can claim several multiples of that. An idle connection still costs memory and a process slot. An active one competes for CPU, locks and I/O.

Throughput usually peaks at a modest number of concurrently executing queries, set by CPU cores and how much time queries spend waiting on disk. Past that point, extra active backends add context switching, lock contention and cache churn without finishing more work. Raising max_connections lets more sessions in, but it does not add capacity. It increases memory pressure, can lower throughput, and means that when overload arrives, hundreds of backends stall together and recovery takes longer.

The usable limit is also smaller than the configured number. superuser_reserved_connections holds slots back for administrators. Monitoring agents, backup and migration tools, logical replication subscribers, cron jobs and engineers' interactive sessions all connect as ordinary clients and use the same slots your pools need.

To see how the slots are actually used, query pg_stat_activity. Filter to backend_type = 'client backend', then count rows grouped by state, usename and application_name. A large idle count means pools are oversized. Persistent idle in transaction sessions mean application code is holding transactions open. Comparing peak active counts with core count shows how close you are to the throughput knee.

-- 1. Headroom: configured ceiling vs. slots actually held by client sessions.
--    Non-superusers are refused once client sessions reach
--    max_connections - superuser_reserved_connections.
SELECT
  current_setting('max_connections')::int                AS max_connections,
  current_setting('superuser_reserved_connections')::int AS superuser_reserved,
  current_setting('max_connections')::int
    - current_setting('superuser_reserved_connections')::int AS non_superuser_limit,
  count(*)                                    AS client_backends,
  count(*) FILTER (WHERE state = 'active')    AS active_now
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- 2. Who holds the slots, and what are they doing?
--    A large idle count suggests oversized pools. Old 'idle in transaction'
--    sessions mean application code is holding transactions open.
SELECT
  state,
  usename,
  coalesce(nullif(application_name, ''), '<unset>') AS application_name,
  count(*)                                          AS sessions,
  max(now() - state_change)                         AS longest_in_state,
  max(now() - xact_start)                           AS oldest_open_xact
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, usename, application_name
ORDER BY sessions DESC;

-- Sample query 1 repeatedly during peak traffic. Compare the highest
-- active_now with the database host's CPU core count to judge how close
-- the workload is to the throughput knee. Compare client_backends with
-- non_superuser_limit to see how much headroom remains.

Two queries against pg_stat_activity: the first compares client sessions with the limit non-superusers can actually reach, and the second breaks those sessions down by state, user and application name to show where idle and long-open transactions come from.

How do you work out a concurrency budget and split it between web and workers?

Start from the database, not the application. A common starting heuristic is roughly (2 × cores) + effective spindles. On SSD or network storage, "spindles" is fuzzy, so treat the result as a first guess. Query mix, lock contention and storage latency move the real number, so confirm it with a load test. Raise total concurrency in steps until throughput stops rising and latency climbs. That knee is your useful concurrency, C.

Next, subtract reserved headroom R for migrations, admin sessions, monitoring, cron jobs and replication. Then divide the remainder across deployments at their maximum replica counts:

per-pod pool = floor(share × (C − R) / max_replicas)

Illustrative estimate (assumed numbers, not a benchmark):

  • Load test knee: C = 50.
  • Reserved headroom: R = 10, leaving 40 connections.
  • Web gets 60% of the remainder (24 connections). At 12 max replicas, that is floor(24 / 12) = 2 per pod.
  • Workers get 40% (16 connections). At 4 max replicas, that is floor(16 / 4) = 4 per pod.
Approach How it works Failure mode
Hard partitioning Fixed share per deployment Stranded capacity when one side is idle
Soft sharing Pools may exceed share; total capped elsewhere A worker burst starves web requests

Hard partitioning is easier to reason about. Prefer it unless you have a global cap, such as a proxy, that enforces the total.

Sizing for max replicas means connections go unused at typical scale, because each pod's pool stays small. Sizing for typical scale lets an autoscaling event push the cluster past the limit at peak, which is exactly when you can least afford connection errors. Size for max and accept some unused capacity.

Within that per-process budget, the pool should match the concurrency inside the process: the number of request threads or worker slots that can hold a connection at the same moment. If the pool is larger than the thread count, the extra connections sit idle while still counting against max_connections. If there are many more threads than connections, requests wait in the pool's queue instead of failing outright. Some of that waiting is fine. A lot of it means the pool has become your real concurrency limit.

The four HikariCP settings that matter most:

  • maximumPoolSize caps the number of connections the process can open. Set it from your budget arithmetic, not from guesswork.
  • minimumIdle keeps warm connections open even when traffic is low. Each warm connection counts against the budget on every replica, so a generous value adds up quickly as you scale out. Setting it equal to maximumPoolSize gives a fixed-size pool with predictable usage.
  • connectionTimeout sets how long a caller waits to get a connection before it fails.
  • maxLifetime retires connections after a set age. Keep it below any idle or session timeout enforced by the database or network path.

Each setting involves a trade-off. A small pool protects the database, but contention moves into the app and shows up as latency. A short maxLifetime creates more reconnect churn. A long one keeps connections to a demoted primary alive longer after a failover.

For web pods, use a short acquisition timeout so overloaded requests fail fast instead of piling up behind the pool. For workers, keep pools strictly bounded to the number of job slots, since background throughput can usually tolerate waiting.

jdbcUrl=jdbc:postgresql://db.internal.example:5432/app
username=app_web
password=CHANGE_ME_PLACEHOLDER

maximumPoolSize=10
minimumIdle=10
connectionTimeout=1000
maxLifetime=1800000

jdbcUrl=jdbc:postgresql://db.internal.example:5432/app
username=app_worker
password=CHANGE_ME_PLACEHOLDER

maximumPoolSize=4
minimumIdle=1
connectionTimeout=30000
maxLifetime=1800000

Two HikariCP property files showing a fixed-size, fail-fast pool for web pods and a small pool bounded to job slots for workers, using standard HikariCP property names.

What hidden multipliers cause 'too many connections' errors?

A pool size that is correct on one pod is a per-process number. The database sees the sum across every process alive at the same moment, and several factors raise that sum without anyone touching the pool setting.

Multiplier Why it inflates the count How to budget it
Processes per container Pre-fork servers and multi-process runtimes give each worker process its own pool Pool size × processes per pod
Autoscaler max replicas Scale-out adds whole pools at peak load, when connections are scarcest Use the autoscaler's maximum, not today's replica count
Rolling deploy surge Old pods hold connections while surge pods open theirs Add surge pods on top of max replicas
Retry storms Clients time out and reconnect while the server still counts the abandoned sessions Keep reserve headroom and cap retries with backoff and jitter
One-off jobs and migrations Cron jobs, migration runners and admin shells each start a default-sized pool Reserve a fixed slice and run them with a pool of one or two

The worst case is the sum of every row at once. A deploy during an autoscaled traffic spike that triggers retries is exactly when that sum occurs, and it is the moment too many connections errors appear.

The budget is only valid while its inputs hold. Recalculate it when you:

  • change autoscaler bounds or deploy surge settings
  • change worker concurrency
  • add a service that shares the database
  • resize the database instance, since memory limits what max_connections can safely be
  • introduce a connection pooler, which changes what "a connection" means on each side

When should you use PgBouncer, and what are the alternatives to bigger pools?

When the budget no longer fits, there are four levers. They fix different problems.

Option What it changes Main cost
Tune app pools only Redistributes a fixed budget Some pods queue for connections
Raise max_connections Raises the ceiling Per-connection memory and contention on the server
PgBouncer, transaction mode Many client connections share few server connections Breaks session-dependent behaviour
Scale up or add read replicas Adds real capacity Spend; replicas need read/write routing and tolerate lag

In transaction pooling, PgBouncer assigns a server connection to a client only for the length of one transaction, then returns it to the pool. Hundreds of mostly idle application connections can share a small server pool, because Postgres only does work while a transaction is open.

Consecutive transactions from one client can land on different backends, so anything that relies on session state breaks:

  • session-level SET commands and temporary tables
  • session-level advisory locks
  • LISTEN/NOTIFY
  • server-side prepared statements, depending on the PgBouncer version and how it is configured

Avoid a pooler when per-query latency is tight, because it adds a network hop. It is also another component that must be highly available, since every query goes through it. It can also hide runaway concurrency. The database stays healthy while requests wait silently in PgBouncer's queue.

Adopting it has a development cost. Code that relies on session state has to change: you replace SET with SET LOCAL, switch to transaction-scoped advisory locks, and give listeners a dedicated direct connection. Account for that work before you treat a pooler as a configuration-only change.

Which guardrails and operational checks keep the connection budget safe?

Give each workload its own database user, such as app_web, app_worker and app_migrations. With separate users, pg_stat_activity shows which deployment holds connections, and Postgres can enforce a cap for each one. Setting ALTER ROLE app_worker CONNECTION LIMIT 60; means a runaway worker scale-up is rejected at login and can't take the slots that web pods need. ALTER DATABASE ... CONNECTION LIMIT does the same for a whole database. Set each role limit to that workload's share of the budget, and keep the total below max_connections minus the superuser reserve and the headroom for admin and replication.

Alert on two signals:

  • Server connection count against the budget. Warn at roughly 80% of each role's limit. This catches drift before logins fail.
  • Pool wait time. HikariCP reports threads waiting for a connection. Steady waiting means the pool is undersized or queries are holding connections too long.

Set HikariCP's connectionTimeout short enough that a starved request fails quickly and returns a clear error. If requests hang until the load balancer times out, they pile up behind the stuck ones and the cause is hard to see.

Change checklist

Recalculate the budget whenever any of these change:

  • Replica count or HPA min/max limits
  • Worker concurrency or threads per process
  • Processes per pod
  • Database instance size, which changes max_connections

Keep the budget as reviewed configuration in the same repository as the autoscaling settings. Then a pull request that raises maxReplicas also has to update the pool math, and a reviewer can catch an overrun during code review. That is much cheaper than finding it as "too many connections" errors during an incident.

-- Illustrative budget: max_connections = 300 on this instance.
-- Leave room for superuser_reserved_connections, admin sessions and replication.
-- web 160 + worker 60 + migrations 5 = 225, well under 300.

-- One login role per workload. Set credentials separately, never in this file.
CREATE ROLE app_web        LOGIN CONNECTION LIMIT 160;
CREATE ROLE app_worker     LOGIN CONNECTION LIMIT 60;
CREATE ROLE app_migrations LOGIN CONNECTION LIMIT 5;

-- Change a cap when the reviewed budget changes. The limit is checked at login,
-- so existing sessions stay open and new logins over the cap are rejected.
-- Superusers are not subject to role limits, so these must be non-superuser roles.
ALTER ROLE app_worker CONNECTION LIMIT 60;

-- Confirm that the instance ceiling matches the numbers the budget assumed.
SELECT current_setting('max_connections')                AS max_connections,
       current_setting('superuser_reserved_connections') AS superuser_reserved;

-- Alert source: current connections per workload as a share of its cap.
-- Warn at about 80% so drift is caught before logins start failing.
SELECT r.rolname,
       r.rolconnlimit                                        AS conn_limit,
       count(a.pid)                                          AS current_conns,
       round(100.0 * count(a.pid) / r.rolconnlimit, 1)       AS pct_of_limit
FROM pg_roles r
LEFT JOIN pg_stat_activity a ON a.usename = r.rolname
WHERE r.rolname IN ('app_web', 'app_worker', 'app_migrations')
  AND r.rolconnlimit > 0          -- -1 means unlimited, so skip those roles
GROUP BY r.rolname, r.rolconnlimit
ORDER BY pct_of_limit DESC;

This example creates a separate Postgres role for each workload, caps each role at its share of an illustrative connection budget, and queries pg_stat_activity to report each role's current connections as a percentage of its cap for an 80% alert.

How can a connection budget save engineering time?

Most of the time spent on connection exhaustion is spent during and after incidents: someone gets paged, finds too many connections in the logs, works out which deployment opened the extra sessions, raises a limit or restarts pods, and then repeats the investigation the next time scaling settings change. A written budget moves that work earlier, where it is cheaper.

  • Problems are caught in review instead of in production. When the budget sits next to the autoscaler and pool configuration, a change that raises maxReplicas, processes per pod or maximumPoolSize comes with arithmetic a reviewer can check in a few minutes. An overrun becomes a review comment instead of an outage.
  • Diagnosis is faster. Per-workload roles and application_name mean pg_stat_activity tells you immediately which deployment holds the connections. Without them, engineers spend incident time correlating pod counts with session counts by hand.
  • Failures are contained. Role connection limits stop one workload's runaway scale-up at login. Web traffic keeps working while the worker problem is fixed, which shrinks both the impact and the number of people pulled into the response.
  • Fewer repeated debates. A shared formula and an agreed split between web and workers replace ad hoc decisions about pool sizes each time a service is tuned. New services get a share from the remaining budget rather than a guess.
  • Less reactive tuning of the database. Raising max_connections under pressure often causes the memory and contention problems described above, which then need their own investigation. Knowing the real concurrency knee keeps that knob where it belongs.
  • Clearer capacity decisions. When the budget is full, the options table above gives a concrete choice: redistribute, add a pooler and pay its migration cost, or add database capacity. The team can weigh that choice deliberately instead of discovering it mid-incident.

The upfront cost is a load test to find the concurrency knee, a short budget document, separate roles and two alerts. After that, the ongoing cost is recalculating the sum when one of the inputs on the change checklist moves.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to database connection pool sizing, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.