"Too many clients": how one export fired 3,200 database queries at the same second

The problem

I work on the backend of a product configurator engine: a system where a product is described as a tree of items (fields, options, groups), and each item carries attributes, dependencies, filters, conditions and computed variables. Other services ask this backend to export a configurator as one big JSON tree, so they can render it, price it or validate an order.

One day, exports started failing in production with this error:

Invalid `prisma.item.findFirst()` invocation:
Too many database connections opened: sorry, too many clients already
    at async ItemTreeService.loadOne (...)
    at async Promise.all (index 197)

Two details in that stack trace told me almost everything before I opened a single file:

• sorry, too many clients already is not an application error. It is PostgreSQL itself refusing new connections, because the server reached its max_connections limit.
• Promise.all (index 197) means that at least 198 promises were running at the same time when it failed. Something was firing a huge number of parallel operations.

And the worst part: our PostgreSQL server is shared with other services. When the export used up every connection, it was not only the export that failed. Every other service talking to that database was refused too. One heavy endpoint could take down systems that had nothing to do with it.

The details

How the tree was being built

The export endpoint called a "build the configurator tree" service. Simplified, the original logic looked like this:

// 1. Get the ids of every item in the configurator
const items = await db.item.findMany({
  where: { configuratorId },
  select: { id: true },
});

// 2. Build each item's full data… all at the same time
const results = await Promise.all(
  items.map((item) => loadItemWithEverything(item.id)),
);

And loadItemWithEverything(id) did its own small set of queries for one item:

async function loadItemWithEverything(itemId: number) {
  const item = await db.item.findFirst({ where: { id: itemId }, include: { type: true } });
  const attributes = await loadAttributes({ itemId });     // 2 queries
  const dependencies = await loadDependencies({ itemId }); // 2 queries
  const variables = await loadVariables({ itemId });       // 1 query
  const filters = await loadFilters({ itemId });           // 1 query
  return { ...item, attributes, dependencies, variables, filters };
}

Each piece looks reasonable on its own. That is exactly why this kind of bug survives code review: no single line is wrong. The problem only appears when you multiply.

It goes deeper: the hidden loops

Dependencies and filters each point to a condition (a small logic tree of operations and comparisons). Inside loadDependencies and loadFilters, every condition was loaded one by one:

// for EACH dependency…
const enriched = await Promise.all(
  dependencies.map(async (dep) => ({
    ...dep,
    condition: await loadCondition(dep.conditionId), // 3 queries each
  })),
);

Loading one condition meant 3 queries: the condition, its operations, and its comparisons.

Counting the queries

When you can't see a performance bug, count. For one item, with all export options enabled:

Now take a realistic configurator: 200 items, with on average 2 dependencies and 1 filter per item:

per item  = 7 + 3 × (2 + 1) = 16 queries
total     = 200 × 16        = 3,200 queries for ONE export

3,200 queries is already a lot. But the real killer is when they run: because of Promise.all, they all start at the same moment.

Why parallel made it worse, not faster

Promise.all feels like a performance tool: "run everything in parallel, it'll be faster". With a database, parallelism has a hard ceiling: connections.

Every query needs a connection. The connection pool tries to give one to each waiting query. When hundreds of queries arrive in the same millisecond:

1. the pool opens as many connections as it is allowed to,
2. the shared PostgreSQL server fills up,
3. new connection attempts get too many clients already,
4. one failed query rejects the whole Promise.all,
5. the whole export fails, and so do other services that needed a connection at that moment.

It is like sending 200 people into a small shop at the same second, each person buying a single item. The shop has a limited number of doors. The doors jam, and nobody gets served, including customers who came for something else.

The challenges

The output had to stay exactly the same

Other services consume this export. A different field order or a missing empty array could break a consumer I don't control. So the rewrite had a strict rule: same shape, same order, same values, byte for byte.

It worked fine on small data

On a configurator with 10 items, the old code ran 160 queries and finished quickly. Nobody noticed anything during development. The problem only exists at production scale, which is why it reached production.

Not every N+1 could be removed at once

While reading the code, I found a comment next to the condition loading that said roughly "N+1 pattern, on purpose, per user constraint". Someone had chosen that design deliberately. Removing it without knowing why would have been reckless. So I split the work into two versions:

• Version A: fix the main item-by-item loop (the real cause of the crash).
• Version B: also batch the condition loading, after confirming the original constraint no longer applies.

Verifying a rewrite of something this big

The tree of a big configurator is thousands of lines of JSON. Reading two versions by eye proves nothing. I needed a mechanical way to prove the rewrite was correct.

The solution

The idea: ask once, sort in memory

Instead of asking the database "give me item 1's attributes", then "give me item 2's attributes", and so on 200 times, ask once:

"Give me all attribute values of this configurator."

Then group the answers by item in memory, which takes a few milliseconds.

The good news: most of the existing loaders already supported filtering by configurator, not just by item. The bulk path was already half-built; it just wasn't used.

Step 1: load all items in one query

A new repository method returns every item of the configurator, with its type and relation counts, in the same order as before:

findItemsForTree(configuratorId: number) {
  return db.item.findMany({
    where: { configuratorId },
    include: {
      type: { select: { id: true, name: true, kind: true } },
      _count: { select: { dependencies: true, filters: true, variables: true } },
    },
    orderBy: { position: 'asc' }, // same ordering as the old per-item path
  });
}

