N+1 Queries: Killing the Cockroach of Web Performance for Good

The N+1 query problem is the cockroach of web performance: universally known, endlessly written about, and thriving in every codebase I audit anyway. The reason isn't ignorance — it's that ORMs make the bug syntactically invisible. The code reads beautifully, the local test with 5 rows flies, and production with 500 rows makes 501 queries. Let's kill it properly: detection, the fix taxonomy, and the two less-famous siblings that hide behind it.

The anatomy, thirty seconds

$orders = Order::where('status', 'open')->get();     // query 1: 200 orders

foreach ($orders as $order) {
    echo $order->customer->name;                     // queries 2..201 — one per order
}

The ORM's lazy loading fetches customer on first access, per instance. 200 fast queries — each maybe 2ms — is 400ms of pure round-trips, and it scales linearly with data growth: the page that was fine at launch degrades gradually, which is exactly why nobody notices until a customer does. The fix is two queries instead of 201:

$orders = Order::with(['customer', 'items.product'])   // eager: 1 + 1 + 2 queries total
    ->where('status', 'open')->get();

(.NET translation: context.Orders.Include(o => o.Customer).ThenInclude(...) — same disease, same cure, EF Core even logs a warning for lazy-load-in-loop if you leave lazy loading enabled, which I don't: it's off in my EF configs, period.)

Detection: make the framework tattle

Hunting N+1 by eyeball is a losing game; make it structural:

// AppServiceProvider — Laravel's nuclear option, and I mean that positively
Model::preventLazyLoading(! app()->isProduction());
// dev/CI: any lazy load THROWS with the exact relation named.
// production: it silently allows (never crash prod over a perf bug).

// Bonus honesty flags while you're there:
Model::preventAccessingMissingAttributes(! app()->isProduction());

This single line converts N+1 from "found during incident review" to "found by the first test that touches the code path". Pair it with query-count assertions on critical endpoints — $this->expectsDatabaseQueryCount(6) around a dashboard request is a regression tripwire that catches the innocent-looking ->append('avatar_url') someone adds next quarter. For running systems: Telescope/Debugbar in dev, and in production a trace showing 200 identical spans under one request is the smoking gun.

The fix taxonomy — because with() isn't always the answer

• Plain eager load (with()) — the 80% case, shown above.
• Constrained eager load — need only recent items? with(['items' => fn ($q) => $q->latest()->limit(5)])… careful: that limit applies to the whole batch in older patterns; for per-parent limits use latestOfMany/window-function approaches. Test with two parents, not one.
• Aggregate instead of load — displaying counts? withCount('items') / withSum('items', 'total') beat loading collections to ->count() them in PHP. Loading 10,000 rows to display the number 10,000 is N+1's chunkier cousin.
• Don't use the ORM at all — list screens joining four tables for display purposes are read-model territory: one honest SQL query, no hydration, no relations to trip on.

Sibling #1: over-eager loading

The reformed N+1 offender's second mistake: with(['customer', 'items.product.brand', 'shipments.events', ...]) on every query "to be safe". Now the index page hydrates six relations to render two columns — memory balloons, latency moves from round-trips to hydration, and the win evaporates. Eager loading is per-use-case, not per-model: the list loads what the list shows; the detail loads what the detail shows. (Default $with on the model is how this disease becomes hereditary — I allow it only for relations used in effectively every context.)

Sibling #2: the SELECT * tax and chunking

Two habits that compound at scale: fetching every column when the screen needs three (select(['id','status','total_minor']) — especially potent when a covering index can then serve the whole query), and loading unbounded sets into memory. Anything iterating "all rows" belongs in chunkById() / lazyById() (or cursor() for stream-processing) — the export job that worked at 10k rows and OOM-killed the worker at 400k is a rite of passage nobody needs to repeat.

The four-line code-review checklist

□ Any relation access inside a loop?          → eager load or read model
□ Any ->count()/->sum() on loaded relations?  → withCount/withSum
□ Eager list match what THIS screen renders?  → trim it
□ Unbounded ->get() on a growing table?       → chunk/lazy/cursor + select()

Run that checklist plus preventLazyLoading in CI, and the cockroach population drops to zero and stays there — which is the actual trick, because N+1 was never hard to fix. It was hard to keep fixed.

Suspect your app is quietly making four hundred queries per page? That's a one-day performance audit with before/after numbers you can frame.