One pool per database, one budget per server: connecting to many PostgreSQL databases at once

The problem

The data source service runs queries against databases it doesn't own. Users register a connection string and a read-only query, and the service executes it on demand. Over time there were several databases, and several of them lived on the same PostgreSQL server, a server shared with about ten other applications and with only around 15 free connection slots on a normal day.

The original code opened a fresh connection for every query:

const client = new pg.Client({ connectionString });
await client.connect();
const result = await client.query(sql);
await client.end();

For one query at a time, that works. For a batch of 23 sources, it opened 23 connections at the same instant, and the shared server refused the overflow with 53300 too many clients. (The investigation itself is in "Random nulls: the bug that hid behind a silent catch".)

The obvious fix is "use a pool". But this service has a twist that most pooling advice doesn't cover: it doesn't know its databases in advance. They're defined at runtime by users. So the question wasn't "how big should my pool be?" but:

How do you limit connections to a server when the number of databases on it can change at any time?

The details

Why one big pool doesn't work

A pg.Pool is bound to one connection string: one host, one database, one user. A pool for database A can't run a query on database B. So the service needs many pools, one per connection string.

Why many pools are dangerous

Here is the trap. Suppose each pool gets max: 5, which sounds modest:

And pools keep idle connections open for reuse. So even when nothing is running, several pools can quietly hold slots on the shared server. A per-pool limit alone isn't a real limit. The true ceiling is the sum of every pool's max on that server.

The key insight: budgets belong to servers, not databases

PostgreSQL's max_connections is a property of the server. Database A, database B and database C on the same host all draw from the same 100 slots. So the limit has to be enforced per host, across all pools that point to it.

The challenges

• Pools are created dynamically. A new source on a new database can appear at any time; its pool must join the right budget automatically.
• The budget must stay constant as pools are added. A new database on a host should shrink everyone's share, not raise the total.
• Connection strings come in different formats. Most are URLs, but not all, so finding "the host" must be safe.
• A full pool must wait, not fail. If our own pool is full, a query should queue. Failing fast would just re-create the null bug from our own side.
• Idle connections must give their slots back. Holding connections forever between short bursts wastes the shared budget.
• A dropped idle connection must not crash the process. That's a classic node-postgres trap.

The solution

1. A provider that owns all pools

A single injectable provider holds every pool for the life of the application:

@Injectable()
export class SourcePoolProvider implements OnModuleDestroy {
  private readonly hostBudget = readHostBudget();             // e.g. 3
  private readonly pools = new Map<string, pg.Pool>();        // connection string → pool
  private readonly membersByHost = new Map<string, Set<string>>(); // host → its connection strings

  getPool(connectionString: string): pg.Pool {
    const existing = this.pools.get(connectionString);
    if (existing) return existing;                            // reuse, never recreate

    const host = hostKeyOf(connectionString);
    const pool = new pg.Pool({
      connectionString,
      max: 1,                         // real value set by rebalance() below
      idleTimeoutMillis: 10_000,      // give idle slots back after 10 s
      connectionTimeoutMillis: 10_000 // wait for a free connection, don't fail fast
    });

    pool.on('error', (err) => logger.warn(`Idle connection error on ${host}: ${err.message}`));

    this.pools.set(connectionString, pool);
    const members = this.membersByHost.get(host) ?? new Set<string>();
    members.add(connectionString);
    this.membersByHost.set(host, members);

    this.rebalance(host);
    return pool;
  }
}

Pools are created lazily, on the first query that needs them, and keyed by connection string. Nothing is hard-coded: a database added by a user tomorrow gets its pool the first time one of its sources runs.

2. Group pools by host

function hostKeyOf(connectionString: string): string {
  try {
    const url = new URL(connectionString);
    return `${url.hostname}:${url.port || '5432'}`;
  } catch {
    // Not URL-shaped (e.g. "host=… dbname=…"): give it its own budget
    // rather than guessing which server it shares.
    return connectionString;
  }
}

The fallback is deliberate: when the host can't be parsed, the safest guess is "its own server", so it never steals from a budget it might not belong to.

3. Rebalance: split the budget fairly

Every time a pool joins a host, all pools on that host are resized:

