I once watched a service go from healthy to completely unresponsive in about thirty seconds. CPU was fine. Memory was fine. The database server had plenty of headroom. But every API request was timing out. The culprit was the connection pool.
It was sized at the default ten connections. That works great in development when you are the only user. In production, with even moderate concurrency, ten connections becomes a hard bottleneck. Requests queue up waiting for a connection. The queue grows. Eventually, the whole thing falls over.
The funny thing is that connection pool sizing is not actually that complicated. There is a formula for it. It is called Little's Law, and it comes from queuing theory. The formula is: connections equals arrival rate times processing time. Or in simpler terms: how many requests show up per second, multiplied by how long each request holds a connection.
Here is a real example. Your API handles one hundred requests per second at peak. Each request hits the database, and those queries take an average of fifty milliseconds to complete. That gives you five connections. One hundred times zero point zero five equals five. That is your theoretical minimum.
But you cannot run at one hundred percent utilization. Once you hit saturation, latency spikes and throughput actually drops. You need headroom. A common rule of thumb is to double the calculated value. So five becomes ten. But here is the catch: that assumes one database per application. If you have multiple instances of your service, you need to divide accordingly. Three application instances sharing one database should each get about three or four connections, not ten.
Most people size their pools backward. They look at the database specs and think: this thing can handle two hundred connections, so I will give the application fifty just to be safe. That is wrong. The database can handle two hundred connections, but it handles them poorly. PostgreSQL, for instance, allocates about ten megabytes of memory per connection for various buffers. Two hundred connections means two gigabytes of RAM before you run a single query. Plus, context switching between that many connections kills performance. You want fewer connections, not more, and you want each one to be busy doing real work.
The defaults in HikariCP reflect this thinking. Maximum pool size defaults to ten. Minimum idle is ten. Connection timeout is thirty seconds. These are starting points, not final answers. If you never change them, you are making a conscious decision to accept whatever behavior happens at your particular scale.
Here is what I have found works in practice. Start with the formula: expected peak queries per second, times average query duration in seconds, times two for headroom. That gives you a target. Then test it. Run a load test that simulates real traffic patterns. Watch the pool metrics. HikariCP exposes everything through Micrometer or JMX if you enable it. You want to see active connections staying well below the maximum, with occasional spikes that resolve quickly. If you are constantly hitting the maximum, your pool is too small. If you are using three connections out of fifty, your pool is too large.
There is another number that matters: minimum idle connections. HikariCP defaults this to the same value as maximum pool size, which means the pool always keeps that many connections open. That avoids the overhead of creating connections under load, which is good. But it also means you are holding resources even during quiet periods. For services with very spikey traffic, I sometimes set minimum idle lower than maximum, allowing the pool to shrink during off-peak hours. For services with steady load, keeping them the same makes sense.
The connection timeout also deserves attention. Thirty seconds means if your database is unreachable or completely saturated, your application waits thirty seconds before failing. In a service handling user requests, thirty seconds is an eternity. I usually drop this to five or ten seconds. Better to fail fast and let your load balancer or circuit breaker handle it than to burn threads waiting for connections that are not coming.
Then there is the database side of the equation. You need to know your database's actual connection limit. PostgreSQL defaults to one hundred max connections. MySQL is usually one hundred and fifty-one. But these are global limits across all clients. If you have five application instances each configured for fifty connections, you have already exceeded PostgreSQL's default before any administrative connections or background tasks come into play.
I have seen this cause actual outages. A deployment brings up new instances before the old ones fully drain their connections. For a moment, you have double the usual application count, each holding their full pool allocation. The database hits max connections and starts rejecting new ones. Applications see connection refused errors. Health checks fail. The deployment rolls back. The old instances reconnect. Sometimes this creates a cascade where you cannot actually deploy at all without taking an intentional outage.
The fix is simple: set your total application pool sizes to something less than the database maximum. Leave room for administrative connections, background jobs, and deployment overlap. If your database allows one hundred connections and you have ten application instances, give each instance no more than eight connections. That leaves twenty for everything else.
Monitoring matters here. Track four things: active connections, idle connections, wait times, timeouts. HikariCP exposes metrics through whatever monitoring system you are using. Set up alerts for connection wait times above some threshold, or for timeouts occurring at all. Timeouts mean your pool is exhausted. Wait times climbing means you are heading toward exhaustion.
One last thing: prepared statements and connection pooling interact more than people realize. Prepared statements are cached per connection. If you have fifty connections and you prepare a common query on each one, that is fifty copies of the parsed query plan sitting in memory. This is usually fine, but it is worth knowing about if you are trying to understand where your memory goes.
Connection pool tuning is not a one-time task. Your traffic patterns change. Your queries change. A pool size that worked perfectly at launch might be wrong six months later after you have added features and onboarded users. Revisit these numbers periodically. The formula does not change, but the variables do.
The service that fell over with thirty second outages? We fixed it by doing the actual math. Peak queries per second, times average query time, times two. Turned out we needed about twelve connections total across three instances. We had defaulted to ten per instance. Thirty connections competing for work that only needed twelve. The queue backed up, latency spiked, and everything broke.
Connection pools are like any other resource: more is not better. The right number is better. Measure your actual load. Apply the formula, test the result, monitor the outcome. That is how you keep databases happy and applications responsive.
Comments (0)