The N+1 Query Problem: A Matter Requiring Immediate Attention

Ah. I see you have an ORM.

I mean no disrespect to your ORM. It is a perfectly civilized abstraction. It keeps SQL out of your application code, handles parameterization, manages migrations, and maps rows to objects with admirable consistency. For the vast majority of operations, it performs its duties without complaint.

It also has a habit — one that it does not advertise — of sending an extraordinary number of queries to your database when you are not watching. This habit is called the N+1 problem, and it is, without exaggeration, the single most common cause of application-level database performance issues I encounter.

I have observed, in production systems, pages generating over 400 database round trips to render a single list view. The application developers were unaware. The users were not — they simply waited, or left.

The good news: it is entirely fixable. Every major ORM provides the mechanism. The difficulty is not the fix — it is knowing the problem exists in the first place. And once you know what to look for, you will begin to see it everywhere.

What is the N+1 query problem?

The N+1 problem occurs when your application executes 1 query to fetch a list of N items, then executes N additional queries to fetch related data for each item individually.

-- Your ORM renders a list of 200 orders with customer names.
-- Here is what it sends to PostgreSQL:

SELECT * FROM orders WHERE status = 'pending';
-- Returns 200 rows. Then, for EACH row:
SELECT * FROM customers WHERE id = 42;
SELECT * FROM customers WHERE id = 17;
SELECT * FROM customers WHERE id = 89;
-- ... 197 more queries exactly like this

That is 201 queries to fetch 200 orders with their customer names. Each query carries a network round trip, a connection checkout from the pool, planner time, execution time, and serialization overhead. At 1.2ms per query — a generous estimate for a local database — that is 241ms of pure overhead before the application can even begin rendering.

What should happen instead:

-- What should have been sent:
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- 1 query. 1 round trip. Same result.

One query. One round trip. The database joins the tables, returns the combined result, and the application proceeds. The difference is not incremental — it is categorical. A 241ms operation becomes a 3ms operation. The database is doing less work, the network is carrying less traffic, and the connection pool is under less pressure.

Why ORMs do this

A reasonable question: if eager loading is clearly better, why do ORMs default to lazy loading in the first place?

The answer is that lazy loading is the correct default for correctness — if not for performance. When an ORM lazily loads a relationship, it guarantees that the related data is only fetched when it is actually accessed. If your code path touches order.customer on only 3 of 200 orders, lazy loading sends 4 queries. Eager loading would have sent a JOIN across all 200 orders, transferring customer data you never used.

This is a sensible trade-off — in theory. In practice, the code path almost always touches every row. A template rendering a list, a serializer building a JSON response, a report iterating through results — these patterns access the relationship on every item. The ORM cannot know this at query time. It optimizes for the general case, and the general case is wrong for the common case.

The result is a design that is architecturally sound and operationally catastrophic. It is not a bug. It is a trade-off made at the framework level that must be corrected at the application level, every time, by every developer, on every query that touches a relationship. The frequency with which this correction is forgotten is the entire reason this article exists.

How bad does it get?

The N+1 problem scales linearly with data. What is imperceptible at 10 rows becomes unbearable at 2,000. Allow me to illustrate:

The "Local DB" column assumes 1.2ms per query — execution plus loopback network overhead. The "Remote DB" column assumes 4.5ms, which accounts for the network round trip to a managed database (RDS, Cloud SQL, Neon, Supabase). That additional 3ms per query is negligible for a single query. Multiply it by 2,001 and it adds 6 seconds of pure network latency.

This is why N+1 problems often go unnoticed in development (local database, fast loopback, small datasets) and surface violently in production (remote database, real network, real data volumes). The developer's laptop is a liar.

The cascade: when N+1 becomes N×M+1

I regret to inform you that it gets worse. When your page accesses multiple levels of relationships — orders with customer names and shipping addresses, products with categories and images, invoices with line items and tax records — the N+1 pattern compounds.

-- A page showing orders with customer names and shipping addresses.
-- The ORM sends:

SELECT * FROM orders WHERE status = 'pending';       -- 1 query → 200 rows
SELECT * FROM customers WHERE id = 42;               -- ×200 queries
SELECT * FROM addresses WHERE customer_id = 42;      -- ×200 MORE queries
-- Total: 1 + 200 + 200 = 401 queries

-- With three levels of nesting, 200 rows becomes 601 queries.
-- With four levels: 801. The multiplication is relentless.

Each additional relationship adds another N queries. Two relationships on 200 parent rows: 401 queries. Three relationships: 601. If any of those relationships have their own nested relationships, the multiplication cascades further.

I have encountered pages — real, production pages serving real users — that generated over 800 queries to render what was, visually, a simple table with a few columns. The developers had no idea. The database, characteristically, did not complain. It simply did what it was told, 800 times, as fast as it could.

The fix follows the same principle:

-- One query. Four tables. Zero drama.
SELECT o.*, c.name, a.city, a.postal_code
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN addresses a ON a.customer_id = c.id
WHERE o.status = 'pending';

Four tables. One query. The database does the joining — which it is spectacularly good at — instead of the application doing it one row at a time over the network.

