Django Safe Migration

Django Migration — Zero Downtime

Project Configuration

Read the project's CLAUDE.md or AGENTS.md for the values below. If they are not set, use the defaults shown.

To configure this skill for your project, add a section like this to your CLAUDE.md or AGENTS.md:

## django-safe-migration
- Django version: 5.1
- Deploy strategy: rolling deploy
- Runtime guard: none
- Migration command: docker compose run web python manage.py sqlmigrate
- Docs URL: https://github.com/your-org/repo/wiki/migrations

---

When to Use

• "Review this migration"
• "Is this migration safe?"
• "Write a migration for..."
• "Rewrite this migration to be safe"
• "How do I add a NOT NULL column / drop a column / add an index / rename a column / add a FK..."

---

Why Zero-Downtime Migrations Matter

In a rolling deploy, the new database schema is applied first, then application instances are restarted one by one. At any moment during the deploy, old code and new code run simultaneously against the same database.

Every migration must be safe to run while the previous version of the app is still serving traffic. A migration that takes an ACCESS EXCLUSIVE lock on a large table blocks all reads and writes — downtime even for a few seconds on a busy table.

The problem is not just the lock itself but the wait queue: a fast ALTER TABLE that takes 50ms will queue behind any long-running transaction, and all subsequent queries queue behind the migration. On a busy table this cascades into connection pool exhaustion.

---

How PostgreSQL Locking Works

For the full conflict matrix (table-level locks × business logic operations × row-level locks) and the FIFO wait-queue explanation, load references/postgres-locks.md. Load it when:

• explaining why a specific operation is unsafe
• a developer asks what a lock type blocks or conflicts with
• reasoning about whether two concurrent operations interact

---

lock_timeout

Any operation that requires ACCESS EXCLUSIVE should be preceded by SET LOCAL lock_timeout. This causes the migration to fail fast (with a clear error) instead of waiting indefinitely for the lock — preventing connection pool exhaustion from queue cascading.

For a normal transactional migration:

migrations.RunSQL("SET LOCAL lock_timeout = '2s'"),
migrations.AlterField(...),  # or any ACCESS EXCLUSIVE operation

SET LOCAL scopes the timeout to the current transaction, so it does not affect other sessions or persist after the migration completes.

Default: 2s — adjust up if the table is known to have long-running transactions that legitimately need more time, or down for stricter environments.

For atomic = False migrations, combine SET LOCAL and the DDL in the same RunSQL operation; see Structural Rules below.

---

Key Patterns

NOT VALID + VALIDATE (for FK and CHECK constraints)

A two-step PostgreSQL technique to add a constraint without a long lock:

1. `ADD CONSTRAINT … NOT VALID` — creates the constraint and enforces it on new writes immediately, but skips scanning existing rows. Takes a brief lock with no table scan — SHARE ROW EXCLUSIVE on both tables for FK constraints, ACCESS EXCLUSIVE for CHECK constraints. 2. `VALIDATE CONSTRAINT` — scans existing rows to confirm they satisfy the constraint. Takes SHARE UPDATE EXCLUSIVE (plus ROW SHARE on the referenced table for FK constraints), which does not block reads or writes.

The dangerous part is the full-table scan, not the constraint creation itself. Splitting it keeps the write-blocking lock window to milliseconds, with the long scan moved to a non-blocking step.

PostgreSQL docs: `NOT VALID` · `VALIDATE CONSTRAINT`

---

Runtime Guards

Some projects configure a custom database backend or linter that raises errors for unsafe operations at migration time (e.g. zero_downtime_migrations, django-pg-zero-downtime-migrations, django-migration-linter).

When reviewing a migration:

• Operations the project's runtime guard blocks → classify as Error (migration will not run)
• Operations the guard does not cover → classify as Warning (migration runs but may cause downtime)

If no runtime guard is configured, treat all unsafe operations as Errors that require a safe rewrite before deploying to production.

Check the Project Configuration block above (or AGENTS.md) for what this project's guard covers.

---

Mode 1: Review

Goal

Identify every operation that is unsafe or risky for a rolling deploy on PostgreSQL.

Steps

1. Read the migration file in full. 2. Run <migration_command> <app_label> <migration_name> to get the actual SQL Django will execute. Always do this — the generated SQL is the ground truth. Use the Migration command value from CLAUDE.md/AGENTS.md; default is python manage.py sqlmigrate. If the key is not set and a Makefile or docker-compose.yml exists in the project root, ask the user: "How do you run sqlmigrate in this project?" and suggest they save the answer to CLAUDE.md. The ORM operation class alone is not sufficient: for example, AlterField on a FK field that adds null=True also drops and re-adds the FK constraint, emitting DROP CONSTRAINT (taking ACCESS EXCLUSIVE on both the child and referenced table) followed by ADD CONSTRAINT FOREIGN KEY without NOT VALID (taking SHARE ROW EXCLUSIVE with a full scan on both tables) — neither is visible from the migration file alone. 3. Load references/operation-guide.md. Load references/postgres-locks.md when explaining why a flagged operation is unsafe. 4. For each SQL statement produced, check it against the detection checklist below. 5. If any operation requires ACCESS EXCLUSIVE (flagged as error or warning), ask the user before outputting the report:

"This migration contains an ACCESS EXCLUSIVE operation. A lock_timeout should be added to fail fast instead of queuing and cascading. The default is 2s — confirm or provide a custom value."

