skip to content

In Django, how does QuerySet.iterator() differ from plain iteration over a QuerySet, and what does its chunk_size control?

level: middleimportance: must knowfreq 50%

answer

  1. skips the result cache
  2. default of 2000 rows
  3. server-side cursor on PostgreSQL and Oracle
  4. prefetch needs an explicit chunk size

basics

~20 s

iterator() streams results without filling the QuerySet's result cache, so rows can be discarded as you go. chunk_size (default 2000) sets how many rows are fetched per round trip, and must be given when prefetch_related() is used.

solid answer

~40 s

Plain iteration calls `_fetch_all()`, which builds **every** row into `_result_cache` before the loop body sees the first one, and keeps them all alive while the QuerySet is referenced. `iterator()` yields rows as they arrive and caches nothing, so memory stays roughly one chunk plus whatever you keep. `chunk_size` is the batch size: on PostgreSQL and Oracle Django opens a **server-side cursor** and `chunk_size` rows are pulled per round trip; on SQLite rows come in `fetchmany()` batches; MySQL's driver loads the whole result into memory regardless. The default is 2000. `iterator()` works with `prefetch_related()` only if you pass `chunk_size` — each chunk gets its own prefetch queries — and since Django 5.0 omitting it raises `ValueError`. Calling `iterator()` again repeats the query. On PostgreSQL, `DISABLE_SERVER_SIDE_CURSORS = True` in the database settings turns the cursor off.

code

python · 14 lines
python
from telemetry.models import Reading

# Caches every Reading before the loop starts
for r in Reading.objects.filter(metric="temp"):
    handle(r)

# Streams; nothing kept once handle() returns
for r in Reading.objects.filter(metric="temp").iterator(chunk_size=5000):
    handle(r)

# With prefetching: chunk_size is mandatory
qs = Reading.objects.prefetch_related("annotations")
for r in qs.iterator(chunk_size=1000):  # qs.iterator() alone raises ValueError
    handle(r)

go deeper

for a junior

Recall that iterator() loops without caching every row, which saves memory on large QuerySets.

for a middle

Explain the result cache, the default chunk_size of 2000, the prefetch_related() requirement and how backends differ in streaming.

for a senior

Diagnose memory and connection behaviour of big loops: server-side cursors, poolers, per-row queries inside the loop and MySQL buffering.

for a principal

Decide which bulk reads run inside web requests versus background jobs, and which streaming approach each database in the estate supports.

## What plain iteration does A Django `QuerySet` is lazy until something evaluates it. `for r in Reading.objects.filter(...)` evaluates it by calling `_fetch_all()`, which runs: ```python self._result_cache = list(self._iterable_class(self)) ``` So **every row is turned into an object and stored** before your loop body runs. The objects stay in `_result_cache` as long as the QuerySet is referenced. For ten thousand rows this is fine and makes a second loop free; for ten million telemetry readings it is the out-of-memory error. ## What `iterator()` changes `Reading.objects.filter(...).iterator(chunk_size=5000)` returns a generator: - **No result cache.** Objects are yielded and can be garbage-collected once your code drops them. - **Re-running.** Calling `iterator()` on a QuerySet that was already evaluated runs the query again rather than reusing the cache. - **Works with projections.** `values_list(...).iterator()` streams tuples, the cheapest combination. - **Async twin.** `aiterator(chunk_size=2000)` does the same with `async for`. ## What `chunk_size` means on each backend | Backend | How rows arrive | Effect of `chunk_size` | |---|---|---| | PostgreSQL | server-side cursor (unless disabled) | rows pulled per round trip from the cursor | | Oracle | server-side cursor, always | rows cached at the driver per fetch | | SQLite | `fetchmany()` batches | batch size from the driver | | MySQL / MariaDB | driver loads the full result into memory | only the batch Django converts at a time | Key numbers and rules: 1. **Default 2000.** With no prefetch, omitting `chunk_size` means 2000 — a value the docs derive from keeping each fetch under roughly 100 KB for typical rows. 2. **`prefetch_related()` needs it.** Without `chunk_size`, Django raises `ValueError: chunk_size must be provided when using QuerySet.iterator() after prefetch_related().` With it, Django prefetches per chunk, so larger chunks mean fewer prefetch queries but more memory. 3. **`IN` limits.** On backends that cap the number of query parameters (SQLite, Oracle), keep `chunk_size` small enough that each chunk's prefetch `IN (...)` list stays under the cap. 4. **Must be positive.** Zero or negative values raise `ValueError`. ## PostgreSQL specifics - The server-side cursor is used only while the connection's `DISABLE_SERVER_SIDE_CURSORS` option is `False` (the default). - PostgreSQL plans cursor queries assuming only a fraction of rows will be read, controlled by `cursor_tuple_fraction`; for a full export that assumption can make the plan worse. - A connection pooler in **transaction pooling** mode is incompatible with server-side cursors held across transactions; the fixes are disabling them, routing the export through a separate database alias, or running it inside `atomic()`. ## Choosing a `chunk_size` - **Too small** means many round trips; each fetch has fixed overhead, so a chunk of 50 rows over ten million rows costs two hundred thousand fetches. - **Too large** raises peak memory and, with prefetching, produces large `IN (...)` lists per chunk. - **Wide rows** (large text or JSON columns) justify a smaller chunk than narrow numeric rows. - **With `prefetch_related()`**, the chunk size also sets how many related-object queries run: one set of prefetch queries per chunk. A few thousand rows is a reasonable starting point; measure peak memory and total time on realistic data before tuning further. ## What `iterator()` does not fix - **Holding onto rows.** Appending every yielded object to a list rebuilds the cache by hand. - **Per-row queries.** Reading `r.device.serial` in the loop still costs a query per row under the default fetch mode, and Django 6.1's `FETCH_PEERS` gains little here because only rows already built are peers. Use `select_related()` or a projection such as `values_list("device__serial", ...)`. - **MySQL memory.** The driver still materialises the result, so very large MySQL exports need keyset batching (`filter(pk__gt=last).order_by("pk")[:n]`) instead. - **Transaction length.** A streamed query keeps its connection busy until the loop finishes.

  • Why does iterator() on MySQL still use a lot of memory for a huge table?
    MySQL does not support streaming results in Django's setup, so the Python driver loads the entire result set into memory; Django then converts rows in `fetchmany()` batches. `iterator()` avoids the model-instance cache but not the driver's copy. Keyset batching on the primary key keeps each query small instead.
  • What happens if you call iterator() twice on the same QuerySet?
    Each call runs the query again, because `iterator()` never stores results in the QuerySet's cache. That is the intended trade: lower memory in exchange for no reuse. If you need two passes over a modest result, plain evaluation with its cache is cheaper.

Plain iteration is filling a warehouse with every parcel before you start delivering; iterator() is taking parcels off a conveyor belt a crate at a time, and chunk_size is the crate size.

saying these in an interview costs you the question

  • Believes iterator() also populates the QuerySet result cache
  • Thinks chunk_size is a LIMIT on the total number of rows
  • Expects iterator() to stream results from MySQL without driver buffering
  • Calls iterator() after prefetch_related() without chunk_size
  • Says iterator() removes per-row related-object queries