Monolith Database Connection Pool Sizing for Web and Workers

What a connection budget is and how to calculate it

A connection budget is the fixed number of server connections your database will accept. Every process that opens a pool competes for that same number, whether it serves HTTP, drains a queue, or runs a one-off script. Postgres enforces the limit with max_connections and rejects connections past it, so the budget is the ceiling. It is not a target to grow into.

The demand side is a sum:

Total = Σ (replicas × processes per replica × pool size) + migrations + cron and one-off tasks + admin consoles + monitoring agents + reserved superuser and replication slots

Pools hold connections open even when idle, so count each pool at its configured maximum, not at its typical usage.

Illustrative estimate (assumed numbers, not a benchmark)

Assumed inputs: 6 web containers × 4 processes × 5 connections, and 4 worker containers × 1 process × 10 connections. Extras total 17: migrations 2, cron 5, consoles 3, monitoring 2, reserved slots 5. max_connections is 200. The worst-case rolling deploy runs a full second set of replicas before the old ones drain.

The budget that fits at steady state fails during every deploy.

The arithmetic is only as good as your inventory. Budgets still run out because of consumers nobody counted, such as a forgotten console session, a BI tool with its own pool, or a migration runner launched from CI.

How pools, processes and the database fit together

Connections in a monolith flow through three layers:

• Processes. Each web server process and each worker process is a separate OS process with its own memory. Its client-side pool lives inside it and cannot be lent to a sibling. Ten web processes with a pool of 5 hold up to 50 connections, even if nine of those processes are idle.
• Server slots. Every one of those connections occupies a backend on the database, and all of them count against the same max_connections limit. Postgres does not distinguish a web connection from a worker connection. It only sees the total.
• An optional server-side pooler. If you add one, it sits between the processes and the database. Application pools connect to the pooler, and the pooler multiplexes them onto a smaller, fixed set of real server connections. The client-side pools still exist. What changes is the number they ultimately consume on the database.

Within one process, size the pool to match the number of threads or concurrent tasks that can issue queries at the same moment. For a threaded server, that is the thread count. For an async worker, it is the concurrency limit.

If the pool is smaller than that, threads queue on checkout while the database sits idle, and that wait shows up as request latency. If it is larger, the extra connections are never checked out. They still hold a backend and its memory, and they still take a slot away from processes that could use it.

CopyWEB (threaded server)                     WORKER (async)
+------------------+ +------------------+ +------------------+
| web 1            | | web 2            | | worker 1         |
| 5 threads        | | 5 threads        | | 10 async tasks   |
| pool: 5 conns    | | pool: 5 conns    | | pool: 10 conns   |
+--------+---------+ +--------+---------+ +--------+---------+
         |                    |                    |
         | 5                  | 5                  | 10
         v                    v                    v
+---------------------------------------------------------------+
| OPTIONAL server-side pooler                                   |
| accepts every client connection (5 + 5 + 10 = 20)             |
| multiplexes them onto a small fixed set of real connections   |
+-------------------------------+-------------------------------+
                                |
                                v  fewer real server connections
