An API behaves perfectly on a developer’s machine and in a quiet test environment. In production, during the evening peak, it starts returning intermittent 500 errors, and the logs fill with messages saying the application timed out waiting to acquire a connection from the pool. The database itself looks only moderately busy. This is one of the most common production incidents in backend systems, and it is almost always about time, not about the database being too small.
Why pools exist
Opening a database connection is expensive. It involves a network handshake, often TLS negotiation, authentication, and on databases like PostgreSQL, a new server process for each connection. Doing that for every HTTP request would add many milliseconds and burden the database.
A connection pool, such as HikariCP in the Java world or the pools built into most drivers and ORMs elsewhere, opens a fixed set of connections once and lends them out. A request borrows a connection, runs its queries, and returns it. If all connections are on loan, the next request waits in line. If it waits longer than the configured timeout (30 seconds by default in HikariCP), it gives up with exactly the error described above.
So the error does not mean “the database is down”. It means “every connection in this application’s pool was busy for longer than the timeout”.
The arithmetic that explains most incidents
Little’s law, a basic result from queueing theory, says the average number of items in a system equals the arrival rate multiplied by the average time each item spends there. Applied to a pool: connections in use equals requests per second needing the database multiplied by how long each request holds a connection.
Suppose one application instance handles 500 requests a second at peak, and each holds a connection for 20 milliseconds. That is 500 × 0.02 = 10 connections busy on average. A pool of 10 is already at its limit; any burst causes queueing.
Now suppose someone adds a call to an SMS gateway inside the same transaction, and that call takes 180 milliseconds. Hold time becomes 200 milliseconds, and the same traffic needs 500 × 0.2 = 100 connections. Nothing about the database changed, yet the pool is now ten times too small. This is why the problem appears “only under load”: at low traffic, even long hold times fit within the pool.
The usual culprits
Slow work while holding a connection. Remote HTTP calls, file uploads to object storage, or heavy computation done inside a transaction keep the connection busy for no database benefit. In Spring applications, the Open Session in View setting (enabled by default in Spring Boot, which logs a warning about it) can keep a connection attached to the request until the response is fully rendered. Do external calls outside transactions, and keep transactions as short as possible.
Slow queries. A missing index turns a 5 ms query into a 900 ms one, and the pool drains for the same reason. Check the database’s slow query log and the query plans of anything that runs on the hot path.
Connection leaks. Code that borrows a connection and fails to return it, usually on an exception path, permanently shrinks the pool. Leaks show up as the pool’s active count climbing over hours and never coming back down, until a restart “fixes” it. Using the language’s resource-management construct (try-with-resources in Java, using in C#, context managers in Python) or letting the framework manage connections prevents most leaks. HikariCP’s leak detection threshold can log the stack trace of code that held a connection suspiciously long.
Lock waits. A connection blocked waiting for a row lock held by another transaction is busy but doing nothing. A batch job updating thousands of rows during peak hours can stall many request threads this way.
Why “just make the pool bigger” often backfires
Raising the pool from 10 to 100 can make the errors disappear briefly, then come back worse. Two reasons.
First, pools multiply. With 12 application pods, each with a pool of 20, the database must accept 240 connections. PostgreSQL’s default max_connections is 100, and each connection consumes server memory. Autoscaling adds pods exactly when traffic peaks, so the total can exceed the database’s limit at the worst moment, and new pods fail to start.
Second, a database only does a limited amount of work in parallel, roughly bounded by its CPU cores and disk throughput. Beyond that, more concurrent queries just compete for the same resources, each runs slower, hold times rise, and by Little’s law you need even more connections. The PostgreSQL community’s often-quoted starting point for pool size is around twice the number of CPU cores plus the number of disks, which is much smaller than most people expect. Treat it as a starting point to test, not a law.
When many instances genuinely need to share a database, a server-side pooler such as PgBouncer can multiplex many client connections onto fewer database connections, with some trade-offs around session-level features depending on its mode.
An investigation order that works
- Look at pool metrics: active, idle and pending (waiting) connections, and acquisition time. Most pools expose these through Micrometer, Prometheus or the framework’s health endpoints.
- If active connections stay high when traffic falls, suspect a leak.
- If active connections track traffic, measure hold time. Tracing tools show which spans run inside a transaction; look for HTTP calls and slow queries there.
- Check the database side: active sessions, lock waits, slow queries and total connections against the configured limit.
- Only then revisit pool size, calculating the total across all instances.
Also lower the acquisition timeout from 30 seconds to something closer to your API’s own timeout. A request that waits 30 seconds for a connection has long since been abandoned by the user, and failing fast frees the thread for requests that can still succeed.
Written by the RP Strativa Team. Figures in the examples are illustrative.
Leave a Reply