SAYLOR Diagnose and recover from database connection saturation by separating demand, leaks, long transactions, pool configuration, locks, and server capacity.
The result you're building
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_connectionswithout 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.
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
| Evidence | Likely layer | First decisive check | What the result means |
|---|---|---|---|
| Many idle-in-transaction sessions | Application/transactions | Group age and call site; inspect transaction boundaries | Code leaves transactions open, retaining locks and connections. |
| Active sessions wait on same lock | Lock contention | Find blocking chain and responsible transaction | Connection saturation follows one blocked critical section. |
| Pool timeouts but DB has free slots | Pool/client | Inspect pool metrics and acquisition stack | Leak, local pool limit, DNS/TLS, or unhealthy connections block clients. |
| Saturation starts after scale-out | Budget | Calculate pool max across replicas/workers | Horizontal scaling multiplied connection demand. |
| Restart helps temporarily | Leak/slow workload | Measure connection age and accumulation after restart | Recovery cleared state but did not remove the cause. |
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.
Step 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.
Step 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.
Step 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.
Step 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.
Step 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.
Step 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.
Step 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.
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
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.
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 sessions | Code leaves transactions open, retaining locks and connections. | Group age and call site; inspect transaction boundaries |
| Active sessions wait on same lock | Connection saturation follows one blocked critical section. | Find blocking chain and responsible transaction |
| Pool timeouts but DB has free slots | Leak, local pool limit, DNS/TLS, or unhealthy connections block clients. | Inspect pool metrics and acquisition stack |
| Saturation starts after scale-out | Horizontal scaling multiplied connection demand. | Calculate pool max across replicas/workers |
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. |
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