If the migration already contains SET LOCAL lock_timeout, note the existing value and ask the user to confirm it is appropriate. Use the confirmed value in the fix instructions. 6. Output a structured report:

Migration Review: <filename>

Errors — will cause downtime or be blocked by runtime guard

• [ERROR] <OperationClass> on <app>.<Model>.<field>: <what it does and why it's unsafe>

Fix: <one-sentence description of the safe alternative> What this rewrite changes: clarify whether ACCESS EXCLUSIVE is still required in the safe version, and if so, explain what actually improves — lock duration (full table scan → milliseconds), failure mode (silent queue cascade → fast timeout error), or both. Never let a reader assume the rewrite eliminates the lock entirely. <wiki/docs link if configured>

Warnings — may cause extended locks or deployment issues

• [WARNING] <OperationClass>: <issue>

Fix: <safe alternative>

Structural Issues

• [ERROR|WARNING] <issue> (e.g., missing atomic=False, RunPython without reverse)

Safe

• [OK] <operations that pass all checks>

7. If there are errors or warnings, offer to rewrite (Mode 3). 8. If everything passes, confirm the migration is safe to deploy.

---

Mode 2: Write

Goal

Generate correct, zero-downtime migration(s) for a described change.

Steps

1. Load references/operation-guide.md and references/examples.md. 2. Clarify the operation if ambiguous:

• What model and field?
• New column or changing an existing one?
• For FK: which table is referenced? New column or existing?
• For type changes: from what type to what type?
• Django version (affects db_default availability)?

3. If the operation will require ACCESS EXCLUSIVE, ask the user:

"This migration will use ACCESS EXCLUSIVE. A lock_timeout will be included to fail fast if the lock cannot be acquired. The default is 2s — confirm or provide a custom value."

4. Determine how many migration files are needed — many patterns require two files in separate PRs. 5. Generate the migration file(s) using patterns from references/examples.md. Add SET LOCAL lock_timeout = '<confirmed_value>' before each ACCESS EXCLUSIVE statement. In normal atomic = True migrations, this can be a preceding RunSQL; in atomic = False migrations, combine the timeout and DDL in the same RunSQL operation so SET LOCAL is still active when the DDL runs. 6. When two files are needed, always output deployment instructions:

## Deployment Order
- Migration 1: ships in the same PR as the model/code change
- Migration 2: ships in a follow-up PR after the deploy is confirmed stable

---

Mode 3: Rewrite

Goal

Transform an existing unsafe migration into one or more safe migrations.

Steps

1. Read the migration file. 2. Identify all unsafe operations using the detection checklist. 3. Load references/operation-guide.md and references/examples.md. 4. If any operation requires ACCESS EXCLUSIVE, ask the user:

"This migration contains an ACCESS EXCLUSIVE operation. A lock_timeout will be added to the rewrite to fail fast if the lock cannot be acquired. The default is 2s — confirm or provide a custom value."

5. For each unsafe operation, apply the correct safe pattern. 6. Add SET LOCAL lock_timeout = '<confirmed_value>' before each ACCESS EXCLUSIVE statement in the rewritten migration. In normal atomic = True migrations, this can be a preceding RunSQL; in atomic = False migrations, combine the timeout and DDL in the same RunSQL operation so SET LOCAL is still active when the DDL runs. 7. If the rewrite requires splitting into two files, generate both and include deployment instructions. 8. Preserve: migration number prefix, dependencies, any safe operations unchanged. 9. After rewriting, verify structural rules (see below). 10. After the migration code, always output a "What this rewrite changes" block that explains:

• Whether ACCESS EXCLUSIVE is still required (often yes — be explicit about this).
• What actually improves: lock duration (full table scan → milliseconds for metadata-only ops), failure mode (silent queue → fast timeout error with lock_timeout), or both.
• Any data integrity window introduced (e.g., NOT VALID means existing rows are unvalidated until Migration 2 runs) and whether it matters given the table's prior state.

---

Detection Checklist

Errors — unsafe regardless of runtime guard

Additional errors if covered by the project's runtime guard

If the project has a runtime guard configured (see Project Configuration), also flag these as Errors (they will raise at migration time):

If no runtime guard is configured, flag these as Errors too — they require a safe rewrite.

Warnings

---

Structural Rules (always check)

• atomic = False on the Migration class whenever any operation uses CONCURRENTLY or VALIDATE CONSTRAINT. For VALIDATE CONSTRAINT specifically: (1) SHARE UPDATE EXCLUSIVE is self-conflicting — see the conflict matrix in references/postgres-locks.md; (2) atomic = True keeps the wrapping transaction open for the full scan duration, holding all prior statement locks and increasing deadlock risk; (3) atomic = False lets VALIDATE run in its own transaction and release locks immediately on completion.
• SeparateDatabaseAndState split for RemoveField / DeleteModel: always two separate files.
• RunPython: always provide reverse_code=migrations.RunPython.noop at minimum.
• RunSQL: always provide reverse_sql. If truly irreversible, use migrations.RunSQL.noop and note why.
• Model access in RunPython: always apps.get_model("app", "ModelName"), never direct imports.
• SET LOCAL lock_timeout: required before every ACCESS EXCLUSIVE operation. Default value is 2s; always confirm with the user before writing or rewriting. Use SET LOCAL (not SET) so the timeout is scoped to the current transaction.
• `atomic = False` exception: SET LOCAL only resets at transaction end. In an atomic = False migration, each operation runs outside a wrapping transaction, so a standalone migrations.RunSQL("SET LOCAL lock_timeout = '2s'") resets before the next operation executes and has no effect. When the migration has atomic = False, always combine the timeout and the DDL in a single RunSQL call:

    migrations.RunSQL("""
        SET LOCAL lock_timeout = '2s';
        ALTER TABLE app_model ADD COLUMN ...;
    """)

