Jesse's Notes

N+1 Queries and Batching

5 min read

An N+1 query is the pattern where fetching a list costs one query, and then processing each of the N items fires one more query — N+1 round trips to answer what could have been answered in one or two. It’s the most common, most invisible performance bug in any system that loads related data lazily. The fix is batching: collect the keys, ask once, distribute the results.

What it looks like

posts = Post.all            # 1 query: SELECT * FROM posts
posts.each do |post|
  puts post.author.name     # N queries: SELECT * FROM authors WHERE id = ?
end

One query becomes 1 + N. With 200 posts that’s 201 round trips. The code reads innocently — post.author looks like a field access, but each one is a hidden database call.

It’s a round-trip problem, not a SQL problem

The reason N+1 hurts isn’t the work the database does — each lookup is trivial. It’s the latency of N sequential round trips. A query that takes 0.2 ms to execute still costs ~1 ms in network + connection overhead; do that 200 times serially and you’ve spent 200 ms doing almost nothing. This is why N+1 generalizes far past ORMs:

  • REST aggregation — a page that calls one endpoint per row.
  • Microservice fan-out — a service that calls a downstream service once per item in a loop.
  • GraphQL resolvers — a field resolver that hits the database per parent object.

Same shape everywhere: a loop wrapped around a remote call. The cost scales with (items × round-trip latency), and when a view spans several domains it becomes (domains × rows) — the number that batching is designed to crush.

Why it happens

Lazy loading is convenient and that convenience is the trap. ORMs and data layers let you traverse relationships as if everything were already in memory, so the query is generated implicitly, at the moment of access, inside whatever loop you happen to be in. The author of the loop can’t see the query; the person who wrote the lazy association can’t see the loop. (The same “two blind spots” invisibility that makes implicit callbacks dangerous.)

Detection

N+1 hides from code review and surfaces only at scale, so you measure rather than reason:

  • Count queries per request in logs or an APM trace — a request that issues hundreds of near-identical SELECT ... WHERE id = ? queries is the tell.
  • Dedicated detectors — e.g. the Bullet gem (Rails), ORM query-count assertions in tests, slow-trace waterfalls in APM that show a staircase of identical calls.
  • Query log tags that attribute a query back to the source line generating it.

Fixes

1. Eager loading (preload the association up front). Tell the data layer to fetch the related records in one batched query instead of N lazy ones — Rails includes/preload, SQL JOIN, EF Include, a single WHERE author_id IN (…). This collapses N+1 into 1+1 (or 1).

2. Batch by key, return a map. When you control the access pattern, gather all the ids first and issue one query keyed by id:

author_ids = posts.map(&:author_id).uniq
authors = Author.where(id: author_ids).index_by(&:id)   # 1 query → hash
posts.each { |p| puts authors[p.author_id].name }

N queries become one; the loop does in-memory lookups.

3. The DataLoader / batch-loader pattern. Sometimes you can’t collect the keys up front — GraphQL resolvers and serializers each see one object at a time with no view of the whole set. A batch loader solves this by deferring each requested key and coalescing the deferred requests into a single query before resolving them. It turns “many callers each asking for one” back into “one query for many,” without the callers having to cooperate. (Watch its per-request cache: it must be cleared between requests or it leaks stale data.)

4. Escalations when batching isn’t enough. A read model / denormalized projection that pre-joins the data; a cache for hot lookups; or accepting a single wider indexed query. These trade write-time or storage cost for read-time round trips.

Don’t over-correct into the opposite bug

Eager-loading everything is its own waste: you pay to fetch associations you never read, balloon the result set, and can turn a clean query into a giant cartesian JOIN. Preload what the view actually uses — no more. The goal is the right number of round trips for the data you need, not zero round trips at any cost.

My read

The reframing I keep is N+1 is a latency-of-round-trips problem, so the fix is always “ask once for many,” whatever the transport. Coming from ORMs it’s easy to file this under “SQL tuning,” but the identical bug bit me in a service that looped over order ids calling a pricing API one at a time — no database in sight. Once you see it as a loop around a remote call, the cure is the same everywhere: hoist the keys out of the loop and batch the call. It pairs directly with read models — designing the read path to take many keys and return a keyed map is how you make N+1 structurally impossible rather than something you keep re-discovering.

Further reading

Graph View

Backlinks