Database Connection Pool Sizing for Monoliths Under Heavy Load

What a connection pool is and why more connections hurt under load

A connection pool keeps a fixed set of open database connections and lends them to application threads on demand. A thread borrows a connection, runs its queries, and returns it, so the next request skips the setup cost. That cost is substantial: each new connection needs a TCP handshake, often TLS negotiation, and authentication. In PostgreSQL, the server also forks a dedicated backend process for every connection. Each process has its own memory, including catalog caches and any work_mem allocated for sorts and hashes during queries.

Pooling also caps concurrency, and that cap does most of the work under load. A database has a fixed number of CPU cores and a fixed amount of disk I/O bandwidth. Once those are saturated, an additional active connection does not add throughput. It adds context switching between backend processes, more contention on locks and shared buffers, and longer queues inside the database. Every query slows down, connections are held longer, and application threads wait longer to borrow one.

The right pool is usually smaller than people expect. Adding connections to fight acquisition timeouts usually makes those timeouts worse. Raising max_connections on the server only moves the queue from the application to the database, and it adds memory overhead for every backend process.

This guide is for backend and platform engineers running a horizontally scaled monolith, where many identical instances each hold their own pool against a single primary database.

How to size the pool: a global budget, core-count heuristics and Little's Law

Treat the pool size as a share of one global budget, not a per-service setting. Every connection that can exist at the same moment counts against the database's max_connections. That includes each app instance's pool, background workers, migration runners, admin consoles, monitoring agents and replication slots. Size for the worst case, which is the maximum instance count your autoscaler allows. Sizing for the typical count fails exactly when traffic spikes and new instances try to connect.

Illustrative estimate (assumed numbers, not a benchmark)

Formula: per_instance_max = floor((max_connections − reserved) ÷ max_instances)

• Assumed: max_connections = 200, reserved for ops, jobs and migrations = 20, 12 instances.
• Steady state: (200 − 20) ÷ 12 = 15 per instance.
• If autoscaling can reach 20 instances: 180 ÷ 20 = 9 per instance.

The budget sets a ceiling. It does not tell you the right size. Two checks help with that:

The core heuristic is a commonly cited starting point that you should validate under load, not trust as a formula. It describes how many queries the database can usefully run at once across all clients, which is far fewer than the 180-connection budget. Little's Law applies the same check from the traffic side: if roughly 24 connections are busy at peak, 180 open ones mostly sit idle and consume memory.

Both checks assume CPU- or IO-bound queries on dedicated hardware. Managed or shared instances, network round-trips inside transactions and lock contention all lengthen hold time and shift the answer.

Implementing it in HikariCP: the settings that matter

Once you have a number, encode it as maximumPoolSize and set minimumIdle to the same value. HikariCP's maintainers recommend this fixed-size pool. With a fixed pool, latency is predictable because every connection is already authenticated and warm. The cost is that idle connections keep holding per-connection memory on the database during quiet periods. An elastic pool releases that memory but pays the TCP, TLS and authentication setup cost when traffic climbs, which is the worst moment to pay it.

connectionTimeout is your backpressure signal. It controls how long a thread waits for a connection before HikariCP throws. On its own it does not protect you. If request threads keep arriving while earlier ones block for the full timeout, the server's thread pool fills and the whole app stops responding, including health checks. Pair it with an upstream request deadline shorter than the timeout, or shed load when pool wait time rises, so excess work fails fast instead of queueing.

Set maxLifetime a margin below the shortest idle or connection cutoff enforced by the database, firewalls, NAT gateways or load balancers. If you set it too long, connections die silently and the next query fails. If you set it too short, the pool churns constantly, and if many connections expire together, a burst of reconnects shows up as a latency spike.

Enable leakDetectionThreshold in staging, and in production at a value well above normal hold time. The stack traces it logs usually point to code that holds a connection across an HTTP call or other slow non-database work.

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

public final class DataSourceFactory {

    public static HikariDataSource create() {
        HikariConfig config = new HikariConfig();
        config.setPoolName("orders-db");
        config.setJdbcUrl("jdbc:postgresql://db.internal.example:5432/orders");
        config.setUsername("orders_app");
        config.setPassword(System.getenv("ORDERS_DB_PASSWORD")); // never hardcode secrets

        // Fixed-size pool: the number from your sizing exercise, with min == max
        // so every connection is already open and authenticated before load arrives.
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(20);

        // Backpressure: fail a borrow after 2s instead of letting threads queue.
        // The upstream request deadline should be shorter than this, or load
        // shedding should start as pool wait time rises.
        config.setConnectionTimeout(2_000);

        // Assume the shortest cutoff on the path (DB, firewall, NAT, LB) is 30 min.
        // Retire connections a margin below it so none are silently dropped.
        // HikariCP adds a small per-connection variance so expiries don't line up.
        config.setMaxLifetime(25 * 60_000L);

        // Log a stack trace for any connection held longer than 10s, which is far
        // above normal hold time. Typical culprit: an HTTP call made mid-transaction.
        config.setLeakDetectionThreshold(10_000);

        return new HikariDataSource(config);
    }
}

