Zero-Downtime Database Migrations: Locks, Backfills and Expand/Contract

A schema change has zero downtime when users see no errors and no stalls while it runs. Two things break that: locks that block queries, and application code that does not match the schema at some point during the rollout. Migration tools solve neither. Planning does.

The math

Old and new code run at the same time. During a rolling deploy, pods with version N and version N+1 serve traffic together. Every schema state along the way has to work with both versions. If a migration renames a column, version N breaks the moment it runs.

A blocking lock stalls everything behind it. In PostgreSQL, an ALTER TABLE that needs an ACCESS EXCLUSIVE lock first waits for every open transaction that has touched that table, even one that only read from it. While it waits, every new query on the table queues behind it. So the stall lasts as long as the longest running transaction plus the time the ALTER holds the lock. Multiply that by your request rate to get the number of stuck requests.

Backfills take time proportional to rows. Measure the update rate per batch on a production-sized copy, divide the row count by it, and you know whether the backfill takes minutes or days. Large single transactions also delay replicas, so watch replication lag during the run.

PostgreSQL rules

Always set a lock timeout for migration sessions, so a blocked ALTER fails quickly instead of stalling the table. Then retry:

SET lock_timeout = '3s';

Adding a column. A nullable column, or one with a non-volatile default, is a metadata-only change since PostgreSQL 11. A volatile default such as clock_timestamp() or gen_random_uuid() rewrites the whole table.

Indexes. Use CREATE INDEX CONCURRENTLY. It does not block writes, but it cannot run inside a transaction block and takes longer. If it fails, it leaves an INVALID index behind, and IF NOT EXISTS will happily skip it on the next run. Drop it and retry:

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx ON orders (customer_id);

-- after a failure
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

NOT NULL on an existing column. SET NOT NULL scans the whole table under an exclusive lock. Since PostgreSQL 12 the scan is skipped if a valid CHECK constraint already proves the column has no nulls. Add that constraint without validation, validate it separately (this takes a weaker lock that allows reads and writes), then set NOT NULL:

ALTER TABLE users ADD CONSTRAINT users_full_name_not_null
  CHECK (full_name IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_full_name_not_null;
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_full_name_not_null;

Foreign keys. Same pattern: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID, then VALIDATE CONSTRAINT.

Changing a column type usually rewrites the table under an exclusive lock. Do it as a new column with a backfill instead.

MySQL rules

InnoDB supports online DDL for many operations, and ALGORITHM=INSTANT for adding columns since MySQL 8.0.12. State the algorithm and lock level explicitly, so MySQL returns an error instead of silently falling back to a blocking table copy:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN status VARCHAR(32) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX orders_status_idx (status), ALGORITHM=INPLACE, LOCK=NONE;

Online DDL still takes a short metadata lock at the start and the end, and that lock waits behind long transactions just like in PostgreSQL. lock_wait_timeout limits the wait. On replicas, an in-place ALTER starts only after it has finished on the primary, which shows up as replication lag for about the same duration.

For large tables or changes online DDL cannot do, use a tool that copies rows into a shadow table in chunks and swaps tables at the end:

• gh-ost reads the binary log instead of using triggers, needs row-based binlog, can be throttled and paused, and does not support foreign keys.
• pt-online-schema-change uses triggers and has options for handling foreign keys.

touch /tmp/ghost.postpone
gh-ost --host=replica.db.example.com --database=my_app --table=orders \
  --user=gh-ost --ask-pass \
  --alter="ADD COLUMN status VARCHAR(32) NULL" \
  --max-load=Threads_running=25 --critical-load=Threads_running=100 \
  --chunk-size=1000 --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

The table swap waits until you delete the flag file.

Expand and contract

Renames and type changes follow the same sequence. Example: replacing users.name with users.full_name.

1. Expand. Add full_name as a nullable column. Old code ignores it.
2. Dual write. Deploy code that writes both columns and still reads name.
3. Backfill existing rows in batches.
4. Switch reads. Deploy code that reads full_name and still writes both.
5. Stop writing the old column. Deploy code that only uses full_name.
6. Contract. Drop name once no running version uses it and the rollback window has passed.

Until the last step, the application can be rolled back at any point without touching the schema.

A batched backfill in PostgreSQL. Primary key ranges keep each batch a short transaction on an index range (COMMIT inside DO needs PostgreSQL 11 and no outer transaction):

DO $$
DECLARE
  batch_start bigint := 0;
  max_id bigint := (SELECT coalesce(max(id), 0) FROM users);
BEGIN
  WHILE batch_start <= max_id LOOP
    UPDATE users SET full_name = name
    WHERE id >= batch_start AND id < batch_start + 10000 AND full_name IS NULL;
    COMMIT;
    PERFORM pg_sleep(0.1);
    batch_start := batch_start + 10000;
  END LOOP;
END $$;

Rows created after the backfill starts are covered by the dual write from step 2. Tune the batch size and pause based on replication lag and database load.

In the pipeline

• Run expand migrations before deploying the code that needs them.
• Ship contract migrations in a later release.
• Tag migrations that cannot run in a transaction, such as CREATE INDEX CONCURRENTLY (runInTransaction:false in Liquibase).
• Test each migration against a production-sized copy and record its duration.

Checklist

• Every schema state works with both the old and the new application version.
• lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL) set for every migration session.
• Indexes created concurrently or online; constraints added NOT VALID and validated separately.
• Type changes and renames done with expand and contract.
• Backfills in small batches, with replication lag watched.
• Destructive steps only after the rollback window has passed.