The connection stays busy after SQL stops
A database connection pool can be exhausted while individual SQL statements remain fast. The missing interval is often connection hold time: everything between acquisition and release, including application work or a remote call that runs while the connection is checked out. Start the investigation there when pool wait rises without a matching increase in query duration.
The reconstruction below models twelve simultaneous requests, four connections, 40 ms of SQL work and an 800 ms external lookup. Holding the connection across that lookup gives the last group a 1,680 ms pool wait. Moving an independent lookup before acquisition reduces the modeled maximum wait to 80 ms. These are calculated timings under fixed assumptions, not a recorded outage or a database performance claim.
That diagnosis is conditional. A slow query, lock contention, leaked connection or arrival burst can produce a similar alert. The useful evidence is the order in which the intervals grow.
Reconstruct one request before increasing capacity
Record four timestamps around the application pool: request arrival, borrow request, acquisition and release. Add SQL spans and external-call spans to the same trace. Use a monotonic clock for durations within a process. Across services, preserve trace relationships and account for clock differences before subtracting wall-clock timestamps.
For the first modeled request, the connection is acquired at 0 ms. SQL runs until 20 ms, an external lookup runs until 820 ms, and the second SQL operation finishes at 840 ms. The pool considers this connection occupied for all 840 ms. Only 40 ms belongs to SQL execution. At 0 ms, the first four requests occupy the four slots and eight other requests wait for a borrow.
A SQL-duration dashboard cannot reconstruct the checkout interval by itself. Capture connection release in cleanup paths as well as successful returns. A client timeout proves that the caller stopped waiting. It does not prove that the application returned the connection. Correlate abandoned requests with outstanding checkouts before declaring a leak.
For PostgreSQL, pg_stat_activity supplies server-side state and wait information. An idle-in-transaction backend has a transaction open without executing a query. An active backend can also be waiting on an event. Neither state directly measures time spent waiting for an application pool slot. Join database observations to the application trace instead of treating them as interchangeable metrics.
Separate the competing explanations
Use each observation to choose a discriminating probe. High pool utilization alone cannot tell you which row in this table applies. Collect the connection count and wait distribution per application instance as well as across the fleet: an aggregate can conceal one overloaded process.
| Suspected cause | Evidence to seek | Probe that could reject it |
|---|---|---|
| External work holds a connection | Long checkout span overlapping the remote call | Compare checkout and SQL intervals on the same request |
| Slow SQL or lock contention | SQL span grows and server wait evidence matches | Inspect the specific query and blocker during the interval |
| Connection is never returned | Outstanding checkout survives request completion | Exercise success, exception and cancellation cleanup paths |
| Arrival burst exceeds capacity | Borrow queue grows while hold duration stays stable | Compare arrival rate and concurrency with the same route at normal load |
| One tenant monopolizes capacity | Waiters correlate with a tenant's concurrent workload | Break down admitted work and hold time by tenant |
The final row connects this diagnosis to SaaS noisy-neighbor isolation. Increasing a shared pool may postpone a queue while preserving the same tenant imbalance. Admission control and workload isolation address a different boundary from connection lifetime.
Replay the checkout schedule
Download the standard-library Python replay, save it as replay.py in an empty working directory, and run it with Python 3. It writes a per-request CSV trace and JSON results. A heap represents the next free time of each connection. No database, network, sleep or random generator is involved.
All twelve requests arrive at time zero. Each connection handles one request at a time. In the first two scenarios, a request holds its connection for 840 ms. In the third, all requests perform an independent 800 ms lookup before asking for a connection, then hold it for 40 ms. This assumes the SQL operations can move together after the lookup without changing the business invariant.
| Calculated scenario | Pool size | Hold per request | Maximum pool wait | Last completion from arrival |
|---|---|---|---|---|
| Remote lookup inside checkout | 4 | 840 ms | 1,680 ms | 2,520 ms |
| Same workflow, larger pool | 8 | 840 ms | 840 ms | 1,680 ms |
| Independent lookup before checkout | 4 | 40 ms | 80 ms | 920 ms |
Pool wait starts when a borrow is requested. In the third scenario that is 800 ms after arrival, so the 80 ms maximum is not total request latency. The model assumes unconstrained external-call concurrency and no database contention. A real dependency limit, uneven response times, query contention or continuous arrivals would change the schedule. Raising the pool size is not guaranteed to reproduce the middle row on a real database.
Shorten ownership without breaking the transaction
Move remote work outside the checkout only after identifying what the transaction protects. If the remote request depends on a locked balance, moving it earlier can invalidate the decision. One possible redesign reads a versioned snapshot, releases the connection, performs the lookup, then reacquires a connection and checks the version before committing. A conflict needs an explicit retry or rejection path.
That redesign also has a boundary: an external side effect may already have happened when the final database check fails. Use a durable intent and an appropriate reconciliation workflow when atomic local state cannot cover the remote effect. The transactional outbox crash cases explain a relevant handoff pattern. An outbox does not make two systems share one transaction.
Bound both admission and borrowing by the remaining request deadline. Give timed-out work a cleanup path. Do not simply raise every instance's pool limit: PostgreSQL's connection settings describe a server-wide connection ceiling and resource allocation tied to it. Budget the combined application fleet, background workers and operational access.
Verify the first queue shrinks
Replay the same arrival pattern against the changed application in an isolated test environment. Compare borrow wait, checkout duration, SQL duration, completed work and error outcomes. Test dependency slowness and cancellation, not just the successful path. Confirm that a shorter checkout has not introduced stale decisions, duplicate external effects or failed cleanup.
The fix is supported when the predicted interval shrinks and the business invariant still holds. If pool wait stays high after remote work leaves the checkout, return to the trace: a different queue or a database constraint is now setting the limit.
Sources
Documentation checked .