Nsikan Patrick Adaowo

Posted on Oct 31 Edited on Oct 31

N+1 Queries: Your ORM Is Lying to You and Your Database Is Paying the Bill

#backenddevelopment #databasedesign #architecture #softwareengineering

Your ORM is supposed to make database access easier, so why is your application making hundreds of database queries to return a single response?

You define your models, establish your relationships, and let the ORM handle the details. No complicated SQL. No messy joins. Just clean, readable application code.

Except there’s a problem.

That innocent-looking loop might be querying the database once for every record it retrieves. Your code stays simple, but your database pays for every additional round trip.

And adding an index might make those queries faster without fixing the underlying problem.

The issue isn’t always how efficiently you’re querying your database. Sometimes, it’s how many times you’re querying it in the first place.

This is the N+1 query problem. And it’s particularly frustrating because the code that introduces it can look perfectly reasonable.

An ORM promises to make database interactions easier to work with. It gives you models, relationships, and convenient methods for retrieving data. But convenient database access is not the same thing as efficient database access.

Your application code might look simple while the database handles a workload that was never necessary.

What Exactly Is an N+1 Query Problem?

Imagine you’re building a SaaS application with users and their projects.
You need an endpoint that returns a list of users alongside the projects each user owns.

The data model is straightforward:
A User can own many Project records.

Each Project belongs to one User.

In a relational database, this is a familiar one-to-many relationship.

The naive implementation might look like this:

Nothing about this code looks particularly alarming.

You retrieve the users, loop through them, and fetch each user’s projects. The code is readable, the relationship is clear, and the response has the shape you need.

But look at what happens at the database level.

The first query retrieves the users:

SELECT * FROM "User";

Then, for every user returned, the application executes another query:

SELECT * FROM "Project" WHERE "userId" = $1;

If the first query returns 50 users, the database receives:

1 query to retrieve the users.
50 additional queries to retrieve their projects.

That’s 51 queries to construct one response.

If the endpoint returns 500 users, it can execute 501 queries.

This is why the problem is called N+1: one initial query, followed by N additional queries.

The exact number depends on the data and the code path, but the underlying issue remains the same: the number of database queries grows with the number of records returned by the initial query.
And that growth can happen without any obvious change to the service’s complexity or readability.

Why N+1 Queries Are More Expensive Than They Look

A common mistake is to assume that an individual query is cheap, so executing many of them must also be cheap.
That assumption ignores the work surrounding each query.

A database query isn’t just the time PostgreSQL spends executing SQL. The application and database must also coordinate the operation.

Depending on the driver, connection pool, network, and execution path, each query may involve:

Sending a request from the application to the database.
Parsing, planning, or executing the statement as applicable.
Reading or modifying the relevant data.
Returning the result to the application.
Converting the result into application-level objects.

Some of these costs can be reduced through prepared statements, connection reuse, or other optimizations. But repeating a query still creates work.
Consider a simplified example.

Suppose each additional query takes an average of 5 milliseconds from the application’s perspective, including the relevant round-trip and processing overhead.

With 100 users, the endpoint executes 101 queries.

If those additional queries run sequentially, the repeated database operations alone could account for roughly 500 milliseconds, before adding the initial query, serialization, and other application work.

That number is illustrative, not a performance benchmark. Actual latency depends on the environment and workload.

The important point is that a query taking only a few milliseconds can become a serious problem when repeated hundreds of times.

And sequential execution isn’t the only concern.

The database is doing unnecessary work

Every additional query consumes some combination of database CPU, connection time, query execution resources, memory, and I/O.

Even when an index makes each lookup efficient, the database still has to process every request.
An index can make a query faster. It cannot eliminate the need to execute that query 100 times.

Your connection pool becomes a bottleneck

Database connections are finite resources.

When an application generates large numbers of queries, those queries must compete for available connections and database capacity.

This becomes particularly problematic when multiple requests are doing the same thing simultaneously.

One endpoint might execute 101 queries. Fifty concurrent requests could generate thousands of database operations in a short period.

The result can be increased connection wait time, growing latency, and reduced throughput.

Your tail latency gets worse

Users don’t experience your endpoint as a collection of individually fast queries. They experience the total time required to receive a response.

A route that performs unnecessary database operations can have acceptable average latency under light traffic and terrible p95 or p99 latency under load.

