N+1 Query Pattern That Collapsed Under Load

An ORM-generated N+1 query pattern that ran fine in staging (10 records) caused 8,400 database queries per user request in production.

Overview

An orders endpoint that looked fine on average fired 1,681 SQL queries for a single power user, and three such requests at once drained the connection pool for everyone.

What happened

The endpoint listed orders with their products and customers. With lazy loading on by default, every association access became its own SELECT.

How it spread

The connection pool was sized for 20 concurrent connections. A handful of heavy requests was enough to starve every other request.

Why nobody saw it

Development and staging — Test data had at most 10-20 orders per user
Production average latency — Most users had fewer than 20 orders
Production tail latency — p99 and p99.9 showed it, but nothing alerted on them

Impact

Orders for one power user — 840
Queries for that one request — 1,681
Queries for 3 concurrent power-user requests — 5,043
Connection pool size — 20

The fix

Association loading — Lazy, one SELECT per order per association — Loaded for the whole list at once
Test data — 10-20 orders per user — Production-scale sizes
Alerting — Average latency — p99 and p99.9 as well

> [!WARNING] Averages hide N+1 problems. The users with the most data feel them first, and they sit in the tail.

Key concepts

N+1 query problem: Fetching N items then issuing one additional query per item, resulting in N+1 total queries instead of 1-2 optimized queries using JOINs or eager loading.
Eager loading: Loading associated records in the same query or a bounded number of additional queries using JOINs or IN clauses, rather than fetching one at a time.
Query batching: Collecting multiple individual queries and executing them as a single batch query using WHERE id IN (...), reducing N queries to 1.
Percentile latency monitoring: Tracking p95, p99, and p99.9 latency rather than averages — average latency can look healthy while a small percentage of requests are extremely slow.

When to use it

Discussing ORM best practices and eager loading
Explaining why production performance testing requires production-scale data
Justifying SQL query logging and slow-query monitoring

Interview questions

How do you detect and fix N+1 query problems in an ORM-based application?