subashthiruppathy

Posted on Oct 10

Diagnosing and Fixing PostgreSQL Connection Pool Starvation in Node.js

#node #postgressql #database #performance

Here's a production incident that looks like a mystery until you've seen it once.

Requests start timing out. Latency graphs go vertical.
Node CPU is low. Memory is fine.
Postgres CPU is low. Queries that do run are fast.
Restarting the app "fixes" it... for a few hours.

Nothing is slow, yet everything is stuck. The culprit is usually connection pool starvation: all connections in your pool are checked out, and every new request is waiting in line for one that never comes back.

This post walks through how to confirm starvation, find the cause, and fix it, using node-postgres (pg), though the ideas apply to any pooled client.

What a pool actually does

Opening a Postgres connection is expensive (TCP, TLS, auth, backend process startup), so apps keep a small pool of open connections and lend them out.

 request ──▶ pool.connect() ──▶ [ conn1 conn2 conn3 ... connN ]  ──▶ Postgres
                  │                    (max = N)
                  └── if all N are busy: WAIT in queue

When every connection is checked out, new callers join a wait queue. If connections come back quickly, you barely notice. If they come back slowly, or never, the queue grows without bound and every request that needs the database hangs behind it.

Key point: starvation isn't about the database being slow. It's about connections being held too long or never returned.

Defaults worth knowing in pg:

Option	Default	Meaning
`max`	`10`	Maximum connections in the pool
`connectionTimeoutMillis`	`0`	No timeout waiting for a connection (waits forever!)
`idleTimeoutMillis`	`10000`	Close idle connections after 10s

That default of 0 is why starvation presents as a hang rather than an error. Fix that first (see below).

Step 1: Confirm it's starvation

Look at the pool's own counters

pg.Pool exposes three numbers:

pool.totalCount: connections currently open
pool.idleCount: open and available
pool.waitingCount: callers queued waiting for a connection

import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
});

setInterval(() => {
  console.log(JSON.stringify({
    msg: 'pg.pool',
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  }));
}, 5_000).unref();

The signature of starvation:

{"total":10,"idle":0,"waiting":47}
{"total":10,"idle":0,"waiting":112}
{"total":10,"idle":0,"waiting":240}

idle: 0 with a growing waiting means demand exceeds supply, and it's not draining. A healthy busy pool shows waiting bouncing near zero.

Look at what Postgres thinks those connections are doing

Now ask the database what the pool's connections are up to:

SELECT
  pid,
  state,
  now() - xact_start    AS xact_age,
  now() - state_change  AS in_state_for,
  wait_event_type,
  wait_event,
  left(query, 100)      AS last_query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

How to read the state column:

`state`	Meaning	What it implies
`active`	Running a query right now	Slow queries or lock contention
`idle`	Connected, nothing in progress	Healthy (or an app-side leak, if the pool says it's checked out)
`idle in transaction`	`BEGIN` was sent, no `COMMIT`/`ROLLBACK` yet	Prime suspect. The app is holding a transaction open while doing something else

The smoking gun is a pile of connections that are idle in transaction with large xact_age. Postgres isn't busy; your app is sitting on connections and not using them.

Compare the two views:

Pool says 10 checked out, Postgres shows most as idle (not in a transaction) → leaked clients (checked out, never released).
Pool says 10 checked out, Postgres shows idle in transaction → transactions held open across non-DB work or never closed.
Pool says 10 checked out, Postgres shows active with long runtimes → slow queries or lock waits; check wait_event_type = 'Lock'.
Pool isn't maxed out but you still see errors → look at Postgres-side max_connections (see "Sizing" below).

Cause 1: Leaked clients (forgot to `release()`)

The classic. You check out a client manually and some code path never releases it.

// ❌ Leaks the client if the query throws
async function getUser(id: string) {
  const client = await pool.connect();
  const { rows } = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  client.release();
  return rows[0];
}

If query throws (bad SQL, timeout, constraint error), release() is never reached. Do that ten times and your pool of ten is permanently empty. Nothing crashes. The app just slowly seizes up.

Fix: `try / finally`, always

// ✅ Always released
async function getUser(id: string) {
  const client = await pool.connect();
  try {
    const { rows } = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return rows[0];
  } finally {
    client.release();
  }
}

Better fix: don't check out manually for single statements

pool.query() checks out a client, runs the query, and releases it for you, even on errors:

// ✅ Simplest and safest for a single statement
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Only use pool.connect() when you genuinely need multiple statements on the same connection (transactions, session settings, advisory locks, cursors).

💡 If a client is in a bad state (for example, after a connection error), call client.release(true). Passing a truthy argument tells the pool to destroy that client instead of returning it to the pool.

Cause 2: Transactions that leak or linger

Transactions are leaks with extra steps. If BEGIN succeeds and the code then throws before COMMIT/ROLLBACK, the client goes back to the pool (or never does) while still inside a transaction, holding locks.

Don't hand-write transaction plumbing at every call site. Write one helper and use it everywhere. Note the release(err) detail: if ROLLBACK itself fails, the connection is likely broken, so we destroy it instead of returning it to the pool.

// db.ts
import type { PoolClient } from 'pg';

export async function withTransaction<T>(
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  let destroy: Error | undefined;
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    try {
      await client.query('ROLLBACK');
    } catch (rollbackErr) {
      destroy = rollbackErr as Error; // broken connection; don't return it to the pool
    }
    throw err;
  } finally {
    client.release(destroy);
  }
}

