The fan-out that killed the search

The rest of this post is the deep dive: why a read costs a connection at all, the arithmetic that turns a fan-out into a deadlock rather than a queue, the UNION that fixed it, and the sibling outage two days later that was the same resource failing from the opposite direction. File paths refer to the real code.

Why a read costs a whole connection

Dhaga Cloud isolates tenants with Postgres row-level security, and the scoping is transaction-local — a design we've written about before. The practical consequence is that a request can't just borrow any connection and run a query. It needs a connection that has been told who it is.

That's getDb() in apps/web/src/lib/db/request-scope.ts. It resolves an RLS-scoped tenant connection, opens a transaction on it, and hands it back. The connection is released by Next's after() when the request finishes — not when your query finishes.

It is also memoised with React's cache(), and this is the detail that made the bug possible to write without noticing:

cache() dedupes within a React Server Component render. In a server action or route handler, it does not.

So the identical line of code — a helper that calls getDb() and runs a query — is a single shared connection when a page renders it, and a fresh checkout every time an action calls it. The same helper, two completely different resource profiles, no visible difference at the call site.

The pool sizing is deliberately small, in packages/ee/src/db/pool.ts:

Three isn't an oversight, it's a budget. The hosted Postgres session pooler gives us roughly 48 slots. Each running instance draws 3 tenant connections plus 2 for the core pool, so five per instance, so about nine warm instances fit before the database is the ceiling. Raising the per-instance number lowers how many instances can exist. There is no free direction.

The arithmetic that doesn't work

hybridSearch needed six keyword sources — notes, facts, follow-ups, events, signals, and an identity match on the contact itself. Written the obvious way, each source awaited its own getDb() and they all ran under one Promise.all.

Six requests for a connection. Three slots.

Here's the part worth sitting with, because it's what makes this a deadlock rather than merely slow: the three that win don't release. A request-scoped connection is held until the request ends. There is no churn inside a single request to free a slot for the three still waiting. They aren't queued behind work that will finish — they're queued behind work that will finish and then keep holding the slot anyway.

So the three losers wait out the full 10-second connect timeout, throw timeout exceeded when trying to connect, and the request 500s. Not degraded. Not slower. Dead.

A fan-out wider than the pool is not a queue. It is a deadlock with a timeout bolted on.

Why it read like a load problem and wasn't

Everything about the symptom said capacity. Intermittent 500s in production, clean locally, fine on a dev database. The natural next moves are all wrong: raise the pool, add a retry, look at traffic graphs.

But the trigger was one user running one search. The failure was deterministic in shape and only looked stochastic because whether a given deploy had a warm connection lying around varied. Load made it more likely, but load was never the cause. The number to compare wasn't requests per second — it was six versus three.

The fix: one statement, on one connection

Both search reads were collapsed into a single UNION ALL. The combined query lives in apps/web/src/lib/repo/search/keyword/combined/query.ts; the graph target search got the same treatment in apps/web/src/lib/repo/graph-data/targets.ts.

-- Illustrative shape. Every branch projects an identical column list;
-- the branches that have no identity columns NULL-pad to match.
SELECT contact_id, source, rank, snippet, ...
FROM (
  <identity branch: fuzzy-name score>
  UNION ALL SELECT n.contact_id, 'notes',     ts_rank(n.search_tsv, q), ... FROM notes n ...
  UNION ALL SELECT f.contact_id, 'facts',     ...
  UNION ALL SELECT fu.contact_id, 'followups', ...
  UNION ALL SELECT ec.contact_id, 'events',   ...
  UNION ALL SELECT s.contact_id, 'signals',   ...
) matched

Every branch has to project the same column list, so the branches without identity columns NULL-pad. It is less elegant than six tidy functions. It is also the entire fix.

Measured locally against our hosted Postgres in ap-southeast-2 with a 1,000-contact seed:

The typeahead number is the honest one to look at: the remaining second is shared auth and connection overhead, not search. We didn't make search fast; we stopped search from being a resource event.

The property that matters more than the milliseconds is that the round-trip count is now fixed at one, however many ways there are to reach a contact. It was six branches when we fixed it. It is eleven today — positions, edges, notes on edge-linked entities, calendar events, tasks — at the same connection cost. Adding a source used to add a connection. Now it adds a UNION ALL.

Promise.all wasn't the villain

Worth being precise, because "we banned Promise.all" would be a satisfying ending and a false one. hybridSearch still uses it:

const db = await getDb();                      // resolved ONCE, up front
const [semanticHits, keyword] = await Promise.all([
  semanticSearch(query).catch(() => []),
  combinedKeywordHits(db, words, weights),     // handle threaded in
]);

The rule isn't "don't run things concurrently". It's resolve getDb() once and thread the handle. Concurrency is fine; concurrent checkout is not.

Where a read genuinely needs several statements, withUserDb(userId, work) opens one scoped transaction and pushes it into an AsyncLocalStorage, so every getDb() inside the callback resolves to that one connection instead of asking for a new one.

And there's a corollary that surprised us, documented in docs/SCALING.md: inside a Server Component render, where cache() does dedupe and the fan-out is therefore safe, a twelve-way Promise.all still buys zero parallelism — because a single node-postgres client runs one query at a time. So on the render path, Promise.all is not dangerous, it's just decorative. Either way the lever that works is the same one: fewer round-trips, not more concurrency.

The sibling outage: same pool, opposite direction

Two days later, natural-language search — which retrieves through this very same path — failed for essentially every production query. About 32 seconds of waiting, then "the AI had trouble", with zero tokens metered.

Not a fan-out this time. The opposite. The answer path checked out one connection during its budget check and then held it across the entire ~30-second answer stream, because the release is wired to after(). With three slots, that saturates under very light concurrency. The next getDb() then burns three retries × 10 seconds ≈ 30.3 seconds in connect-retry before throwing — which is exactly where the 32-second wait came from. The real error was swallowed by an empty catch, so the whole thing surfaced as a vague "busy" notice.

The fix was to give each database phase its own short-lived scope, with the model calls outside all of them:

await withUserDb(userId, () => assertAiBudget(userId));
const hits = await withUserDb(userId, () => retrieveHits(plan, query));
// ── the Haiku plan and the Sonnet answer stream run HERE, holding nothing ──
await withUserDb(userId, () => recordAiAction("search", model, usage));

That works because getDb() checks the AsyncLocalStorage before the cache() memo — deliberately, so one request can open and close several scopes in sequence rather than being pinned to its first one.

Put the two incidents side by side and they're the same resource seen from two angles. One took too many connections at once; the other held one for too long. A three-slot pool is intolerant of both, and a code review that only asks "is this query fast?" catches neither.

The takeaways

1. When every read checks out a connection, Promise.all multiplies concurrency, not throughput. Count the concurrent checkouts against your pool max before you count milliseconds.
2. Know where your memoisation actually applies. cache() dedupes in an RSC render and not in a server action. The same helper is safe on one path and a fan-out on the other, with nothing at the call site to tell you which.
3. Request-scoped checkout means no churn inside a request. A fan-out wider than the pool doesn't queue — it deadlocks until the connect timeout fires.
4. Prefer one round-trip to a fan-out. UNION ALL branches keep the connection cost constant as sources multiply; ours went from six to eleven for free.
5. Never hold a connection across a model call. Open a short scope, close it, run inference, open another.
6. A swallowed error turns a 30-second deadlock into "something went wrong". The connect-timeout message was the whole diagnosis, and an empty catch ate it.