Run Schema Changes on Large Tables in Django Safely

Problem statement

A migration that runs in milliseconds on a development database can take a production site down when the table has tens of millions of rows. The failure is rarely the migration being slow by itself; it is locking:

• CREATE INDEX without CONCURRENTLY blocks writes to the table for the whole build
• adding a foreign key or check constraint scans the table while holding locks
• adding a NOT NULL column with a volatile default can rewrite the table
• even a quick ALTER TABLE waits for an exclusive lock, and while it waits, every new query on that table queues behind it

The last point surprises many teams: a harmless metadata change can freeze the site because a long-running query holds a conflicting lock, the migration waits, and all application queries wait behind the migration.

This guide covers PostgreSQL-specific techniques for making common schema changes from Django migrations without blocking production traffic.

Quick answer

1. Inspect the SQL with python manage.py sqlmigrate app 00xx before deploying.
2. Set a short lock_timeout in risky migrations so they fail fast instead of blocking traffic.
3. Create indexes with AddIndexConcurrently in a non-atomic migration.
4. Add constraints as NOT VALID first, then validate in a separate step.
5. Add columns as nullable (or with a constant default) and backfill in batches outside the schema migration.
6. Make columns NOT NULL via a validated check constraint before setting NOT NULL.
7. Split changes across releases (expand, migrate data, contract) so old and new code both work.
8. Rehearse on a production-sized copy and monitor locks while migrating.

Step-by-step solution

1. Read the SQL Django will run

python manage.py sqlmigrate orders 0042

Look for CREATE INDEX without CONCURRENTLY, ALTER TABLE ... ADD CONSTRAINT (especially foreign keys), SET NOT NULL, type changes (ALTER COLUMN ... TYPE), and statements that update existing rows.

Also check table sizes to know which operations are risky:

SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

2. Fail fast with lock_timeout

Every Django migration on PostgreSQL runs inside a transaction by default. Set a lock timeout at the start so a blocked ALTER TABLE gives up instead of queueing traffic behind it:

from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [("orders", "0041_previous")]

    operations = [
        migrations.RunSQL("SET LOCAL lock_timeout = '3s';", reverse_sql=migrations.RunSQL.noop),
        migrations.AddField(
            model_name="order",
            name="channel",
            field=models.CharField(max_length=20, null=True, blank=True),
        ),
    ]

SET LOCAL applies until the end of the migration's transaction. If the lock is not acquired within three seconds, the migration fails with a lock timeout error, the transaction rolls back, and application traffic continues. Retry the deploy when the conflicting long-running query or transaction is gone.

Find what is blocking when this happens:

SELECT pid, now() - xact_start AS xact_age, state, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Long-running analytics queries and idle-in-transaction sessions are common culprits.

3. Add columns safely

• Nullable column: metadata-only change, fast.
• Column with a constant default: on PostgreSQL 11 and later, adding a column with a non-volatile default is also metadata-only and does not rewrite the table.
• Column with a volatile default (for example a function like gen_random_uuid() evaluated per row): rewrites the table and holds an exclusive lock throughout.

When AddField has a default, Django's schema editor adds the column with that value as a database default so existing rows are filled, then drops the database default again; Django applies default in Python for new rows afterwards. With a constant value on PostgreSQL 11 or later, this is fast. A callable default (such as uuid.uuid4) is evaluated once when the migration runs, so every existing row receives the same value, which is rarely what you want. For values that must differ per row, add the column as nullable and backfill in batches. Django 5.0's db_default keeps a database-level default permanently when you want the database to own it.

4. Backfill in batches

Do not update millions of rows in one migration transaction. A single huge UPDATE holds row locks for its duration, generates large amounts of WAL, and can bloat the table.

Use a management command or a non-atomic data migration that processes batches:

# orders/management/commands/backfill_order_channel.py
import time

from django.core.management.base import BaseCommand
from django.db import transaction

from orders.models import Order


class Command(BaseCommand):
    help = "Backfill Order.channel in batches."

    def add_arguments(self, parser):
        parser.add_argument("--batch-size", type=int, default=5000)
        parser.add_argument("--sleep", type=float, default=0.2)

    def handle(self, *args, batch_size, sleep, **options):
        last_pk = 0
        total = 0
        while True:
            ids = list(
                Order.objects.filter(pk__gt=last_pk, channel__isnull=True)
                .order_by("pk")
                .values_list("pk", flat=True)[:batch_size]
            )
            if not ids:
                break
            with transaction.atomic():
                updated = Order.objects.filter(pk__in=ids, channel__isnull=True).update(channel="web")
            total += updated
            last_pk = ids[-1]
            self.stdout.write(f"updated {total} rows (last pk {last_pk})")
            time.sleep(sleep)

Paging by primary key keeps each batch query efficient, and the sleep gives replicas and the primary time to keep up. The command is restartable because it only touches rows still missing a value.

5. Create indexes concurrently

CREATE INDEX CONCURRENTLY builds an index without blocking writes, but it cannot run inside a transaction. Django provides an operation for PostgreSQL:

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models


class Migration(migrations.Migration):
    atomic = False

    dependencies = [("orders", "0042_order_channel")]

    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["channel", "created_at"], name="order_channel_created_idx"),
        ),
    ]

