skip to content

Nested Payload Cost

2 roadmaps4 questions

Nested serializers and SerializerMethodField fire a query per row unless get_queryset() adds select_related or prefetch_related. Interviewers ask how you find and fix a slow list endpoint.

part ofDjango REST Frameworkoverview, primer and where to startread it →
on this pageshow

explore

questions

4

A Django REST Framework article list endpoint with nested author and tags now runs hundreds of queries per page; how do you find and fix the cause?

level: seniorimportance: must knowfreq 62%

answer

  1. count first, then read
  2. repeated SELECTs differing only by id
  3. map each to a serializer field
  4. one plan in get_queryset(), then a guard

basics

~20 s

Count the queries per request, find the SELECTs repeated per article, map each to the serializer field reading that relation, then load it in get_queryset(): select_related('author'), prefetch_related('tags'), annotate() for counts. Pin the result with a query-count test.

solid answer

~40 s

First measure: reproduce the page and count its SQL. The signature is one SELECT for the page, then the same SELECT repeated per article with a different id. Map each repeated query to the serializer field that caused it: a nested `AuthorSerializer` reading `article.author`, `tags` calling `article.tags.all()`, a `SerializerMethodField` running `count()`. Usually a recent serializer change added the field, because DRF never adjusts the queryset for you. Then fix it in the view's `get_queryset()`: `select_related('author')` for the foreign key, `prefetch_related('tags')` for the many-to-many, `annotate(Count(...))` for the counter, and rewrite method fields to read those attributes. A page of 50 should drop from about 150 queries to a handful, independent of page size. Finally, add a test asserting the query count so the next field cannot regress it.

code

python · 18 lines
python
from django.db.models import Count
from rest_framework import viewsets

from blog.models import Article
from blog.serializers import ArticleSerializer


class ArticleViewSet(viewsets.ReadOnlyModelViewSet):
    serializer_class = ArticleSerializer

    def get_queryset(self):
        return (
            Article.objects
            .select_related("author__team")          # author + author.team
            .prefetch_related("tags")                # tags = TagSerializer(many=True)
            .annotate(comment_count=Count("comments"))
            .order_by("-published_at")
        )

go deeper

for a junior

Learn to recognise the pattern: one query for the list, then the same query repeated for every row. The fix goes in the view's get_queryset().

for a middle

Map each repeated query to the serializer field behind it, and pick select_related, prefetch_related or annotate for each relation kind.

for a senior

Run the whole loop: measure, map, fix in one plan, then pin it with a query-count test and, on Django 6.1, a strict fetch mode for hot endpoints.

for a principal

Make query budgets part of the API's definition of done, so serializer changes carry their loading plan and a test in the same review.

