Connection Pool Sizing for a Scaled Monolith: Replicas, HikariCP, Postgres

What a PostgreSQL connection actually costs

Connection pool sizing is a budget problem. The database can hold only so many connections before it degrades, and every replica of your monolith draws from that same limit.

PostgreSQL forks a separate backend process for each client connection. Each process has its own private memory for catalog caches, query plans and per-session state. Each one is also a scheduling unit for the operating system, whether it is running a query or sitting idle in a pool. A few hundred mostly-idle backends still take memory and add contention on shared structures, such as the work Postgres does to compute which transactions are visible to a snapshot.

The larger risk is work_mem. It is not a per-connection cap. Each sort or hash operation inside a query can use up to that amount, and hash-based operations can use more. A single complex query can therefore claim several multiples of it. Worst-case memory scales with connection count:

Illustrative estimate (assumed numbers, not a benchmark)

worst-case query memory ≈ active connections × memory-hungry operations per query × work_mem

With 400 connections, 2 operations per query and a 16 MB work_mem, the worst case is 400 × 2 × 16 MB ≈ 12.8 GB. This sits on top of shared_buffers and the operating system's page cache.

Raising max_connections adds no CPU cores and no disk throughput. It only lets more work queue inside the database instead of in your application. When a monolith runs across many replicas, each with its own pool, that queue grows every time someone scales out.

Who should use this sizing method

This method is for teams that run one application, usually a monolith, as several identical replicas against a single PostgreSQL primary, with a connection pool such as HikariCP inside each process. It fits best when the replica count changes: rolling deploys, an autoscaler, or manual scale-outs during busy periods.

You are likely to benefit if you have seen any of these:

• "too many clients" errors during deploys or scale-outs, even though steady-state traffic is fine.
• HikariCP connection acquisition timeouts while the database host is not busy.
• On-call engineers unable to open a psql session during an incident because the connection limit is full.
• Pool sizes that were raised over time to make errors go away, with no record of why each value was chosen.

It matters less if you run a single application instance, or if the database already sits behind a server-side pooler with a fixed server-side pool. In that case you still need the same budget reasoning, but you apply it to the pooler's server connections rather than to each replica. Platforms where instances are short-lived and very numerous, such as function-as-a-service runtimes, usually need a server-side pooler from the start, because per-instance pools multiply too quickly.

How does it work?

The method treats the database's connection limit as a fixed budget and works through it in a set order:

1. Count every consumer from the database's side. Add up the application replicas at their peak count, plus migrations, cron jobs, admin sessions, monitoring and reserved slots, and keep headroom for incident access. The total must fit under max_connections.
2. Cap the application's share by what Postgres can execute concurrently. The database host's cores and storage set how many queries can usefully run at once. Connections beyond that only move the queue into the database.
3. Derive each replica's pool size from the budget. Divide the application's share by the autoscaler's maximum replica count, and configure HikariCP as a fixed-size pool with a short acquisition timeout.
4. Reduce how long each request holds a connection. Keep remote calls and other slow work outside transactions, so a small pool can serve a high request rate.
5. Validate under load. Watch HikariCP's pool metrics and pg_stat_activity together, and use the combination to decide whether to grow the pool, shrink it, or fix the code.

The sections below cover each step in turn.

How the connection budget works: replicas × pool size + everyone else

Count connections from the database's side. Postgres enforces a single ceiling, max_connections, and every client draws from it: each replica's pool, plus everything that isn't the application.

The replica count to use is the peak, not the steady state. During a rolling deploy, new pods open their pools before old pods drain theirs. An autoscaler can add pods at the same moment. For that window, old plus new replicas all hold connections.

Budget: (R_peak × P) + migrations + cron + admin + monitoring + superuser_reserved_connections + headroom ≤ max_connections

Copy old pods (6)          surge pods (3)         everyone else
 [pool 10] x6          [pool 10] x3           migrations, cron,
      \                    |                  admin, monitoring
       \___________________|____________________/
                           v
        +-----------------------------------------+
        | Postgres max_connections = 150          |
        |  used 105 | reserved 3 | headroom 42    |
        +-----------------------------------------+

Illustrative estimate (assumed numbers, not a benchmark)

The headroom does real work. When the application is stuck and leaking connections, the database fills up first. That is also when an engineer needs to open a session to run pg_stat_activity or terminate backends. The reserved slots help only roles with superuser rights. If your on-call engineers connect through a normal role and the budget has no slack, they get "too many clients" errors at the moment they most need access.

