The N+1 Problem & Query Optimization

The N+1 problem is when we run 1 query to fetch a list, then N more queries — one for each item in that list — to fetch related data. So a list of 100 posts becomes 101 database round-trips.

In simple language — instead of asking the database once, we ask it in a loop, hammering it once per row.

It’s the single most common performance bug in ORM-heavy backends, and interviewers love it because the fix shows we actually understand what our ORM is doing under the hood.

Why it happens

It almost always sneaks in through an ORM (a library that maps objects to rows — think Prisma, Sequelize, ActiveRecord). The code looks totally innocent:

const posts = await db.query("SELECT * FROM posts LIMIT 100");  // 1 query
for (const post of posts) {
  // this fires a fresh query EVERY loop iteration → 100 queries
  post.author = await db.query("SELECT * FROM users WHERE id = $1", [post.userId]);
}
// total: 1 + 100 = 101 queries

Each .author access looks like a property, but it’s secretly a database call. One list of 100 posts → 101 queries. On a real page that’s the difference between 20ms and 2 seconds.

The fixes

The core idea is always the same: stop looping, start batching. Ask for everything in one (or two) queries.

1. JOIN — let the database do the stitching. One query brings back posts with their authors.

SELECT posts.*, users.name AS author_name
FROM posts
JOIN users ON users.id = posts.user_id
LIMIT 100;   -- one round-trip, done

2. Eager loading — the ORM way to say “fetch the relation up front, not lazily”. Most ORMs expose this as include, with, or preload. Under the hood it’s usually one JOIN or one follow-up WHERE id IN (...) — either way, not a loop.

// Prisma-style: one extra IN-query instead of 100
const posts = await db.post.findMany({ include: { author: true }, take: 100 });

3. Batching / DataLoader — the go-to for GraphQL. A DataLoader collects all the individual .author requests fired during one tick, dedupes them, and fires a single WHERE id IN (1,2,3,...). It turns N calls into one behind the scenes, so our code can stay simple.

-- what batching collapses the N author lookups into:
SELECT * FROM users WHERE id IN (7, 12, 12, 30, 41);  -- deduped keys

Other quick wins

The N+1 fix is the headline, but a few habits keep queries fast in general:

• Select only the columns we need — SELECT id, name beats SELECT *. Less data over the wire, and it opens the door to covering indexes.
• Avoid SELECT * — it breaks covering indexes and drags along big columns (blobs, JSON) we didn’t want.
• Index the columns we filter and join on — an unindexed JOIN or WHERE is a full scan hiding in plain sight.
• Paginate — LIMIT/OFFSET or keyset pagination. Never fetch 100k rows to show 20.
• Run EXPLAIN — it’s the truth serum. It shows whether a query is a fast index scan or a slow sequential scan, and whether a JOIN is doing something silly.

Practical takeaway

If a page feels slow, the first thing to check is the query count in the logs. Seeing “1 + N” repeated queries is the smoking gun for N+1 — fix it with a JOIN, eager loading, or a DataLoader. Then tidy up the basics: select only needed columns, index the filter/join columns, paginate, and let EXPLAIN confirm the plan. Turning 101 round-trips into 1 is often the biggest single win we’ll ever ship.