Migration Examples

Concrete, copy-paste-ready patterns for zero-downtime Django migrations on PostgreSQL. Adapt app labels, table names, and field names to your project.

---

Column Drop (Two-File Split)

File 1 — remove from Django state (ships with code change)

# Generated by Django 5.2
# 0042_remove_mymodel_myfield_from_state.py

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0041_previous_migration"),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.RemoveField(
                    model_name="mymodel",
                    name="myfield",
                ),
            ],
            database_operations=[],
        )
    ]

File 2 — drop column from DB (follow-up PR)

# 0043_remove_mymodel_myfield_from_db.py

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0042_remove_mymodel_myfield_from_state"),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[],
            database_operations=[
                migrations.RunSQL(
                    sql="""
                        SET LOCAL lock_timeout = '2s';
                        ALTER TABLE myapp_mymodel DROP COLUMN myfield;
                    """,
                    reverse_sql=migrations.RunSQL.noop,
                )
            ],
        )
    ]

---

Add Index (Concurrent)

# 0044_mymodel_myfield_idx.py

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


class Migration(migrations.Migration):
    atomic = False  # CREATE INDEX CONCURRENTLY cannot run inside a transaction

    dependencies = [
        ("myapp", "0043_previous_migration"),
    ]

    operations = [
        AddIndexConcurrently(
            model_name="mymodel",
            index=models.Index(
                fields=["myfield"],
                name="mymodel_myfield_idx",
            ),
        ),
    ]

---

Add NOT NULL Column (Django 5.0+ db_default)

# 0045_mymodel_add_status.py

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0044_previous_migration"),
    ]

    operations = [
        migrations.AddField(
            model_name="mymodel",
            name="status",
            field=models.CharField(max_length=50, db_default="active"),
        ),
    ]

---

Add Foreign Key to Large Table (NOT VALID + VALIDATE)

File 1 — add column + constraint NOT VALID

# 0046_mymodel_add_other_id_fk_not_valid.py

from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0045_previous_migration"),
        ("otherapp", "0010_othermodel"),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.AddField(
                    model_name="mymodel",
                    name="other",
                    field=models.ForeignKey(
                        "otherapp.OtherModel",
                        null=True,
                        blank=True,
                        on_delete=django.db.models.deletion.SET_NULL,
                    ),
                ),
            ],
            database_operations=[
                migrations.RunSQL(
                    sql="""
                        SET LOCAL lock_timeout = '2s';
                        ALTER TABLE myapp_mymodel ADD COLUMN other_id integer NULL;
                        ALTER TABLE myapp_mymodel
                            ADD CONSTRAINT myapp_mymodel_other_id_fk
                            FOREIGN KEY (other_id)
                            REFERENCES otherapp_othermodel(id)
                            NOT VALID;
                    """,
                    reverse_sql="""
                        SET LOCAL lock_timeout = '2s';
                        ALTER TABLE myapp_mymodel DROP CONSTRAINT myapp_mymodel_other_id_fk;
                        ALTER TABLE myapp_mymodel DROP COLUMN other_id;
                    """,
                )
            ],
        )
    ]

File 2 — validate constraint (follow-up PR)

# 0047_mymodel_validate_other_id_fk.py

from django.db import migrations


class Migration(migrations.Migration):
    atomic = False  # VALIDATE CONSTRAINT must run outside a wrapping transaction: SHARE UPDATE EXCLUSIVE is self-conflicting; atomic=True would hold all prior locks open for the full scan duration

    dependencies = [
        ("myapp", "0046_mymodel_add_other_id_fk_not_valid"),
    ]

    operations = [
        migrations.RunSQL(
            sql="ALTER TABLE myapp_mymodel VALIDATE CONSTRAINT myapp_mymodel_other_id_fk;",
            reverse_sql=migrations.RunSQL.noop,
        )
    ]

---

Safe Column Type Change (VARCHAR Widening via RunSQL)

For safe cases only: varchar(N) → varchar(M) where M > N, or varchar → text. The custom backend blocks AlterField type changes — use SeparateDatabaseAndState to bypass.

# 0048_mymodel_widen_myfield.py

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0047_previous_migration"),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.AlterField(
                    model_name="mymodel",
                    name="myfield",
                    field=models.TextField(),
                ),
            ],
            database_operations=[
                migrations.RunSQL(
                    sql="ALTER TABLE myapp_mymodel ALTER COLUMN myfield TYPE text;",
                    reverse_sql="ALTER TABLE myapp_mymodel ALTER COLUMN myfield TYPE varchar(100);",
                )
            ],
        )
    ]

---

Set NOT NULL on Existing Nullable Column (Four-Step Path)

File 1 — add CHECK NOT VALID + set NOT NULL + drop CHECK

