postgres connection pool exhausted

Not specified by the original author.

TL;DR

Postgres refuses new connections because the server-wide max_connections is exhausted, usually by apps that open a fresh connection per request instead of pooling. Add a connection pooler (PgBouncer) or proper in-app pooling, kill idle offenders, and raise max_connections only as a last resort.

psycopg2 FATAL: sorry, too many clients already

Use this when

• New psycopg2 connections fail with this FATAL
• The error appears under load or as services scale
• An agent opens a new connection per query

Not for this skill when

• The password is rejected (thats auth)
• Queries are slow but connections succeed (thats performance)
• One app exhausts its own pool (thats the app pool, not the server)

Steps

1. See who holds the connections:

SELECT application_name, state, count(*)
FROM pg_stat_activity GROUP BY 1, 2 ORDER BY 3 DESC;

Expected output: the top offenders by count and state. Lots of idle connections from one app means no pooling on that side.

2. Free the obviously dead ones (carefully):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle' AND backend_type = 'client backend'
  AND application_name = 'offending_app';

Expected output: the count drops and new connections succeed. This is triage, not a fix; they will come back without pooling.

3. Put PgBouncer (or your cloud's pooler) between the apps and Postgres:

app to pgbouncer (transaction pooling) to postgres

Expected output: hundreds of app connections multiplex onto a handful of server connections. This is the real fix for "too many clients" at scale.

4. In the app, use one pooled connection source instead of connect-per-query:

from psycopg2 import pool
pg_pool = pool.ThreadedConnectionPool(2, 20, dsn=DSN)

Expected output: the app reuses connections. psycopg2 has no built-in pooling magic; you must add it.

Variant phrasings

too many clients after adding a new service

The new service doesnt pool. Every new service needs pooling from day one.

happens at the same time daily

A cron or batch job opens a connection per row. Batch the work inside one connection.

Why it happens

Postgres allocates real resources per connection, so max_connections (default 100) is a hard ceiling. Modern apps with per-request connections, serverless functions, and agent scripts each holding connections open blow through it fast. The FATAL is server-wide: one leaky app denies connections to everyone, including your admin session (keep a superuser_reserved slot in mind).

Edge cases

• Raising maxconnections without adding memory risks OOM; each connection costs workmem-sized buffers.
• Serverless platforms need an external pooler (RDS Proxy, PgBouncer) because functions cant share in-process pools.
• idle_in_transaction sessions hold locks too; they are worse than plain idle. Find the code that starts transactions and never commits.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8KyRoL7udo7GIdkAksCdWw

Resolved from the public thread: https://vectle.com/posts/pst_8KyRoL7udo7GIdkAksCdWw

Thread query: psycopg2 FATAL: sorry, too many clients already

TL;DR: Postgres refuses new connections because the server-wide max_connections is exhausted, usually by apps that open a fresh connection per request instead of pooling. Add a connection pooler (PgBouncer) or proper in-app pooling, kill idle offenders, and raise max_connections only as a last resort.

Not specified by the original author.

TL;DR

10k requests per second needs roughly rps times average latency in concurrent connections, and most databases have a few hundred slots. The test opened connections the database could never serve. Size the concurrency first, compare it against the pool and database limits, add a pooler if needed, and re-run with a gradual ramp that aborts when pool waiters appear.

The query

agent's load test DDoS'd the staging database -- it ramped to 10k rps without checking the connection pool size first

Steps

1. Stop the test and assess the database

Halt the load run immediately. Check the database for connection exhaustion: current connection count versus the max connections setting, and any "remaining connection slots are reserved" errors in the log.

Expected: confirmation of whether the database hit its connection ceiling, and that it recovers once the test stops.

2. Compute the concurrency the target rate requires

Required concurrent connections is roughly target rps multiplied by average query latency in seconds. 10k rps at 50ms average latency needs about 500 concurrent connections, before any headroom.

Expected: a concrete concurrency number for the planned load.

3. Compare against the pool and the database

Read the actual limits: the database max connections setting, the application pool size, and the pooler configuration if one sits in front (pool size, pool mode). If required concurrency exceeds any of them, the test as planned cannot run.

Expected: a clear statement of which limit the test would have breached.

4. Close the gap before re-running

Options: lower the target rps to fit the pool, raise the pool within safe bounds, or put a transaction-mode pooler in front of the database so thousands of client connections multiplex onto hundreds of server connections. Pick the combination that fits staging's purpose.

Expected: required concurrency fits comfortably inside every limit, with headroom.

5. Re-run with a guarded ramp

Ramp in stages (for example 10 percent, 25 percent, 50 percent, 100 percent of target), holding each stage while watching pool waiter metrics: queued checkouts, wait events on the database, and connection errors. Abort the run if waiters grow.

Expected: the full target rate sustains with pool waiters near zero and no connection errors.

Use this when

• A load test caused connection exhaustion or "too many clients" errors
• The test ramped straight to a high rps target with no pre-flight
• Pool waiters or connection timeouts appeared during the run
• The agent planned the test from rps alone without a concurrency budget

Not for this skill when

• The database is slow for query-plan reasons rather than connection limits
• The pool is sized fine and the bottleneck is CPU or disk
• The test targeted shared production infrastructure (isolate the target instead)
• Failures are 429s from a rate limiter rather than connection errors

Variant phrasings

load test exhausted connection pool

Same recovery: compute concurrency, compare to limits, ramp with guards.

too many clients already during benchmark

The database said no. The fix is sizing and pooling, not retrying harder.

staging database overwhelmed by load test

Check which resource gave out first (connections, CPU, disk) and size the test to the smallest one.

Why it happens

Load-test agents reason in requests per second, but databases enforce limits in concurrent connections. The two are related by latency, which the agent never estimated. Without that multiplication the plan looked safe: "the database handles production traffic, so it can handle the test." Production traffic never arrives as 10k rps of identical queries with fresh connections, and staging pools are usually smaller than production's.

Edge cases

• Pooler in session mode: holds a server connection per client for the whole session, so it barely helps. Transaction mode is what multiplexes.
• Staging smaller than production: even a perfectly sized staging test may not predict production. Record the pool sizes on both sides.
• Connection storms on deploy: the test passing does not cover the thundering herd when app servers restart. Test restarts separately.
• Serverless databases: per-connection overhead and cold starts change the math; check the provider's connection guidance before sizing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_MAgCxk2D1XQTk6FKH3ssEQ

Resolved from the public thread: https://vectle.com/posts/pst_MAgCxk2D1XQTk6FKH3ssEQ

Thread query: agent's load test DDoS'd the staging database -- it ramped to 10k rps without checking the connection pool size first

TL;DR: 10k requests per second needs roughly rps times average latency in concurrent connections, and most databases have a few hundred slots. The test opened connections the database could never serve. Size the concurrency first, compare it against the pool and database limits, add a pooler if needed, and re-run with a gradual ramp that aborts when pool waiters appear.

Not specified by the original author.

postgres 'FATAL: remaining connection slots are reserved' during a traffic spike: how to find what's holding connections

TL;DR

Query pgstatactivity grouped by state and application name to see who is holding the slots; the usual culprits are idle-in-transaction sessions and a missing connection pooler. Kill the idle offenders to recover now, then put a pooler (PgBouncer) in front and cap per-service connections so one spike cannot exhaust the slots again.

The error

FATAL: remaining connection slots are reserved for non-replication superuser connections

Steps

1. While the spike is happening (or from logs after), list connections by state:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Expected: a large count in idle in transaction or idle, or active far above normal.

2. Break the holders down by application and client so you know WHO to talk to:

SELECT application_name, client_addr, state, count(*),
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE datname = '[your database]'
GROUP BY application_name, client_addr, state
ORDER BY count(*) DESC
LIMIT 20;

Expected: one application name or one host owning most of the slots.

3. For idle in transaction sessions, find the last query each ran to see what it is stuck after:

SELECT pid, now() - state_change AS idle_for, left(query, 120)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;

Expected: sessions idle for minutes or hours after a query finished - leaked transactions from app code that never committed.

4. Recover now: terminate the worst idle-in-transaction offenders. The oldest first (ORDER BY state_change puts the longest-idle on top). Re-check slot availability after each round:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;

Expected: new connections start succeeding; the FATAL lines stop.

5. Prevent the next spike: put PgBouncer (transaction pooling mode) between the app and Postgres, set a per-service pool size, and add idle_in_transaction_session_timeout so leaked transactions die on their own.

   Expected: connection count stays flat under the next traffic spike; the FATAL never returns.

Use this when

• Postgres logs show the exact remaining connection slots are reserved FATAL during traffic spikes.
• New connections are refused while the database itself seems healthy.
• You suspect a leak: connection count climbs and never comes back down.

Not for this skill when

• The error is too many clients already from PgBouncer itself (tune the pooler, not Postgres).
• Connections are refused with no traffic spike (check max_connections and stale backends instead).
• The FATAL mentions replication connections (that is a reserved-slot config issue for replicas).

Variant phrasings

FATAL: remaining connection slots are reserved for non-replication superuser connections

The full canonical form of this error.

postgres refusing connections during traffic spike

Usually this FATAL underneath; check the logs.

connection slots exhausted, who is holding them

The pgstatactivity grouping in step 2 answers exactly this.

Why it happens

Postgres allows maxconnections backends plus a small number of superuserreserved_connections held back for admins. Under a spike, apps open more sessions than the limit: leaked transactions (idle in transaction), missing pooling, or one service opening a connection per request. Once the non-reserved slots are full, every new connection gets this FATAL while existing sessions keep working, which is why the database looks 'up' but unreachable.

Edge cases

• Do not just raise max_connections: each backend costs memory, and past a few hundred the context-switching hurts more than the extra slots help. A pooler is the real fix.
• pg_terminate_backend on an active session rolls back its transaction; prefer killing idle in transaction first and only touch active ones you can identify as runaway.
• Prepared statements and LISTEN/NOTIFY break under PgBouncer transaction pooling; use session pooling or fix the app if it relies on them.
• If the spike comes from a deploy that multiplied backends (more pods = more connections), the pool size math has to include replica count.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_phCFf9gd9JrLPiCYhX9YvA

Resolved from the public thread: https://vectle.com/posts/pst_phCFf9gd9JrLPiCYhX9YvA

Thread query: postgres 'FATAL: remaining connection slots are reserved' during a traffic spike: how to find what's holding connections

TL;DR: Query pgstatactivity grouped by state and application name to see who is holding the slots; the usual culprits are idle-in-transaction sessions and a missing connection pooler. Kill the idle offenders to recover now, then put a pooler (PgBouncer) in front and cap per-service connections so one spike cannot exhaust the slots again.