Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 39 ยท Testing and Deployment

Query Optimization for DRF APIs

Make Django REST Framework endpoints fast: find N+1 queries, use select_related, prefetch_related, annotate, only and defer, avoid method-field traps, and lock query counts in with tests.

Most slow DRF endpoints are not slow because of Python. They are slow because of the number of database queries. A serializer that touches a related object for every row quietly turns one request into hundreds of queries, and nobody notices on a development database with ten rows. In production, with ten thousand rows and a network round trip to the database for each query, the endpoint times out.

This lesson teaches you how to see the queries, the three standard fixes (select_related, prefetch_related, annotate), and how to keep an endpoint fast with tests. The examples count queries with CaptureQueriesContext, the same tool you met in Testing DRF APIs.

The N+1 problem

One query Fetch the list of books
N more queries Fetch each book's author one by one
Slow endpoint 100 books means 101 queries

The serializer asked for book.author, and Django lazily loaded it

1 / 2

Step 1: make the queries visible

You cannot fix what you cannot see. In development, three tools help:

  • django-debug-toolbar shows each SQL query of a page. For APIs, request the browsable HTML page to see it.
  • CaptureQueriesContext (or assertNumQueries) counts queries inside a test or a shell session.
  • The django.db.backends logger at level DEBUG prints every query to the console.

Foreign keys: select_related

When a serializer reads obj.author.name, Django fetches the author lazily: a new SELECT the first time you touch book.author. select_related("author") makes Django fetch both tables with one SQL JOIN.

Python N+1 with a foreign key, and the select_related fix

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Output:

TEXT
slow: 20 books -> 21 queries
fast: 20 books -> 1 queries
the JOIN: SELECT "qop1_book"."id", "qop1_book"."title", "qop1_book"."author_id", "qop1_author"."id", "qop1_author"."name" FROM "qop1_book" INNER JOIN "qop1_auth ...

Twenty books cost 21 queries the slow way and 1 the fast way. Twenty thousand books would cost 20,001 queries versus 1 (when paginated, per page). A good rule: in get_queryset(), every relation the serializer reads must be named in select_related or prefetch_related.

Many-to-many and reverse relations: prefetch_related

A JOIN cannot efficiently return "one book with its many reviews" as a single row. For these relations, prefetch_related("reviews") runs one extra query for all related rows (WHERE book_id IN (...)) and joins them to the parents in Python. The total is always the same (two queries), regardless of how many books there are.

Use Prefetch objects to filter or order the prefetched rows, and combine it with select_related on the same queryset.

Python Reverse relations: nested reviews with prefetch_related

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Output:

TEXT
no prefetch                        11 queries | first book has 3 reviews
prefetch_related('reviews')         2 queries | first book has 3 reviews
Prefetch with a filter              2 queries | book 0 top reviews: [4]

Watch out for a quiet trap: calling .filter(), .order_by() or .all() on a prefetched relation inside the serializer or a SerializerMethodField creates a new query for each row and throws the prefetch away. If you need a filtered list, use a Prefetch(..., to_attr=...) and read the attribute.

Let the database count: annotate

A "number of reviews" or "average rating" field is often written like this:

Python
review_count = serializers.SerializerMethodField()

def get_review_count(self, obj):
    return obj.reviews.count()      # one COUNT query per row!

That is N+1 again, hidden in a method. Compute it in the queryset with annotate() and read the value as a plain field. The database does the work in a single grouped query.

Python annotate(Count, Avg) instead of per-row queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Output:

TEXT
method field with .count()          9 queries | {'id': 4, 'title': 'Book 3', 'review_count': 4}
annotate(Count, Avg)                1 queries | {'id': 4, 'title': 'Book 3', 'review_count': 4, 'average': 3.5}
annotate + only('title')            1 queries | {'id': 4, 'title': 'Book 3', 'review_count': 4, 'average': 3.5}
long summary column loaded: False

only("title") and defer("summary") skip large columns (long text, JSON blobs) that the response does not use. The saving is real on wide tables, but be careful: if the serializer then reads a deferred field, Django quietly fires one query per row to fetch it. Verify with a query count.

Other things that make endpoints slow

  • No pagination. Returning 50,000 rows is slow whatever you optimise. See Pagination.
  • Missing indexes on columns used in filters and ordering (db_index=True or Meta.indexes). See filtering.
  • HyperlinkedRelatedField and StringRelatedField. Each reads a related object, so they need select_related too.
  • Expensive SerializerMethodFields that call other services or compute heavy values per row. Precompute, cache, or move the work out of the request.
  • Counting totals. The count in page-number pagination runs COUNT(*). On large tables use cursor pagination.
  • Doing writes one at a time. For bulk imports use bulk_create() and bulk_update(), and pass many=True to the serializer for validation only.
  • Slow Python for big responses. DRF's serializers are flexible but not the fastest; if you profile and serializer time dominates, return .values() rows through a lighter path for that endpoint.

Lock it in with a test

An optimisation that is not tested will be undone by the next developer who adds a field to the serializer. Pin the count:

Python
def test_book_list_query_count(self):
    with self.assertNumQueries(2):        # books + prefetched reviews
        self.client.get("/api/books/")

If someone adds an unprefetched relation, the test fails immediately and points at the endpoint.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A list endpoint shows each book's author name. Which queryset removes the N+1 problem?

    author is a foreign key, so select_related fetches it with a JOIN in the same query. (prefetch_related would also work, with two queries.)
  2. 2. Why is obj.reviews.count() inside a SerializerMethodField a problem?

    Each serialized row triggers its own COUNT query. Use annotate(Count(...)) in the queryset instead.
  3. 3. What is the total query count for prefetch_related('reviews') on 10 or on 10,000 books?

    One query for the books and one for all their reviews, joined in Python.
  4. 4. How do you stop a query-count regression from coming back?

    A test that pins the number of queries fails as soon as a new lazy relation sneaks in.

Summary

  • Slow endpoints are usually query-count problems: count queries first, then fix.
  • select_related joins foreign keys; prefetch_related (with Prefetch) loads many-to-many and reverse relations in one extra query.
  • Use annotate(Count/Avg/Sum) rather than per-row method fields; use only()/defer() carefully to skip big columns.
  • Paginate, index filtered columns, and keep heavy work out of per-row methods.
  • Pin query counts with assertNumQueries.

Next: Production Settings and Deployment