This is why N+1 problems often become obvious only after an application starts receiving meaningful traffic.
The application didn’t suddenly become badly written. The workload exposed an inefficiency that was already present.

The ORM Didn’t Lie. It Hid the Cost.

Let’s be precise about the title.

Your ORM isn’t necessarily generating bad SQL. In the example above, each individual SQL statement is reasonable.

The problem is the way the application uses the ORM.

Object-relational mappers make relational data easier to work with by representing tables and relationships as application-level objects. That abstraction is useful, but it can make the cost of accessing related records less obvious.

Consider this TypeORM example:

The repository methods are straightforward. The relationship is expressed through objects rather than handwritten SQL.

But the database still sees a query for the users followed by a query for each user’s projects.

The same pattern can appear in Prisma, TypeORM, Sequelize, Django ORM, Hibernate, and other data-access tools.

The syntax changes. The database workload does not.

This is an important distinction: an ORM can simplify how you express a query without guaranteeing that the overall query strategy is efficient.

A method call that looks like a single operation at the application level may generate multiple SQL statements. And a loop containing a database call can multiply those statements in ways that are easy to overlook during code review.

The right question isn’t simply, “Does this code use an ORM correctly?”

It’s also, “How many database operations does this code generate for a typical request?”

The First Fix: Fetch Related Data Intentionally

Let’s return to the original requirement.

We want to retrieve users and their projects.

We don’t actually need to retrieve each user’s projects independently. We already know the complete set of users we’re interested in.
That gives us a few better options.

Option A: Use a nested relation query

With Prisma, you can request the related projects as part of the same logical query:

Instead of manually fetching projects inside a loop, you describe the response shape you need.

Depending on the ORM version and relation-loading strategy, Prisma may load the related records using a database join or multiple batched SQL queries. Either way, this avoids the original pattern of issuing one independent project query for every user.

That distinction matters.

A nested ORM query doesn’t universally mean one SQL statement. Some ORMs fetch relations using a join; others issue separate queries and combine the results in application code. Some support both strategies.

What matters first is eliminating the repeated per-record lookup. After that, you can evaluate which relation-loading strategy best fits the workload.

This Prisma’s documentation explains the available relation-query strategies and their trade-offs

Option B: Fetch the parent records, then batch the children

Sometimes you need more control over how the data is assembled.

You can retrieve the users first, collect their IDs, and fetch all matching projects in a single additional query.

Assume userId is a numeric field in this example. If your schema uses UUIDs or strings, use the corresponding TypeScript type.

The important part is this filter:

where: { userId: { in: userIds, }, }

Instead of executing one query per user, the application asks PostgreSQL to retrieve projects belonging to the entire set of user IDs.

For a non-empty user list, this approach uses two logical database reads: one for users and one for projects.

The application then groups the projects by userId and assembles the response.

This is a useful pattern when:

You need custom response shaping.
You want separate control over parent and child queries.
The relation-loading behaviour of your ORM isn’t suitable for the endpoint.
You need to reuse the fetched records for additional application-level processing.

There are trade-offs. You now own the grouping logic, and a large list of IDs may require batching or pagination. You also need to ensure that the response doesn’t load an unbounded number of child records.

But the query count no longer grows by one for every user.

That’s the key improvement.

A JOIN Is Not Automatically the Best Answer

At this point, it can be tempting to conclude that every N+1 problem should be fixed with a SQL JOIN.
Often, a join is a good solution. But treating it as a universal rule replaces one simplistic assumption with another.

Consider:

This retrieves users and their projects through a single SQL statement.

For a straightforward one-to-many relationship, it can be an effective solution.

But joins change the shape of the result set.

If one user has ten projects, that user’s columns can appear across ten result rows. If you join multiple one-to-many relationships, the number of intermediate rows can grow dramatically.

For example, a user has 10 projects and 20 notifications. Joining both collections without carefully controlling the query shape can produce up to 200 combinations for that user.

This is sometimes called row multiplication or join explosion.

The database may still execute the query efficiently for a particular workload, but the application can end up processing far more data than it actually needs.

The practical lesson is not to avoid joins. It is to understand what the join produces.

Use a join when the relationship and result shape make sense. Consider separate batched queries when they avoid unnecessary row multiplication or make pagination easier to reason about. Measure both approaches when the workload justifies it.

