All postsEngineering

Zero-downtime Postgres migrations in Django 6

Migration outages come from the lock queue. Expand, backfill, switch and contract in Django with db_default, concurrent indexes and safe NOT NULL.

Johnny Unar9 min read

01

the lock queue is what takes you down

The usual story about a migration causing an outage goes like this: someone adds a column with a default to a huge table, Postgres rewrites every row, and the site hangs. That was true before Postgres 11. Since 11, adding a column with a non-volatile default is a catalog change that finishes instantly no matter how big the table is, and the default is stored once rather than written into every row. A constant or a stable function such as now() qualifies. A volatile default like gen_random_uuid() or clock_timestamp() still rewrites the table, which you can confirm by comparing pg_relation_filenode() before and after.

What still takes sites down is the lock queue. Most ALTER TABLE forms, including the cheap ADD COLUMN, need an ACCESS EXCLUSIVE lock for a moment, and that lock conflicts with everything, even a plain SELECT. If any transaction is holding even a read lock on the table, say a report that started a minute ago or a connection sitting idle in transaction, the ALTER has to wait. Every query that arrives after it then queues behind the waiting ALTER, because Postgres won't let them jump ahead of a pending exclusive lock. A migration that would take a millisecond turns into a table nobody can read until the old transaction finishes. You can watch this happen with three psql sessions: one opens a transaction and selects from the table, the second runs alter table ... add column, and a simple select ... where id = 1 in the third hangs until the first commits.

The defence is a short lock_timeout, so the migration gives up quickly instead of freezing traffic, plus a retry. You don't need to touch the migrations to set it: libpq reads PGOPTIONS from the environment, and psycopg passes it through, so the deploy step can look like this:

sh
1#!/bin/sh
2for attempt in 1 2 3 4 5; do
3 PGOPTIONS="-c lock_timeout=3s" python manage.py migrate && exit 0
4 sleep $((attempt * 10))
5done
6exit 1

When the lock isn't available within three seconds, the statement fails with canceling statement due to lock timeout. Django runs each migration in its own transaction on Postgres, so the failed one rolls back cleanly and the next attempt starts it again. The exception is a migration with atomic = False, which is covered below.

When a migration does hang, find out who is in the way before killing anything:

sql
1select pid,
2 pg_blocking_pids(pid) as blocked_by,
3 state,
4 now() - xact_start as xact_age,
5 left(query, 60) as query
6from pg_stat_activity
7where cardinality(pg_blocking_pids(pid)) > 0
8 or pid in (select unnest(pg_blocking_pids(a.pid)) from pg_stat_activity a)
9order by xact_start;

The rest of this post is the release sequence that keeps every step short: expand, backfill, switch, contract. The running example replaces customer.email with a normalized, unique, non-null primary_email, and all of the code was run against Postgres 18 with Django 6.1.

02

expand with columns old code can live with

During a deploy, old and new versions of the app run side by side, so every schema change has to work with both. The first release only adds things. A new nullable column is the simplest case. A new non-null column needs a default that lives in the database, and this is where Django's db_default (added in Django 5.0) beats default:

python
1from django.db import models
2from django.db.models.functions import Now
3
4
5class Customer(models.Model):
6 email = models.TextField()
7 primary_email = models.TextField(null=True)
8 created_at = models.DateTimeField(db_default=Now())

sqlmigrate shows the difference. With db_default=Now() Django emits ADD COLUMN "created_at" timestamp with time zone DEFAULT (STATEMENT_TIMESTAMP()) NOT NULL and leaves the default in place, which is metadata-only on Postgres 11 and later because statement_timestamp() is stable. With a Python-side default=timezone.now, Django adds the column with a literal timestamp as the default and then immediately runs ALTER COLUMN ... DROP DEFAULT. That's also cheap, but it leaves the column non-null with no default, so any pod still running the old code fails on insert with a not-null violation until the rollout finishes. The database default keeps old inserts working.

03

indexes without blocking writes

A plain CREATE INDEX blocks writes for the whole build. CREATE INDEX CONCURRENTLY only takes a SHARE UPDATE EXCLUSIVE lock, but Postgres refuses to run it inside a transaction block, so the migration must set atomic = False. Django ships an operation for the common case, AddIndexConcurrently in django.contrib.postgres.operations, which keeps the model state in sync without hand-written SQL.