Usage:

await withTransaction(async (tx) => {
  await tx.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amt, from]);
  await tx.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amt, to]);
});

Now there is exactly one place where BEGIN, COMMIT, ROLLBACK, and release live, and it's correct.

Cause 3: Holding a connection while doing non-database work

This one is subtle and extremely common. The code is "correct" (everything is released) but connections are held far too long:

// ❌ The connection is held for the entire duration of the HTTP call
await withTransaction(async (tx) => {
  const { rows } = await tx.query('SELECT * FROM orders WHERE id = $1 FOR UPDATE', [id]);
  const quote = await fetch('https://shipping-partner.example/quote', { /* ... */ }); // 200ms-30s!
  await tx.query('UPDATE orders SET shipping_cost = $1 WHERE id = $2', [quote.cost, id]);
});

If the partner API gets slow, each in-flight request now pins a connection (and a row lock) for the length of that call. With a pool of 10 and a partner that takes 5 seconds, your maximum throughput is 2 requests per second, and Postgres shows idle in transaction everywhere.

Fix: do slow external work outside the transaction

// ✅ Fetch first, then open a short transaction
const quote = await fetchShippingQuote(id);               // no connection held

await withTransaction(async (tx) => {
  await tx.query('SELECT 1 FROM orders WHERE id = $1 FOR UPDATE', [id]);
  await tx.query('UPDATE orders SET shipping_cost = $1 WHERE id = $2', [quote.cost, id]);
});

Rule of thumb: a connection should be checked out only for as long as you're actually talking to the database. No HTTP calls, no file I/O, no await sleep, no CPU-heavy work, and no message-queue publishes inside a transaction. If you need to publish an event atomically with a DB change, use the outbox pattern rather than doing both in one long transaction.

Cause 4: Nested acquisition (self-inflicted deadlock)

This is the nastiest one because it works fine in dev and deadlocks under load.

// ❌ Holds connection A while waiting for connection B
async function createOrder(input) {
  return withTransaction(async (tx) => {
    const order = await tx.query('INSERT INTO orders ... RETURNING *', [...]);
    const customer = await getCustomer(input.customerId); // calls pool.query() => needs ANOTHER connection
    // ...
  });
}

With a pool of 10: if 10 requests enter createOrder simultaneously, each holds one connection and each now waits for a second connection that doesn't exist. Every connection is held by a request waiting on the pool. Nobody can make progress, ever. It's a textbook resource deadlock, and the pool's wait queue never drains.

Fix options

Pass the client down so inner code reuses the same connection:

async function getCustomer(id: string, db: Pick<Pool, 'query'> = pool) {
  const { rows } = await db.query('SELECT * FROM customers WHERE id = $1', [id]);
  return rows[0];
}

await withTransaction(async (tx) => {
  const customer = await getCustomer(input.customerId, tx); // same connection
});

Do the read before the transaction, if it doesn't need to be transactional.
Never "fix" it by just raising max. You've only raised the number of concurrent requests needed to trigger it.

To catch this in testing, set max: 1 or max: 2 in your test config. Any nested acquisition then deadlocks immediately instead of in production at peak.

Cause 5: Fan-out with `Promise.all`

// ❌ 500 concurrent queries against a pool of 10
const users = await Promise.all(ids.map((id) => pool.query('SELECT ... WHERE id = $1', [id])));

This doesn't leak anything, but one request can monopolize the entire pool, starving every other request while it works through the queue. It's also an N+1 query pattern in disguise.

Fixes

Batch it into one query:

const { rows } = await pool.query('SELECT * FROM users WHERE id = ANY($1::uuid[])', [ids]);

If you truly need many queries, limit concurrency (for example with p-limit) to a small slice of the pool:

import pLimit from 'p-limit';
const limit = pLimit(3); // leave headroom for other requests
const results = await Promise.all(ids.map((id) => limit(() => fetchOne(id))));

