Budget connection pools

Opening a PostgreSQL connection is not free. The server forks work for each one, and it enforces a hard ceiling: max_connections, 100 by default. A connection pool keeps a few connections open and hands them out to requests, so your application neither pays the setup cost per query nor stampedes the server.

The part people miss is that a pool is a budget, and the budget is cluster-wide.

Create one bounded pool in each application process:

import pg from 'pg'

const { Pool } = pg

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5,
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 30000,
  statement_timeout: 10000,
  application_name: 'notes-web',
})

Each setting bounds a different failure. max: 5 caps how many connections this process can hold. connectionTimeoutMillis stops a request from waiting forever for a free client. idleTimeoutMillis returns unused connections to the server. statement_timeout kills runaway queries instead of letting them occupy a connection for minutes. application_name labels these connections so you can recognize them later.

Do the multiplication

Five connections is an example budget. Eight replicas with a pool of five can still request forty connections. Autoscaling makes this worse: the platform adds replicas under load, and every new replica brings its whole pool allowance with it, right when the database is busiest.

Leave room for workers, migrations, and emergency access. If the application fleet can consume every available connection, the day something goes wrong is the day you cannot open a psql session to investigate.

Verify the budget from the server

Count what the server actually sees:

SELECT application_name, count(*)
FROM pg_stat_activity
WHERE datname = 'notes_app'
GROUP BY application_name;

 application_name | count
------------------+-------
 notes-web        |    10
 notes-worker     |     3

That application_name label now pays off: you can attribute every connection to a service and check the totals against your budget.

When the budget is blown, the symptom is FATAL: sorry, too many clients already from the server, or pool timeout errors inside the application. Do not fix that by raising max. Find which service is over budget or leaking first.

One more habit: call await pool.end() when a script or worker shuts down. Short-lived scripts that skip it leave connections lingering until the server times them out, and enough of those eat the budget too.