All notes
Programming

The migration that froze the site: lock queues, NOT VALID, CONCURRENTLY and the two-deploy rule

Even a one-millisecond ALTER TABLE can stall every request. Which DDL locks what, and how to add NOT NULL columns, build indexes and drop columns in Django without downtime.

Picture an ordinary release. You add one nullable field to the Order model, makemigrations produces a two-line migration, and CI runs migrate before the new pods start. The ALTER TABLE itself takes milliseconds. Yet for the next half minute every page that touches orders hangs, the connection pool fills up, and health checks start failing.

Nothing was "slow" in the usual sense. The migration simply asked PostgreSQL for a lock at the wrong moment, and the whole application lined up behind it. This post is about why that happens, which DDL statements are actually dangerous, and how to ship schema changes in Django without stopping traffic: adding a NOT NULL column, building indexes, dropping and renaming columns, and the one setting that should be in every migration, lock_timeout.

Why "it's just one ALTER TABLE" breaks

Almost every ALTER TABLE in PostgreSQL takes an ACCESS EXCLUSIVE lock on the table. That is the strongest lock there is: it conflicts with everything, including a plain SELECT, which holds ACCESS SHARE.

The naive mental model is "the ALTER is instant, so the lock is instant". Two things break it.

First, some statements are not instant. They rewrite the whole table or scan every row while holding ACCESS EXCLUSIVE. On a large table that means minutes of total downtime for that table.

Second, and more surprising: even an instant statement has to wait for its lock, and while it waits it blocks everyone who comes after it. PostgreSQL grants locks in queue order. If a long report or a forgotten transaction holds ACCESS SHARE, your ALTER waits. Every new query that wants ACCESS SHARE now conflicts with the waiting ACCESS EXCLUSIVE request, so it queues behind the ALTER instead of running.

-- session 1: a long report or a forgotten open transaction
BEGIN;
SELECT count(*) FROM shop_order;  -- holds ACCESS SHARE until COMMIT

-- session 2: the migration
ALTER TABLE shop_order ADD COLUMN note text;  -- waits for ACCESS EXCLUSIVE

-- session 3: an ordinary request from the site
SELECT * FROM shop_order WHERE id = 42;  -- now waits too, behind session 2

Session 3 would normally run instantly. Now it waits as long as session 1 keeps its transaction open. With a busy site that means every worker gets stuck, and you have an outage caused by a migration that would have taken a millisecond.

The danger of DDL is not only how long it runs, but how long it waits for its lock, because everyone queues behind it.

When this is happening, this query shows who is blocking whom:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
ORDER BY xact_start;

Which DDL locks what

Here is the practical map for PostgreSQL 16+. "Instant" means it only changes the catalog; "scan" means it reads every row; "rewrite" means it writes a new copy of the table.

Operation Lock Cost
ADD COLUMN nullable, no default ACCESS EXCLUSIVE instant
ADD COLUMN ... DEFAULT <constant> ACCESS EXCLUSIVE instant (since PG 11)
ADD COLUMN ... DEFAULT <volatile> (now(), gen_random_uuid()) ACCESS EXCLUSIVE rewrite
ALTER COLUMN ... TYPE ACCESS EXCLUSIVE usually rewrite (except e.g. widening varchar, varchar → text)
ALTER COLUMN ... SET NOT NULL ACCESS EXCLUSIVE scan (skipped if a valid CHECK (col IS NOT NULL) exists)
ADD CONSTRAINT ... CHECK / FOREIGN KEY ACCESS EXCLUSIVE / SHARE ROW EXCLUSIVE scan
ADD CONSTRAINT ... NOT VALID same as above instant
VALIDATE CONSTRAINT SHARE UPDATE EXCLUSIVE scan, but reads and writes continue
CREATE INDEX SHARE builds the index, writes blocked
CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE slower, writes continue
DROP COLUMN, RENAME COLUMN ACCESS EXCLUSIVE instant

Two rules come out of this table. The first: anything that scans or rewrites under ACCESS EXCLUSIVE must be split into steps that do the heavy work under a weaker lock. The second: even instant operations need lock_timeout, because of the queue. Let's start with the second, since it is the cheapest fix.

lock_timeout: fail fast instead of freezing the site

lock_timeout limits how long a statement may wait for a lock. If it cannot get the lock in time, the statement fails with canceling statement due to lock timeout, the transaction rolls back, and the queue behind it is released. A failed migration that you retry is far better than a frozen site.

