The N+1 Query Problem: One Loop, Four Hundred Round Trips

The code looks fine. That is what makes this bug survive review.

const posts = await db.post.findMany({ take: 50 });   // 1 query

for (const post of posts) {
  console.log(post.author.name);                       // 50 more queries
}

Fifty-one round trips to render fifty rows. Locally, against a database on localhost, each is a fraction of a millisecond and nobody notices. In production, with the database across an availability zone at 2 ms, that loop costs a tenth of a second in pure waiting — and it scales with the page size, so the “load more” button makes it worse.

Why the code gives you no warning

post.author reads like a property. It is a network request. The ORM made relations lazy so that fetching a post does not drag its author, its comments and its tags along uninvited — which is correct, and which means the loop above is indistinguishable from ordinary object access.

This is why it survives code review: there is nothing to see. The tell is structural, not textual — a database access inside a loop over database results — and once you learn to look for that shape you find it everywhere.

The fix: ask once

Tell the ORM what you need up front. Every ORM has this; only the spelling differs.

// Prisma
const posts = await db.post.findMany({ take: 50, include: { author: true } });

// Django
Post.objects.select_related("author")[:50]        // FK: one JOIN
Post.objects.prefetch_related("tags")[:50]        // M2M: second query, batched

// Rails
Post.includes(:author).limit(50)

// SQLAlchemy
select(Post).options(selectinload(Post.author)).limit(50)

Two strategies sit under these names, and the difference matters:

Join. One query, relation joined in. Best for to-one relations. On to-many it multiplies rows — fifty posts with twenty comments each returns a thousand rows with the post columns repeated twenty times, which can be slower than the problem you were fixing.

Batched second query. Fetch the posts, collect the ids, then one WHERE author_id IN (...). Two queries total regardless of N, no row multiplication. This is what Django’s prefetch_related and SQLAlchemy’s selectinload do, and it is the safer default for to-many.

Where it hides

Serializers. The query looks clean; the serializer touches author.name while rendering each item. The N+1 is in the presentation layer, far from the query.

Computed properties. A post.commentCount getter that runs a query. Called once, fine. Called in a map over a list, it is an N+1 that grep will not find.

GraphQL resolvers. The canonical case. Each field resolves independently, so a query asking for fifty posts and their authors calls the author resolver fifty times. The standard fix is a per-request DataLoader, which collects ids within a tick and issues one batched query — and it is worth adding before you need it, because retrofitting it later means touching every resolver.

Nested N+1. Posts → comments → comment authors. Fifty posts, twenty comments each, and an author lookup per comment is a thousand queries from one page load.

Catch it automatically

Finding these by reading code does not scale, and finding them in production means a user found them first.

Count queries per request. Log it, and alert when an endpoint crosses a threshold. A route that suddenly went from 3 queries to 300 is unambiguous.

Assert it in tests. The most durable fix, because it prevents regression rather than reporting it:

test('post list issues a bounded number of queries', async () => {
  const { queries } = await countQueries(() => getPostList({ take: 50 }));
  expect(queries).toBeLessThanOrEqual(3);
});

That test fails the moment someone adds a lazy relation access to a serializer, which is exactly when you want to hear about it. Note it asserts a bound, not an exact number — an exact count makes the test brittle against harmless refactors and people delete brittle tests.

Use the detectors. Bullet (Rails), nplusone (Django), or your APM’s trace waterfall, where an N+1 is visually obvious as a picket fence of identical spans.

When N+1 is fine

Not every one is worth fixing. If N is reliably small — a detail page with one user and three related records — five queries against a local database is nothing, and eager-loading everything has its own cost: you fetch columns and rows nobody reads, and inflate memory.

The rule is about unbounded N. If N grows with a collection, page size, or anything a user controls, fix it. If N is two, leave it alone and spend the attention somewhere it matters.

What you have actually fixed

Not slow SQL. Every one of those queries was fast — that is why indexes and query plans do not help here, and why people spend a long time optimising the wrong thing.

You fixed the number of times you asked. The database was never the bottleneck; the round trip was. That distinction is the whole lesson, and it generalises: the fastest query is the one batched into a query you were already making.