Have you ever built an API endpoint that looks dead simple, listing 50 blog posts with each author's name, then watched it take 2-3 seconds in testing?
After checking the database log, the database server was being bombarded with 51 separate queries just to serve that one request. The data itself was nothing special.
This phenomenon is widely known as the N+1 Query Problem. It is one of the most classic performance anti-patterns, and it traps backend developers who use an ORM all the time.
Key takeaways
- N+1 Query runs one initial query to fetch the main list, then N extra queries one at a time inside a loop.
- The root cause is Lazy Loading, the behavior that ships as the default in almost every ORM.
- The fix is Eager Loading, fetching the relation up front, through joinedload or selectinload in SQLAlchemy and select_related or prefetch_related in Django.
- The payoff is large: dozens of database round-trips shrink to one or two queries.
What Is the N+1 Query Problem?
Put simply, N+1 Query happens when your application does two things:
- 1 initial query to fetch the main data list, rows of it, for example
SELECT * FROM posts LIMIT 50;. - N extra queries to fetch the related data one row at a time inside a loop, for example
SELECT * FROM authors WHERE id = 1;, then id = 2, and so on for 50 iterations.
The total number of queries executed against the database becomes 1 + N = 51 queries. Now imagine 1,000 people hitting that page at once. Your database floods with tens of thousands of round-trips that could have been settled in one or two queries.
To picture the waste, try this shopping analogy. You are told to buy 10 different items at a minimarket.
- The N+1 way: buy milk, drive home. Drive back to the minimarket, buy bread, drive home again. Repeat 10 times. The time goes into the road, not the shopping.
- The efficient way: bring the full shopping list, grab all 10 items at once, pay at the register, then head home in one trip.
That back-and-forth drive is network latency. The more often you shuttle small queries back and forth, the slower your API response gets.
Why Does the ORM Keep Trapping Us?
Almost every modern ORM uses Lazy Loading by default.
What that means: related data is not loaded into memory until its attribute is accessed explicitly in code. At a glance that is practical and saves memory. But the moment you run a for loop over the related objects, the ORM executes a fresh query on every iteration without telling you. No error, no warning, just slowness. ๐
The Fix: Eager Loading vs Lazy Loading
The main solution is to apply Eager Loading, often also called pre-fetching.
Instead of waiting for the relation to be accessed one row at a time, you tell the ORM up front: "Please bring the related data along in one JOIN query or one IN (...) query." The difference can be drastic, from 51 queries down to 1.
Code Examples: How to Fix It in Python
1. In SQLAlchemy (FastAPI / Flask)
# The N+1 problem (Lazy Loading)
posts = db.query(Post).all()
for post in posts:
print(post.author.name) # Triggers a new query on every iteration!
# Fix: Eager Loading with joinedload or selectinload
from sqlalchemy.orm import joinedload
posts = db.query(Post).options(joinedload(Post.author)).all()
for post in posts:
print(post.author.name) # Author data was already loaded in one go via JOIN!
2. In Django ORM
# The N+1 problem
posts = Post.objects.all()
for post in posts:
print(post.author.name) # Repeated query against the Author table
# Fix for ForeignKey / OneToOne: select_related
posts = Post.objects.select_related('author').all()
# Fix for ManyToMany / Reverse ForeignKey: prefetch_related
posts = Post.objects.prefetch_related('tags').all()
When Is Lazy Loading Still Reasonable?
Eager loading does not mean you should delete every lazy loading call. There are times when lazy loading is actually the cheaper choice.
- When the relation is only accessed on a small subset of rows, for example a single post's detail page, lazy loading is fine.
- When you prefetch a huge relation table but only need a few columns from it, your application memory can balloon instead.
- Most importantly, get in the habit of checking the query log or using a profiling tool during development. Tools like Django Debug Toolbar or the
echo=Trueoption in SQLAlchemy show immediately when your queries suddenly pile up.
N+1 Query is a classic trap that stays invisible on localhost with a handful of dummy rows, then pushes the server straight into timeout once the data reaches the thousands. So inspect your queries early, and reach for eager loading like joinedload in SQLAlchemy or select_related in Django whenever you deal with a large set of relations.
Happy databasing, and may your queries always be one. ๐