Connection Pooling: Node.js, Postgres, PgBouncer

Your API is humming along at 200 requests per second. Traffic doubles during a launch, you scale your Node.js service from 4 pods to 20, and within seconds Postgres starts rejecting connections: FATAL: sorry, too many clients already. Every new pod opened its own pool, the math went past max_connections, and now healthy requests fail because the database ran out of room to talk to them.

Here is the uncomfortable part: adding more app servers made things worse, not better. A Postgres connection is not free, and treating it like one is the fastest way to take your database down under load. This post is about serving thousands of concurrent requests on a few dozen connections, using the app-side pool correctly and putting PgBouncer in front of Postgres when the pool alone is not enough.

Why one Postgres connection is heavy

Postgres uses a process-per-connection model. Every client connection forks a dedicated backend process on the server, with its own memory for the parser, planner, caches, and work_mem allocations. That process exists whether it is running a query or sitting idle mid-transaction.

So connections cost RAM and scheduler time, and there is a hard ceiling: max_connections (default 100). Idle connections still hold a backend process. Once you cross the limit, new connects fail outright:

FATAL: sorry, too many clients already

The naive fix, raising max_connections to 2000, backfires. Each backend competes for CPU and memory, context-switching overhead climbs, and throughput drops even though the number looks generous. Postgres is happiest with a small, busy set of connections, not a large, mostly-idle one.

node-postgres Pool basics

The pg library gives you a client-side pool so you are not opening a fresh TCP + TLS + auth handshake per query. You create one Pool per process and reuse it.

import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                       // default is 10
  idleTimeoutMillis: 30_000,     // drop idle clients after 30s
  connectionTimeoutMillis: 5_000 // fail fast if the pool is exhausted
})

// pool.query() checks out a client, runs the query, and releases it for you
const { rows } = await pool.query('SELECT id, email FROM users WHERE id = $1', [userId])

pool.query() is the shortcut for single statements: it acquires an idle client, runs the query, and returns it to the pool automatically. For a multi-statement transaction you must check out one client and keep it, because BEGIN, your writes, and COMMIT all have to run on the same backend:

const client = await pool.connect()
try {
  await client.query('BEGIN')
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from])
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to])
  await client.query('COMMIT')
} catch (err) {
  await client.query('ROLLBACK')
  throw err
} finally {
  client.release() // ALWAYS release, even on error, or you leak a connection
}

Forgetting client.release() in a finally is the classic pool leak: the client is never returned, the pool slowly starves, and eventually every request hangs on connectionTimeoutMillis.

Why bigger is not better

The instinct is to raise max until the errors stop. Do not. If every one of your 20 pods sets max: 50, you are asking Postgres for up to 1000 connections. The pool is a limiter, not a throttle you loosen under pressure.

A useful mental model: your database can only truly run a handful of queries in parallel (bounded by CPU cores and disk). Past that point, extra connections just queue inside Postgres instead of queueing in your app, and you lose visibility. A sane starting point is small:

per-pod max  ~=  (target total connections) / (number of pods)

If Postgres allows 100 connections, you reserve some for migrations and admin, and you run 10 pods, then max: 8 per pod is a reasonable start (80 total). Measure before you raise it. Watch SELECT count(*) FROM pg_stat_activity for total and active connections, track how long clients wait to be checked out, and watch p99 query latency. If connections sit idle, you are oversized; if requests pile up waiting for a client while Postgres CPU is low, you are undersized. This is the same “measure the resource, do not guess the number” discipline as tuning the AWS SDK’s socket pool with maxSockets.

When the app pool is not enough: PgBouncer

Client-side pools solve reuse within one process. They do nothing about the multiplication across processes. Twenty pods, serverless functions that scale to hundreds of instances, or a mix of services all hitting the same database will blow past max_connections no matter how careful each pool is.

That is what PgBouncer is for. It is a lightweight connection pooler that sits between your apps and Postgres. Your apps open cheap connections to PgBouncer; PgBouncer maintains a small pool of real backend connections to Postgres and hands them out as needed.

