Hunt Down N+1 Queries

Performance Engineering · Part 2

The N+1 query is the most common performance bug in web apps, and one of the sneakiest: it runs fine with ten records in dev and falls over at ten thousand in production, because it fires one database query per row instead of one for the whole set. This walks through spotting N+1 queries, fixing them with a join or a bulk fetch, and keeping them from creeping back.

N+1: one query to fetch a list; N more to fetch related data for each item. Common; deadly at scale.

Step 1: The Pattern (5 min)

# 1 query: get all users
users = User.query.all()

# N queries: get each user's posts
for user in users:
    posts = Post.query.filter_by(user_id=user.id).all()
    print(f"{user.name}: {len(posts)} posts")

100 users = 101 queries. Each query has latency. Total time is N × latency, not just N rows × bytes.

Step 2: Detect via Logging (5 min)

Enable SQL logging in dev:

Django:

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

SQLAlchemy:

engine = create_engine("...", echo=True)

Watch the log when you hit the endpoint. Identical query repeated = N+1.

Step 3: Detect via Profile (5 min)

In a flame graph:

• One DB call function appears thousands of times

• Each is fast (small bar)

• Together they dominate

That's N+1.

Step 4: Fix With JOIN (10 min)

Django:

users = User.objects.prefetch_related('posts').all()
for user in users:
    posts = user.posts.all()  # Already loaded; no query

SQLAlchemy:

users = session.query(User).options(joinedload(User.posts)).all()

ORM does one query (or two — list + bulk fetch) instead of N+1.

Step 5: Bulk Fetch (10 min)

If ORM doesn't help, do it manually:

users = User.query.all()
user_ids = [u.id for u in users]

# One query for all posts
posts = Post.query.filter(Post.user_id.in_(user_ids)).all()

# Group by user
posts_by_user = {}
for post in posts:
    posts_by_user.setdefault(post.user_id, []).append(post)

# Display
for user in users:
    print(f"{user.name}: {len(posts_by_user.get(user.id, []))} posts")

2 queries total. Worth it.

Step 6: GraphQL DataLoader Pattern (10 min)

For GraphQL or APIs with deep nesting:

class PostLoader:
    def __init__(self):
        self.pending = []
        self.results = {}
    
    def load(self, user_id):
        self.pending.append(user_id)
        # Return promise; resolves after batch
    
    def dispatch(self):
        if not self.pending:
            return
        posts = Post.query.filter(Post.user_id.in_(self.pending)).all()
        for post in posts:
            self.results.setdefault(post.user_id, []).append(post)
        self.pending = []

Calls collect IDs; batch executes; resolves all at once. The DataLoader pattern.

Step 7: Lazy Loading Considered (10 min)

Lazy loading caused most N+1s. Patterns:

# Lazy (causes N+1 if iterated)
class User:
    posts = relationship("Post", lazy="select")  # SQL on access

# Eager (one extra query upfront)
class User:
    posts = relationship("Post", lazy="joined")

Default to lazy; opt into eager when you know you need the relation.

But explicit prefetch_related per query is usually safer than changing defaults.

Step 8: Watch Out For Hidden N+1 (10 min)

ORM properties hide it:

@property
def total_value(self):
    return sum(item.price for item in self.items)
    # Each item.price triggers a query?

If item.price is lazy-loaded from a related table — yes, N+1.

Audit: when you write a property that uses ORM relationships, ask whether the loading is efficient.

Step 9: Test Performance Regressions (15 min)

def test_endpoint_query_count():
    with db.capture_queries() as queries:
        client.get('/users')
    assert len(queries) < 5

CI catches N+1 regressions before they ship.

Various libraries (Django-debug-toolbar, query-counter) help.

Step 10: When N+1 Is Fine (5 min)

If N is small (< 5), the overhead is minimal. Don't optimize prematurely.

The pattern bites when:

• N is large (hundreds, thousands)

• Latency to DB is high (network)

• Endpoint is hot (called often)

For internal admin tools with low traffic, simple N+1 may be acceptable.

What You Just Did

You can detect, diagnose, and fix N+1 queries. The single biggest source of DB performance issues.

Common Failure Modes

Lazy by default for everything. N+1 lurking in every endpoint.

Fixing without measuring. "Just in case" prefetching that's actually slower.

Ignoring N+1 in tests. No regression catch.

ORM hides the queries. Don't realize what's happening.

N+1 in property accessors. Looks innocent; actually slow.

---

Continue the Performance Engineering path

• Previous — Part 1: Profile a Slow Endpoint

• Next — Part 3: Add an Index That Works

Part of the Performance Engineering learning path.