beginnerintermediateadvanced

Django · Section 73

Pagination and Large Result Sets

Level: advanced
Read: 32 min
Concepts: 3

The rule the roadmap states in one line — never return unbounded data from an API — turns into three separate engineering problems, and solving one does not solve the others. The first is the page boundary. Page numbers and limit/offset are the same SQL underneath, and both express the boundary as a *count*: skip 300 rows, which the database must produce and discard before returning anything, so deep pages cost progressively more. Worse, a count-based boundary moves when the data does — an insert while someone reads page 3 pushes a row onto page 4, so they see it twice, and a delete makes one vanish unseen. A cursor makes the boundary a *value* taken from the last row returned, which inserts cannot move and an index can seek to directly, at the same cost on every page. All three shapes are broken by an unstable sort: ties have no defined order, and the database may return them differently between the two queries that produce consecutive pages, so a unique tiebreaker in the ordering is not a refinement but a correctness requirement. The second problem is memory, and there are two buffers rather than one. A queryset caches every row it fetched, and a response is assembled completely before its first byte is sent. `.iterator()` removes the first — chunked reads through a server-side cursor on PostgreSQL, with no result cache — and `StreamingHttpResponse` removes the second. Removing either alone leaves the job still failing, which is why a generator wrapped around `list(qs)` is the most convincing non-fix in this area. One sharp edge: with `prefetch_related`, an explicit `chunk_size` is mandatory, or Django skips the prefetch and hands back an N+1. The third problem is time. Past a certain size no amount of streaming beats a proxy timeout, and because a streamed response commits its status code with the first byte, a failure at 90% arrives as a truncated file inside a `200`. That is the point to stop returning the data at all: accept with `202` and a job id, build in a worker, write to object storage, and hand over a short-lived signed link.

Django overview

Python Django Full Course for Beginners | Complete All-in-One Tutorial | 3 HoursDave Gray

What is true here

Offset boundaries are counts that move and deepen; cursor boundaries are values that do not.
A unique tiebreaker in order_by is a correctness requirement, not a refinement.
Two buffers hold the dataset — the result cache and the response body — and both must be removed.
prefetch_related with .iterator() silently degrades to an N+1 without an explicit chunk_size.
When the binding limit becomes time, the answer is a job and a link, not a longer stream.

What you will be able to do

Choose between page numbers and cursors from how deep and how volatile the data is
Explain why rows duplicate across pages, and fix it in one line
Export an arbitrarily large table with memory that does not grow
Recognise when streaming stops being enough and design the job instead

The same listing, bounded and unbounded

Unbounded — works until it does not

+Every matching row fetched and cached on the queryset
+A model instance per row, then a serialized copy of all of them
+The whole body assembled before the first byte leaves
+Deep pages walk and discard everything skipped
+Fails as a killed process, or a client timeout, or duplicated rows

Bounded — flat in the row count

−A page, or a chunked cursor read — never the whole set at once
−`values()` instead of model instances
−Bytes leave as they are produced
−The boundary is a value, so inserts cannot shift it
−Genuinely large results become a job with a durable artifact

The three pagination shapes

What each boundary is made of, what it costs at depth, and the tiebreaker all of them need.

Page numbers, limit/offset, and cursors — and stable ordering

coreintermediate

Never return an unbounded result set. The three ways to bound one are page numbers (`?page=7`), limit/offset (`?limit=50&offset=300`), and cursors (`?after=<opaque token>`). Page numbers and limit/offset are the same thing underneath — both become SQL `OFFSET`, and the database has to walk and discard every skipped row, so page 2,000 costs far more than page 2. Cursors instead say "give me rows after this one", which is a `WHERE` the database can satisfy with an index and costs the same on every page. All three are broken by an unstable sort: if two rows can tie, the order between them is undefined, and rows silently repeat or disappear between pages.

Think of it as

Ask what the page boundary is *made of*. With `OFFSET`, the boundary is a count — "skip 300 rows" — which means the database must produce and discard those 300 rows first, and it also means the boundary moves whenever the underlying data changes. Someone inserting a row while a user reads page 3 pushes one row from page 3 onto page 4, so the user sees it twice; a deletion makes a row vanish unseen. With a cursor, the boundary is a *value* — "rows after (2026-09-05T10:00, id 8412)" — which does not move when other rows are inserted or deleted, and which an index on the same columns can seek to directly. That is the whole trade: `OFFSET` gives you random access to any page and gets slower the deeper you go; a cursor gives you cheap sequential access and no page numbers. Stable ordering is the part that decides whether either works at all. Sorting by `-created_at` alone is not a total order when two rows share a timestamp, and the database is free to return ties in any order — including a different order on the next query for the same page. Appending a unique tiebreaker (`("-created_at", "-id")`) makes the order total, which is what makes both the offset boundary and the cursor comparison well defined. This is not a subtle correctness issue you can defer: the symptom is duplicated and missing rows in a paginated export, and it is nearly impossible to diagnose from a bug report.

