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.

  • 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