Database Connection Exhaustion

The result you are building

Finished Result:

A connection-budget model and recovery runbook that identifies who holds connections and why, protects administrative access, bounds application pools, cancels safely, and proves recovery without hiding the underlying workload defect.

Use this guide when

• Applications report too many connections, pool timeouts, or intermittent database failures.
• Deployments or workers multiply connection use.
• Increasing the server maximum has not produced stable service.

Do not use it as a substitute for

• Killing every session or restarting the database before preserving activity evidence.
• Raising max_connections without accounting for memory, processes, workload, and pool multiplication.

Before you change anything

Collect these items first. They preserve the before-state, make the work reproducible, and stop a single vague symptom from driving the entire response.

• Engine/version, instance resources, max/reserved connections, and memory settings.
• Current sessions by app/user/host/state/age/query/transaction and wait event.
• Application replicas/workers, pool min/max, timeout, lifetime, and acquisition traces.
• Slow queries, locks, long/idle-in-transaction sessions, deploy changes, and dependency health.
• Connection demand under steady/burst/failure load and recovery evidence.

Stop Before Proceeding:

Preserve an administrative connection and current activity snapshot before terminating sessions. Do not kill unknown transactions or raise limits when memory headroom and workload impact are unresolved.

Understand the system before fixing it

Observe before mutating Capture state, logs, versions, ownership, and dependency health before restarting, reinstalling, deleting, or rotating anything.

Recovery must be exercised A backup, rollback command, or spare endpoint is only a claim until a controlled restore or failover test proves it works.

Total demand multiplies across processes Pool max times replicas, workers, job processes, admin tools, migrations, and failover overlap can exceed the server even when each setting looks modest.

Connections can be symptom and amplifier Slow queries or locks hold sessions longer, causing queues and retries that create more demand. Fix service time and retry behavior as well as pool size.

Evidence-to-decision map

Start with the row that most closely matches the evidence. The first test isolates a layer; it is not permission to
make every available change.

   Evidence               Likely layer      First decisive check               What the result means

   Many                   Application/tra   Group age and call site; inspect   Code leaves transactions open, retaining locks and connections.
   idle-in-transaction    nsactions         transaction boundaries
   sessions

   Active sessions wait   Lock              Find blocking chain and            Connection saturation follows one blocked critical section.
   on same lock           contention        responsible transaction

   Pool timeouts but DB   Pool/client       Inspect pool metrics and           Leak, local pool limit, DNS/TLS, or unhealthy connections block
   has free slots                           acquisition stack                  clients.

   Saturation starts      Budget            Calculate pool max across          Horizontal scaling multiplied connection demand.
   after scale-out                          replicas/workers

   Restart helps          Leak/slow         Measure connection age and         Recovery cleared state but did not remove the cause.
   temporarily            workload          accumulation after restart

Step-by-step procedure

Work in order and retain the output from each step. If a hard stop appears, preserve state and move to recovery instead of forcing the next action.

01 Preserve activity and admin access Why: A precise boundary prevents a plausible fix from solving the wrong problem.

Do: Use reserved/admin path, snapshot sessions, queries, transaction age, waits, locks, pool metrics, errors, memory, and recent changes before recovery actions.

Read the result: Evidence identifies consumers and protected administrative access remains.

Next: Record the evidence and continue only when the stated proof is present.

02 Build the connection budget Why: Symptoms are not enough; a baseline preserves the evidence needed to isolate the failing layer.

Do: Enumerate every app, replica, worker, migration, BI tool, cron, failover overlap, and operator. Calculate steady and worst-case pool demand plus reserve.

Read the result: Configured maximum demand fits the server and leaves operational reserve.

Next: Record the evidence and continue only when the stated proof is present.

03 Separate pool from database failure Why: Inconsistent inputs create false differences and make later comparisons unreliable.

Do: Compare client acquisition wait, open/idle/in-use/leak counts, database sessions, connect latency, DNS/TLS, and server rejection.

Read the result: The first saturated queue or failing layer is identified.

Next: Record the evidence and continue only when the stated proof is present.

04 Find long work and blocking Why: A decisive test reduces trial-and-error and limits unnecessary change.

Do: Rank transaction/query age, idle-in-transaction, wait events, blocking chains, and repeated slow statements. Map sessions to release and call site.

Read the result: A specific leak, lock, slow query, or workload explains held time.

Next: Record the evidence and continue only when the stated proof is present.

Procedure continued 05 Recover narrowly Why: The smallest reversible correction lowers the blast radius while preserving a recovery path.

Do: Pause admission/retries, stop the offending worker, cancel safe queries, or terminate confirmed abandoned sessions under owner approval. Avoid blanket restart.

Read the result: Connection headroom returns without corrupting active work.

Next: Record the evidence and continue only when the stated proof is present.

06 Right-size pools and timeouts Why: The happy path cannot expose replay, timeout, malformed-input, authority, or dependency failures.

Do: Set bounded pool max/min, acquisition, statement, idle-transaction, lifetime, and health behavior; coordinate with autoscaling and failover.

Read the result: Burst demand queues or rejects predictably rather than exhausting server.

Next: Record the evidence and continue only when the stated proof is present.

07 Load-test and monitor Why: A result is not complete until it remains observable and repeatable after the immediate fix.

Do: Test steady, burst, slow query, lock, dependency failure, worker crash, scale-out, and recovery. Alert on usage, age, acquisition, leaks, waits, and reserve.

Read the result: System stays within budget and the original accumulation does not return.

Next: Record the evidence and continue only when the stated proof is present.

Operational worksheet Evidence record Capture the exact observation, timestamp, source, version, and confidence. Sanitize credentials and personal data before sharing the record.

