Skip to content

N+1 query problem: excessive queries in loop

Quick summary

You fetch N records then issue one query per record to load related data — this results in N+1 queries instead of one join.

Why this happens

  • Related data is loaded lazily one record at a time inside a loop over the parent records

  • An ORM lazy-loading configuration triggers a separate query for each related entity access

  • The developer did not use a join or batch loading strategy for the related data

Minimal example

✗ Broken code
const users = await db.query('SELECT * FROM users');
for (const user of users) {
  user.orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [user.id]); // N queries!
}
✓ Fixed code
const users = await db.query('SELECT * FROM users');
const orders = await db.query('SELECT * FROM orders WHERE user_id = ANY($1)', [users.map(u => u.id)]);
// Group orders by user_id and assign
const ordersByUser = {};
for (const order of orders) { (ordersByUser[order.user_id] ||= []).push(order); }
for (const user of users) { user.orders = ordersByUser[user.id] || []; }

Batching the related data fetch into a single query with WHERE user_id IN (...) reduces N+1 queries to just 2 queries total.

How to diagnose

  • Enable query logging and count the number of queries executed for a typical request

  • Look for query patterns where one query is followed by N queries for related data

  • Use a query profiler or APM tool to detect N+1 patterns automatically

How to fix

  • Use a JOIN to fetch parent and related data in a single query

  • Use eager loading or prefetch directives in the ORM to load related data in bulk

  • Batch the related data fetch: collect all foreign keys, then query with WHERE id IN (...)

How to prevent

  • Use eager loading or joins for related data that is always needed with the parent records

  • Monitor query counts in development and test for N+1 patterns before deployment

Related Resources

Related Glossary

Related Lessons

← Back to language