python

qs.order_by("-placed_at", "-id")     # total order: the tiebreaker is unique
qs[300:350]                          # LIMIT 50 OFFSET 300 — walks 300 rows first

What we're doing: Show the same listing paginated three ways, and what each one does at depth and under inserts.

orders/pagination.pypython

# ---- 1. Page numbers: fine shallow, slow deep, wrong under inserts ------
def page_numbers(request):
    qs = Order.objects.filter(state="paid").order_by("-placed_at", "-id")
    page = Paginator(qs, 50).page(request.GET.get("page", 1))
    return render(request, "orders.html", {"page": page})
#   ?page=2000  ->  LIMIT 50 OFFSET 99950 : the database walks 99,950 rows
#   Paginator also runs COUNT(*) for num_pages, on every request


# ---- 2. The bug the tiebreaker prevents ---------------------------------
def unstable(request):
    qs = Order.objects.filter(state="paid").order_by("-placed_at")   # ties possible
    return Paginator(qs, 50).page(request.GET.get("page", 1))
#   1,200 orders share one placed_at (a nightly import).
#   Page 3 and page 4 both contain order 8412; order 8677 is on neither.


# ---- 3. Cursor: constant cost, insert-safe -----------------------------
def cursor_page(request, limit=50):
    qs = Order.objects.filter(state="paid").order_by("-placed_at", "-id")

    if cursor := request.GET.get("after"):
        placed_at, order_id = decode_cursor(cursor)
        qs = qs.filter(
            Q(placed_at__lt=placed_at)
            | Q(placed_at=placed_at, id__lt=order_id)      # the tie half
        )

    rows = list(qs[: limit + 1])                            # one extra = "has next"
    has_next, rows = len(rows) > limit, rows[:limit]

    return {
        "results": [serialize(o) for o in rows],
        "next": encode_cursor(rows[-1]) if has_next and rows else None,
    }

5–7: Two costs, both invisible in the code: the discarded rows behind `OFFSET`, and the `COUNT(*)` the `Paginator` runs to know how many pages exist.
11–15: The failure that motivates the tiebreaker. A bulk import gives many rows an identical `placed_at`, the database orders ties arbitrarily, and the arbitrary order can differ between the two queries that produce page 3 and page 4.
22–26: The cursor comparison in full. The `Q` pair is the row-value comparison `(placed_at, id) < (…, …)` written out, and the second half is what handles rows sharing a timestamp.
28: Fetching one extra row answers "is there a next page" without a `COUNT(*)`, which is often the single biggest saving on a large filtered table.
32–34: The cursor is derived from the last row returned, so it is a value rather than a position — inserts and deletes elsewhere cannot move it.

Why this works: The three implementations are the same listing, and the differences are entirely in the boundary: a count that moves and gets expensive, versus a value that is stable and indexable.

Ordering by a non-unique column and paginating it

Wrong

python

qs = Order.objects.order_by("-placed_at")       # ties are unordered
page_3 = qs[100:150]
page_4 = qs[150:200]
# order 8412 appears on both; order 8677 appears on neither

Better

python

qs = Order.objects.order_by("-placed_at", "-id")   # total order
page_3 = qs[100:150]
page_4 = qs[150:200]
# every row appears exactly once, and re-running a page returns the same rows

What you see: A paginated export produces a file with duplicate rows and missing rows, in numbers small enough to look like a data problem rather than a pagination problem. Re-running it produces a *different* set of duplicates.

Why: SQL makes no promise about the order of rows that tie on the `ORDER BY` key, and it is free to return them differently between executions — a different plan, a different degree of parallelism, or a different physical layout is enough. `LIMIT/OFFSET` slices that unstable sequence at fixed positions, so any reshuffling around the boundary moves rows across it. Appending a unique column makes the order total, so there is no freedom left and the slice is well defined. The same requirement applies to cursors, where the tiebreaker is also what the comparison uses to resume.

Pick by how deep the reader goes and how fast the data changes

rows inserted constantlydata rarely changes

first few pages onlydeep or full traversal

