Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Django QuerySet Shape, Prefetch, and Page Cost

Last updated: 5 Oct 20268 min read
tutorial
IntermediateBy AITrove Editorial

A QuerySet describes work until evaluation asks the database for results. That delay is useful, but it makes an innocent-looking template or serializer capable of issuing a query per row. For a foreign-key district on a permit, select_related can fetch the single-valued relation with a join. For attachments, prefetch_related performs a separate batch query and attaches the many-valued rows to the selected parents. Neither call means every relation should be loaded by default. A useful list has an explicit authorization filter, stable order, selected relation shape, and page limit. Its cost is the rows and objects actually materialized, plus the database work required to find them.

Working case

A reviewer queue lists 47 permits, their districts, and attachment names. The first version fetches permits and then accesses each district and attachment collection while rendering. Its query count grows with the page. The next version prefetches every attachment for an unbounded 47,000-case result, reducing query count while exhausting application memory. A third version paginates with a very high offset and becomes slow even though the page has only 47 rows. The repaired queue filters by reviewer, applies a deterministic created-at and ID order, slices before evaluation, and loads only relations shown on this screen. A cursor avoids repeatedly walking deep offsets.

Implementation boundary

python
from django.db.models import Prefetch
from .models import PermitAttachment, PermitCase


def reviewer_queue(reviewer_id: int):
    attachments = PermitAttachment.objects.only("id", "case_id", "name")
    cases = (
        PermitCase.objects.filter(allowed_reviewers__id=reviewer_id)
        .select_related("district")
        .prefetch_related(Prefetch("attachments", queryset=attachments))
        .order_by("-created_at", "-id")[:47]
    )
    return list(cases)

Start with a permission-scoped base QuerySet. Use select_related for the district foreign key because each case has at most one district. Use prefetch_related for attachments only when this view actually displays them, and select the attachment columns needed by a compact summary if the full model is too large. Apply a deterministic order with an ID tie breaker and fetch at most 47 rows. For continuation, encode a server-validated cursor from the final ordering key rather than accepting an arbitrary raw SQL fragment. A simple slice works for an initial page; a deep feed should use keyset conditions that match the order. Materialize once, then serialize without accidental relation access.

Cost and boundaries

The initial list evaluates at most 47 parent rows, so response memory is O(page size plus selected attachments). A many-valued prefetch issues another query and can still transfer many attachment rows per case; cap or summarize them if collections are large. A join for a single-valued relation avoids repeated queries but increases row width. Counting all matching rows for every page adds a separate potentially expensive operation. High-offset pagination can scan or discard many earlier rows in the database, even though Python receives only one page. Cursor pagination changes the client contract but supports indexed continuation. Inspect query counts, transferred bytes, plan shape, and p95 latency with realistic assignment sizes.

Failure trace

Render 47 cases with a query counter and compare the unoptimized and planned versions. Include a case with no attachments and one with many; neither should silently trigger an extra per-row lookup. Change two creation timestamps to the same value and ensure the ID tie breaker prevents duplicates or omissions across pages. Request a cursor outside the user's scope and make sure filtering remains in force. Test a deep offset to demonstrate its database cost before replacing it. Verify a screen that does not display attachments does not prefetch them. Confirm serialization cannot reach a deferred relation that reintroduces hidden work.

Verification

  • The reviewer filter survives every page and cursor.
  • Only displayed relations are joined or prefetched.
  • Ordering stays stable when timestamps are equal.

Practice drill

Build a 47-row reviewer queue and seed enough cases to make the old high-offset path measurable. Record SQL query count and rows returned while rendering district and attachment summaries. Add select_related and a bounded prefetch, then repeat the measurement. Remove the attachment panel and remove its prefetch too. Introduce duplicate timestamps, implement an ID tie breaker, and write continuation tests that traverse pages without repeats. Compare a deep offset with a keyset continuation under the same authorization predicate. Document whether an exact total count is truly needed by the product.

Decision note

Choose relation loading after fixing the page and permission scope; optimize measured query and row costs together.

Common Mistakes

  • Prefetching an unbounded result set to eliminate query count alone.
  • Assuming a high offset costs only the returned page.
  • Using a timestamp without a unique tie breaker for continuation.

Related lessons

Django Request and Persistence Boundaries; Django Middleware, Sessions, and Object Permissions; Django Forms, CSRF, Atomic Approval, and On-Commit Work; Django ASGI, Async Views, and the Sync ORM Boundary; Data Persistence; Database Capacity and Online Change Operations.

Apply and check

Build Project: Django permit review service and review Web Development: Django request and persistence quiz.

web-tech
web-development
Storage details