# The N+1 Query Problem > The ORM trap that is fast on seed data and dead in production: one query quietly becomes N+1. How to spot it and fix it. --- # The N+1 Query Problem You shipped a page. It flew on your laptop with ten rows of seed data. Then production filled up, and the same page started taking four seconds, then eight, then timing out - and nothing in your code changed. The bug was there the whole time, hiding behind a line that looked completely innocent. This guide shows you exactly what that line does, how to see it, and the three ways to fix it. ## How to read this Read it in order, once, start to finish. Phase 1 builds the mental model so the rest stops feeling like magic. Phase 2 is the day-job skill: seeing the extra queries in your own logs. Phase 3 is the fix, plus the tradeoff nobody warns you about. The examples are deliberately plain - the trap is the same whether you write Python, Ruby, PHP, Java, or JavaScript, so don't get hung up on one ORM's spelling. ## The phases 1. [What N+1 actually is](01-what-n-plus-one-is.md) - the mental model: one query, then one more per row. 2. [Seeing it in your logs](02-seeing-it-in-the-logs.md) - how to catch it red-handed with query logs and APM. 3. [Fixing it without over-fetching](03-fixing-it.md) - eager loading, the single JOIN, batching, and the tradeoff. --- # What N+1 actually is Here's the situation you keep walking into. You write a list page - orders, posts, users, whatever. For each row you show something from a related table: the customer's name next to each order, the author next to each post. The code reads like plain English, you ship it, and it's fast. Weeks later it's the slowest page in the app and you have no idea why, because the code still reads like plain English. The name tells you the whole story: **1** query to load the list, then **N** more queries - one for every single row in that list. Ten rows, eleven queries. Ten thousand rows, ten thousand and one queries. The "+1" is the list; the "N" is the part that grows with your data and quietly kills you. ## The line that looks innocent Imagine loading all the orders and printing each customer's name. In almost any ORM, it looks roughly like this: ```text orders = Order.all() # query #1: SELECT * FROM orders for order in orders: print(order.customer.name) # each .customer = one more query ``` *What just happened:* the first line ran one query and gave you a list of orders. But `order.customer` is not data you already have - it's a *trapdoor*. The first time you touch it, the ORM secretly runs another query to go fetch that customer. Loop over 500 orders and you've fired 500 hidden queries on top of the first one. The total is 1 + N = 501. The cruel part is that `order.customer.name` looks exactly like reading a field you already loaded. There is no `query()` call, no SQL in sight, no syntax that screams "I am about to hit the database." That's the trap: the cost is invisible at the point where you pay it. ## Why is it called "lazy loading"? When you load `Order.all()`, the ORM does the minimum: it fetches the orders and stops. It does **not** go fetch every related customer, because maybe you'll never look at them. Deferring that work until you actually ask is called **lazy loading**, and on its own it's a sensible default - why pay for data you might not use? The N+1 problem is what lazy loading turns into when you *do* use it, once per row, inside a loop. Each `.customer` access wakes the lazy loader, which thinks "oh, you want this one? hang on" and runs a query. It has no idea you're about to ask for 499 more. It can't see the loop. So it solves each request in the dumbest possible isolation: one round trip at a time. ```text SELECT * FROM orders; -- the "+1" SELECT * FROM customers WHERE id = 17; -- row 1 SELECT * FROM customers WHERE id = 4; -- row 2 SELECT * FROM customers WHERE id = 91; -- row 3 ... -- ... and so on, N times ``` *What just happened:* this is the actual SQL your one innocent loop produced. Each `WHERE id = ?` is a separate trip to the database - connect, send, wait, receive - and the waiting dominates. Even if every query is instant on its own, the *round trips* stack up. A query that takes 1 millisecond becomes 500 milliseconds of nothing-but-waiting when you run it 500 times. > The killer isn't slow queries. It's *fast* queries run a thousand times. Each one is innocent; the multiplication is fatal. ## Why your laptop lied to you On ten rows of seed data, N+1 is 11 queries. Eleven fast queries against a local database with no network in between is genuinely instant - you will never feel it. Your tests pass. Your demo flies. Everything looks perfect. Production is a different planet. There are 10,000 rows instead of 10. The database lives on another machine, so every query pays real network latency. And dozens of users are hitting that same page at once, each spawning their own flood of N queries. The cost didn't appear in production - it was always there, scaling with `N`. You only changed `N`. This is why N+1 is so dangerous specifically: it is a **performance bug that scales with data**, and you develop on tiny data. The feedback loop that would catch it is exactly the one your dev environment removes. ## The mental model to keep Hold onto this one picture and you'll recognize N+1 everywhere for the rest of your career: **a loop over rows, where the body touches a relation.** That's the shape. Whenever you see code iterating a collection and reaching across to a related object inside the loop, your alarm should go off - not because it's wrong, but because it *might* be firing one query per turn of the loop. It isn't the loop that's the problem, and it isn't the relation. It's the combination: asking the database the same kind of question over and over, one row at a time, when you could have asked once for all of them. Phase 3 is entirely about turning N+1 questions into a single question. But first you need to *see* it happening, which is Phase 2. For builders: if you're fuzzy on what an ORM is actually doing when you write `order.customer`, the trapdoor makes a lot more sense after reading [how an ORM works](/guides/how-an-orm-works) - N+1 is the price of the convenience that guide describes. ```quiz [ { "q": "In the name \"N+1\", what does the \"+1\" refer to?", "choices": [ "One extra query the database adds for safety", "The single query that loads the list of rows", "The first row in the result set", "An off-by-one bug in the loop" ], "answer": 1, "explain": "The \"+1\" is the initial query that fetches the list; the \"N\" is the one-query-per-row that follows." }, { "q": "Why does N+1 usually stay invisible during development?", "choices": [ "ORMs disable lazy loading in dev mode", "Dev databases run a faster query planner", "On tiny seed data, N is small and the queries are local and instant", "The bug only exists in compiled production builds" ], "answer": 2, "explain": "Eleven instant local queries feel like nothing. The cost scales with N, and dev data keeps N tiny." }, { "q": "What makes the line `order.customer.name` so easy to miss?", "choices": [ "It looks like reading already-loaded data, but secretly fires a query", "It only runs on every other iteration", "It throws a warning that most loggers hide", "It always loads the wrong customer" ], "answer": 0, "explain": "Attribute access looks free. The lazy loader turns that innocent dot into a hidden round trip to the database." } ] ``` Watch it animated: [the N+1 query problem](/explainers/NPlusOne.dc.html) --- # Seeing it in your logs You can't fix what you can't see, and N+1 is built to stay unseen. The whole point of an ORM is to hide SQL from you - which is wonderful right up until the hidden SQL is the bug. So the single most useful skill here isn't memorizing fixes. It's learning to make the database *show its work*, then recognizing the telltale pattern in what it shows. Once you've caught N+1 in the act even once, you'll never un-see it. The pattern is loud and unmistakable. You have to turn the lights on. ## Step one: turn on query logging Every ORM can be told to print the SQL it runs. The setting has a different name in each one, but the idea is universal: log every query to the console. Flip it on in development and reload the page you're suspicious of. ```text # the setting is named differently per stack, e.g.: # Rails / ActiveRecord -> on by default in the dev log # Django -> log the 'django.db.backends' logger at DEBUG # SQLAlchemy -> create_engine(url, echo=True) # Hibernate -> hibernate.show_sql = true # Prisma / Sequelize -> log: ['query'] ``` *What just happened:* you told the ORM to stop hiding. From now on, every query it runs appears in your console as real SQL. This is the one switch that converts N+1 from an invisible mystery into something you can literally count. ## Step two: read the pattern Now reload your suspicious page and watch the log. N+1 has a fingerprint you cannot mistake for anything else - **the same query, repeated, with only the ID changing.** ```sql SELECT * FROM orders WHERE status = 'open'; -- the +1 SELECT * FROM customers WHERE id = 17; -- N begins... SELECT * FROM customers WHERE id = 4; SELECT * FROM customers WHERE id = 91; SELECT * FROM customers WHERE id = 23; SELECT * FROM customers WHERE id = 17; -- note: 17 again! SELECT * FROM customers WHERE id = 56; -- ... 200 more lines exactly like these ... ``` *What just happened:* the wall of near-identical `SELECT ... WHERE id = ?` lines IS the N+1. One outer query, then a long stutter of single-row lookups that differ only in the number. That repetition is the signature - when your log scrolls with the same statement over and over, you've found it. Bonus tell: notice `id = 17` shows up twice. The lazy loader doesn't even remember it already fetched customer 17; it asks again. That's pure waste on top of the waste. > The fingerprint is repetition. One unique query repeated 200 times is N+1. Two hundred genuinely different queries is a different (and rarer) problem. ## Step three: count, don't eyeball On a real page you might have several relations and several loops, and the log becomes a blur. Don't try to read every line - **count the queries instead.** Most stacks give you a query counter for exactly this. ```text Page rendered. Database: 1 + 247 queries in 1,830 ms. ``` *What just happened:* this is the smoking gun in numeric form. A page that should need a small, fixed handful of queries instead ran 248. The number to watch is whether query count grows when your data grows. The clear test: load the page with 10 rows, note the count; load it with 50 rows, note it again. If the count jumped roughly fivefold, your queries scale with rows - that's N+1, confirmed. A healthy page's query count barely moves when the row count changes. ## In production: lean on your APM You can't tail a console in production, and that's where N+1 actually hurts. This is what an **APM** (Application Performance Monitoring tool - think Datadog, New Relic, Sentry, Scout) is for. It records every request and breaks down where the time went, including how many database queries each endpoint fired and how long they took in total. The N+1 signature in an APM is visual and as obvious as in the log: a request's timeline shows a dense ladder of dozens or hundreds of tiny, identical database spans stacked one after another. Each bar is short; the stack of them is enormous. Many APMs will even flag it for you with a literal "N+1 queries detected" warning on the endpoint - they pattern-match the same repetition you learned to read by eye. The workflow that actually works in practice: ```text 1. APM flags a slow endpoint, mostly "time in database". 2. The trace shows 1 list query + a tall stack of identical row lookups. 3. Reproduce it locally with query logging on. 4. Count queries before; apply a fix (Phase 3); count after. 5. The count should drop to a small constant. Ship. ``` *What just happened:* you closed the loop from symptom to confirmation. Production told you *where* (which endpoint), local logging told you *what* (the repeated query), and the before/after count proves the fix worked instead of you just hoping it did. For builders: if your APM says the time is in the database but you *don't* see the repetition fingerprint - it's one query that's genuinely slow, not N+1 - that's a different diagnosis. Head to [why is my query slow](/guides/why-is-my-query-slow) instead; the fix there is indexes and query shape, not eager loading. ```quiz [ { "q": "What is the unmistakable fingerprint of N+1 in a query log?", "choices": [ "One enormous query with many JOINs", "The same query repeated many times, differing only by an ID", "Queries that each take several seconds", "Errors about too many open connections" ], "answer": 1, "explain": "N+1 shows up as a long stutter of near-identical single-row lookups - same statement, only the ID changes." }, { "q": "You load a page with 10 rows (12 queries), then with 50 rows (52 queries). What does this tell you?", "choices": [ "The database needs a bigger connection pool", "Query count scales with rows - classic N+1", "The queries are slow and need an index", "Nothing; query count always grows with data" ], "answer": 1, "explain": "Count growing in step with row count is the confirmation. A healthy page's query count stays roughly constant." }, { "q": "Your APM says an endpoint is slow and spends its time in the database, but the trace shows ONE long query, not a stack of identical small ones. What is this?", "choices": [ "Still N+1, just hidden", "A connection leak", "A single slow query - an indexing/query-shape problem, not N+1", "A caching misconfiguration" ], "answer": 2, "explain": "No repetition fingerprint means it isn't N+1. One genuinely slow query is solved by indexes and query shape instead." } ] ``` --- # Fixing it without over-fetching The fix is one idea wearing three outfits: **stop asking one row at a time; ask for everything up front.** That's it. Whether your ORM spells it `includes`, `preload`, `joinedload`, `with`, `selectinload`, or `prefetch_related`, every fix you'll ever apply is the same move - tell the ORM, *before* the loop, that you're going to need the related data, so it can grab it all in one shot instead of dribbling it out N times. The good news: this is usually a one-line change. The catch nobody mentions until you've been burned: doing it carelessly trades one performance bug for another. Let's get both right. ## Fix 1: eager loading (the everyday answer) This is what you'll reach for 90% of the time. **Eager loading** is the opposite of lazy: you announce the relation when you load the list, and the ORM fetches all the related rows ahead of the loop. ```text # before - lazy, fires 1 + N queries orders = Order.all() for order in orders: print(order.customer.name) # after - eager, fires 2 queries total, no matter how many rows orders = Order.all().include(:customer) # the spelling varies per ORM for order in orders: print(order.customer.name) ``` *What just happened:* you added one hint - `include(:customer)` - and the loop body didn't change at all. But now the ORM is smart about it. Under the hood it does this: ```sql SELECT * FROM orders; SELECT * FROM customers WHERE id IN (17, 4, 91, 23, 56, ...); ``` *What just happened:* instead of N single-row lookups, the ORM collected every customer ID from the orders and asked for all of them in **one** query with `WHERE id IN (...)`. Two queries total - the list, then everything it relates to - and that "2" stays "2" whether you have 10 orders or 10,000. This style is often called **preloading** or **select-in loading**: it stays two queries by design, which makes its cost easy to reason about. ## Fix 2: a single JOIN The other shape collapses it all the way to **one** query by joining the tables, so the related data rides along on the same rows. ```sql SELECT orders.*, customers.name FROM orders JOIN customers ON customers.id = orders.customer_id; ``` *What just happened:* one query hands you orders *and* their customer names together, already stitched. Most ORMs expose this as "joined" eager loading (`joinedload`, `JOIN`-based `includes`, etc.). One round trip, zero stutter. When you only need a column or two from the related table, this is often the leanest possible answer. So when do you pick the IN-query (Fix 1) versus the JOIN (Fix 2)? Roughly: ```text JOIN -> great for one-to-one / belongs-to (each order has 1 customer) IN / preload -> safer for one-to-many (each customer has MANY orders) ``` *What just happened:* the rule of thumb has a sharp reason behind it, which is the next section - and it's the part that catches people. ## The trap on the other side: over-fetching Here's the tradeoff the cheerful tutorials skip. A JOIN across a **one-to-many** relation *multiplies rows*. Join customers to their orders and a customer with 50 orders shows up 50 times in the result, with all their customer columns repeated on every single row. Pull a few large relations together and the result set explodes - sometimes a JOIN-based "fix" moves *more* total data over the wire than the N+1 did. This is **over-fetching**: solving "too many queries" by creating "one absurdly fat query." ```text N+1 -> too many queries, each tiny (round-trip cost) fat JOIN -> few queries, but enormous duplicated result (payload cost) preload/IN -> few queries, no row multiplication (usually the sweet spot) ``` *What just happened:* you can see why preloading (Fix 1) is the safe default for one-to-many - it never multiplies rows, because the related rows come back in their *own* query, not glued onto the parent. JOINs shine for to-one relations where there's no multiplication to worry about. The real goal was never "fewest queries at any cost." It's **few queries AND a sensibly-sized result.** Both axes matter. ## The discipline that prevents all of it You don't want to be hunting N+1 forever. Two habits keep it from coming back: ```text 1. Select only the columns you actually render, not SELECT *. 2. Make query count a test: assert this endpoint runs <= K queries. ``` *What just happened:* the first habit shrinks the payload side of the tradeoff - fewer columns means a JOIN multiplies less data and `SELECT *` stops dragging blobs you never display. The second is the real safety net: many test frameworks let you assert "this code path runs at most K queries." Pin K to a small constant and the day someone reintroduces N+1, a test fails *before* production does - which is the whole battle, because N+1 is precisely the bug that hides until production. > Fewest queries is not the goal. *Few queries that move only the data you need* is the goal. Chase only the first number and you'll JOIN your way into an over-fetching bug. For builders: when you eager-load, you're choosing the query shape the ORM generates. If a JOIN-based load is still slow even after killing the N+1, the bottleneck has moved from *how many* queries to *how fast one query is* - that's [why is my query slow](/guides/why-is-my-query-slow) territory (indexes, the join itself, the query plan). ```quiz [ { "q": "What is the core idea behind every N+1 fix?", "choices": [ "Cache the results so the loop never hits the database", "Load all the related data up front instead of one row at a time", "Move the loop into a database stored procedure", "Add an index to the related table" ], "answer": 1, "explain": "Eager loading, JOINs, and batching are all the same move: ask once for everything, before the loop, instead of N times inside it." }, { "q": "Why is preload / IN-query loading often safer than a JOIN for a one-to-many relation?", "choices": [ "It uses fewer total queries than a JOIN", "A JOIN across one-to-many multiplies rows and duplicates parent columns", "JOINs can't be used with ORMs", "Preloading is always faster for every relation type" ], "answer": 1, "explain": "A one-to-many JOIN repeats each parent row once per child, inflating the result. Preloading fetches children in a separate query, so no multiplication." }, { "q": "What does \"over-fetching\" mean in this context?", "choices": [ "Running the same query too many times", "Fetching rows before the user requests them", "Solving N+1 with a JOIN so fat it moves more data than N+1 did", "Loading data into a cache that's never read" ], "answer": 2, "explain": "Collapsing to one query can backfire: a multiplying JOIN with SELECT * can move more total data than the original N+1. Few queries AND a lean result is the goal." } ] ```