page numbers — fine: a catalogue browsed a page or two deep; OFFSET depth never gets large
page numbers — slow: page 2,000 makes the database walk and discard 100,000 rows to return 50
page numbers — wrong: inserts shift the boundary: the reader sees a row twice, or never sees it
cursor — the only correct option: a value-based boundary that inserts cannot move, and an index can seek to
compound ordering is required in all four: ("-placed_at", "-id") — a sort that can tie is not a total order

Three shapes, and what each one is actually for

Three shapes, and what each one is actually for
Shape	Boundary	Deep-page cost	Use when
page number	a count of rows to skip	grows with depth	a human UI with a page picker over modest data
limit/offset	the same count, spelled differently	grows with depth	an API that must support arbitrary jumps
cursor	a value from the last row	constant	feeds, exports, sync, anything deep or changing
keyset over a job	the last processed id	constant, and resumable	batch work that must survive a restart

Together

python

Order.objects.filter(
    placed_at__lte=after_time,
).exclude(placed_at=after_time, id__gte=after_id).order_by("-placed_at", "-id")[:100]

Remember: Page numbers and limit/offset are the same SQL, and both make the database walk and discard everything you skipped — cost grows with depth, and the boundary moves whenever rows are inserted. A cursor makes the boundary a *value* instead of a count, so it is index-seekable and insert-safe. Whichever you pick, append a unique tiebreaker to the ordering: a sort that can tie is not a total order, and the symptom of getting this wrong is duplicated and missing rows that change on every run. Cap addressable page depth, and send full traversal to a cursor or an export job.

Chunked reads and streamed writes

The two buffers between the database and the client, and why removing one is not enough.

`iterator()`, batch processing, and streaming responses

coreadvanced

A normal queryset fetches every matching row and caches all of them on the queryset, so `for order in Order.objects.all()` holds the whole table in memory. `.iterator()` fetches in chunks instead — on PostgreSQL it uses a server-side cursor — and does not populate that cache, so memory stays flat however many rows there are. `StreamingHttpResponse` does the same for the response: instead of building the whole body and then sending it, it sends pieces as they are produced. Together they turn "works until the table grows" into something with a fixed memory cost.

Think of it as

Two separate buffers can each hold your whole dataset, and both have to be removed for a large job to be safe. The first is the queryset's result cache, which exists so a second loop over the same queryset does not re-query — genuinely useful for a page of results, and exactly wrong for a million rows. `.iterator()` opts out of it: rows arrive in chunks and are discarded as you pass them. The second is the response body, which by default is assembled completely before the first byte is written. `StreamingHttpResponse` takes an iterator and writes as it goes. Removing one without the other leaves a job that still fails — streaming a response built from `list(qs)` bounds nothing. Two caveats matter in practice. Opting out of the cache means every re-iteration re-queries, so `.iterator()` is for a single pass; if you need the rows twice, you need a different design, not a second loop. And `prefetch_related` is only honoured when you pass an explicit `chunk_size`, because the prefetch needs a batch of primary keys to fetch against — omit it and the related objects come back one query per row, turning a memory fix into an N+1. Finally, on PostgreSQL the chunking is done with a server-side cursor, which is a connection-local object; that is why a transaction-pooling connection pooler needs server-side cursors disabled.

python

qs.iterator(chunk_size=2000)                      # no result cache
qs.prefetch_related("items").iterator(chunk_size=2000)   # chunk_size REQUIRED here

What we're doing: Stream a CSV of an arbitrarily large table with flat memory, and handle the two traps that make it fail anyway.

reports/export.pypython

class Echo:
    """A file-like object whose write() returns the line instead of storing it."""

    def write(self, value):
        return value


def stream_orders_csv(request):
    writer = csv.writer(Echo())
    rows = (
        Order.objects
        .filter(placed_at__year=2026)
        .values_list("id", "reference", "total", "placed_at")
        .iterator(chunk_size=2000)                  # no result cache, server-side cursor
    )

    def lines():
        yield writer.writerow(["id", "reference", "total", "placed_at"])
        for row in rows:
            yield writer.writerow(row)

    response = StreamingHttpResponse(lines(), content_type="text/csv")
    response["Content-Disposition"] = 'attachment; filename="orders-2026.csv"'
    return response


def process_in_batches():
    """Batch processing: same streaming read, but write in bulk."""
    updates = []
    for order in Order.objects.filter(needs_recalc=True).iterator(chunk_size=2000):
        order.total = recalculate(order)
        updates.append(order)

        if len(updates) >= 1000:
            Order.objects.bulk_update(updates, ["total"])
            updates.clear()                          # or memory grows anyway

    if updates:
        Order.objects.bulk_update(updates, ["total"])