On PostgreSQL, Django runs each migration inside a transaction (unless atomic = False), so SET LOCAL applies exactly to that migration and resets at commit:

# shop/migrations/0002_order_total_tiyin.py
from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [("shop", "0001_initial")]

    operations = [
        migrations.RunSQL(
            "SET LOCAL lock_timeout = '3s'",
            reverse_sql="SET LOCAL lock_timeout = '3s'",
        ),
        migrations.AddField(
            model_name="order",
            name="total_tiyin",
            field=models.BigIntegerField(null=True),
        ),
    ]

Note that statement_timeout is a different thing: it limits total execution time, including the work itself. For DDL you mostly want lock_timeout; for a backfill you may want neither.

Since a lock timeout is an expected outcome, the deploy should retry rather than give up. A crude but working version:

for attempt in 1 2 3 4 5; do
  python manage.py migrate --noinput && break
  echo "migrate failed (attempt $attempt), retrying in 10s..."
  sleep 10
done

This retries on any error, not only lock timeouts, so keep the attempt count small and look at the log when it fails repeatedly. Also remember that inside one migration the locks accumulate: if the migration touches three tables, it holds all three locks until commit. Small migrations keep that window short.

Adding a NOT NULL column safely

Start with the good news. Since PostgreSQL 11, ADD COLUMN ... NOT NULL DEFAULT 'UZS' with a constant default does not rewrite the table: the default is stored in the catalog and returned for old rows. Django, for a field with default=, emits exactly that and then drops the database default. So a constant default is a safe case (with lock_timeout).

The hard case is when the value must be computed per row. Say we are moving money from Decimal to integer tiyin and need a total_tiyin column filled from total. A constant default makes no sense, and ADD COLUMN ... NOT NULL without a default fails on a non-empty table. The plan is expand, backfill, then tighten:

-- 1. nullable column, no default: metadata only, instant
ALTER TABLE shop_order ADD COLUMN total_tiyin bigint;

-- 2. backfill in small batches, each in its own transaction (see below)

-- 3. constraint that is NOT checked against existing rows: instant
ALTER TABLE shop_order ADD CONSTRAINT order_total_tiyin_nn
    CHECK (total_tiyin IS NOT NULL) NOT VALID;

-- 4. full scan, but under SHARE UPDATE EXCLUSIVE: reads and writes continue
ALTER TABLE shop_order VALIDATE CONSTRAINT order_total_tiyin_nn;

-- 5. PostgreSQL sees the valid CHECK and skips its own full scan
ALTER TABLE shop_order ALTER COLUMN total_tiyin SET NOT NULL;

-- 6. the CHECK is now redundant
ALTER TABLE shop_order DROP CONSTRAINT order_total_tiyin_nn;

Step 5 is the trick: since PostgreSQL 12, SET NOT NULL skips the full scan if a valid CHECK (col IS NOT NULL) constraint already proves there are no NULLs. So the expensive scan happens in step 4 under SHARE UPDATE EXCLUSIVE, which does not block reads or writes.

Now the same in Django. First the model for the transition period. New rows must be correct from the moment the column appears, otherwise the backfill chases a moving target:

# shop/models.py (transition period)
from django.db import models


class Order(models.Model):
    total = models.DecimalField(max_digits=14, decimal_places=2)
    total_tiyin = models.BigIntegerField(null=True)
    created_at = models.DateTimeField(auto_now_add=True)

    def save(self, *args, **kwargs):
        # dual write: new rows are already correct while the backfill runs
        self.total_tiyin = int(self.total * 100)
        super().save(*args, **kwargs)

Migration 0002 is the one shown in the lock_timeout section: a nullable BigIntegerField. Then the backfill. It must not run as one huge UPDATE: that holds row locks on the whole table for its entire duration, creates a mass of dead tuples at once, and produces a WAL spike that replicas have to replay. Small batches, each committed separately, walking the primary key with keyset pagination so we never rescan the beginning of the table:

# shop/migrations/0003_backfill_total_tiyin.py
from django.db import migrations
from django.db.models import BigIntegerField, F
from django.db.models.functions import Cast

BATCH_SIZE = 5_000


def backfill_total_tiyin(apps, schema_editor):
    Order = apps.get_model("shop", "Order")
    last_pk = 0
    while True:
        ids = list(
            Order.objects.filter(pk__gt=last_pk)
            .order_by("pk")
            .values_list("pk", flat=True)[:BATCH_SIZE]
        )
        if not ids:
            break
        Order.objects.filter(pk__in=ids, total_tiyin__isnull=True).update(
            total_tiyin=Cast(F("total") * 100, output_field=BigIntegerField())
        )
        last_pk = ids[-1]