Cause 6: Slow queries and lock waits

Sometimes the connections really are busy. If pg_stat_activity shows active queries running for seconds, or wait_event_type = 'Lock', then connections are being held by work that's legitimately slow.

Find the long runners and what blocks them:

-- Longest-running active queries
SELECT pid, now() - query_start AS runtime, wait_event_type, wait_event, left(query, 120)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
ORDER BY runtime DESC
LIMIT 10;

-- Who is blocking whom
SELECT blocked.pid  AS blocked_pid,
       blocking.pid AS blocking_pid,
       left(blocked.query, 80)  AS blocked_query,
       left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Then do the usual database work: add the missing index, fix the query plan (EXPLAIN (ANALYZE, BUFFERS)), shorten lock-holding transactions, and avoid long SELECT ... FOR UPDATE sessions. A single forgotten idle in transaction connection holding a row lock can block dozens of others, which then pile up as active and further drain your pool.

Set safety nets so the next bug can't take you down

You'll never prevent every leak by code review alone. Add guardrails that turn hangs into fast, loud errors.

Client-side: fail instead of waiting forever

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,  // error if no connection within 5s (default is "wait forever")
  idleTimeoutMillis: 30_000,
  query_timeout: 15_000,           // client-side per-query timeout
  statement_timeout: 15_000,       // server-side: Postgres cancels statements over 15s
  idle_in_transaction_session_timeout: 10_000, // server-side: kills sessions stuck idle in a transaction
});

pool.on('error', (err) => {
  // Emitted when an idle client errors (e.g. server restarted). Without a handler, this crashes Node.
  logger.error({ err }, 'pg idle client error');
});

What each does:

connectionTimeoutMillis converts a silent hang into a timeout exceeded when trying to connect error you can alert on and return a 503 for, so load sheds instead of piling up.
statement_timeout stops a single runaway query from pinning a connection indefinitely.
idle_in_transaction_session_timeout is the strongest protection against Causes 2 and 3: Postgres terminates any session that sits idle in transaction too long. The connection is torn down, the transaction rolled back, and locks released.
Consider lock_timeout too, so statements give up rather than queue indefinitely behind a lock.

