Zero-Downtime Postgres Migrations

The migration that takes down production is almost never the one anyone was worried about. It’s rarely a DROP TABLE — everyone reviews those carefully. It’s the “safe-looking” one: adding a NOT NULL column with a default, or building an index on a busy table during business hours, run by someone who reasonably assumed that if the migration tool generated it automatically, it must be safe to run.

The uncomfortable truth is that “safe” in a migration tool’s eyes usually means “syntactically correct,” not “won’t block the table.” Postgres’s DDL operations acquire real locks, and several of the most common schema changes acquire a lock — ACCESS EXCLUSIVE — that blocks every read and write against the table for however long the operation takes. On a small table that’s milliseconds, invisible. On a table with tens of millions of rows and constant traffic, that’s a multi-minute production outage caused by a migration that looked like a one-line diff.

You’ll learn:

• Which common schema changes take a table-blocking lock, and which ones are already safe by default in modern Postgres
• How to add a column with a default value without rewriting the whole table
• The correct three-step pattern for adding a NOT NULL constraint without blocking writes
• How CREATE INDEX CONCURRENTLY avoids the standard index-build lock, and its real failure mode
• How to read pg_locks and pg_stat_activity to see exactly what a migration is about to block, before running it
• How to structure an Alembic or Django migration to apply this pattern safely by default

Table of Contents

1. Why Some DDL Blocks the Whole Table
2. Adding Columns Without Locking
3. Adding NOT NULL Without Blocking Writes
4. Building Indexes with CONCURRENTLY
5. Backfilling Large Tables Safely
6. Reading pg_locks Before You Run a Migration
7. Safe Migrations in Alembic and Django
8. Common Pitfalls

Why Some DDL Blocks the Whole Table

Every ALTER TABLE in Postgres acquires a lock on the table for the duration of the operation, and the lock level determines what else can happen concurrently. The strictest, ACCESS EXCLUSIVE, blocks literally everything else — reads, writes, even other DDL — until it’s released. Whether a given ALTER TABLE needs that strictest lock, or can get away with something much lighter, depends specifically on whether Postgres has to rewrite the table’s existing rows to perform the change.

Operations that only touch metadata — adding a nullable column, adding a check constraint marked NOT VALID, dropping a column — take ACCESS EXCLUSIVE too, but only briefly, because there’s no data to rewrite; the lock is held just long enough to update the catalog. Operations that have to rewrite every existing row — changing a column’s type in a way that isn’t binary-compatible, adding a column with a volatile default in older Postgres versions, adding certain kinds of constraints without NOT VALID — hold that same ACCESS EXCLUSIVE lock for as long as the rewrite takes, which scales directly with table size and can run into minutes or longer on a large table under real load.

The practical rule: any DDL statement whose duration scales with row count is a real risk on a busy table, and any statement whose duration is constant regardless of row count (metadata-only changes) is close to always safe. Knowing which category a given ALTER TABLE falls into, before running it, is the entire skill this post is about.

A quick reference for the operations that come up most often:

The pattern across every “no” in the last two columns is the same: Postgres has a lighter lock mode available for almost every operation that scales with table size, and the only cost of using it is that the operation itself takes longer wall-clock time to finish — a tradeoff that’s almost always worth making on a table anyone else is actively using.

Adding Columns Without Locking

Adding a nullable column with no default has always been a fast, metadata-only operation in Postgres — this one was never actually dangerous:

ALTER TABLE orders ADD COLUMN discount_code TEXT;

Adding a column with a default value used to be the classic trap: before Postgres 11, this rewrote every existing row to populate the default, taking ACCESS EXCLUSIVE for the whole rewrite. Since Postgres 11, a non-volatile default (a literal, not now() or random()) is stored purely as metadata and applied lazily on read for old rows — no table rewrite, no long lock, regardless of table size:

-- Safe and fast on Postgres 11+, any table size
ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending';

The exception that still matters: a volatile default — DEFAULT now(), DEFAULT gen_random_uuid(), or anything that has to compute a genuinely different value per row — still forces a full table rewrite even on modern Postgres, because a lazily-applied default only works when every row can share the exact same stored value. For anything that needs a per-row computed value, add the column nullable with no default, backfill in batches (below), then add the default for future rows only.

Adding NOT NULL Without Blocking Writes

