Data AccessGENERALDATABASE-SPECIFICFRAMEWORK-SPECIFIC

The N+1 Query Problem

One query for the list, one more for every row: 100 users become 101 statements, and the source code shows none of it.

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

Why does an endpoint that reads one table issue a hundred queries, and how do I see it before production does?

The requirement

GET /users should return each user with their recent order count. It works, it passes review, and it is the slowest endpoint in the service.

The obvious build

Load the users, then in the response loop read user.orders for each one. It reads exactly like the requirement, which is why it survives review.

Why it breaks

One query returns 100 users. Then the loop issues one SELECT per user. That is 101 statements where one or two would do — and the count scales with the page size, so raising the limit to 500 makes it 501.

How it breaks in production

One query returns 100 users. Then the loop issues one SELECT per user. That is 101 statements where one or two would do — and the count scales with the page size, so raising the limit to 500 makes it 501.
Each of those statements is individually fast, so the slow query log shows nothing. The endpoint is slow because of round trips, not because of any single query.
Every one of the 101 statements takes a connection from the pool for its duration. Under concurrency an N+1 endpoint is the fastest way to exhaust a pool (Connection Pools).
The pattern hides in serialization, in template rendering, in a map that calls a helper, and in a permission check performed per row. It is rarely written as an obvious loop.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

The "1" is the query that returns the parent rows. The "N" is one query per parent, triggered by touching a lazy relation or by calling a function that queries (What an ORM Actually Does).
Nothing in the code distinguishes the N from a field access. user.orders and user.email are the same syntax and differ by three orders of magnitude in cost.
The cost is dominated by round trips, not by work in the database. Each statement pays request/response latency on the connection, and those costs are serial: statement 2 does not start until statement 1 returns.
It is therefore a latency problem that grows linearly with result size, and a throughput problem that grows with concurrency, since each in-flight request occupies a pool connection for N sequential round trips.
Depth multiplies. Users to orders to line items is not 1+N, it is 1 + N + (N x M) — the same bug nested.

1 + 100 = 101

The arithmetic is the whole lesson. One statement fetches the parents; then one statement per parent fetches the children. With a page of 100 users you issue 101 statements to answer one request, and the code that does it is four lines long.

Read the log below next to the code that produced it. Nothing in the source suggests a hundred round trips, and that is precisely why this bug is everywhere.

The four lines that issue 101 statements

1const users = await repo.findUsers({ limit: 100 })   // 1 statement
2 
3return users.map((u) => ({
4  id: u.id,
5  name: u.name,
6  orderCount: u.orders.length,                        // 1 statement, per user
7}))

The N is u.orders.length. It is inside a map, not a for loop, and it looks like a property read — three reasons it passes review.

SELECT id, name FROM users LIMIT 100;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
... 96 more identical statements ...
SELECT * FROM orders WHERE user_id = 100;

-- 101 statements. None of them slow.
-- 101 sequential round trips on one pooled connection.

Three fixes, three different costs

There is no single right answer, and choosing without knowing the data shape is how a fix becomes the next incident. The deciding question is how many children a parent has, and whether you need the children at all.

How should the related data be fetched?

What do you need from the children, and how many are there?

Join in one statement

when Few children per parent, and you need their fields. One round trip, one plan.

cost Row multiplication: 100 parents x 50 children is 5,000 rows transferred and de-duplicated in memory. Two sibling one-to-many joins multiply each other.

Second batched query (`WHERE parent_id IN (...)`)

when Many children per parent, or several relations to load. Two statements total, regardless of parent count.

cost One extra round trip, an in-memory grouping step, and a bind-parameter list that must be chunked when the parent set is large.

Aggregate in SQL

when You need a count, sum or "latest one" — not the children themselves.

cost One statement and no children in memory, but you cannot then render the children without going back for them.

Do nothing (accept the N)

when The parent set is bounded and tiny — a fixed set of three configuration rows — and clarity is worth more.

cost A latent scaling bug the day someone removes the bound. Write down why it is safe.

Counting is a better answer than loading

GENERALBoth statements are portable SQL; the GROUP BY column list is stricter on MySQL with ONLY_FULL_GROUP_BY enabled than on Postgres, which allows grouping by primary key alone.

The requirement said "with their recent order count". It never asked for the orders. A large share of real N+1s disappear once you notice that the children were fetched only to be reduced to a number.

Doing the reduction in the database means one statement, no row multiplication and no children in application memory. It is the cheapest of the three fixes and the most often overlooked, because the object model invites you to traverse the relation.

Order count per user

Load children, count in the application

-- 1 + N statements, all rows transferred
SELECT id, name FROM users LIMIT 100;
SELECT * FROM orders WHERE user_id = $1;  -- x100

Aggregate in the query

-- 1 statement, 100 rows, no order rows transferred
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.tenant_id = $1
GROUP BY u.id, u.name
LIMIT 100;

The database is a set processor and counting is a set operation. This removes both the round trips and the data transfer, rather than trading one for the other — which is what the join and batch fixes do.

How to build it

