Fix Postgres connection pool exhaustion from serverless runtimes

TL;DR — If your serverless app talks directly to managed Postgres, the most common failure mode is too many concurrent instances each opening their own pool. Confirm it with pg_stat_activity and your app logs, then fix it by putting a transaction-capable pooler in front of Postgres or by reducing app-side pool sizes to 1-2 and reusing a single client per warm instance. Reading time: ~6 min

The scenario

You push a routine Tuesday deploy that adds one more API endpoint and a background job. Traffic is not huge, but your serverless platform scales out fast after a newsletter send, and suddenly requests start failing intermittently. The database dashboard still shows CPU and storage are fine, but the app logs fill with connection timeouts and remaining connection slots are reserved for non-replication superuser connections. Users see random 500s, retries make it worse, and every new cold start seems to pile on more broken connections.

Symptoms

• Intermittent 500, 502, or 504 from API routes that hit Postgres.
• App logs contain one or more of these verbatim:

FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved for non-replication superuser connections
Error: connect ETIMEDOUT 10.0.12.34:5432
Error: timeout exceeded when trying to connect
SequelizeConnectionAcquireTimeoutError: Operation timeout
PrismaClientInitializationError: Timed out fetching a new connection from the connection pool
psycopg_pool.PoolTimeout: couldn't get a connection after 30.00 sec

• Managed Postgres metrics show connections pegged near max_connections while CPU is normal or only mildly elevated.
• pg_stat_activity shows many sessions in idle, idle in transaction, or many nearly identical connections from the same app user.
• Errors spike during bursts, deploys, cron fan-out, queue drains, or after enabling higher serverless concurrency.
• User-visible pattern: first requests after scale-out fail more often than requests handled by already-warm instances.

Likely causes

Step-by-step diagnosis

1. Check whether Postgres is actually hitting connection limits.

SELECT current_setting('max_connections') AS max_connections;
SELECT count(*) AS current_connections FROM pg_stat_activity;
SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;

This is your problem if current_connections is close to max_connections, especially with many idle or idle in transaction sessions. Jump to Fixes → Each serverless instance creates its own DB pool or Fixes → Connections leaked by not closing clients / transactions left open.

1. Identify who is opening the connections.

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

This is your problem if you see many connections from the same app user/application name, often from many ephemeral client IPs or NAT addresses. Jump to Fixes → No external connection pooler and Fixes → App-side pool size is too high.

1. Check whether your app is direct-connecting to Postgres instead of using a pooler.

printf '%s
' "$DATABASE_URL"

This is your problem if the URL points straight at your managed Postgres host on port 5432 and you do not intentionally run a pooler. Example shape:

postgres://app_user:***@db-prod.xxxxx.region.provider.com:5432/app

Jump to Fixes → No external connection pooler between serverless runtime and Postgres.

1. Inspect your codebase for pool size and client lifecycle.

grep -RniE 'new Pool\(|max:|pool_size|connection_limit|pgbouncer=true|createPool\(|Sequelize\(|PrismaClient\(' .

This is your problem if you find pool sizes like 10, 20, or framework defaults repeated across API handlers, or if a new client is created inside the request handler rather than module scope. Jump to Fixes → App-side pool size is too high.

1. Look for leaked transactions and clients.

SELECT pid, usename, application_name, now()-xact_start AS tx_age, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY tx_age DESC;

This is your problem if rows stay here for minutes and the query text matches app code paths. Jump to Fixes → Connections leaked by not closing clients / transactions left open.

1. Check for long-running queries occupying the pool.

SELECT pid, now()-query_start AS age, wait_event_type, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC
LIMIT 20;

This is your problem if a few queries run for tens of seconds or minutes during the incident. Jump to Fixes → Long-running queries hold connections.

1. Correlate with burst sources: deploys, cron, queues, webhook retries.

• In your provider dashboard, open the function/service metrics page and compare request concurrency or invocation count against DB connection count.
• In your job runner or queue dashboard, check the worker concurrency setting and recent run fan-out. This is your problem if connection spikes line up with batch jobs or retries rather than normal user traffic. Jump to Fixes → Serverless fan-out from jobs, webhooks, or parallel batch code.

Fixes

Each serverless instance creates its own DB pool, multiplying connections past Postgres limits

Reduce per-instance pool size aggressively and reuse one client or pool per warm instance.

Node pg example:

import { Pool } from 'pg';

const pool = globalThis._pool ?? new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 2,
  min: 0,
  idleTimeoutMillis: 10000,
  connectionTimeoutMillis: 5000,
  allowExitOnIdle: true
});

if (!globalThis._pool) globalThis._pool = pool;

export default pool;

Bad pattern to remove:

export async function handler(req, res) {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
  const result = await pool.query('select 1');
  res.json(result.rows);
}

If your runtime supports per-instance concurrency > 1, keep max small anyway; serverless scale-out is the multiplier.

Verify it worked:

SELECT count(*) FROM pg_stat_activity WHERE usename = 'app_user';

