Anas Issath

Posted on Oct 29 Originally published at levelup.gitconnected.com

select_related vs prefetch_related: Stop Memorizing, Start Understanding

#django #python #orm #performance

One question tells you which to use every single time, and it's not "which method do I remember?"

These two methods are how Django developers fix the N+1 query problem, the single most common ORM performance bug. They're also the pair people most often memorize wrong, apply backwards, and never quite feel sure about. You look it up, you copy the one that seems right, the error goes away, and you move on still not really knowing why it was that one.

Let's end that. There's a single question that tells you which to reach for, every time, with zero memorization. The answer isn't arbitrary: it falls directly out of how relational databases physically work. Once you see why there are two methods, you'll never confuse them again.

We'll start with the problem they both solve, then the one question, then the two traps that catch even experienced developers.

The Problem They Both Solve (a 60-Second Refresher)

You list 100 books and print each author's name:

for book in Book.objects.all():        # 1 query for the books
    print(book.author.name)            # 1 query PER book to get the author

That's 101 queries: one for the books, then one more every time you touch book.author, because Django didn't load the authors up front and has to run out to the database for each one.

This is the N+1 problem, and both select_related and prefetch_related exist to collapse those hundred extra queries down to one or two. They're the same goal ("load the related data efficiently, up front") achieved two different ways.

The only question is which way. And that's where people flail. So here's the question that settles it.

The One Question: Does This Relationship Point to ONE Thing, or MANY Things?

That's it. That's the whole decision. Forget the method names for a second and just look at the shape of the relationship you're crossing:

Does each object point to exactly ONE related object? → select_related
Does each object point to MANY related objects? → prefetch_related

Map it to Django's field types and it's completely mechanical:

Relationship	Field type	Shape	Use
A book has one author	`ForeignKey`	One	`select_related`
A user has one profile	`OneToOneField`	One	`select_related`
An author has many books	Reverse `ForeignKey`	Many	`prefetch_related`
A post has many tags	`ManyToManyField`	Many	`prefetch_related`

You don't memorize "select_related is for ForeignKey." You ask "one or many?" and the field type answers it for you. ForeignKey and OneToOne point at one thing; reverse-ForeignKey and ManyToMany point at many. One → select. Many → prefetch. Done.

But why? Why does "one vs many" force different methods? Because of how a database actually combines tables, and understanding that is what makes this permanent instead of memorized.

Why "One" Gets a JOIN (`select_related`)

select_related works by adding a SQL JOIN. It fetches the object and its related object together, side by side, in a single query:

books = Book.objects.select_related("author")   # ONE query, with a JOIN
for book in books:
    print(book.author.name)                     # already loaded - no extra query

The SQL is essentially "get each book and glue its author's columns onto the same row." That works beautifully when each book has one author, because gluing one author onto one book row keeps things tidy: 100 books → 100 rows, each carrying its author. One clean query, no duplication.

This is why select_related is restricted to single-valued relationships (ForeignKey, OneToOne). Django's own docs are explicit that it's limited to single-valued relationships, and the reason is the next section. A JOIN is the right tool precisely because there's one thing on the other side to glue on.

Why "Many" Can't Use a JOIN, and Gets a Second Query Instead (`prefetch_related`)

Now picture joining across a many relationship. An author has 50 books. If you JOIN author-to-books, the database can't put 50 books on one author row, so it duplicates the author across 50 rows, one per book.

Do that for 100 authors with 50 books each and your result set explodes into 5,000 rows, most of it the same author data repeated over and over. That's wasteful and slow, and it's exactly why Django refuses to use a JOIN here. The docs describe it as avoiding "the much larger result set" that joining across a many-relationship produces.

So prefetch_related takes a completely different strategy: two separate queries, joined in Python.

authors = Author.objects.prefetch_related("books")   # TWO queries, not a JOIN
for author in authors:
    for book in author.books.all():                  # already loaded - no extra query
        print(book.title)

Here's the mechanism, and it's clever:

Django runs query #1 to get all the authors.
Then it runs query #2: a single query that grabs all the books belonging to all those authors at once (WHERE author_id IN (...)).
Then, in Python memory, Django matches each book to its author.

Two queries total, no matter how many authors or books, and no exploded result set, because there's no JOIN. Each object's data is fetched exactly once.

That's the whole "why":

One relationship → a JOIN is efficient → select_related.
Many relationship → a JOIN explodes → separate query matched in Python → prefetch_related.

The two methods aren't arbitrary API choices you memorize. They're the only two sensible ways to load related data, each matched to the relationship shape that makes it efficient.

The reframing to keep: you're not choosing a method. You're identifying a shape (one or many), and the correct method is forced by how databases handle that shape.

Following Chains, and Combining Both

Real queries cross several relationships, and both methods follow chains with the double-underscore syntax:

# Follow FK chain: book → author → publisher (all "one" hops)
Book.objects.select_related("author__publisher")

# Combine them: each book has ONE author (select) and MANY tags (prefetch)
Book.objects.select_related("author").prefetch_related("tags")

That last line is the real-world pattern. A single query is usually a mix of "one" hops and "many" hops, so you select_related the single-valued ones and prefetch_related the multi-valued ones in the same queryset. Apply the "one or many?" question to each relationship you touch, and chain the right tool for each.