1–5: The standard trick for streaming CSV: `csv.writer` needs something with `write()`, and this one returns the formatted line rather than accumulating it, so the generator can yield it.
10–14: `values_list` avoids model instances and `.iterator()` avoids the result cache. Both are needed — either alone still holds a copy of everything.
17–20: A generator, not a list. `StreamingHttpResponse` pulls from it, so rows are formatted and sent as the cursor produces them.
29–36: Batch processing is the same read pattern with a bounded write. The accumulator is what would otherwise re-introduce the memory problem `.iterator()` removed.
36: Clearing the list matters. Reassigning inside the loop while a reference is held elsewhere is the usual way this quietly keeps growing.

Why this works: Both buffers are removed on the read side and the write side is bounded too, so peak memory is a function of chunk size and batch size — two constants you chose — rather than of how many rows match.

Using `prefetch_related` with `iterator()` and no `chunk_size`

Wrong

python

for order in Order.objects.prefetch_related("items").iterator():
    total = sum(i.price for i in order.items.all())   # 1 query PER ORDER

Better

python

for order in Order.objects.prefetch_related("items").iterator(chunk_size=2000):
    total = sum(i.price for i in order.items.all())   # 1 prefetch per 2,000 orders

What you see: A job rewritten to fix memory becomes dramatically slower instead, and the query count is now roughly the row count — the exact N+1 the prefetch was there to prevent.

Why: Django's documentation states that "`prefetch_related()` calls will only be observed if a value for `chunk_size` is provided". The prefetch works by collecting a batch of primary keys and issuing one query for their related rows, and without an explicit chunk size there is no batch to collect against, so the prefetch is skipped and each `order.items.all()` lazily queries. The fix is the argument itself, and it is worth passing on every `.iterator()` with a prefetch rather than relying on remembering which ones have one.

Two buffers between the database and the client — both must go

PostgreSQL

10,000,000 matching rows

Server-side cursor

iterator() — the database holds position, you hold a chunk

QuerySet result cache

the default path: every row materialised and kept

One chunk in memory

chunk_size=2000 — constant, whatever the total

Whole response body

JsonResponse: built completely before the first byte

StreamingHttpResponse

bytes leave as they are produced

Client

Or: an export job

write to object storage, email a link — survives a client timeout

Which buffer each tool removes

Which buffer each tool removes
Approach	Rows held	Response held	Safe at 10 million rows?
`for o in qs:`	all	all	no — both buffers full
`qs.iterator()` + `JsonResponse`	one chunk	all	no — the payload is still whole
`list(qs)` + `StreamingHttpResponse`	all	one chunk	no — the rows are still whole
`qs.iterator()` + `StreamingHttpResponse`	one chunk	one chunk	yes — flat in the row count
export job → object storage	one chunk	nothing (a link)	yes, and it survives a client timeout

Together

python

rows = Order.objects.values("id", "total").iterator(chunk_size=2000)
return StreamingHttpResponse(to_csv(rows), content_type="text/csv")

Remember: Two buffers can each hold your whole dataset — the queryset result cache and the response body — and removing one leaves the job still failing. `.iterator()` removes the first, `StreamingHttpResponse` removes the second, and a generator wrapped around a `list(qs)` removes neither. Always pass an explicit `chunk_size` when a prefetch is involved, or Django skips the prefetch entirely and hands you an N+1. `.iterator()` is a single pass by design: re-iterating re-queries, so if the rows are needed twice the answer is a different design.

When it stops being a response

The point where the binding limit is time, and the export shape that survives a dropped connection.

Large export jobs

standardadvanced

Past a certain size, streaming a response is not enough, because the limit that bites is no longer memory — it is time. A load balancer, a proxy and the client each have their own timeout, and none of them care that your generator is making steady progress. The shape that works is to stop returning the data at all: accept the request, return `202` with a job id, build the file in a worker, write it to object storage, and give the user a link. The export then survives a dropped connection, a deploy, and a client that closed the tab.

Think of it as

The decision is about who owns the wait. In a streamed response, the HTTP request owns it: the connection has to stay open for the whole build, a worker is occupied for the whole build, and any one of the intermediaries can end it at a moment none of them will report usefully. Ending at 90% produces a truncated file that looks complete, because the status code was sent at the start and cannot be retracted. In a job, the *user* owns the wait: the request is over in milliseconds, the work happens where restarts and retries are normal, and the result is a durable artifact with its own lifetime. That last property is what makes jobs worth the extra moving parts even when streaming would technically fit — the file can be re-downloaded, its generation can be retried without redoing the request, and a failure is a job in a failed state rather than a half-written download. The practical rule is to look at three things: the expected duration against your infrastructure timeouts, whether a truncated result would be detectable by the user, and whether the same export is likely to be requested again. Any one of those pointing the wrong way makes it a job.