Copy ROLLING-DEPLOY PEAK (illustrative estimate, assumed numbers)

 +---------------------------+  +----------------+  +--------------------+
 | old pods x6               |  | surge pods x3  |  | everyone else      |
 | [pool 10] [pool 10] ...   |  | [pool 10] ...  |  | migration      2   |
 | 6 x 10 = 60               |  | 3 x 10 = 30    |  | cron/batch     5   |
 +-------------+-------------+  +-------+--------+  | admin/psql     5   |
               |                        |           | monitoring     3   |
               |                        |           | subtotal      15   |
               |                        |           +---------+----------+
               +-----------+------------+                     |
                           |  app: 90                         |  15
                           v                                  v
 +-----------------------------------------------------------------------+
 | Postgres  max_connections = 150                                       |
 |                                                                       |
 | [ app 90 ][ other 15 ][ reserved 3 ][ headroom 42 ]                   |
 |                                                                       |
 | used: 90 app + 15 other = 105                                         |
 | reserved: superuser_reserved_connections = 3                          |
 | headroom: 42 slots so on-call can still log in                        |
 +-----------------------------------------------------------------------+
   9 x 10 + 15 + 3 + 42 = 150

Illustrative estimate with assumed numbers: at the rolling-deploy peak, 9 replicas with 10-connection pools plus migrations, cron, admin and monitoring use 105 of 150 connections, leaving 3 superuser-reserved slots and 42 slots of headroom for incident responders.

Working backward from what Postgres can execute concurrently

The budget sets an upper bound, but it doesn't tell you how many connections are useful. Each PostgreSQL connection is served by its own backend process. A query only progresses while that process is on a CPU or waiting on I/O it actually needs, so the database host's cores and storage set useful concurrency. Application thread count does not.

A commonly cited starting point is cores × 2 + effective spindles. The idea is that a core can usefully alternate between two backends while one waits on disk, and each independent storage device adds some overlap. Treat it as a hypothesis to load-test, not a rule. Workloads dominated by I/O wait can benefit from more connections. Heavy lock contention or long analytical queries can make even the formula's number too high.

Illustrative estimate: assume an 8-core database host on SSD storage, counted as one effective spindle. The heuristic suggests about 17 active connections across all replicas. An application server with 200 worker threads does not need 200 connections, because most of those threads are parsing requests, rendering responses or waiting on other services. If 200 queries reach Postgres at once, backends compete for CPU, locks and buffer access. Each query gets slower, and total throughput often drops. With a small pool, requests wait briefly in the application's queue, and the queries that do run finish faster. Tail latency is usually better as a result.

The cost is that small pools handle bursts poorly. A sudden spike can fill the queue and hit acquisition timeouts while the database sits mostly idle.

How to implement it: configuring HikariCP for a fixed budget

Start from the budget, not from a default. Divide the connections left for the application by the largest replica count the autoscaler can ever reach, not by today's count:

maximumPoolSize = floor((max_connections − reserved − other clients) / max replicas)

Illustrative estimate (assumed numbers, not a benchmark): with max_connections = 200, 20 reserved for superuser and admin sessions, and 30 for migrations, cron jobs and BI tools, 150 remain. With an autoscaler ceiling of 10 replicas, each pool gets floor(150 / 10) = 15.

Set minimumIdle equal to maximumPoolSize. The pool then opens all 15 connections at startup and keeps them, so a traffic spike never pays for TCP, TLS and authentication handshakes. Set connectionTimeout to a few hundred milliseconds to a couple of seconds rather than leaving the 30-second default. When the pool is exhausted, a short timeout returns an error that the caller can shed or retry. A long timeout parks request threads while they wait, and those threads keep holding memory and upstream sockets.

Pin the autoscaler's maximum replica count to the number in the formula. If you raise that maximum without recomputing the pool size, a scale-out can exceed max_connections, and new replicas fail during startup. You then have two choices. You can cap elasticity, which means the service cannot absorb load past the ceiling. Or you can shrink every pool so more replicas fit, which leaves each replica more exposed to the burst-timeout problem described in the previous section.

