N+1 Query Problem

What is the N+1 Problem?

The N+1 problem is a common performance anti-pattern in database access where an application executes 1 query to fetch a list of parent records, then N additional queries to fetch related data for each parent — resulting in N+1 total queries instead of a constant number.

This problem is especially prevalent when using Object-Relational Mappers (ORMs) that lazy-load relationships by default. While each individual query may be fast, the cumulative effect of hundreds or thousands of queries creates significant latency, database load, and network overhead.

How Does the N+1 Problem Occur?

Consider a typical scenario: you have users and orders tables, and you want to display each user along with their orders.

Step 1 — Fetch all users (1 query):

SELECT * FROM users;
-- Returns N users

Step 2 — For each user, fetch their orders (N queries):

SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
-- ... repeated for all N users

If there are 100 users, this results in 101 queries (1 + 100). If there are 10,000 users, it becomes 10,001 queries.

ORM Example

In most ORMs, this happens silently through lazy loading. Here is an example using Laravel's Eloquent:

// N+1 problem: lazy loading triggers a query per user
$users = User::all();                // 1 query
foreach ($users as $user) {
    echo $user->orders;              // N queries (one per user)
}

Eloquent lazy-loads the orders relationship on first access for each $user, resulting in N additional queries behind the scenes.

How to Fix the N+1 Problem

1. Eager Loading with with()

Eloquent's with() method solves the N+1 problem by eager loading relationships. Under the hood, it executes a second query using WHERE IN to batch-fetch all related records:

// 2 queries: one for users, one for all their orders
$users = User::with('orders')->get();

// SQL executed:
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1, 2, 3, ..., N);

For nested relationships, you can eager load multiple levels:

$users = User::with('orders.items')->get();

2. Preventing Lazy Loading

Since Laravel 8, you can make the application throw an exception whenever a relationship is lazy-loaded, catching N+1 problems during development:

// In AppServiceProvider::boot()
Model::preventLazyLoading(! app()->isProduction());

This will throw a LazyLoadingViolationException if any code accesses a relationship that was not eager-loaded, making N+1 queries impossible to miss during development.

3. loadMissing() for Conditional Eager Loading

When you already have a collection and want to eager load a relationship only if it hasn't been loaded yet:

$users = User::all();

// Only runs the query if 'orders' wasn't already loaded
$users->loadMissing('orders');

N+1 in NoSQL Databases

The N+1 problem is not limited to SQL databases — it is actually more prevalent in NoSQL databases like MongoDB and CouchDB because they lack server-side JOINs. Fetching related data across collections almost always requires multiple round-trips.

MongoDB Example

// N+1 problem in Mongoose
const users = await User.find();                            // 1 query
for (const user of users) {
    const orders = await Order.find({ userId: user._id });  // N queries
}

Fix — batch with $in:

const users = await User.find();
const userIds = users.map(u => u._id);
const orders = await Order.find({ userId: { $in: userIds } }); // 2 queries total

Denormalization (Materialized View Pattern)

Document databases offer a unique solution that relational databases do not: storing the pre-computed join result as a single document. Instead of referencing related collections and joining at query time, you materialize the final result at write time so reads are always a single lookup.

This is known as the Materialized View pattern — the document acts as a single source of truth that contains everything needed to serve a read request.

{
    "_id": "order_101",
    "user": { "id": 1, "name": "Alice", "email": "alice@example.com" },
    "items": [
        { "product": "Widget", "price": 25, "qty": 2 },
        { "product": "Gadget", "price": 30, "qty": 1 }
    ],
    "total": 80,
    "created_at": "2026-03-20T10:00:00Z"
}

With this schema, fetching an order with its user and line items requires a single query with no additional lookups.

Handling Write Amplification with Immutable Documents

The main concern with denormalization is write amplification — when shared data changes (e.g., a user updates their email), every document embedding that data needs to be updated. However, this can be mitigated by designing certain document types as immutable (append-only).

For example, transaction or order documents are natural candidates for immutability. Once an order is placed, its snapshot of the user's name, product prices, and quantities should never change — that was the state at the time of the transaction. Even if the user later changes their email or a product is renamed, the order document remains correct because it reflects what was true when it was created.

This immutability rule eliminates the write amplification problem for these documents entirely:

• Mutable documents (user profiles, product catalogs): keep normalized or accept the update cost.
• Immutable documents (transactions, invoices, audit logs): safely denormalize with a frozen snapshot — no updates needed, ever. As a bonus, immutability naturally provides a complete audit trail — every document preserves the exact state at the time it was created, making it easy to trace what happened and when.

By establishing clear rules about which documents are immutable, you get the read performance benefits of denormalization without the consistency headaches of keeping embedded data in sync.

How to Detect the N+1 Problem

• preventLazyLoading(): In Laravel 8+, calling Model::preventLazyLoading() in development throws an exception on any lazy-loaded relationship, catching N+1 problems immediately.
• Laravel Debugbar: The barryvdh/laravel-debugbar package shows the exact number and content of queries per request, making N+1 patterns easy to spot.
• Query logging: Enable query logging in your ORM or database to see exactly how many queries are executed per request. A suspiciously high count for a simple page load is a red flag.
• Application Performance Monitoring (APM): Tools like Datadog, New Relic, or Scout APM can surface slow endpoints with excessive query counts.

Summary

Approach	Queries	Trade-off
N+1 (lazy loading)	1 + N	Simple code, terrible performance at scale
Eager loading (with())	2	Recommended default in Eloquent; uses WHERE IN under the hood
Batch loading ($in in NoSQL)	2	Same idea as eager loading, applied manually in MongoDB/CouchDB
Denormalization / Materialized View (NoSQL)	1	Fastest reads; mitigate write amplification with immutable document rules