Course contentsShow
← Software Engineering
Lesson 883 of 1,93327. Database Design in PracticeFree lesson

Avoiding N+1 Query Problems

Identify and fix N+1 query antipatterns where loops execute separate queries instead of using joins or batching.

Avoiding N+1 Query Problems

What you'll learn: You'll learn to identify and eliminate the N+1 query antipattern where loops execute separate database queries instead of fetching related data efficiently.

The Problem

The N+1 query problem occurs when your code fetches a list of N records, then loops through them executing one additional query per record to fetch related data. That's 1 initial query + N follow-up queries = N+1 total queries.

Example scenario: You fetch 100 users, then loop through them to fetch each user's posts individually. That's 101 database round-trips when you could have done it in 1 or 2.

Why It Happens

This antipattern emerges when developers think about data one record at a time rather than in sets. It's especially common with ORMs that make it easy to access relationships (like user.posts) without realizing each access triggers a new query.

# BAD: N+1 queries
users = get_all_users()  # 1 query
for user in users:
    posts = get_posts_for_user(user.id)  # N queries (one per user)
    print(f"{user.name}: {len(posts)} posts")

The Solution

Use joins or batch loading to fetch all related data upfront:

# GOOD: 1 or 2 queries
users_with_posts = get_users_with_posts_joined()  # 1 query with JOIN
# OR
users = get_all_users()  # 1 query
all_posts = get_posts_for_user_ids([u.id for u in users])  # 1 batched query

The first query might use a SQL JOIN. The second approach batches IDs into a single WHERE user_id IN (...) query—this is what the DataLoader pattern does.

Detection

Check your execution plans. If a query inside a loop shows up hundreds of times with different parameters, you've found an N+1 problem.

Key Takeaway: Always fetch related data in bulk using joins or batched queries instead of executing separate queries inside loops—turn N+1 queries into 1 or 2 queries.