Connection pool sizing

Size it — requests per second for one instance and how long each holds a connection. Little’s law gives the connections busy on average (L = λW); a pool of exactly that is 100% busy, so the table treats the pool as an M/M/c queue (Erlang C) and shows, for each size, how busy it is, the chance a request waits at all, the mean and p99 wait, and the chance of waiting longer than connectionTimeout. The suggestion is the smallest pool with a p99 wait under a tenth of the hold time and no more than 85% busy. The hold time is the whole time a connection is out — usually the whole @Transactional method, and with Spring’s default open-in-view the rest of the request — not the query time; Hikari’s hikaricp.connections.usage metric measures it.

Database limit — the PostgreSQL wiki’s starting point, connections = (cores × 2)
effective spindles, with what it does and doesn’t mean (it is active connections for the whole server, cores are physical, spindles are a guess for SSDs). Then every instance’s pool, the extra instances during a rolling deploy or at the autoscaling maximum, and other clients (migrations, admin tools, jobs) against max_connections less the reserved slots, drawn as a bar, with the largest pool per instance that fits. Pick an Amazon RDS or Aurora class to fill in its default max_connections from the parameter-group formula.

Simulate — a discrete-event simulation: requests arrive at random, round-robin across the instances; each borrows a connection from its instance’s pool (or waits for one, and fails after connectionTimeout), runs its work on the database and holds the connection a little longer for app code. The database runs every active query at once on its cores; past the core count they share, and a contention factor β makes total throughput fall as more compete. Sweep pool sizes runs the same arrivals for a range of sizes and plots the p99, the median and the timeouts against pool size: too small queues and times out in the application, too large moves the queue into the database in a burst. Run shows one size over time — connections in use, requests waiting, queries active against the cores, and p99 and timeouts per second. Presets set up the cases worth seeing.

Hikari settings — tick the things on your network path that close connections (an AWS NLB’s 350 s idle timeout, an ALB, NAT gateways, PgBouncer’s server_idle_timeout and server_lifetime, RDS Proxy, MySQL’s wait_timeout…) and correct their numbers. maxLifetime is set under any lifetime limit and keepaliveTime under any idle limit, within Hikari’s 30-second minimums. You get the Spring Boot application.yml or .properties to copy, and Check in the Spring Boot inspector opens it in that tool’s checker. Below, every Hikari setting explained with its default.

Erlang C assumes random (Poisson) arrivals and exponentially distributed hold times, one queue per pool and requests that wait rather than give up. Real traffic is burstier; use the simulator with a burst, and your own metrics.

The database model is deliberately simple: queries share cores fairly, and β stands in for everything that makes a busy database slower than its core count suggests (lock waits, context switches, buffer and cache contention, I/O queues). It shows the shape, not your numbers. Measure throughput and latency on your own system as you change the pool.

Hikari’s pool is treated as fixed-size and FIFO; connection creation, validation and the `ConcurrentBag’s thread-local reuse are not modelled.

RDS values use the instance’s nominal memory; DBInstanceClassMemory is a little lower, so the real default is a little lower too.

HikariCP wiki, About Pool Sizing and the configuration reference.

PostgreSQL wiki, Number Of Database Connections.

Amazon RDS User Guide, “Maximum number of database connections” (the max_connections parameter-group formulas).

J. D. C. Little (1961) and A. K. Erlang (1917) via the queueing planner, whose engine this page uses for Erlang C.