The fix in every major ORM

Every ORM that generates N+1 patterns also provides a way to avoid them. The mechanism is called eager loading — fetching the related data in the same query (or in a single additional query) instead of lazily loading it row by row.

The syntax varies. The principle is universal.

Django

# Django — the N+1:
for order in Order.objects.filter(status='pending'):
    print(order.customer.name)  # triggers a query per row

# Django — the fix:
for order in Order.objects.filter(status='pending').select_related('customer'):
    print(order.customer.name)  # no additional queries

Django's own documentation covers select_related thoroughly, and it is the right starting point. It performs a SQL JOIN. Use it for foreign key and one-to-one relationships. For many-to-many and reverse foreign keys, use prefetch_related, which executes a second query with an IN clause. The distinction matters: select_related is one query with a JOIN; prefetch_related is two queries with an IN. Both eliminate the N+1. Choose based on the relationship type and cardinality.

Rails / ActiveRecord

# Rails — the N+1:
Order.where(status: 'pending').each do |order|
  puts order.customer.name  # N queries
end

# Rails — the fix:
Order.where(status: 'pending').includes(:customer).each do |order|
  puts order.customer.name  # 1 or 2 queries total
end

The Rails team has documented eager loading well in the Active Record Query Interface guide. includes lets Rails choose between a JOIN and a separate IN query based on the query conditions. Use eager_load to force a JOIN, preload to force separate queries. Rails also provides the Bullet gem, which detects N+1 patterns at runtime and suggests the appropriate eager load — a remarkably useful tool that I wish every framework had.

SQLAlchemy

# SQLAlchemy — the N+1:
orders = session.query(Order).filter_by(status='pending').all()
for order in orders:
    print(order.customer.name)  # lazy load, N queries

# SQLAlchemy — the fix:
from sqlalchemy.orm import joinedload
orders = session.query(Order).options(
    joinedload(Order.customer)
).filter_by(status='pending').all()

The SQLAlchemy documentation addresses this in detail — the relationship loading section of their docs is where you want to begin. joinedload adds a JOIN. subqueryload uses a correlated subquery. selectinload uses an IN clause. Each has trade-offs: joinedload is a single query but can produce cartesian products with multiple has-many relationships; selectinload avoids the cartesian at the cost of a second query. For most cases, selectinload is the safer default.

Prisma

// Prisma — the N+1:
const orders = await prisma.order.findMany({
  where: { status: 'pending' }
})
// Then accessing order.customer triggers N queries

// Prisma — the fix:
const orders = await prisma.order.findMany({
  where: { status: 'pending' },
  include: { customer: true }
})

Prisma's include fetches relations in separate batched queries — not JOINs. This avoids the cartesian product issue but means two round trips. Prisma does not currently support JOIN-based eager loading, which is a trade-off worth understanding: it is always correct but not always optimal. For most applications, the batched approach is more than sufficient.

Eloquent (Laravel)

// Eloquent (Laravel) — the N+1:
$orders = Order::where('status', 'pending')->get();
foreach ($orders as $order) {
    echo $order->customer->name;  // N queries
}

// Eloquent — the fix:
$orders = Order::where('status', 'pending')->with('customer')->get();
foreach ($orders as $order) {
    echo $order->customer->name;  // 1 additional query
}

Laravel's documentation on Eloquent eager loading is clear and well-organized. The with method eager loads using a separate IN query. It is clean, readable, and handles nested relationships naturally: with(['customer', 'customer.address']). Laravel 9+ also offers strict mode, which raises an exception on any lazy load — a feature I regard with considerable approval.

Hibernate (JPA)

// Hibernate (JPA) — the N+1:
List<Order> orders = em.createQuery(
    "SELECT o FROM Order o WHERE o.status = 'pending'", Order.class
).getResultList();
// Accessing order.getCustomer().getName() triggers N queries

// Hibernate — the fix:
List<Order> orders = em.createQuery(
    "SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = 'pending'",
    Order.class
).getResultList();

Hibernate's JOIN FETCH in JPQL (or @EntityGraph annotations) forces a JOIN. Without it, Hibernate's default lazy loading will produce the N+1 pattern on every @ManyToOne and @OneToMany relationship. Hibernate's documentation is extensive on this topic, because the problem is extensive in every Hibernate application I have ever examined.

An honest word about eager loading

I should be forthcoming about the limits of eager loading, because pretending they do not exist would be a disservice to you and an embarrassment to me.

Eager loading is not universally better than lazy loading. There are situations where it makes things worse:

The cartesian product. When you eager load multiple has-many relationships using JOINs, the result set multiplies across all relationships.

-- Eager loading orders with BOTH line_items and shipments:
SELECT o.*, li.*, s.*
FROM orders o
LEFT JOIN line_items li ON li.order_id = o.id
LEFT JOIN shipments s ON s.order_id = o.id
WHERE o.status = 'pending';

-- If an order has 5 line items and 2 shipments,
-- that order produces 5 × 2 = 10 rows in the result set.
-- 200 orders × 10 rows each = 2,000 rows transferred.
-- The data was correct. The volume was not.

