skip to content

Large Result Sets

2 roadmaps5 questions

Handling big result sets: values() and values_list() skip model instances, iterator(chunk_size) streams rows past the result cache, and batch_size bounds bulk calls. Interviewers probe memory.

part ofDjangooverview, primer and where to startread it →
on this pageshow

explore

questions

5

In Django, what do values() and values_list() return instead of model instances, and what do flat=True and named=True change?

level: juniorimportance: must knowfreq 60%

answer

  1. dicts versus tuples
  2. no model objects built
  3. flat needs exactly one field
  4. named gives Row namedtuples

basics

~10 s

values() yields dictionaries keyed by field name and values_list() yields tuples, both skipping model instances. flat=True with one field returns bare values; named=True returns namedtuples called Row.

solid answer

~40 s

`Reading.objects.values("id", "metric")` is still a lazy `QuerySet`, but iterating it yields dicts like `{"id": 1, "metric": "temp"}`; `values_list("id", "metric")` yields tuples `(1, "temp")`. Neither builds model instances, so there are no methods, no `save()`, no related-object access — and much less CPU and memory per row, which is why they suit exports and ID lists. `values_list("id", flat=True)` yields plain values (`[1, 2, 3]`) and is only valid with exactly one field; `named=True` yields `Row(id=1, metric='temp')` namedtuples at a small cost; the two cannot be combined. A `ForeignKey` appears as `device_id` unless you ask for `device__serial`, which adds a join. Django 6.1 deprecates `flat=True` with no field name — pass one, such as `"pk"`.

code

python · 13 lines
python
from telemetry.models import Reading

Reading.objects.values("id", "metric")[:2]
# <QuerySet [{'id': 1, 'metric': 'temp'}, {'id': 2, 'metric': 'rpm'}]>

Reading.objects.values_list("id", "device__serial")[:2]
# <QuerySet [(1, 'SN-001'), (2, 'SN-001')]>   (join on device)

ids = list(Reading.objects.filter(metric="temp").values_list("pk", flat=True))
# [1, 5, 9, ...]

for row in Reading.objects.values_list("id", "value", named=True)[:1]:
    print(row.id, row.value)

go deeper

for a junior

Recall the shapes: values() gives dicts, values_list() gives tuples, flat=True with one field gives bare values.

for a middle

Explain the flat and named rules, how foreign keys and relation lookups appear, and why multi-valued relations multiply rows.

for a senior

Choose projections deliberately for exports and bulk reads, and know when instances are needed so only() is the better trim.

for a principal

Set conventions for data-only paths — reporting, exports, ID lookups — so they never build model instances they do not use.

