liveBook · Manning

N + 1 query problem

As explained in Fast by Default

An N+1 query pattern is an inefficient data-access pattern in which one query retrieves a collection and additional queries retrieve related data for each item.

The N + 1 query problem occurs when code loads a collection with one query, then loads related data separately for every item. A list of 100 posts followed by one author lookup per post produces 101 queries; the same relationship can often be loaded with a join, eager loading, or a batch request. The extra work may seem harmless with small test data but becomes a scalability failure as collection sizes grow. section 4.5.1 section 4.4.2

Sequential lookups versus batching

A post feed first fetches posts, then awaits fetch_user(post.author_id) inside a loop. That creates one query per author lookup. Instead, collect author IDs, fetch all authors in one bulk query, and attach each returned author to its post. For 50 posts, this changes 51 queries into two: one for posts and one for authors. section 4.4.2

Listing 4.3 The N+1 problem in Python
posts = await fetch_posts()
for post in posts:
    post.author = await fetch_user(post.author_id)
 

This loop makes the pattern visible: the related-data query runs once for every post returned by the initial query. section 4.4.2

The pattern also appears outside a conventional ORM loop. A GraphQL query for users and their posts can invoke a resolver once for users and then once per user for posts. A checkout endpoint can repeat inventory, price, and discount lookups for every cart item. The shared warning sign is repeated, near-identical queries whose only changing value is an item ID. section 5.3.3 section 7.2.1 section 7.2.2

Prevent N + 1 queries by changing how related data is requested and verified:

  • Load related records in fewer queries with joins, ORM eager loading, batch loading, or query optimization. Rails includes and Django select_related or prefetch_related are named eager-loading options. section 4.5.1
  • Use a batching layer to collect IDs during an execution cycle, issue one bulk request, and map results back to callers. DataLoader-style patterns address this explicitly in GraphQL resolvers. section 4.4.2 section 5.3.3
  • Coalesce identical in-flight requests when several callers ask for the same resource; this shares one network request, while batching combines requests for different resources. section 4.4.2
  • Enable query logging and test realistic collection sizes. Repeated statements with changing IDs reveal the pattern; more than 20 queries for a simple endpoint merits investigation, while more than 50 is a strong warning. section 7.2.2
  • Trace request phases and compare database time with the latency budget. Many individually fast sequential queries can still dominate total response time. section 7.2.1

Do not confuse too many queries with one slow query. Query logging exposes query count and repetition; EXPLAIN or EXPLAIN ANALYZE investigates how an individual query executes, including sequential scans and index scans. Both checks matter because an optimized query count can still contain a slow query, while many fast queries can add up to an unacceptable response. section 7.2.2 section 7.2.1

Make query-count checks part of normal delivery rather than a production-only rescue. Profile endpoints with realistic parameters, measure database versus processing time, inspect connection-pool usage, and compare total latency against a defined budget. Short pre-review profiling checks help catch repeated work while the code is still easy to change. section 7.2.4 section 7.6

How Fast by Default covers it

How deep it goes
Defines the pattern, contrasts sequential and batched implementations, and gives logging, tracing, and query-analysis checks.
Examples
Uses post-author lookups, product-author GraphQL requests, and checkout cart-item inventory, price, and discount calls.
What it assumes
Assumes familiarity with database queries and related records while illustrating the pattern with direct Python-style fetch calls.
What you can do after
Diagnose/debug with query logs and traces, then replace per-item related-data fetches with eager or batch loading.
How it connects
Connects eager loading, joins, batch loading, DataLoader, request coalescing, query logging, and request tracing.

Also explained in

Compare how 7 books explain N + 1 query problem →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Fast by Default. The section links point to the parts of the book it draws on.