AI AI Tools Guidebook
Troubleshooting

Supabase Connection Pool Exhausted

`error: remaining connection slots are reserved` — Serverless code hitting raw Postgres.

Published: Author: AI Productivity Guide Team 🌐 查看中文版本

Your Vercel / Cloudflare / Netlify functions all 500 simultaneously. Logs are full of:

error: remaining connection slots are reserved for non-replication
       superuser and rds_superuser connections

Or:

sorry, too many clients already

Supabase (really Postgres) connection pool exhaustion. Your Free plan gives 60 connections, Pro 200 — sounds like a lot, but Serverless platforms spin up 100 instances and each grabs one. The real problem isn’t “too few connections” — it’s that Serverless + direct Postgres is an anti-pattern.

Mental model: Postgres connections are heavy (~10MB RAM each), not lightweight like HTTP. Serverless concurrent traffic must go through a connection pooler (Supabase has Supavisor / PgBouncer built in).

Common causes

Ordered by hit rate, highest first.

1. Serverless connecting directly on :5432 (no pooler)

Most common. Code uses Dashboard’s “Direct connection” string. Each Lambda / Worker instance opens its own connection. Burst traffic → 100 instances → 100 connections → blow up.

How to spot it: Is the connection string postgres://...supabase.com:5432/postgres or :6543? 5432 = direct, 6543 = pooler.

2. Connections never released / long transactions

const client = await pool.connect();
const result = await client.query('SELECT ...');
// ❌ forgot client.release()

Leak one connection per request, exhausted in hours.

How to spot it: Connection count climbs monotonically without dropping.

3. pgbouncer mode set to session

Supavisor defaults to transaction, but some tutorials switch to session (better compat but holds connections). Session mode kills pooling benefits.

How to spot it: Connection string has ?pgbouncer=true&pool_mode=session?

4. Statements PgBouncer can’t pool

Prepared statements, LISTEN, temporary tables don’t work in transaction-mode pooling. ORMs (e.g., Prisma) use prepared statements by default — needs special config.

How to spot it: Errors like prepared statement "X" does not exist.

5. Background cron jobs running slow queries

Scheduled analytics queries that take 30s — meanwhile normal traffic needs connections — pool eaten by cron.

How to spot it: Errors cluster around cron trigger times.

6. Side-channel tools (pgAdmin, Metabase) holding connections

You / coworkers point GUI tools directly at prod DB — each GUI holds 5-10 connections.

How to spot it: SELECT * FROM pg_stat_activity on the DB shows application_name from those tools.

Shortest path to fix

Step 1: Emergency relief — kill old connections

-- In Supabase Dashboard SQL Editor
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Kill idle-in-transaction older than 5 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '5 minutes';

Frees connections immediately so service recovers — then fix properly.

Step 2: Switch to pooler connection string

Supabase Dashboard → Project Settings → Database → Connection string:

Direct (avoid in Serverless):
postgres://postgres:[password]@db.xyz.supabase.co:5432/postgres

Supavisor pooler (Serverless = mandatory):
postgres://postgres.xyz:[password]@aws-0-region.pooler.supabase.com:6543/postgres

Update code / env var to use the pooler. Restart services.

Step 3: Verify pool_mode

postgres://...@...:6543/postgres?pgbouncer=true
                                  ^^^^^^^^^^^^
                              unspecified = transaction (recommended)

If you explicitly set &pool_mode=session, remove it — session mode defeats pooling.

Step 4: Prisma / ORM specifics

// .env
DATABASE_URL="postgres://...@:6543/postgres?pgbouncer=true&connection_limit=1"
DIRECT_URL="postgres://...@:5432/postgres"  // for migrations

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")  // prisma migrate uses direct
}

Prisma + pgbouncer requires ?pgbouncer=true&connection_limit=1.

Step 5: Always release queries

// ❌ Missing release
const client = await pool.connect();
await client.query(...);
// no finally

// ✅ Correct
const client = await pool.connect();
try {
  await client.query(...);
} finally {
  client.release();
}

// Or simpler: pool.query auto-releases
await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Step 6: Monitoring + alerts

-- Current active connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Long idle-in-transaction (release leak signal)
SELECT pid, query, query_start, state
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '1 minute';

Alert when connection count > 80% of cap.

Step 7: Upgrade plan / connection limit

Pro plan defaults to 200 connections, Team 400, Enterprise tunable. Short term, upgrade. Long term, still need pooling done right.

Prevention

  • Serverless = always :6543 pooler; never touch :5432
  • Only long-running backends (Fly.io, Railway, EC2) use direct :5432
  • With an ORM, set connection_limit=1 (each serverless instance needs only 1)
  • Every query: await + try/finally release
  • Monitor connection count, alert above 80% of cap
  • Cron / batch jobs use their own connection or service — don’t compete for the main pool
  • Forbid direct laptop → prod DB connections; use Supabase Studio or a read replica
  • For high-traffic projects, model out connection needs on day 1 — Free’s 60 burns fast

Tags: #Indie dev #Debug #Troubleshooting

Related Articles