Why does Postgres say sorry, too many clients already while its CPU sits at 12%? Because it is not busy, it is full: every connection slot is taken, and most of the processes holding them are idle.
Postgres allows 100 connections by default. Forty serverless instances with node-postgres's default pool of 10 want 400. Nothing in the code changed on launch day; the number of copies of the code did.
This post shows how to:
- count connections on paper
- size node-postgres pools
- handle serverless
- run PgBouncer in transaction mode without the four things it breaks.
Why a Postgres connection is expensive: it is a process
In MySQL a connection is a thread. In Postgres it is an operating system process, forked for each client, with its own memory for caches and plans plus work_mem for every sort.
The Postgres docs set max_connections to 100 by default, with three reserved for superusers.
I think of that limit as a feature. A server with 8 cores runs about 8 queries at the same instant, and a thousand connections only make a thousand processes fight over those cores, memory and locks.
Postgres 14 made idle connections much cheaper, but active ones still compete. The mistake is in how we spend the slots.
Count your connections on paper
Every app instance runs a node-postgres Pool with a default max of 10. On a normal day a small serverless app runs three instances: 30 connections.
On launch day it scales to forty, and forty pools of ten want 400 connections from a server that allows 97.
- Normal day, 3 instances × 1030 connections
- Postgres allows (100 − 3 reserved)97 connections
- Launch day, 40 instances × 10400 connections
peak instances × pool max
+ workers × their pool max
+ cron, migrations, admin panel, BI tool, your laptop
= total, which must stay under max_connections minus ~20% headroomThen make the pool honest:
import { Pool } from "pg";
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10, // per instance; see the sum above
idleTimeoutMillis: 10_000, // give slots back when an instance goes quiet
connectionTimeoutMillis: 2_000, // default 0 waits forever; fail fast instead
});That last line matters most. With the default, a request that cannot get a connection waits until something upstream times out, and the user sees a hang with no error anywhere.
As for max, go smaller than you think. The HikariCP write-up on pool sizing suggests roughly cores times two for the whole database. I don't treat it as exact, but its direction is right: ten connections with a millisecond of queueing usually beat fifty all running at once.
Serverless multiplies every pool
On a long-running server you have a few processes with one pool each. On serverless, every concurrent instance is its own process with its own pool, and a frozen instance can keep its connections open while idle.
Forty instances is just a good afternoon. Two changes fix most of it:
- Set
max: 1per serverless instance, since most platforms send one request at a time to an instance. - Put a pooler between the functions and the database. Most managed Postgres providers offer a pooled connection string in transaction mode; use it for the app and keep the direct string for migrations. If you run Postgres yourself, the pooler is PgBouncer.
PgBouncer transaction mode: what it saves and what it breaks
PgBouncer speaks the Postgres protocol, so the app only changes its connection string (usually to port 6432).
It holds a few real server connections and lends them out. pool_mode decides when a client gives one back:
| Mode | Server connection returns to the pool | Good for |
|---|---|---|
session (default) |
when the client disconnects | apps relying on session state; saves little |
transaction |
when each transaction ends | web apps, serverless, almost everyone |
statement |
after every statement | rare; multi-statement transactions refused |
In transaction mode, 2,000 clients can share 20 server connections as long as they are not all mid-transaction at once:
[databases]
app = host=10.0.0.5 port=5432 dbname=app
[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
max_prepared_statements = 200Here's the catch: your next statement may run on a different server connection. Anything that lives on the connection outside a transaction is lost or shared with strangers.
The feature table lists it all. Four items bite real apps:
- Plain
SETis the one I would grep for first. RunSET search_path = tenant_42per request and that setting sticks to the server connection; the next client to get it reads tenant 42's invoices. Useset_config(..., true)orSET LOCALinside a transaction, or better, row-level filtering, as in multi-tenant data isolation. - Session advisory locks have the same problem; use
pg_advisory_xact_lock(). LISTENneeds a stable session, so give the listener a direct connection.- Protocol-level prepared statements (node-postgres queries with a
name) work from PgBouncer 1.21 whenmax_prepared_statementsis above zero. The docs list a default of 200, but early 1.21 releases shipped with 0, so check yours. SQLPREPAREstill does not work.
// Safe in transaction mode: everything session-like is scoped to the transaction.
const client = await pool.connect();
try {
await client.query("BEGIN");
await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
await client.query("SELECT pg_advisory_xact_lock($1)", [invoiceSeqLockId]);
const { rows } = await client.query({
name: "next-invoice-number",
text: "UPDATE tenants SET last_invoice = last_invoice + 1 WHERE id = $1 RETURNING last_invoice",
values: [tenantId],
});
await client.query("COMMIT");
return rows[0].last_invoice;
} catch (e) {
await client.query("ROLLBACK");
throw e;
} finally {
client.release();
}Which pool mode to pick
For almost any web app I would pick:
- transaction mode
- small per-instance pools
- a direct connection kept aside for migrations and
LISTEN.
Then audit the code once for plain SET, session locks and SQL PREPARE. Each works perfectly in development, where there is no PgBouncer, and fails strangely in production.
Session mode I would avoid. It feels safe because nothing breaks, and it does nothing for many clients that stay connected and mostly idle.
The result? Forty instances each open one connection to PgBouncer, which holds 20 to Postgres with maybe 12 busy, and the database spends its time working instead of waiting.
Conclusion
Count your connections on paper today: peak instances times pool size, plus everything else that connects. If the total is near max_connections, put PgBouncer in transaction mode in front and shrink the pools.
Connection count is capacity planning, so redo it whenever instance limits change. The rest of that conversation is in what I check before a traffic spike, and if the database is busy for real once connections are fixed, read the Postgres indexes I'd add first.
How many connections does your app open at peak, and have you ever counted?