The N+1 Query Problem: We Found 23 In One Codebase And Killed Every One

Most “slow API” tickets are not really about slow APIs. They are about one endpoint that quietly fires four hundred queries to render a page that should fire two. The N+1 query problem is the single most common reason a perfectly fine-looking codebase grinds to a halt the moment real data hits it.

I went through a real Node.js + Postgres service we ship to production with the goal of finding every N+1 query in it. There were 23. Some were obvious. Some were hiding three function calls deep inside a serializer. Here is exactly how to find them, exactly how to fix them, and the numbers from before and after.

What N+1 actually is

You query for a list of N items. Then, for each item, you fire another query to load something related. That is 1 query for the list and N queries for the children. Hence “N+1”.

The classic version, in Prisma:

const posts = await prisma.post.findMany({ take: 50 });

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

Looks reasonable. Reviews fine. Loads 50 posts and fires 51 queries. On a busy endpoint with a cold cache, this is the difference between a 40ms response and a 1.8s response — not because any single query is slow, but because round-trip latency adds up fast.

The same shape shows up in Sequelize, TypeORM, ActiveRecord, Django ORM, and every hand-rolled repository that ever existed. ORMs do not cause N+1. They just make it easier to write.

Step 1: Stop trusting your local environment

Locally, with 12 rows in the database, an N+1 query takes 8ms total. In production, with 200,000 rows and the same code shape, it takes 4 seconds. Your dev machine will not catch this. You need to either seed real-sized data or watch the actual query count.

The cheapest way to see the truth is to log every query with its timing. In Prisma:

const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
  ],
});

prisma.$on('query', (e) => {
  console.log(`${e.duration}ms  ${e.query}`);
});

Run a single API request. If a single endpoint produces more than ~5 lines, you almost certainly have an N+1 hiding in it.

For Sequelize:

const sequelize = new Sequelize(url, {
  logging: (sql, timing) => console.log(`${timing}ms  ${sql}`),
  benchmark: true,
});

For raw pg, wrap the client:

const originalQuery = client.query.bind(client);
client.query = async (...args) => {
  const start = Date.now();
  const result = await originalQuery(...args);
  console.log(`${Date.now() - start}ms  ${args[0]}`);
  return result;
};

This is enough to find the worst offenders. You do not need a fancy tool yet.

Step 2: Count queries per request

Once you know N+1 exists, the next move is to count queries per request automatically so you can fail a test or fail a build when the count regresses.

A tiny middleware that attaches a counter to each request:

import { AsyncLocalStorage } from 'node:async_hooks';

const queryContext = new AsyncLocalStorage<{ count: number }>();

prisma.$on('query', () => {
  const ctx = queryContext.getStore();
  if (ctx) ctx.count++;
});

app.use((req, res, next) => {
  queryContext.run({ count: 0 }, () => {
    res.on('finish', () => {
      const ctx = queryContext.getStore();
      console.log(`[${req.method} ${req.path}] queries: ${ctx?.count}`);
    });
    next();
  });
});

Now every request prints how many queries it ran. The first time we turned this on, the worst endpoint was firing 412 queries to load 50 posts with comments and authors. Once you can see it, you can fix it.

Step 3: Use the right tool to actually see EXPLAIN

For the queries that survive deduplication, you want to know which ones are slow on their own merits. Postgres has had this answered for years:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Read the output bottom-up. The first thing to look for is Seq Scan on a table that should have an index, or a Rows Removed by Filter number that dwarfs the rows returned. Both mean Postgres scanned far more than it needed to.

Two extension flags that are worth a one-time setup on a dev or staging database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS auto_explain;

pg_stat_statements aggregates queries by shape and ranks them by total time. If you have an N+1, the parent query and the per-row child query will both jump out — the child query will show up with a wildly high calls count and a small mean_exec_time. That signature is gold.

SELECT calls, mean_exec_time::int AS avg_ms, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

auto_explain automatically logs the plan for any query slower than a threshold. Set it to 100ms in staging and the slow plans show up in your Postgres logs without anyone having to remember to instrument them.

Step 4: The fixes that actually work

Once you have the offending code, there are four patterns that handle 95% of N+1s. In rough order of how often I reach for them:

Eager load with an include or JOIN

The simplest fix. Tell the ORM to load the relation in the same query.

Before:

const posts = await prisma.post.findMany({ take: 50 });
for (const post of posts) {
  post.author = await prisma.user.findUnique({ where: { id: post.authorId } });
}

After:

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

Two queries instead of 51. The Prisma engine issues the parent select and a single IN (...) query for the authors, then stitches them in memory. Same result for the API consumer.

Batch with DataLoader for graph-shaped reads

When the call graph is recursive — a GraphQL resolver, a serializer that walks an object tree, anything where you cannot easily push the relation up to the top-level query — DataLoader is the right tool.

