The N+1 Query Problem

You write an endpoint. It's fast in development with a handful of rows. You ship it. Six months later, with 10,000 rows in the table, it's timing out. You check the logs and see something like this:

SELECT * FROM orders WHERE id = 1;
SELECT * FROM users WHERE id = 42;
SELECT * FROM orders WHERE id = 2;
SELECT * FROM users WHERE id = 17;
SELECT * FROM orders WHERE id = 3;
SELECT * FROM users WHERE id = 42;
-- ... 997 more pairs

That's the N+1 problem.

What Is N+1?

N+1 happens when you fetch a list of N records, and then for each record, make an additional query to fetch related data. Instead of 2 queries (one for orders, one for all users), you make 1 + N queries (one for orders, then one per order for its user).

In code it usually looks like this:

// 1 query: get all orders
const orders = await db.query('SELECT * FROM orders')

// N queries: get the user for each order
const results = await Promise.all(
  orders.map(async (order) => {
    const user = await db.query('SELECT * FROM users WHERE id = $1', [order.userId])
    return { ...order, user }
  })
)

With 1,000 orders, this makes 1,001 database round-trips. Each query has network latency overhead. Even if each takes 1ms, that's a second of pure overhead before any query time.

Why It Happens

The pattern is so easy to fall into because:

It looks correct, and it is correct
With small datasets in development, it's fast enough to not notice
ORMs can make it invisible, a field access triggers a hidden query

Here's the same bug through an ORM (pseudocode):

const orders = await Order.findAll()

for (const order of orders) {
  const user = await order.getUser() // triggers a SELECT per iteration
  console.log(user.name)
}

The Fix: Batch the Queries

Instead of one query per record, fetch all related records in one query:

// 1 query: get all orders
const orders = await db.query('SELECT * FROM orders')

// 1 query: get all relevant users at once
const userIds = [...new Set(orders.map(o => o.userId))]
const users = await db.query(
  'SELECT * FROM users WHERE id = ANY($1)',
  [userIds]
)

// Map users by ID for O(1) lookup
const usersById = Object.fromEntries(users.map(u => [u.id, u]))

// Join in memory
const results = orders.map(order => ({
  ...order,
  user: usersById[order.userId],
}))

Now it's 2 queries regardless of how many orders you have.

SQL JOIN

Alternatively, use a JOIN:

SELECT
  orders.*,
  users.name AS user_name,
  users.email AS user_email
FROM orders
JOIN users ON users.id = orders.user_id

One query, database handles the join. This is often the right answer for simple cases, though it can get complex when you need multiple levels of nesting.

ORM Eager Loading

ORMs usually have a mechanism to avoid N+1, they batch the related queries automatically if you tell them to:

// Sequelize
const orders = await Order.findAll({
  include: [{ model: User }],
})

// TypeORM
const orders = await orderRepo.find({
  relations: ['user'],
})

// Prisma
const orders = await prisma.order.findMany({
  include: { user: true },
})

The ORM does the batching for you. The key is knowing when your ORM is doing it and when it's not.

Detecting N+1 in Practice

In development, enable query logging and look for repetitive patterns:

// With Prisma
const prisma = new PrismaClient({
  log: ['query'],
})

In production, track query counts per request in your observability tooling. A request that makes 500+ queries is almost certainly an N+1.

Some tools that help: Bullet Gem (Rails), the why-is-node-running npm package for tracing async queries, Datadog APM's database trace view, or just reading your slow query logs.

The Rule

Any time you're iterating over a list and querying inside the loop, pause. Ask yourself: can I batch this? Almost always the answer is yes. The batched version is the same data, the same logic, just expressed differently.

The N+1 problem is one of the few bugs that reliably gets worse as your product succeeds. Small datasets hide it; large datasets expose it. Fix it before it becomes urgent.