database-migration-patterns

Zero-downtime schema changes use the expand-contract pattern: add new schema elements without removing old ones, migrate data in stages, then clean up deprecated columns. This approach keeps the database available throughout by separating reads from writes and avoiding long-running locks.

Safe column rename uses the expand-contract strategy: create a new column, backfill data, update application code to write to both columns, verify correctness, then drop the old column. Alembic migrations enforce this sequence with versioned scripts and rollback plans.

Add NOT NULL in stages: first make the column NOT NULL at the application level by validating before insert, then add a CHECK constraint without validation, backfill any nulls, and finally enforce the database constraint. This avoids table locks during the migration.

Create indexes safely using CONCURRENTLY in PostgreSQL, which builds the index without blocking writes. Alembic templates guide this approach alongside backfill strategies that minimize locking and maintain availability during index creation on production data.

Data migrations require separate Alembic scripts, staged backfills to avoid locks, validation checks to confirm correctness, and tested rollback plans. Split large migrations into smaller batches and run them during low-traffic windows to minimize impact.

Versioned migrations with Alembic track every schema change, enable reproducible deployments across environments, enforce best practices like expand-contract and safe indexing, and provide rollback capabilities if a production change fails or causes issues.