The N+1 query problem

The N+1 query problem happens when code fetches a list with one query and then runs one more query for each item to get its related data: 1 query for 50 restaurants, then 50 queries for their menus. Each query is fast, so nothing looks slow on its own, but the round trips add up in a straight line with N. ORMs make it easy to write without noticing.

Picture it first: one trip to the storeroom per item

A kirana shop takes a phone order for 50 items. The assistant reads the first line, walks to the storeroom at the back, brings that item, comes back, reads the next line and walks again. Each trip takes 30 seconds, so the order takes 25 minutes, though picking each item takes a few seconds. A smarter assistant takes the whole list, makes one trip with a basket and is back in two minutes.

Map it back:

• Reading one line at a time is your loop over the parent list.
• Each walk to the storeroom is a database round trip: network both ways, parse, plan, execute, send results.
• The basket trip with the full list is one batched query: WHERE restaurant_id = ANY(...).
• The assistant who never mentions it will take 50 trips is the ORM: restaurant.dishes looks like reading a field, not walking to the storeroom.

What it looks like

A food-delivery listing page shows 50 restaurants in a city. Each card has the restaurant’s name, its zone (a to-one relation) and its dishes (a to-many relation). In Django:

restaurants = Restaurant.objects.filter(city_id=7).order_by("-rating")[:50]

for r in restaurants:                   # 1 query for the list
    print(r.name, r.zone.name)          # +1 query per restaurant (foreign key)
    for d in r.dishes.all():            # +1 query per restaurant (reverse foreign key)
        print("   ", d.name)

The SQL log:

SELECT ... FROM restaurants WHERE city_id = 7 ORDER BY rating DESC LIMIT 50
SELECT ... FROM zones  WHERE id = 412
SELECT ... FROM dishes WHERE restaurant_id = 101
SELECT ... FROM zones  WHERE id = 412
SELECT ... FROM dishes WHERE restaurant_id = 117
... 96 more, identical apart from the id ...

That is 1 + 50 + 50 = 101 queries for one page. The signature is a run of statements that are identical except for one id. It nests, too: if each dish card then lazy-loads its tags, 50 restaurants × 30 dishes add another 1,500 queries, the “1 + N + N × M” form.

Rails (restaurants.each { |r| r.dishes.each { ... } }) and Hibernate (looping over a lazy @OneToMany collection) behave the same way; the Rails guide opens its eager-loading section with exactly this pattern: 10 books plus one query per author, 11 queries.

Why ORMs make it so easy

Lazy loading is the sensible default. If every association loaded eagerly, fetching one restaurant would drag in its dishes, reviews, orders and their customers, which is extraneous fetching. So ORMs return a placeholder and query on first access. That is the right default for one object and the wrong one inside a loop.

Property access looks free. r.zone.name has no await, no db. and no SQL in sight. Nothing on the screen says “network call”.

The loop is far from the query. A controller builds the list; a template ({{ r.zone.name }}) or a JSON serializer touches the association later. Adding one field to an API response can add N queries without anyone editing a query.

Marking a relation EAGER is not a fix. JPA makes @ManyToOne and @OneToOne eager by default, yet Hibernate’s user guide warns that if a query doesn’t JOIN FETCH an eager association, Hibernate issues a secondary select for each entity: N+1 again. Its advice is to make associations lazy and fetch what each use case needs explicitly.

Development data is small. With five restaurants in your local database, N+1 costs 3 ms. It appears in production, where N is 50 per page and pages are requested thousands of times a second.

The arithmetic

Assume a simple primary-key or foreign-key query costs 0.5 ms per round trip when the application and database are in the same availability zone: network both ways plus parse, plan and execute. (Measure your own; the shape of the result doesn’t depend on the exact figure.) Lazy loads run one after another, because each waits for its result before the loop moves on.

The middle row comes from physics. AWS places a Region’s zones within 100 km of each other, and light in fibre covers about 200 km per millisecond, so a cross-zone round trip can add up to 2 × 100 ÷ 200 = 1 ms before any switching. Mumbai to Singapore is about 3,900 km, at least 40 ms per round trip, as worked out in CAP theorem and PACELC. Batching turns a cost that scales with N into one that scales with the number of associations.

Latency is only the visible part:

• Database load. At 1,000 page views a second, 101 queries per page is 101,000 queries/s, against 3,000 for three batched queries. Each one pays parse, plan, execute and protocol overhead, so the database spends its CPU on overhead rather than data: the busy database anti-pattern.
• Connection hold time. A request holds a pooled connection while its queries run. By Little’s law, connections in use = request rate × hold time: 1,000/s × 25.5 ms ≈ 26 connections, against 1,000/s × 1.5 ms ≈ 2. A pool of 20 that looked generous becomes the bottleneck and requests queue for a connection (connection pooling).
• Tail latency. Fifty-one sequential queries are 51 chances to hit a slow one. If each query exceeds its p99 one time in a hundred, the chance that at least one of 51 does is 1 − 0.99⁵¹ ≈ 40%, so the page’s p60 inherits the query’s p99.

