beginnerintermediateadvanced

Django · Pagination and Large Result Sets

Pagination and Large Result Sets

Bound the rows, bound the memory, and know when the answer is a job instead of a response

OFFSET walks and discards everything you skippeda cursor makes the boundary a value instead of a count
A sort that can tie is not a total orderwithout a unique tiebreaker, rows repeat and vanish between pages
Two buffers hold your dataset, not onethe result cache and the response body — removing one leaves it still failing
prefetch_related needs an explicit chunk_sizeomit it and Django skips the prefetch, turning a memory fix into an N+1
Past a point the limit is time, not memorya stream that dies at 90% delivers a truncated file inside a 200

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

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.

Key features

`?page=N` and `?limit=&offset=` compile to the same `LIMIT/OFFSET` SQL — the difference is the URL, not the cost
`OFFSET n` makes the database produce and discard `n` rows, so deep pages get progressively more expensive
A cursor is a `WHERE (sort_key, id) < (…, …)` comparison — an index seek, constant cost per page
Any sort that can tie is not a total order: append a unique column, e.g. `order_by("-placed_at", "-id")`
Without a tiebreaker, rows repeat or vanish between pages, and re-running the same page can return different rows
Django's `Paginator` runs a `COUNT(*)` for `num_pages`; on a large filtered table that count can cost more than the page

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

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

Command

python

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

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.

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

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.

Key features

`iterator(chunk_size=2000)` — 2,000 is the default; "iterating over a QuerySet with `iterator()` will not populate the result cache"
On PostgreSQL, `iterator()` "uses server-side cursors to retrieve results efficiently"
"`prefetch_related()` calls will only be observed if a value for `chunk_size` is provided" — omit it and you get an N+1
A server-side cursor is connection-local, so transaction-pooling poolers need `DISABLE_SERVER_SIDE_CURSORS: True`
`StreamingHttpResponse` takes an iterator; nothing is buffered, so the client sees bytes before the job finishes
A streamed response cannot change its status code after the first byte — an error mid-stream arrives inside a 200

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

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

defaultOOM beforethis.iterator()flat memoryfor anythinggenuinely large

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

Command

python

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

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.

3.Large export jobs

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.

Key features

A streamed response commits its status code with the first byte — a mid-stream failure arrives inside a `200`
Proxy and load-balancer timeouts end a slow stream regardless of progress, producing a truncated file
The job shape: `202` + job id → worker builds → object storage → a link, usually time-limited
A job survives deploys and worker restarts; an in-flight streamed response does not
Write the file under a key derived from the job id, so a retry cannot collide with a previous attempt
Give exports an expiry: they are a copy of data whose permissions may change after it is written

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

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.

Command

python

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

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.

Spotted a mistake on this page? Tell us.

Read Sheet