Someone adds a NOT NULL DEFAULT true column to the users table. Runs fine on the local database with 50,000 rows. On production with 40 million rows, it holds an exclusive table lock for four minutes while it rewrites every row. Every read and write queues behind the lock. The application is effectively down.

This was supposed to be a non-breaking change.

Data migrations are different in production in one critical way: the database is live. There’s traffic hitting it, there are rows being inserted by old code while new code is deploying, and there are locks that affect every query - not just the one you’re running.

What Actually Locks

ALTER TABLE in most databases holds an exclusive lock for the duration of the operation. While the lock is held, all reads and writes on that table wait. For a table with millions of rows, adding a column with a default value means the database rewrites every row, which takes time proportional to the table size.

Postgres has gotten better at this in recent versions - simple column additions can be done without a full rewrite - but constraints, indexes, and type changes still lock.

The operations most likely to cause problems in production:

• Adding a NOT NULL column without a server-side default
• Adding a non-concurrent index (use CREATE INDEX CONCURRENTLY)
• Adding a foreign key constraint
• Changing a column’s type when an implicit cast isn’t available
• Running a large UPDATE that modifies most rows in the table

The Expand-Contract Pattern

The core idea: never require a state that both old and new code cannot handle simultaneously.

When you add a column, there’s a window where new code is deployed on some servers but old code still runs on others. If new code requires the column and old code doesn’t write to it, you’ll get NOT NULL violations during that window. If old code requires the column not to exist, new code can’t add it before the old code is gone.

The pattern has four phases, deployed across multiple releases:

Phase 1 - Expand (backward compatible): Add the new column as nullable, with no constraints that would reject rows from the old code. The old code ignores the new column. The new code writes to both old and new columns during the transition.

-- Safe: nullable, no constraints, instant on any table size
ALTER TABLE orders ADD COLUMN payment_method VARCHAR(50);

Phase 2 - Backfill: Populate the new column for existing rows. Do this in batches to avoid one massive UPDATE that locks the table and spikes I/O.

-- In batches, not all at once
UPDATE orders
SET payment_method = 'card'
WHERE id BETWEEN 1 AND 10000
  AND payment_method IS NULL;
-- Repeat for next batch, pause between batches

In application code or a migration script:

batch_size = 10_000
last_id = 0

while True:
    updated = db.execute("""
        UPDATE orders SET payment_method = 'card'
        WHERE id > %(last_id)s AND payment_method IS NULL
        LIMIT %(batch_size)s
        RETURNING id
    """, {'last_id': last_id, 'batch_size': batch_size})

    if not updated:
        break

    last_id = max(row['id'] for row in updated)
    time.sleep(0.1)  # breathing room for other queries

Phase 3 - Constrain: Once all rows have the value and old code is no longer deployed, add the NOT NULL constraint. In Postgres, you can do this without a full table lock:

-- Add constraint without validating existing rows (fast, no full scan)
ALTER TABLE orders
  ADD CONSTRAINT orders_payment_method_not_null
  CHECK (payment_method IS NOT NULL) NOT VALID;

-- Validate existing rows (scans table but only holds ShareUpdateExclusiveLock,
-- not an exclusive lock, so reads and writes continue)
ALTER TABLE orders VALIDATE CONSTRAINT orders_payment_method_not_null;

Phase 4 - Contract: Remove the old column or old code path once everything is confirmed stable.

Deploying Code and Migrations Together

A common mistake is deploying a code change and a schema migration in the same release. This creates a timing problem: if the migration runs before the code deploys, old code runs against the new schema. If the migration runs after, new code runs against the old schema.

The rule: schema changes must be backward compatible with the code that’s currently running, not just with the code you’re about to deploy.

In practice, this means:

• Deploy code that can handle both the old schema and the new schema
• Run the migration
• In a later release, clean up the dual-handling code

Example: adding a required status column to orders. Bad approach:

# Deploy and migration together - dangerous
# migration adds: ALTER TABLE orders ADD COLUMN status VARCHAR NOT NULL DEFAULT 'pending'
# code immediately uses: order.status

Safe approach:

# Deploy 1: add nullable column, code reads with fallback
# migration: ALTER TABLE orders ADD COLUMN status VARCHAR
def get_order_status(order):
    return order.status or 'pending'  # handles null during transition

# Deploy 2 (later): backfill, add constraint, remove fallback

When Rollback Is Impossible

A migration that adds a column can be rolled back (drop the column). A migration that drops a column cannot - the data is gone. A migration that changes data may not be reversible.

Think about rollback before running any migration on production:

• Can the previous code version run against the post-migration schema?
• Is the migration reversible without data loss?
• If the migration runs partially (the process dies midway), is the database in a valid state?

For genuinely irreversible migrations, run them during a planned maintenance window with a tested rollback procedure. “Tested” means you ran the rollback on a production-size database copy and confirmed it works.

The Short Decision Guide

Operation	Risk	Approach
Add nullable column	Low	Run directly, no special handling
Add NOT NULL column	High	Expand-contract pattern, 3+ deploys
Remove column	Medium	Remove code references first, then drop
Add index	High on large tables	CREATE INDEX CONCURRENTLY
Change column type	High	New column + backfill + switch
Large data backfill	Medium	Batched updates with pauses
Drop table	Irreversible	Maintenance window, verified backup

If your migration is a DROP or a type change that loses data, write the rollback first. If you can’t write the rollback, you need a maintenance window.