A plain ALTER TABLE orders ALTER COLUMN status SET NOT NULL has to scan the entire table to verify no existing row violates the new constraint, holding ACCESS EXCLUSIVE for that full scan. On a large, busy table, that scan competing with real traffic is exactly the kind of migration that causes an outage. The safe pattern splits this into three steps that never hold a long exclusive lock:

-- Step 1: add a CHECK constraint as NOT VALID — instant, metadata-only
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

-- Step 2: validate it separately — takes a lock, but only ROW SHARE,
-- which permits concurrent reads and writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

-- Step 3 (Postgres 12+): now SET NOT NULL is instant, because
-- the planner can prove it from the already-validated constraint
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
DROP CONSTRAINT orders_status_not_null;  -- optional cleanup

Step 1 is instant because NOT VALID tells Postgres to enforce the constraint only on rows written from this point forward, without checking existing rows. Step 2 does the expensive full-table check, but under a much lighter lock than ACCESS EXCLUSIVE — concurrent reads and writes proceed normally while it runs, at the cost of some extra I/O contention during the validation itself. Step 3, on Postgres 12 and later, is a genuine metadata-only operation because the planner can prove the column is already non-null from the validated constraint, without a second table scan. This three-step pattern is the single highest-value trick in this entire post, and it generalizes to any CHECK or foreign key constraint, not just NOT NULL.

Building Indexes with CONCURRENTLY

A plain CREATE INDEX takes a SHARE lock that blocks writes (though not reads) to the table for the entire build — on a large table, that can mean minutes of blocked INSERT/UPDATE/DELETE statements, which is functionally an outage for any write-heavy service. CREATE INDEX CONCURRENTLY avoids that by building the index in multiple passes, releasing its lock between them so writes can proceed throughout:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

The real cost is worth knowing precisely: the build takes noticeably longer (often two to three times a plain CREATE INDEX on the same table) and, critically, it cannot run inside a transaction block — most migration frameworks wrap every migration in a transaction by default, so running CONCURRENTLY correctly usually means explicitly telling the framework to skip transactional wrapping for that specific migration.

The other real failure mode: if the build is interrupted — a connection drop, a cancelled statement, a server restart — it can leave behind an invalid index, visible in \d tablename as INVALID, which still consumes disk space and still gets maintained on every write, but is never used for queries. Postgres doesn’t clean this up automatically:

-- Check for invalid indexes across the database
SELECT indexrelid::regclass, indrelid::regclass
FROM pg_index WHERE NOT indisvalid;

-- Drop and rebuild
DROP INDEX CONCURRENTLY idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

DROP INDEX CONCURRENTLY exists for exactly this cleanup case and, like the create variant, avoids the blocking lock a plain DROP INDEX would take. Once the index is safely built, confirm it’s actually being used the way you expect with EXPLAIN ANALYZE — the guide to reading Postgres query plans covers exactly how to verify a new index changed the plan shape rather than assuming a faster query proves it.

Backfilling Large Tables Safely

A single UPDATE orders SET status = 'pending' WHERE status IS NULL against millions of rows does the right thing logically but the wrong thing operationally — it’s one giant transaction, holding row locks on every touched row until commit, generating a burst of WAL traffic, and risking a long-running transaction that blocks autovacuum from cleaning up dead tuples elsewhere in the database for its entire duration. Backfills belong in small, committed batches instead:

import time

BATCH_SIZE = 5000

while True:
    with connection.cursor() as cursor:
        cursor.execute("""
            UPDATE orders SET status = 'pending'
            WHERE id IN (
                SELECT id FROM orders
                WHERE status IS NULL
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            )
        """, [BATCH_SIZE])
        rows_updated = cursor.rowcount
    if rows_updated == 0:
        break
    time.sleep(0.1)  # let replication and autovacuum keep up

FOR UPDATE SKIP LOCKED means concurrent backfill batches (or ongoing application writes touching the same rows) don’t block each other — a batch simply skips rows another process already has locked and picks them up on a later pass. The short sleep between batches is deliberate: it gives replication lag, autovacuum, and connection pool contention room to recover between bursts, rather than hammering the table continuously the way one giant transaction implicitly does anyway, just spread out instead of concentrated.

Reading pg_locks Before You Run a Migration

A surprising number of postgres add column lock incidents trace back to nobody checking what was already running against the table before the migration started — a long-running report query or an unrelated batch job holding a lock the migration then queues behind, turning what should have been an instant metadata change into a multi-minute wait with everything else queued up behind it in turn. Rather than guessing whether a migration is safe, Postgres exposes exactly what’s holding and waiting on locks right now:

