Skip to content
Sandeep Kumar
Updated 5 min read

Postgres connection pooling with node-postgres and PgBouncer

Why Postgres runs out of connections before it runs out of CPU, how to size a node-postgres Pool, and what PgBouncer transaction mode breaks.

Sandeep Kumar, Full-stack EngineerSandeep KumarFull-stack Engineer
Postgres connection pooling with node-postgres and PgBouncer: Forty instances wanted four times what Postgres allows
On this page
  1. Why a Postgres connection is expensive: it is a process
  2. Count your connections on paper
  3. Serverless multiplies every pool
  4. PgBouncer transaction mode: what it saves and what it breaks
  5. Which pool mode to pick
  6. Conclusion

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.

code
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% headroom

Then make the pool honest:

ts
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: 1 per 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:

2,000client connections PgBouncer acceptsmax_client_conn
20server connections to Postgresdefault_pool_size
Transaction mode: many clients, a few real connections
ini
[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 = 200

Here'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 SET is the one I would grep for first. Run SET search_path = tenant_42 per request and that setting sticks to the server connection; the next client to get it reads tenant 42's invoices. Use set_config(..., true) or SET LOCAL inside 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().
  • LISTEN needs 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 when max_prepared_statements is above zero. The docs list a default of 200, but early 1.21 releases shipped with 0, so check yours. SQL PREPARE still does not work.
ts
// 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?

FAQs

What does 'sorry, too many clients already' mean in Postgres?

The server has reached max_connections and refuses new ones. It almost never means Postgres is overloaded in CPU terms. It means the total of all connection pools, workers and tools pointing at it is larger than the limit, usually because more app instances started than the pool sizes were planned for.

How big should my node-postgres pool be?

Start from the database limit and divide it among everything that connects at peak, then leave about 20% free for migrations, admin tools and replication. For a single app server, a pool of 10 to 20 is plenty for most workloads, because a query that waits in the pool queue for a millisecond is cheaper than a query that runs alongside 100 others on the same cores.

Should I use PgBouncer in session or transaction mode?

Transaction mode for web apps and serverless functions, because it releases the server connection at the end of each transaction and lets far more clients share it. Session mode only when the app depends on session state such as LISTEN/NOTIFY, session advisory locks or temporary tables that live across transactions, and then it saves little over connecting directly.

Do prepared statements work with PgBouncer?

Protocol-level prepared statements work in transaction mode from PgBouncer 1.21 onwards when max_prepared_statements is set above zero; PgBouncer tracks them and re-prepares them on whichever server connection a client gets. SQL-level PREPARE and DEALLOCATE commands still do not work in transaction mode.

Found this useful?Share on XShare on LinkedIn

Related reading