A Django management command exporting ten million telemetry readings to CSV is killed for memory; how would you rewrite its query loop?

seniorshould knowfreq 42%

answer

stop building model instances
stream instead of caching
join in SQL, not per row
keyset batches as the fallback

basics

~20 s

Project only the needed columns with values_list(), including related columns via __ lookups, stream them with iterator(chunk_size=...), and write rows straight to the file. Where streaming is unavailable, page by primary key in fixed batches.

solid answer

~40 s

The original loop is usually for r in Reading.objects.all(): writer.writerow([r.id, r.device.serial, ...]): it fills the result cache with ten million instances and adds a query per row for device. Rewrite it as Reading.objects.filter(...).order_by("pk").values_list("pk", "device__serial", "recorded_at", "metric", "value").iterator(chunk_size=5000) passed to csv.writer(...).writerows(): tuples instead of instances, the device serial joined in SQL, nothing cached, and on PostgreSQL a server-side cursor feeding 5000 rows per round trip. Avoid anything that materialises the set — len(), list(), if qs: — and do not collect rows in Python. If server-side cursors are unavailable (MySQL, or a transaction-pooling pooler), switch to keyset batches: filter(pk__gt=last_pk).order_by("pk")[:5000] in a loop, each a short, independent query.

The original loop is usually `for r in Reading.objects.all(): writer.writerow([r.id, r.device.serial, ...])`: it fills the result cache with ten million instances and adds a query per row for `device`. Rewrite it as `Reading.objects.filter(...).order_by("pk").values_list("pk", "device__serial", "recorded_at", "metric", "value").iterator(chunk_size=5000)` passed to `csv.writer(...).writerows()`: tuples instead of instances, the device serial joined in SQL, nothing cached, and on PostgreSQL a server-side cursor feeding 5000 rows per round trip. Avoid anything that materialises the set — `len()`, `list()`, `if qs:` — and do not collect rows in Python. If server-side cursors are unavailable (MySQL, or a transaction-pooling pooler), switch to **keyset batches**: `filter(pk__gt=last_pk).order_by("pk")[:5000]` in a loop, each a short, independent query.

code

python · 18 lines

python

from telemetry.models import Reading

BATCH = 5000


def keyset_batches(day):
    base = Reading.objects.filter(recorded_at__date=day).order_by("pk")
    last_pk = 0
    while True:
        batch = list(
            base.filter(pk__gt=last_pk).values_list(
                "pk", "device__serial", "recorded_at", "metric", "value"
            )[:BATCH]
        )
        if not batch:
            return
        yield from batch
        last_pk = batch[-1][0]

go deeper

for a junior

Recall that looping over a big QuerySet loads every row first, and that values_list() plus iterator() avoids it.

for a middle

Explain each fix: projection, join via __ lookup, iterator(chunk_size), and why len() or if qs: undo them.

for a senior

Choose between a streamed cursor and keyset batches based on backend, pooler and run length, and make the export resumable.

for a principal

Decide whether bulk exports belong in the application at all or in a database-native path, weighing load on the primary against operational simplicity.

The failing command

A typical first version:

python

for r in Reading.objects.filter(recorded_at__date=day):
    writer.writerow([r.id, r.device.serial, r.recorded_at, r.metric, r.value])

Three separate problems, each Django-specific:

The result cache. Iterating a QuerySet evaluates it with _fetch_all(), building all ten million Reading instances before the first row is written.
Model instances. Each carries _state, every column and descriptors; for an export that only formats five values, that is pure overhead.
A query per row. r.device is a forward foreign key loaded on first access — ten million extra queries under the default FETCH_ONE mode.

The rewrite

python

import csv

from django.core.management.base import BaseCommand

from telemetry.models import Reading


