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

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

TL;DR

Pool exhaustion means demand for connections exceeds supply: too few connections, connections held too long (slow queries, missing closes), or leaks. Diagnose by watching pool metrics (active, idle, waiting threads) during the incident, then fix the actual cause: usually slow queries holding connections or code not returning them. Raising max pool size without fixing the hold time just moves the bottleneck to the database.

The query

how to debug connection pooling exhaustion in production

Use this when

• Apps log pool exhausted or connection wait timeouts
• Latency spikes correlate with traffic
• Pool sizes were set by guesswork
• After adding new features that use the database heavily

Not for when

• Slow queries themselves (tune those separately, then recheck the pool)
• Database server overload (the pool protects the DB; check both)
• First-time pool configuration

Steps

Step 1: Watch pool metrics during the incident

Look at active connections, idle connections, and threads waiting for a connection. Waiting threads with zero idle connections is exhaustion; waiting threads with idle connections available is a pool bug or misconfiguration. Expected output: the pool state classified: truly exhausted vs misbehaving.

Step 2: Find what holds connections too long

Check for slow queries and transactions holding connections open: long-running transactions, queries without timeouts, code paths that forget to close. The pool drains because checkouts never return, not because traffic is high. Expected output: the top connection-holders identified by query or code path.

Step 3: Check for leaks

If active connections grow monotonically over hours without load growth, something leaks: connections opened and never closed. Heap dumps or connection-creation stack traces find them. Leaks are the most common cause of gradual exhaustion. Expected output: leaking code path found, or leak ruled out by stable counts under steady load.

Step 4: Size the pool from data, not defaults

Set max pool size based on measured concurrent demand plus headroom, and set connection timeouts (max lifetime, idle timeout, checkout timeout) so bad states self-heal. Document the sizing math next to the config. Expected output: pool sized for the real workload with timeouts that bound the damage.

Step 5: Protect the database from the fix

Raising pool size raises database load. Check the database's connection count and per-connection overhead before increasing. Sometimes the right fix is fewer, faster queries rather than a bigger pool. Expected output: pool and database sized as a pair; neither overwhelms the other.

Provenance

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

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

Thread query: how to debug connection pooling exhaustion in production

TL;DR: Pool exhaustion means demand for connections exceeds supply: too few connections, connections held too long (slow queries, missing closes), or leaks. Diagnose by watching pool metrics (active, idle, waiting threads) during the incident, then fix the actual cause: usually slow queries holding connections or code not returning them. Raising max pool size without fixing the hold time just moves the bottleneck to the database.

Not specified by the original author.

TL;DR

10k requests per second needs roughly rps times average latency in concurrent connections, and most databases have a few hundred slots. The test opened connections the database could never serve. Size the concurrency first, compare it against the pool and database limits, add a pooler if needed, and re-run with a gradual ramp that aborts when pool waiters appear.

The query

agent's load test DDoS'd the staging database -- it ramped to 10k rps without checking the connection pool size first

Steps

1. Stop the test and assess the database

Halt the load run immediately. Check the database for connection exhaustion: current connection count versus the max connections setting, and any "remaining connection slots are reserved" errors in the log.

Expected: confirmation of whether the database hit its connection ceiling, and that it recovers once the test stops.

2. Compute the concurrency the target rate requires

Required concurrent connections is roughly target rps multiplied by average query latency in seconds. 10k rps at 50ms average latency needs about 500 concurrent connections, before any headroom.

Expected: a concrete concurrency number for the planned load.

3. Compare against the pool and the database

Read the actual limits: the database max connections setting, the application pool size, and the pooler configuration if one sits in front (pool size, pool mode). If required concurrency exceeds any of them, the test as planned cannot run.

Expected: a clear statement of which limit the test would have breached.

4. Close the gap before re-running

Options: lower the target rps to fit the pool, raise the pool within safe bounds, or put a transaction-mode pooler in front of the database so thousands of client connections multiplex onto hundreds of server connections. Pick the combination that fits staging's purpose.

Expected: required concurrency fits comfortably inside every limit, with headroom.

5. Re-run with a guarded ramp

Ramp in stages (for example 10 percent, 25 percent, 50 percent, 100 percent of target), holding each stage while watching pool waiter metrics: queued checkouts, wait events on the database, and connection errors. Abort the run if waiters grow.

Expected: the full target rate sustains with pool waiters near zero and no connection errors.

Use this when

• A load test caused connection exhaustion or "too many clients" errors
• The test ramped straight to a high rps target with no pre-flight
• Pool waiters or connection timeouts appeared during the run
• The agent planned the test from rps alone without a concurrency budget

Not for this skill when

• The database is slow for query-plan reasons rather than connection limits
• The pool is sized fine and the bottleneck is CPU or disk
• The test targeted shared production infrastructure (isolate the target instead)
• Failures are 429s from a rate limiter rather than connection errors

Variant phrasings

load test exhausted connection pool

Same recovery: compute concurrency, compare to limits, ramp with guards.

too many clients already during benchmark

The database said no. The fix is sizing and pooling, not retrying harder.

staging database overwhelmed by load test

Check which resource gave out first (connections, CPU, disk) and size the test to the smallest one.

Why it happens

Load-test agents reason in requests per second, but databases enforce limits in concurrent connections. The two are related by latency, which the agent never estimated. Without that multiplication the plan looked safe: "the database handles production traffic, so it can handle the test." Production traffic never arrives as 10k rps of identical queries with fresh connections, and staging pools are usually smaller than production's.

Edge cases

• Pooler in session mode: holds a server connection per client for the whole session, so it barely helps. Transaction mode is what multiplexes.
• Staging smaller than production: even a perfectly sized staging test may not predict production. Record the pool sizes on both sides.
• Connection storms on deploy: the test passing does not cover the thundering herd when app servers restart. Test restarts separately.
• Serverless databases: per-connection overhead and cold starts change the math; check the provider's connection guidance before sizing.

Provenance

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

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

Thread query: agent's load test DDoS'd the staging database -- it ramped to 10k rps without checking the connection pool size first

TL;DR: 10k requests per second needs roughly rps times average latency in concurrent connections, and most databases have a few hundred slots. The test opened connections the database could never serve. Size the concurrency first, compare it against the pool and database limits, add a pooler if needed, and re-run with a gradual ramp that aborts when pool waiters appear.