Django's N+1 Problem: Choosing Between select_related and prefetch_related

Your book list page renders 100 rows. It also fires 101 SQL queries. The page works, tests pass, and nobody notices until production data grows and the database CPU graph starts climbing. This is the N+1 problem, and Django's ORM makes it easy to create by accident — and straightforward to fix once you know which of its two tools to reach for.

The thesis is simple: select_related and prefetch_related solve the same symptom with different mechanisms, and picking the wrong one (or applying both blindly) can make things worse. The choice comes down to the cardinality of the relationship you're traversing.

Why the ORM hides the extra queries

Django QuerySets are lazy: nothing hits the database until you iterate. When you then access a related object — say book.author.name inside a loop or template — the ORM quietly issues one query per row to fetch that author. One query for the books, plus N for the authors. The ORM caches each related object after fetching, so repeated access to the same author is free, but 100 books by 100 different authors means 100 extra round trips.

Round trips are the real cost. Even a 1 ms query becomes 100 ms of pure latency when serialized, before you count connection overhead and lock contention.

select_related: a JOIN for single-valued relations

select_related tells Django to fetch the related object in the same query using a SQL JOIN. It works on single-valued relationships: ForeignKey and OneToOneField. The result is one query, however many rows you render:

# Before: 1 + N queries
books = Book.objects.all()
for book in books:
    print(book.author.name)  # extra query per book

# After: exactly 1 query
books = Book.objects.select_related('author')
for book in books:
    print(book.author.name)  # already loaded

You can chain across relations with double-underscore syntax — select_related('author__publisher') — but each hop widens the JOIN. On nullable ForeignKeys Django emits a LEFT OUTER JOIN, which is fine, but a chain across four or five relations can produce a wide, expensive result set where the database does more work than the N+1 would have. Join what the view actually renders, not everything reachable.

prefetch_related: a second query for multi-valued relations

For many-to-many fields and reverse ForeignKeys, a JOIN is the wrong tool: joining a book to its five genres would return the book row five times, and Django would have to deduplicate. prefetch_related instead runs one separate query per relationship — SELECT ... WHERE book_id IN (...) — and stitches the results together in Python.

# 2 queries total: books, then genres for all of them
books = Book.objects.prefetch_related('genres')
for book in books:
    print([g.name for g in book.genres.all()])

Combining both is the common real-world pattern: Book.objects.select_related('author').prefetch_related('genres') renders a 100-book list with authors and genre badges in 3 queries instead of 201.

When you need the prefetched set filtered or ordered, use a Prefetch object:

from django.db.models import Prefetch

books = Book.objects.prefetch_related(
    Prefetch('reviews',
             queryset=Review.objects.filter(approved=True)
                                  .order_by('-created_at'),
             to_attr='visible_reviews')
)

Two things to know: a plain prefetch_related('reviews') cannot be filtered by the main queryset's WHERE clause — that's what the custom queryset argument is for. And if you use to_attr, you must access book.visible_reviews (a list); calling book.reviews.all() afterwards ignores the prefetch and queries again.

The trade-off: eagerness costs memory

Neither method is free. select_related duplicates the joined columns across every row, and prefetch_related materializes the entire related set in Python memory. If you prefetch genres on a view that never renders them, you've added a query and allocated objects for nothing. The right granularity is per-view: look at what the template or serializer touches, and prefetch exactly that.

A related trap is adding these methods to a shared manager or base queryset "to be safe." That helps the list view and silently taxes every other caller, including admin changelists and background tasks that only needed a count.

Verify it, then lock it in

Don't guess — count. Django Debug Toolbar shows per-request queries in development. In tests, assertNumQueries turns the fix into a regression guard:

class BookListTests(TestCase):
    def test_query_count(self):
        with self.assertNumQueries(3):
            response = self.client.get('/books/')
            # force template rendering of related fields
            self.assertContains(response, self.author.name)

Run this with python manage.py test in your project (it needs a test database, which Django creates and destroys automatically). If the test fails with a higher count, something in the view is still hitting the database lazily — str(queryset.query) in a shell shows whether you got a JOIN (select_related) or should expect a follow-up query (prefetch_related).

The actionable habit: when a view touches related objects in a loop, ask one question — is the relationship single-valued or multi-valued? ForeignKey or OneToOne gets select_related; many-to-many or reverse FK gets prefetch_related. Then add an assertNumQueries test so the next refactor can't quietly bring the N+1 back. Behavior described here applies to long-stable Django ORM APIs (Django 3.2 through current releases); queryset internals can vary by version, so verify against your own.