Connection count should flatten well below max_connections during the same traffic level.

No external connection pooler between serverless runtime and Postgres

Put a transaction-pooling proxy in front of Postgres and point the app at it. Generic options are PgBouncer you run yourself or a managed pooler your DB provider exposes.

Minimal PgBouncer config:

[databases]
app = host=db.internal port=5432 dbname=app user=app_user password=REDACTED

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
default_pool_size = 50
max_client_conn = 1000
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

Point the app at the pooler:

export DATABASE_URL='postgres://app_user:REDACTED@pgbouncer.internal:6432/app'

Trade-off: transaction pooling breaks session-level features such as temp tables across transactions, session advisory locks, LISTEN/NOTIFY on the same session, and some prepared statement behavior unless your driver/framework is configured for pooler compatibility.

Verify it worked:

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

You should see far fewer backend connections on Postgres than app requests.

App-side pool size is too high for burst concurrency

Set pool size based on worst-case instance count, not on a single VM mental model.

Rule of thumb for serverless direct connections:

safe_pool_max_per_instance = floor((db_max_connections - admin_headroom) / max_serverless_instances)

Example: max_connections=100, reserve 20 for admin/migrations/other apps, max instances 40 => floor((100-20)/40)=2.

Concrete settings:

PGPOOLSIZE=2
DB_POOL_MAX=2
PRISMA_CLIENT_ENGINE_TYPE=binary

Prisma example URL parameter if supported by your setup:

DATABASE_URL="postgresql://app_user:REDACTED@pgbouncer.internal:6432/app?connection_limit=2"

Sequelize example:

pool: { max: 2, min: 0, acquire: 5000, idle: 10000 }

Verify it worked:

grep -RniE 'max: 2|connection_limit=2|DB_POOL_MAX=2|PGPOOLSIZE=2' .

Then load the endpoint and confirm DB connections do not scale linearly with invocations.

Connections leaked by not closing clients / transactions left open

Always release clients in finally, and keep transactions tight.

Node pg pattern:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET last_seen = now() WHERE id = $1', [id]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Python psycopg pattern:

with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("select 1")

If you find stuck sessions now, terminate only the app user sessions after confirming they are safe to kill.

⚠️ Terminating sessions will abort in-flight requests and can cause user-visible errors. Do not kill migration sessions or unknown admin sessions.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user'
  AND state = 'idle in transaction';

Verify it worked:

SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction' AND usename = 'app_user';

It should return 0 or near-zero under normal load.

Long-running queries hold connections so the pool cannot recycle them fast enough

Find the slow query, add the missing index, or cap statement duration.

Find heavy queries:

SELECT pid, now()-query_start AS age, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC
LIMIT 10;

Inspect plan:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Add an index example:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created_at ON orders (customer_id, created_at DESC);

Set a timeout at role or app level:

ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

Trade-off: timeouts turn latent slowness into explicit errors; that is usually preferable during incidents because it frees connections.

Verify it worked:

SELECT now()-query_start AS age, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 5;

The oldest active queries should be much shorter.

Serverless fan-out from jobs, webhooks, or parallel batch code causes connection storms

Throttle the source of concurrency and batch DB work.

Examples:

// Instead of Promise.all(items.map(doDbWork))
for (const item of items) {
  await doDbWork(item);
}

Or bounded concurrency:

import pLimit from 'p-limit';
const limit = pLimit(5);
await Promise.all(items.map(item => limit(() => doDbWork(item))));

For queue workers, lower worker concurrency in your provider dashboard or worker config so worst-case concurrent DB users fit your connection budget.

Verify it worked:

• Re-run the job with the lower concurrency setting.
• Confirm pg_stat_activity no longer spikes to the previous peak.

Prevention

1. Add a connection-budget alert tied to max_connections.

SELECT round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity;

Alert at >70% sustained for 5 minutes and page at >85%.

1. Pin conservative pool sizes in code and env, and review them in CI.

# ci/check-db-pool.sh
if grep -RniE 'max:\s*([3-9]|[1-9][0-9]+)|connection_limit=([3-9]|[1-9][0-9]+)' src/; then
  echo 'DB pool too large for serverless runtime';
  exit 1;
fi

1. Add DB timeouts at the role level so leaks and slow queries self-clear.

ALTER ROLE app_user SET statement_timeout = '15s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET lock_timeout = '5s';

1. Emit application_name per service so pg_stat_activity is actionable.

export DATABASE_URL='postgres://app_user:REDACTED@pgbouncer.internal:6432/app?application_name=api'

Or driver config equivalent. This lets you separate API, worker, migration, and cron traffic immediately.

1. Load-test connection behavior before deploys, not just request latency.

k6 run --vus 50 --duration 2m script.js

During the test, watch:

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

Fail the release if connections approach your alert threshold.

1. Keep migrations and admin tooling off the same connection budget during incidents. Use a separate DB role for migrations and reserve headroom in your connection math. If your DB allows it, set lower connection limits for app roles and keep an admin path available for recovery.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.