When importing 200,000 attendee rows with Django's bulk_create() and later correcting them with bulk_update(), which caveats and batch_size choices matter?

seniorshould knowfreq 42%

answer

one statement per batch, no save()
primary keys only on some backends
ignore_conflicts versus update_conflicts
CASE WHEN per batch
in_bulk to map keys

basics

~20 s

bulk_create() inserts in batches without save() or save signals, sets primary keys only on PostgreSQL, MariaDB and SQLite, and loses them with ignore_conflicts. bulk_update() writes CASE WHEN updates per batch; batch_size bounds statement size and memory.

solid answer

~40 s

bulk_create(objs, batch_size=None, ...) turns the list into one multi-row INSERT per batch, wrapped in a transaction when there are several batches. It skips save() and the pre_save/post_save signals, does not handle many-to-many fields or multi-table-inheritance children, and casts objs to a list, so a generator is fully materialised. Primary keys come back only on PostgreSQL, MariaDB and SQLite, and not at all with ignore_conflicts=True; update_conflicts=True with update_fields and unique_fields gives an upsert. bulk_update(objs, fields, batch_size=None) writes each batch as an UPDATE ... SET col = CASE WHEN pk = ... END, returns the rows updated, and builds every WHEN clause before running anything. Leaving batch_size unset means as large as the backend allows; set it to keep statements and memory bounded, and use in_bulk(field_name="email") to map CSV keys to existing rows.

`bulk_create(objs, batch_size=None, ...)` turns the list into one multi-row `INSERT` per batch, wrapped in a transaction when there are several batches. It skips `save()` and the `pre_save`/`post_save` signals, does not handle many-to-many fields or multi-table-inheritance children, and casts `objs` to a list, so a generator is fully materialised. Primary keys come back only on PostgreSQL, MariaDB and SQLite, and not at all with `ignore_conflicts=True`; `update_conflicts=True` with `update_fields` and `unique_fields` gives an upsert. `bulk_update(objs, fields, batch_size=None)` writes each batch as an `UPDATE ... SET col = CASE WHEN pk = ... END`, returns the rows updated, and builds every `WHEN` clause before running anything. Leaving `batch_size` unset means as large as the backend allows; set it to keep statements and memory bounded, and use `in_bulk(field_name="email")` to map CSV keys to existing rows.

code

python · 32 lines

python

from itertools import islice

from django.db import models


class Attendee(models.Model):
    email = models.EmailField(unique=True)
    name = models.CharField(max_length=200)


def import_rows(rows):  # rows: iterable of {"email": ..., "name": ...}
    rows = list(rows)
    existing = Attendee.objects.in_bulk(
        [r["email"] for r in rows], field_name="email"
    )
    to_update = []
    new_objs = (
        Attendee(email=r["email"], name=r["name"])
        for r in rows
        if r["email"] not in existing
    )
    for r in rows:
        if (obj := existing.get(r["email"])) and obj.name != r["name"]:
            obj.name = r["name"]
            to_update.append(obj)

    while batch := list(islice(new_objs, 2000)):
        Attendee.objects.bulk_create(batch, batch_size=2000)
    for start in range(0, len(to_update), 2000):
        Attendee.objects.bulk_update(
            to_update[start : start + 2000], ["name"], batch_size=2000
        )

go deeper

for a junior

Recall that bulk_create() and bulk_update() write many rows in few queries and skip save() and its signals.

for a middle

Explain which backends return primary keys, what ignore_conflicts and update_conflicts change, and how bulk_update() builds CASE WHEN statements per batch.

for a senior

Design the import: validate first, map existing rows with in_bulk(), chunk input with islice, set batch_size, and replay the side effects that receivers would have done.

for a principal

Decide when an ORM bulk path is enough and when a load through a database-native copy command or a staging table is worth the loss of model-level behaviour.

What the two methods do

Both are Django QuerySet methods meant for many rows at once.

bulk_create(objs, batch_size=None, ignore_conflicts=False, update_conflicts=False, update_fields=None, unique_fields=None) inserts a list of unsaved model instances with a multi-row INSERT per batch and returns the same objects, in the same order.
bulk_update(objs, fields, batch_size=None) writes the named fields of already-saved instances back with one UPDATE per batch and returns the number of rows updated.

Both are dramatically faster than calling save() 200,000 times, and both give up per-object behaviour to get there.

`bulk_create()` caveats

