The N+1 Query Problem.

🐛The symptom

Your endpoint returns 20 blog posts, each with its author's name. In dev it's instant. In prod with 20 posts it takes 900ms and your DB dashboard shows a spike of tiny queries. What happened?

🔍The code (looks totally innocent)

posts = Post.objects.all()          # 1 query
for p in posts:
    print(p.author.name)            # ← 1 query EACH loop

You ran 1 query for the posts, then N more (one per post) to fetch each author. 20 posts = 21 queries. That's the "N+1". Scale to 500 rows and it's 501 round-trips — each with network + planning overhead.

🧠Why it hides

The killer query isn't in your code — it's triggered lazily when you touch p.author. The ORM makes DB access look like plain attribute access, so the cost is invisible at the call site. It also never shows up with 2 rows of seed data in dev. It only bites when real data arrives.

✅The fix: fetch related data up front

Tell the ORM to JOIN or batch-load ahead of the loop.

Django:

Post.objects.select_related('author')   # SQL JOIN → 1 query total

Rails:

Post.includes(:author)                  # 2 queries: posts, then authors IN (...)

SQLAlchemy:

select(Post).options(joinedload(Post.author))

select_related does a JOIN (best for to-one). For to-many (post.comments), prefer prefetch/includes which runs ONE extra query using WHERE author_id IN (1,2,3...) instead of one per row. 21 queries → 2.

⚖️The nuance (this is the intermediate part)

A giant JOIN isn't always the win. Eager-loading a to-many with JOIN can multiply rows (10 posts × 50 comments = 500 rows shipped, lots duplicated). That's why mature ORMs default to the "IN (...)" batch strategy for collections — 2 lean queries beat 1 fat cartesian one. Rule of thumb: JOIN for to-one, batched IN-query for to-many.

🔎How to catch it before prod

• Log SQL count per request (django-debug-toolbar, Rails Bullet, SQLAlchemy echo).
• Add a test asserting query count: assertNumQueries(2). It fails loudly the day someone adds a lazy .author inside a loop.

🎯Your exercise

Take any list endpoint you have that renders a related field (author, category, owner...). Log the raw SQL count for one request. Is it ~2, or does it grow with the number of rows? If it grows, add select_related/includes/joinedload and watch the count collapse. Bonus: which relations should stay JOIN vs switch to batched IN?