Django ORM Interview Questions: Query Counts, N+1 Problems, and Prefetch Pitfalls

Django ORM interview questions are best answered with a query count and an explanation of which operation caused each query. select_related() can remove repeated foreign-key lookups. prefetch_related() can collect related rows in separate queries. Neither method guarantees that every later access will use the data you already loaded.

This guide follows three books, two authors, and six reviews through a measured experiment. You will predict evaluation, detect an N+1 pattern, repair a filtered-prefetch mistake, and explain why batching changes the query count.

Evidence boundary: official behavior is linked to Django's 5.2 documentation. The numerical results come from an original local fixture using Django 5.2.17 and SQLite 3.53.4. They exclude schema setup, inserts, authentication, sessions, and request middleware. These are practice questions, not claims about a particular employer's interview frequency.

Define the output before counting queries

Our endpoint needs one row per book. Sometimes it needs the author's name; sometimes it needs all reviews; sometimes only approved reviews. Those are different contracts, so an optimization is valid only when it preserves the requested result.

The dataset is deliberately small. B1 and B2 share author Ada, while B3 belongs to Bo. Each book has one approved review and one unapproved review. The expected author output is [(B1, Ada), (B2, Ada), (B3, Bo)]; all-review counts are [2, 2, 2], and approved-review counts are [1, 1, 1].

An N+1 pattern means one query obtains a collection and then repeated accesses issue additional queries for individual items. The exact count depends on the code path and cache state. Do not define it as “every page always runs exactly N plus one statements.” Nested relationships can create more than one repeated lookup per item.

For the exercise, every measured branch starts with a new QuerySet unless we explicitly test reuse. That prevents an earlier evaluation from accidentally making a later optimization look effective.

Question 1: When does a QuerySet execute?

The official QuerySet reference distinguishes construction from evaluation. Building a filter normally creates a query description. Iterating it, converting it to a list, or asking for its length can execute SQL.

Our first sequence was:

books = Book.objects.order_by('id')
first = [book.title for book in books]
second = [book.title for book in books]
third = [book.title for book in books.filter(id__gt=0)]

Constructing books issued zero statements. The first comprehension issued one. The second issued zero because it reused that evaluated QuerySet's result cache. The filtered clone issued one new statement even though it returned the same three titles.

This is why “Django caches queries” is too broad. In the example, one QuerySet instance caches its evaluated result. That does not imply a process-wide cache matching arbitrary equivalent SQL. Two separately constructed QuerySets can each execute.

A useful follow-up is whether you need the objects at all. If the only question is existence or count, consider the corresponding database operation. If you will immediately iterate all objects afterward, account for both operations instead of assuming an extra existence check is free.

Question 2: Why did three books cause four queries?

Here is the unoptimized author access:

rows = [
    (book.title, book.author.name)
    for book in Book.objects.order_by('id')
]

Our captured SQL contained one book query and three author queries. B1 and B2 both belong to Ada, but that did not make their separate model instances share an already-loaded author relation. Each book's first author access triggered its own lookup in this branch.

The result is four statements, not three and not one. That distinction makes this a useful interview fixture: repeated foreign-key values can tempt you to assume global deduplication that the code does not provide.

Now change the source:

Book.objects.select_related('author').order_by('id')

The same comprehension produced the same ordered tuples with one SQL statement. The joined rows carried the author data needed by those accesses. We asserted output equality as well as the reduced count.

The related-object documentation helps distinguish single-valued access from collections. For an interview answer, connect your chosen loader to the relationship cardinality and the fields the caller actually reads.

Question 3: When is prefetching the appropriate shape?

A book has many reviews, so loading the collection is a different problem from fetching one author. Our all-review branch was:

books = Book.objects.prefetch_related('reviews').order_by('id')
counts = [len(list(book.reviews.all())) for book in books]

The measured count was two SQL statements: books first, then their reviews. The result was [2, 2, 2]. The second query used the parent identifiers to fetch matching reviews, and Django associated them with the books in Python.

That is the useful contrast: a join can bring single-valued relations into one result, while prefetching can load a related collection separately. But “prefetch always means two queries” is not a safe rule. Multiple relationship levels, additional lookups, empty parent results, and chunked iteration change the work.

If a screen needs author names and review objects, combining a suitable author join with a review prefetch may be reasonable. If it needs only a review count, materializing every review might be unnecessary. Ask what the response needs before optimizing the wrong workload.

Question 4: Why does filtering after prefetching miss the cache?

This code looks optimized but changes the access pattern:

books = Book.objects.prefetch_related('reviews').order_by('id')
counts = [
    len(list(book.reviews.filter(approved=True)))
    for book in books
]

The actual count was five: one query for books, one for the prefetched complete review collections, and three additional filtered queries. The output remained [1, 1, 1], so checking only the values would not expose the wasted work.

The important distinction is between the prefetched reviews.all() result and a new filtered QuerySet. The latter asks the database a different question. The earlier prefetch does not turn every later manager method into an in-memory operation.

