Cutting an API from 10 seconds to 1.5

The problem

An organisation member tree-view endpoint took 10 seconds to respond. It returned the reporting hierarchy — every member, their position in the tree, and the data needed to render each node — and it was the first thing users saw on the page it backed.

Ten seconds is past every threshold that matters. It is long enough that users assume failure and retry, which multiplies load on an endpoint that is already struggling. It is long enough to sit near default gateway and client timeouts, so the endpoint fails intermittently under conditions nobody can reproduce on demand.

The actual cause

The endpoint walked the hierarchy recursively: fetch a node, query for its children, recurse into each child, repeat. This reads naturally — it mirrors the shape of the data, and the code is short and obviously correct.

It is also the N+1 query problem wearing a tree costume. Every single node triggers its own round trip to the database. For an organisation with a thousand members, that is roughly a thousand sequential queries, and they are sequential by construction: recursion cannot issue a child’s query until the parent’s has returned.

The decisive detail is that the per-query cost was never the problem. Each individual query was fast — likely a few milliseconds, indexed, unremarkable in isolation. Profiling the SQL would have found nothing worth fixing. The cost was that the endpoint paid network round-trip latency a thousand times in a row, and round-trip latency does not care how fast the query is. At two milliseconds of overhead per call, a thousand nodes is two seconds of pure waiting before counting any real work.

This is why the problem scaled with organisation size rather than with data volume. Larger customers — the ones who matter most — got the worst experience, and the endpoint degraded as the product succeeded.

The fix: traverse by level, not by node

Replacing recursion with breadth-first traversal changes what a query corresponds to. Instead of asking “who are this node’s children?” once per node, BFS asks “who are the children of all nodes at this level?” once per level — a single query with a set of parent IDs rather than one query per parent.

That collapses the query count from number of nodes to depth of the tree. Organisational hierarchies are wide and shallow: a company of a thousand people is rarely more than six or seven levels deep. A thousand sequential round trips become roughly seven, and crucially that number barely moves as the organisation grows — doubling headcount widens levels without meaningfully deepening them. The endpoint stopped scaling with the thing that was growing.

Batch processing is what makes each level one query instead of many, and it also lets the database do what it is good at: one query returning a thousand rows is dramatically cheaper than a thousand queries returning one row each, because the planner can use a set-based access path instead of repeating the same lookup with different parameters.

Concurrent retrieval closes the remaining gap. Once a level’s members are known, the additional data each node needs is independent per node, so those fetches have no reason to wait on each other. Running them concurrently means total time approaches the slowest fetch rather than the sum of all of them.

Why not a recursive CTE?

The obvious alternative is pushing the whole traversal into the database with a recursive common table expression, returning the entire subtree in one query. It is a legitimate choice and in some situations the better one.

The trade is control. A recursive CTE hands traversal to the query planner, which makes depth limiting, per-level filtering, partial results and authorisation checks awkward to express — and when it does choose a poor plan, there is little room to intervene. Level-wise BFS in application code keeps each step inspectable and bounded, and leaves room for the concurrent per-node retrieval that a single monolithic query cannot express. It also keeps the pattern portable rather than tied to one database’s recursive CTE behaviour.

Results

Response latency fell from 10 seconds to 1.5 — an 85% reduction — and the endpoint’s cost profile changed shape. It no longer scales with the number of members in an organisation; it scales with how deeply nested that organisation is, which is both far smaller and far more stable.

What I would do differently

I would add a query counter per request to the service’s instrumentation. The root cause here was invisible to ordinary metrics: no slow query, no high error rate, no CPU saturation — just a large number of individually unremarkable calls. Counting queries per request surfaces an N+1 pattern immediately and catches the next one before a user reports it. I would also set an explicit depth bound on the traversal, because a cycle introduced by bad data turns a graceful endpoint into an unbounded one.