skip to content

In Django, how would you add a NOT NULL column to a 50-million-row events table without downtime, and why does db_default matter?

level: seniorimportance: must knowfreq 48%

answer

  1. who inserts during the deploy window
  2. what AddField does with default=
  3. the column default gets dropped
  4. a default the database keeps

basics

~20 s

Declare the field with db_default so AddField leaves a DEFAULT on the column. With only a Python default, Django drops the column default after filling existing rows, and the old release's INSERTs, which omit the column, then fail.

solid answer

~40 s

Migrations usually run while the previous release is still serving, and that code does not know the new column, so its INSERTs leave it out. With only `default="web"`, `AddField` adds the column as `DEFAULT 'web' NOT NULL` to fill existing rows and then runs `ALTER COLUMN ... DROP DEFAULT`, because Django treats `default` as a Python-side value; from then on every old-release insert violates NOT NULL until the rollout finishes. Since Django 5.0, `db_default="web"` keeps the `DEFAULT` on the column, so old inserts succeed. On current PostgreSQL a constant default is added without rewriting all 50 million rows, so the `ALTER` itself is short. A callable `default` like `uuid.uuid4` is evaluated once for all existing rows; when each row needs its own value, add the column nullable, backfill in batches, then tighten it.

code

python · 8 lines
python
from django.db import models
from django.db.models.functions import Now


class Event(models.Model):
    name = models.CharField(max_length=100)
    created_at = models.DateTimeField(db_default=Now())
    source = models.CharField(max_length=32, db_default="web")

go deeper

for a junior

Remember that a Django default is applied in Python, while db_default is stored on the column by the database.

for a middle

Explain the SQL AddField emits for default versus db_default and why the DROP DEFAULT breaks inserts from the previous release.

for a senior

Plan the column add for a busy table: db_default for constants, nullable plus backfill plus tightening for computed values, verified with sqlmigrate.

for a principal

Weigh one-step db_default adds against multi-release nullable adds, and make the choice a reviewed rule rather than a per-author guess.

## The deploy window In a rolling or blue-green deploy, `migrate` normally runs **before** every old process has been replaced. For a while, the previous release, which has never heard of the new column, keeps reading and writing the table. Django's ORM builds an `INSERT` from the model's concrete fields, so the old release's inserts simply omit the new column. Whether those inserts succeed depends entirely on what the database does with a missing value for a `NOT NULL` column: it needs a **database-level default**. ## What AddField does with default versus db_default Suppose `analytics.Event` gains `source = models.CharField(max_length=32, ...)`. | Field declaration | SQL `AddField` runs (PostgreSQL) | Old release's INSERT during the deploy | |---|---|---| | `default="web"` | `ADD COLUMN ... DEFAULT 'web' NOT NULL`, then `ALTER COLUMN ... DROP DEFAULT` | fails: NOT NULL violation | | `db_default="web"` (Django 5.0+) | `ADD COLUMN ... DEFAULT 'web' NOT NULL`, default kept | succeeds: the database fills `'web'` | | `null=True` | `ADD COLUMN ...` nullable | succeeds with NULL | The second statement in the first row is the trap: Django uses the default only to populate existing rows, then removes it, because `default` is meant to be applied in Python when instances are created. `db_default` is the value the **database** owns; Django's documentation says it stays set at the database level and is used for inserts outside the ORM and when adding the field in a migration. `db_default` accepts literals and database functions such as `Now()`, not Python callables. ## Why the table size matters - Adding a column always takes a short exclusive lock on the table. On current PostgreSQL, a column with a **constant** default is recorded without rewriting existing rows, so the lock is brief even on 50 million rows. The engine rules for which defaults avoid a rewrite belong to the database, not Django. - A **callable** Python default such as `uuid.uuid4` is evaluated **once** during the migration, so every existing row gets the same value, which also breaks any `unique=True`. - Changing `null=True` to `null=False` later with `AlterField` makes the database check every row, which on a large table holds a lock for the scan. ## The safe recipe 1. If one constant works for old and new rows, declare the field `NOT NULL` with `db_default`. One migration, safe with the old release running. 2. If each row needs a computed value, add the field with `null=True` (or with a placeholder `db_default`). 3. Deploy code that writes the field for every new row. 4. Backfill existing rows in a separate, non-atomic data migration with batches. 5. Tighten: `AlterField` to `null=False`. On PostgreSQL, `django.contrib.postgres.operations` offers `AddConstraintNotValid` and `ValidateConstraint`, in separate migrations, to add and then validate a `CheckConstraint(condition=Q(source__isnull=False), ...)` without one long validating lock. ## Check before you ship - Run `python manage.py sqlmigrate analytics 0031` and read the SQL: a `DROP DEFAULT` after an `ADD COLUMN ... NOT NULL` is the signal the old release will break. - Keep schema migrations small and separate from backfills, so the locking statement is short and reviewable. - Keep `db_default` after the deploy if it is a sensible business default; removing it is itself a migration. The interview signal is knowing that the danger is not the `ALTER` alone but the old code running against the new schema, and that `db_default` is the Django feature built for exactly that window.

  • What does sqlmigrate show that makes a NOT NULL AddField unsafe for the old release?
    An `ADD COLUMN ... NOT NULL` followed by `ALTER COLUMN ... DROP DEFAULT`. The second statement leaves the column without a database default, so any insert from code that does not know the field fails until that code is gone.
  • If both default and db_default are set on a Django field, which one applies?
    `default` takes precedence when instances are created in Python, while `db_default` stays on the column and is used by inserts that omit the field, including the old release's, and to fill existing rows when the field is added.

saying these in an interview costs you the question

  • A Python default keeps a DEFAULT on the column after AddField
  • The old release will include the new column in its inserts
  • A callable default gives each existing row its own value
  • db_default accepts any Python callable
  • Changing null=True to null=False later is free on a large table