Connection Pool Sizing

Connection Pool Sizing

Purpose

Size a database pool from measurement and distinguish insufficient local capacity from database saturation, leaks and long-held connections. Raising maximumPoolSize helps only if measured database headroom can absorb the extra concurrent work within the required SLO.

Workflow

Before applying settings, inspect compiler/runtime, resolved HikariCP, Spring Boot, Hibernate and JDBC driver versions, database engine/version, metrics binding and transaction/connection release mode. No single Java baseline is declared for this diagnostic workflow; the SQL examples target PostgreSQL, and virtual-thread APIs require Java 21+. Preserve target versions. Missing workload or capacity evidence requires a measurement plan and conditional recommendation, not an invented size.

1. Measure W, the mean connection hold time, with hikaricp.connections.usage; inspect p50/p99 separately for tails. Hold time runs from successful checkout until return to the pool, which can include time before the first statement and after commit. Verify the metric adapter; completed-use samples can omit connections still stuck or leaked.
2. Compute mean concurrency L = λ_borrow × mean(W) using completed borrows/second and hold durations from the same pool and stable interval, not query rate or a percentile. Then model a candidate pool against the arrival distribution and latency/error budget. A fixed 1.5× margin is a starting hypothesis, not a sizing law.
3. Establish the database-side budget with the database owner: reserved administrative connections, total application instances, workload classes, CPU saturation, storage latency, lock pressure, and failover topology. The familiar cores × 2 + spindles expression is a benchmark heuristic, not a portable ceiling. If demand exceeds the measured safe budget, reduce hold time or arrival rate before adding concurrency.
4. Set the timeouts and lifetimes deliberately (see Rules).
5. On an incident, check in order: pending and acquire p99 (is it the pool?), usage p50 versus p99 (does a minority hold connections far too long?), pg_stat_activity for long idle in transaction sessions (what code is holding a transaction open while no statement runs?), then pg_stat_statements ordered by total_exec_time and by calls — the second reveals N+1.

Rules

• At a fixed pool size, halving mean hold time doubles the ideal borrow-service capacity only if the workload and other bottlenecks remain unchanged. Confirm useful throughput and database health rather than treating that arithmetic as a measured end-to-end speedup.
• Pool waiting grows non-linearly near saturation, but a real database pool is not automatically M/M/c: arrivals may be bursty, hold times heavy-tailed, transactions correlated, and the database itself slows as concurrency rises. Use Erlang-C only as an explicit approximation and validate candidate sizes with production distributions or a representative load test.
• Never equate the pool to the container's thread count. Threads waiting for a connection can be intentional backpressure, but their wait must still fit the request deadline. Across instances, pool maxima must fit the database's configured connection budget; do not assume a vendor default or managed-service limit.
• Avoid connection-timeout=0, which HikariCP treats as effectively unbounded. Its default is 30,000 ms and its accepted minimum is 250 ms. Choose a finite value inside the caller's remaining deadline and validate the resulting rejection behaviour. Failing fast is what enables a circuit breaker, backoff retry and a degraded response — a long wait converts partial saturation into total unavailability. This timeout is pool-wide and covers acquisition, not query execution or the whole request. For shorter remaining budgets than the supported minimum, use deadline-aware upstream admission and a client cancellation policy; do not assume setting a sub-minimum value works.
• Set max-lifetime a few seconds below an enforced database or infrastructure connection lifetime when one exists; do not derive it from unrelated idle or request timeouts. Firewalls, load balancers and NAT gateways may drop idle TCP connections; pool keepalive, driver/OS TCP keepalive, validation, and JDBC socket timeouts cover different parts of that failure. HikariCP's maxLifetime default is 1,800,000 ms (30 min), and 0 means infinite. Current HikariCP defaults keepaliveTime to 120,000 ms; older releases and some integrations used 0. Verify the resolved version and effective configuration. Driver/OS TCP keepalive and JDBC socket timeouts address different failure modes. In-use connections are retired after return, so maxLifetime is not protection against an infrastructure cutoff during a long borrow. Account for hold duration and recovery margin.
• In Spring's default proxy mode, self-invocation such as this.method() bypasses transactional interception. Method visibility support depends on proxy type and Spring version; a separate proxied collaborator or TransactionTemplate makes the boundary explicit. AspectJ weaving has different semantics, so inspect the configured advice mode before diagnosing from source alone.
• idle in transaction says that a transaction is open while the backend is not executing a statement. Correlate application traces and transaction age before attributing the gap to HTTP, messaging, user think time, or business logic. A 300 ms HTTP call inside @Transactional, while a connection is held, contributes at least 300 ms to that borrow. Only if every borrow has that delay is pool_size / 0.3 an ideal upper bound. Old transactions may retain locks or cleanup horizons; inspect isolation, transaction age and backend_xmin rather than assuming every READ COMMITTED transaction holds one snapshot throughout.
• N+1 can consist of individually fast or slow statements and costs both round trips and database work. High pg_stat_statements.calls is a lead, not proof: correlate statement-count deltas with one endpoint invocation and result cardinality. Choose fetching/batching/projection from access and pagination needs, then test the count envelope across multiple cardinalities.
• flush() sends pending changes; clear() controls persistence-context retention. JDBC batch configuration is separate. Hibernate 6.6 disables insert batching with identity generation; changing ID strategy requires schema/compatibility evidence. Delegate fetch and batch design to orm-fetch-and-batching-performance; prepared-statement count alone does not prove batching.
• Hikari's contended borrow path can appear in jdk.ThreadPark; verify stack/blocker identity rather than treating every park as pool contention. Runtime JFC thresholds filter short waits; inspect them before concluding that absent events mean no queueing.
• Inspect effective isolation rather than assuming the engine default. For PostgreSQL 40001, retry the whole transaction in a fresh transaction, including reads and decisions, with bounded attempts/backoff/jitter only when external effects are safe. It also occurs at REPEATABLE READ; repeated failure or exhaustion remains an application-visible failure requiring diagnosis.
• Virtual threads do not change Little's arithmetic; they change where the bottleneck sits. Replacing a fixed worker pool can remove implicit admission control. The JDBC pool still bounds checked-out connections, but waiting tasks/memory need separate bounded admission and deadlines (concurrency-limiting-and-bulkheads).

References

Return the measurement interval/population, mean and tail hold times, borrow rate, candidate pool and aggregate database budget, competing diagnosis, and validation/rollback bounds. State which checks ran and which claims remain conditional.

• Sizing and configuration — the calculation, the HikariCP settings with their real defaults, and the pre-deploy and monitoring checklists. Read when configuring or reviewing a pool.
• Pool incident triage — the ordered diagnostic path from pool metrics to pg_stat_activity to pg_stat_statements to EXPLAIN. Read during an incident where threads are waiting on the database.