The objective is not to minimize the SQL statement count at any cost. It is to minimize unnecessary work while preserving the data and consistency your endpoint requires.

Why Adding an Index Doesn’t Fix the Root Problem

Suppose the original query is slow:

That may be the right index for the query, depending on the table size, data distribution, and execution plan.

But the N+1 pattern remains.

If the application retrieves 100 users and issues one project query for each, the database still receives 100 project queries. The index may make those lookups faster, but it doesn’t remove their round-trips or execution overhead.

There is another detail worth remembering: indexes aren’t free. They consume storage and add maintenance work to inserts, updates, and deletes.

An index should support a meaningful access pattern, not compensate blindly for a query strategy that should be redesigned.

A useful debugging sequence is:

Count how many times the query executes per request.
Identify whether the repetitions are necessary.
Change the query shape to batch or load the related data appropriately.
Inspect the execution plan of the remaining important queries.
Add or adjust indexes when the evidence supports doing so.

Use PostgreSQL’s EXPLAIN (ANALYZE, BUFFERS) to investigate query execution, remembering that ANALYZE executes the statement. For statements that modify data, take appropriate precautions and understand the side effects before running it.

Query-plan analysis and query-count analysis answer different questions. You need both when both are relevant.

The GraphQL Trap: One Resolver, One Query, Repeated Many Times

N+1 problems are particularly easy to introduce in GraphQL.

Imagine a GraphQL API that returns users and their projects:

query { users { id name projects { id name } } }

The users resolver fetches the users.

Then the projects field resolver runs for each returned user.

If that resolver performs an independent database query, the request can produce the familiar N+1 pattern.

The confusing part is that each resolver may be perfectly reasonable in isolation.

The users resolver knows how to fetch users. The projects resolver knows how to fetch projects for a user. Both do their jobs correctly.

The inefficiency appears when the GraphQL execution model invokes the projects resolver repeatedly without coordinating those database reads.

One common solution is a DataLoader-style batching mechanism.

Conceptually, the resolver asks for projects for a particular user:

const projects = await projectLoader.load(user.id);

Instead of immediately executing a separate query for every call, the loader collects multiple user IDs and fetches the corresponding projects in a batch.

The batch function might look like this:

The returned array must correspond to the order of the incoming IDs, including empty results. That ordering contract is important for DataLoader implementations.

In production, you would normally use a DataLoader implementation or equivalent batching utility rather than building all of its caching and scheduling behavior yourself.

Also, create loaders with the appropriate request scope when cached results depend on the current user or authorization context. A global loader cache can accidentally reuse data across requests if it’s designed incorrectly.

DataLoader isn’t just a performance trick. It addresses a structural problem: field-level resolvers often need a way to coordinate data access across many related objects.

Why Promise.all() Is Not a Real Fix

One of the most common attempts to fix a slow loop is to make the queries concurrent.

Instead of:

A developer writes:

The second implementation removes the sequential wait between queries.

That can reduce wall-clock latency for small, bounded workloads. But it still issues one query per user.

With 100 users, you’re still generating roughly 100 project queries.

Worse, you’re now attempting to issue them concurrently. Depending on the database pool and workload, this can increase contention, create a queue of waiting queries, and consume resources more aggressively.

Concurrency changes when the work runs. It does not eliminate the work.

There are legitimate reasons to execute independent queries concurrently. The mistake is using concurrency as a substitute for reducing unnecessary database operations.

Before asking, “How can I make these queries run faster?”, ask, “Why am I running so many queries at all?”

Not Every Repeated Query Is an N+1 Problem

A good engineer also knows when not to apply a pattern mechanically.

Imagine you’re processing a small batch of three records. Fetching related data separately might be perfectly acceptable if it makes the code simpler and the performance requirements are met.

Or suppose each item requires a different query with distinct filters, authorization rules, or business logic. Those queries may not be safely reducible to one batch.

The defining concern isn’t simply that a query appears multiple times. It’s that a query is being repeated per result when the underlying data could be retrieved more efficiently as a group.

You should also distinguish N+1 from other database performance problems.