sequenceDiagram
    participant Pods as 20 Node.js pods
    participant PB as PgBouncer
    participant PG as Postgres

    Pods->>PB: ~500 cheap client connections
    PB->>PG: ~25 real backend connections
    Note over PB,PG: many clients multiplexed<br/>onto few backends
    PB-->>Pods: query results

PgBouncer pooling modes

PgBouncer has three modes, and choosing the right one is the whole game.

• Session pooling (the default): a Postgres backend is assigned to a client for the entire life of the client connection, released only when the client disconnects. Safe and fully compatible with every Postgres feature, but it barely improves on your app pool since a long-lived client still pins a backend.
• Transaction pooling: a backend is assigned only for the duration of a transaction. As soon as you COMMIT or ROLLBACK, the backend goes back to the pool and can serve another client. This is where the multiplexing win comes from, and it is what most high-concurrency setups use.
• Statement pooling: like transaction pooling, but transactions spanning multiple statements are disallowed, so every statement effectively autocommits. Niche; you probably do not want it.

The transaction-mode caveats that bite

Transaction pooling breaks session-level state by design, because the backend under your feet changes between transactions. The docs are blunt: it breaks a few session-based features of Postgres, so you can only use it when the application cooperates by not leaning on them. Concretely:

• No session state that outlives a transaction. A plain SET search_path = ... or SET statement_timeout = ... run on its own applies to whatever backend you happened to get, then that backend serves someone else. Same for LISTEN, session-level advisory locks, and WITH HOLD cursors. Do not rely on them.
• Prepared statements depend on version and config. SQL-level PREPARE/DEALLOCATE is never supported in transaction mode. Protocol-level prepared statements (what pg uses when you pass a name) are handled through max_prepared_statements. PgBouncer 1.24 and newer ship it enabled by default (200); older builds defaulted to 0 (off), so you had to set it yourself. Set it to 0 and prepared statements stop working, which is where those “prepared statement does not exist” errors come from.
• SET LOCAL is your friend. Unlike SET, SET LOCAL is scoped to the current transaction, so it is automatically cleaned up at COMMIT/ROLLBACK and is safe under transaction pooling. This matters directly for row-level security in multi-tenant Postgres: you set the tenant inside the transaction and let it evaporate with the transaction.

BEGIN;
-- transaction-scoped: safe under PgBouncer transaction pooling
SET LOCAL app.current_tenant = '42';
SELECT * FROM invoices;  -- RLS policy reads current_setting('app.current_tenant')
COMMIT;

If you ran that as SET instead of SET LOCAL, the tenant id would leak onto a shared backend and the next request on it could read the wrong tenant’s data. In transaction pooling, that is a security bug, not just a style nit.

Placement: app pool AND PgBouncer

These are not either/or. Keep a small pg Pool in each process (it still saves handshakes and gives you per-process backpressure), and point its connectionString at PgBouncer instead of Postgres directly. PgBouncer in transaction mode then collapses the total down to a tiny set of real backends. Background workers that open transactions to claim jobs with SELECT … FOR UPDATE SKIP LOCKED benefit the same way, as long as each unit of work is a clean transaction.

One rule for transaction mode: know your max_prepared_statements setting (PgBouncer 1.24+ enables it by default, older builds do not) before you rely on client-side prepared statements, and never depend on session state surviving between queries.

Takeaway

Postgres connections are heavy because each one is a real server process. Your app-side pg Pool exists to reuse them; keep max small and size it by measuring pg_stat_activity and checkout wait time, not by turning the dial up when errors appear. When many processes multiply past max_connections, put PgBouncer in transaction mode in front of the database, use SET LOCAL for anything session-scoped, and configure prepared statements deliberately. Do that and a few dozen backends will happily serve thousands of concurrent clients.

References

• PgBouncer features and pooling modes
• PgBouncer configuration reference (pool_mode, max_prepared_statements)
• node-postgres: Pool API
• node-postgres: Pooling guide
• PostgreSQL: Connections and Authentication (max_connections)
• PostgreSQL: SET command (SET LOCAL scope)