A fixed-size HikariCP pool with a short connection timeout for backpressure, a maxLifetime below an assumed 30-minute network cutoff, and leak detection for connections held across slow non-database work.

Shorten connection hold time before you grow the pool

I/O. Little's Law shows why this matters: the connections in use equal the request rate multiplied by the hold time. If you cut hold time, the pool you need shrinks in proportion. That is usually a code change, not a database upgrade.

Illustrative estimate (assumed numbers, not a benchmark): connections ≈ requests/s × hold time (s). At 200 requests/s, each request runs 40 ms of SQL and waits 160 ms on a payment API inside the transaction. That needs 200 × 0.2 = 40 connections. If the API call moves out of the transaction, the same traffic needs 200 × 0.04 = 8.

Common patterns that inflate hold time:

• Remote calls inside a transaction. HTTP or RPC latency becomes connection time.
• Long transactions. Computation, file I/O or retries between statements keep the connection checked out.
• ORM lazy loading. Queries fire late, so the session and its connection stay open longer than the code suggests.
• Open-session-in-view. The connection is held for the whole request, including serialization and rendering.

The accompanying example splits one transactional method into three steps: a short read transaction, the remote call with no connection held, and a short write transaction. The write must re-check state, for example with a version column, because the data can change during the call.

Separate pools for batch and reporting

Give batch or reporting jobs their own small pool when they share the process with request traffic and hold connections for seconds or minutes. A slow job then queues behind its own pool and cannot starve user-facing requests. The cost is a fragmented budget. Both pools count against the database's connection limit, and the batch pool can sit idle while the request pool starves. Keep the batch pool small and schedule heavy jobs off-peak.

Copyfrom psycopg_pool import ConnectionPool

pool = ConnectionPool("postgresql://app:changeme@db.internal/shop", max_size=10)

# BEFORE: the connection stays checked out for the whole payment API call.
def checkout_before(order_id):
    with pool.connection() as conn:  # commits and returns the conn on block exit
        row = conn.execute("SELECT total FROM orders WHERE id = %s", (order_id,)).fetchone()
        payment_api.charge(order_id, row[0])  # ~160 ms of HTTP while holding a connection
        conn.execute("UPDATE orders SET status = 'paid' WHERE id = %s", (order_id,))

# AFTER: short read, remote call with no connection held, short guarded write.
def checkout_after(order_id):
    # 1. Short read transaction: capture what the call needs, including the version.
    with pool.connection() as conn:
        total, version = conn.execute(
            "SELECT total, version FROM orders WHERE id = %s AND status = 'pending'",
            (order_id,),
        ).fetchone()

    # 2. Remote call. No connection is held, so its latency does not count as hold time.
    #    Use an idempotency key so a retry cannot charge twice.
    payment_api.charge(order_id, total, idempotency_key=f"order-{order_id}-v{version}")

    # 3. Short write transaction: re-check state, since the row may have changed.
    with pool.connection() as conn:
        cur = conn.execute(
            "UPDATE orders SET status = 'paid', version = version + 1 "
            "WHERE id = %s AND version = %s AND status = 'pending'",
            (order_id, version),
        )
        if cur.rowcount == 0:
            # Someone else changed the order during the call: reconcile or refund.
            raise ConcurrentUpdateError(order_id)

# payment_api and ConcurrentUpdateError are placeholders for application code.

A before/after sketch using psycopg 3's connection pool that moves a slow payment API call out of the transaction. The work is split into a short read, the remote call with no connection held, and a short write that uses a version column to detect changes made during the call.

Alternatives and complements: PgBouncer and server-side pooling

Per-instance pools stop adding up once you run many instances. Twenty instances with 15 connections each is 300 server connections. Autoscaling makes the total unpredictable, and every independent pool, such as the batch pool above, adds to it. PgBouncer sits between the application and PostgreSQL and multiplexes many client connections onto a small set of server connections:

[app instance + in-app pool] ×N → PgBouncer → PostgreSQL

Keep the in-app pools, but make them small. They still save each request from opening its own connection to PgBouncer. In the configuration excerpt that follows, max_client_conn caps the client side, default_pool_size caps server connections per database/user pair, and pool_mode selects the multiplexing behaviour.

Session vs. transaction mode

In session mode, a client keeps its server connection until it disconnects. Application pools hold connections open indefinitely, so session mode barely reduces the server count. In transaction mode, the server connection goes back to PgBouncer at COMMIT or ROLLBACK. Idle client connections then cost the database nothing, which is where the large reduction comes from.

Transaction mode breaks anything that assumes the same server connection across transactions:

• session-level state changed with SET
• advisory locks held across transactions
• server-side prepared statements, depending on your PgBouncer configuration and driver

Check your PgBouncer and driver documentation before switching.

Skip PgBouncer when a few instances fit comfortably within your connection budget. It adds a network hop and another process to deploy and monitor. It is also a single point of failure unless you run it redundantly.

Copy; pgbouncer.ini (excerpt)
; Illustrative values: 20 app instances, each with a small in-app pool of 15.

[databases]
; Clients connect to "appdb" on PgBouncer, which forwards to the real server.
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

; Authenticate clients against a userlist file (no secrets inline).
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Return the server connection to the pool at COMMIT/ROLLBACK.
; This produces the large reduction, but it breaks session-level SET,
; advisory locks held across transactions, and possibly server-side
; prepared statements. Check your PgBouncer and driver docs first.
pool_mode = transaction

; Client side: 20 instances x 15 = 300 client connections, plus headroom
; for autoscaling and the batch pool.
max_client_conn = 400

; Server side: the real PostgreSQL connections per database/user pair.
; With one app user, this is the cap on server connections.
default_pool_size = 20

; A few extra server connections that are used only when clients have
; waited longer than reserve_pool_timeout (in seconds).
reserve_pool_size = 5
reserve_pool_timeout = 3

A minimal pgbouncer.ini in transaction mode that accepts up to 400 client connections from many app instances and multiplexes them onto about 20 server connections per database/user pair (illustrative values).

Diagnosing connection exhaustion: undersized pool, saturated database or leak

When requests time out waiting for a connection, the pool is the symptom, not necessarily the cause. Three failure modes look identical from the application log but differ sharply in pool and database metrics, so check both sides before changing anything.

On the pool side, watch active, idle and pending (threads waiting for a connection), plus acquisition wait time. On the database side, group sessions by state with SELECT state, count(*) FROM pg_stat_activity GROUP BY state;. The state that matters most is idle in transaction. It means a connection has opened a transaction and is doing nothing while it holds locks and a pool slot. That usually points to application code doing slow work, such as an HTTP call, mid-transaction or forgetting to commit.

The second row is the trap. Adding connections to a saturated database increases contention and makes every query slower.

Copy-- 1. Sessions by state for the current database (client backends only).
--    Many 'idle in transaction' rows point to a leak or long-held transactions.
SELECT state, count(*) AS sessions
FROM pg_stat_activity
WHERE datname = current_database()
  AND backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

-- 2. Oldest idle-in-transaction sessions: how long each has been
--    holding a transaction (and its locks) while doing nothing.
SELECT pid,
       usename,
       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 xact_start
LIMIT 10;

-- 3. Saturation check: active sessions that are waiting, grouped by wait type.
--    Many waits on Lock, LWLock or IO suggest the database, not the pool, is the bottleneck.
SELECT wait_event_type, wait_event, count(*) AS waiting
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY waiting DESC;

-- 4. Backstop: end sessions idle in a transaction longer than 60s for the app role.
--    This applies to new sessions; it does not replace fixing the leak.
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

PostgreSQL queries to group sessions by state, list the longest idle-in-transaction sessions, check active sessions for wait events that indicate a saturated database, and set idle_in_transaction_session_timeout as a backstop for the application role.

A load-testing procedure for choosing the final pool size

The only reliable way to find the right number is to measure it on hardware that matches production: the same instance class, storage type and replica layout, with a dataset large enough that working-set and cache behaviour are realistic. Replay production traffic or build a load profile from real query mix ratios. A synthetic benchmark that hammers one primary-key lookup will overstate how many connections the database can use productively.

The procedure

1. Fix the pool size high, so the database rather than the pool is the constraint.
2. Step client concurrency up in stages, holding each stage long enough for latency to stabilise.
3. At each stage, record throughput (transactions per second), p99 latency, and database-side CPU, I/O wait and lock waits.
4. Plot throughput and p99 against concurrency. Find the knee: the point where throughput stops rising and p99 starts climbing steeply.

The number of active connections at the knee is the database's useful concurrency. Set the global pool budget at that point or slightly below it, then divide it across instances at the autoscaling maximum. Requests beyond that limit wait in the pool's queue, and HikariCP's connectionTimeout bounds how long they wait. A request that times out fails fast with a clear error. Without the bound, it would add load to a database that is already saturated.

When to re-run

Re-run the test after any of these changes:

• Schema changes or new indexes. These alter per-query cost.
• Hardware or instance-class changes. These move the knee.
• A change in the autoscaling maximum. This changes how the budget divides across instances.

Why it pays off

Smaller pools and shorter transactions often remove the apparent need for a larger database tier, because the extra capacity was being spent on contention rather than work. They also turn vague, system-wide slowdowns into explicit pool-timeout errors. Those errors point directly at the constraint, so engineers spend less time debugging them.

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