How a Missing client.release() in an Error Path Exhausted Our Postgres Connection Pool

14:47 UTC. Datadog: api error rate 41% over 5m, route: POST /api/reports/export. Five minutes later the same monitor is at 68%, and it's not just export anymore, every route that touches Postgres is timing out. The API is up. The database is up. Nothing between them is working.

the setup

Two weeks earlier, the reports dashboard shipped three quick-filter chips: Completed, Failed, Pending. Product wanted one-click filtering without opening the full date-range picker. The chips called the same export endpoint the date-range flow used, just with status set and both date fields left blank. Nobody had re-run the endpoint's query builder against that specific combination, because the chips PR touched only the frontend and the endpoint already had test coverage for "date range only" and "date range plus status."

function buildExportQuery(orgId, filters) {
  let query = 'SELECT * FROM events WHERE org_id = $1';
  const params = [orgId];

  if (filters.startDate && filters.endDate) {
    query += ' AND created_at BETWEEN $2 AND $3';
    params.push(filters.startDate, filters.endDate);
  }

  if (filters.status) {
    query += ' AND status = $4';
    params.push(filters.status);
  }

  return { query, params };
}

The $4 in the status clause assumes the date-range branch always ran first and claimed $2 and $3. Filter by status alone and that assumption breaks: the query text asks Postgres to bind four parameters, the params array holds two.

The export endpoint didn't use the pool's convenience method for this query. It needed a transaction, so it checked out a dedicated client:

app.post('/api/reports/export', async (req, res) => {
  const client = await pool.connect();
  try {
    const { query, params } = buildExportQuery(req.body.orgId, req.body.filters);
    await client.query('BEGIN');
    const result = await client.query(query, params);
    await client.query('COMMIT');
    client.release();
    return res.json({ rows: result.rows });
  } catch (err) {
    await client.query('ROLLBACK');
    return res.status(400).json({ error: err.message });
  }
});

client.release() exists exactly once, on the line between COMMIT and the success response. There's no finally block. Every request that hit the catch branch returned its 400 and kept its client, forever, with no error in any log to say so, because from the endpoint's point of view it had handled the error correctly.

the scramble

First theory, on-call's: a traffic spike. Reports had been trending up all week, and a burst of concurrent exports could plausibly saturate max_connections. Request volume across the fleet for the prior hour was flat against the seven-day baseline, no spike, no unusual client IP concentration. Rejected within four minutes.

Second theory: a slow query holding row locks and backing everything up behind it. Postgres's own view into that is pg_locks, and it came back clean, no blocking locks, nothing waiting on anything at the database level. Whatever was wrong wasn't a lock problem, which meant it was something between the app and the database rather than inside a query.

the hunt

The app's pool object exposes three counters that node-postgres updates live: totalCount, idleCount, and waitingCount. Someone had wired those into a debug endpoint months earlier for exactly this kind of situation and nobody had needed it since:

{
  "totalCount": 20,
  "idleCount": 0,
  "waitingCount": 37
}

Pool max was 20. All 20 were checked out, none idle, and 37 requests were queued waiting for one to come free. That's the app's side of the story: every client the pool had ever handed out was still marked as in use. The database's side of the same moment told a different story:

SELECT state, count(*) FROM pg_stat_activity
WHERE application_name = 'reports-api'
GROUP BY state;

    state    | count
-------------+-------
 idle        |    20
(1 row)

Twenty connections, all idle, none running a query, none in a transaction. The app thought all 20 were busy. Postgres said all 20 were doing nothing. Both were right: the clients were checked out from the pool's bookkeeping, but nothing was executing against them and nothing was going to, because whatever code held them had already returned a response and moved on. That combination, pool says busy, database says idle, only happens one way in node-postgres: a client got pool.connect()'d and never got .release()'d.

pg_stat_activity also logs the last query each backend ran. Every one of the 20 idle connections showed the same query text truncated at the same point:

SELECT * FROM events WHERE org_id = $1 AND status = $4

Twenty connections, one query shape, the status-only filter with the orphaned $4.

the find

One customer's ops team had the reports dashboard open in a pinned browser tab with a "live view" toggle that auto-refreshed the current filter every 30 seconds, a feature built for exactly this kind of always-on monitoring use case. They'd been using the Failed quick-filter chip since it shipped two weeks earlier. Every 30-second refresh sent status: 'Failed' with no date range, hit buildExportQuery's mismatched placeholder, and Postgres rejected the bind with a parameter-count error before the query ever ran. The catch block returned its 400. The client that had been checked out for that request never went back to the pool.

One leaked connection every 30 seconds, from one tab, is a pool of 20 gone in ten minutes. The live-view toggle had been running against that filter combination since the chips shipped; it had just never happened to hit exactly this filter shape with enough repetition, until that afternoon, to burn through the whole pool before anyone left for the day and closed the tab.

the fix

The immediate fix, restore service: restart the API pods to force the pool to reconnect from zero, which is a blunt instrument but the fastest way to give the 37 queued requests a working connection again. The real fix, before anyone touched the release logic:

app.post('/api/reports/export', async (req, res) => {
  const client = await pool.connect();
  try {
    const { query, params } = buildExportQuery(req.body.orgId, req.body.filters);
    await client.query('BEGIN');
    const result = await client.query(query, params);
    await client.query('COMMIT');
    return res.json({ rows: result.rows });
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    return res.status(400).json({ error: err.message });
  } finally {
    client.release();
  }
});

And the actual bug that triggered it, the mismatched placeholder in the query builder:

function buildExportQuery(orgId, filters) {
  let query = 'SELECT * FROM events WHERE org_id = $1';
  const params = [orgId];

  if (filters.startDate) {
    params.push(filters.startDate);
    query += ` AND created_at >= $${params.length}`;
  }
  if (filters.endDate) {
    params.push(filters.endDate);
    query += ` AND created_at <= $${params.length}`;
  }
  if (filters.status) {
    params.push(filters.status);
    query += ` AND status = $${params.length}`;
  }

  return { query, params };
}

Placeholder numbers are now derived from the params array's own length at the moment each clause is added, instead of hardcoded against an assumption about which branches ran first. There's no filter combination left where the two can drift apart.

Alongside both fixes: a Datadog metric wired straight to the pool's own counters, checked every 15 seconds, alerting on waitingCount > 0 sustained for 2 minutes rather than waiting for it to show up as request-level 503s downstream. A queued acquire is the leading indicator; the timeout errors are just the lagging one.

the aftermath

The other three call sites had been fine so far for the same reason this one had been fine for two weeks: nobody had hit their specific failure branch with enough repetition to matter. All three now route through a small wrapper that acquires a client, runs a callback, and guarantees release in a single place instead of leaving it to each call site to remember:

async function withClient(pool, fn) {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release();
  }
}

The pool never logged an error. It did exactly what it was told: hand out a client, wait for it to come back. Nobody ever told it the client wasn't coming.