import DataLoader from 'dataloader';

const userLoader = new DataLoader<string, User>(async (ids) => {
  const users = await prisma.user.findMany({
    where: { id: { in: [...ids] } },
  });
  const byId = new Map(users.map((u) => [u.id, u]));
  return ids.map((id) => byId.get(id)!);
});

// Anywhere in the request, in any resolver:
const author = await userLoader.load(post.authorId);

DataLoader collects every .load(id) call within a single tick of the event loop and fires one batch query. The interface still looks like findById, but under the hood it is findByIds. Drop one of these into a per-request context and most graph N+1s evaporate.

Aggregate in SQL, not in Node

A classic pattern: load posts, then for each post, count the comments.

const posts = await prisma.post.findMany({ take: 50 });
for (const post of posts) {
  post.commentCount = await prisma.comment.count({
    where: { postId: post.id },
  });
}

Push the count into SQL:

const posts = await prisma.post.findMany({
  take: 50,
  include: { _count: { select: { comments: true } } },
});

Or, when the ORM’s aggregate API isn’t expressive enough, drop to raw SQL:

const posts = await prisma.$queryRaw<Post[]>`
  SELECT
    p.*,
    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
`;

Postgres is far better at counting things than your application server. Let it.

Denormalize when the query never gets fast enough

For a feed endpoint we ship that loads 20 posts with their author, top 3 comments, like count, and current user’s reaction, we tried every combination of joins and aggregates. The plan was always somewhere between 80ms and 200ms — fine, but the budget was 25ms.

We added a comment_count and like_count column to posts, kept them in sync with triggers, and dropped the joins. The endpoint now serves in 12ms. The cost is two integer columns and four lines of trigger SQL. Worth it.

CREATE OR REPLACE FUNCTION bump_post_comment_count() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF (TG_OP = 'DELETE') THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER comments_count_trigger
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION bump_post_comment_count();

Denormalization gets a bad name because people reach for it before they have tried indexing or aggregating. Earn it; do not start there.

Step 5: Lock it down so it does not come back

Fixing N+1 is not a one-time job. The same patterns will sneak back in within two sprints unless you put a gate in place.

A simple integration test that asserts query counts works surprisingly well:

it('GET /feed runs at most 4 queries', async () => {
  let count = 0;
  prisma.$on('query', () => count++);

  await request(app).get('/feed?limit=50').expect(200);

  expect(count).toBeLessThanOrEqual(4);
});

If someone adds a serializer that fires per-row queries, this test fails before the code reaches main. We have three of these on our hottest endpoints. They have caught regressions four times in the last six months.

For production observability, add the per-request query count to your structured logs and chart it. A sudden jump in average queries-per-request on a stable endpoint almost always means someone reintroduced an N+1.

The before-and-after

After two days of work — finding, fixing, and adding the regression tests — the numbers across the worst five endpoints in the service:

Endpoint	Queries before	Queries after	p50 before	p50 after
GET /feed	412	3	1.81s	42ms
GET /users/:id/posts	187	2	820ms	28ms
GET /search	96	4	640ms	71ms
GET /notifications	68	3	410ms	24ms
GET /admin/audit	244	5	2.10s	96ms

No new infrastructure. No caching layer. No rewrite. Just finding the queries and replacing them with the version that should have been written the first time.

A few patterns that did not pay off

Worth calling out, since these get recommended a lot:

A blanket Redis cache in front of the API. Tempting, but it papers over the underlying problem and creates a new one (cache invalidation). The endpoints we cache today are the ones we cache after fixing the queries — not before.

Switching ORMs. We considered moving from Prisma to Drizzle “to get more control”. The N+1s would have moved with us. The bottleneck was code shape, not ORM.

Manually sharding the parent query into smaller LIMIT chunks. Marginally helps for a single slow query. Does nothing for an N+1, since the per-row child query is the actual problem.

The takeaway

If your API is slow and you have not yet logged every query for one request, do that first. It will tell you within five minutes whether you have an N+1 problem, and which endpoint to look at. Most fixes are one-line changes — an include, a DataLoader, a _count — and the speedups are not 20%; they are 10x to 50x.

The work is unglamorous. Nobody will notice until the graphs go down and stay down. That is the point.

---

This article was put together by The Practical Developer, with notes from query-tuning work we have done on real production systems. If you are building a service where this kind of database hygiene matters and you would rather hire the practice than learn it the hard way, Yojji is the international software development studio behind a lot of the work that ends up here. Founded in 2016, with offices across Europe, the US, and the UK, Yojji builds custom web and mobile products with senior engineers, dedicated teams, and a strong bias toward shipping software that holds up under real load — including the boring, valuable work of keeping query counts honest.