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.uuid4onAddField. Django calls the callable once while generating the SQL and uses that value as theDEFAULTfor every existing row. All old rows get the same UUID, and aunique=Truemigration fails. Add the column nullable, backfill per row, then tighten.- Idle-in-transaction sessions. A connection that ran
BEGINand then went off to call an external API holds its locks the whole time.lock_timeoutsaves the site, but the migration still cannot proceed. Look forstate = 'idle in transaction'inpg_stat_activityand setidle_in_transaction_session_timeout. - Dual write leaks.
QuerySet.update(),bulk_create(), raw SQL andsave(update_fields=[...])bypass thesave()logic shown above. Check every write path, or rely on the backfill being rerunnable and run it once more just beforeVALIDATE. - Foreign keys. Adding a
ForeignKeycreates 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 aNOT VALIDconstraint withRunSQL, thenVALIDATE. - Type changes.
ALTER COLUMN TYPEfromintegertobigintrewrites the table underACCESS EXCLUSIVE. Treat it as a new column plus backfill, not as anAlterField. - 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
sqlmigratefor every migration and read the SQL, not only the Python. - Put
SET LOCAL lock_timeoutat 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 NULLwith a computed value: nullable column, dual write, batched backfill,CHECK ... NOT VALID,VALIDATEin a separate migration,SET NOT NULL, drop theCHECK.- Indexes on live tables:
AddIndexConcurrentlywithatomic = 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_activityfor 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...