# 0049_mymodel_set_myfield_not_null.py

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0048_previous_migration"),
    ]

    operations = [
        # Step 1: add CHECK constraint (not valid — fast, metadata-only)
        migrations.RunSQL(
            sql="""
                ALTER TABLE myapp_mymodel
                    ADD CONSTRAINT chk_myfield_not_null
                    CHECK (myfield IS NOT NULL)
                    NOT VALID;
            """,
            reverse_sql="ALTER TABLE myapp_mymodel DROP CONSTRAINT chk_myfield_not_null;",
        ),
    ]

File 2 — validate (follow-up PR, long-running)

# 0050_mymodel_validate_myfield_not_null.py

from django.db import migrations


class Migration(migrations.Migration):
    atomic = False  # VALIDATE CONSTRAINT is long-running

    dependencies = [
        ("myapp", "0049_mymodel_set_myfield_not_null"),
    ]

    operations = [
        migrations.RunSQL(
            sql="ALTER TABLE myapp_mymodel VALIDATE CONSTRAINT chk_myfield_not_null;",
            reverse_sql=migrations.RunSQL.noop,
        )
    ]

File 3 — apply SET NOT NULL + drop CHECK (fast, skips scan because valid CHECK exists)

# 0051_mymodel_apply_not_null_drop_check.py

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0050_mymodel_validate_myfield_not_null"),
    ]

    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.AlterField(
                    model_name="mymodel",
                    name="myfield",
                    field=models.CharField(max_length=100),  # null=False now
                ),
            ],
            database_operations=[
                migrations.RunSQL(
                    sql="""
                        SET LOCAL lock_timeout = '2s';
                        ALTER TABLE myapp_mymodel ALTER COLUMN myfield SET NOT NULL;
                        ALTER TABLE myapp_mymodel DROP CONSTRAINT chk_myfield_not_null;
                    """,
                    reverse_sql="""
                        SET LOCAL lock_timeout = '2s';
                        ALTER TABLE myapp_mymodel ALTER COLUMN myfield DROP NOT NULL;
                        ALTER TABLE myapp_mymodel
                            ADD CONSTRAINT chk_myfield_not_null
                            CHECK (myfield IS NOT NULL)
                            NOT VALID;
                    """,
                )
            ],
        )
    ]

---

Data Migration (RunPython)

# 0052_backfill_mymodel_status.py

from django.db import migrations


def backfill_status(apps, schema_editor):
    MyModel = apps.get_model("myapp", "MyModel")  # always apps.get_model, never direct import
    batch_size = 1000
    while True:
        batch_ids = list(
            MyModel.objects
            .filter(status__isnull=True)[:batch_size]
            .values_list("pk", flat=True)
        )
        if not batch_ids:
            break
        MyModel.objects.filter(pk__in=batch_ids).update(status="active")


class Migration(migrations.Migration):

    dependencies = [
        ("myapp", "0051_previous_migration"),
    ]

    operations = [
        migrations.RunPython(
            backfill_status,
            reverse_code=migrations.RunPython.noop,
        ),
    ]

---

Drop Index (Concurrent)

# 0053_mymodel_drop_myfield_idx.py

from django.contrib.postgres.operations import RemoveIndexConcurrently
from django.db import migrations


class Migration(migrations.Migration):
    atomic = False  # DROP INDEX CONCURRENTLY cannot run inside a transaction

    dependencies = [
        ("myapp", "0052_previous_migration"),
    ]

    operations = [
        RemoveIndexConcurrently(
            model_name="mymodel",
            name="mymodel_myfield_idx",
        ),
    ]

---

Deployment Instructions Template

When outputting migrations that require a split, always include:

## Deployment Order

**Migration 1** (`0042_remove_mymodel_myfield_from_state.py`):
- Ships in the same PR as the model/code change
- Safe to run immediately — only updates Django's migration state, no DB change

**Migration 2** (`0043_remove_mymodel_myfield_from_db.py`):
- Ships in a **follow-up PR**, after confirming the deploy is stable
- Runs the actual DDL against the database

Operation Guide — Zero Downtime Migrations

Each entry explains: what PostgreSQL does, which lock it takes, what that lock blocks, and the safe alternative.

PostgreSQL version assumptions: This guide assumes PostgreSQL 11 or later for ADD COLUMN behavior (constant defaults no longer rewrite the table). It assumes PostgreSQL 12 or later for the SET NOT NULL + CHECK optimization (scan is skipped when a valid CHECK IS NOT NULL exists). On earlier versions, both operations perform full table rewrites and should be treated as unsafe on large tables.

Docs links: if the project has a docs/wiki URL configured in SKILL.md, append it when flagging each operation in review output. If none is configured, omit links.

Table of Contents

• ADD COLUMN
• SET NOT NULL on Existing Nullable Column
• DROP COLUMN
• DROP TABLE
• RENAME COLUMN
• RENAME TABLE
• ALTER COLUMN TYPE
• CREATE INDEX
• DROP INDEX
• ADD UNIQUE CONSTRAINT
• ADD FOREIGN KEY CONSTRAINT
• ADD CHECK CONSTRAINT
• ADD EXCLUSION CONSTRAINT
• RunPython — Data Migrations
• RunSQL
• Quick Lock Reference

---

ADD COLUMN

Why it can be unsafe

ALTER TABLE ADD COLUMN takes ACCESS EXCLUSIVE briefly. For PostgreSQL 11+, adding a column with a constant default no longer rewrites the table — it's a metadata-only operation. The lock is held only for milliseconds.

