N+1 Query Problem

Tiny Summary

The N+1 query problem happens when you fetch a list of N items, then make 1 additional query for each item's related data. Instead of 1 query, you make N+1 queries. For 100 items, that's 101 queries instead of 2. This kills performance.

---

The Problem

You're displaying a list of 10 blog posts with their authors.

Bad code (N+1 queries):

# Query 1: Get all posts
posts = db.query("SELECT * FROM posts LIMIT 10")

for post in posts:
    # Query 2, 3, 4... 11: Get author for each post
    author = db.query(f"SELECT * FROM users WHERE id = {post.author_id}")
    print(f"{post.title} by {author.name}")

Queries executed:

1. SELECT * FROM posts LIMIT 10              (1 query)
2. SELECT * FROM users WHERE id = 1          (post 1's author)
3. SELECT * FROM users WHERE id = 2          (post 2's author)
4. SELECT * FROM users WHERE id = 3          (post 3's author)
... (8 more queries)
11. SELECT * FROM users WHERE id = 10         (post 10's author)

Total: 11 queries for 10 posts
Time: ~110ms (10ms per query × 11 queries)

Good code (2 queries):

# Query 1: Get all posts
posts = db.query("SELECT * FROM posts LIMIT 10")

# Query 2: Get all authors at once
author_ids = [post.author_id for post in posts]
authors = db.query(f"SELECT * FROM users WHERE id IN ({author_ids})")
author_map = {author.id: author for author in authors}

for post in posts:
    author = author_map[post.author_id]
    print(f"{post.title} by {author.name}")

Queries executed:

1. SELECT * FROM posts LIMIT 10
2. SELECT * FROM users WHERE id IN (1,2,3,4,5,6,7,8,9,10)

Total: 2 queries for 10 posts
Time: ~20ms (10ms per query × 2 queries)

5x faster!

---

At Scale, It's Catastrophic

100 blog posts:

• N+1: 101 queries, ~1 second
• Fixed: 2 queries, ~20ms

1000 blog posts:

• N+1: 1001 queries, ~10 seconds (page times out)
• Fixed: 2 queries, ~20ms

The pattern doesn't scale. At all.

---

Real Examples

Example 1: E-commerce Product List

# BAD: N+1 queries
products = db.query("SELECT * FROM products LIMIT 50")

for product in products:
    category = db.query(f"SELECT * FROM categories WHERE id = {product.category_id}")
    reviews = db.query(f"SELECT AVG(rating) FROM reviews WHERE product_id = {product.id}")
    inventory = db.query(f"SELECT quantity FROM inventory WHERE product_id = {product.id}")

# 50 products × 3 queries each + 1 = 151 queries!

# GOOD: Eager loading
products = db.query("SELECT * FROM products LIMIT 50")
product_ids = [p.id for p in products]

categories = db.query(f"SELECT * FROM categories WHERE id IN ({category_ids})")
reviews = db.query(f"SELECT product_id, AVG(rating) FROM reviews WHERE product_id IN ({product_ids}) GROUP BY product_id")
inventory = db.query(f"SELECT product_id, quantity FROM inventory WHERE product_id IN ({product_ids})")

# 4 queries total, regardless of product count

Example 2: Social Feed

// BAD: N+1 queries
const posts = await db.query('SELECT * FROM posts LIMIT 20');

for (const post of posts) {
  const user = await db.query(`SELECT * FROM users WHERE id = ${post.user_id}`);
  const likes = await db.query(`SELECT COUNT(*) FROM likes WHERE post_id = ${post.id}`);
  const comments = await db.query(`SELECT COUNT(*) FROM comments WHERE post_id = ${post.id}`);
}

// 20 posts × 3 queries + 1 = 61 queries

// GOOD: Batch queries
const posts = await db.query('SELECT * FROM posts LIMIT 20');
const postIds = posts.map(p => p.id);
const userIds = posts.map(p => p.user_id);

const users = await db.query(`SELECT * FROM users WHERE id IN (${userIds})`);
const likes = await db.query(`SELECT post_id, COUNT(*) FROM likes WHERE post_id IN (${postIds}) GROUP BY post_id`);
const comments = await db.query(`SELECT post_id, COUNT(*) FROM comments WHERE post_id IN (${postIds}) GROUP BY post_id`);

// 4 queries total

---

How ORMs Hide This Problem

ORMs (Object-Relational Mappers) make N+1 queries invisible.

Django (bad):

# Looks innocent, actually terrible
posts = Post.objects.all()[:10]

for post in posts:
    print(post.author.name)  # Hidden query for EACH post!

Django (good):

# Eager load related data
posts = Post.objects.select_related('author').all()[:10]

for post in posts:
    print(post.author.name)  # No extra queries, data already loaded

Rails (bad):

# N+1 queries
posts = Post.limit(10)
posts.each do |post|
  puts post.author.name  # Query for each author
end

Rails (good):

# Eager loading
posts = Post.includes(:author).limit(10)
posts.each do |post|
  puts post.author.name  # No extra queries
end

---

How to Detect N+1 Queries

1. Enable Query Logging

Django:

# settings.py
LOGGING = {
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
        },
    },
}

# Now see all queries in console

Rails:

# Development mode shows queries automatically
# Look for repeated similar queries

2. Use N+1 Detection Tools

Python: django-debug-toolbar

Shows all queries for each page, highlights duplicates.

Ruby: bullet gem

# Gemfile
gem 'bullet'

# Alerts you in development when N+1 detected

Node.js: sequelize logging

const sequelize = new Sequelize({
  logging: console.log  // See all queries
});

3. Monitor Query Counts

If loading 10 items triggers >10 queries, you probably have N+1 problem.

---

Solutions by Framework

Django

# select_related: For ForeignKey, OneToOne (joins tables)
posts = Post.objects.select_related('author', 'category').all()

# prefetch_related: For ManyToMany, reverse ForeignKey (separate queries, batched)
posts = Post.objects.prefetch_related('tags', 'comments').all()

# Both together:
posts = Post.objects.select_related('author').prefetch_related('tags').all()

Rails

# includes: Eager load associations
posts = Post.includes(:author, :tags).limit(10)

# joins: SQL JOIN (use when filtering by association)
posts = Post.joins(:author).where(authors: { verified: true })

Node.js (Sequelize)

// include: Eager load associations
const posts = await Post.findAll({
  include: [
    { model: User, as: 'author' },
    { model: Tag }
  ],
  limit: 10
});

GraphQL (DataLoader)

// DataLoader batches requests automatically
const userLoader = new DataLoader(async (userIds) => {
  const users = await db.query(`SELECT * FROM users WHERE id IN (${userIds})`);
  return userIds.map(id => users.find(u => u.id === id));
});

// In resolver:
posts.map(post => userLoader.load(post.author_id));
// All author queries batched into 1 query automatically!

---

When Eager Loading Backfires

Over-fetching:

# Loading 1000 posts with 10 tags each = 10,000 rows returned
posts = Post.objects.prefetch_related('tags').all()[:1000]

# If you only need post titles, this wastes memory
# Better: Only load what you need
posts = Post.objects.only('id', 'title').all()[:1000]

Unused data:

# Loading authors but never using them = wasted query
posts = Post.objects.select_related('author').all()

for post in posts:
    print(post.title)  # Never use post.author!

Don't eager load data you don't use. Only optimize queries you actually make.

---

Key Insights

N+1 queries are the most common performance killer in web apps. One loop can trigger hundreds of queries.

ORMs hide the problem by making queries look like attribute access. Always check what queries your ORM generates.

Fix it by batching: Fetch all related data in 1-2 queries instead of N queries. Use your ORM's eager loading features.

Use the simulation to see how N+1 queries explode as list size grows!