N+1 Query Problem

The N+1 problem runs one query for a list and one more per row. Eager loading, joins, or batched lookups reduce it to a constant number of queries.

performance

Definition

The N+1 query problem occurs when an application fetches a list of N rows and then issues an additional query for each row to load an association, resulting in N+1 database round trips. It is common with lazy-loading ORMs and per-row API calls. The fix is to load related data in one query or a bounded number of batched queries, then assemble the result in memory.

Context and scope

N+1 shows up in dashboards, exports, and serializers that access relationships inside a loop. It is often invisible in small datasets and becomes severe at scale because latency multiplies with row count and connection usage. Measuring query counts per request is the reliable way to detect it.

Common misconceptions

ORMs automatically prevent N+1 — lazy loading often causes it; eager loading must be requested or configured
A database index fixes N+1 — indexes speed each query but do not reduce the number of round trips
Joining everything is always better — wide joins can duplicate data and inflate transfer; batching is sometimes better

Anti-patterns

Accessing a lazy-loaded association inside a loop without eager loading
Calling an API per item instead of using a batch endpoint
Ignoring query counts because the local dataset is small

Related concepts

Related examples

Authoritative source

https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance

The pattern

// ❌ One query for users, then one per user
const users = await db.user.findMany();
for (const user of users) {
  user.posts = await db.post.findMany({ where: { authorId: user.id } });
}

For 100 users this issues 101 queries. Even at 2 ms each, the request spends most of its time waiting for round trips.

Fixes

// ✅ One query with a join/relation load
const users = await db.user.findMany({ include: { posts: true } });

-- ✅ Set-based join
SELECT u.id, u.name, p.id AS post_id, p.title
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.id = ANY($1);

For remote APIs, replace per-item calls with a batch endpoint or a DataLoader-style batching layer that coalesces keys within a tick.

When to use which

Eager loading is convenient when the relationship is small and always needed. Joins are efficient for set-based reads but can duplicate parent rows. Batched lookups work well across service boundaries or when the related data comes from another system.

Verification

Count queries per request in tests or development. A list endpoint’s query count should not grow linearly with the number of rows. Compare latency at 10, 100, and 1,000 rows; N+1 often looks fine at 10 and fails at 1,000.

Pitfalls

Loading all associations eagerly can create its own performance problem when the graph is large. Pagination plus eager loading can still produce huge payloads. Measure both query count and data volume, and avoid unbounded include graphs.