However, if the column is NOT NULL with no database-level default, old code (still running during rolling deploy) will try to INSERT rows without specifying the new column — and the DB will reject them with column cannot be null.

With db_default — Safe ✅

Django 5.0+ db_default sets the default at the database level. The DB fills in the value for any INSERT that omits the column, so old code continues to work.

field = models.CharField(max_length=100, db_default="")
field = models.BooleanField(db_default=False)
field = models.IntegerField(db_default=0)

One migration file. No split needed.

Nullable — Safe ✅

field = models.CharField(max_length=100, null=True, blank=True)

Old code omitting the column gets NULL — no constraint violation.

With Django default only, NOT NULL — Partially Unsafe ⚠️

The custom backend runs ADD COLUMN DEFAULT … NOT NULL then DROP DEFAULT. The DDL itself is safe (no table rewrite). But after the migration, the database default is gone. Old code inserts that omit the column will fail with column cannot be null until all instances are restarted.

Prefer `db_default` to keep the default at the DB level and eliminate this window.

---

SET NOT NULL on Existing Nullable Column

Why it's unsafe

ALTER TABLE ALTER COLUMN SET NOT NULL scans every row in the table to verify no NULLs exist. On a large table this takes minutes under ACCESS EXCLUSIVE, blocking all reads and writes.

Safe: four-step CHECK CONSTRAINT path ✅

PostgreSQL 12+ skips the full scan for SET NOT NULL if a valid CHECK (col IS NOT NULL) constraint already exists. The strategy: add the check as NOT VALID (fast), validate it separately (slow but non-blocking), then SET NOT NULL (fast, skips scan).

Migration 1 — add CHECK NOT VALID (fast, ACCESS EXCLUSIVE on metadata only):

ALTER TABLE app_model ADD CONSTRAINT chk_col_not_null CHECK (col IS NOT NULL) NOT VALID;

Migration 2 — validate (SHARE UPDATE EXCLUSIVE, non-blocking, long-running, atomic = False):

ALTER TABLE app_model VALIDATE CONSTRAINT chk_col_not_null;

Migration 3 — apply NOT NULL + drop CHECK (fast, no scan because valid CHECK exists):

ALTER TABLE app_model ALTER COLUMN col SET NOT NULL;
ALTER TABLE app_model DROP CONSTRAINT chk_col_not_null;

---

DROP COLUMN

Why it can be unsafe

ALTER TABLE DROP COLUMN itself is fast (metadata + ACCESS EXCLUSIVE briefly). The problem is code compatibility: Django generates explicit column lists in every SELECT. If the column is dropped before all instances restart with the new code, old code will crash querying a column that no longer exists.

Safe: two-file SeparateDatabaseAndState split ✅

Migration 1 — remove from Django's ORM state only, no DB change (ships with code):

operations = [
    migrations.SeparateDatabaseAndState(
        state_operations=[
            migrations.RemoveField(model_name="mymodel", name="myfield"),
        ],
        database_operations=[],
    )
]

After this migration + full deploy, old code is gone. The column still exists in the DB — nothing breaks.

Migration 2 — drop from DB (follow-up PR):

operations = [
    migrations.SeparateDatabaseAndState(
        state_operations=[],
        database_operations=[
            migrations.RunSQL(
                sql="""
                    SET LOCAL lock_timeout = '2s';
                    ALTER TABLE app_mymodel DROP COLUMN myfield;
                """,
                reverse_sql=migrations.RunSQL.noop,
            )
        ],
    )
]

Deployment: Migration 1 ships with the code change. Migration 2 ships in a follow-up PR after the deploy is confirmed stable.

---

DROP TABLE

Same reasoning and same two-file split as DROP COLUMN. First migration: DeleteModel in state_operations, empty database_operations. Second migration: RunSQL DROP TABLE.

---

RENAME COLUMN

Why it's unsafe

ALTER TABLE RENAME COLUMN is fast (metadata only), but it's a compatibility break: old code queries the old column name, new code queries the new name. During a rolling deploy, both versions run simultaneously — one of them will fail.

The custom backend raises UnsafeDatabaseOperationException to prevent this.

Safe: three-phase multi-deployment approach ✅

There is no single-migration safe rename. The pattern:

1. Phase 1 (current PR): Add new column (nullable or with db_default). Deploy code that writes to both old and new columns and reads from the new one with a fallback. 2. Phase 2 (next PR): RunPython data migration to backfill existing rows from old → new column. 3. Phase 3 (follow-up PR): Drop old column using the two-file SeparateDatabaseAndState split.

---

RENAME TABLE

Why it's unsafe

Same compatibility issue as RENAME COLUMN but at table level. Old code references the old table name.

The custom backend raises for RenameModel.

Safe: SeparateDatabaseAndState with updatable view ✅

1. Phase 1: Rename the table in DB, create an updatable view with the old name. Old code reads/writes through the view. New code uses the real table. 2. Phase 2: After full deployment, drop the view.

---

ALTER COLUMN TYPE

Why it's unsafe

ALTER TABLE ALTER COLUMN TYPE acquires ACCESS EXCLUSIVE. If the type change requires rewriting existing values (e.g., varchar → integer), PostgreSQL rewrites the entire table — potentially minutes of lock on large tables. Even for "cheap" casts, the lock is held.

The custom backend raises for all type changes.

PostgreSQL-safe type changes (but still blocked by the backend)