Detecting it

N+1 has a clear fingerprint: the same statement, differing only in one id, run many times within one request. Look for it in several places.

• Query logs grouped by normalised statement. Strip the literals and count per request; anything with a count near the page size is a suspect.
• pg_stat_statements. It groups statements by normalised text, so the child query shows up as one entry with a tiny mean_exec_time (say 0.05 ms) and an enormous calls count: roughly N times the calls of the parent query. Sort by calls, not only by total time. (Since PostgreSQL 18, IN lists of constants that differ only in length also fold into one entry.)
• Traces. An APM or OpenTelemetry trace of the request shows a staircase of identical database spans under one parent span, as in the figure above. Many tracing tools flag repeated spans automatically.
• Framework guards.
  • Rails: strict_loading on a relation, a record or app-wide (strict_loading_by_default) raises ActiveRecord::StrictLoadingViolationError on any lazy load, and the :n_plus_one_only mode raises only when the load would cause N+1. The Bullet gem watches queries in development and reports N+1 queries, unused eager loading and missing counter caches; Bullet.raise = true fails your tests.
  • Django: assertNumQueries pins a view’s query count in tests; django-debug-toolbar’s SQL panel lists every query a page ran. From Django 6.1, fetch_mode(models.FETCH_RAISE) raises FieldFetchBlocked instead of lazily fetching.
  • Hibernate: hibernate.generate_statistics exposes counters such as getPrepareStatementCount() per session factory.
• A query-count test that scales. Render the page with 1 item and with 20, and assert the query count is the same. A fixed number catches regressions; a count that grows with N catches N+1 itself.

Fix one: a JOIN in one query

For to-one relations (restaurant → zone), a JOIN is the obvious fix: it adds columns, not rows. Rails’ eager_load, Django’s select_related("zone") and JPA’s join fetch r.zone all generate one SQL statement with the zone’s columns alongside each restaurant.

For to-many relations a JOIN returns one row per child, so a restaurant with 30 dishes appears in 30 rows with its columns repeated, and the ORM de-duplicates in memory. Workable for one small collection. Two collections in one JOIN multiply: each restaurant contributes 30 dishes × 40 reviews = 1,200 rows, so 50 restaurants return 60,000 rows to show 50 cards. That is the cartesian explosion; Hibernate’s guide warns that fetching several collections in parallel produces a Cartesian product at the database level. A LIMIT 50 on a joined query also counts joined rows rather than restaurants, so the ORM must work around it.

The rule of thumb: JOIN to-one relations freely and at most one collection; load other collections with separate queries.

Fix two: a second query with IN or ANY

Load the parents, collect their ids, and fetch every child in one more round trip:

-- 1: the list
SELECT id, name, zone_id FROM restaurants
WHERE city_id = 7 ORDER BY rating DESC LIMIT 50;

-- 2: every dish for those 50 restaurants in one round trip
SELECT restaurant_id, id, name, price_paise FROM dishes
WHERE restaurant_id = ANY($1);          -- $1 = array of the 50 ids

Then group the dishes by restaurant_id in memory. If each card needs only the top three dishes, push that into the same query with a window function (row_number() OVER (PARTITION BY restaurant_id ORDER BY orders_30d DESC), keeping rows numbered 1 to 3) instead of looping.

Three details matter:

• The batched query needs an index too. dishes(restaurant_id) turns it into 50 index lookups in one statement; without it, one query scans the whole table. Check it with EXPLAIN like any other query.
• Prefer one array parameter to a list of placeholders. = ANY($1) has the same text for 5 ids or 500, so it is one prepared statement and one pg_stat_statements entry. An IN ($1, …, $500) list changes shape with N, and PostgreSQL’s wire protocol counts bind parameters in a 16-bit field, so one statement tops out at about 65,000 placeholders.
• Chunk very large id lists. Ten thousand ids in one query means a large message, a large plan input and one big result held in memory. Batches of a few hundred to a few thousand ids keep each query predictable.

Fix three: eager loading in your ORM

Every major ORM wraps the two fixes above. Learn which call does which.

Rails offers three methods:

Restaurant.where(city_id: 7).limit(50).preload(:dishes)      # 2 queries: ... WHERE restaurant_id IN (...)
Restaurant.where(city_id: 7).limit(50).eager_load(:zone)     # 1 query with LEFT OUTER JOIN
Restaurant.where(city_id: 7).limit(50).includes(:zone, :dishes)
# includes picks: separate queries by default, a JOIN if you filter on the association

