Database Migrations Without Breaking a Rolling Deploy

A rolling deploy often has version N and version N+1 serving requests at the same time. If version N+1 renames users.name to display_name before old instances leave, version N may start failing immediately. The deploy system can roll back containers, but the database schema remains changed.

The safer pattern is expand, migrate, contract. First add a compatible structure. Then deploy code that can use both old and new forms. Backfill existing rows. Only after old code is gone do you enforce the new constraint and remove the old column.

Suppose the app currently reads and writes users.name. Avoid a one-step rename. Add display_name as nullable; deploy code that writes both columns and reads COALESCE(display_name, name); backfill rows in batches; change reads to the new column; later stop writing name; finally drop it in a separate release.

ALTER TABLE users ADD COLUMN display_name text;

-- Transitional read while old and new versions coexist:
SELECT id, COALESCE(display_name, name) AS display_name
FROM users WHERE id = $1;

The dual-write period matters. A backfill alone cannot capture new records written by old replicas after the backfill finishes. Decide which column is authoritative during the transition and verify that both application versions behave correctly.

Even a logically compatible migration can affect availability if it waits for or holds a table lock. Large rewrites and index creation need special planning. PostgreSQL's CREATE INDEX CONCURRENTLY permits writes during much of index building, but it takes longer, has restrictions, and cannot run inside a transaction block. Test the exact SQL against production-like volume.

Set a lock timeout for migrations so a blocked change fails visibly instead of waiting behind a long transaction while requests pile up. Backfill in small batches with progress metrics rather than one enormous transaction.

• Version N works with the expanded schema.
• Version N+1 works while old rows still have null in the new column.
• The migration can fail without leaving the app unusable.
• A rollback to version N remains possible after the new code ships.
• Backfill progress, lock waits, and query errors are observable.

PostgreSQL can leave an invalid index behind if CREATE INDEX CONCURRENTLY fails. The migration runner may report failure while the catalog still contains that index name. A retry that blindly issues the same SQL may then fail for a different reason. Inspect index validity, drop or repair the invalid index using the documented procedure, and retry deliberately.

This illustrates why migration tooling needs more than a “run all files” button. Record which migration ran, whether it completed, and whether the database is in a transitional state. Alert on a failed migration before deploying code that assumes the new index or column exists.

For a large backfill, select a bounded primary-key range per batch, commit each batch, and store progress. If the worker stops, it resumes at the last committed range. Avoid assuming that a fast migration on a ten-row staging table predicts locking and I/O on a production table with millions of rows.

PostgreSQL `ALTER TABLE` details schema-change behavior. PostgreSQL `CREATE INDEX` documents concurrent builds and limitations.