../writing

Cut a Slow N+1 Query Down

Naseebullah Ahmadi  Senior Software Engineer, London

An endpoint that queries once per parent row instead of once per request looks fine with ten rows and falls over with ten thousand. Batch the per-row lookups into one query and the round trips disappear.

#postgres
#sql
#performance
#n-plus-one
5 min read
#engineering
In one line

A loop that calls a query once per row turns 1 request into 1 + N round trips. Collect the IDs first and fetch them in one batched WHERE id IN (...) (or JOIN). Then look each row up from an in-memory map instead of hitting the database again.

You're here when

Signs this is your situation
  • An endpoint gets slower in proportion to how many rows it returns, not how much work each row does
  • Query logs or an APM trace show the same shaped query repeated dozens or hundreds of times per request
  • An ORM relation is being read inside a for/.map() over a parent list (order.customer, post.author)
  • A GraphQL resolver on a list field re-queries per item returned by the parent field

The play

  1. 1Confirm it's actually N+1: turn on query logging (or open the APM trace) and count how many near-identical queries fire for one request.
  2. 2Find the loop: a for/.map() over parent rows that calls .find(), .load(), or a relation getter once per iteration.
  3. 3Pull the loop's per-row lookup out and replace it with one query: collect every parent ID first, then WHERE id IN (...) or a single JOIN.
  4. 4Index the batched result by its foreign key (a Map), and have the loop read from that map instead of querying again.
  5. 5If the N+1 is inside framework-managed resolution (GraphQL field resolvers), swap the manual fix for a batch loader that does this automatically per request.
  6. 6Re-check the query log: the count should now be flat no matter how many rows come back.
@itsnas before.ts
codebefore.ts
// one query for the orders, then one MORE query per order
const orders = await db`select * from orders where account_id = ${accountId}`
 
for (const order of orders) {
  order.customer = await db`
    select * from customers where id = ${order.customerId}
  `.then(rows => rows[0])
}
main
Nas (@itsnas)

Which path

A straightforward one-to-many

  • Collect the foreign keys, one WHERE id = ANY(...) (or a JOIN), build a Map keyed by the foreign key.
  • Works for ORMs too: most ship an eager-load option (include, with, .load() on a query builder) that does exactly this under the hood.

Nested GraphQL resolvers

  • Hand-batching each resolver gets unwieldy fast once fields nest.
  • Reach for a per-request DataLoader: it coalesces every .load(id) call made in the same tick into one batched fetch, and caches within the request.

Gotchas

  1. 1

    One giant IN clause with no chunking

    Passing ten thousand IDs into a single IN/ANY can hit a parameter limit or just get slow to plan. Chunk into batches of a few hundred to a couple thousand if the ID list is unbounded.

  2. 2

    Eager-loading everything by default

    Swinging the other way and always including every relation over-fetches on the endpoints that never needed it. Batch the relations a given endpoint actually reads, not every relation the model has.

Confirm you're clear

  1. 1The query log shows a fixed, small number of queries for the endpoint regardless of how many rows it returns.
  2. 2Latency scales roughly linearly with result size, not with an extra round trip per row.
  3. 3Doubling the row count in a test fixture doesn't double the query count.