Pool Sizing

Pool max is per process, so max times the number of replicas must stay inside the database's connection limit.

Syntaxnew Pool({ max: Number(process.env.DB_POOL_MAX ?? 10) })

A pool's max is per process, not per application. Run six copies of your service with max: 20 each and the database sees 120 connections. Each such copy is a replica: one of several identical instances of your program running at the same time, usually behind a load balancer, often each in its own container, which is a packaged copy of your app and everything it needs to run.

Postgres caps the total with max_connections, typically 100 by default, and superuser_reserved_connections holds 3 of those back for administrators. Go over and new connections are refused with too many clients already, which takes down the whole service rather than one request.

How it works

Do the arithmetic before you deploy. Replicas times max, plus the migration job, the scheduled tasks, and the psql session someone has left open, has to stay under the cap. Read the real cap with SHOW max_connections; rather than assuming it.

A bigger pool is not a faster database. Every Postgres connection is a separate server process with its own memory, and the real work is still limited by CPU cores and disk. Five to twenty connections per replica is an ordinary setting.

What trips people up

pool.waitingCount is how many callers are queued for a connection. Steadily above zero with an idle database means the pool is too small; with a saturated database, a larger pool only makes queries slower. When replica count makes the arithmetic impossible, put PgBouncer in front and give the fleet one shared pool. In its transaction pooling mode, session-level features such as LISTEN and a session SET stop working.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Decide the pool max for a service that is about to go from two replicas to ten.
  • Tell 'the database is slow' apart from 'requests are queueing for a connection' using waitingCount.
  • Keep enough connection headroom that a deploy's migration job never hits 'too many clients already'.
  • Give long-running workers a smaller pool than the request-serving replicas.

More examples

Pool size as configuration, not a constant

Reach for this the first time the same image runs with a different replica count in two places. connectionTimeoutMillis is the setting people miss: without it, exhausting the pool looks like a hang rather than an error you can see in the logs.

Example · javascript
import { Pool } from 'pg';

// One replica on a laptop can afford a big pool; twelve replicas sharing
// one database cannot. The number belongs in the environment.
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: Number(process.env.DB_POOL_MAX ?? 10),

  // Close a connection that has sat unused this long (default 10000).
  idleTimeoutMillis: 10_000,

  // Stop waiting for a free connection after 5s. The default is 0, which
  // waits forever and turns a busy pool into a hung request.
  connectionTimeoutMillis: 5_000,

  // Retire a connection after this long, so a database restart or a
  // failover does not leave stale connections in the pool forever.
  maxLifetimeSeconds: 1800,
});

Logging the three pool counters to see which way to tune

These three numbers answer the question a dashboard of response times cannot: whether the latency is the database working or your own requests queueing for a connection. Log them before changing max, not after.

Example · javascript
import { pool } from './db.js';

// totalCount: connections the pool holds, busy or idle.
// idleCount:  held and not in use.
// waitingCount: callers queued, waiting for one to be released.
setInterval(() => {
  console.log(JSON.stringify({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  }));
}, 10_000).unref(); // unref so this timer never keeps the process alive

// waiting > 0 and the database is idle      -> the pool is too small
// waiting > 0 and the database is saturated -> queries are too slow, or
//                                              the pool is already too big
// idle == total for long stretches          -> max is larger than needed

Budgeting the whole fleet, not just the web process

Write this table down somewhere in the repository. The failure it prevents is a scale-up that looks harmless, exhausts max_connections, and takes out the background workers rather than the service that grew.

Example · javascript
// Every process that opens its own pool counts against max_connections.
//
//   api        4 replicas x max 10 = 40
//   worker     2 replicas x max  5 = 10
//   cron       1 replica  x max  2 =  2
//   migrations 1 job      x max  1 =  1  (runs during a deploy only)
//   humans     psql, admin tools   =  5  (keep a few spare)
//   ---------------------------------------
//   total                            58  of 100 - 3 reserved = 97
//
// The workers deliberately get a smaller pool than the API: they are few
// and each one runs long queries, so more connections buy nothing.
//
// api deployment:    DB_POOL_MAX=10
// worker deployment: DB_POOL_MAX=5
// cron deployment:   DB_POOL_MAX=2
//
// Doubling the api to 8 replicas would reach 98 and refuse connections, so
// that scale-up comes with DB_POOL_MAX=5, or with PgBouncer in front.

Related

node db connectnode db transactionsnode db mongonode k8s scalingnode process env

Discussion

  • Be the first to comment on this lesson.