How to load-test a database connection pool

The symptoms

• •Acquired connection count plateaus at the configured pool maximum and pending requests begin timing out
• •Application p99 latency rises sharply once offered concurrency approaches or exceeds pool size
• •Database-side session count remains near max_connections even after the load tapers off
• •Pending/waiting client queue grows during sustained load and fails to drain within the acquire timeout
• •File descriptor or memory usage on the application process climbs across repeated runs and does not return to baseline
• •Application logs show acquire-timeout errors or "remaining connection slots are reserved" messages from the database

Likely causes

• •Pool maximum sized smaller than the peak concurrency the workload offers
• •Connections held longer than expected because release is missing on error or early-return paths
• •No statement or transaction timeout, so a single slow query occupies a connection indefinitely
• •Acquire timeout set so high that saturation is masked behind unbounded waiting
• •Idle connections not recycled, leaving stale rows visible on the database side
• •Combined offered concurrency from multiple app instances exceeds the database's max_connections

First ten minutes

1. 01Capture the pool configuration: min, max, idleTimeoutMillis, connectionTimeoutMillis, and statement or transaction timeout
2. 02Record idle baseline pool stats (totalCount, idleCount, waitingCount) before any load is applied
3. 03Confirm the database's max_connections leaves headroom above pool max summed across all app instances
4. 04Note the workload shape: read vs write mix, average transaction length, and expected peak concurrency
5. 05Choose a ramp profile that exceeds expected peak by a defined margin (e.g., 2x pool max)
6. 06Define success criteria up front: acquired ceiling, pending ceiling, p99 ceiling, and post-load settle time
7. 07Select an isolated environment so DB-side CPU and IO pressure do not contaminate the signal

Evidence to collect

• •Pool metrics over time: totalCount, idleCount, waitingCount, and any per-event acquire/release counters
• •Application latency percentiles p50/p95/p99 and error rate per request class
• •Database pg_stat_activity session count grouped by state, plus the longest active transaction age
• •Database slow query log entries time-correlated with ramp steps and saturation onset
• •Open file descriptor count and resident memory for each application process across the run
• •Application error log entries for acquire timeout, release-after-error, and connection-reset events

Where to look

• •Application pool metrics endpoint or pool debug logs that report acquire and release events
• •Database pg_stat_activity view for live sessions, states, and oldest transaction age
• •Database server log for connection, disconnection, and slow query lines around the ramp window
• •Process inspection for the application: /proc/<pid>/fd count and ps output to confirm connection sockets are released
• •Load generator logs to verify offered concurrency actually reached the intended target and was not throttled client-side
• •Distributed tracing spans marking transaction boundaries where a connection must be released on every exit

Diagnostic steps

1. 01Establish an idle baseline: confirm the pool settles to configured min and idleCount is non-zero before any load is applied
2. 02Run a low-concurrency ramp and observe when waitingCount first becomes non-zero; this is the contention onset point
3. 03Increase offered concurrency in steps until waitingCount plateaus or the configured connectionTimeoutMillis fires
4. 04Repeat the ramp with the statement or transaction timeout disabled to separate DB slowness from pool sizing effects
5. 05Stop the load generator and verify the pool returns to configured min within idleTimeoutMillis; persistent excess indicates a leak
6. 06Inject a fault on a controlled path (force a query error mid-transaction) and confirm the connection is released on the error branch
7. 07Re-run with multiple application instances to model combined offered concurrency against the database's max_connections
8. 08Compare two identical runs after a configuration change using the same ramp profile to attribute deltas correctly

Common mistakes

• •Declaring success on throughput alone while ignoring waitingCount, p99, and post-load settle behavior
• •Driving load from a single concurrent client, which cannot reproduce contention against a multi-slot pool
• •Skipping the post-load check, allowing slow leaks that only appear after minutes of idle to go undetected
• •Setting connectionTimeoutMillis so high that exhaustion is masked behind long waits rather than surfaced as errors
• •Testing only read-only traffic and missing write paths where locks or row contention extend hold time
• •Comparing runs while database CPU or IO is also under unrelated pressure, contaminating the pool signal

Safe fixes

• •Adjust pool maximum only after confirming the database has headroom and per-connection hold time is bounded
• •Set an explicit connectionTimeoutMillis so backlog fails fast and surfaces saturation as a visible error rate
• •Add a per-statement or per-transaction timeout to cap the worst-case hold time on each connection
• •Audit every code path that acquires a connection and ensure release is reached on success, error, and early-return branches
• •Enable idle connection recycling so stale sessions do not accumulate in pg_stat_activity between bursts
• •Re-run the identical ramp after each change and diff acquired, waiting, p99, and settle-time before accepting the result

Prove the fix

1. 01Acquired count never exceeds the configured pool maximum at any sampled point across the full ramp window
2. 02WaitingCount is non-zero only briefly during ramp transitions and drains within the configured connectionTimeoutMillis
3. 03Application p99 latency stays within the predefined target up to the chosen peak offered concurrency
4. 04After load stops, acquired returns to the configured minimum within idleTimeoutMillis with no stragglers
5. 05Database pg_stat_activity returns to baseline count with no long-lived idle-in-transaction rows older than the expected threshold
6. 06Open file descriptor count on the application process does not trend upward across repeated identical runs

Prevention and next steps

• •Include pool saturation scenarios in CI load tests rather than only throughput-oriented smoke runs
• •Alert on waitingCount depth and on the rate of acquire-timeout errors, not only on request error rate
• •Track active connections per instance versus configured max as a rolling metric to catch drift early
• •Document the supported concurrency envelope and refresh it whenever pool or schema changes ship

Safe commands and checks

psql -h <db-host> -p 5432 -U <user> -d <db-name> -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"
psql -h <db-host> -p 5432 -U <user> -d <db-name> -c "SELECT pid, state, now() - query_start AS age, left(query, 80) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 10;"
psql -h <db-host> -p 5432 -U <user> -d <db-name> -c "SELECT setting FROM pg_settings WHERE name = 'max_connections';"
curl -s http://<app-host>:<metrics-port>/<pool-stats-path> | jq '.totalCount, .idleCount, .waitingCount'
pgrep -f <app-process-name> # obtain <pid> for the running application process
ls /proc/<pid>/fd | wc -l # count open file descriptors, including connection sockets, for the app process
ps -o pid,rss,nlwp,cmd -p <pid> # read resident memory and thread count for the app process
grep -c "connectionTimeout" <app-log-path> # count acquire-timeout events in the application log over the test window