These three casts are free in PostgreSQL — no table rewrite, just a metadata update:

1. varchar(N) → varchar(M) where M > N (widening) 2. varchar(N) → text 3. numeric(P, S) → numeric(P2, S) where P2 > P (precision increase)

For these, bypass the backend guard using SeparateDatabaseAndState with RunSQL:

operations = [
    migrations.SeparateDatabaseAndState(
        state_operations=[
            migrations.AlterField(
                model_name="mymodel",
                name="myfield",
                field=models.TextField(),
            ),
        ],
        database_operations=[
            migrations.RunSQL(
                sql="ALTER TABLE app_mymodel ALTER COLUMN myfield TYPE text;",
                reverse_sql="ALTER TABLE app_mymodel ALTER COLUMN myfield TYPE varchar(100);",
            )
        ],
    )
]

Structurally unsafe type changes

For anything requiring a value rewrite (e.g., varchar → integer, changing semantics), use the same three-phase approach as column rename: add new column → backfill → drop old column.

---

CREATE INDEX

Why it's unsafe

CREATE INDEX (non-concurrent) takes a SHARE lock for the entire duration of the index build. On a large table this can take minutes, blocking all writes.

Safe: AddIndexConcurrently ✅

CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE — doesn't block reads or writes. Requires atomic = False because it cannot run inside a transaction.

from django.contrib.postgres.operations import AddIndexConcurrently

class Migration(migrations.Migration):
    atomic = False  # required — CONCURRENTLY cannot run in a transaction

    operations = [
        AddIndexConcurrently(
            model_name="mymodel",
            index=models.Index(fields=["myfield"], name="mymodel_myfield_idx"),
        ),
    ]

---

DROP INDEX

Why it can be unsafe

DROP INDEX (non-concurrent) takes ACCESS EXCLUSIVE, briefly blocking reads and writes.

Safe: RemoveIndexConcurrently ✅

from django.contrib.postgres.operations import RemoveIndexConcurrently

class Migration(migrations.Migration):
    atomic = False

    operations = [
        RemoveIndexConcurrently(
            model_name="mymodel",
            name="mymodel_myfield_idx",
        ),
    ]

---

ADD UNIQUE CONSTRAINT

Why it's unsafe

Django's default approach runs CREATE INDEX inline (takes SHARE lock, blocks writes during build) then promotes it to a constraint. On a large table this is equivalent to a slow CREATE INDEX.

The custom backend's alter_field already handles the index-first pattern for unique=True field changes — but AddConstraint(UniqueConstraint(...)) on an existing field does not get this treatment automatically.

Note: NOT VALID is not available for UNIQUE constraints — it is only supported for FOREIGN KEY and CHECK constraints (PostgreSQL 15: sql-altertable.html).

Safe: index first, then promote ✅

Migration 1 — create the index concurrently:

class Migration(migrations.Migration):
    atomic = False

    operations = [
        AddIndexConcurrently(
            model_name="mymodel",
            index=models.Index(fields=["myfield"], name="mymodel_myfield_uniq"),
        ),
    ]

Migration 2 — promote the index to a constraint (fast, uses existing index):

operations = [
    migrations.SeparateDatabaseAndState(
        state_operations=[
            migrations.AlterField(
                model_name="mymodel",
                name="myfield",
                field=models.CharField(max_length=100, unique=True),
            ),
        ],
        database_operations=[
            migrations.RunSQL(
                sql="ALTER TABLE app_mymodel ADD CONSTRAINT mymodel_myfield_uniq UNIQUE USING INDEX mymodel_myfield_uniq;",
                reverse_sql="ALTER TABLE app_mymodel DROP CONSTRAINT mymodel_myfield_uniq;",
            )
        ],
    )
]

---

ADD FOREIGN KEY CONSTRAINT

Why it's unsafe

ALTER TABLE ADD CONSTRAINT FOREIGN KEY scans the entire child table to validate referential integrity. It takes SHARE ROW EXCLUSIVE on the child table and SHARE ROW EXCLUSIVE on the referenced table simultaneously. This blocks writes (but not reads) on both tables for the duration of the scan — on large tables that is minutes.

