Skip to main content
OpsRoadmaps

Runbooks / postgresql-connection-exhaustion

PostgreSQL connection exhaustion

"FATAL: too many clients already" or app pool waiters climbing — find who holds the slots, free them safely, and size the pool against max_connections.

12 canonical sections · 5 command blocks · last reviewed · updated 2026-09-21

Related focus areas:CloudOpsDataOpsSRE

Fix the underlying gap:AWS Security ImprovementCloud Cost OptimizationIncident Management MaturityObservability MaturityPlatform Engineering AdoptionProduction-Ready KubernetesResilience and DR ImprovementSRE Adoption

On this page
  1. Symptoms
  2. Severity / impact
  3. Immediate safety actions
  4. Fast checks
  5. Diagnostic commands
  6. Likely causes
  7. Decision tree
  8. Remediation
  9. Verification
  10. Escalation
  11. Prevention
  12. Related metrics / alerts
  13. Sources

Symptoms

  • Clients see FATAL: too many clients already (53300) or connection timeouts; DatabaseConnections (RDS) sits at/near max_connections.
  • App-side: connection-pool waiters climb, request latency jumps at the pool boundary (everything waits, nothing errors until the pool timeout).
  • Often follows a deploy (new service, bigger pool), a traffic spike (more app instances), or an outage of a downstream dependency (requests pile up holding connections).

Severity / impact

  • Sev-1 when writes fail outright — this is a full outage of DB-backed paths, and retry storms make it worse fast.
  • Sev-2 when a pool is saturated but drains; recurring saturation is a Sev-1 rehearsal.

Immediate safety actions

  • Do NOT restart the database: it drops every connection and the reconnect stampede usually lands right back at the ceiling.
  • Capture pg_stat_activity NOW (who holds slots, states) — it is the evidence and it changes minute to minute.
  • If one application just deployed with a bigger pool, scale that deployment's replicas DOWN a notch (or restore the old pool size) — the fastest lever is usually the newest change.

Fast checks

  • Count by state: a wall of idle in transaction = leaked transactions (app bug); many active doing the same query = a stampede; many idle = pools oversized for their traffic.
  • Compare count(*) from pg_stat_activity to max_connections — and remember RDS reserves some for admin/superuser.
  • One database/user or many? One claimant = its pool math is wrong; everything at once = instance-level sizing.

Diagnostic commands

-- Slots by state — idle-in-transaction is the usual thief:
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC;
-- Headroom check: current vs configured ceiling:
SELECT count(*) AS in_use, current_setting('max_connections') AS max_connections FROM pg_stat_activity;
-- Who has been idle-in-transaction longest (locks and slots held for nothing):
SELECT pid, usename, application_name, now() - state_change AS idle_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC LIMIT 10;
-- Claimants by application/user — which pool is over its share:
SELECT usename, coalesce(application_name, '(none)') AS app, count(*) AS slots
FROM pg_stat_activity GROUP BY 1, 2 ORDER BY slots DESC LIMIT 10;
-- Free slots held by leaked transactions older than 30 minutes (verify the list, then run):
SELECT pg_terminate_backend(pid)  -- deliberate kill of confirmed-leaked sessions only
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '30 minutes';

Likely causes

1. Pools sized per-instance without multiplying by instance count: 10 apps × 20 connections × 6 replicas = 1200 > max_connections. 2. Leaked transactions: missing commit/rollback on an error path → idle in transaction rows that never release. 3. Retry stampede: a slow query backs up request handlers, each holding a connection, while health checks open new ones. 4. Long-running maintenance (VACUUM, pg_dump, big migrations) consuming slots during peak. 5. Connection churn without a pooler: open-per-request workloads thrash auth and slot allocation.

Decision tree

  • Wall of idle in transaction → terminate the stale ones (stopgap), fix the app's transaction leak (the real fix).
  • All slots one application → its pool × replicas exceed the budget: recompute and cap (see Prevention formula).
  • Slots active on the same slow query → it is a slow-query incident wearing a connection costume: chase the query, not the count.
  • Spike during maintenance window → reschedule or throttle the maintenance connection count.

Remediation

  • Free slots now: terminate only sessions that are provably leaked (idle-in-transaction past a threshold, or a confirmed-dead claimant) — terminating an ACTIVE session kills real work.
  • Shrink the loudest pool (config + redeploy) or scale its replicas down until the DB breathes; a smaller pool queues inside the app instead of failing at the DB — that is the correct side to queue on.
  • Enable/point services at a pooler (PgBouncer / RDS Proxy) for transaction pooling: hundreds of app connections multiplex onto a few DB slots.
  • Fix the leak: every transaction gets commit/rollback on all paths (framework transaction scopes, finally, or BEGIN hygiene); add a statement_timeout and idle_in_transaction_session_timeout so leaks self-limit.

Verification

  • pg_stat_activity count comfortably under max_connections (target ≤80%) at peak traffic for 30+ minutes.
  • Zero idle in transaction older than a few minutes; pool waiters back at zero; error rate recovered.
  • After the durable fix (pool math / pooler / leak fix): a load test or the next peak does NOT re-saturate.

Escalation

  • Service owner of the dominant claimant (their pool/leak); DBRE for max_connections changes or pooler rollout; leadership if write-path loss threatens data-consuming processes.
  • Hand-off state: state counts, top claimants, terminated pids, current pool sizes per app, what changed recently.

Prevention

  • Budget explicitly: max_connections ≥ (sum over services of pool_size × replicas) + admin reserve + 20% headroom — reviewed at every scale-up.
  • idle_in_transaction_session_timeout (e.g. 15–30 min) and per-role connection limits (ALTER ROLE … CONNECTION LIMIT) so no single app can eat the instance.
  • PgBouncer/RDS Proxy for anything churning connections; alert at 80% of max_connections and on idle-in-transaction age.

Sources

Know which failure modes you actually have?

A free OpsRoadmaps assessment scores your controls with explainable findings and builds a prioritized roadmap — runbook coverage is one of the things it measures.

Assess my stack

Runbooks document self-reported practice. Commands and thresholds were last reviewed 2026-09-19 — verify against your own environment before acting.