Postgres too many clients: Pool Sizing and Connection Exhaustion
Each process caches its own pool and nothing coordinates them. Divide the ceiling by replicas, put a transaction-mode pooler in front, and stop idle-in-transaction sessions from holding slots.

Introduction & Quick Summary
Symptom. FATAL: sorry, too many clients already on connect, or FATAL: remaining connection slots are reserved for non-replication superuser connections, or from a serverless function Timed out fetching a new connection from the connection pool / Supabase's Max client connections reached with code: EMAXCONNSESSION. Existing queries keep working; every new connection fails, and the application appears to hang at startup or under the first burst of traffic.
Cause. PostgreSQL accepts at most max_connections backends (default 100, tuned to 200–500 on most managed plans). Each application process usually opens a pool of its own, so 10 pods × 20 connections is 200 connections before any traffic. Serverless functions multiply that: one instance per concurrent request, each creating a pool. Leaked connections (idle in transaction, an unclosed client after an exception) hold slots until a timeout, so the ceiling is reached and stays reached.
Fix. Put a pooler in front (PgBouncer, or Supavisor in transaction mode), shrink per-process pools to fit max_connections / instances, set idleTimeoutMillis/idle_in_transaction_session_timeout, and find the leak with pg_stat_activity. Raising max_connections alone buys very little: each backend costs memory, and the real limit is often the plan's connection cap.
Core Mechanics, Signatures & Reproducible Setup
A connection in PostgreSQL is a forked process. max_connections counts them; superuser_reserved_connections (default 3) keeps a few for administration so you can still get in and run pg_terminate_backend. An application-side pool (pg, psycopg, SQLAlchemy, Prisma, JDBC) is a cache of those processes, and nothing coordinates the caches between instances.
Reproduce the failure locally to see the messages:
-- as postgres
show max_connections; -- 100 by default
select count(*), state from pg_stat_activity group by state;# open 101 idle clients
for i in $(seq 1 101); do (psql "postgresql://app@localhost/app" -c 'select pg_sleep(300)' &) ; done
psql "postgresql://app@localhost/app" -c 'select 1'
# FATAL: sorry, too many clients alreadyDiagnose from the one connection you still have:
select state, count(*), max(now() - state_change) as oldest
from pg_stat_activity
where backend_type = 'client backend'
group by state
order by 2 desc;idle in transaction with a large oldest is the signature of a leak: the client began a transaction and neither committed nor closed, which also blocks vacuum. idle with many rows and a growing count is a pool that is too large or duplicated across instances.
Key Comparison Table
| Setup | Connection per client | Prepared statements | Use for |
|---|---|---|---|
| Direct connection | 1 backend | Yes | Long-lived single process, admin work |
| PgBouncer session mode | 1 backend while connected | Yes | Legacy apps that need session state |
| PgBouncer transaction mode | Backend only during a transaction | No (unless max_prepared_statements 1.21+) | Most web apps |
| Supavisor transaction mode | Shared, multiplexed | Limited | Supabase serverless and edge |
| Serverless with a per-invocation pool | 1 per instance | No | Only with a pooler in front |
| Setting | Where | Effect |
|---|---|---|
max_connections | Server, needs restart | Hard ceiling per cluster |
superuser_reserved_connections | Server | Slots reserved for admin |
idle_in_transaction_session_timeout | Server or role | Kills abandoned transactions |
pool.max / connection_limit | App | Connections per instance |
pool.idleTimeoutMillis | App | How long an idle connection is kept |
statement_timeout | Server or role | Stops a query from pinning a backend |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. Sizing the pool against the ceiling
max_connections = 100
pooler_max_client_conn = 200 (PgBouncer side)
reserved for admin = 3
application instances = N
pool per instance ≈ floor((max_connections - reserved - 10) / N)With 8 pods and no pooler: floor((100 - 3 - 10) / 8) = 10 connections per pod, and that is the whole budget. Every extra replica silently pushes the cluster over. A pooler changes the arithmetic because clients connect to the pooler, not to Postgres.
// node-postgres — explicit and bounded
import { Pool } from 'pg';
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: Number(process.env.PG_POOL_MAX ?? 10),
idleTimeoutMillis: 30_000,
connectionTimeoutMillis: 5_000,
});
process.on('SIGTERM', () => pool.end()); // release slots on shutdown2. Serverless: one pool per invocation is the trap
A serverless handler that constructs a Pool at module scope keeps it for the lifetime of the instance — fine when instances are reused, fatal when the platform scales to 200 concurrent instances. On Supabase, use the pooler connection string on port 6543 (transaction mode) for serverless and the direct 5432 connection only for migrations and long-lived workers.
// Prisma + Supabase pooler: limit the pool, disable prepared statements
// DATABASE_URL="postgresql://...@aws-0-region.pooler.supabase.com:6543/postgres?pgbouncer=true&connection_limit=1"connection_limit=1 looks extreme and is the documented answer for serverless: the pooler multiplexes, so one client connection per instance is enough.
3. Transaction mode and prepared statements
-- Fails through a transaction-mode pooler:
PREPARE p AS select 1; EXECUTE p; DEALLOCATE p;
-- ERROR: prepared statement "p" does not exist (the next statement lands on a different backend)Any feature that assumes the same backend across statements breaks: PREPARE, LISTEN, session-level SET, advisory locks, temporary tables. The fixes are statement_cache_size=0 for asyncpg, pgbouncer=true for Prisma, prepare_threshold=0 / prepare=False for psycopg 3, and useServerPrepStmts=false for MySQL-style drivers — each driver has one switch and it must be on.
4. Finding and ending the leak
-- Blockers and abandoned transactions
select pid, usename, application_name, state,
now() - state_change as stuck_for,
left(query, 60) as query
from pg_stat_activity
where state in ('idle in transaction', 'active')
and now() - state_change > interval '5 minutes'
order by stuck_for desc;
-- Terminate one
select pg_terminate_backend(12345);
-- Preventive: per-role limits
alter role app set idle_in_transaction_session_timeout = '30s';
alter role app set statement_timeout = '30s';idle_in_transaction_session_timeout is the single highest-value setting for a pool that fills up: it reclaims slots from code that forgot to commit, and it turns a slow leak into a logged error instead of an outage.
5. Reserved slots: getting back in
psql "postgresql://postgres@host/postgres?options=-c%20statement_timeout%3D5000" \
-c "select count(*) from pg_stat_activity where state='idle';"The superuser reserved slots only help a role with SUPERUSER (or pg_read_all_stats for visibility). On managed Postgres, the owner role is usually superuser-like; on Supabase it is postgres. If you cannot get in at all, the platform's SQL editor uses its own pooled connection and usually still works — that is the way back in to terminate backends.
6. Pool timeouts read like database outages
Timed out fetching a new connection from the connection pool.
(More info: http://pris.ly/d/connection-pool (Current connection count: 10, Connection limit: 10))The database is healthy; the pool is exhausted because every connection is busy on a slow query. Check pg_stat_activity for active with long query_start, then fix the query or add statement_timeout. Increasing the pool usually makes it worse — more connections competing for the same CPU.
Common Compilation/Runtime Mistakes & Version Gotchas
| Mistake | Symptom | Correct approach |
|---|---|---|
Raising max_connections as the first fix | Slow recovery, memory pressure | Pooler and per-instance limits |
| A pool per request | Count grows with traffic | Module-scope pool, or pooler |
No idleTimeoutMillis | Idle connections never released | 30s, plus server-side timeout |
| Pool per pod sized for one pod | Works at 2 replicas, fails at 10 | Divide by the max replica count |
Session SET through a transaction pooler | Value disappears | SET LOCAL inside the transaction |
| Prepared statements via PgBouncer | prepared statement does not exist | Disable statement caching |
| Not closing clients in tests | CI exhausts the dev database | afterAll(() => pool.end()) |
Version notes: PgBouncer 1.21+ supports max_prepared_statements for transaction mode, so the "no prepared statements" rule has an exception on a recent pooler — verify the version before enabling driver-level statement caching. PostgreSQL 17 changed nothing about max_connections but reduced per-connection memory, which is why some managed plans raised the default cap around that release. Supabase's Supavisor requires the pgbouncer=true flag for Prisma even though the pooler is not PgBouncer: the flag disables prepared statements, which is the actual requirement.
For the surrounding work, the SQL formatter makes a long pg_stat_activity query readable, the URL tool decodes a connection string with query parameters (pgbouncer=true&connection_limit=1) so you can confirm pooling flags actually made it into the deployment, and the JSON formatter is useful when a platform returns the connection error as a JSON body inside a 500.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesPostgres RLS 42P17: infinite recursion detected in policy
A policy on members that reads members re-enters itself. One security definer function with a pinned search_path ends the recursion and leaves the rest of the policy intact.
SQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.