Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

N+1 queries: lazy loading, joins & batching

Why a page that fetches a list once and then reaches back to the database for every row sends N+1 queries, what the extra round trips cost, how to catch them in logs and tests, and the fixes with their trade-offs: a join, a second query by ids, a grouped count, batching per request, and not loading everything.

A loop with no query in it

The template behind a city library's "New this week" page is a dozen lines long, and none of them mentions the database. It asks for one branch's 50 newest titles, then loops over them and prints each title's author, title.author.name, the branches holding a copy, title.copies, and how many readers are waiting for it, title.holds.count. On a developer's laptop the page renders in a blink. The database's log for one page load tells another story:

SELECT * FROM titles WHERE branch_id = 4 ORDER BY added_on DESC LIMIT 50, then SELECT * FROM authors WHERE id = 311, SELECT * FROM copies WHERE title_id = 9020 and SELECT count(*) FROM holds WHERE title_id = 9020, and those three again for each of the other 49 titles.

One query fetched the list; then each of the N rows sent queries of its own. That is the N+1 query problem, here 1 + 50 × 3: 151 statements for a page whose data fits in three. The cost hides in what title.author does when it is read.

Where the extra queries come from

The ORM loads relations by lazy loading: title.author looks like a field, but the first time it is read it runs a query for that one author. A screen that never shows the author never pays for it; inside a loop, it can pay once per row, as it does in this log. How ORMs load and track objects belongs to Active Record vs Data Mapper; the problem appears under both styles, and without an ORM too: a serializer that embeds each author, or a loop calling holdsRepository.countFor(title.id), makes the same shape.

What 151 queries cost

Each statement sent on its own pays a network round trip, plus the database's work to run it, and to parse and plan it unless a prepared plan is reused. If a simple indexed query like these costs about 0.5 ms end to end inside one data centre, 151 of them take about 75 ms, where three queries take a few milliseconds. On the laptop, with the database on the same machine, the 151 cost about 15 ms in total, and nobody noticed.

Then the library moved its web servers to a cloud region in the same country and kept the catalogue database in the city's data centre, a 10 ms round trip away. Lazy loads run one after another, each waiting for the previous answer, so the page now spends 151 × 10 ms, about 1.5 seconds, on round trips alone. Three queries would wait 30 ms. An index cannot help here: one on holds.title_id makes each of the 50 counts fast, and all 50 round trips remain. Indexing & query optimisation makes one query fast; N+1 is about how many run.

The same page, two ways

Same rows, same database: one query for the list and three per title, or three in all.

Counting statements, not milliseconds

While the database sits next to the code, the query count shows what latency hides: a trace span per statement turns one request into a ladder of queries that differ only in an id. Django's assertNumQueries fails a test when a block runs a different number of queries than expected, and Rails' strict_loading raises an error on a lazy load. A budget of three queries for this page fails the day someone adds title.cover to the template.

Fetching the author with the list

The fix is to load each relation together with the list, called eager loading, in one of two forms. The first is a join: SELECT … FROM titles JOIN authors ON authors.id = titles.author_id returns each title with its author's columns on the same row, and the ORM builds both objects from it (Django's select_related, Rails' eager_load). For a to-one relation like the author, each title is still one row, and an author with three new books costs three repeated sets of columns.

The join that multiplies

A to-many relation changes the arithmetic. A join to copies returns one row per copy, so a title with 12 copies comes back 12 times. Join copies and holds in the same query and every copy row pairs with every hold row: 12 copies and 40 holds make 480 rows for one title. That cartesian explosion can cost more than the queries it replaced. A join to a collection also breaks the page size: LIMIT 50 applied to the joined result counts joined rows, not titles, so the ORM has to work around it, in some ORMs by loading every row and applying the limit in memory.

The second form avoids the multiplication: fetch the list, then the related rows for all of it in one more query, SELECT * FROM copies WHERE title_id IN (9020, 9021, …), and let the ORM attach each copy to its title in memory (Django's prefetch_related, Rails' preload). One query per collection, no duplicated rows. The limit is the length of the list: 50 ids is nothing, but 10,000 ids in one statement can hit the cap drivers and databases put on bind parameters. Some ORMs split such a list into chunks; usually the better fix is a smaller page, as Pagination: offset, keyset & cursors explains.

Count where the rows are

The holds need neither form. The page prints a number, and eager loading would carry 40 hold rows across the network to print "40". Let the database count, once for all 50 titles: SELECT title_id, count(*) FROM holds WHERE title_id IN (…) GROUP BY title_id. A row comes back only for titles that have holds. A title nobody is waiting for is missing from the result, not present with a zero, so the code must read a missing id as 0, or the page shows a blank or fails on the lookup. The page is down to three queries: titles joined to their authors, copies by ids, holds counted.

When nobody holds the whole list

Eager loading needs the code that fetches the list to know what each row will need. When the per-row reads live in separate pieces of code, a component per title or a resolver per field, no single place sees the list. Batching per request fills that gap: each piece asks a loader for one key, the loader waits until the current round has asked, then sends one query for all the keys. The keys must reach the loader before it sends its query; a loop that asks for one key and waits for the answer before asking for the next still sends one query per row. DataLoader, the batching loader most often used with GraphQL, is explained in API styles: REST vs GraphQL vs gRPC.

The same shape crosses the network. A page that asks a reviews service for each title's rating makes 50 HTTP calls, one round trip each, and the fix is the same: give the service an endpoint that takes a list of ids and answers for all of them in one call. Why the ratings live behind another service at all is a question for Database per Service. A shared cache in front of the per-row reads, such as Redis, still pays a round trip per key unless the keys are fetched in one call (Caching layers).

Loading everything instead

A team burned by N+1 adds every relation the model has to the base query all screens share, and the search page, which shows only titles, now fetches authors, copies and holds for each of its 200 results. The database does work nobody sees, and memory grows with each relation. Eager loading belongs to the screen, not to the model: load what this page prints.

And N+1 is not wrong every time. A page of five rows inside one data centre makes 16 queries, about 8 ms, and a relation read only when a reader opens a single title is cheaper loaded lazily. What to watch is a query count that grows with the length of the list. No Challenge scores that count, and the Grid shows calls between components, not the statements behind them; the closest is Comment System, whose threads are sorted, paged and voted on. A thread page is this library page with comments for titles: work out how it gets each comment's author and vote total in a fixed number of queries, and what it shows for a comment nobody has voted on.

Practice this