Step 2: load each relation once, for the whole configurator

const items = await repo.findItemsForTree(configuratorId);

// 4 bulk calls instead of 4 × N calls
const [attributes, dependencies, variables, filters] = await Promise.all([
  withDetails      ? loadAttributes({ configuratorId })   : null,
  withDependencies ? loadDependencies({ configuratorId }) : null,
  withVariables    ? loadVariables({ configuratorId })    : null,
  withFilters      ? loadFilters({ configuratorId })      : null,
]);

Promise.all is still here, but now it runs 4 calls, not hundreds. Parallelism is fine when the number of parallel operations is small and fixed.

Step 3: group by item, then build the exact same objects

const groupBy = <T>(rows: T[], key: (row: T) => number) => {
  const map = new Map<number, T[]>();
  for (const row of rows) {
    const k = key(row);
    if (!map.has(k)) map.set(k, []);
    map.get(k)!.push(row);
  }
  return map;
};

const attributesByItem   = groupBy(attributes ?? [],   (a) => a.itemId);
const dependenciesByItem = groupBy(dependencies ?? [], (d) => d.itemId);
const variablesByItem    = groupBy(variables ?? [],    (v) => v.itemId);
const filtersByItem      = groupBy(filters ?? [],      (f) => f.itemId);

const tree = items.map((item) => ({
  id: item.id,
  position: item.position,
  parentPosition: item.parentPosition,
  name: item.name,
  // …every field the old response had, in the same order…
  ...(withDetails      && { attributes:   attributesByItem.get(item.id) ?? [] }),
  ...(withDependencies && { dependencies: dependenciesByItem.get(item.id) ?? [] }),
  ...(withVariables    && { variables:    variablesByItem.get(item.id) ?? [] }),
  ...(withFilters      && { filters:      filtersByItem.get(item.id) ?? [] }),
}));

Grouping 10,000 rows in a Map is pure CPU work in Node.js: microseconds to a few milliseconds. The database, the network and the connection pool are no longer involved per item.

Step 4: control the pool explicitly

Fixing the loop removes the flood. But I also wanted a guarantee that this service can never again take the shared database down, even if a future bug reintroduces a burst. So I capped this service's connection pool explicitly with a configurable DB_POOL_MAX (default 10). When a burst happens, queries queue inside this service instead of grabbing more server slots. A slow export is acceptable; taking down other services is not.

(This step had its own surprise: the usual connection_limit URL parameter was being silently ignored. That story has its own post.)

I also turned off per-query logging in production. It wasn't the cause, but with 3,200 queries per export, the logs were unreadable exactly when I needed them most.

Step 5: prove it is identical

Before touching the code, I exported two real configurators and saved the JSON as before.json. After the rewrite, I exported them again as after.json and compared them:

diff <(jq -S . export-before.json) <(jq -S . export-after.json) && echo "identical"

Both were byte-for-byte identical. That comparison is the reason I could ship a rewrite of the most-used endpoint with confidence.

The results

These timings were measured on a local database with almost no latency. In production, every query also pays a network round trip, so removing about 3,190 round trips matters even more there.

What's next: Version B

The conditions inside dependencies and filters still load one by one. The fix is the same pattern already used in the code for actions: collect all conditionIds, load them in 3 bulk queries (conditions, operations, comparisons), and look them up from a Map. 100 dependencies would go from 300 queries to 3. It is ready to implement once the original constraint is confirmed obsolete.

Lessons learned

• Count queries, not lines. The bug was not in any single line. It was in N × M × K. When an endpoint is slow, write down how many queries it runs as a formula of the data size.
• Promise.all over a database is a load test you didn't plan. Parallelism is great for a small, fixed number of calls. Over an unbounded list, it is a burst generator.
• Prefer "one query per relation" over "one query per row". Load each relation for the whole parent at once, then group in memory. The query count becomes constant.
• Look for the bulk path that already exists. Here, the loaders already supported configuratorId. The fix reused existing code instead of adding new complexity.
• Protect shared resources with explicit limits. A connection pool cap turns "we took down the database" into "this one endpoint was slower". Design for the bad day.
• Prove refactors with real data. A saved before.json and a diff gave more confidence than any amount of reading.
• Respect "on purpose" comments. When code says a strange pattern is deliberate, find out why before removing it. Split the work so the safe part ships first.

FAQ

What does "sorry, too many clients already" mean in PostgreSQL?

It means the PostgreSQL server has reached its max_connections limit and refuses new connections. It usually comes from too many application instances, pools that are too large, or code that opens a burst of parallel queries.

What is the N+1 query problem?

It happens when you load a list (1 query), then run one extra query for each element of the list (N queries). With nested relations it multiplies: N items × M relations × K sub-relations. The fix is to load each relation once for all parents and group the results in memory.

Is Promise.all bad for database queries?

Not by itself. It is fine for a small, fixed number of independent queries. It becomes dangerous when the number of promises grows with your data, because every promise competes for a database connection at the same time.

How can I check that a performance refactor did not change the output?

Save real responses before the change, run the same requests after the change, and compare them with a tool like diff (with JSON keys sorted, for example using jq -S). If they are identical, the refactor is safe.