Scaling Databases & Data Consistency · Finding the Bottleneck
N+1 Queries and ORM Query Explosions
How an order list that looks like one query runs 101, and how to load relations in one or two queries in every ORM.
Scenario
Example app: an admin page lists the latest 100 orders with the customer's name and the number of items. It was fine in development with 10 orders.
What you see in production
The page takes 1.8 s. pg_stat_statements shows SELECT … FROM customers WHERE id = $1 as the most-called statement in the database — 40 million calls a day — even though "nobody queries customers one by one".
The naive code
Lazy loading inside a loop — one query for the list, then one per row:
orders = Order.objects.order_by("-created_at")[:100] # 1 query
for o in orders:
rows.append((o.id, o.customer.name, o.items.count())) # +1 customer query, +1 count query per order
List<Order> orders = em.createQuery("select o from Order o order by o.createdAt desc", Order.class)
.setMaxResults(100).getResultList();
for (Order o : orders) {
rows.add(new Row(o.getId(), o.getCustomer().getName(), o.getItems().size())); // lazy loads per order
}
$orders = Order::latest()->take(100)->get();
foreach ($orders as $order) {
$rows[] = [$order->id, $order->customer->name, $order->items()->count()];
}
const orders = await prisma.order.findMany({ orderBy: { createdAt: "desc" }, take: 100 });
for (const o of orders) {
const customer = await prisma.customer.findUnique({ where: { id: o.customerId } }); // N queries
rows.push([o.id, customer?.name]);
}
Reproduce it
A fake ORM that counts queries:
queries = []
CUSTOMERS = {i: f"Customer {i}" for i in range(1, 21)}
ORDERS = [{"id": n, "customer_id": n % 20 + 1, "items": n % 4 + 1} for n in range(1, 101)]
def query(sql):
queries.append(sql)
def lazy_page():
query("SELECT * FROM orders ORDER BY created_at DESC LIMIT 100")
rows = []
for o in ORDERS:
query(f"SELECT name FROM customers WHERE id = {o['customer_id']}")
query(f"SELECT count(*) FROM order_items WHERE order_id = {o['id']}")
rows.append((o["id"], CUSTOMERS[o["customer_id"]], o["items"]))
return rows
def eager_page():
query("SELECT * FROM orders ORDER BY created_at DESC LIMIT 100")
ids = sorted({o["customer_id"] for o in ORDERS})
query(f"SELECT id, name FROM customers WHERE id IN ({len(ids)} ids)")
query("SELECT order_id, count(*) FROM order_items WHERE order_id IN (100 ids) GROUP BY order_id")
return [(o["id"], CUSTOMERS[o["customer_id"]], o["items"]) for o in ORDERS]
for page in (lazy_page, eager_page):
queries.clear()
rows = page()
print(f"{page.__name__:10} rows={len(rows)} queries={len(queries)} "
f"(≈ {len(queries) * 1.5:.0f} ms at 1.5 ms per round trip)")
Output
lazy_page rows=100 queries=201 (≈ 302 ms at 1.5 ms per round trip) eager_page rows=100 queries=3 (≈ 4 ms at 1.5 ms per round trip)
Root cause
ORMs load relations lazily by default: touching order.customer issues a query at that moment. In a loop, that becomes 1 + N (or 1 + 2N) round trips. Each is fast, but round-trip latency and per-query overhead add up, and the database spends its time on millions of tiny identical queries.
The fix
Load relations eagerly — in a join or in one batched IN (...) query — and count in the database:
orders = (Order.objects.select_related("customer") # JOIN for to-one relations
.annotate(item_count=Count("items")) # COUNT in the same query
.order_by("-created_at")[:100])
rows = [(o.id, o.customer.name, o.item_count) for o in orders] # 1 query total
# to-many relations: .prefetch_related("items") → 1 extra IN query
stmt = (select(Order).options(joinedload(Order.customer), selectinload(Order.items))
.order_by(Order.created_at.desc()).limit(100))
orders = session.scalars(stmt).unique().all() # 2 queries
@EntityGraph(attributePaths = {"customer"})
@Query("select o from Order o order by o.createdAt desc")
List<Order> latest(Pageable page);
// plus: spring.jpa.properties.hibernate.default_batch_fetch_size=100 batches remaining lazy loads into IN queries
$orders = Order::with('customer')->withCount('items')->latest()->take(100)->get(); // 2 queries
// in AppServiceProvider::boot(): Model::preventLazyLoading(! app()->isProduction());
const orders = await prisma.order.findMany({
orderBy: { createdAt: "desc" }, take: 100,
include: { customer: { select: { name: true } }, _count: { select: { items: true } } },
});
Trade-offs
- Joins for to-one relations are cheap; joining several to-many relations multiplies rows (a cartesian blow-up) — use separate
INqueries (prefetch_related,selectinload) for those. - Eager-loading everything everywhere over-fetches; load what the page uses.
- GraphQL resolvers hit the same problem per field — use a DataLoader to batch.
Detect it in production
- Queries per request in logs/APM (alert above ~20 for a page); Django Debug Toolbar, Laravel Telescope, Hibernate statistics in development.
- Laravel
preventLazyLoading(), SQLAlchemylazy="raise", and test assertions such as Django'sassertNumQueriesstop regressions in CI. - A tiny
SELECT … WHERE id = $1at the top ofpg_stat_statementsby calls is the classic signature.
Key points
- Lazy loading in a loop = 1 + N queries.
- Use joins for to-one relations and batched
INqueries for to-many; count in SQL. - Make lazy loading fail loudly in development and test query counts in CI.
Practice questions
An admin page with 100 orders runs 201 queries. What's happening?
Answer
Lazy loading inside a loop: one query for the orders, then one query per order for the customer and one per order for the item count (1 + 2N).
When should you use a JOIN-based eager load versus a separate IN query?
Answer
JOINs suit to-one relations. For to-many relations (especially several), separate IN (...) queries avoid multiplying rows; SQLAlchemy's selectinload and Django's prefetch_related do this.
How can you stop N+1 regressions from reaching production?
Answer
Make lazy loading raise in development/tests (Laravel preventLazyLoading, SQLAlchemy lazy='raise') and assert query counts in tests (e.g. Django assertNumQueries); monitor queries per request.
Open in the app to mark lessons complete, take notes and track your progress.