Django splits by relation type: select_related is a SQL join and only follows foreign keys and one-to-one relations; prefetch_related runs one extra query per lookup and joins in Python, so it handles reverse foreign keys and many-to-many:

(Restaurant.objects.filter(city_id=7).order_by("-rating")
    .select_related("zone")        # JOIN
    .prefetch_related("dishes")    # + 1 query: dishes WHERE restaurant_id IN (...)
    [:50])

Two Django traps: filtering a prefetched relation (r.dishes.filter(veg=True)) is a new query per restaurant and wastes the prefetch, so use a Prefetch object with a queryset instead; and Django 6.1’s new FETCH_PEERS mode batches lazy loads of foreign keys and one-to-one fields across all instances from the same queryset (two queries instead of 1 + N) but does not cover related managers such as r.dishes.all(), which still need prefetch_related.

Hibernate and JPA use query-level fetching:

// One query: a to-one plus at most one collection
select distinct r from Restaurant r
  join fetch r.zone
  left join fetch r.dishes
where r.city.id = :city

// Lazy collections initialised in batches: WHERE restaurant_id IN (?, ?, ...)
@OneToMany(mappedBy = "restaurant")
@BatchSize(size = 50)
private List<Dish> dishes;

A JPA entity graph does the same as join fetch declaratively. @BatchSize (or hibernate.default_batch_fetch_size for every lazy association) turns N lazy loads into N ÷ batch size queries, and FetchMode.SUBSELECT reloads all the collections with one subquery. Hibernate’s guide is candid that batching beats N+1, but a JOIN FETCH or a DTO projection fetching exactly the needed columns is usually better.

Fix four: GraphQL and DataLoader

GraphQL makes N+1 the default. Each field has a resolver, and a query like restaurants(first: 50) { name brand { name logo } } calls the brand resolver once per restaurant. A naive resolver runs one query each.

DataLoader, a small library in the GraphQL project’s GitHub organisation, ported from the Loader API Facebook built in 2010, fixes this without changing resolvers much. Each resolver calls brandLoader.load(brandId), which returns a promise instead of querying. The loader collects every load() made within a single tick of the event loop and then calls your batch function once with all the keys. The batch function must return an array of the same length, in the same order as the keys, so it re-sorts the database rows and fills gaps with null or an error.

It also memoises: within one loader instance, loading key 7 twice returns the same promise. That cache is deliberately per request: create loaders when a request starts and discard them at the end, so one user never sees another’s data, and keep Redis or Memcached for shared caching. maxBatchSize caps the keys per call when your database or API needs chunking.

Fix five: don’t fetch it at all

Sometimes the child data the page needs is a summary: a review count, an average rating, the cheapest dish. Then store the summary on the parent. A rating_avg and review_count on restaurants, updated when a review is written or by a job every few minutes, turns 50 aggregate queries into zero. Rails’ counter caches do this for counts, and Bullet suggests one when it sees size called on many associations.

You pay on the write path (every review now also updates a restaurant row, a potential hot spot for popular restaurants) and in freshness (the job’s interval). Caching whole restaurant cards in Redis is the same idea one layer up; fetch them with one multi-get (MGET), not 50 GETs, or you have rebuilt N+1 against the cache.

N+1 across microservices

The same shape appears between services, where each round trip is an HTTP or gRPC call instead of a query. A feed service builds a page of 50 posts and calls the user service once per author to get names and avatars, and the user service runs a query for each call.

Assume each internal call takes 3 ms (network, serialisation, the user service’s own query). Sequentially that is 50 × 3 = 150 ms. Firing them in parallel, 10 at a time, cuts latency to 5 waves × 3 ms = 15 ms, but the user service still receives 50 requests and its database 50 queries per page, and every retry multiplies. Parallelism hides N+1’s latency; it doesn’t remove its load. This is the chatty I/O anti-pattern.

The fixes mirror the database ones:

• A batch endpoint. GET /users?ids=… (or a gRPC BatchGetUsers) takes up to a few hundred ids and returns the users in one call, backed by one WHERE id = ANY($1) query. Return results keyed by id and document the maximum batch size.
• An aggregation layer. A backend-for-frontend or a federated GraphQL gateway composes the page and batches downstream calls with DataLoader-style loaders (API composition, BFF and GraphQL federation).
• Caching. Author profiles change rarely; cache them in the feed service with a short TTL and fetch only the misses in one batch call.
• Denormalisation. Copy the author’s display name into the feed item, kept up to date by events from the user service (change data capture), and the call disappears from the read path.

When N+1 is acceptable

N+1 is a cost, not a sin. It is fine when:

• N is small and bounded. An order page loading its 3 delivery addresses lazily makes 4 queries; the eager version saves about a millisecond and adds code.
• The children are usually already loaded. Hibernate returns an entity already in the persistence context without a query, and DataLoader’s memo does the same within a request, so 50 restaurants in the same zone cost one zone lookup, not 50.
• The relation is rarely used. Eager-loading reviews for every listing when one user in fifty opens them wastes more than the occasional lazy query. Bullet flags exactly this as “unused eager loading”.
• Latency doesn’t matter and load is low. A nightly export can afford it, though batching is usually just as easy there.

The test is the arithmetic: N bounded, total round-trip time within the page budget, and query volume the database can absorb at peak.

Cheat sheet

How to say it in the interview

“N+1 is one query for a list of N items and then one query per item for related data, usually an ORM lazy-loading inside a loop or a serializer. Each query is fast, but they’re sequential round trips: 51 at half a millisecond is 25 ms in one zone and seconds across regions, plus 50 times the query load and longer connection hold times. I catch it with a query-count test and by looking for a statement with a huge calls count in pg_stat_statements or a staircase in traces. I fix it by loading in bulk: JOIN to-one relations, fetch each collection with one WHERE id = ANY query, and not JOIN two collections, which multiplies rows. In GraphQL I use DataLoader; between services, a batch endpoint.”

Why not always JOIN everything in one query? To-one JOINs are free in rows, but each to-many JOIN multiplies them. Two collections of 30 and 40 per parent turn 50 parents into 60,000 rows, about 17 times the 3,550 rows that separate queries return, and LIMIT stops counting parents.

Isn’t making the association EAGER enough? No. Eager mapping loads the relation everywhere, even where it isn’t needed, and in JPA a query that doesn’t join fetch an eager association still loads it with one secondary select per row. Fetch per use case instead.

How does DataLoader know when to send the batch? It collects every load() call made in the current tick of the event loop and calls the batch function once at the end of it. Resolvers for sibling fields all run in that tick, so they share one batch. You can supply a custom scheduler for a longer window.

What changes when it happens between microservices? Each step is a network call with serialisation and its own database query, so it is costlier and multiplies load downstream. Parallel calls hide latency but not load. Add a batch endpoint, aggregate in a BFF with loaders, cache stable data or denormalise it into the caller.

How large can the IN list get? Large, but don’t push it. Use one array parameter (= ANY($1)) so the statement shape stays constant, and chunk into batches of a few hundred to a few thousand ids; with individual placeholders PostgreSQL’s protocol limit is about 65,000 per statement.

Where to go next

• Query execution plans: check that the batched query uses an index.
• Connection pooling: why longer hold times exhaust the pool.
• Chatty I/O: the general anti-pattern of many small calls.
• Extraneous fetching: the opposite failure, loading what you never use.
• API composition, BFF and GraphQL federation: batching across services.
• REST vs GraphQL vs gRPC: why GraphQL resolvers invite N+1.
• Data modeling and denormalization: storing summaries so there is nothing to fetch.

References and further reading

1. Ruby on Rails Guides: Active Record Query Interface, eager loading associationsguides.rubyonrails.orgThe guide’s own 1 + 10 = 11 queries example, and how includes, preload (separate IN query) and eager_load (LEFT OUTER JOIN) differ, plus strict_loading and its n_plus_one_only mode.
2. Django documentation: QuerySet API, select_related and prefetch_relateddocs.djangoproject.comselect_related as a SQL join limited to foreign key and one-to-one, prefetch_related as one extra query per lookup joined in Python, and why filtering a prefetched relation runs a new query.
3. Django documentation: Fetch modesdocs.djangoproject.comDjango 6.1’s FETCH_ONE (the 1 + N default), FETCH_PEERS (batched on-demand loading) and FETCH_RAISE, and which relations they cover.
4. Hibernate ORM User Guide: Fetchingdocs.jboss.orgSELECT, JOIN, BATCH and SUBSELECT strategies, why un-fetched EAGER associations still cause N + 1, @BatchSize and default_batch_fetch_size, and the Cartesian product from fetching several collections.
5. GraphQL DataLoader READMEgithub.comCoalescing loads within one tick of the event loop, the batch function’s same-length same-order contract, per-request memoisation, and maxBatchSize.
6. Bullet gem READMEgithub.comDevelopment-time detection of N+1 queries, unused eager loading and missing counter caches, and raising errors in tests.
7. Django documentation: Testing tools, assertNumQueriesdocs.djangoproject.comAsserting the exact number of queries a block of code runs, the basis of query-count regression tests.
8. PostgreSQL 18 documentation: pg_stat_statementspostgresql.orgStatements grouped by normalised text with constants replaced by parameters, per-statement calls and timing, and constant IN lists of different lengths merged into one entry.
9. AWS Regions and Availability Zonesaws.amazon.comAvailability Zones in a Region are within 100 km of each other, which bounds the extra round-trip time across zones.