N+1 Query Problem in ORMs

The N+1 query problem in ORMs happens when code loads a list of parent records, then triggers another database query for each row in that list. The endpoint still returns correct data, but query count grows with page size: 20 rows become 21 queries, 100 rows become 101 queries, and a nested relation can become 1 + N + N before anyone notices.

That is why N+1 issues are so common in production. The code looks clean. Each individual query may use an index. Local test data is small. Then traffic grows, page sizes increase, database round trips multiply, and a harmless-looking endpoint starts consuming connection pool slots, database CPU, and latency budget.

For the broader database performance and correctness cluster, see the SQL And Data Correctness hub.

What N+1 Looks Like In An Endpoint

Imagine an orders endpoint that renders a list page:

orders(id, customer_id, created_at, total_cents)
customers(id, name, tier)
order_items(id, order_id, sku, quantity)

The page needs:

• the 50 most recent orders
• the customer name for each order
• the number of items in each order

The ORM code can look reasonable:

const orders = await prisma.order.findMany({
  orderBy: { createdAt: 'desc' },
  take: 50,
  select: {
    id: true,
    customerId: true,
    createdAt: true,
    totalCents: true,
  },
})

const rows = []

for (const order of orders) {
  const customer = await prisma.customer.findUnique({
    where: { id: order.customerId },
    select: { name: true },
  })

  const itemCount = await prisma.orderItem.count({
    where: { orderId: order.id },
  })

  rows.push({
    orderId: order.id,
    customerName: customer?.name ?? 'Unknown',
    itemCount,
    totalCents: order.totalCents,
  })
}

The code says "for each order, get the details I need." The database sees something else:

SELECT id, customer_id, created_at, total_cents
FROM orders
ORDER BY created_at DESC
LIMIT 50;

SELECT name FROM customers WHERE id = $1;
SELECT count(*) FROM order_items WHERE order_id = $2;

SELECT name FROM customers WHERE id = $3;
SELECT count(*) FROM order_items WHERE order_id = $4;

-- repeated for every returned order

That is not one query plus one relation query. It is one parent query, 50 customer queries, and 50 item-count queries.

For a page size of 50, the endpoint runs 101 queries. For a page size of 100, it runs 201. If another resolver loads shipping address, discount, or refund state inside the same loop, the query count grows again.

The important point is not that ORMs are bad. The problem is that object access hides query shape. A line that looks like a local property read can be a network round trip to the database.

Why N+1 Hides In Local Development

N+1 usually survives code review because the local version feels fine.

With five seed records, six or eleven queries may not look alarming. Each query is simple. The database is on a developer machine or a nearby test instance. The endpoint returns quickly. The pull request reviewer reads the application logic, not the generated SQL.

Production changes the shape:

N+1 is often discovered late because total request duration is a weak early signal. The first version may still fit inside the latency budget. The better signal is query count per request.

An endpoint that lists 50 orders should not run a query count that changes linearly with those 50 rows. It should have a bounded query shape: one query for orders, one bounded query for customers, one bounded query for item counts, and perhaps one or two more for permissions or feature state.

Detect N+1 With Query Counts And SQL Fingerprints

The most useful N+1 detector is a query-count budget per request path.

For example:

In logs or tracing, N+1 usually appears as the same SQL fingerprint repeated many times:

GET /orders?limit=50
db.query count=101 duration=480ms

50x SELECT name FROM customers WHERE id = $1
50x SELECT count(*) FROM order_items WHERE order_id = $1

The fingerprints are more important than any single slow query. Each individual lookup may be fast. The endpoint is slow because it does too many small things.

Add the signal where you can:

• count SQL statements per HTTP request
• group SQL statements by normalized fingerprint
• record query count in APM spans
• log query count in non-production request traces
• add integration tests for important endpoint budgets

A simple regression test is often enough to stop the same bug from returning:

it('renders the order list without per-row relation queries', async () => {
  await seedOrders({ count: 50, withCustomers: true, withItems: true })

  const queryCounter = startQueryCounter(db)

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

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

The exact budget depends on the endpoint. The rule is that increasing the number of returned rows should not increase the number of relation queries one by one.

Read Generated SQL, Not Only ORM Code

Fixing N+1 requires looking at the SQL the ORM emits.

ORM relation APIs are useful, but they are abstractions over real query plans, joins, indexes, network round trips, and result shapes. The generated SQL tells you whether a fix reduced query count, whether it created a huge join, and whether the database can use indexes on the foreign keys involved.

PostgreSQL's EXPLAIN documentation describes how EXPLAIN shows the execution plan for a statement, including scan and join choices. See PostgreSQL: EXPLAIN. That matters after you collapse many tiny queries into one or a few larger queries: fewer queries is usually better, but the new query still needs a sensible plan.

Use two levels of evidence:

1. Query count for the request.
2. Query plan for the heavier remaining statements.

If the query count drops from 101 to 3 but the new relation query scans a large table without a useful index, you have fixed one problem and exposed another. That is still progress, but it is not the end of the performance work.

For the broader workflow of query plans, indexes, and rollout safety, see How to Find and Fix Slow SQL Queries in Production. If an index exists but the new query is still slow, the next diagnostic layer is covered in Why Database Indexes Didn't Fix Your Slow Query.

Fix 1: Eager Load The Relation Deliberately

The first fix is to make the relation load explicit.

In Prisma, relation queries can use include or nested select to return related data with the parent result. Prisma also documents relation load strategies that can use a database join or separate queries joined at the application level, depending on the strategy and database. See Prisma relation queries and Prisma select fields.

For the orders example, you can load only the fields the page needs:

const orders = await prisma.order.findMany({
  orderBy: { createdAt: 'desc' },
  take: 50,
  select: {
    id: true,
    createdAt: true,
    totalCents: true,
    customer: {
      select: {
        id: true,
        name: true,
        tier: true,
      },
    },
    items: {
      select: {
        id: true,
      },
    },
  },
})

return orders.map((order) => ({
  orderId: order.id,
  customerName: order.customer.name,
  customerTier: order.customer.tier,
  itemCount: order.items.length,
  totalCents: order.totalCents,
}))

This makes the query shape intentional. The endpoint no longer asks the database for each customer and each item count inside the loop.

Eager loading is a strong default when:

• the page always needs the related data
• the relation is small or bounded
• the selected columns are narrow
• pagination happens on the parent records first
• the generated SQL has a stable plan

It can hurt when:

• the relation is large and duplicates parent rows through a join
• the page only needs a count but loads every child row
• the selected relation includes large text or JSON columns
• the query joins several optional one-to-many relations at once
• the ORM creates SQL that is hard for the database to optimize

The practical rule is to eager load deliberately, not reflexively. Load the relation because the page needs it, select only the columns required, and inspect the generated SQL.

Fix 2: Replace Per-Row Loads With Batched IN Queries

Sometimes eager loading is not the best shape. The endpoint may need aggregate counts, the ORM may generate an awkward join, or different parts of the response may need separate relation data.

In that case, batch the related fetches yourself.

const orders = await prisma.order.findMany({
  orderBy: { createdAt: 'desc' },
  take: 50,
  select: {
    id: true,
    customerId: true,
    createdAt: true,
    totalCents: true,
  },
})

const customerIds = [...new Set(orders.map((order) => order.customerId))]
const orderIds = orders.map((order) => order.id)

const [customers, itemCounts] = await Promise.all([
  prisma.customer.findMany({
    where: { id: { in: customerIds } },
    select: { id: true, name: true, tier: true },
  }),
  prisma.orderItem.groupBy({
    by: ['orderId'],
    where: { orderId: { in: orderIds } },
    _count: { _all: true },
  }),
])

const customersById = new Map(customers.map((customer) => [customer.id, customer]))
const itemCountsByOrderId = new Map(itemCounts.map((item) => [item.orderId, item._count._all]))

return orders.map((order) => {
  const customer = customersById.get(order.customerId)

  return {
    orderId: order.id,
    customerName: customer?.name ?? 'Unknown',
    customerTier: customer?.tier ?? 'unknown',
    itemCount: itemCountsByOrderId.get(order.id) ?? 0,
    totalCents: order.totalCents,
  }
})

The endpoint now has a bounded query shape:

1. Fetch the parent orders.
2. Fetch all needed customers with WHERE id IN (...).
3. Fetch all needed item counts grouped by order_id.

That usually turns 101 queries into 3.

Batched loading has its own details:

• Deduplicate IDs before querying.
• Preserve parent order when mapping results back.
• Handle missing related rows deliberately.
• Chunk very large IN lists if the page can be large.
• Ensure foreign-key columns used for batching are indexed.
• Prefer grouped counts over loading full child collections when the page only needs counts.

This is often the cleanest fix for list endpoints because it keeps parent pagination clear and avoids joining more data than the response needs.

Fix 3: Use Request-Scoped Loaders For Resolver Layers

N+1 is especially common in GraphQL resolvers and service layers where each field resolver is intentionally small.

A resolver like this composes nicely but can query once per row:

const resolvers = {
  Order: {
    customer: (order, _args, ctx) => {
      return ctx.db.customer.findUnique({
        where: { id: order.customerId },
      })
    },
  },
}

If a GraphQL query returns 50 orders and asks for customer, that resolver can run 50 customer lookups.

A request-scoped loader keeps resolver composition while batching keys:

function createLoaders(db: Database) {
  return {
    customerById: new DataLoader(async (ids: readonly string[]) => {
      const customers = await db.customer.findMany({
        where: { id: { in: [...ids] } },
      })

      const customersById = new Map(customers.map((customer) => [customer.id, customer]))

      return ids.map((id) => customersById.get(id) ?? null)
    }),
  }
}

const resolvers = {
  Order: {
    customer: (order, _args, ctx) => {
      return ctx.loaders.customerById.load(order.customerId)
    },
  },
}

The DataLoader README describes batching as its primary feature and says loaders coalesce individual loads before calling the batch function. It also warns that caches should be per request when different users may see different data. See GraphQL DataLoader.

That per-request detail is not optional. A long-lived loader can leak stale or unauthorized data across users. The loader should live for one request, use the current request's auth context, and disappear when the request finishes.

Fix 4: Use A Read Shape Instead Of An Object Graph

Sometimes the best N+1 fix is not another relation-loading option. It is changing the read shape.

List pages often do not need full domain objects. They need cards, rows, or summaries:

• order id
• customer display name
• total amount
• item count
• status
• created date

That can be a focused SQL query, a database view, a materialized summary, or a read-model table maintained by the write path. The right choice depends on freshness needs, write volume, and operational complexity.

For example, a list endpoint can read from an order summary shape:

SELECT
  o.id,
  o.created_at,
  o.total_cents,
  c.name AS customer_name,
  count(oi.id) AS item_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= $1
GROUP BY o.id, c.name
ORDER BY o.created_at DESC
LIMIT 50;

That may be clearer than asking the ORM to hydrate a graph and then throwing most of it away.

The trade-off is ownership. A handcrafted read query or summary table is another contract to maintain. Use it when the endpoint is important enough and the generic object graph keeps producing poor query shape.

Mistakes That Make N+1 Fixes Weaker

The common N+1 fixes can still produce weak results if they are applied mechanically.

Read replicas are a common distraction here. If an endpoint sends 100 repeated reads to a replica, the primary may look better, but the product still pays for 100 round trips and the database tier still does unnecessary work. That failure mode is related to Why Read Replicas Didn't Reduce Database Load.

Caching can be useful too, but it should not be the first explanation for a bad query shape. If the uncached endpoint runs 101 queries for 50 rows, the source problem is still in the access pattern.

A Practical Rollout Plan

Treat an N+1 fix like a small production performance change, not just a code cleanup.

1. Pick one endpoint with real traffic or clear search/product value.
2. Capture baseline query count, query fingerprints, p95 latency, DB time, and connection pool waits.
3. Reproduce the endpoint with a realistic row count in staging or an integration test.
4. Choose one fix: eager loading, batched IN queries, request-scoped loaders, or a read-specific query.
5. Verify the new query count does not grow with returned rows.
6. Inspect the generated SQL for the remaining heavier statements.
7. Add a query-budget regression test for the endpoint.
8. Deploy gradually if the endpoint is hot.
9. Compare before and after metrics over the same traffic shape.

The regression test is the part teams often skip. Without it, N+1 usually returns during a future feature change when someone adds a new relation to the response.

If the endpoint runs inside a write flow or transaction, also check transaction boundaries. Fewer queries can reduce lock duration, but the transaction still needs to be scoped correctly. That adjacent problem is covered in Database Transaction Boundaries in Backend APIs.

The Short Version

N+1 is not a correctness bug. It is a query-shape bug.

The endpoint asks for a list, then repeats relation queries per row. It works locally because the data is small and each individual query is fast. It fails in production because query count grows with result size and concurrency.

The fix is to make data access explicit:

• measure query count per request
• look for repeated SQL fingerprints
• eager load small required relations
• batch relation reads with IN queries
• use request-scoped loaders for resolver layers
• use read-specific shapes for important list endpoints
• add a query-budget test so the problem does not return

Once the query count is bounded, the remaining SQL performance work becomes easier to reason about. You are no longer fighting a hidden loop. You are looking at a small number of intentional queries and deciding whether their plans, indexes, and payloads match the endpoint's job.