The fix is to use separate queries for multiple has-many relationships rather than JOINing them all. Django's prefetch_related, Rails' preload, and SQLAlchemy's selectinload all handle this correctly — they issue one additional query per relationship instead of one massive JOIN.

Loading data you do not use. If your code path conditionally accesses a relationship — say, only showing customer details for VIP orders — then eager loading all customers is wasted work. The ORM transferred data you never touched. Lazy loading would have been more efficient in this specific case. The correct response is not to abandon eager loading but to be deliberate about which relationships you load.

Deeply nested includes. Eager loading five levels of nested relationships in a single query produces a result set of staggering width. At a certain depth, it is better to make separate, focused queries for each level. The ORM's batching strategies handle this well; the temptation to include everything in one call does not.

The principle: eager loading eliminates the N+1 problem. But it introduces its own failure modes when applied indiscriminately. The goal is not "always eager load" — it is "never accidentally lazy load."

How to detect N+1 patterns

The most direct method in development: enable query logging and look for the signature — many identical queries in rapid succession, differing only in the WHERE parameter.

# Django: add to settings.py
LOGGING = {
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
        }
    }
}

# Rails: already logs in development. In production:
ActiveRecord::Base.logger = Logger.new(STDOUT)

# SQLAlchemy:
engine = create_engine(url, echo=True)

# Laravel: enable query log
DB::enableQueryLog();
// ... run your code ...
dd(DB::getQueryLog());

For Django, the Django Debug Toolbar shows duplicate queries per page load. For Rails, the Bullet gem detects N+1 patterns automatically and suggests the precise eager load. For Laravel, Debugbar and the built-in strict mode serve the same purpose.

Detection in production with pg_stat_statements

In production, you rarely have the luxury of debug toolbars. pg_stat_statements reveals the pattern from the database's perspective:

-- Find likely N+1 children: simple queries with very high call counts
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query LIKE 'SELECT%FROM%WHERE%id = $1'
  AND calls > 1000
ORDER BY calls DESC
LIMIT 20;

-- A SELECT * FROM customers WHERE id = $1 with 50,000 daily calls
-- is almost certainly the child side of an N+1 pattern.

The signal is unmistakable: a trivially simple query — a single-table lookup by primary key — with a call count orders of magnitude higher than its complexity warrants. If SELECT * FROM customers WHERE id = $1 has 50,000 daily calls while SELECT * FROM customers has 200, something is fetching customers one at a time when it could be fetching them in bulk. That is an N+1.

Look also at the total_exec_time column. An N+1 child query is fast individually — typically under 1ms. But when called 50,000 times per day, the total execution time accumulates. A 0.3ms query called 50,000 times consumes 15 seconds of PostgreSQL's day on a query that should not exist.

Why N+1 patterns keep returning

The N+1 problem is chronic, not acute. You fix it, and it comes back. Not because the fix failed, but because the conditions that created it are ongoing.

Every new feature that renders related data is an opportunity to introduce an N+1. A new column in an admin table, a new field in an API serializer, a new template partial that traverses a relationship. The developer writes order.customer.name and the ORM silently generates a query. No error, no warning, no indication that anything is amiss — until production traffic makes it visible.

Code review catches some of these. Automated detection tools catch others. But the most effective prevention is making lazy loading impossible by default:

# Django 4.2+ — raise an exception on lazy loading:
# In settings.py for development:
CONN_MAX_AGE = 0  # ensure fresh connections
# Then in your test settings or middleware:
# django-auto-prefetch or django-zen-queries can enforce this

# SQLAlchemy — raiseload prevents lazy loading:
from sqlalchemy.orm import raiseload
orders = session.query(Order).options(
    raiseload('*')  # any lazy access raises an exception
).filter_by(status='pending').all()

# Laravel — strict mode (Laravel 9+):
# In AppServiceProvider::boot()
Model::preventLazyLoading(! app()->isProduction());

Strict mode inverts the default: instead of lazily loading and hoping someone notices the N+1, the application raises an exception whenever a lazy load is attempted. Every relationship access must be explicitly eager loaded. This is more work upfront and precisely the right amount of work. You know exactly what data each query fetches, because you declared it.

I am of the firm opinion that strict mode should be the default in every development and test environment. The additional effort of declaring your eager loads is trivial compared to the effort of diagnosing N+1 patterns in production.

When the fix is not in the application

Eager loading solves N+1 patterns you know about. The challenge is the ones you don't — and the ones that slip through after every code change, feature addition, and dependency update.

Gold Lapel addresses this at the proxy level. It observes the actual query traffic between your application and PostgreSQL and detects the N+1 signature: many identical parameterized queries arriving in rapid succession from the same connection. It then batches them into a single operation before they reach the database. Your ORM sends 200 individual SELECT statements; PostgreSQL receives 1 query with an IN clause.

This does not replace fixing the application code — eager loading is still the right long-term solution, and strict mode is still the right prevention. But a proxy-level safety net catches the N+1 patterns that slip through code review, that emerge from new features, and that hide in rarely-exercised code paths until production traffic finds them. The ORM sends the queries it was always going to send. The proxy ensures the database receives something more reasonable.