N+1 Query Problem

The Problem: One Page, 21 Queries

The "My Orders" page in our food delivery app shows the user's last 20 orders, each with the restaurant name. A natural first version:

11. Get the user's 20 orders                  → 1 query
22. For each order, get its restaurant name   → 20 queries
3                                     Total   = 21 queries

This is the N+1 query problem: 1 query to get a list, then N more queries, one for each item in the list.

Think of shopping with a list of 20 items and driving to the store separately for each one. The shopping takes seconds; the 20 trips take all afternoon. One trip with the full list is the fix.

// N+1: one query for orders, then one per order
const orders = (await pool.query(
  'SELECT id, restaurant_id, amount FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20',
  [userId]
)).rows;

for (const order of orders) {
  const r = await pool.query('SELECT name FROM restaurants WHERE id = $1', [order.restaurant_id]);
  order.restaurantName = r.rows[0].name;          // 20 extra queries
}

Why It Hurts

Each query is a round trip: the request travels to the database and the answer travels back. Even fast queries add up:

11 round trip ≈ 2 ms (network + query)
2 
320 orders  → 21 queries × 2 ms =  42 ms
4100 orders → 101 queries × 2 ms = 202 ms

It also holds a pool connection for the whole loop and multiplies database load. At 2,000 page views per second, 21 queries each means 42,000 queries per second instead of 2,000.

The bug hides easily. With 3 test orders on your laptop, it feels instant. It only shows up with real data.

In Java, it's even more hidden. With lazy loading (load related data only when the code touches it), order.getRestaurant().getName() looks like a normal getter but quietly runs a query.

Fix 1: One Query With a JOIN

Ask the database for orders and restaurant names together:

const orders = (await pool.query(
  'SELECT o.id, o.amount, r.name AS restaurant_name ' +
  'FROM orders o JOIN restaurants r ON r.id = o.restaurant_id ' +
  'WHERE o.user_id = $1 ORDER BY o.created_at DESC LIMIT 20',
  [userId]
)).rows;                                          // 1 query total

Fix 2: Batch Into Two Queries

When a JOIN isn't possible (data in another service or collection), collect all the ids and fetch them in one query with IN / $in:

11. Get 20 orders                              → 1 query
22. Collect their 20 restaurant ids
33. Get all 20 restaurants in one query         → 1 query
44. Match them up in code
5                                      Total    = 2 queries (not 21)

const orders = (await pool.query(
  'SELECT id, restaurant_id, amount FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20',
  [userId]
)).rows;

const ids = [...new Set(orders.map(o => o.restaurant_id))];
const restaurants = (await pool.query(
  'SELECT id, name FROM restaurants WHERE id = ANY($1)',    // one query for all ids
  [ids]
)).rows;

const nameById = new Map(restaurants.map(r => [r.id, r.name]));
orders.forEach(o => { o.restaurantName = nameById.get(o.restaurant_id); });

Which Fix to Use

How to Spot It

• Log queries and count them per request. A number that grows with list size is N+1.
• Node.js: mongoose.set('debug', true); for pg, log inside a wrapper around pool.query.
• Java: spring.jpa.show-sql=true, or Hibernate statistics.
• In production, APM tools show the same query repeated many times in one request.

Interview Angle

Common mistakes:

• Thinking populate runs a query per document. It batches into one $in query per path.
• Fixing N+1 with FetchType.EAGER. It still loads relations with separate queries in many cases, and now always loads them, even when not needed.

Summary

1N+1         → 1 query for a list + 1 query per item
2Cost        → 21 round trips instead of 1, grows with list size
3Hidden in   → loops, lazy loading (JPA getters)
4Fix 1       → JOIN / JOIN FETCH / $lookup → 1 query
5Fix 2       → batch ids with IN / $in / populate → 2 queries
6Fix 3       → embed data read together (MongoDB)
7Detect      → count queries per request

Fewer queries helps, but it matters just as much that the queries we send are safe. Next, SQL and NoSQL injection shows how user input can turn into an attack.