Most important first.

Fix it by fetching the related data in a bounded number of statements. There are three shapes, and each has a real cost: a join, a second query with WHERE parent_id IN (...), or an aggregate computed in SQL (Eager Loading and Batching).
When you only need a count or a sum, do not load the children at all. A grouped aggregate or a lateral subquery answers "how many orders" in the parent query.
Make the fetch strategy explicit at the call site — the use case knows what it needs; the entity class does not.
Add a per-request query counter and assert on it in tests for list endpoints. This is the only reliable defence, because reading the code will not tell you.
Cap page size. An N+1 on 20 rows is a smell; on 5,000 rows it is an outage (Pagination That Survives a Large Table).

What can go wrong

Failure modes

The join fix multiplies rows: one user with 50 orders becomes 50 result rows, and a page of 100 users becomes 5,000 rows to transfer and de-duplicate. Combining two one-to-many joins multiplies both, producing a Cartesian blowup (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
The eager-loading fix over-fetches: loading every relation on every entity turns a cheap endpoint into a wide one, moving the cost from round trips to payload size.
Fixing the visible loop while a permission check, an audit hook or a computed property keeps querying per row.
IN (...) batching with an unbounded parent list produces a statement with 10,000 bind parameters, which some drivers and databases reject outright.
Caching each child lookup instead of batching: the query count drops in staging where the cache is warm and returns in production where it is not (Cache-Aside).

What can race

The N queries do not run in one consistent snapshot unless the whole request is in one transaction: a child row can be inserted between statement 3 and statement 40, so the response mixes two points in time (Isolation Levels).
Under READ COMMITTED — the Postgres default — each of the N statements sees its own snapshot, so an N+1 read is genuinely non-atomic even inside a transaction.

Security

Per-row authorization checks that hit the database are an N+1 with a security label. Batch the check, or filter in the query so unauthorized rows never load (Object-Level Authorization).
An endpoint whose cost scales with a client-supplied limit is a denial-of-service lever. Enforce a maximum server-side; the client's parameter is a request, not an instruction (Resource Limits).
The join fix must carry the tenant predicate onto every joined table, not just the parent, or a child row from another tenant can appear in the result (Tenant Isolation).

Misreads

"The database is slow." No statement is slow. The endpoint is slow because it made 101 sequential round trips.
"Add an index." An index makes each of the 101 queries faster and leaves 101 queries. Sometimes worth doing, never the fix.
"Just join everything." A join is one of three fixes and the one most likely to multiply rows into a much larger problem.
"It only happens with ORMs." Any code that calls a query function inside a loop has it, including hand-written SQL and calls to another service over HTTP (Fan-Out: Waiting for the Slowest of Seven is the same shape one layer up).
"It is fine, the queries are cached." Then you have moved the problem to cache warmth and added an invalidation obligation (Cache Invalidation).

Operating it

How you see it in production

Query count per request, tagged by route. A route where count grows with page size is definitive — no interpretation needed.
A distributed trace shows it as a picket fence: one span, then a long row of short, identical spans (The Comb: N+1 as a Visible Shape).
The database sees many executions of one normalised statement with low individual cost. Grouping the statement log by normalised text surfaces it immediately.
Compare wall-clock endpoint time to summed statement time. If they are close and neither statement is slow, the problem is count.

What changes at 10x and 100x

At 10x page size the endpoint is 10x slower — linear, predictable and the reason it "suddenly" broke when someone raised a default limit.
At 10x concurrency it stops being a latency problem and becomes a saturation problem: N connections held for N sequential round trips per request, and the pool queue grows (Connection Pool Exhaustion).
The database rarely shows stress. CPU is fine, no query is slow, and the graph that moves is connection count and application latency — which is why this is so often misdiagnosed as "the database is slow".

What this costs

Joining trades round trips for row multiplication and a wider result set. Correct when children per parent are few; wrong when they are many.
Separate batched queries trade one extra round trip for no multiplication. Usually the best default, and it costs an in-memory grouping step.
Aggregating in SQL is the cheapest of all when you only need a number, and it costs expressiveness — you cannot then show the children without fetching them.
Query-count assertions in tests cost maintenance: they fail on legitimate changes. That is the point, and it is still noise.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALIndependent of language, ORM and database. It is a property of issuing one query per element of a collection, which hand-written code does just as easily.
DATABASE-SPECIFICThe relative cost of the join fix differs by engine and data shape: Postgres will often choose a hash join for a large IN list, while MySQL/InnoDB historically favours nested-loop joins driven by an index, which changes when a join beats two round trips (Join Algorithms: Nested Loop, Hash, Merge).
FRAMEWORK-SPECIFICHibernate and SQLAlchemy produce this by accident through lazy proxies; Django needs select_related/prefetch_related; Prisma requires explicit include, so the accidental form is rarer there but the deliberate loop is not.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designover-under-fetching graphql-costs

Databasejoins join-algorithms-internals query-tuning reading-explain

Performancen-plus-one-traces fan-out-latency trace-waterfall

Concurrencysequential-await-trap