private rebalance(host: string) {
  const members = this.membersByHost.get(host);
  if (!members) return;
  const perPool = Math.max(1, Math.floor(this.hostBudget / members.size));
  for (const connectionString of members) {
    const pool = this.pools.get(connectionString);
    if (pool) pool.options.max = perPool;
  }
}

With a budget of 9 on a host:

Adding a database shrinks the shares instead of growing the total. (Math.max(1, …) guarantees every database can still run at least one query at a time.)

This works because node-postgres's pool reads options.max every time it hands out a connection, so changing it takes effect immediately. When a share shrinks, connections already open above the new limit aren't killed mid-query. They're simply closed by the idle timeout once they're free.

4. Wait instead of failing

connectionTimeoutMillis is set generously (10 seconds) on purpose. When our pool is full, a query waits in our queue until a connection is free. A short timeout here would turn "busy for 300 ms" into an error, and recreate the original null problem with our own hands.

This is the real change of behavior:

That's also why the batch handler can still use Promise.all over all codes: the pool serializes the work behind the budget, so parallel requests no longer mean parallel connections.

5. Give idle connections back

Bursts are short (about a second) and rare. With idleTimeoutMillis: 10_000, connections are returned 10 seconds after the burst. The steady-state cost on the shared server is zero, instead of every pool hoarding its share forever.

6. Don't crash on a dropped idle connection

When an idle connection in a pool loses its socket (server restart, network blip, idle killer on the server side), node-postgres emits an 'error' event on the pool. With no listener, Node.js treats it as an unhandled error event and crashes the whole process. One line prevents that:

pool.on('error', (err) => logger.warn(`Idle connection error on ${host}: ${err.message}`));

The broken idle connection is discarded, and the next query simply gets a new one.

7. Clean shutdown

On application shutdown, every pool is closed:

async onModuleDestroy() {
  const pools = [...this.pools.values()];
  this.pools.clear();
  this.membersByHost.clear();
  await Promise.all(pools.map((p) => p.end().catch(() => undefined)));
}

Deploys don't leave orphaned connections on someone else's server.

8. Choosing the budget

The budget is an environment variable, so it can change without a release. Its value followed the architecture:

• While queries ran on the user's path, the budget was 9, enough for a 23-code batch to complete in 1–2 seconds with a peak of 8 connections.
• After results were stored and served from our own database (see "Stop querying live"), the only live queries left were a slow nightly job, refresh-on-edit and a source's first fill. The budget dropped to 3.

Measured with the pool in place: the same 23-code batch, five runs, 0 nulls, peak 8 connections, back to 0 twelve seconds later.

Lessons learned

• Pooling advice assumes one database. With many runtime-defined databases, you need many pools and a shared limit.
• Limit what the server limits. max_connections is per server, so your budget must be per server too, not per database or per pool.
• The real ceiling is the sum. Idle pools hold slots; add up every pool's max on a host to know your true footprint.
• New members should shrink shares, not grow the total. Rebalancing keeps the budget constant as databases are added.
• When your pool is full, wait. A generous acquire timeout turns contention into latency instead of errors.
• Listen for pool errors. Without an 'error' listener, one dropped idle socket can take down the process.
• Return idle connections. On a shared server, being polite between bursts is free.
• Size the budget to the architecture. Once the hot path stopped querying live, the budget could shrink from 9 to 3.

FAQ

How do I connect to multiple PostgreSQL databases from one Node.js app?

Create one pg.Pool per connection string, reuse it for the life of the app, and create it lazily the first time it's needed. If several databases live on the same server, also cap the total connections across those pools.

Why does node-postgres crash with an unhandled 'error' event?

When an idle client in a Pool loses its connection, the pool emits an 'error' event. If nothing listens to it, Node.js throws it as an uncaught error and the process exits. Always add pool.on('error', …).

Can I change a node-postgres pool's max size at runtime?

Yes. The pool reads options.max whenever it hands out a client, so updating it takes effect for the next checkout. Connections above a lowered limit are released by the idle timeout rather than closed immediately.

Should connectionTimeoutMillis be short or long?

It depends on what you want when the pool is full. A short value fails fast. A longer value makes requests wait for a free connection. When failures would be stored or shown to users, waiting is usually the better choice.