SELECT
    pg_locks.pid,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.query,
    pg_stat_activity.state
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_locks.relation = 'orders'::regclass;

granted = false rows are the ones actually stuck waiting — that’s the live signal that something is currently blocked, not a hypothetical. Running this query in a second session immediately after starting a migration, while it’s mid-flight, is the fastest way to confirm in real time whether it acquired the lock level you expected or a stricter one, and whether anything else is queued up behind it. Combine this with pg_stat_activity.query_start to see how long a blocking statement has already been running, which is often the deciding factor in whether to let a migration finish or cancel it before it causes real damage. pg_blocking_pids(pid) is worth knowing too — pass it the PID of a stuck query and it returns exactly which other sessions are blocking it, which turns “something is blocking my migration” into a specific, actionable PID to investigate or terminate rather than a guess.

For a genuinely risky migration on a large production table, it’s worth setting a conservative lock_timeout for the session running it, so a migration that can’t acquire its lock quickly fails loudly and immediately instead of queuing silently behind other traffic and blocking everything that queues up after it in turn:

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT ...;

A migration that fails fast with a clear lock not available error is vastly preferable to one that sits queued for minutes while every other query on that table queues up behind it too — a failed migration can simply be retried; a queue of blocked production queries is already an incident by the time anyone notices.

Safe Migrations in Alembic and Django

Alembic doesn’t apply these patterns automatically — a straightforward op.create_index(...) in an autogenerated migration produces a plain, blocking CREATE INDEX by default. Writing an alembic safe migration for an index means adding both postgresql_concurrently=True and marking the migration explicitly non-transactional:

from alembic import op

def upgrade():
    op.execute("COMMIT")  # end the implicit transaction Alembic opened
    op.create_index(
        "idx_orders_status", "orders", ["status"],
        postgresql_concurrently=True,
    )

Django’s migration framework has a dedicated, purpose-built flag for exactly this case — atomic = False on the migration class, combined with AddIndexConcurrently from django.contrib.postgres.operations:

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="idx_orders_status"),
        ),
    ]

Both frameworks also support the constraint-splitting pattern from earlier as three explicit, separate migration steps rather than a single autogenerated SET NOT NULL — deliberately break the migration into the NOT VALID / VALIDATE / SET NOT NULL sequence in your migration files instead of trusting either tool’s default single-step output for a NOT NULL change on an existing populated table.

Common Pitfalls

Mistake: trusting an autogenerated migration on a large existing table without reading what it actually does. Migration tools generate correct SQL, not necessarily safe SQL for a table already carrying production traffic. Solution: read every autogenerated ALTER TABLE against the “does this rewrite the table” question before running it against production.

Mistake: running CREATE INDEX CONCURRENTLY inside a transaction block. Postgres rejects this outright — it cannot run concurrently within a transaction. Solution: explicitly break transactional wrapping for that migration step in whichever framework you’re using.

Mistake: leaving an invalid index behind after an interrupted concurrent build. It silently costs disk space and write overhead while contributing nothing to query performance. Solution: periodically check pg_index.indisvalid and clean up any invalid indexes found.

Mistake: backfilling millions of rows in one transaction. This holds locks and generates WAL pressure for the transaction’s entire duration, and blocks autovacuum in the meantime. Solution: batch the backfill with LIMIT and FOR UPDATE SKIP LOCKED, with a short pause between batches.

Mistake: adding a volatile-default column and assuming Postgres 11’s fast-default optimization covers it. DEFAULT now() or similar still forces a full table rewrite, unlike a plain literal default. Solution: add the column nullable, backfill in batches, then apply the default for future rows only.

Wrapping Up

Zero-downtime migrations aren’t a separate discipline from ordinary schema changes — they’re ordinary schema changes with one extra habit attached: knowing, before running anything, whether the operation’s duration scales with table size, and choosing the pattern (fast default, NOT VALID/VALIDATE, CONCURRENTLY, batched backfill) that keeps it constant instead. Every pattern in this post exists because Postgres already gives you a lighter-weight path for almost every dangerous-looking change — the skill is knowing it’s there and reaching for it by default on any table too large or too busy to risk the naive version.

Before your next migration touches a production table, do you know which lock it’s about to take, or are you finding out from an incident?