skip to content

In Django, does async for over a QuerySet stream rows from the database, and when should you use aiterator() instead?

level: seniorimportance: nice to knowfreq 22%

answer

  1. the result cache
  2. one crossing or many
  3. chunks of two thousand
  4. large exports, used once

basics

~20 s

No. async for fetches every row into the result cache in one synchronous call, then yields them. aiterator() fetches in chunks (2000 by default), skips the cache and can use server-side cursors, suiting large read-once results.

solid answer

~40 s

`QuerySet.__aiter__` awaits `sync_to_async(self._fetch_all)()` once and then yields from `_result_cache`, so `async for` loads the **whole result** into memory before your first iteration — convenient and cached, but not streaming. `aiterator(chunk_size=2000)` is the async twin of `iterator()`: it pulls rows in chunks, each chunk through its own thread handoff, does not fill the QuerySet cache, and on backends that support it (PostgreSQL unless `DISABLE_SERVER_SIDE_CURSORS` is set, Oracle) streams from a server-side cursor. Since 5.0 it also honours earlier `prefetch_related()` calls, prefetching per chunk. Use `async for` for bounded pages such as the latest 50 notifications; use `aiterator()` for large one-pass work such as exporting a user's full notification history.

code

python · 18 lines
python
import json

from django.http import StreamingHttpResponse

from notifications.models import Notification


async def export_notifications(request):
    user = await request.auser()
    qs = Notification.objects.filter(recipient=user).order_by('pk').values(
        'pk', 'verb', 'created_at'
    )

    async def rows():
        async for row in qs.aiterator(chunk_size=1000):
            yield json.dumps(row, default=str) + '\n'

    return StreamingHttpResponse(rows(), content_type='application/x-ndjson')

go deeper

for a junior

Know that async for loads the whole QuerySet and that aiterator() exists for large result sets.

for a middle

Explain the result cache, the single _fetch_all crossing behind async for, and how aiterator() chunks and skips the cache.

for a senior

Choose chunk sizes and iteration style for exports and backfills, and account for server-side cursors, poolers and per-chunk prefetching.

for a principal

Decide where bulk data work belongs: an async request streaming an export, or a background job that does not hold a connection for minutes.

## What `async for` actually does A Django QuerySet supports asynchronous iteration through `__aiter__`. In current Django that method returns a small async generator which: 1. awaits `sync_to_async(self._fetch_all)()` — **one** crossing that runs the query and fills the QuerySet's **result cache** with every row; 2. then yields each cached item, with no further database access. So `async for n in qs` behaves like evaluating `list(qs)` on a worker thread and iterating the list. That has consequences: - **Memory** holds the entire result set before the loop body runs once; - **Caching** works as in sync code: iterating the same QuerySet again reuses the cache; - **Latency to first row** equals the time to fetch everything. For a bounded query — a sliced page, a user's unread notifications — that is exactly right. ## What `aiterator()` does differently `aiterator()` is the async version of `iterator()`. Its signature in Django 6.1 is `aiterator(chunk_size=2000)`. | Aspect | `async for` over the QuerySet | `await`-driven `qs.aiterator()` | |---|---|---| | Fetching | Everything in one crossing | One crossing per chunk of `chunk_size` rows | | Result cache | Filled | Bypassed | | Memory | Whole result set | About one chunk | | Server-side cursor | No | Yes on PostgreSQL (unless `DISABLE_SERVER_SIDE_CURSORS`) and Oracle | | `prefetch_related()` | Supported | Supported since 5.0, prefetched per chunk | | Re-iterating | Uses the cache | Runs the query again | The chunking lives in Django's iterable classes: a synchronous generator is created once and advanced one slice at a time through `sync_to_async`, and since those calls are thread-sensitive, every chunk runs on the same worker thread and connection. ## Choosing between them - **Pages and summaries** — latest 50 notifications, unread counts per type: `async for` (or an `async for` comprehension). Simple, cached, one crossing. - **Large one-pass jobs** — export a user's entire notification history, backfill a field over millions of rows: `aiterator()` with a chunk size tuned to row width. - **Streaming responses** — an async generator feeding a streaming response can iterate `aiterator()` and yield as it goes, so memory stays flat. - **Anything re-read** — if you will loop twice, prefer the cached form or materialise a list once. ## Caveats to mention - Each chunk is a separate thread handoff; very small chunk sizes trade memory for many crossings. - Server-side cursors interact with transaction-pooling connection poolers; the docs point at `DISABLE_SERVER_SIDE_CURSORS` for that setup. - `aiterator()` with `prefetch_related()` performs the extra prefetch queries per chunk, so the chunk size also controls the size of the `IN` lists in those queries. ## Worked sizing example A user with 400,000 notifications asks for a full export. 1. `async for` over the QuerySet would load all 400,000 model instances into the result cache before writing the first line — hundreds of megabytes and a long silent wait. 2. `aiterator(chunk_size=1000)` over `values()` holds about 1,000 small dicts at a time and starts writing after the first chunk; the export takes 400 crossings instead of one. 3. Using `values()` or `values_list()` rather than model instances cuts per-row memory further, which matters more than the chunk size once rows are wide. The same reasoning applies to background backfills driven from async code. ## The short answer interviewers look for "`async for` is not streaming — it fills the result cache in one go. For big result sets I use `aiterator()`, which fetches in chunks, skips the cache and can stream from a server-side cursor."

  • Does iterating a Django QuerySet twice with async for run the query twice?
    No. The first `async for` fills the QuerySet's result cache through one `_fetch_all()` crossing; the second iterates the cache. By contrast, calling `aiterator()` again always re-runs the query, because it bypasses the cache.
  • Why might a very small chunk_size make aiterator() slow in Django?
    Each chunk is fetched through its own `sync_to_async` handoff and, without server-side cursors, its own driver batch. With a tiny chunk size, handoffs and round-trips dominate. Tune the size to row width: large enough to amortise crossings, small enough to bound memory.

saying these in an interview costs you the question

  • async for over a QuerySet streams rows one by one from the database.
  • aiterator() fills the result cache, so a second loop is free.
  • aiterator() ignores prefetch_related() in current Django.
  • Using aiterator() for a 20-row page is always the better choice.
  • Every row fetched by aiterator() costs its own thread handoff.