class Migration(migrations.Migration):
    atomic = False  # every UPDATE commits on its own

    dependencies = [("shop", "0002_order_total_tiyin")]

    operations = [
        migrations.RunPython(backfill_total_tiyin, migrations.RunPython.noop),
    ]

atomic = False is what makes each update() commit on its own. If the migration dies halfway, rerunning it is safe: rows that are already filled are skipped by total_tiyin__isnull=True.

Then the constraint, in two separate migrations:

# shop/migrations/0004_order_total_tiyin_nn.py
from django.contrib.postgres.operations import AddConstraintNotValid
from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [("shop", "0003_backfill_total_tiyin")]

    operations = [
        AddConstraintNotValid(
            model_name="order",
            constraint=models.CheckConstraint(
                condition=models.Q(total_tiyin__isnull=False),
                name="order_total_tiyin_nn",
            ),
        ),
    ]
# shop/migrations/0005_validate_order_total_tiyin_nn.py
from django.contrib.postgres.operations import ValidateConstraint
from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [("shop", "0004_order_total_tiyin_nn")]

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

Why separate? If AddConstraintNotValid and ValidateConstraint sit in one atomic migration, the ACCESS EXCLUSIVE lock from the first operation is held until commit, i.e. through the whole validation scan. You have carefully split the work and then glued it back together. Either put them in different migrations or set atomic = False.

Do not forget to add the same CheckConstraint to Order.Meta.constraints, otherwise the next makemigrations will generate a RemoveConstraint.

Finally, the column becomes NOT NULL and the helper constraint goes away (remove it from Meta.constraints too):

# shop/migrations/0006_order_total_tiyin_not_null.py
from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [("shop", "0005_validate_order_total_tiyin_nn")]

    operations = [
        migrations.RunSQL(
            "SET LOCAL lock_timeout = '3s'",
            reverse_sql="SET LOCAL lock_timeout = '3s'",
        ),
        migrations.AlterField(
            model_name="order",
            name="total_tiyin",
            field=models.BigIntegerField(),
        ),
        migrations.RemoveConstraint(
            model_name="order",
            name="order_total_tiyin_nn",
        ),
    ]

Before merging, look at the SQL Django will actually run:

python manage.py sqlmigrate shop 0006

You should see SET NOT NULL and a DROP CONSTRAINT, with no UPDATE. If the field had a default, Django would first emit UPDATE ... SET total_tiyin = ... WHERE total_tiyin IS NULL, which is exactly the kind of surprise sqlmigrate exists for.

Indexes: CREATE INDEX CONCURRENTLY

A plain CREATE INDEX holds a SHARE lock: reads work, but every INSERT, UPDATE and DELETE on the table waits until the index is built. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead and builds the index in several passes while writes continue. The price: it is slower, it waits for transactions that started before it, and it cannot run inside a transaction block.

Django has a ready operation for it in django.contrib.postgres:

# shop/migrations/0007_order_created_at_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 = [("shop", "0006_order_total_tiyin_not_null")]

    operations = [
        AddIndexConcurrently(
            model_name="order",
            index=models.Index(fields=["created_at"], name="order_created_at_idx"),
        ),
    ]

makemigrations generates a regular AddIndex; replace it by hand with AddIndexConcurrently and add atomic = False. For dropping there is RemoveIndexConcurrently.

If a concurrent build fails (a deadlock, a unique violation, a cancelled statement), PostgreSQL leaves an invalid index behind. It is not used for reads but is still maintained on every write. Find and drop it before retrying:

-- indexes left behind by a failed CREATE INDEX CONCURRENTLY
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

-- drop it without blocking writes, then run the migration again
DROP INDEX CONCURRENTLY IF EXISTS order_created_at_idx;

Dropping and renaming columns: two deploys

Here the problem is not locks. DROP COLUMN is instant. The problem is that during a rolling deploy old and new code run at the same time against the same database.

Django never writes SELECT *: it lists every column of the model by name. If the migration drops legacy_status while old pods are still serving traffic, every query from the old code fails with column "legacy_status" does not exist. The reverse also breaks: if the column is NOT NULL without a database default and the new code no longer knows about it, every INSERT from the new code fails.

So the change is split across two releases.