⚠️ DROP CONSTRAINT on a FK acquires ACCESS EXCLUSIVE on both the child table and the referenced table. PostgreSQL implements FK constraints as system triggers on both sides; dropping the constraint removes those triggers. A migration that drops and immediately re-adds a FK (Django's default when altering a FK field) will hold ACCESS EXCLUSIVE on both tables for the full duration of the re-add scan if NOT VALID is not used. Always include SET LOCAL lock_timeout before any DROP CONSTRAINT on a FK.

Safe: NOT VALID + VALIDATE pattern ✅

NOT VALID skips the scan of existing rows — only new/updated rows are checked going forward. VALIDATE CONSTRAINT then validates existing rows under SHARE UPDATE EXCLUSIVE (non-blocking).

Migration 1 — add as NOT VALID (SHARE ROW EXCLUSIVE on both tables — no row scan, lock held milliseconds):

operations = [
    migrations.SeparateDatabaseAndState(
        state_operations=[
            migrations.AddField(
                model_name="mymodel",
                name="other",
                field=models.ForeignKey(
                    "otherapp.OtherModel",
                    null=True,
                    blank=True,
                    on_delete=django.db.models.deletion.SET_NULL,
                ),
            ),
        ],
        database_operations=[
            migrations.RunSQL(
                sql="""
                    SET LOCAL lock_timeout = '2s';
                    ALTER TABLE app_mymodel ADD COLUMN other_id integer NULL;
                    ALTER TABLE app_mymodel
                        ADD CONSTRAINT app_mymodel_other_id_fk
                        FOREIGN KEY (other_id) REFERENCES otherapp_othermodel(id)
                        NOT VALID;
                """,
                reverse_sql="""
                    SET LOCAL lock_timeout = '2s';
                    ALTER TABLE app_mymodel DROP CONSTRAINT app_mymodel_other_id_fk;
                    ALTER TABLE app_mymodel DROP COLUMN other_id;
                """,
            )
        ],
    )
]

SET LOCAL lock_timeout scopes the timeout to this transaction so the migration fails fast (with a clear error) instead of waiting indefinitely and cascading into connection pool exhaustion. 2s is the default; adjust if the table is known to have long-running transactions. SET LOCAL is used here (not SET) because Migration 1 runs inside a normal Django transaction — SET LOCAL resets automatically when the transaction commits.

The reverse_sql also includes SET LOCAL lock_timeout because DROP CONSTRAINT on a FK takes ACCESS EXCLUSIVE on both the child and referenced table.

Do not add lock_timeout to the VALIDATE CONSTRAINT step. VALIDATE uses SHARE UPDATE EXCLUSIVE (non-blocking), is expected to run for a long time while scanning rows, and would cause spurious failures if a lock_timeout fired during the scan.

Migration 2 — validate (SHARE UPDATE EXCLUSIVE on child table + ROW SHARE on referenced table — both non-blocking, atomic = False):

class Migration(migrations.Migration):
    atomic = False

    operations = [
        migrations.RunSQL(
            sql="ALTER TABLE app_mymodel VALIDATE CONSTRAINT app_mymodel_other_id_fk;",
            reverse_sql=migrations.RunSQL.noop,
        )
    ]

Deployment: Migration 1 ships with the code change. Migration 2 ships in a follow-up PR.

Why atomic = False for VALIDATE CONSTRAINT

Three reasons — not just "it's long-running":

1. `SHARE UPDATE EXCLUSIVE` is self-conflicting (see the lock conflict matrix in postgres-locks.md, the SHARE UPDATE EXCLUSIVE row × SHARE UPDATE EXCLUSIVE column = X). A session already holding SHARE UPDATE EXCLUSIVE on a table conflicts with another session trying to acquire it; running inside a wrapping transaction that already holds other locks increases the window for this conflict. 2. Accumulated lock hold time: with atomic = True, Django wraps all operations in a single BEGIN…COMMIT. The VALIDATE scan runs inside that transaction, holding all prior statement locks until the scan completes — potentially minutes. Other sessions waiting on those earlier locks pile up. 3. Clean release on completion: with atomic = False, VALIDATE runs in its own transaction and releases its SHARE UPDATE EXCLUSIVE + ROW SHARE locks immediately on completion. The next statement starts with a clean lock slate.

Django and DEFERRABLE INITIALLY DEFERRED

Django generates all FK constraints on PostgreSQL as DEFERRABLE INITIALLY DEFERRED. If a migration drops a FK in the same transaction as DML operations that triggered the deferred FK trigger, PostgreSQL will raise:

ERROR: cannot ALTER TABLE because it has pending trigger events

Django's generated workaround is:

SET CONSTRAINTS '<constraint_name>' IMMEDIATE;

placed immediately before the DROP CONSTRAINT. This forces the pending deferred trigger to fire immediately and be resolved before the DROP. This line is safe and expected — do not remove it from reviewed migrations.

---

ADD CHECK CONSTRAINT

Why it's unsafe

Same as FK: ALTER TABLE ADD CONSTRAINT CHECK scans every row under ACCESS EXCLUSIVE.

Safe: NOT VALID + VALIDATE ✅

# Migration 1: add as NOT VALID (fast, ACCESS EXCLUSIVE on metadata only)
# SET LOCAL scopes lock_timeout to this transaction; omit from VALIDATE step (SHARE UPDATE EXCLUSIVE, non-blocking)
migrations.RunSQL(
    sql="""
        SET LOCAL lock_timeout = '2s';
        ALTER TABLE app_mymodel ADD CONSTRAINT chk_myfield CHECK (myfield > 0) NOT VALID;
    """,
    reverse_sql="""
        SET LOCAL lock_timeout = '2s';
        ALTER TABLE app_mymodel DROP CONSTRAINT chk_myfield;
    """,
)

# Migration 2: validate (atomic = False, non-blocking)
class Migration(migrations.Migration):
    atomic = False
    operations = [
        migrations.RunSQL(
            sql="ALTER TABLE app_mymodel VALIDATE CONSTRAINT chk_myfield;",
            reverse_sql=migrations.RunSQL.noop,
        )
    ]

---

ADD EXCLUSION CONSTRAINT

Why it's unsafe

ALTER TABLE ADD CONSTRAINT EXCLUDE builds a GiST or SP-GiST index inline under ACCESS EXCLUSIVE. There is no CONCURRENTLY variant for exclusion constraints.

The custom backend raises for ExclusionConstraint. No in-place safe alternative exists — requires downtime window or new table + copy.

Note: NOT VALID is not available for EXCLUDE constraints — it is only supported for FOREIGN KEY and CHECK constraints (PostgreSQL 15: sql-altertable.html).

---

RunPython — Data Migrations

Required rules

1. Always use `apps.get_model`, never direct imports.

Why: a direct import uses the current model class. In a migration, you need the model as it existed at the time the migration was written. If the model later changes (field added, renamed), a direct import in an old migration will fail or corrupt data.

# Wrong — uses current model, breaks if schema changes later
from myapp.models import MyModel

# Correct — uses historical model snapshot
def migrate(apps, schema_editor):
    MyModel = apps.get_model("myapp", "MyModel")

2. Always provide `reverse_code`.

migrations.RunPython(migrate, reverse_code=migrations.RunPython.noop)

3. Batch large updates.

A single .update() on millions of rows holds FOR NO KEY UPDATE row locks on every matching row for the full duration of the transaction, blocking application writes on those rows. Use chunked updates instead:

def migrate(apps, schema_editor):
    MyModel = apps.get_model("myapp", "MyModel")
    batch_size = 1000
    while True:
        batch_ids = list(
            MyModel.objects
            .filter(status__isnull=True)[:batch_size]
            .values_list("pk", flat=True)
        )
        if not batch_ids:
            break
        MyModel.objects.filter(pk__in=batch_ids).update(status="active")

Each batch runs in its own short write-lock window (when the migration has atomic = False) or commits row locks more frequently, keeping the lock window small per batch and avoiding long queues on application writes.

---

RunSQL

Always provide `reverse_sql`. If the operation is truly irreversible, use migrations.RunSQL.noop and add a comment explaining why.

For DDL statements that are long-running or use CONCURRENTLY, set atomic = False on the Migration class.

---

Quick Lock Reference

PostgreSQL Locking Reference

Source: django-pg-zero-downtime-migrations and PostgreSQL docs.

Load this file when:

• explaining why a specific operation causes downtime
• a developer asks what a lock type blocks
• reasoning about whether two concurrent operations can conflict

---

Table-Level Lock Conflict Matrix

X = conflict (the row lock blocks the column lock from being acquired).

Key insight: ACCESS EXCLUSIVE conflicts with everything, including ACCESS SHARE (plain SELECT). This means any ALTER TABLE that holds ACCESS EXCLUSIVE blocks all reads and writes until it completes or is queued behind long-running transactions.

Note: SHARE is not self-conflicting, so two concurrent CREATE INDEX (non-concurrent) operations do not block each other. However, both block all writes (ROW EXCLUSIVE) for their full duration.

---

Migration Operations and Their Locks

Notes:

• CREATE SEQUENCE, DROP SEQUENCE, CREATE TABLE, DROP TABLE take ACCESS EXCLUSIVE but are safe because application code should not reference them yet (new) or anymore (dropped).
• Not all ALTER TABLE operations take ACCESS EXCLUSIVE — but Django's schema editor issues them for all field/constraint changes by default. The only exceptions are operations explicitly rewritten to use CONCURRENTLY or NOT VALID, or ADD FOREIGN KEY which uses SHARE ROW EXCLUSIVE instead.
• VALIDATE CONSTRAINT uses SHARE UPDATE EXCLUSIVE, which does not conflict with reads or writes — this is why it is the safe way to validate large tables. For FK constraints, it also acquires ROW SHARE on the referenced table, which is likewise non-blocking.

---

Business Logic Operations and Their Locks

Critical implication: CREATE INDEX (non-concurrent) takes SHARE lock, which conflicts with ROW EXCLUSIVE. This means CREATE INDEX blocks all writes (INSERT, UPDATE, DELETE) for the entire duration of the index build — potentially minutes on a large table.

CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, which does not conflict with ROW EXCLUSIVE — writes continue unblocked during the build.

---

Row-Level Lock Conflict Matrix

Row locks matter for data migrations (RunPython) that update many rows. If migration and application code update the same rows concurrently, the second writer waits for the first to finish.

Implication for data migrations: a RunPython that updates all rows in a large table via a single .update() call holds FOR NO KEY UPDATE row locks on every row for the duration. Application writes to those rows queue behind it. Use chunked updates or id-range batches to keep the lock window small per transaction.

---

The FIFO Wait Queue Problem

This is why even a "fast" ACCESS EXCLUSIVE can cause downtime:

Timeline:
  [long SELECT running]        ←── holds ACCESS SHARE
  [ALTER TABLE queued]         ←── waiting for ACCESS SHARE to release, holds ACCESS EXCLUSIVE slot
  [all new queries queued]     ←── waiting behind the ALTER TABLE

Even if the ALTER TABLE itself takes 50ms, if it has to wait behind a 30-second analytics query, all new requests (including simple SELECTs) queue behind it for 30 seconds. On a busy service this fills the connection pool.

Four metrics to think about for any migration:

1. Operation time — how long the DDL statement itself runs (index builds, constraint scans). Minimize with CONCURRENTLY and NOT VALID. 2. Waiting time — how long the migration waits for existing transactions to finish before it can acquire the lock. Minimize by running migrations during low-traffic periods or by setting lock_timeout. 3. Connection pool pressure — queries queuing behind a lock consume connections. The longer the wait, the more connections pile up. Minimize by keeping operations small. 4. Operations per transaction — more operations in one transaction means longer lock hold time and higher deadlock risk. Keep migration files small and focused.

---

Safe vs Unsafe: Quick Lookup