## The scenario `GET /api/articles/?page=3` used to take 40 ms and now takes 900 ms. The `ArticleSerializer` renders a nested `author` object, a `tags` list and a `comment_count`. Nothing changed in the view. Something changed in the serializer: in Django REST Framework (DRF), a serializer change *is* a query change, because DRF never adds `select_related()` or `prefetch_related()` for you. ## Step 1: measure the query count Reproduce the request and count the SQL it emits, using whatever query inspection your project already has: a debug toolbar in development, logged SQL, or a test that counts queries. You are looking for the shape, not the total: - 1 query for the pagination count, 1 for the page of articles; - then **the same SELECT repeated 50 times**, differing only in the id it filters by — once per article. Repeated queries that differ only by a foreign-key value are the per-row fingerprint. How to read that output in general belongs to the SQL-inspection topic; here the question is which serializer field emits each group. ## Step 2: map every repeated query to a field | Repeated SQL | Serializer field that caused it | Fix in `get_queryset()` | |---|---|---| | `SELECT ... FROM blog_author WHERE id = %s` | `author = AuthorSerializer(read_only=True)` | `select_related("author")` | | `SELECT ... FROM blog_team WHERE id = %s` | `team = serializers.CharField(source="author.team.name")` | `select_related("author__team")` | | `SELECT ... FROM blog_tag INNER JOIN blog_article_tags ...` | `tags = TagSerializer(many=True, read_only=True)` | `prefetch_related("tags")` | | `SELECT COUNT(*) FROM blog_comment WHERE article_id = %s` | `SerializerMethodField` calling `obj.comments.count()` | `annotate(comment_count=Count("comments"))` | Two DRF facts help the mapping: 1. A to-many field — nested `many=True` or a list of pks — always reads the manager with `.all()`, so it costs a query per row unless prefetched. 2. A single `PrimaryKeyRelatedField` uses a pk-only shortcut and costs nothing; the moment someone swaps it for a nested serializer or a `StringRelatedField`, the relation is loaded per row. That swap is the classic "nothing changed in the view" regression. ## Step 3: fix it in one place Put the whole loading plan in the view's `get_queryset()`, which `list()` passes through filtering and pagination and `retrieve()` passes through `get_object()`: - `select_related()` for forward foreign keys and one-to-ones, including chains (`author__team`); - `prefetch_related()` for many-to-many and reverse relations; use `Prefetch(..., queryset=...)` to trim columns or filter; - `annotate()` for counts and flags, then render them with read-only fields instead of method fields. With pagination on, the prefetch runs for the page's rows only. The result for a page of 50 is roughly: one count, one article query with the author and team joined and the comment count computed, one tag query — about three queries, whatever the page size. ## Step 4: guard against the next regression - Add a test that requests the list with several rows and asserts a fixed number of queries; the number must not grow with the row count. - On **Django 6.1**, the queryset's `fetch_mode(models.FETCH_RAISE)` makes a lazy foreign-key or one-to-one access raise `FieldFetchBlocked` instead of querying — a strict mode for tests of hot endpoints. `FETCH_PEERS` is a production safety net that loads a missing foreign key for all instances from the same queryset at once. - Neither fetch mode covers many-to-many or reverse-FK **managers** such as `article.tags.all()`: those still need `prefetch_related()`. ## Where to be careful with `annotate()` - A `Count()` over a multi-valued relation joins that relation into the main query. Combined with another join over a second multi-valued relation, counts can multiply; `Count("comments", distinct=True)` or a subquery avoids it. Prefetches run as separate queries, so `prefetch_related("tags")` does not interfere. - The annotation name must match the serializer field name (or its `source=`), or the field raises an attribute error at render time. ## Mistakes to avoid while fixing - **Prefetching in the serializer** (e.g. in `to_representation()`): by then rows are already loaded one page at a time. - **`select_related()` on a many-to-many**: it does not follow multi-valued relations, and Django raises an error for a relation it cannot join. - **Over-fetching**: a `prefetch_related()` for a field only the detail view renders costs a query on every list page; switch plans per action if needed.

  • Would switching the queryset to Django 6.1's FETCH_PEERS mode alone have fixed this endpoint?
    Partly. `FETCH_PEERS` batches lazy foreign-key and one-to-one loads, so `article.author` would drop to one query per page. It does not apply to many-to-many or reverse-FK managers, so `article.tags.all()` would still query per article, and a `count()` in a method field would too.
  • Why can the detail route be fine while the list route is slow with the same serializer?
    Detail serializes one object, so each relation costs one query in total; list multiplies it by the number of rows. Both go through `get_queryset()`, so the same loading plan fixes both, though a heavy prefetch only the detail view needs may be worth switching off for list.
  • Where does the loading plan live when list and detail use different serializers?
    In `get_queryset()`, branching on `self.action` in a viewset (for example `'list'` versus `'retrieve'`), next to `get_serializer_class()`, which branches the same way. Keeping both decisions side by side makes it obvious when a serializer gains a relation its queryset does not load.

saying these in an interview costs you the question

  • Fix it by caching the whole response instead of finding the per-row queries.
  • Add select_related('tags') to join the many-to-many into the main query.
  • The view did not change, so the serializer cannot be the cause.
  • FETCH_PEERS on Django 6.1 removes the need for prefetch_related() on tags.
  • Load the relations inside the serializer's to_representation() for each object.
open as a page

In a Django REST Framework generic view, where do select_related() and prefetch_related() go so a nested serializer stops querying per row?

level: juniorimportance: should knowfreq 52%

basics

~20 s

They go on the view's queryset: the queryset class attribute or, usually, an overridden get_queryset(). DRF never optimises queries for you, and the serializer only reads attributes, so the view must load author with select_related() and tags with prefetch_related().

open as a page

In Django REST Framework, why does a SerializerMethodField often bring back N+1 queries even after the view prefetches, and how do you fix it?

level: middleimportance: should knowfreq 46%

basics

~20 s

A SerializerMethodField calls get_<field>(obj) once per object, and any ORM query built there runs per row. A filter() on a prefetched relation ignores the prefetch cache. Fix it with annotate() or Prefetch(to_attr=...) in get_queryset(), then read the attribute.

open as a page

Django REST Framework takes seconds to serialize 5,000 articles although the query count is constant; where does the time go, and what helps?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

The time goes to Python: DRF calls get_attribute() and to_representation() for every field of every row, and nested serializers, method fields and hyperlinks multiply that. Paginate, use a slim read-only list serializer, and never build a serializer per row.

open as a page