• atomic = False is required.
• Keep concurrent index operations in their own migration, separate from other schema changes.
• If a concurrent build fails, it can leave an INVALID index behind. Check and drop it before retrying:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

RemoveIndexConcurrently drops indexes without blocking.

For indexes created implicitly (for example by db_index=True or a new ForeignKey), set db_index=False on the field and add the index concurrently in a separate migration.

6. Add constraints without long locks

Adding a check or foreign key constraint normally validates every existing row while holding locks. PostgreSQL can add the constraint as NOT VALID (enforced for new rows only) and validate it later with a weaker lock:

from django.contrib.postgres.operations import AddConstraintNotValid, ValidateConstraint
from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [("orders", "0043_order_channel_idx")]

    operations = [
        AddConstraintNotValid(
            model_name="order",
            constraint=models.CheckConstraint(
                condition=models.Q(total__gte=0),
                name="order_total_non_negative",
            ),
        ),
    ]

Then, in a later migration:

class Migration(migrations.Migration):
    dependencies = [("orders", "0044_order_total_check_not_valid")]

    operations = [
        ValidateConstraint(model_name="order", name="order_total_non_negative"),
    ]

VALIDATE CONSTRAINT scans the table but only takes a SHARE UPDATE EXCLUSIVE lock, which allows normal reads and writes. CheckConstraint uses condition= in Django 5.1 and later; older versions use check=.

7. Make a column NOT NULL on a large table

ALTER COLUMN ... SET NOT NULL scans the whole table under an exclusive lock, unless PostgreSQL (version 12 and later) can prove no NULLs exist from a valid check constraint:

1. Backfill all rows (step 4).
2. Add CHECK (channel IS NOT NULL) NOT VALID with AddConstraintNotValid.
3. ValidateConstraint in a separate migration.
4. Alter the field to null=False; PostgreSQL uses the validated constraint to skip the scan.
5. Optionally remove the now-redundant check constraint.

8. Renames and type changes

Renaming a column or table in one release breaks the old code still running during deploys. Use expand and contract:

1. Add the new column; write to both old and new from the application.
2. Backfill the new column.
3. Switch reads to the new column.
4. Stop writing the old column; remove it in a later release.

When you need Django's model state to change without SQL (for example after a manual database change), SeparateDatabaseAndState lets you apply state and database operations separately. Use it carefully and document why.

Type changes that rewrite the table (for example integer to bigint on a large primary key) need dedicated migration plans: a new column, dual writes, backfill, and a swap during a maintenance window or with more advanced tooling.

9. Rehearse and monitor

Restore a recent production-sized copy into staging and run the migrations with timing:

time python manage.py migrate orders 0045

During the production run, watch for lock waits:

SELECT blocked.pid AS blocked_pid, left(blocked.query, 60) AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Verification check:

• sqlmigrate output contains no blocking index or constraint creation on large tables
• risky migrations set lock_timeout
• concurrent index builds finish with valid indexes
• application error rates and latency stay normal while migrations run
• no invalid indexes or unvalidated constraints are left behind unintentionally

Explanation

PostgreSQL protects schema changes with table locks, and some operations need exclusive locks or full table scans. On small tables that is invisible; on large ones it blocks traffic. The techniques here split each change into steps that either avoid strong locks (CONCURRENTLY, NOT VALID), hold them only briefly (metadata-only ALTER TABLE with a lock timeout), or move bulk work out of transactions (batched backfills). Combined with expand-and-contract releases, schema changes become routine deploys instead of maintenance windows.

Edge cases / notes

MySQL

The lock behavior and available operations differ substantially on MySQL; online DDL support depends on the operation and storage engine. The concepts (inspect SQL, avoid long locks, batch backfills) still apply.

Replicas

Large backfills and index builds generate WAL that replicas must replay. Monitor replication lag and slow batches down if lag grows.

Third-party tools

Packages exist that lint Django migrations for unsafe operations. Adding one to CI catches risky changes before review.

Common mistakes

• Using AddIndex instead of AddIndexConcurrently on large tables.
• Backfilling millions of rows inside the same migration that adds the column.
• Running migrations without lock_timeout while long analytics queries are active.
• Renaming columns in one release while old code is still serving requests.

Next steps

• Plan releases with Django Migration Strategy for Safe Deployments.
• Recover from failures with Django Migration Failed During Deploy: Recovery Playbook.
• Keep old and new code compatible with Blue-Green Deployments for Django.
• Repair migration state with Fix InconsistentMigrationHistory in Django.

FAQ

How do I create an index on a large table in Django without downtime?

Use AddIndexConcurrently from django.contrib.postgres.operations in a migration with atomic = False. It builds the index without blocking writes.

Why did a simple Django migration lock my whole site?

The ALTER TABLE waited for a lock held by a long-running query or transaction, and all new queries on that table queued behind it. Set lock_timeout so migrations fail fast instead.

How do I add a NOT NULL column to a large table safely?

Add it as nullable, backfill in batches, add a NOT VALID check constraint, validate it, then set NOT NULL, which PostgreSQL can apply without a full scan.

Should data backfills run inside Django migrations?

For large tables, run them as batched management commands or non-atomic data migrations, separate from schema changes, so no single transaction locks or rewrites millions of rows.