No save(), no save signals. Overridden save() methods and pre_save/post_save receivers do not run. Field pre_save() hooks do run while the INSERT is assembled, so auto_now_add timestamps are filled.
No model validation. Nothing calls full_clean(); validate the CSV before building instances.
Primary keys. For an auto-increment or db_default primary key, the new values are set on the objects only on PostgreSQL, MariaDB and SQLite; on other databases obj.pk stays None.
ignore_conflicts=True tells the database to skip rows that violate a unique constraint, and in exchange disables setting primary keys on every object. On MySQL and MariaDB it also downgrades some other errors to warnings.
update_conflicts=True turns the insert into an upsert: conflicting rows get update_fields overwritten. On PostgreSQL and SQLite you must also pass unique_fields. Since Django 5.0 primary keys are set in this mode where the database supports it.
No many-to-many. Insert the through-model rows with their own bulk_create() once the parent keys exist.
No multi-table inheritance children: Django raises ValueError("Can't bulk create a multi-table inherited model").
The input is cast to a list, so passing a generator of 200,000 objects builds all of them in memory. Feed it chunks with itertools.islice instead.

`bulk_update()` caveats

bulk_update() builds, for each field, a CASE WHEN pk = 1 THEN ... WHEN pk = 2 THEN ... END expression and runs filter(pk__in=batch).update(...) inside transaction.atomic(). That shape has consequences:

It cannot update the primary key, and every object must already have one.
It skips save(), the save signals and field pre_save() hooks, so auto_now columns do not move.
It prepares every WHEN clause for every batch before executing any query, so a huge list costs memory even with a small batch_size. The documentation's remedy is to loop over slices yourself.
Duplicates inside one batch: only the first instance produces an update, and the returned count can be lower than len(objs).
Fields on multi-table inheritance ancestors cost an extra query per ancestor.

Choosing `batch_size`

Setting	`bulk_create()`	`bulk_update()`
`batch_size=None`	as many rows per INSERT as the backend allows	one batch, except on SQLite and Oracle
explicit value	capped at the backend's maximum	capped at the backend's maximum
why set it	keeps each statement and its parameter list bounded	keeps each CASE expression and SQL string small

SQLite and Oracle limit the number of parameters in one query, and Django computes the maximum batch from the number of fields. On PostgreSQL with the default client-side parameter binding there is no cap, so a 200,000-row list becomes one enormous statement unless you set a value such as 1,000 to 5,000.

Replaying what the bulk path skipped

Because no save() override or save receiver ran, list what they normally do for this model and run it once per batch instead: rebuilding a search index entry, invalidating cached counts, sending welcome e-mails through a task queue, writing an audit row. Doing it per batch keeps it cheap and makes the omission deliberate rather than accidental.

A realistic import

Parse the CSV into dicts and validate them.
Load the rows that already exist with in_bulk(): Attendee.objects.in_bulk(emails, field_name="email") returns a dict from email to instance, silently ignoring emails that are not found. field_name must be a unique field.
Split into new and existing rows.
bulk_create() the new ones in islice chunks with an explicit batch_size.
Change attributes on the existing instances and bulk_update() them with the list of fields you touched.
Run any side effects that save() receivers would have done explicitly, because none of them fired.