python

return Response({"job_id": job.id, "status": "queued"}, status=202)
# then: GET /exports/<job_id>/  ->  {"state": "ready", "download_url": …}

What we're doing: The whole shape in one file — accept, build, and hand over — with the two authorization checks people leave out.

exports/views.pypython

class ExportCreateView(APIView):
    def post(self, request):
        serializer = ExportRequestSerializer(data=request.data)
        serializer.is_valid(raise_exception=True)

        job = ExportJob.objects.create(user=request.user, filters=serializer.validated_data)
        transaction.on_commit(lambda: build_export.delay(job.id))
        return Response({"job_id": str(job.id), "state": job.state}, status=202)


@shared_task(bind=True, max_retries=3)
def build_export(self, job_id):
    job = ExportJob.objects.get(pk=job_id)
    job.state = "running"
    job.save(update_fields=["state"])

    try:
        qs = visible_orders_for(job.user).filter(**job.filters)   # scoped to the OWNER
        rows = qs.values_list(*EXPORT_COLUMNS).iterator(chunk_size=2000)

        key = f"exports/{job.id}.csv"                             # job id, not a guessable name
        with default_storage.open(key, "wb") as fh:
            fh.write(encode_header(EXPORT_COLUMNS))
            for chunk in batched(rows, 2000):
                fh.write(encode_csv(chunk))

        job.file_key, job.state = key, "ready"
    except Exception as exc:
        job.state, job.error = "failed", str(exc)[:500]
        job.save(update_fields=["state", "error"])
        raise self.retry(exc=exc, countdown=60)

    job.save(update_fields=["file_key", "state"])


class ExportDownloadView(APIView):
    def get(self, request, job_id):
        job = get_object_or_404(ExportJob, pk=job_id, user=request.user)
        if job.state != "ready":
            return Response({"state": job.state}, status=409)
        return redirect(default_storage.url(job.file_key))

8: `202 Accepted` is the honest status: the work has not happened, and the body carries the id the client will poll. Returning `200` here claims a result that does not exist.
18–19: The queryset is scoped to the job's owner, not to whoever is downloading later. A job created by one user must not be able to widen its own scope on retry.
22: A key derived from the job id, which is a UUID. A name built from the filters or a timestamp is guessable, and object storage keys are often reachable by anyone who knows them.
28–32: Failure is recorded on the job before re-raising, so a client polling the job sees `failed` with a reason rather than a job stuck at `running` forever.
37–40: The second authorization check. The download must re-verify ownership — a job id in a URL is not an authorization token, and `409` for a job that is not ready is clearer than a broken redirect.

Why this works: Every step is placed where its failure is survivable: validation in the request, the long work in a retryable task, and the bytes in storage rather than through a worker — so a dropped connection costs nothing and a retry cannot corrupt a previous attempt.

An export that survives a dropped connection

1. Accept, do not produce

Validate the filters, create a job row, enqueue after commit, return 202 with a job id. The request is over in milliseconds.

2. Build with a chunked read

The worker streams rows out of the database and writes them straight into storage. Memory is one chunk; there is no HTTP connection to keep alive.

3. Record the outcome on the job

Success stores the key and the row count; failure stores the error. Either way the job row is the single source of truth a client can poll.

4. Hand over a time-limited link

The download endpoint checks the job belongs to the caller, then redirects to a short-lived signed URL. The bytes never pass through your workers.

Choosing between a stream and a job

Choosing between a stream and a job
Signal	Stream it	Make it a job
expected duration	well inside every timeout	anywhere near one of them
a truncated result	obvious to the user	looks like a complete file
likely to be requested again	no	yes — the artifact is reusable
failure handling	the user retries the whole thing	the job retries itself
who waits	the HTTP connection	the user, asynchronously

Together

python

job = ExportJob.objects.create(user=request.user, filters=filters)
build_export.delay(job.id)
return Response({"job_id": job.id, "status": "queued"}, status=202)

Remember: Past a certain size the binding limit is time, not memory, and no amount of streaming beats a proxy timeout — a stream that dies at 90% delivers a truncated file inside a `200`, because the status was committed with the first byte. Accept the request with `202` and a job id, build in a worker, write to object storage, and hand back a short-lived signed link. Scope the export queryset to the job's owner, key the file by job id, record failures on the job row, and re-check ownership on download.