Freeing a connection does not mean completing a query
What occupies connections
Even a small share of long-running work can occupy the entire pool. Here, 88% of operations take 5 ms, 10% are useful slow operations taking 200 ms, and 2% are stuck and hold a slot for 2,000 ms. Total connection demand depends on both each group’s share and duration.
What timeouts achieve
In this model, the timeout guard cancels the query and releases its connection. A client stopping its wait does not by itself guarantee cancellation in a real database. A useful query taking 200 ms fails with a 10 ms timeout. Stuck operations occupy database connections but are counted separately from useful traffic.
Why optimize the query itself
An index reduces a useful slow query from 200 to 20 ms. A 50 ms timeout now gives it enough time while stopping stuck operations. Optimization and time limits solve different problems. An index alone is insufficient if stuck operations still hold connections.
What to compare
Inspect required connection occupancy, cancellations and useful query completion. Less work is not always better: a short timeout reduces occupancy but cuts off needed responses. This is an aggregate model; real query plans and lock waits need separate investigation.
Task
500 operations/s: 88% fast, 10% useful but slow, 2% stuck. There are 10 connections. Add a timeout guard and an index; canceling useful work does not count as success.
Fast query: 5 ms; slow: 200 ms, or 20 ms with an index; stuck: 2,000 ms. 10 concurrent connections provide 10 seconds of work per second. The limiter caps occupancy at timeout, but a useful slow query succeeds only if it finishes in time. The stuck 2% are excluded from useful timeline load but occupy connections and are counted separately.
- Add a timeout guard and test a short timeout.
- Add an index that speeds up the useful slow query.
- Choose a timeout above the new query time and check useful completion.