Trap 1: Filtering a Prefetch Silently Re-Queries (the One That Catches Everyone)

This is the trap that bites experienced developers, and it's worth internalizing.

prefetch_related caches the related objects and attaches them to the parents, so author.books.all() reads from the cache, no extra query. But the instant you filter the prefetched relationship, Django ignores the cache and runs a fresh query per parent:

authors = Author.objects.prefetch_related("books")

for author in authors:
    author.books.all()                    # ✅ uses the prefetch cache - no query
    author.books.filter(published=True)   # ❌ IGNORES the cache - new query PER author

Why? Because Django prefetched all the books, but it can't know in advance which filter you'll apply, so a new .filter() can't be served from the "all books" cache. It goes back to the database. You've silently reintroduced the N+1 you were trying to kill.

The fix is the Prefetch object, which lets you customize the query used for prefetching (including filtering it) and store the result under a new attribute name with to_attr:

from django.db.models import Prefetch

authors = Author.objects.prefetch_related(
    Prefetch(
        "books",
        queryset=Book.objects.filter(published=True),
        to_attr="published_books",        # store the filtered result here
    )
)

for author in authors:
    for book in author.published_books:   # ✅ pre-filtered, cached - no extra query
        print(book.title)

Now the filtered set is fetched efficiently up front and cached under published_books.

Bonus: because to_attr stores it in its own attribute, you can prefetch the same relationship multiple times with different filters (e.g. published_books and draft_books) without them clobbering each other.

Any time you catch yourself writing .filter() on a prefetched relationship inside a loop, reach for Prefetch.

Trap 2: `prefetch_related` Can Do ForeignKey, but You Usually Shouldn't

A subtle point: prefetch_related technically works on single-valued relationships too (ForeignKey, OneToOne). But for those, select_related is more efficient, because one JOIN beats two separate queries when the JOIN doesn't cause duplication.

So "it works" isn't "it's right." Keep the rule clean: single-valued → select_related (unless you have a specific reason not to JOIN, like joining would produce an enormous row set for other reasons). The "one or many?" question still gives you the right default every time.

Why Understanding This Beats Memorizing It

Almost every developer eventually memorizes "select_related for ForeignKey, prefetch_related for ManyToMany." That surface rule works, right up until it doesn't.

A reverse ForeignKey (which is "many" but still a ForeignKey) trips the memorizers up, because their rule was about field names, not relationship shapes. The person who memorized sees "ForeignKey," reaches for select_related, and gets an error or a bad query. The person who understands asks "does an author point to one book or many?", sees "many," and reaches for prefetch_related without hesitation.

Same field type, opposite answer, and only the mental model gets it right.

That's the whole reason this article exists. The two methods look like trivia to memorize, but they're actually a direct, physical consequence of how relational databases combine data. A JOIN is efficient for one-to-one gluing and disastrous for one-to-many multiplication, so Django gives you a JOIN-based tool for the first shape and a separate-query tool for the second.

Hold that (one gets a JOIN, many gets a second query, because that's what the database can do efficiently) and you never have to look it up again. You'll just glance at a relationship, see its shape, and know. That's the difference between using the ORM and understanding it.

The 30-Second Takeaway

Both select_related and prefetch_related fix the N+1 query problem: loading related data up front instead of one query per object. The only question is which to use.
The one question: does the relationship point to ONE thing or MANY? One → select_related. Many → prefetch_related. The field type answers it: ForeignKey/OneToOne point to one; reverse-ForeignKey/ManyToMany point to many.
select_related uses a SQL JOIN (single query), efficient for "one" relationships because gluing one related row onto each object stays tidy. It's limited to single-valued relationships for exactly this reason.
prefetch_related uses a separate query matched in Python, because JOINing a "many" relationship would explode the result set (an author repeated across 50 book rows). It runs 2 queries total and matches in memory.
Chain and combine: follow relations with author__publisher, and mix select_related("author").prefetch_related("tags") when a query has both shapes.
Trap 1: calling .filter() on a prefetched relationship ignores the cache and re-queries per object. Use a Prefetch object (with to_attr) to filter efficiently up front.
Trap 2: prefetch_related works on ForeignKeys too, but select_related is more efficient there. Keep single-valued → select_related.
Don't memorize field-name rules (they break on reverse ForeignKeys). Understand the shape: one gets a JOIN, many gets a second query, because that's what the database does efficiently.

Your Turn

Next time you write a queryset, don't reach for a remembered method. Glance at each relationship and ask "one or many?" The right tool falls out on its own, and the N+1 problem stops being something you fix and starts being something you never create.

Has the prefetch-filter trap ever bitten you? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

Top comments (1)

Ethan Moore • Oct 29

The one-or-many framing is a good way to remember it, and the row-explosion example makes it clear why a JOIN is the wrong tool for many-side relations. One tip from experience: when you need a filtered or ordered subset on the many side, use Prefetch() with a custom queryset and to_attr, otherwise calling .filter() on the prefetched manager silently throws away the cache and brings back the N+1. Do you also check query counts in tests, for example with assertNumQueries, so these regressions get caught?