+---------------------------------------------------------------+
| POSTGRES  --  max_connections is one shared limit             |
| [#][#][#][#][#][#][#][#][ ][ ][ ][ ][ ][ ][ ] ...             |
| one backend per connection; web and worker look the same      |
+---------------------------------------------------------------+

No pooler: every pool connects directly, so all 20 hold backends.
Pool size per process = threads or concurrent tasks in it:
  smaller -> threads wait on checkout while the database is idle
  larger  -> unused connections still hold a slot and its memory

Each web and worker process keeps its own client-side pool, all pools draw from the single max_connections budget on Postgres, and an optional server-side pooler between them reduces how many real backends those pools consume.

Why web and workers need separate sub-budgets

That slot might be one a web process needed. Web and worker code share a codebase and a database, but they use connections in very different ways.

A shared primary is a fairness problem. A job burst, such as a backfill or a retry storm, can check out connections and hold them. If worker autoscaling adds replicas at the same time, every new replica opens its own pool. Web processes then wait on checkout, and the outage looks like slow pages even though the database is not saturated. Any team running web and background jobs against one primary should split the budget into a web share and a worker share.

Isolation has a cost: it strands capacity. When workers are quiet, their reserved slots sit unused and cannot absorb a web spike. Most teams accept this, because a guaranteed floor for web is worth more than peak utilization.

Queue-depth autoscaling has no natural ceiling, so it needs a hard cap on replicas or per-replica concurrency.

Illustrative estimate (assumed numbers, not a benchmark)

• Usable budget = max_connections − reserved = 200 − 20 = 180
• Web share = pods × processes × pool size = 6 × 4 × 5 = 120
• Worker share = 180 − 120 = 60
• Max worker replicas = worker share ÷ connections per replica = 60 ÷ 10 = 6

Implementing the budget: per-process config, application_name and role limits

Those numbers only hold if every process actually uses them, so don't hard-code a pool size. Read it from configuration per process type, for example DB_POOL_SIZE=5 for web and a different value for workers. Set application_name the same way (web, worker-billing), either through the connection string or the driver's options. The pool size can then change with a deploy rather than a code edit, and every row in pg_stat_activity shows which process opened it.

Configuration is a convention, though, and a scaled-up worker deployment will ignore it. To make the budget binding, give web and workers separate database roles and cap each one with ALTER ROLE ... CONNECTION LIMIT, using the shares above: 120 for the web role and 60 for the worker role. A runaway worker fleet then gets refused at login instead of starving web requests. Superusers bypass these limits, so the application should never connect as one.

Roles can also carry guardrails through ALTER ROLE ... SET. A short statement_timeout on the web role stops one slow query from holding a connection indefinitely. idle_in_transaction_session_timeout kills sessions that opened a transaction and stopped doing work, which is a common reason a pool quietly shrinks. Tight timeouts will also kill legitimate long jobs and migrations. Run migrations under their own role, and let specific jobs raise their limit with SET or SET LOCAL.

Small pools don't remove waiting. They move it from the database into your app, where requests queue for a free connection. Set the pool's checkout timeout low enough that requests fail fast with a clear error, rather than hanging until the load balancer gives up.

Copy-- Separate login roles per process type. Never connect the app as a superuser:
-- superusers bypass CONNECTION LIMIT.
CREATE ROLE app_web     LOGIN NOSUPERUSER PASSWORD 'change-me-web';
CREATE ROLE app_worker  LOGIN NOSUPERUSER PASSWORD 'change-me-worker';
CREATE ROLE app_migrate LOGIN NOSUPERUSER PASSWORD 'change-me-migrate';

-- Hard budget: logins beyond the cap are refused, so a scaled-up
-- worker fleet cannot starve web of connections.
ALTER ROLE app_web     CONNECTION LIMIT 120;
ALTER ROLE app_worker  CONNECTION LIMIT 60;
ALTER ROLE app_migrate CONNECTION LIMIT 2;

-- Guardrails. These apply to new sessions only, so they take effect
-- as pools recycle their connections.
ALTER ROLE app_web SET statement_timeout = '5s';
ALTER ROLE app_web SET idle_in_transaction_session_timeout = '30s';

-- Workers run longer queries, but still must not sit idle in a transaction.
ALTER ROLE app_worker SET statement_timeout = '2min';
ALTER ROLE app_worker SET idle_in_transaction_session_timeout = '60s';

-- Migrations take locks and rewrite tables: no statement timeout.
ALTER ROLE app_migrate SET statement_timeout = 0;

-- A known long job raises its own limit for one transaction only.
-- SET LOCAL reverts at COMMIT/ROLLBACK, so the pooled connection
-- goes back to the pool with the role default.
BEGIN;
SET LOCAL statement_timeout = '30min';
-- ... long-running report or backfill ...
COMMIT;

-- Check usage against the budget. application_name comes from each
-- process's connection settings (e.g. web, worker-billing).
SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE usename IN ('app_web', 'app_worker', 'app_migrate')
GROUP BY usename, application_name, state
ORDER BY usename, count(*) DESC;

Separate PostgreSQL roles enforce the web and worker connection budgets with CONNECTION LIMIT, carry per-role statement and idle-in-transaction timeouts, and let a long job raise its timeout with SET LOCAL; a pg_stat_activity query then shows usage by role and application_name.

Diagnosing pool exhaustion and finding connection hogs

Client-side or server-side?

Exhaustion appears in two places, and the fix depends on which one you have. Client-side exhaustion shows up as checkout or acquire timeouts in the application. The pool is full, Postgres may have spare slots, and requests are waiting on your own process. Server-side exhaustion shows up as refused new connections. Postgres rejects them with errors such as "sorry, too many clients already" because the total across all processes has reached max_connections.

Compare both views

Put the app's pool metrics (in use, idle, waiting) next to pg_stat_activity grouped by application_name and state. If you set application_name per process type, this tells you which tier holds the connections.

Read the result as follows:

• Many active sessions mean the database is busy and probably slow.
• Many idle sessions mean pools are oversized for their load.
• Many idle in transaction sessions mean the application opened a transaction and stopped issuing queries. These sessions keep their connection and can also hold locks.

To find long-running transactions, filter on state = 'idle in transaction' or order by now() - xact_start.

Common hogs

• External HTTP calls inside a transaction. A web request that calls a payment API while holding a connection keeps that connection for the full network round trip, including retries.
• Long job transactions. Workers that wrap a whole batch in one transaction hold a connection for minutes and delay vacuum cleanup.
• Leaked or unreturned connections. An error path that skips release causes a slow, steady drop in available connections that a restart temporarily fixes. Turn on your pool's leak detection or checkout-duration logging to find the code path responsible.

Copy-- 1. Who holds connections? Compare these counts with each app tier's pool metrics.
SELECT application_name, state, count(*) AS conns
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY conns DESC;

-- Headroom against the server-side limit.
SELECT count(*) AS used,
       current_setting('max_connections')::int AS max_conns
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- 2. Sessions sitting idle inside an open transaction (possible locks held).
SELECT pid, application_name,
       now() - xact_start   AS xact_age,
       now() - state_change AS idle_for,
       left(query, 80)      AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC;

-- 3. Longest-running transactions in any state (long job batches show up here).
SELECT pid, application_name, state,
       now() - xact_start AS xact_age,
       left(query, 80)    AS current_or_last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_age DESC
LIMIT 10;

Diagnostic queries against pg_stat_activity that group connections by application_name and state, check usage against max_connections, and list idle-in-transaction sessions and the longest-running transactions.

When raising max_connections is the wrong fix

Raising max_connections usually treats the symptom. In Postgres, each connection is a separate backend process with its own memory for catalog caches, query plans and session state. That cost applies even when the connection is idle. Under load, the bigger risk is work_mem. It is allocated per sort or hash operation, not per connection, so one complex query can use several multiples of it. Multiply that by a few hundred active backends and the server can exhaust RAM and start swapping, or the OOM killer can terminate a backend, which forces Postgres to restart all sessions.

CPU and disk set a harder ceiling. A database can only make progress on roughly as many queries as it has cores and I/O capacity to serve. Connections beyond that point do not add throughput. They wait on locks, compete for buffers and context-switch, so median latency holds steady while p99 climbs. More connections also hide real defects. A leaked checkout or a transaction left open in a worker takes longer to exhaust a larger pool, so the bug surfaces later and is harder to trace.

A modest increase is reasonable when you add a process type or replica count deliberately, the arithmetic from your budget shows a small shortfall, and memory headroom is confirmed. Note that changing the setting requires a server restart. Avoid an increase when exhaustion appears gradually, follows deploys, or coincides with long-running transactions. Size for enough connections to serve real concurrency, not the maximum the server will accept.

Alternatives: server-side poolers, read replicas and separate databases

When the database cannot accept more connections, a transaction-mode pooler such as PgBouncer can let many clients share fewer server connections. A client holds a server connection only for the duration of a transaction, and the connection returns to the pool on commit or rollback. Your budget then splits into two numbers: client connections to the pooler, which can be large, and the pooler's server-side pool, which must still fit under max_connections.

The cost is that consecutive transactions from one client can run on different server connections, so anything that relies on session state can break:

• Plain SET commands, temporary tables and other session-level settings can leak to other clients or disappear between transactions.
• Session-level advisory locks held across transactions may stay on a connection that another client then uses.
• LISTEN/NOTIFY listeners need a dedicated connection.
• Prepared statements may fail, depending on how your driver and pooler are configured.

Verify each of these against your own driver and pooler versions before you switch.

The pooler also adds operational work. It needs its own monitoring and sizing, and it becomes a single point of failure unless you make it highly available. It can also hide which application holds a server connection, so application_name in pg_stat_activity may no longer tell you what you expect.

Send reads to a replica only when the reader can tolerate stale data.

Impact on cost and engineering time

The payoff of an explicit budget shows up during incidents. When connections run out, the on-call engineer can compare current usage against the headroom math and see where it went over. Because every process sets application_name, a single query against pg_stat_activity grouped by that column shows which process type holds the connections. That replaces guessing across logs from several services.

Sizing against autoscaler maximums and deploy overlap prevents the most confusing kind of exhaustion: failures that appear only during a rolling deploy or a traffic spike, then disappear before anyone can look. The worst case to plan for is:

peak connections = Σ (max replicas × processes per replica × pool size) × overlap factor + reserved admin/tool slots

The overlap factor accounts for old and new replicas running side by side during a rollout. If a capacity limit is written down in config, any change to it goes through code review. Someone can approve or reject a larger pool in a pull request before it causes a 3 a.m. page.

These benefits come with ongoing costs. The process inventory goes stale unless someone owns it. Per-role connection limits and per-process pool settings are extra configuration that can drift from what is actually deployed. A server-side pooler is another service you have to run, monitor and upgrade. For a small monolith with a single process type, a comment next to the pool setting may be all the budget you need.

When to revisit the budget

• You add a new process type, such as a scheduler, a new worker queue or a one-off migration runner.
• Autoscaler minimums or maximums change, or a new scaling trigger is added.
• The deploy strategy changes how many old and new replicas run at the same time.
• A new tool connects to the database directly, such as BI dashboards, CDC pipelines or admin consoles.
• Pool size defaults change after a framework or driver upgrade.
• You change the database instance size or max_connections.

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 monolith 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.