Django AddField default value locking during zero-downtime deploy

Question

Goal: add a non-nullable field with a database default to an existing table in a small Django application while keeping reads and writes available during a staggered deploy across PostgreSQL and MySQL.

Django applies migrations sequentially and wraps schema changes in a transaction on backends that support transactional DDL, such as PostgreSQL and SQLite. Adding a non-nullable column with a default can require a full table rewrite and an exclusive lock on older PostgreSQL versions or MySQL, which blocks concurrent traffic. A RunPython backfill inside the same migration may extend that lock duration. Django does not automatically detect or prevent these unsafe operations.

Assumptions: PostgreSQL 11 or later avoids a rewrite for a constant default, while earlier PostgreSQL and MySQL versions may lock the table. The exact lock behavior depends on the production backend version and must be confirmed before deploy.

Questions: Does Django hold the schema lock for the entire RunPython backfill on PostgreSQL 15? Can MySQL 8.0 add a non-nullable column with a constant default without rewriting the table? Should the field be introduced as nullable, backfilled separately, and then constrained to preserve zero-downtime deploys?

Muntasir · Answer

Answer
On PostgreSQL 15, adding a column with a constant default is a metadata‑only change; Django’s migration transaction holds an Access Exclusive lock for the whole migration, so any RunPython in the same file keeps that lock until the transaction commits.
MySQL 8.0 can add a column with the INSTANT algorithm, but adding a non‑null column with a default may still trigger a table rebuild unless the column is appended at the end and you are on a recent minor release (≥ 8.0.13). Without that guarantee, the operation can block writes.
Likely explanation
Because Django wraps the entire migration file in a single transaction on backends that support transactional DDL, the schema lock acquired for the AddField operation is not released until after the RunPython backfill finishes. This extends the downtime window even though the column addition itself is fast on PostgreSQL 15. On MySQL, the INSTANT add‑column optimization has strict position and version requirements; if those are not met, MySQL falls back to a copy‑and‑swap rebuild that holds an exclusive metadata lock for the duration of the operation.
Recommended zero‑downtime steps

Add the field as null=True with a default value (no NOT NULL constraint). This creates the column instantly on PostgreSQL 15 and, if conditions allow, instantly on MySQL 8.0.
Backfill existing rows in batches using a separate RunPython or management command that updates the column in small chunks, committing frequently to avoid long transactions.
Once all rows have the desired value, apply a second migration that alters the field to null=False (and removes the default if you want it to be enforced solely by the constraint). This step is a fast metadata change on PostgreSQL 15 and, when the column is at the end, an INSTANT change on MySQL 8.0.

If you can confirm that the new column will be added at the end of the table and you are running MySQL 8.0.13 or later, step 2 can be performed with a single RunPython inside the same migration after the nullable AddField, because the subsequent AlterField to NOT NULL will be instant. Otherwise, keep the backfill in a separate, non‑transactional step.
Missing diagnostic detail: Please verify the exact MySQL minor version and whether the column will be positioned at the end of the table; this determines whether the INSTANT algorithm applies and influences whether a single‑migration approach is safe.

Django AddField default value locking during zero-downtime deploy

Django AddField default value locking during zero-downtime deploy

1 answer

Answer

Likely explanation

Recommended zero‑downtime steps

Your perspective belongs here.

0 question comments