too many connections" Postgres: connection pooling fix

TL;DR

Put PgBouncer in transaction pooling mode between your apps and Postgres, and shrink each apps pool max. The error means Postgres hit max_connections, and the fix is almost never raising that number: it is funneling hundreds of app connections through a small pool of real server connections.

FATAL: sorry, too many clients already

Use this when

• Apps fail with "too many clients already"
• pg_stat_activity shows hundreds of idle connections
• New services or workers keep adding connections until the DB falls over

Not for this skill when

• Connections fail with authentication or timeout errors (not a count problem)
• Queries are slow but connections succeed
• Transactions deadlock (different error, different fix)

Steps

1. Confirm the problem is connection count, and see who holds them:

SELECT state, count(*), string_agg(DISTINCT usename, ', ') AS users
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Expected output: a breakdown like 400 idle, 30 active. If most are idle, pooling will fix it. If most are active, you have a workload problem too.

1. Check the ceiling and how close you are:

SHOW max_connections;
SELECT count(*) AS current FROM pg_stat_activity;

Expected output: something like 100 max with 97 in use. Note that superuserreservedconnections (default 3) are held back, so apps hit the wall a few connections early.

1. Kill the worst offenders right now to restore service. Idle-in-transaction connections are the first target because they hold locks:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '10 minutes';

Expected output: a set of true values, one per terminated backend. This buys time; it doesnt fix the leak.

1. Deploy PgBouncer in transaction pooling mode in front of Postgres:

[databases]
maindb = host=db.internal port=5432 dbname=maindb
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Expected output: apps connect to PgBouncer (port 6432) instead of Postgres directly. Transaction mode reuses one server connection per transaction, so 1000 app connections need only ~25 real ones. Point every app at the pooler.

1. Shrink the per-app pools so the pooler stays the funnel, not Postgres:

# example: SQLAlchemy
engine = create_engine(db_url, pool_size=5, max_overflow=5, pool_timeout=30)

Expected output: each app instance holds at most 10 connections to the pooler. Sum the maxes across all services and keep the total comfortably under PgBouncers maxclientconn.

1. Set guardrails so one bad deploy cant wedge the database again:

ALTER DATABASE maindb SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE maindb SET statement_timeout = '60s';

Expected output: runaway transactions get killed automatically. These are per-database settings that survive restarts.

Variant phrasings

postgres FATAL sorry too many clients already

The exact error. Steps 1-3 triage, steps 4-5 fix it structurally.

too many connections for role

You hit a per-role connection limit (ALTER ROLE ... CONNECTION LIMIT), not max_connections. Raise the role limit or pool that roles connections.

pgbouncer vs raising max_connections

Raising max_connections past a few hundred hurts: each backend costs memory and context-switch overhead. Pooling is the correct fix at any scale that hits the limit.

Why it happens

Every Postgres connection is a full OS process with its own memory, so the server caps them at max_connections (default 100). Modern apps open pools per instance, per worker, per thread, and the totals add up fast while most connections sit idle. The pooler multiplexes: apps think they each have a dedicated connection, but the server only sees a small steady set.

Edge cases

• Transaction pooling breaks features that need session state: prepared statements, LISTEN/NOTIFY, advisory locks, and temp tables. Use session pooling or a direct connection for those workloads.
• Serverless or autoscaled apps can spike connection counts in seconds; put the pooler in front before you need it.
• PgBouncer itself needs sizing: defaultpoolsize times the number of databases is your real server-connection count.
• Connection leaks in app code (never closing) eventually exhaust even a pooler; fix the leak, the pooler just delays it.

Provenance

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