Deploy 1. Remove the field from the model, but only from Django's state, and make the column nullable so inserts from new code work:

# deploy 1: shop/migrations/0008_remove_legacy_status_from_state.py
from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [("shop", "0007_order_created_at_idx")]

    operations = [
        migrations.SeparateDatabaseAndState(
            # Django forgets the field: new code no longer SELECTs or INSERTs it
            state_operations=[
                migrations.RemoveField(model_name="order", name="legacy_status"),
            ],
            # the column stays, but must accept rows that new code inserts
            database_operations=[
                migrations.RunSQL(
                    "ALTER TABLE shop_order "
                    "ALTER COLUMN legacy_status DROP NOT NULL",
                    reverse_sql=migrations.RunSQL.noop,
                ),
            ],
        ),
    ]

Deploy 2, after every pod runs code that no longer mentions the field:

# deploy 2: shop/migrations/0009_drop_legacy_status_column.py
from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [("shop", "0008_remove_legacy_status_from_state")]

    operations = [
        migrations.RunSQL(
            "SET LOCAL lock_timeout = '3s'",
            reverse_sql=migrations.RunSQL.noop,
        ),
        migrations.RunSQL(
            "ALTER TABLE shop_order DROP COLUMN IF EXISTS legacy_status",
            reverse_sql=migrations.RunSQL.noop,
        ),
    ]

Renaming is the same story, worse: there is no moment when both names exist. If you only want a nicer name in Python, do not touch the database at all:

from django.db import models


class Order(models.Model):
    # the Python name changes; the column in PostgreSQL stays "amount"
    total = models.DecimalField(max_digits=14, decimal_places=2, db_column="amount")

sqlmigrate for the resulting migration should show no column rename. If you really need a new column name in the database, use expand and contract: add the new column, write to both, backfill, switch reads, and drop the old column in a later deploy as shown above.

Pitfalls in production

  • default=uuid.uuid4 on AddField. Django calls the callable once while generating the SQL and uses that value as the DEFAULT for every existing row. All old rows get the same UUID, and a unique=True migration fails. Add the column nullable, backfill per row, then tighten.
  • Idle-in-transaction sessions. A connection that ran BEGIN and then went off to call an external API holds its locks the whole time. lock_timeout saves the site, but the migration still cannot proceed. Look for state = 'idle in transaction' in pg_stat_activity and set idle_in_transaction_session_timeout.
  • Dual write leaks. QuerySet.update(), bulk_create(), raw SQL and save(update_fields=[...]) bypass the save() logic shown above. Check every write path, or rely on the backfill being rerunnable and run it once more just before VALIDATE.
  • Foreign keys. Adding a ForeignKey creates both an index and a constraint, and the constraint locks both tables and scans the referencing one. On a big table, consider splitting it into a concurrent index and a NOT VALID constraint with RunSQL, then VALIDATE.
  • Type changes. ALTER COLUMN TYPE from integer to bigint rewrites the table under ACCESS EXCLUSIVE. Treat it as a new column plus backfill, not as an AlterField.
  • CONCURRENTLY waits for old transactions. It does not block others, but it waits for transactions that started before it, so a long report can stall the migration step itself.
  • Replication lag. Each batch of the backfill becomes WAL that replicas must apply. If you read from replicas, watch the lag and add a short pause between batches if it grows.

Checklist

  • Run sqlmigrate for every migration and read the SQL, not only the Python.
  • Put SET LOCAL lock_timeout at the top of every atomic migration that touches a large or hot table, and make the deploy retry.
  • Nothing that scans or rewrites a big table runs under ACCESS EXCLUSIVE.
  • NOT NULL with a computed value: nullable column, dual write, batched backfill, CHECK ... NOT VALID, VALIDATE in a separate migration, SET NOT NULL, drop the CHECK.
  • Indexes on live tables: AddIndexConcurrently with atomic = False; check for invalid indexes after failures.
  • Drop or rename columns in two deploys: first the code stops using it, then the database drops it.
  • Before a big migration, look at pg_stat_activity for long transactions.

Takeaway

A migration is dangerous for two reasons: it can hold a strong lock while doing heavy work, and it can queue for a lock and drag every other query into the queue with it. The cure is the same each time: split the heavy part out under a weaker lock (NOT VALID + VALIDATE, CONCURRENTLY, batched backfill), keep the strong-lock steps tiny, and protect them with lock_timeout. And whenever old and new code share a database, let the schema change take two deploys instead of one.

// comments

Loading comments...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