class Command(BaseCommand):
    help = "Export one day of readings to CSV"

    def add_arguments(self, parser):
        parser.add_argument("day")
        parser.add_argument("path")

    def handle(self, *args, **options):
        rows = (
            Reading.objects.filter(recorded_at__date=options["day"])
            .order_by("pk")
            .values_list("pk", "device__serial", "recorded_at", "metric", "value")
            .iterator(chunk_size=5000)
        )
        with open(options["path"], "w", newline="") as fh:
            writer = csv.writer(fh)
            writer.writerow(["id", "device", "recorded_at", "metric", "value"])
            writer.writerows(rows)

What each piece does:

values_list(...) yields tuples, skipping instance construction entirely.
device__serial turns the per-row foreign key access into a join in the single query.
iterator(chunk_size=5000) bypasses the result cache; on PostgreSQL it uses a server-side cursor and pulls 5000 rows per round trip.
writerows(rows) consumes the generator directly, so no list of ten million rows ever exists.
order_by("pk") gives a stable, index-friendly order and makes a restart point possible.

Things that silently undo it

Code	Why it hurts
`total = len(qs)` before the loop	evaluates and caches the whole QuerySet; use `qs.count()`
`if qs:`	evaluates the QuerySet too; use `qs.exists()`
`rows = list(qs.iterator())`	rebuilds the cache by hand
`r.device.serial` inside the loop	a query per row; project it instead
`.prefetch_related(...)` without `chunk_size`	`iterator()` raises `ValueError`

When a server-side cursor is not an option

Streaming depends on the backend and the connection path:

MySQL: the driver loads the full result into memory even with iterator().
Transaction-pooling poolers in front of PostgreSQL: server-side cursors must be disabled (DISABLE_SERVER_SIDE_CURSORS = True) or confined to a separate alias or an atomic() block.
Very long exports: one cursor holds a connection, and a snapshot, for the whole run.

The fallback is keyset batching on the primary key:

Start with last_pk = 0.
Fetch list(qs.filter(pk__gt=last_pk).order_by("pk").values_list(...)[:5000]).
Write the batch; set last_pk to the last row's pk; stop when a batch is empty.

Each batch is a short, indexed query with no cursor to keep open, and the command can resume from a saved last_pk. Avoid OFFSET-based slicing (qs[i:i+5000] with growing i) for this: each page makes the database skip all earlier rows, so later pages get slower.

Making the command operable

A long export should survive restarts and report what it is doing:

Log progress every N rows with self.stdout.write(), using a counter rather than re-counting the QuerySet.
Record the last primary key written so a rerun can start from pk__gt=last_pk instead of from the beginning.
Write to a temporary file and rename it at the end, so consumers never read a half-written CSV.
Run it off the web path — a management command scheduled by the platform, or a background task — so no request timeout applies.

Settings worth checking

With DEBUG = True, Django records each query's SQL in connection.queries, capped at the most recent 9,000 entries; for a single streamed query it is harmless, but a per-row loop with DEBUG on adds overhead to every query. Run exports with production settings.

