How to prove PostgreSQL pool clients are released on errors

The symptoms

• •Application-side pool reports exhaustion even though live request count is low, suggesting unreleased clients.
• •PostgreSQL pg_stat_activity shows client backends idle in transaction or idle longer than the application's expected transaction length.
• •Stack traces from pool acquisition timeouts point to error handlers that never invoke the release method on the failure branch.
• •Background error logs from long-held backends such as statement timeouts, idle-in-transaction timeouts, or lock waits surface only after a failure path.
• •Intermittent increases in pg_stat_database connections and rollbacks correlate with elevated 5xx response rates rather than peak traffic.

Likely causes

• •Try-with-resources or equivalent RAII wrapper is bypassed when exceptions are thrown before the client is acquired into a managed scope.
• •Connection checkout is performed in a helper that throws, and the caller's catch block does not release because no client reference exists yet.
• •Transaction wrappers commit or rollback but bypass the surrounding pool release because of early returns, swallowed exceptions, or finally blocks that only run on success.
• •Timeouts during checkout release a different internal handle than the one held by the caller, so caller-side release is a no-op.
• •Callback-based async code drops the client reference when an error propagates before the completion handler runs.
• •Nested borrow patterns re-enter the pool and the inner scope returns the wrong client to the outer scope.

First ten minutes

1. 01Identify the single pool entry point used by the failing service and list every exit point: success return, thrown exception, caught-and-rethrown, and timeout-induced releases.
2. 02Grep the codebase for the pool checkout call paired with each exit point; confirm a release or close call exists on every branch, including catch and finally.
3. 03Record the current count of backends in pg_stat_activity grouped by state and application_name to establish a baseline before any new probe.
4. 04Enable or confirm pool-side instrumentation that emits a checkout timestamp and release timestamp per acquired client, scoped to one service instance.
5. 05Force a controlled failure on a non-production path that exercises the suspected error branch and capture both pool metrics and pg_stat_activity snapshots before and after.

Evidence to collect

• •Per-checkout event log containing acquisition time, release time, caller function, and exception class if the release was triggered by an error path.
• •Snapshot of pg_stat_activity filtered to the service's application_name, ordered by state_change, taken at the moment a leak is suspected.
• •Snapshot of pg_stat_database numbackends and xact_rollback counts across the same window as the suspected leak.
• •Trace or span that records the borrow boundary so success and failure exits can be matched to a release event.
• •Pool's own in-use and idle counters at the moment pg_stat_activity shows more idle backends than the pool reports idle.
• •Exception type, message, and stack frames for the failure path being investigated, captured from a reproducible run.

Where to look

• •Application source: the function that calls pool.checkout, acquire, or borrow and every catch, finally, and early return around it.
• •PostgreSQL statistics: pg_stat_activity for live backend state and pg_stat_database for backend counts and rollback rates, per the monitoring stats documentation.
• •Pool configuration: minimum, maximum, and acquire timeout values, plus any interceptor or wrapper that intercepts release calls.
• •Database server logs: log_min_duration_statement, log_lock_waits, and idle_in_transaction_session_timeout events tied to the service's application_name.
• •Runtime metrics: gauges for in-use, idle, waiting, and acquisition latency from the pool's metrics endpoint.

Diagnostic steps

1. 01Diff code paths between success and failure exits and count distinct release calls; any path without a release is the primary suspect.
2. 02Reproduce the suspected error branch in a test harness with checkout instrumentation enabled and assert that a release event fires within a bounded time.
3. 03Cross-check pool in-use counter against pg_stat_activity rows for the same application_name; a persistent gap indicates server-side backends held without a matching client.
4. 04Inspect pg_stat_activity.state and state_change columns; rows stuck in idle in transaction past expected duration confirm an unclosed transaction rather than a missing release.
5. 05Compare pg_stat_database.xact_rollback growth with application-side error counts; divergence suggests failed transactions that did not propagate to release logic.
6. 06Review server log events for idle_in_transaction_session_timeout and statement_timeout; both indicate a backend outlived its intended transaction scope.
7. 07Trace one checkout end-to-end across success and failure and verify the release event timestamp is later than acquisition and within the expected lease duration.

Common mistakes

• •Relying solely on pool-side in-use counters without cross-checking pg_stat_activity, which hides server-side state.
• •Treating a finally block as proof of release without verifying it is reached when the checkout itself throws before client assignment.
• •Wrapping release in a conditional that skips on error, under the assumption the pool will reclaim, which only holds for specific pool implementations.
• •Assuming try-with-resources always protects the borrow when the resource variable is declared after the call that may throw.
• •Counting idle pool clients as released without checking whether the underlying backend is also idle or active in pg_stat_activity.

Safe fixes

• •Conditional on evidence that a specific exit path lacks a release call: route every failure exit through a single guarded release that is invoked before the function returns or rethrows.
• •Conditional on evidence of pre-acquisition exceptions: ensure the release guard checks for a non-null client reference before invoking release, since releasing a never-acquired handle is undefined.
• •Conditional on evidence of swallowed exceptions: replace silent catches with explicit error propagation so finally blocks execute on the documented control flow.
• •Conditional on evidence that the transaction wrapper leaks but the outer release runs: add an explicit rollback before release on every failure path, then verify rollback appears in pg_stat_database.xact_rollback.
• •Conditional on evidence that async callbacks drop the client: capture the client in a closure-scoped handle before any await that may reject, and release from both success and error continuations.

Prove the fix

1. 01Run the previously failing error scenario under instrumentation and observe a release event timestamp later than acquisition for every invocation, including the previously leaky branch.
2. 02During a sustained load that exercises success and failure paths, pg_stat_activity for the service's application_name shows no rows in idle in transaction beyond the configured idle_in_transaction_session_timeout.
3. 03Pool in-use gauge returns to its pre-failure-run baseline within the configured release deadline after the failing scenario completes.
4. 04pg_stat_database.numbackends for the service does not grow across repeated failure runs of equal size, indicating no cumulative leak.
5. 05Synthetic fault injection that throws after checkout but before the success path produces a release event and no new idle in transaction backend in pg_stat_activity.

Prevention and next steps

• •Adopt a single borrow helper that owns checkout, release, and rollback invariants so new code paths cannot bypass them.
• •Add a CI test that injects an exception after checkout and asserts that pg_stat_activity for the test application_name returns to baseline.
• •Alert on divergence between pool in-use gauge and pg_stat_activity rows for the same application_name beyond a small tolerance.
• •Periodically audit code paths touching the pool to confirm a release appears on every exit, including catch, finally, and early return.

Safe commands and checks

psql -h <pg_host> -p <port> -U <user> -d <db> -c "SELECT pid, usename, application_name, state, state_change, query_start FROM pg_stat_activity WHERE application_name = '<app_name>' ORDER BY state_change;"
psql -h <pg_host> -p <port> -U <user> -d <db> -c "SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database WHERE datname = current_database();"
psql -h <pg_host> -p <port> -U <user> -d <db> -c "SELECT pid, state, age(now(), state_change) AS idle_for FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)') AND application_name = '<app_name>' ORDER BY idle_for DESC;"
psql -h <pg_host> -p <port> -U <user> -d <db> -c "SHOW idle_in_transaction_session_timeout; SHOW statement_timeout;"