You can also set these at the database or role level (ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10s') so they apply to every client, not just this app.

⚠️ These timeouts are a safety net, not a fix. If idle_in_transaction_session_timeout is firing, you have a bug to find, so log and alert when it happens.

Shed load deliberately

Return a fast 503 with Retry-After when the pool is saturated, rather than letting requests queue until they time out at the load balancer:

app.use((req, res, next) => {
  if (pool.waitingCount > 50) {
    return res.status(503).set('Retry-After', '2').json({ error: 'busy' });
  }
  next();
});

Sizing the pool (and why "just increase max" is usually wrong)

Raising max feels like the obvious fix, and it's tempting because it makes the symptom disappear briefly. But:

It doesn't fix leaks. A leak drains any pool size. A bigger pool only delays the outage.
Postgres has a hard cap. Check SHOW max_connections;. Every connection is a separate backend process with memory overhead. Total connections across all app instances must fit:

instances × pool.max   +   other clients (migrations, cron, BI tools, admin)   ≤   max_connections

Ten pods with max: 20 is 200 connections before anything else connects.

More connections can mean lower throughput. Once concurrent active queries exceed what the database's CPU and I/O can actually run in parallel, extra connections just add contention and context switching. For many workloads, a pool in the tens (not hundreds) per database performs better than a huge one.

Practical approach: start modest, measure waitingCount and query times under realistic load, and increase only if you see waiting without leaks or long holds, and only while Postgres still has headroom.

When you do need many app connections: PgBouncer

If you have many app instances or serverless functions, put a pooler like PgBouncer between them and Postgres. In transaction pooling mode, a server connection is assigned only for the duration of each transaction, so thousands of client connections can share a few dozen real ones.

Trade-offs to know about:

Session-level state (SET, session advisory locks, LISTEN/NOTIFY, temp tables) doesn't work reliably across transactions in transaction mode.
Named prepared statements can break depending on your PgBouncer version and config. Check its docs and your client's settings.
It doesn't fix leaks or long transactions. It just changes where the scarce resource lives.

Serverless note

Each function instance often creates its own pool. Use max: 1 (or a very small number), reuse the pool across invocations by declaring it at module scope, and front the database with a pooler. Otherwise a traffic spike creates hundreds of instances × max connections and you'll hit too many clients already.

Instrumentation: make starvation visible before it's an outage

Export pool stats as metrics and alert on them:

import client from 'prom-client';

new client.Gauge({
  name: 'pg_pool_total', help: 'Open connections',
  collect() { this.set(pool.totalCount); },
});
new client.Gauge({
  name: 'pg_pool_idle', help: 'Idle connections',
  collect() { this.set(pool.idleCount); },
});
new client.Gauge({
  name: 'pg_pool_waiting', help: 'Callers waiting for a connection',
  collect() { this.set(pool.waitingCount); },
});

// Time spent waiting to acquire (your best early-warning signal)
const acquireTime = new client.Histogram({
  name: 'pg_pool_acquire_seconds', help: 'Time to acquire a connection',
  buckets: [0.001, 0.005, 0.02, 0.1, 0.5, 1, 5],
});

export async function timedConnect() {
  const end = acquireTime.startTimer();
  try { return await pool.connect(); } finally { end(); }
}

Alert on:

pg_pool_waiting > 0 sustained for more than a few seconds
p95 of pg_pool_acquire_seconds rising (it climbs before requests start failing)
Connection timeout errors from connectionTimeoutMillis
Postgres-side: count of idle in transaction sessions older than N seconds, and total connections as a % of max_connections

Reproduce it on purpose (so you trust your fix)

Don't wait for production to prove a fix works. Cause starvation in a test:

// starvation.test.ts
import { Pool } from 'pg';
import { it, expect } from 'vitest';

it('surfaces a leak as a fast error, not a hang', async () => {
  const pool = new Pool({ max: 2, connectionTimeoutMillis: 500 });

  // Leak two clients on purpose
  await pool.connect();
  await pool.connect();

  await expect(pool.query('SELECT 1')).rejects.toThrow(/timeout/i);
  expect(pool.waitingCount).toBeGreaterThanOrEqual(0);
  await pool.end().catch(() => {});
});

Run your integration tests with a tiny pool (max: 2) to flush out nested acquisition. And load test with a tool like autocannon or k6 while watching pg_stat_activity and your pool metrics to see how the system behaves at saturation, not just below it.

Diagnosis cheat sheet

What you see	Likely cause	Fix
Pool `idle=0, waiting` growing; PG connections mostly `idle`	Leaked clients	`try/finally`, prefer `pool.query`
PG connections `idle in transaction` with big `xact_age`	Missing commit/rollback, or slow non-DB work inside a transaction	`withTransaction` helper; move I/O outside; `idle_in_transaction_session_timeout`
Deadlock only under concurrency; works with low traffic	Nested acquisition	Pass the client down; test with `max: 1`
One endpoint tanks everything	`Promise.all` fan-out / N+1	Batch queries; limit concurrency
PG connections `active` for seconds; `wait_event_type = Lock`	Slow queries or lock contention	Indexes, query tuning, shorter transactions
`too many clients already`	`instances × max` exceeds `max_connections`	Smaller pools, PgBouncer
Requests hang forever with no error	`connectionTimeoutMillis` is `0`	Set it; shed load with 503s

Key takeaways

Starvation looks like a hang with idle CPUs. Check pool.waitingCount and pg_stat_activity before touching anything.
Compare the pool's view with Postgres's view. idle vs idle in transaction vs active tells you which class of bug you have.
Always release. Use try/finally, prefer pool.query, and centralize transactions in one helper.
Hold connections only while talking to the database. No HTTP calls or other slow work inside transactions.
Never acquire a second connection while holding one. Pass the client down.
Set timeouts (connectionTimeoutMillis, statement_timeout, idle_in_transaction_session_timeout) so failures are fast and loud.
Don't fix leaks by growing the pool. Size it against max_connections, and use PgBouncer when you need many clients.
Instrument the pool, so you see the queue growing long before users do.

Have you been bitten by a pool problem I didn't cover? Share your war story in the comments.

Further reading: the node-postgres pooling docs, the PostgreSQL docs on pg_stat_activity and client connection timeouts, and the PgBouncer docs.

Top comments (2)

Kashif Manzer • Oct 10

One more cause worth adding: cancelled requests racing the pool. node-postgres pool.connect() does not accept an AbortSignal, so when an HTTP request times out or the client disconnects, the queued pool.connect() can still resolve later and hand you a connection the caller no longer needs. If that code path then runs its queries anyway, it burns a connection on work nobody asked for, and it can leak entirely when the handler's error path does not release it. Checking whether the request is still alive right after acquiring, and releasing immediately if it is gone, closes that hole.

subashthiruppathy • Oct 10

Good point, thanks! pool.connect() can’t be aborted, so a queued call can resolve for a request that’s already gone. I’ve added this as Cause 7, with a helper that releases the connection right away if the request was cancelled, and a re-check of the signal after acquiring.