A unique index on an expression is where it gets awkward. models.UniqueConstraint(Lower("primary_email"), ...) is the right model declaration, and on Postgres Django implements it as a unique index, but the generated migration builds it without CONCURRENTLY. SeparateDatabaseAndState fixes that: run your own SQL against the database and tell Django's migration state that the constraint now exists.

python
1from django.contrib.postgres.operations import AddIndexConcurrently
2from django.db import migrations, models
3from django.db.models.functions import Lower
4
5
6class Migration(migrations.Migration):
7 atomic = False
8 dependencies = [("customers", "0003_sync_trigger")]
9
10 operations = [
11 AddIndexConcurrently(
12 "customer",
13 models.Index(fields=["created_at"], name="customer_created_idx"),
14 ),
15 migrations.SeparateDatabaseAndState(
16 database_operations=[
17 migrations.RunSQL(
18 sql="""
19 create unique index concurrently customer_primary_email_uniq
20 on customers_customer (lower(primary_email))
21 """,
22 reverse_sql="drop index concurrently customer_primary_email_uniq",
23 ),
24 ],
25 state_operations=[
26 migrations.AddConstraint(
27 "customer",
28 models.UniqueConstraint(
29 Lower("primary_email"), name="customer_primary_email_uniq"
30 ),
31 ),
32 ],
33 ),
34 ]

The model's Meta declares the same index and constraint, and makemigrations --check reports no changes afterwards, which is how you know the state and the database agree. Because the index name matches what Django would have generated, later migrations that alter or drop the constraint work normally.

Non-atomic migrations need more care when they fail. If a concurrent build is cancelled, by lock_timeout or a duplicate value, Postgres leaves an INVALID index behind, and Django doesn't record the migration as applied. A retry then fails with "already exists". Drop the invalid index and run it again. Avoid IF NOT EXISTS on these statements: it turns the retry into a silent success while the invalid index enforces nothing.

04

backfill outside the migration, with a trigger for writes in flight

Before backfilling, make sure rows written during and after the backfill come out right, otherwise the backfill chases a moving target. While old code still writes only email, a temporary trigger keeps primary_email in step. It has to handle updates as well as inserts, because old code that changes an email after the backfill would otherwise leave primary_email stale:

sql
1create function customers_sync_primary_email() returns trigger
2language plpgsql as $$
3begin
4 if tg_op = 'INSERT' then
5 if new.primary_email is null then
6 new.primary_email := lower(new.email);
7 end if;
8 elsif new.email is distinct from old.email then
9 new.primary_email := lower(new.email);
10 end if;
11 return new;
12end;
13$$;
14
15create trigger customers_sync_primary_email
16before insert or update of email on customers_customer
17for each row execute function customers_sync_primary_email();

Ship it in a migration via RunSQL, with a reverse that drops the trigger and the function. The backfill itself doesn't belong in a migration: a RunPython that walks millions of rows runs inside one transaction, holds its locks to the end and can't be resumed. A management command that commits small batches is easier on the database and safe to restart:

python
1import time
2
3from django.core.management.base import BaseCommand
4from django.db import connection
5
6BATCH = 5_000
7PAUSE = 0.05
8
9
10class Command(BaseCommand):
11 def handle(self, *args, **options):
12 last_id = 0
13 while True:
14 with connection.cursor() as cur:
15 cur.execute(
16 """
17 with batch as (
18 select id
19 from customers_customer
20 where id > %s
21 order by id
22 limit %s
23 ),
24 updated as (
25 update customers_customer c
26 set primary_email = lower(c.email)
27 from batch
28 where c.id = batch.id
29 and c.primary_email is null
30 )
31 select max(id) from batch
32 """,
33 [last_id, BATCH],
34 )
35 batch_max = cur.fetchone()[0]
36 if batch_max is None:
37 break
38 last_id = batch_max
39 self.stdout.write(f"backfilled up to id {last_id}")
40 time.sleep(PAUSE)

The cursor advances with max(id) from the batch. Taking the last row of UPDATE ... RETURNING instead is a common bug, because Postgres doesn't promise that RETURNING follows the ORDER BY of the CTE that fed it. The data-modifying CTE runs even though the final select doesn't reference it, since Postgres always executes those exactly once. Each loop iteration commits on its own under Django's autocommit, so stopping the command loses at most one batch, and a restart just walks the ids again and skips rows that are already filled. Watch replication lag while it runs and raise PAUSE if replicas fall behind.