## What the two methods do Both are Django **QuerySet methods** meant for many rows at once. - **`bulk_create(objs, batch_size=None, ignore_conflicts=False, update_conflicts=False, update_fields=None, unique_fields=None)`** inserts a list of **unsaved model instances** with a multi-row `INSERT` per batch and returns the same objects, in the same order. - **`bulk_update(objs, fields, batch_size=None)`** writes the named `fields` of already-saved instances back with one `UPDATE` per batch and returns the number of rows updated. Both are dramatically faster than calling `save()` 200,000 times, and both give up per-object behaviour to get there. ## `bulk_create()` caveats - **No `save()`, no save signals.** Overridden `save()` methods and `pre_save`/`post_save` receivers do not run. Field `pre_save()` hooks do run while the INSERT is assembled, so `auto_now_add` timestamps are filled. - **No model validation.** Nothing calls `full_clean()`; validate the CSV before building instances. - **Primary keys.** For an auto-increment or `db_default` primary key, the new values are set on the objects **only on PostgreSQL, MariaDB and SQLite**; on other databases `obj.pk` stays `None`. - **`ignore_conflicts=True`** tells the database to skip rows that violate a unique constraint, and in exchange **disables setting primary keys** on every object. On MySQL and MariaDB it also downgrades some other errors to warnings. - **`update_conflicts=True`** turns the insert into an upsert: conflicting rows get `update_fields` overwritten. On PostgreSQL and SQLite you must also pass `unique_fields`. Since Django 5.0 primary keys are set in this mode where the database supports it. - **No many-to-many.** Insert the through-model rows with their own `bulk_create()` once the parent keys exist. - **No multi-table inheritance children**: Django raises `ValueError("Can't bulk create a multi-table inherited model")`. - **The input is cast to a list**, so passing a generator of 200,000 objects builds all of them in memory. Feed it chunks with `itertools.islice` instead. ## `bulk_update()` caveats `bulk_update()` builds, for each field, a `CASE WHEN pk = 1 THEN ... WHEN pk = 2 THEN ... END` expression and runs `filter(pk__in=batch).update(...)` inside `transaction.atomic()`. That shape has consequences: - It **cannot update the primary key**, and every object must already have one. - It skips `save()`, the save signals **and** field `pre_save()` hooks, so `auto_now` columns do not move. - It **prepares every `WHEN` clause for every batch before executing any query**, so a huge list costs memory even with a small `batch_size`. The documentation's remedy is to loop over slices yourself. - Duplicates inside one batch: only the first instance produces an update, and the returned count can be lower than `len(objs)`. - Fields on multi-table inheritance ancestors cost an extra query per ancestor. ## Choosing `batch_size` | Setting | `bulk_create()` | `bulk_update()` | |---|---|---| | `batch_size=None` | as many rows per INSERT as the backend allows | one batch, except on SQLite and Oracle | | explicit value | capped at the backend's maximum | capped at the backend's maximum | | why set it | keeps each statement and its parameter list bounded | keeps each CASE expression and SQL string small | SQLite and Oracle limit the number of parameters in one query, and Django computes the maximum batch from the number of fields. On PostgreSQL with the default client-side parameter binding there is no cap, so a 200,000-row list becomes one enormous statement unless you set a value such as 1,000 to 5,000. ## Replaying what the bulk path skipped Because no `save()` override or save receiver ran, list what they normally do for this model and run it once per batch instead: rebuilding a search index entry, invalidating cached counts, sending welcome e-mails through a task queue, writing an audit row. Doing it per batch keeps it cheap and makes the omission deliberate rather than accidental. ## A realistic import 1. Parse the CSV into dicts and validate them. 2. Load the rows that already exist with **`in_bulk()`**: `Attendee.objects.in_bulk(emails, field_name="email")` returns a dict from email to instance, silently ignoring emails that are not found. `field_name` must be a unique field. 3. Split into new and existing rows. 4. `bulk_create()` the new ones in `islice` chunks with an explicit `batch_size`. 5. Change attributes on the existing instances and `bulk_update()` them with the list of fields you touched. 6. Run any side effects that `save()` receivers would have done explicitly, because none of them fired.

In Django, how do you do an upsert of attendees by email with bulk_create()?
Pass `update_conflicts=True`, `unique_fields=["email"]` and `update_fields=["name"]`. Rows whose email already exists get `name` overwritten instead of failing. `unique_fields` is required on PostgreSQL and SQLite, and those fields must be backed by a unique constraint. Oracle does not support it.
In Django, why might bulk_update() return a smaller number than the length of the list you passed?
It returns the sum of rows matched by each batch's `UPDATE`. Duplicates of the same object in one batch produce only one update, and rows deleted by another process since you loaded them no longer match `pk__in`, so they are not counted.

saying these in an interview costs you the question

bulk_create() calls save() on each object, just in one transaction
bulk_create() always sets primary keys on the returned objects
ignore_conflicts=True still gives each new object its primary key
bulk_update() can change the primary key of each object
Passing a generator to bulk_create() keeps memory flat

When importing 200,000 attendee rows with Django's bulk_create() and later correcting them with bulk_update(), which caveats and batch_size choices matter?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What the two methods do

bulk_create() caveats

bulk_update() caveats

Choosing batch_size

Replaying what the bulk path skipped

A realistic import

▾common follow-ups2 questions

▾red flags5 flags

What the two methods do

bulk_create() caveats

bulk_update() caveats

Choosing batch_size

Replaying what the bulk path skipped

A realistic import

answer

go deeper

`bulk_create()` caveats

`bulk_update()` caveats

Choosing `batch_size`

`bulk_create()` caveats

`bulk_update()` caveats

Choosing `batch_size`