TL;DR: The N+1 query problem occurs when an ORM executes one initial database query followed by N subsequent queries to fetch related child data. I solve this by using SQL joins, batch fetching (IN queries), or data denormalization. This guide shows you how to implement each fix.
Let’s be honest: ORMs are a double-edged sword. Tools like Hibernate, Prisma, or TypeORM sucker you in with incredibly slick, beautiful interfaces. They make database interactions feel like pure poetry—until they turn around and beat you down with nasty performance bugs.
I see this happen all the time. You build a clean-looking feature, push it to production, and suddenly your database performance falls off a cliff. Nine times out of ten, you’ve quietly introduced the classic N+1 query problem without even realizing it.
What is the N+1 query problem?
To put it simply, the N+1 query problem is what happens when your application makes one query to fetch parent records, and then makes N individual queries to fetch related child data. Instead of hitting the database once, you end up hitting it N+1 times. If you have 100 records, you are making 101 queries, which will absolutely melt your database under load.
Let's look at a hypothetical scenario. Imagine your team is building a social media feed. You want to display a list of posts, and each post needs to show the author’s username.
Under the hood, your ORM might make one query to grab the posts:
SELECT * FROM posts LIMIT 100;
Then, to display the usernames, it loops through those posts and fires off a separate query for every single post:
SELECT username FROM users WHERE id = [post.userId];
Suddenly, what should have been a single fast database roundtrip turns into a barrage of 101 queries. I promise you, doing this repeatedly when users load their feeds is a fast track to a production outage.
How do you identify N+1 queries in your code?
I find the easiest way to catch N+1 queries is by enabling SQL logging in development or using APM tools to monitor your database traffic. If you see a massive block of near-identical SELECT statements executing in rapid succession, you have an N+1 bug. You can also spot them by keeping a close eye on your ORM’s relationship loading behavior.
If you do not have APM tools set up, just watch your console output in your local dev environment. When a single page load triggers a continuous scroll of database logs that look identical except for the primary key, I guarantee you have a performance problem on your hands.
What are the best ways to solve the N+1 query problem?
I resolve N+1 queries using one of three main strategies: database JOINs, batch queries with an IN clause, or data denormalization. The right choice depends on your read-to-write ratios and how complex your relationships are. Here is how I break down these three approaches:
| Solution | Database Roundtrips | Complexity | When I Use It |
|---|---|---|---|
| SQL JOIN | 1 | Low | Standard relational databases where tables are indexed. |
| Batching (IN clause) | 2 | Medium | Large datasets where large JOINs become too expensive. |
| Denormalization | 1 | High | Scale-heavy, read-intensive feeds where performance is everything. |
How does a SQL JOIN solve N+1?
A SQL JOIN combines both tables into a single query, which allows the database engine to correlate and return all your data in one roundtrip. Instead of retrieving raw posts and then looking up users, you return an "enhanced" post that already contains the username details. This is usually my go-to fix because it leverages what SQL databases do best.
Most modern ORMs allow you to trigger this behavior using eager loading flags. You just tell the ORM to explicitly include the relation, and it will rewrite the underlying query to use a join under the hood.
How does batching with an IN clause fix the issue?
Batch fetching solves the problem in exactly two database queries by utilizing a SQL IN clause to retrieve child data in bulk. First, the application fetches the parent records, extracts the foreign keys, and then runs a single query to get all related rows at once. This keeps the total database roundtrips to two, regardless of how many records you have.
In our feed scenario, this means you run one query for the 100 posts, extract all the userId values, and run a single follow-up: SELECT * FROM users WHERE id IN (1, 2, 3...). It is incredibly clean and prevents massive, slow joins on huge datasets.
When should you resort to data denormalization?
Data denormalization is the right choice when you are working with non-relational structures or read-heavy systems where you simply cannot afford joins. You solve N+1 by writing the duplicate data—like storing the username directly inside the posts table—so everything you need is in one place. Just remember that you will have the persistent headache of keeping that duplicated data updated when a user changes their username.
If a user updates their name, you have to run a background job to cascade that update to every single post they've ever written. It's a trade-off: you get lightning-fast reads, but your write logic gets significantly more complex.
FAQ
Why do ORMs default to lazy loading if it is so dangerous?
Lazy loading is the default because it is convenient and saves memory by only fetching what you ask for. But if you ask me, it is a total trap; it assumes developers will manually specify eager loading whenever they handle collections, which we almost always forget to do.
Does eager loading completely eliminate the N+1 query problem?
Yes, eager loading forces the ORM to fetch related data upfront, usually through a JOIN or an IN query. However, you have to be careful not to eager-load every single relation on a model, or you will end up pulling half your database into application memory.
How do you handle denormalized data sync issues?
If you go the denormalization route, you have to accept the trade-off of maintaining that data. I usually handle this by triggering asynchronous background workers or event listeners to update the duplicated username across all post records whenever a user changes their name.
Top comments (0)