Copyimport com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public final class DataSourceFactory {

    // Budget inputs (illustrative values; keep in sync with Postgres and the autoscaler)
    private static final int MAX_CONNECTIONS = 200;  // Postgres max_connections
    private static final int RESERVED = 20;          // superuser/admin sessions
    private static final int OTHER_CLIENTS = 30;     // migrations, cron jobs, BI tools
    private static final int MAX_REPLICAS = 10;      // must equal the autoscaler's max replicas

    public static HikariDataSource create() {
        // Size each pool for the autoscaler ceiling, not for today's replica count
        int poolSize = (MAX_CONNECTIONS - RESERVED - OTHER_CLIENTS) / MAX_REPLICAS; // 15

        HikariConfig config = new HikariConfig();
        config.setPoolName("app-pool");
        config.setJdbcUrl("jdbc:postgresql://db.internal:5432/app");
        config.setUsername("app_user");
        config.setPassword(System.getenv("DB_PASSWORD")); // never hard-code secrets

        // Fixed-size pool: open every connection at startup and keep it,
        // so spikes never pay TCP/TLS/auth setup cost
        config.setMaximumPoolSize(poolSize);
        config.setMinimumIdle(poolSize);

        // Fail fast when the pool is exhausted instead of the 30 s default;
        // value is in milliseconds (HikariCP rejects anything below 250)
        config.setConnectionTimeout(1_000);

        return new HikariDataSource(config);
    }
}

A HikariCP setup that derives maximumPoolSize from the connection budget divided by the autoscaler's maximum replica count, sets minimumIdle equal to it for a fixed-size pool, and uses a one-second connectionTimeout so callers fail fast.

When a bigger pool is the wrong fix: long transactions and remote calls

Both of those options assume the time each request holds a connection is fixed. Often it isn't, and the cheaper fix is in the code.

A pool's required size is roughly the request rate multiplied by how long each request holds a connection. Most of that hold time is usually not spent executing SQL. A handler opens a transaction, reads a row, calls a payment provider over HTTP, publishes a message, then writes and commits. For the whole round trip, the connection sits idle in a transaction. Postgres keeps the backend process, its locks and its snapshot alive. The snapshot also holds back vacuum.

Illustrative estimate: assume 200 requests per second and 5 ms of real query time per request. That needs about 1 connection busy at any moment. Add a 150 ms remote call inside the transaction and the figure rises to about 31. The database is doing the same work. The pool just looks starved.

The fix is to move the remote call outside the transaction. Read what you need and commit. Make the call. Then open a short second transaction to record the result, and use a status column or idempotency key to handle a failure between the two steps. For message publishing, write the event to an outbox table inside the transaction and let a separate process publish it.

Raising the pool size and max_connections instead adds backend processes that each consume memory. More processes compete for CPU, which increases context switching and lock contention. That slows every client on the database, including the ones that were already behaving well.

Copy// BEFORE: the pooled connection stays idle in a transaction for the whole HTTP round trip.
void chargeOrderBefore(long orderId) throws SQLException {
    try (Connection c = dataSource.getConnection()) {
        c.setAutoCommit(false);
        BigDecimal amount = loadAmount(c, orderId);            // ~5 ms of SQL
        String ref = payments.charge(orderId, amount);         // ~150 ms remote call, locks and snapshot held
        markPaid(c, orderId, ref);
        c.commit();
    }
}

// AFTER: two short transactions, with the remote call made while no connection is held.
void chargeOrderAfter(long orderId) throws SQLException {
    BigDecimal amount;
    try (Connection c = dataSource.getConnection()) {
        c.setAutoCommit(false);
        amount = loadAmount(c, orderId);
        setStatus(c, orderId, "PAYMENT_PENDING");            // a recovery job can find orders stuck here
        c.commit();
    }                                                          // connection returns to the pool here

    // The idempotency key makes a retry after a crash safe. The provider will not charge twice.
    String ref = payments.charge(orderId, amount, "order-" + orderId);

    try (Connection c = dataSource.getConnection()) {
        c.setAutoCommit(false);
        markPaid(c, orderId, ref);                             // sets status = 'PAID' and stores the provider reference
        c.commit();
    }
}
// loadAmount, setStatus, markPaid: plain PreparedStatement helpers (omitted).
// payments: placeholder HTTP client for the payment provider.

The same payment flow written two ways: the first holds a HikariCP connection open in a transaction across the HTTP call, and the second commits before the call and records the result in a second short transaction, using a status column and an idempotency key.

Validating the sizing: what to measure on both sides

Sizing math gives you a starting number. A load test tells you whether it holds, but only if you watch the pool and the database at the same time.

Application side

From each replica's HikariCP pool, collect:

• Active connections: borrowed and currently in use.
• Idle connections: open but unused.
• Pending threads: request threads waiting for a connection.
• Acquisition time: how long a thread waits to get a connection.

HikariCP exposes the counts through its pool MXBean and through common metrics libraries. Acquisition time is the most useful signal because it shows queueing directly. A healthy pool hands out connections in near-zero time even under peak load.

Database side

Group pg_stat_activity by state, filtered to your application's database: SELECT state, count(*) FROM pg_stat_activity WHERE datname = current_database() GROUP BY state;. The states that matter are active (executing), idle (held by a pool, doing nothing) and idle in transaction (a transaction is open but no query is running).

