Learn interview-prep-45

Migrations and Schema Changes

Browsing without an account — progress is saved in this browser only.Sign in to save it

Migrations and Schema Changes

Reading45 min

Every backend engineer eventually ships a migration that locks a table in production. Interviewers ask about migrations to find out whether you've already learned that lesson, or you're about to learn it the hard way on their system. This lesson covers how Django migrations actually work, which schema changes are dangerous on a big table, and how to change a live table without an outage.

How Django migrations actually work

makemigrations compares your models against the last known state, tracked through migration files rather than the live database, and writes a Python file describing the difference. migrate applies pending migration files in dependency order and records which ones ran, in a django_migrations table.

Python

# migrations/0007_add_phone_number.py
from django.db import migrations, models

class Migration(migrations.Migration):
    dependencies = [("accounts", "0006_alter_user_email")]
    operations = [
        migrations.AddField(
            model_name="user",
            name="phone_number",
            field=models.CharField(max_length=20, blank=True, default=""),
        ),
    ]

Four habits interviewers expect you to name:

One logical change per migration. Don't bundle an unrelated index add with a column rename. A rollback should undo one thing, not three.
Never edit a migration that has already run anywhere (staging, a teammate's machine, production). Treat migration files as an append-only log. Editing an already-applied one desyncs django_migrations's record from what actually happened. Write a new migration to fix a mistake instead.
Keep migrations reversible when you can. Implement reverse_code for a RunPython operation, so migrate app 0006 actually works instead of throwing IrreversibleError.
Read the generated SQL before running it in production, with python manage.py sqlmigrate accounts 0007. This one habit turns "I think this is safe" into "I checked exactly what lock this takes."

bash

python manage.py makemigrations accounts
python manage.py sqlmigrate accounts 0007
python manage.py migrate accounts

Remember: run sqlmigrate before every production deploy. It's the difference between guessing a migration is safe and knowing exactly what lock it takes.

Why some migrations lock the whole table

Changing a table's structure needs a lock. The real question to ask is not "will this work," it's "which lock does it take, and for how long."

Operation	Lock in Postgres	Danger on a large table
`ADD COLUMN`, nullable, no default	Metadata-only, near-instant (Postgres 11+)	Safe
`ADD COLUMN` with a non-null default	Metadata-only on Postgres 11+	Safe on 11+; check your version
`ADD COLUMN` with a volatile default (like `uuid.uuid4`)	Full table rewrite	Dangerous: locks the whole table for the rewrite
`ALTER COLUMN TYPE`	Full rewrite, exclusive lock	Dangerous on a large table
`CREATE INDEX` (plain)	Blocks writes for the duration	Dangerous: use `CREATE INDEX CONCURRENTLY` instead
`ADD CONSTRAINT NOT NULL`	Full scan to validate, holds a lock	Dangerous: split into `NOT VALID` plus a separate `VALIDATE CONSTRAINT`
`ADD FOREIGN KEY`	Scans both tables to validate	Same fix: `NOT VALID`, then validate separately

The dangerous rows all take an ACCESS EXCLUSIVE lock, which blocks both writes and reads from every other connection while it's held. On a table with 50 million rows, a full rewrite can take minutes. During that window, your app effectively has an outage on anything touching that table.

Remember: the danger question is never "will this migration work," it's "what lock does it take, and for how long." ACCESS EXCLUSIVE blocks reads too, not just writes.

Adding a required column to a huge table, safely

Take the worst case: adding a NOT NULL column with a default to a huge, high-traffic table, without an outage. The trick is splitting one risky operation into several small, non-blocking ones, deployed one at a time.

Step 1: add the column, nullable, no default. Fast, metadata-only.

Python

migrations.AddField(
    model_name="order", name="status",
    field=models.CharField(max_length=20, null=True, blank=True),
)

Step 2: backfill in small batches, off the request path, never as one giant transaction.

Python

def backfill_status(apps, schema_editor):
    Order = apps.get_model("orders", "Order")
    batch_size = 5000
    last_id = 0
    while True:
        batch = list(
            Order.objects.filter(id__gt=last_id, status__isnull=True)
            .order_by("id").values_list("id", flat=True)[:batch_size]
        )
        if not batch:
            break
        Order.objects.filter(id__in=batch).update(status="pending")
        last_id = batch[-1]

Batching matters for two reasons. One giant UPDATE on 50 million rows holds row locks and generates a mountain of write-ahead log in a single transaction. And it's all-or-nothing: if it dies at row 40 million, you redo all 40 million. Batches of a few thousand commit independently and pick up from last_id if interrupted.

Step 3: add the NOT NULL constraint in two phases, so the full-table scan does not block anything.

sql

-- Instant: doesn't scan existing rows, only enforces the rule on new writes
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID;

-- Scans the table but takes a lighter lock that never blocks reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

Step 4: once confident, backfill complete and the constraint validated, update the Django model to match.

Python

migrations.AlterField(
    model_name="order", name="status",
    field=models.CharField(max_length=20, default="pending"),
)

Four small migrations, each either instant or non-blocking, instead of one migration that locks the whole table. Say this in an interview and name the sequence: "add nullable, backfill in batches so I never hold a long transaction or lock the whole table, then tighten to NOT NULL in a follow-up deploy once the backfill is confirmed complete." That sequencing, not any single line of code, is the actual thing being tested.

Remember: a required column on a huge table is four small migrations: add nullable, backfill in batches, add the constraint NOT VALID, then validate it. Never one big blocking migration.

Data migrations and the historical model trap

A data migration transforms existing rows rather than the schema. Always fetch models through apps.get_model, never by importing the real model class directly: the historical model reflects the schema at that exact point in migration history, which matters once the real model gains fields or methods the migration was never written to expect.

Python

def split_full_name(apps, schema_editor):
    User = apps.get_model("accounts", "User")
    for user in User.objects.filter(first_name="", last_name="").iterator():
        parts = user.full_name.split(" ", 1)
        user.first_name = parts[0]
        user.last_name = parts[1] if len(parts) > 1 else ""
        user.save(update_fields=["first_name", "last_name"])

iterator() avoids loading the whole queryset into memory at once, which matters once "existing data" means millions of rows. For very large tables, batch this the same way as the backfill above, rather than one unbounded iterator() pass inside a single migration transaction.

Remember: always fetch models through apps.get_model inside a data migration. It gives you the schema as it existed at that point in history, not whatever the real model currently looks like.

Zero-downtime migrations: expand, migrate, contract

During a rolling deploy, old and new application code run against the same database at the same time, for as long as it takes instances to cycle through. A migration is zero-downtime only if both versions of the code work correctly against the schema at every point in that window.

That rules out any single step that both adds and immediately requires a column. The standard technique is expand/contract:

Expand: add the new column or table, purely additive. Old code ignores it; nothing breaks.
Migrate and dual-write: deploy code that writes to both the old and new columns (or reads the new one with a fallback to the old), and backfill existing rows.
Contract: once every instance runs the new code and the backfill is done, remove the old column and the compatibility code.

A column rename (username to handle) can never be a single RENAME COLUMN in a zero-downtime deploy, because old code would break the instant the old column disappeared:

Python

# Expand: add handle, keep username
migrations.AddField(model_name="user", name="handle", field=models.CharField(max_length=50, null=True))

# App code deployed after this: write to both
def save_username(user, value):
    user.username = value
    user.handle = value
    user.save()

# Backfill handle from username for existing rows (batched, as above)

# Contract, only once every instance has stopped reading username:
migrations.RemoveField(model_name="user", name="username")

The interview answer, said plainly: "Zero-downtime means both old and new code work against the schema during a rolling deploy. Expand adds new structure additively, dual-write and backfill keep both paths working while they coexist, and contract removes the old structure only once every instance is on the new code." Naming "expand/contract" is what signals you've actually done this, not just read about it once.

Remember: expand adds, contract removes. Never combine them into one step, or old code breaks the instant new code deploys.