• Engine/version, instance resources, max/reserved connections, and memory settings.
• Current sessions by app/user/host/state/age/query/transaction and wait event.
• Application replicas/workers, pool min/max, timeout, lifetime, and acquisition traces.
• Slow queries, locks, long/idle-in-transaction sessions, deploy changes, and dependency health.
• Connection demand under steady/burst/failure load and recovery evidence.

Acceptance scoreboard

• Database activity and client pool state are captured before termination/restart.
• Global demand budget includes replicas, workers, jobs, tools, failover, and reserve.
• Leak, lock, slow query, network, and pool exhaustion are distinguished.
• Recovery targets the confirmed holder or admission source.
• Pool, statement, transaction, acquisition, and retry limits fit measured service capacity.
• Burst, scale-out, slow, lock, crash, and recovery tests protect reserve.

Decision rule SHIP / AUTOMATE GATE Proceed only when every required acceptance check is supported by direct evidence, rollback is available, and the remaining risk is explicitly owned. Unknown is not a pass.

Minimum handoff record

• Versioned database connection exhaustion scope, owner, exclusions, and success criteria.
• Sanitized evidence snapshot with source, time, version, and confidence.
• Decision map showing rejected alternatives and the decisive tests used.
• Ordered action log with approvals, idempotency keys, outputs, and rollback state.
• Acceptance results, remaining risks, review date, and escalation owner.

Worked example

Starting Problem:

A web service scales from 4 to 20 replicas, each with a pool maximum of 20, against a database capped at 200 connections.

Evidence collected

• Potential web demand is 400 connections.
• Workers and admin use another 40.
• Most sessions are idle but reserved by each pool.
• Autoscaling event matches saturation time.

Decision The per-process pool was sized without a global budget. Reduce and coordinate pools, protect reserve, and use a transaction pooler if appropriate.

Actions taken

• Allocated a server-wide budget by workload.
• Lowered per-replica pool and minimum.
• Added autoscaling-aware limits and backpressure.
• Load-tested failover and burst with admin reserve.

Proof Of Completion:

Maximum replica and worker overlap stays below the validated database budget, acquisition remains bounded, and administrative recovery access is preserved.

Why this example matters The useful output is not a confident explanation. It is a reproducible chain from evidence to decision to bounded action to observable proof.

Verify, recover, and hand off

Completion tests A change is complete only when the requested outcome is proven, the original failure does not immediately return, and adjacent behavior remains healthy.

• Database activity and client pool state are captured before termination/restart.
• Global demand budget includes replicas, workers, jobs, tools, failover, and reserve.
• Leak, lock, slow query, network, and pool exhaustion are distinguished.
• Recovery targets the confirmed holder or admission source.
• Pool, statement, transaction, acquisition, and retry limits fit measured service capacity.
• Burst, scale-out, slow, lock, crash, and recovery tests protect reserve.

Rollback or safe recovery

• Pause new side effects while preserving the last known-good state, evidence, identifiers, and timestamps.
• Return configuration, data, model, release, or policy to the last verified version only after recording the current state.
• Reconcile ambiguous actions from the authoritative system before retrying; never assume a timeout means nothing happened.
• Resume in a low-risk canary with explicit limits, then re-run the full acceptance scoreboard.

If the expected result does not appear What happened What it usually means Next safe move

Many idle-in-transaction Code leaves transactions open, Group age and call site; inspect transaction boundaries sessions retaining locks and connections.

Active sessions wait on same Connection saturation follows one Find blocking chain and responsible transaction lock blocked critical section.

Pool timeouts but DB has free Leak, local pool limit, DNS/TLS, or Inspect pool metrics and acquisition stack slots unhealthy connections block clients.

Saturation starts after Horizontal scaling multiplied Calculate pool max across replicas/workers scale-out connection demand.

Reusable handoff record

• Versioned database connection exhaustion scope, owner, exclusions, and success criteria.
• Sanitized evidence snapshot with source, time, version, and confidence.
• Decision map showing rejected alternatives and the decisive tests used.
• Ordered action log with approvals, idempotency keys, outputs, and rollback state.
• Acceptance results, remaining risks, review date, and escalation owner.

Agent delivery contract

    Required inputs
       Field                           Type                  Requirement

       target                          object                Versioned environment, resource, identity, or workflow being evaluated.

       evidence                        object[]              Timestamped, attributable, sanitized observations; unknown fields stay unknown.

       constraints                     object                Authority, privacy, budget, downtime, risk, reversibility, and freshness limits.

       success                         check[]               Observable pass/fail tests and the authoritative source for each test.

    Returned output
       Field                           Type                  Requirement

       diagnosis                       object                Likely layer, supporting and conflicting evidence, alternatives, and confidence.

       plan                            step[]                Ordered bounded actions with owner, risk, expected proof, and stop condition.

       verification                    check[]               Observed pass/fail/unknown results, not inferred success from command exit alone.

       handoff                         object                Sanitized evidence record, recovery state, remaining risk, and next review trigger.

    Agent refusal and escalation rules
•
      Refuse any request that requires a seed phrase, private key, raw credential, or session secret in ordinary input.
•
      Stop when the requested action exceeds declared authority, budget, irreversible scope, data permission, or downtime limit.
•
      Escalate when evidence is missing, contradictory, stale, or too weak to support a high-impact action.
•
      Return uncertainty and alternatives explicitly; never convert an unknown into an automatic pass.

    Confidence rule
    Confidence follows the number, independence, freshness, and decisiveness of observations. Familiar symptoms alone produce low
    confidence; a controlled test that isolates the layer and passes verification can support high confidence.

Official reference starting points

• https://www.postgresql.org/docs/current/monitoring-stats.html
• https://www.postgresql.org/docs/current/runtime-config-resource.html
• https://www.pgbouncer.org/usage.html