The N+1 Query Problem: Why Your API Might Be Making 101 Database Queries Instead of 2

If your API feels fast in development but becomes painfully slow in production as your data grows, there's a good chance you're looking at the N+1 query problem. It's one of the most common backend performance issues, and the frustrating part is that the code usually looks completely fine at first glance.

The Setup

Say you have two tables: customers and orders. Each order belongs to a customer through a foreign key.

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC(10, 2)
);

Here's what that relationship looks like. Each row in orders points back to exactly one row in customers through customer_id, while a single customer can have many orders pointing at them. That's a classic one to many relationship.

erDiagram
    customers ||--o{ orders : "has many"
    customers {
        int id PK
        varchar name
    }
    orders {
        int id PK
        int customer_id FK
        numeric total
    }

You can also picture it as two flat tables with the foreign key drawing the line between them:

customers                      orders
+----+---------+               +----+-------------+--------+
| id | name    |               | id | customer_id | total  |
+----+---------+               +----+-------------+--------+
| 1  | Sarah   |     <---------| 1  | 1           | 42.00  |
| 2  | James   |     <---+     | 2  | 1           | 15.50  |
| 3  | Emily   |     <-+ \-----| 3  | 2           | 99.99  |
+----+---------+       \-------| 4  | 3           | 27.30  |
                                +----+-------------+--------+

Every arrow is orders.customer_id referencing customers.id. That's the foreign key. It's what makes it possible to reconstruct "this customer, with these orders" without storing the order data twice.

Now say you want to return every customer along with all the orders they've placed. A common first pass looks something like this (pseudocode, but it maps closely to a naive Prisma or ActiveRecord setup):

const customers = await db.query('SELECT * FROM customers');

for (const customer of customers) {
  customer.orders = await db.query(
    'SELECT * FROM orders WHERE customer_id = ?',
    [customer.id]
  );
}

Why It's Called N+1

This looks reasonable. You get all the customers, then loop through and grab each one's orders. But look at what's actually hitting the database:

1. One query to fetch all customers
2. One additional query per customer to fetch that customer's orders

If you have 100 customers, that's 1 query for the customers plus 100 queries for their orders: 101 total queries. Hence "N+1," where N is the number of rows from the first query.

sequenceDiagram
    participant App
    participant DB

    App->>DB: SELECT * FROM customers
    DB-->>App: 100 customers

    loop for each of the 100 customers
        App->>DB: SELECT * FROM orders WHERE customer_id = ?
        DB-->>App: orders for that customer
    end

    Note over App,DB: Total: 1 + 100 = 101 queries

This scales linearly with your data, and not in a good way. At 100 customers, 101 queries might not even show up as a bottleneck. At 100,000 customers, that's 100,001 queries per request, and your database (and your server) will not thank you for it. If this endpoint is something like an admin dashboard or a customer list page that gets hit often, that cost repeats on every single request.

The Fix: A Single SQL JOIN

Instead of fetching customers and orders separately, you can pull both in one query using a JOIN:

SELECT
  customers.id,
  customers.name,
  orders.id AS order_id,
  orders.total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

This returns every customer paired with each of their orders in a single result set, one round trip to the database instead of N+1.

sequenceDiagram
    participant App
    participant DB

    App->>DB: SELECT customers.*, orders.* FROM customers LEFT JOIN orders ON orders.customer_id = customers.id
    DB-->>App: all customers with their orders, in one result set

    Note over App,DB: Total: 1 query, regardless of customer count

Your application code then just needs to group the flat rows back into a nested structure (customers with an orders array), which is a cheap in-memory operation compared to hundreds of network round trips to the database.

How Different Frameworks Solve This

Every major ORM has a built-in way to do this join for you, they just name it differently:

Framework	Feature name	Example
Django	select_related / prefetch_related	Customer.objects.prefetch_related('orders')
Laravel (Eloquent)	Eager loading	Customer::with('orders')->get()
Prisma	include	prisma.customer.findMany({ include: { orders: true } })
Rails (ActiveRecord)	Eager loading	Customer.includes(:orders)

Under the hood, they're all doing roughly the same thing: either issuing a single JOIN query, or batching the "get related records" step into one WHERE customer_id IN (...) query instead of N separate ones. Either approach collapses N+1 queries down to 1 or 2.

How to Catch This in Your Own Code

The tricky part about N+1 is that the code reads fine. The problem only shows up when you look at what's actually hitting the database. A few ways to catch it:

• Turn on query logging in development and count how many queries a single request generates. If that number scales with the size of your dataset, that's the smell. A "get all customers" endpoint that fires 1 query with 5 test customers and 51 queries with 50 customers is the tell.
• Most ORMs have a debug or profiling mode (Prisma's query logging, Laravel Debugbar, Django Debug Toolbar) that will show you the exact query count per request.
• Load test with realistic data volumes, not 5 rows in a dev database. N+1 is invisible at small scale and brutal at large scale.

It Gets Worse With Nested Relationships

The two table example is the simplest case, but N+1 compounds fast once you add another level of relationships. Say each order also has line items:

const customers = await db.query('SELECT * FROM customers');

for (const customer of customers) {
  customer.orders = await db.query(
    'SELECT * FROM orders WHERE customer_id = ?',
    [customer.id]
  );

  for (const order of customer.orders) {
    order.items = await db.query(
      'SELECT * FROM order_items WHERE order_id = ?',
      [order.id]
    );
  }
}

Now you're not just paying N+1, you're paying N (customers) plus M (orders per customer) queries, nested inside each other. With 100 customers averaging 5 orders each, that's 1 query for customers, 100 queries for orders, and 500 more queries for order items: 601 queries for what should be 3.

This is exactly the shape of bug that shows up in GraphQL resolvers too. A resolver for orders that runs a query per parent customer, and a resolver for items that runs a query per parent order, will happily reproduce N+1 at every level of the graph unless something batches those calls. This is why libraries like dataloader exist in the GraphQL/Node ecosystem: they collect all the individual "get orders for customer X" calls that fire within a single tick, merge them into one WHERE customer_id IN (1, 2, 3, ...) query, and hand each caller back its slice of the result.

A Rough Sense of the Cost

Query count is only part of the story, latency per query matters too. If a single query takes even 2ms round trip (a reasonable number for a local or same region database), the difference looks like this as your customer table grows:

Customers	N+1 queries	Approx. time at 2ms/query	Join queries	Approx. time
100	101	~200ms	1	~2ms
1,000	1,001	~2s	1	~2ms
10,000	10,001	~20s	1	~2ms
100,000	100,001	~200s	1	~2ms

Those numbers are simplified (real databases pipeline and cache some of this), but the trend is the real point: N+1 turns your response time into a function of your data size, while a JOIN keeps it roughly constant regardless of how many customers you have. For something like an order history page or a checkout flow, that's the difference between a page that loads instantly and one that times out under real traffic.

Don't Forget the Index

A JOIN only stays fast if the foreign key column is indexed. Without an index on orders.customer_id, the database has to scan the entire orders table for every customer it's joining against, which can turn your one query fix into a slow one query fix on large tables.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Most ORMs create this index automatically when you define a foreign key relationship, but it's worth checking on tables that were set up by hand or migrated from another system, since a missing index here is a common reason a JOIN based fix still feels slow.

Takeaway

N+1 is one of those bugs that doesn't look like a bug. The code is readable, it passes tests, and it works fine with a handful of records. It's only when your data grows that the query count explodes and starts dragging down response times. The fix is almost always the same: replace the loop of individual queries with a single JOIN or your ORM's eager loading feature, and let the database do the work it's actually good at.