05

switch, then enforce not null without a long lock

Once the backfill is done, the next release reads primary_email and writes both columns, and only after it's fully rolled out do you enforce the constraint. ALTER COLUMN ... SET NOT NULL on its own scans the whole table while holding ACCESS EXCLUSIVE, which on a big table is exactly the lock-queue outage from the start of this post. Since Postgres 12 it skips that scan if existing constraints already prove the column has no nulls, and a validated CHECK (primary_email IS NOT NULL) does. Validation takes only SHARE UPDATE EXCLUSIVE, so reads and writes continue during the scan:

python
1from django.db import migrations, models
2
3
4class Migration(migrations.Migration):
5 atomic = False
6 dependencies = [("customers", "0004_indexes")]
7
8 operations = [
9 migrations.SeparateDatabaseAndState(
10 database_operations=[
11 migrations.RunSQL(
12 "alter table customers_customer add constraint "
13 "customer_primary_email_nn check (primary_email is not null) not valid",
14 migrations.RunSQL.noop,
15 ),
16 migrations.RunSQL(
17 "alter table customers_customer validate constraint customer_primary_email_nn",
18 migrations.RunSQL.noop,
19 ),
20 migrations.RunSQL(
21 "alter table customers_customer alter column primary_email set not null",
22 "alter table customers_customer alter column primary_email drop not null",
23 ),
24 migrations.RunSQL(
25 "alter table customers_customer drop constraint customer_primary_email_nn",
26 migrations.RunSQL.noop,
27 ),
28 ],
29 state_operations=[
30 migrations.AlterField("customer", "primary_email", models.TextField()),
31 ],
32 ),
33 ]

atomic = False is deliberate. In one transaction, the brief ACCESS EXCLUSIVE lock from adding the constraint would be held through the validation scan, which defeats the point. Run non-atomically, each statement commits on its own. With client_min_messages = debug1 Postgres even tells you it skipped the scan: "existing constraints on column ... are sufficient to prove that it does not contain nulls". The CHECK is redundant afterwards, so the last step drops it.

Postgres 18 shortens this. It stores NOT NULL as a real constraint in pg_constraint and lets you add it as NOT VALID, so alter table customers_customer add constraint customer_primary_email_not_null not null primary_email not valid enforces the rule for new rows immediately, and a later validate constraint checks existing rows under SHARE UPDATE EXCLUSIVE and leaves the column properly NOT NULL, with no CHECK to clean up. Foreign keys follow the same pattern on any supported version: add them NOT VALID, then VALIDATE CONSTRAINT in a separate step.

06

contract much later

Removing the old column is the last step, and it should wait a full release or two after the switch, until nothing reads email anymore: not the app, not a forgotten cron job, not an analytics export. Do it in two stages. First tell Django the field is gone while the column stays, so the ORM stops selecting and inserting it:

python
1from django.db import migrations
2
3from customers.sync_trigger_sql import DROP_SYNC_TRIGGER, SYNC_TRIGGER
4
5
6class Migration(migrations.Migration):
7 dependencies = [("customers", "0005_primary_email_not_null")]
8
9 operations = [
10 migrations.RunSQL(DROP_SYNC_TRIGGER, SYNC_TRIGGER),
11 migrations.SeparateDatabaseAndState(
12 database_operations=[
13 migrations.RunSQL(
14 "alter table customers_customer alter column email drop not null",
15 migrations.RunSQL.noop,
16 ),
17 ],
18 state_operations=[migrations.RemoveField("customer", "email")],
19 ),
20 ]

The trigger SQL lives in a plain module next to the app, because Django refuses to load a non-migration file from the migrations package. The drop not null is the part people forget. Once the model no longer knows about email, new inserts leave it out, and a NOT NULL column without a default rejects every one of them. A release later, when the column is truly unused, a plain RemoveField or RunSQL drops it, and that takes the same short ACCESS EXCLUSIVE lock as any other ALTER, so the lock_timeout retry still applies.

That leaves you with five or six small migrations for one conceptual change, and that's fine. Squashing them later shortens test setup on fresh databases, but it does nothing for production safety, so do it only after every environment has applied the originals.

Thanks for reading.
Johnny Unar

Written by

Johnny Unar

Keep reading

Stop patching.Start building.

Tell us what's slowing you down. We'll bring coffee and opinions.