N+1 Query Prevention

Status: Complete
Category: Performance
Default enforcement: Soft
Author: PushBackLog team

Tags

• Topic: performance, quality
• Skillset: backend, data-engineering
• Technology: generic
• Stage: execution, review

Summary

The N+1 query problem occurs when an application executes one query to fetch a list of records and then one additional query per record to fetch related data, resulting in N+1 total queries. This is one of the most common causes of silent, severe performance degradation in data-driven applications.

Rationale

Invisible at small scale, catastrophic at large scale

N+1 queries are particularly insidious because they appear to work correctly during development and early production. With 10 orders in a test database, 11 queries take milliseconds. With 500 orders in production, 501 queries add seconds to a page load. The code hasn’t changed — only the data volume.

The N+1 problem is endemic to ORM-based applications because ORMs make relationship traversal look cheap in code (order.author.name) while silently firing a database query behind the property access. Without query logging enabled in development, developers build N+1 patterns without knowing.

The database round-trip cost

At the heart of the N+1 problem is the cost of a database round-trip. Even on a well-tuned local network, each round-trip carries latency. 500 round-trips at 1ms each is 500ms of serialised waiting. This cannot be addressed by tuning the queries themselves — the solution is to reduce the number of queries.

Guidance

Detection

The first step is visibility. Enable query logging in development and staging:

• Prisma: set log: ['query'] in the client options
• Sequelize: logging: console.log option
• ActiveRecord (Rails): Bullet gem
• Django: Django Debug Toolbar
• Hibernate: spring.jpa.show-sql=true
• Generic: count queries on suspicious endpoints; any endpoint firing 10+ queries for a single page is a candidate

Solutions

Eager loading with an ORM

// N+1: one query for orders, then one per order for the author
const orders = await Order.findAll();
for (const order of orders) {
  console.log(order.author.name); // Fires a DB query each time
}

// Fixed: single JOIN query
const orders = await Order.findAll({
  include: [{ model: User, as: 'author', attributes: ['name'] }]
});

DataLoader pattern (GraphQL / batching)

import DataLoader from 'dataloader';

const userLoader = new DataLoader(async (userIds: string[]) => {
  // All IDs collected within a tick — fetched in ONE query
  const users = await User.findAll({ where: { id: userIds } });
  return userIds.map(id => users.find(u => u.id === id));
});

// Even when called from N resolvers, userLoader deduplicates and batches
const author = await userLoader.load(order.authorId);

Examples

Before: N+1 in a REST endpoint

// GET /api/orders — fetches orders then N author queries
app.get('/api/orders', async (req, res) => {
  const orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [req.user.id]);
  const result = [];
  for (const order of orders) {
    // A separate DB query for each order
    const product = await db.query('SELECT name, price FROM products WHERE id = $1', [order.product_id]);
    result.push({ ...order, product: product.rows[0] });
  }
  res.json(result);
});
// For 100 orders: 101 queries

After: single JOIN query

app.get('/api/orders', async (req, res) => {
  const orders = await db.query(`
    SELECT o.*, p.name AS product_name, p.price AS product_price
    FROM orders o
    JOIN products p ON p.id = o.product_id
    WHERE o.user_id = $1
  `, [req.user.id]);
  // 1 query regardless of order count
  res.json(orders.rows);
});

Anti-patterns

1. Accessing related properties in a loop without eager loading

for (const order of orders) {
  console.log(order.customer.address); // N queries if customer is not eagerly loaded
}

2. Trusting the ORM to do the right thing by default

Most ORMs default to lazy loading (Hibernate, ActiveRecord, Sequelize). Lazy loading is convenient but almost always wrong for list views. Default to explicit eager loading declarations.

3. Fixing N+1 with caching

Caching each individual query result reduces the damage but doesn’t fix the underlying problem. Fix the query; use caching for other optimisation goals.

4. Not monitoring query count in development

Without query count instrumentation, N+1 patterns are invisible during development. Add query logging or a tool like Bullet/Django Debug Toolbar as a standard development practice.

Related practices

• Caching Strategy
• Async Patterns

Part of the PushBackLog Best Practices Library. Suggest improvements →