This is an especially good review comment because it can identify both correctness and cost: “The approved counts are right, but these filters bypass the loaded collections. Either filter the related rows during prefetch or deliberately filter the loaded objects in Python, depending on the contract.”

Do not automatically choose Python filtering. If each book has thousands of historical reviews and the response needs two approved ones, fetching everything just to discard most of it wastes memory and transfer volume.

Question 5: How does filtered Prefetch with to_attr help?

We repaired the branch by naming the filtered collection explicitly:

from django.db.models import Prefetch

approved = Prefetch(
    'reviews',
    queryset=Review.objects.filter(approved=True),
    to_attr='approved_reviews',
)
books = Book.objects.prefetch_related(approved).order_by('id')
counts = [len(book.approved_reviews) for book in books]

The result was again [1, 1, 1], with two queries. We compared that output directly with the five-query branch. The optimization therefore preserved the business result while removing three redundant round trips in this fixture.

approved_reviews is a list attached to each returned book, not a related manager. That name also communicates that it contains a subset. A later developer can distinguish book.approved_reviews from the complete book.reviews relationship.

There is still a lifetime boundary. The list represents what was loaded when the prefetch ran. Updating the database afterward does not magically turn that Python list into a live subscription. If the code mutates reviews, decide when it should reload or replace the response data.

The QuerySet API's prefetch section documents these cache and custom-prefetch details. Our exact counts are observations from the supplied dataset, rather than universal constants copied from documentation.

Question 6: What changes with iterator and chunk_size?

The next branch kept prefetching but consumed the books in chunks:

books = Book.objects.prefetch_related('reviews').order_by('id')
counts = [
    len(list(book.reviews.all()))
    for book in books.iterator(chunk_size=2)
]

With three books and a chunk size of two, our trace showed three statements: one main book query and two review queries, one for each batch of parent objects. The result stayed [2, 2, 2].

In this Django 5.2 fixture, calling iterator() after prefetching without providing chunk_size raised ValueError. The error is useful version-specific evidence. Do not repeat an old blanket rule that iterator always ignores prefetch, or assume a snippet from another release has the same behavior.

Batching trades some additional queries for a different loading pattern. It can reduce the collection of model objects retained by normal QuerySet evaluation, but database-driver buffering and related-object volume still matter. If the caller wraps the whole iterator in list, it also retains all yielded objects itself.

The right follow-up is therefore “What consumes the iterator, and how many related rows are in each batch?” Query count alone cannot answer peak-memory or end-to-end latency questions.

Build a query-count test that cannot cheat

Our fixture used CaptureQueriesContext around only the operation being measured. It created tables and seed rows before entering the context. Each comparison printed the returned values and saved every captured SQL statement, making the count explainable rather than merely asserted.

For application tests, Django's testing tools include assertNumQueries. Put the access that triggers evaluation inside the assertion. Constructing a lazy QuerySet outside it and never consuming it inside can produce a meaningless zero-query pass.

Also keep serializers and templates in view. A service may return efficiently loaded objects, then a serializer method accesses an unplanned relation for every object. If the performance requirement concerns the response, include that serialization step in a focused test.

Use more than one parent in the fixture. A one-book example hides growth: a repeated lookup can look cheap until the endpoint returns fifty books. Include shared authors, multiple reviews, an empty relation, and the filter condition that the response actually uses.

Explain performance beyond the statement count

Django's optimization guidance starts with measuring the workload. Removing repeated queries can be valuable, but a single large query is not automatically faster than a few selective ones. Returned columns, join fan-out, indexes, row estimates, network distance, and Python allocation all affect the result.

Our SQLite fixture demonstrates query construction and cache behavior. It does not establish production speed on PostgreSQL or MySQL. For that, inspect the actual SQL and execution plan on representative data, then measure response latency and memory.

A strong interview answer gives two levels of evidence: “This change preserves the three response rows and reduces statements from four to one in the regression test. I would next examine the production plan and latency before claiming a percentage improvement.” That keeps the verified result useful without inflating it.

Continue with related backend exercises

Question	Practice focus
Repair a Django Post-Creation Endpoint and Its React Contract	Preserve response correctness while changing backend code.
Debug Event Search, Pagination, and Redis Invalidation	Trace queries, pagination, and stale results.
Design appointment scheduling data model and APIs	Explain relationships and access patterns.
Build a Paginated Provider Search Backend and React Filter UI	Connect query inputs to the response contract.
Explain Modern Python Features and Ecosystem	Communicate Python framework choices precisely.

For Django ORM interview questions, make the database work visible: state the output, predict evaluation, capture SQL, preserve results, and test the exact related access.

Sources and Further Reading

• Django 5.2: QuerySet API reference
• Django 5.2: Database access optimization
• Django 5.2: Making queries and following relationships
• Django 5.2: Testing tools and assertNumQueries