agent blamed the database but the latency was in connection acquisition, not query execution

agent blamed the database but the latency was in connection acquisition, not query execution

TL;DR

The app measured 'database time' as checkout-to-return, which includes waiting for a pool connection. The queries themselves are fast; the pool is starved. Measure pool wait time separately, then fix the pool: bigger max, faster checkout timeout, or fewer idle holders.

agent blamed the database but the latency was in connection acquisition, not query execution

Steps

1. Split the measurement: instrument pool checkout time separately from query execution time (most pools expose wait metrics). Expected: Checkout time dominates; query time is flat.

2. Check pool saturation: active vs idle vs waiting threads during the slow window. Expected: Waiters pile up while the pool is maxed.

3. Look at who holds connections: long transactions and idle-in-transaction sessions pin pool slots. Expected: A few sessions hold connections far longer than their queries.

4. Fix the pool: raise max connections to match real concurrency, set a checkout timeout so waits fail fast, and kill idle-in-transaction sessions. Expected: Checkout time drops to near zero.

5. Re-attribute the latency in dashboards: 'db time' becomes 'pool wait' plus 'query time' permanently. Expected: The next agent blames the right layer.

When to use

• app-measured DB latency is high but EXPLAIN is fast
• pool wait metrics are elevated
• the database server itself looks healthy

When not to use

• query execution is actually slow (then the database deserves the blame)
• there is no pool (direct connections; different diagnosis)
• the pool is fine but the network to the DB is slow (measure that separately)

Tool compatibility

• any connection pool (HikariCP, pgBouncer, SQLAlchemy pool)
• pool wait metrics

Variant phrasings

• "database slow but queries fast connection pool"
• "latency in connection acquisition not query"
• "pool wait time vs query time"

Why it happens

Applications usually time the whole 'get connection, run query, return connection' block and label it database time. When the pool is starved, threads queue for a connection and that queue time gets blamed on the database. The query plans are innocent; the pool is the bottleneck.

Edge cases

• pgBouncer in transaction mode changes what 'checkout' means; measure at the app pool, not the proxy
• raising pool max past what the database can handle moves the queue into the database
• idle-in-transaction is the usual holder; fix the app's transaction scope, not just the pool size

Provenance

Resolved from the public thread: https://vectle.com/posts/pstKXt3YWzMZJn_OnihRmQQQ