On PostgreSQL, one Django migration adds first_name and last_name and then backfills 20 million customers with RunPython; why is that risky, and how do Migration.atomic and RunPython's atomic argument help?
answer
- one transaction per migration
- schema lock held during the loop
- pending trigger events
- atomic = False plus batches
basics
~20 sOn PostgreSQL the whole migration is one transaction, so the table stays locked by the ALTER while the backfill runs and everything rolls back together. Split schema and data; for huge tables set Migration.atomic = False and commit batches yourself.
solid answer
~50 sOn backends with transactional DDL (PostgreSQL, SQLite) Django wraps each migration in one transaction when `Migration.atomic` is `True`, the default. The `AddField` takes a strong table lock that is held until commit, so a 20-million-row `RunPython` loop inside the same migration blocks traffic for its whole duration, and Django's docs warn it can also fail with `cannot ALTER TABLE ... because it has pending trigger events`. I split it: schema in one migration, data in the next. For the data migration on a huge table I set `atomic = False` on the `Migration` and commit batches with `transaction.atomic()`, making the function skip rows already split so a rerun resumes. `RunPython(atomic=True)` wraps just that operation in a transaction inside a non-atomic migration; on MySQL, which lacks transactional DDL, Django already wraps each `RunPython` in its own transaction unless you pass `atomic=False`.
code
python · 32 linesfrom django.db import migrations, transaction
BATCH = 5000
def split_full_name(apps, schema_editor):
Customer = apps.get_model("crm", "Customer")
qs = Customer.objects.using(schema_editor.connection.alias)
while True:
with transaction.atomic(using=schema_editor.connection.alias):
batch = list(
qs.filter(first_name="", last_name="")
.exclude(full_name="")
.order_by("pk")[:BATCH]
)
if not batch:
return
for c in batch:
c.first_name, _, c.last_name = c.full_name.strip().partition(" ")
if not c.first_name:
c.first_name = "?"
qs.bulk_update(batch, ["first_name", "last_name"])
class Migration(migrations.Migration):
atomic = False
dependencies = [("crm", "0007_customer_first_name_customer_last_name")]
operations = [
migrations.RunPython(split_full_name, migrations.RunPython.noop),
]go deeper
Remember that a Django migration on PostgreSQL runs as one transaction by default and that schema and data changes belong in separate migrations.
Explain Migration.atomic, RunPython's atomic argument and can_rollback_ddl, and how they combine differently on PostgreSQL and MySQL.
Design the backfill: separate non-atomic migration, bounded batches in transaction.atomic(), a resumable filter, and awareness of locks held by the schema step.
Decide which backfills run inside deploy migrations and which move to throttled out-of-band jobs, and set the team's rule for it.
## How Django decides transactions for a migration Two attributes and one backend feature decide whether an operation runs in a transaction: - **`Migration.atomic`**, a class attribute, `True` by default. - **The operation's `atomic`**: `RunPython` takes `atomic=None` by default; most other operations, `RunSQL` included, have `atomic = False` on the base `Operation` class. - **`connection.features.can_rollback_ddl`**: `True` for PostgreSQL and SQLite, `False` for backends like MySQL and Oracle where DDL cannot be rolled back. The schema editor opens one transaction around the whole migration only when `can_rollback_ddl and Migration.atomic`. Otherwise, for each operation Django computes `operation.atomic or (Migration.atomic and operation.atomic is not False)` and, if true, wraps just that operation in `transaction.atomic()`. | Backend | `Migration.atomic` | `RunPython(atomic=...)` | Result for the RunPython | |---|---|---|---| | PostgreSQL / SQLite | `True` (default) | any | runs inside the single migration transaction | | PostgreSQL / SQLite | `False` | `None` or `False` | no transaction; your code manages it | | PostgreSQL / SQLite | `False` | `True` | wrapped in its own transaction | | MySQL / Oracle | `True` (default) | `None` | wrapped in its own transaction | | MySQL / Oracle | any | `False` | not wrapped | Note the first row: on PostgreSQL, `RunPython(atomic=False)` inside a normal migration does **not** escape the migration's transaction. ## Why the combined migration hurts With `AddField` x2 and a large `RunPython` in one default migration on PostgreSQL: 1. The `ALTER TABLE` statements take a strong lock on the customer table, held until the transaction ends. 2. The backfill then runs for minutes inside that same transaction, so reads and writes queue behind the lock for the whole time. 3. Every updated row stays uncommitted until the end; a failure at row 19 million rolls back everything, including the columns. 4. Django's documentation warns that mixing schema changes and `RunPython` in one PostgreSQL migration can fail with `OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events`. The detailed lock levels and trigger mechanics belong to PostgreSQL; what matters on the Django side is that the migration boundary is the transaction boundary. ## The safer structure 1. **Migration A (schema)**: add `first_name` and `last_name` as nullable or with a default, kept atomic. It commits in moments. 2. **Migration B (data)**: `atomic = False` on the `Migration` class; the `RunPython` function loops over primary-key ranges or `iterator()` chunks and wraps each batch in `transaction.atomic()`, so each commit is small and locks are row-level and brief. 3. Make B's function **resumable**: filter to rows whose new columns are still empty, so re-running after a crash continues instead of starting over. 4. Later migrations tighten constraints or drop `full_name` once the application no longer reads it. ## Trade-offs of non-atomic migrations - A failure part-way leaves some rows done and the migration **not recorded as applied**; the next `migrate` runs B again from the top, which is why resumability matters. - `BEGIN`/`COMMIT` written by hand inside `RunSQL` is only allowed in non-atomic migrations on PostgreSQL and SQLite, otherwise it breaks Django's transaction state. - On MySQL, if the function uses `schema_editor` to run DDL, Django's default per-operation wrapping can crash; the documented fix is `RunPython(..., atomic=False)`. - Batches still generate write load; very large backfills are often throttled or moved out of the deploy path, a policy question owned elsewhere. ## What interviewers listen for That the candidate knows the migration, not the operation, is the transaction on PostgreSQL, that `atomic=False` on a `RunPython` does not change that, and that a big backfill belongs in its own non-atomic migration with explicit, resumable batches.
- Why does the batch loop need every processed row to stop matching the filter?The loop re-queries rows that still look unprocessed. If a row's full_name splits into empty strings, say whitespace only, it would match again forever. Either exclude such rows in the filter or write a marker value so each batch shrinks the remaining set.
- If a non-atomic data migration crashes half-way, what state is Django in?The committed batches stay in the database, but the migration is not recorded in `django_migrations`, so the next `migrate` runs it again from the start. That is safe only if the function skips rows it already processed.
saying these in an interview costs you the question
- RunPython(atomic=False) escapes the migration transaction on PostgreSQL
- Each operation in a Django migration commits separately on PostgreSQL
- Migration.atomic = False makes Django batch the backfill automatically
- A crashed non-atomic data migration rolls back cleanly
- On MySQL a failed migration also rolls back its ALTER TABLE