## The failing command A typical first version: ```python for r in Reading.objects.filter(recorded_at__date=day): writer.writerow([r.id, r.device.serial, r.recorded_at, r.metric, r.value]) ``` Three separate problems, each Django-specific: 1. **The result cache.** Iterating a QuerySet evaluates it with `_fetch_all()`, building all ten million `Reading` instances before the first row is written. 2. **Model instances.** Each carries `_state`, every column and descriptors; for an export that only formats five values, that is pure overhead. 3. **A query per row.** `r.device` is a forward foreign key loaded on first access — ten million extra queries under the default `FETCH_ONE` mode. ## The rewrite ```python import csv from django.core.management.base import BaseCommand from telemetry.models import Reading class Command(BaseCommand): help = "Export one day of readings to CSV" def add_arguments(self, parser): parser.add_argument("day") parser.add_argument("path") def handle(self, *args, **options): rows = ( Reading.objects.filter(recorded_at__date=options["day"]) .order_by("pk") .values_list("pk", "device__serial", "recorded_at", "metric", "value") .iterator(chunk_size=5000) ) with open(options["path"], "w", newline="") as fh: writer = csv.writer(fh) writer.writerow(["id", "device", "recorded_at", "metric", "value"]) writer.writerows(rows) ``` What each piece does: - **`values_list(...)`** yields tuples, skipping instance construction entirely. - **`device__serial`** turns the per-row foreign key access into a join in the single query. - **`iterator(chunk_size=5000)`** bypasses the result cache; on PostgreSQL it uses a server-side cursor and pulls 5000 rows per round trip. - **`writerows(rows)`** consumes the generator directly, so no list of ten million rows ever exists. - **`order_by("pk")`** gives a stable, index-friendly order and makes a restart point possible. ## Things that silently undo it | Code | Why it hurts | |---|---| | `total = len(qs)` before the loop | evaluates and caches the whole QuerySet; use `qs.count()` | | `if qs:` | evaluates the QuerySet too; use `qs.exists()` | | `rows = list(qs.iterator())` | rebuilds the cache by hand | | `r.device.serial` inside the loop | a query per row; project it instead | | `.prefetch_related(...)` without `chunk_size` | `iterator()` raises `ValueError` | ## When a server-side cursor is not an option Streaming depends on the backend and the connection path: - **MySQL**: the driver loads the full result into memory even with `iterator()`. - **Transaction-pooling poolers** in front of PostgreSQL: server-side cursors must be disabled (`DISABLE_SERVER_SIDE_CURSORS = True`) or confined to a separate alias or an `atomic()` block. - **Very long exports**: one cursor holds a connection, and a snapshot, for the whole run. The fallback is **keyset batching** on the primary key: 1. Start with `last_pk = 0`. 2. Fetch `list(qs.filter(pk__gt=last_pk).order_by("pk").values_list(...)[:5000])`. 3. Write the batch; set `last_pk` to the last row's `pk`; stop when a batch is empty. Each batch is a short, indexed query with no cursor to keep open, and the command can resume from a saved `last_pk`. Avoid `OFFSET`-based slicing (`qs[i:i+5000]` with growing `i`) for this: each page makes the database skip all earlier rows, so later pages get slower. ## Making the command operable A long export should survive restarts and report what it is doing: - **Log progress** every N rows with `self.stdout.write()`, using a counter rather than re-counting the QuerySet. - **Record the last primary key written** so a rerun can start from `pk__gt=last_pk` instead of from the beginning. - **Write to a temporary file and rename it** at the end, so consumers never read a half-written CSV. - **Run it off the web path** — a management command scheduled by the platform, or a background task — so no request timeout applies. ## Settings worth checking With `DEBUG = True`, Django records each query's SQL in `connection.queries`, capped at the most recent 9,000 entries; for a single streamed query it is harmless, but a per-row loop with `DEBUG` on adds overhead to every query. Run exports with production settings.

Why not export with qs[i:i + 5000] slices in a loop?
Each slice becomes `LIMIT 5000 OFFSET i`, and the database must walk past every earlier row to reach the offset, so later pages get progressively slower. Filtering on `pk__gt=last_pk` with `order_by("pk")` uses the primary key index and costs the same for every batch.
How do you report progress without evaluating the whole QuerySet?
Call `qs.count()` once, which runs `SELECT COUNT(*)` and returns a number without loading rows, then count rows as you write them. `len(qs)` would evaluate and cache every row, defeating the streaming.

saying these in an interview costs you the question

Uses len(queryset) to get the row count before streaming
Reads r.device.serial in the loop instead of projecting device__serial
Paginates a ten-million-row export with growing OFFSET slices
Assumes iterator() streams from MySQL without driver buffering
Collects all rows into a list before writing the file

A Django management command exporting ten million telemetry readings to CSV is killed for memory; how would you rewrite its query loop?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The failing command

The rewrite

Things that silently undo it

When a server-side cursor is not an option

Making the command operable

Settings worth checking

▾common follow-ups2 questions

▾red flags5 flags

The failing command

The rewrite

Things that silently undo it

When a server-side cursor is not an option

Making the command operable

Settings worth checking

answer

go deeper