Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readSQL
#SQL#PostgreSQL#Performance#Pooling
3D illustration of client cubes queueing at a database with a limited slot rack

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:

sql
-- as postgres show max_connections; -- 100 by default select count(*), state from pg_stat_activity group by state;
bash
# 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 already

Diagnose from the one connection you still have:

sql
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

SetupConnection per clientPrepared statementsUse for
Direct connection1 backendYesLong-lived single process, admin work
PgBouncer session mode1 backend while connectedYesLegacy apps that need session state
PgBouncer transaction modeBackend only during a transactionNo (unless max_prepared_statements 1.21+)Most web apps
Supavisor transaction modeShared, multiplexedLimitedSupabase serverless and edge
Serverless with a per-invocation pool1 per instanceNoOnly with a pooler in front
SettingWhereEffect
max_connectionsServer, needs restartHard ceiling per cluster
superuser_reserved_connectionsServerSlots reserved for admin
idle_in_transaction_session_timeoutServer or roleKills abandoned transactions
pool.max / connection_limitAppConnections per instance
pool.idleTimeoutMillisAppHow long an idle connection is kept
statement_timeoutServer or roleStops a query from pinning a backend

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. Sizing the pool against the ceiling

code
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.

ts
// 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 shutdown

2. 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.

ts
// 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

sql
-- 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

sql
-- 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

bash
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

code
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

MistakeSymptomCorrect approach
Raising max_connections as the first fixSlow recovery, memory pressurePooler and per-instance limits
A pool per requestCount grows with trafficModule-scope pool, or pooler
No idleTimeoutMillisIdle connections never released30s, plus server-side timeout
Pool per pod sized for one podWorks at 2 replicas, fails at 10Divide by the max replica count
Session SET through a transaction poolerValue disappearsSET LOCAL inside the transaction
Prepared statements via PgBouncerprepared statement does not existDisable statement caching
Not closing clients in testsCI exhausts the dev databaseafterAll(() => 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 tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.