The N+1 Query Problem: How to Spot It and Fix It

A page that loads in 80 ms in development takes 4 seconds in production, and no single query is slow. Look at the query log and you'll find the same statement repeated hundreds of times with a different ID. That's the N+1 query problem: 1 query for a list, plus N more — one per item.

How it happens

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

for (const post of posts) {
  post.author = await prisma.user.findUnique({           // 50 queries
    where: { id: post.authorId },
  })
}

51 round trips. Each is fast — a primary-key lookup — but round-trip latency dominates: at 1–2 ms each on a local network (far more if the database is in another region), that's 50–100 ms of pure waiting, and it grows linearly with page size. Nest it (posts → comments → authors) and it multiplies.

It's especially common in AI-generated code, because each piece looks correct in isolation, and development databases rarely have enough rows to feel it. (What Is an ORM?.)

Where it hides

Explicit loops with awaits inside, as above.
Lazy-loaded relations — Django's post.author, Rails' post.author, SQLAlchemy's default lazy loading, TypeORM lazy relations. Accessing the attribute in a template silently fires a query.
Serializers that include nested relations per item.
GraphQL resolvers — Post.author resolved independently for every post in a list is N+1 by construction. (GraphQL vs REST.)
Promise.all over a map — concurrent, so it's faster than sequential, but still N queries and N connections from the pool. (Postgres Connection Pooling.)

Detecting it

Turn on query logging in development. Prisma: new PrismaClient({ log: ["query"] }). Django: django-debug-toolbar or the connection.queries list. Rails: the development log, or the Bullet gem, which flags N+1 automatically.

Count queries per request. A request that issues more than ~10–20 queries deserves a look. Some teams add a middleware that logs a warning above a threshold, or a test that asserts a maximum query count for key endpoints.

In production, look at pg_stat_statements:

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

A simple SELECT … WHERE id = $1 with calls in the millions, far outnumbering the list queries that feed it, is the signature.

Tracing (OpenTelemetry and APM tools) shows it vividly: a waterfall of dozens of identical short database spans under one request. (Structured Logging.)

Fix 1: eager loading

Ask the ORM to fetch the relation up front.

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

# Django
posts = Post.objects.select_related("author")[:50]            # FK: SQL JOIN
posts = Post.objects.prefetch_related("comments")[:50]        # reverse/M2M: 2nd query with IN

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

Under the hood this is either a JOIN or a second query with WHERE id IN (...) — 2 queries instead of 51, regardless of page size.

Fix 2: batch it yourself

When the ORM can't express it, collect IDs and fetch once:

const posts = await db.post.findMany({ take: 50 })
const authorIds = [...new Set(posts.map((p) => p.authorId))]
const authors = await db.user.findMany({ where: { id: { in: authorIds } } })
const byId = new Map(authors.map((a) => [a.id, a]))
for (const p of posts) p.author = byId.get(p.authorId)

Fix 3: DataLoader for GraphQL (and other fan-out)

A DataLoader collects every load(id) call made within one tick of the event loop and issues a single batched query:

const userLoader = new DataLoader(async (ids: readonly string[]) => {
  const users = await db.user.findMany({ where: { id: { in: [...ids] } } })
  const byId = new Map(users.map((u) => [u.id, u]))
  return ids.map((id) => byId.get(id) ?? null)   // same order as ids
})

// resolver
Post: { author: (post) => userLoader.load(post.authorId) }

Create loaders per request, so caching doesn't leak data between users.

Fix 4: let SQL do it

For aggregates, a join with GROUP BY beats fetching children to count them:

SELECT p.id, p.title, count(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
ORDER BY p.created_at DESC
LIMIT 50;

Don't overcorrect

Giant eager loads — include five levels deep can produce a huge join with row explosion, or pull far more data than the page shows. Select only the fields you need.
One-off detail pages fetching three related records don't need batching.
Paginate first. Eager loading 10,000 posts' authors is still slow. (API Pagination.)

And make sure the batched lookups hit indexes — foreign key columns like comments.post_id are not indexed automatically in PostgreSQL. (Database Indexes.)

Preventing it with AI tools

Add to CLAUDE.md: "Never query the database inside a loop. Use include/select_related or batch by IDs. For list endpoints, report the number of queries executed." Then ask the agent to verify with query logging on, against a database seeded with realistic volumes — N+1 is invisible with five rows. (How to Write a CLAUDE.md.)

EasySpawn gives each project a managed Postgres in the same workspace as your app, so Claude Code can seed realistic data, count queries per request, and prove an N+1 fix before it ships. See how it works or join the waitlist.