The N+1 Query Problem: How One Page Fires 2,101 Queries (and How to Get Back to 3)

I put a query counter on a single page and loaded it. The page rendered fine. The counter said 101.

That's the N+1 problem. And the reason it survives code review, testing, and staging is the twist most explanations bury: every one of those queries is fast.

Where the 101 comes from

One innocent loop (pseudo-code, parameters skipped for brevity; never build SQL by string concat in real code):

posts = db.query("SELECT * FROM posts LIMIT 100")
for post in posts:
    author = db.query("SELECT ... FROM users WHERE id = ?", post.author_id)
    render(post, author)

One query for the list, plus N queries for the rows. 1 + 100 = 101. Turn on your query log and you can watch it happen: the same SELECT, over and over, one id at a time.

Why "every query is fast" doesn't save you

Each of those queries is indexed. Each takes about a millisecond. The problem is that every query is a round trip to the database, and a round trip costs a few milliseconds on a real network. Sometimes more.

The cost of an N+1 is not query speed. It is count times round trip. A hundred round trips at a few milliseconds each, and one page waits half a second.

It nests

Each comment also loads its author. Now the loop hides another loop:

100 posts x 20 comments each
1 (posts) + 100 (authors) + 2,000 (comment authors) = 2,101 queries

That is 2,101 queries for one page. Not an estimate, just the arithmetic of a nested loop. N+1 fires one query for every node in your object tree. Comments, authors, tags: add a relation and it multiplies again.

The fix: one query per level

Stop asking one row at a time. Collect the ids at each level, fire one batched query per level, and stitch the results in code:

SELECT * FROM posts LIMIT 100
SELECT ... FROM comments WHERE post_id IN (...)
SELECT ... FROM users WHERE id IN (...)

2,101 queries becomes 3. The count now follows the DEPTH of the tree, not the size of it. A million rows? Still 3 queries.

In most frameworks the batch fix is one line, the eager-load option:

• JPA/Hibernate: JOIN FETCH (to-one), @BatchSize or @EntityGraph (to-many)
• Rails: includes / preload
• Django: select_related (to-one) / prefetch_related (to-many)
• GraphQL: DataLoader
• EF Core: Include + AsSplitQuery

The JOIN trap

"Why not just JOIN everything?" For to-one relations, yes: a post and its one author JOIN into one clean row.

But JOIN a post to its 50 comments and the post comes back 50 times, once per comment row. Worse, pagination breaks: LIMIT 100 counts joined rows, not posts, so you ask for 100 posts and get 2.

The rule: to-one, JOIN. To-many, batch with IN.

Why nobody notices until production

The ORM hides the queries. You write post.author and a SELECT fires behind your back. In development you have 10 rows and the page is instant. In production you have 10,000, one page fires thousands of queries, each query holds a database connection while it runs, the pool empties, every request waits, and the page you tested a hundred times goes down.

A lot of "it worked yesterday" outages are exactly this.

Catch it in 60 seconds

Turn on query logging in development, load ONE page, read the log:

• Hibernate: spring.jpa.show-sql=true
• Rails: config.log_level = :debug
• Django: enable logging for django.db.backends
• Prisma: log ["query"]

The same SELECT repeating with different ids means that page has an N+1. If the query count grows when your data grows, same disease.

When it's fine

Not every N+1 is worth fixing. Three items on a dashboard? Leave it. Don't over-engineer. Fix it when N grows with your data. The senior move isn't fixing every one, it's knowing which ones matter.

Fast pages aren't about fast queries. They're about fewer of them.

Watch it happen

The full episode shows the counter climbing to 2,101 and collapsing to 3:

Numbers are from the demo in the video (generic posts/comments/authors schema), with the round trip simulated to model a remote database. Same-datacenter latency is sub-millisecond; cross-network can be tens of ms. The lesson is count times YOUR latency.

What's the worst N+1 you've found in production?

Comments

[Luis]

Great breakdown of a problem that still appears in many production applications. The N+1 query issue is a perfect example of how code that looks clean at the application layer can create serious performance problems underneath.

I like the emphasis on measuring first — query logs, profiling, and database analysis usually reveal the real bottleneck faster than guessing. Solutions like eager loading, batching, and carefully designed data access patterns can dramatically reduce unnecessary database work.

This is also a good reminder that scalability is often about understanding the interaction between application code and the database, not just optimizing one side. Great practical example!

[Dilip V P]

Thanks Luis. The "looks clean at the application layer" part is the whole trap. The loop reads like normal, idiomatic code, so review never questions it. That is why I push the query log so hard: 30 seconds of measuring beats an hour of guessing.

[Mads Hansen]

The “every query is fast” point is the part that catches teams off guard.

N+1 is rarely a single scary query. It is a thousand boring ones, each individually defensible, multiplied by object shape and network latency. That makes it especially easy for code review to miss and for staging to understate.

One extra production guard I like is treating query count as a first-class page/workflow metric, not just latency:

• per-request query count
• repeated query fingerprint count
• max query budget by route
• pool wait time
• rows returned vs rows rendered
• alert when count scales with fixture size

That also matters for AI/database tools. If an agent can ask follow-up questions or inspect related records, it needs the same budget thinking: not just “was the SQL valid?”, but “did this workflow explode into avoidable database round trips?”

Fast queries still need a budget. Otherwise the database pays for every hidden loop.

[Dilip V P]

This list is gold. The one I would adopt first is your last point: alert when query count scales with fixture size. That is an automated N+1 detector. A test that says "this endpoint gets a budget of X queries" fails the moment someone adds a lazy relation, long before staging gets a chance to understate it.

Also agree on the agent side. An agent that asks follow-up questions is a loop you did not write, so it needs the same budget thinking. "Was the SQL valid" is table stakes. "How many round trips did this workflow cost" is the real metric.

Fast queries still need a budget. That is the sentence I wish I had put in the video.