skip to content

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%

answer

  1. rows per INSERT or UPDATE statement
  2. default depends on backend limits
  3. the input is turned into a list
  4. slice the source with islice

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.

solid answer

~40 s

`bulk_create(objs, batch_size=...)` splits the insert into statements of at most `batch_size` rows; `bulk_update(objs, fields, batch_size=...)` does the same for its `CASE WHEN` updates. The default is "as many as the database allows": one statement on PostgreSQL with client-side binding, while SQLite and Oracle cap query parameters, so Django computes a smaller maximum and never exceeds it even if you ask for more. What `batch_size` does **not** do is save Python memory: `bulk_create()` casts `objs` to a list, fully consuming a generator, and `bulk_update()` builds the `WHEN` clauses for **every** batch before running any query. For millions of rows, slice the source with `itertools.islice` and call the bulk method per slice. Multiple batches inside one call run in one transaction, so a failure rolls back that whole call.

code

python · 16 lines
python
import csv
from itertools import islice

from telemetry.models import Reading

SIZE = 5000


def import_file(path, device_id):
    with open(path, newline="") as fh:
        source = (
            Reading(device_id=device_id, recorded_at=r["ts"], metric=r["metric"], value=r["value"])
            for r in csv.DictReader(fh)
        )
        while batch := list(islice(source, SIZE)):
            Reading.objects.bulk_create(batch, batch_size=SIZE)  # one INSERT per slice

go deeper

for a junior

Recall that batch_size limits how many rows go into each INSERT or UPDATE statement.

for a middle

Explain the backend-dependent default, why PostgreSQL defaults to one statement, and why batch_size does not bound Python memory.

for a senior

Build imports that slice the source, choose batch sizes by row width, and decide where transaction boundaries fall for restartability.

for a principal

Weigh ORM bulk calls against database-native loading paths for very large imports, and set the operational rules for running them.

## What `batch_size` controls Both bulk methods turn many objects into few SQL statements: - **`bulk_create(objs, batch_size=None, ...)`** — one multi-row `INSERT` per batch. - **`bulk_update(objs, fields, batch_size=None)`** — one `UPDATE ... SET col = CASE WHEN pk=1 THEN ... END WHERE pk IN (...)` per batch. `batch_size` is the **maximum number of objects per statement**. It shapes the SQL sent to the database, not the amount of Python memory used. ## The default, backend by backend Django asks the backend for the largest safe batch (`connection.ops.bulk_batch_size()`), based on the database's **limit on query parameters**: | Backend | Parameter limit | Default batch | |---|---|---| | PostgreSQL (client-side binding, the default) | none enforced by Django | all objects in one statement | | PostgreSQL with server-side binding | 65,535 | derived from fields per row | | SQLite | the connection's variable limit | derived from fields per row | | Oracle | 65,535 | derived from fields per row | If you pass a `batch_size` larger than the computed maximum, Django uses the maximum. For `bulk_update`, the primary key counts twice per object (once in the `WHEN`, once in the `IN`), so its batches are smaller for the same number of fields. On PostgreSQL the default means **one enormous statement** for a million objects: a huge SQL string, a long-running statement and heavy memory on both sides. Setting `batch_size` to a few thousand keeps statements reasonable. ## Why `batch_size` does not bound Python memory 1. **`bulk_create()` lists its input.** The documentation states it casts `objs` to a list, fully evaluating a generator, so it can put objects with manually set primary keys first. Passing a generator of ten million unsaved objects still builds all ten million. 2. **`bulk_update()` prepares every batch first.** It builds the `Case`/`When` expressions for all batches before executing the first query; the docs warn this can take more memory than expected. 3. **The objects themselves.** Whatever produced the objects — a CSV reader, a list comprehension, a QuerySet — has to be bounded too. The documented pattern is to slice the source yourself: ```python from itertools import islice def load(readings_iter, size=5000): while batch := list(islice(readings_iter, size)): Reading.objects.bulk_create(batch, batch_size=size) ``` For updates, pair it with streamed ids: fetch a slice of ids, load those objects, change them, `bulk_update()` that slice. ## Transactions and failure - When one `bulk_create()` call needs **more than one batch**, Django wraps the batches in one `atomic()` block, so an error in batch 7 rolls back batches 1–6 of that call. - `bulk_update()` always runs its batches inside one `atomic()` block and returns the number of rows matched. - Separate calls made from your own slicing loop are separate transactions (in autocommit) unless you wrap them. That is usually what you want for a long import: each slice commits, and a restart can skip slices already loaded. ## What bulk calls skip `batch_size` changes nothing about the per-object behaviour bulk methods bypass, which matters when replacing a `save()` loop: - `bulk_create()` and `bulk_update()` do not call each model's `save()` and do not send `pre_save`/`post_save` signals. - `bulk_create()` sets primary keys on the objects only on some backends (PostgreSQL, MariaDB and SQLite for auto-generated keys). - `bulk_update()` cannot change primary keys, and duplicates within one batch update only once. If code relies on signals or `save()` overrides, a bulk rewrite needs that logic moved elsewhere first. ## Choosing a number - Start around a few thousand rows per batch and measure; very small batches waste round trips, very large ones create huge statements. - Wide rows (many columns, large text) deserve smaller batches. - On SQLite and Oracle, Django already enforces the ceiling; your value only needs to be at or below it.

  • Why can passing a generator to bulk_create() still exhaust memory?
    `bulk_create()` casts `objs` to a list before inserting, so the whole generator is consumed and every object held at once. `batch_size` only splits the SQL afterwards. Slice the generator yourself with `islice` and call `bulk_create()` per slice.
  • What happens if batch 7 of a single bulk_create() call fails?
    When one call spans several batches, Django runs them inside one `atomic()` block, so the error rolls back the batches already inserted by that call. Calls made separately from your own slicing loop commit independently in autocommit mode.

saying these in an interview costs you the question

  • Believes batch_size makes bulk_create() stream a generator lazily
  • Assumes the PostgreSQL default batch is small
  • Thinks each batch of one bulk_create() call commits separately
  • Expects bulk_update() to build only one batch in memory at a time
  • Says a batch_size above the SQLite limit causes an error