A schema change on a live database is not just a code change. A careless migration can take a lock that makes every query on a table wait, and for a busy table that is a small outage. Zero-downtime migrations are mostly about avoiding that lock.
First principles
Two facts govern every migration on a live database.
- Schema changes take locks. Most Postgres schema changes lock the table. Some are quick metadata updates. Others must scan or rewrite every row, and they hold the lock until they finish. The lock lasts as long as the work.
- Code and schema do not change at the same instant. During a rolling deploy, old and new application code run side by side against one database. Whatever schema exists at any moment has to work for both.
From the first fact: keep every lock short. That means metadata-only changes, work split into batches, and operations Postgres can run without blocking writes. From the second: never make a change the running code cannot handle. That means adding before using, and removing only after nothing uses it. Every section below applies one of those two rules.
The migration that locks the table
Adding a column with a volatile default, or adding a constraint that forces the database to scan every row, holds a lock for the whole operation. On a table with millions of rows, every request that touches it queues behind that lock.
Add columns as nullable first
A nullable column with no default is a fast, metadata-only change. It is the safe first step.
# step one: add the column as nullable
status = models.CharField(max_length=20, null=True)Backfill in batches
Fill the new column in a separate data migration, in batches. One giant UPDATE locks every row it touches until it commits. Many small updates each hold their locks briefly, and they can be paused or resumed.
Then enforce the constraint
Once every row has a value, a later migration flips the column to null=False. No row can fail the check now, because there is nothing left to fix. Postgres still reads every row to confirm that, and it holds the lock while it reads. On a very large table, first add a CHECK (status IS NOT NULL) constraint as NOT VALID and validate it in a separate step. Postgres 12 and later can then use that constraint to skip the scan.
Build indexes concurrently
A plain CREATE INDEX locks writes to the table while it runs. Postgres can build an index without that lock, and Django exposes this directly. The migration must opt out of running inside a transaction, which is what atomic = False does.
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models
class Migration(migrations.Migration):
atomic = False
operations = [
AddIndexConcurrently(
"order",
models.Index(fields=["status"], name="order_status_idx"),
),
]Renames and drops need two deploys
Never rename or drop a column in the same release that stops using it. Old application code is still running during a rolling deploy. Ship code that works with both shapes, let it settle, then change the schema. This is the expand-then-contract pattern, and it applies to every destructive change.
Decouple deploy from migrate
Run migrations as their own deliberate step, not as a side effect of a container starting. Then you control the sequence: expand the schema, deploy the code, migrate the data, and only later contract.
The checklist before I run one
- Does this take a lock, and for how long?
- Will the currently deployed code still work after it runs?
- Is any backfill batched rather than one large statement?
- Can it be rolled back, or at least rolled forward safely?