N+1 in Django: select_related, prefetch_related, fetch modes

Article.

An N+1 is one query for a list, then one more query per item when the code touches a relation the list did not load. Django makes it easy to write and hard to see: book.author.name in a template looks like attribute access and is a database round trip.

How do you prove an endpoint has no N+1, and fix one without loading more than you need? Every count below comes from a runnable script on Django 6.1.2 with SQLite: 100 books, each with an author (5 kB bio), an optional editor, two tags and two reviews.

The problem, counted

for book in Book.objects.all():          # 1 query
    print(book.author.name)              # +1 per book

Count queries in tests, not by eye:

from django.db import connection
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    render_book_list()
assert len(ctx.captured_queries) <= 3, [q["sql"] for q in ctx.captured_queries]

self.assertNumQueries(n) in TestCase, or pytest-django’s django_assert_max_num_queries, do the same. (connection.queries is only filled when DEBUG=True; the context manager works regardless.)

select_related: one query with a JOIN

For forward ForeignKey and OneToOneField. Django adds the related table to the query and builds both objects from each row:

select_related("author")   ... FROM "shop_book" INNER JOIN "shop_author" ON ...
select_related("editor")   ... FROM "shop_book" LEFT OUTER JOIN "shop_author" ON ...

A non-null FK becomes an INNER JOIN; a nullable one a LEFT OUTER JOIN, so books without an editor are kept with book.editor = None. It follows chains (select_related("author__publisher")); called with no arguments it follows every non-null FK, which is rarely what you want.

prefetch_related: a second query, joined in Python

For reverse FKs, many-to-many, and generic relations. Django runs the main query, collects the primary keys, then runs one query per relation:

SELECT ... FROM "shop_book"
SELECT ("shop_book_tags"."book_id") AS "_prefetch_related_val_book_id", "shop_tag".*
  FROM "shop_tag" INNER JOIN "shop_book_tags" ON ...
  WHERE "shop_book_tags"."book_id" IN (...)

The results are matched to parents in Python and stored in each instance’s prefetch cache, which book.tags.all() then reads.

What keeps the prefetch, and what throws it away

.all(), and .count() or .exists() on it, use the cache. Any new queryset method on the related manager (filter, exclude, order_by) builds a fresh query per book, and the prefetch is wasted. Filter inside the prefetch instead:

books = Book.objects.prefetch_related(
    Prefetch("reviews", queryset=Review.objects.filter(stars=5), to_attr="top")
)
for b in books:
    b.top            # a plain list, already filtered

to_attr keeps the filtered list separate from book.reviews, so code that expects all reviews is not silently given a subset.

Which one, and when the choice matters

The relation’s direction usually decides: forward FK, select_related; reverse FK or M2M, prefetch_related. But prefetch_related also works on a forward FK, and sometimes it is better:

select_related("author"):    ~501 kB of column values for 100 books
prefetch_related("author"):  ~50 kB for the 10 distinct authors

The JOIN repeats each author’s 5 kB bio on every one of its books’ rows. With few distinct parents and wide parent rows, two queries ship a tenth of the data. With many distinct parents and narrow rows, the JOIN wins. Chaining select_related across several to-one hops is fine; joining across several collections multiplies rows and is why Django refuses select_related on M2M.

Django 6.1: fetch modes

Django 6.1 lets a queryset decide what happens when code touches a field it did not load:

from django.db import models

Book.objects.fetch_mode(models.FETCH_PEERS)   # load it for all peers at once
Book.objects.fetch_mode(models.FETCH_RAISE)   # refuse: FieldFetchBlocked
# models.FETCH_ONE is the classic behaviour, one query per instance

FETCH_PEERS turns the forward-FK and deferred-field N+1 into 2 queries without naming the relation, but reverse FKs and M2M still need prefetch_related. FETCH_RAISE is the useful one in tests and development: Fetching of Book.author blocked. points at the exact line that would have queried.

Where it breaks in production

The column-level N+1

Book.objects.only("title") and then a template reading book.isbn: 101 queries, one per book, to load a deferred column. only() and defer() are optimisations that turn into N+1s the moment someone touches another field. FETCH_PEERS or FETCH_RAISE makes this visible.

Filtering a prefetched relation in a loop

A serializer method does obj.reviews.filter(approved=True). The view already prefetches reviews, the query count is still N+1 (102 here), and nobody understands why. Move the filter into a Prefetch with to_attr.

Prefetching too much

prefetch_related on 50,000 parents sends an IN list of 50,000 ids and holds every child in memory: the endpoint got faster and the worker’s memory doubled. Paginate first, or stream with iterator(chunk_size=...), which since Django 4.1 prefetches per chunk: 20-book chunks over 100 books ran 6 queries (one for the books, one per chunk).

Async views

In an async view, touching an unloaded relation does not cost a query, it raises: SynchronousOnlyOperation: You cannot call this from an async context. Load relations explicitly (select_related, prefetch_related, or aprefetch_related_objects); the same mistake in SQLAlchemy async raises MissingGreenlet.

Hidden N+1s

Properties that query (@property def latest_order), permission checks per object, __str__ that reads a relation (admin list pages), DRF SerializerMethodFields. They do not look like relation access at all.

Detecting it at three layers

• Tests: query-count assertions on every list endpoint, and FETCH_RAISE in the test settings’ querysets where practical. nplusone, often recommended, has had no release since 2018.
• APM: Sentry’s N+1 detector, or the count of database spans per request in OpenTelemetry traces.
• Database: in pg_stat_statements an N+1 is a key lookup with a huge calls count and one row per call. There, the loop above cost the database 21 ms and the client 1.1 s in round trips (pg_stat_statements).

The trade not taken

Eager loading still builds every related object in Python. When the page only needs a number or a short list, let the database compute it: annotate(review_count=Count("reviews")), a Subquery for the latest item, or ArrayAgg / JSONBAgg from django.contrib.postgres to return children inside the parent row. values() skips model instances entirely.

Denormalised counters avoid the work at read time. These cost flexibility and sometimes correctness under concurrency, but on hot list endpoints they beat any loading strategy.

Related

• N+1 query problem - N+1 across ORMs
• DRF Performance and the N+1 Problem - N+1 in DRF serializers
• Loading Strategies and N+1 - SQLAlchemy loader strategies
• Resolvers, N+1, and DataLoader - batching in GraphQL
• Async Django, Channels, and Async ORM - async Django