schema-migration

Zero-downtime database migrations use the expand-contract pattern to apply phased schema changes without locking tables. You add nullable columns, backfill data separately, switch application writes, and remove old columns during the contract phase.

Idempotent SQL and Alembic migrations require timestamped filenames and UP/DOWN scripts that execute safely multiple times. You must separate DDL schema changes from DML data operations and include explicit rollback paths for every migration.

The expand-contract pattern for database migrations avoids downtime by executing four phases: expand to add the new column, migrate to backfill values, transition application writes to the new schema, and contract to remove the old column.

Yes, this approach supports index creation and data backfills on relational production databases by separating DDL and DML into distinct migration files. You run backfills as separate data migrations to avoid locking issues during large schema updates.

Separating DDL and DML into different migration files prevents locking conflicts and ensures safe rollback paths. DDL schema changes and DML data backfills execute independently, making the zero-downtime expand and migrate phases reliable and reversible.

Merged database migration files are immutable; modifying them breaks idempotency and rollback consistency. You must never edit merged migrations directly on main branches or perform destructive operations without following the expand-contract workflow.