Reading them together

• Pending threads rise and Postgres active is low: the database has spare capacity. Grow the pool if the total budget allows it.
• Pending threads rise and active sits at your CPU-bound ceiling with climbing latency: the database is saturated. A larger pool only deepens its queue. Shrink the pool or reduce the query cost.
• Many idle in transaction sessions: the code holds connections while doing other work. Fix the code, not the pool size.
• Idle stays high on both sides at peak: the pool is oversized. Reclaim that budget.

Each run gives you one of these diagnoses, so each change has a stated reason. You stop adjusting the number until the errors go away.

Copy-- Run repeatedly during the load test (e.g. every few seconds via psql's \watch)
-- alongside the HikariCP active/idle/pending/acquisition-time metrics.

-- 1. Session counts by state for this database.
--    backend_type filter excludes autovacuum, walwriter and other background processes.
--    oldest_in_state shows how long the longest session has been in that state.
SELECT
    coalesce(state, '<unknown>')      AS state,
    count(*)                          AS sessions,
    max(now() - state_change)         AS oldest_in_state
FROM pg_stat_activity
WHERE datname = current_database()
  AND backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

-- 2. If 'idle in transaction' is non-trivial, find the code holding it open.
--    query is the LAST statement the session ran before going idle,
--    which usually points at the code path that forgot to commit or release.
SELECT
    pid,
    application_name,
    client_addr,
    now() - xact_start    AS xact_age,
    now() - state_change  AS idle_for,
    left(query, 120)      AS last_query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state = 'idle in transaction'
ORDER BY xact_start
LIMIT 20;

Two pg_stat_activity queries to run during a load test: one counts client sessions by state with the age of the oldest session in each state, and the other lists the longest-open 'idle in transaction' sessions with their last statement so you can trace them to the code that holds connections.

How this saves engineering time

Most connection-pool trouble costs time through incidents and guesswork, not through the configuration itself. A budget-driven approach removes several recurring sources of both:

• Fewer deploy and scale-out incidents. Sizing pools against the peak replica count, including the rolling-deploy surge, removes a common cause of "too many clients" failures that otherwise appear only under real traffic and get debugged under pressure.
• Incidents stay debuggable. Budgeted headroom means on-call engineers can still connect and inspect pg_stat_activity while the application is misbehaving, instead of first having to free up connections to see what is going on.
• Diagnosis replaces trial and error. Reading HikariCP metrics and pg_stat_activity together points to one of a few specific causes: spare database capacity, a saturated database, code holding connections, or an oversized pool. Each points to a specific change, so engineers stop cycling through pool-size values and redeploys.
• Changes are reviewable. When the pool size is computed in code from max_connections, reserved slots, other clients and the autoscaler ceiling, a change to any input shows up in code review. Someone raising the replica limit sees that the pool size has to change with it, rather than discovering the problem in production.
• Cheaper fixes come first. Finding long hold times, such as a remote call inside a transaction, often resolves pool starvation with a local code change. That avoids work that costs much more effort: raising max_connections, resizing the database host, or introducing new infrastructure.
• A clear trigger for bigger changes. The budget arithmetic shows when per-replica pooling has run out of room, so a decision to add a server-side pooler or read replicas rests on numbers the team has already checked, not on a vague sense that the database is struggling.

Alternatives and when to avoid direct pooling: PgBouncer and read replicas

Per-replica pooling stops being enough when the replica count, not the per-replica demand, drives the total. If autoscaling can double your replicas, replicas × pool size can exceed max_connections even though each pool is correctly sized. A server-side pooler such as PgBouncer fixes this by multiplexing many client connections onto a small set of server connections, so the database sees a stable count regardless of how many application instances exist.

Transaction-mode pooling, the mode that delivers that multiplexing, assigns a server connection only for the duration of a transaction. Anything that assumes the same backend across transactions breaks. A plain SET leaks to whichever client gets that backend next, while SET LOCAL is scoped to its transaction and stays safe. Session-level advisory locks are held by a backend the client no longer owns, while transaction-level advisory locks still work. LISTEN breaks. Prepared statements fail unless your pooler version and configuration explicitly support them. You also add a component to monitor, upgrade and make highly available, plus a network hop on every query.

Read replicas give you a second budget. Separate read and write pools each target their own server with its own max_connections, and you size each one against that server. Replication lag is a correctness problem, not only a performance one. A read issued right after a write can miss that write, so read-your-own-writes paths must use the primary.

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 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.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

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.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• 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.