## Two projections of the same query A normal `QuerySet` builds a **model instance** for every row: it calls `from_db()`, sets up state, and gives you an object with methods and related-object descriptors. When you only need data, that work is wasted. Django offers two **projections** that keep the query lazy and chainable but change what iteration yields: | Call | Each row becomes | Example | |---|---|---| | `Reading.objects.all()` | a `Reading` instance | `<Reading: 1>` | | `.values("id", "metric")` | a `dict` | `{"id": 1, "metric": "temp"}` | | `.values_list("id", "metric")` | a `tuple` | `(1, "temp")` | | `.values_list("id", flat=True)` | a bare value | `1` | | `.values_list("id", "metric", named=True)` | a `Row` namedtuple | `Row(id=1, metric='temp')` | All of these are still `QuerySet`s: you can `filter()`, `order_by()`, slice, `count()` or call `iterator()` on them, and calling `filter()` before or after `values()` produces the same SQL. ## The rules of `values_list()` 1. **`flat=True` needs exactly one field.** With two or more, Django raises `TypeError: 'flat' is not valid when values_list is called with more than one field.` 2. **`flat` and `named` are exclusive.** Passing both raises `TypeError`. 3. **No fields means all fields**, in declaration order. 4. **Django 6.1 deprecation:** `values_list(flat=True)` with no field name now emits `RemovedInDjango70Warning`; write `values_list("pk", flat=True)`. 5. **`named=True` has a small cost** for building namedtuples, in exchange for readable attribute access. A common single-value idiom: `Reading.objects.values_list("value", flat=True).get(pk=42)` returns just that number. ## How fields and relations appear - A `ForeignKey` named `device` appears as **`device_id`** in a plain `values()` call — the raw column, no join. - **`device__serial`** follows the relation with a join and returns the related column. - Expressions work too: `values(metric_lower=Lower("metric"))` or `values_list("id", Lower("metric"))`. - **Multi-valued relations multiply rows.** `values("name", "tags__label")` returns one dict per tag, and a row with no tags gets `None`. The documentation warns that the "one row, one object" assumption breaks for many-to-many and reverse foreign keys. ## Why this matters for large results For a list of thousands of rows the difference is easy to see: a tuple of five values is far cheaper to create and hold than a model instance with its `_state`, descriptors and every column loaded. Choosing the projection is often the first step in making a big read fit in memory, before chunked iteration is even considered. It also removes a class of hidden queries. A dict has no `device` attribute to lazy-load, so a template or export that accidentally reached across a relation fails loudly instead of issuing one query per row. ## Memory in numbers The saving is easy to demonstrate in an interview. For a `Reading` with a foreign key and four value columns: - A **model instance** holds its field values in `__dict__`, plus a `ModelState` in `_state` with its database alias, `adding` flag and fetch mode, plus caches for related objects once touched. - A **dict** from `values()` holds only the requested keys and values. - A **tuple** from `values_list()` holds only the values, with no key strings per row. Instance construction also runs Python code per row — `from_db()`, field conversion into attributes, and on 6.1 fetch-mode bookkeeping — so projections save CPU as well as memory. For a job that formats five columns into a CSV line, tuples are the natural unit. Two cautions keep the comparison honest: 1. Projections still need **field conversion**: a `DateTimeField` comes back as an aware `datetime`, a `DecimalField` as `Decimal`. The saving is the object overhead, not the conversion. 2. A projection still sits in the **result cache** if you iterate the QuerySet normally. For very large results, combine it with `iterator()`. ## When not to use them - You need **model behaviour**: methods, properties, `save()`, `get_absolute_url()`. - You need **related managers** or prefetched collections on each row. - Code downstream expects instances — passing dicts to a `ModelForm` or a model-based serializer will not work. In those cases trim columns with `only()` instead, which keeps instances.

  • What does values("device") return for a ForeignKey called device?
    The foreign key's raw value — the related primary key — under the key `device`; a plain `values()` with no arguments names it `device_id`. No join happens. To get a column of the related model, ask for `device__serial`, which joins the device table.
  • Why can values("name", "tags__label") return more rows than there are objects?
    Following a many-to-many or reverse foreign key joins one row per related record, so an object with three tags appears three times and one with none appears once with `None`. The projection returns joined rows, not objects.

saying these in an interview costs you the question

  • Says values() returns a list rather than a lazy QuerySet
  • Uses values_list("id", "name", flat=True) expecting a flat list
  • Expects values() rows to have methods such as save()
  • Believes values("device") returns the related Device object
  • Thinks values() and only() both return dictionaries
open as a page

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%

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.

open as a page

In Django, what does batch_size do on bulk_create() and bulk_update(), and how do you load millions of rows without holding them all in memory?

level: middleimportance: should knowfreq 38%

basics

~20 s

batch_size caps how many objects go into each INSERT or UPDATE statement; without it Django uses one batch unless the backend limits query parameters. It does not reduce Python memory, since bulk_create() lists its input, so feed it slices.

open as a page

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

level: seniorimportance: should knowfreq 42%

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.

open as a page

Why can Django's QuerySet.iterator() fail behind a PostgreSQL pooler in transaction pooling mode, and what are the fixes Django documents?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

iterator() opens a server-side cursor that exists on one connection and outlives its autocommit transaction; a transaction pooler may send the next fetch elsewhere. Fix: disable server-side cursors, use a direct alias, or wrap in atomic().

open as a page