Missing indexes: The database must examine too many rows to answer a query.
Over-fetching: The application retrieves more columns or records than it needs.
Inefficient joins: A query creates excessive intermediate rows or performs expensive operations.
Connection pool exhaustion: Requests wait for database connections to become available.
N+1 queries: The application issues unnecessary additional queries as the number of parent records grows. These problems can occur together, but they require different diagnoses.

A slow endpoint is a symptom, not a diagnosis. Don’t start adding indexes, introducing Redis, or rewriting your ORM queries until you understand which cost is actually dominating the request.

How to Detect N+1 Queries Before Production

The most reliable approach is to observe what the application sends to the database.

Log Prisma queries during development

Prisma supports query-event logging that can help you inspect generated SQL and execution durations.
For example, depending on your Prisma version and client setup:

This is a simplified example of setting up a query listener. In a NestJS application, integrate the listener into your Prisma service or database instrumentation setup rather than creating a separate client for every service.

Avoid indiscriminately logging sensitive parameter values in production. Query logs can expose personal information or other confidential data, and excessive logging can introduce its own performance overhead.

Look for repeated query shapes

Suppose your logs contain:

`SELECT ... FROM "User";

SELECT ... FROM "Project"
WHERE "userId" = $1;

SELECT ... FROM "Project"
WHERE "userId" = $1;`

The repeated query shape is a clue.

The parameter changes, but the SQL structure stays the same. If the same route triggers this pattern repeatedly, investigate whether the query is being executed once for every parent record.

Don’t assume that repeated SQL automatically means N+1. A query may legitimately execute multiple times. Correlate the events with the request, route, and data-access code.

Measure query count per request

Execution duration alone doesn’t tell the full story.

A route that executes 40 queries taking 2 milliseconds each may look healthy when you inspect individual query durations. Yet the route could be doing far more database work than its response requires.

Useful measurements include:

Number of database queries per request.
Repeated query shapes and execution counts.
Total database time per request.
Endpoint latency, including p50, p95, and p99.
Connection pool wait time and utilization.
Rows returned and, where available, rows examined or read.

Test with realistic data volumes

An N+1 problem may be invisible when your test database contains only two users.

With two users, the endpoint performs three queries. That may seem harmless.

With 200 users, the same code performs 201 queries.

Performance tests should use representative record counts and relationships. Where practical, test query counts as well as response time. A test that verifies only the returned JSON can pass even when the implementation generates hundreds of unnecessary queries.

A Practical Checklist for Reviewing Database Access

When reviewing a NestJS service, GraphQL resolver, or repository method, ask:

1. Am I querying inside a loop?

Look for database calls inside for, for...of, map, forEach, or other repeated execution paths. Not every occurrence is wrong, but each deserves scrutiny.

2. Can I fetch the related records in one batch?

If you already have a collection of IDs, consider a query using IN, an ORM’s nested relation loading, or a DataLoader-style batching mechanism.

3. Am I returning only the data the endpoint needs?

Use select or the equivalent to avoid loading large text fields, unnecessary relations, or entire entities when the response requires only a few fields.

4. Is the response bounded?

Pagination matters. Fetching all users and all their projects in one giant query can replace an N+1 problem with an unbounded data-loading problem.

5. Does the chosen loading strategy fit the relationship?

A join may be effective for one relation but expensive when several large collections are loaded together. Compare the query shapes and actual execution costs.

6. Have I measured the result?

Count the queries before and after the change. Compare endpoint latency and database resource usage under representative load.

7. Is the data access still correct?

Optimization must preserve authorization, filtering, ordering, pagination, and any required consistency guarantees. A faster query that returns the wrong records is not an optimization.

Final Thoughts: Your ORM Is Only as Smart as the Queries You Ask It to Make

N+1 queries are frustrating because they rarely look like a serious engineering mistake at first.

The code is readable. The relationships are modeled correctly. The endpoint returns the expected response.

The problem is hidden in the execution pattern.

An ORM can make database interactions easier to express, but it cannot remove the need to understand how those interactions translate into actual database work.

And the fix isn’t always to write raw SQL, add an index, introduce a cache, or force every relation into a join. Sometimes, the most effective improvement is simply to stop asking the database the same question one record at a time.

Think about the workload your code creates, not just the code you write.

When an endpoint becomes slow, don’t stop at asking whether each query is efficient. Ask whether all those queries need to exist in the first place.

Because the fastest unnecessary query is the one your application never sends.