database-migrations-sql-migrations

Use the expand-contract pattern: first add new columns or tables in a backward-compatible way, then backfill data in batches, and finally drop old structures after code deployment. Create indexes with CREATE INDEX CONCURRENTLY to avoid table locks.

Add the column as nullable, backfill values in batches, then add a CHECK constraint with NOT VALID and validate it separately. In PostgreSQL 12+, this avoids a full table rewrite and long exclusive locks.

Flyway uses versioned plain SQL files applied in order, while Alembic generates Python migration scripts with upgrade and downgrade functions from SQLAlchemy models. Both integrate with version control and support rollback paths.

Process rows in batches of around 10,000 using keyset pagination on an indexed cursor column, committing after each batch and adding short sleeps between iterations. For very large tables, partition the key range and migrate partitions in parallel with multiple workers.

Take a snapshot or backup before applying changes, run the migration inside a transaction with validation checks, and keep a tested down migration script for each version. If post-migration validation fails, restore from the snapshot and delete the version record from the schema_migrations table.

Avoid it when the change requires rewriting the entire table with incompatible data types, when dual-write synchronization cannot capture all write paths, or when the database version lacks concurrent index and constraint validation features. In those cases, schedule a maintenance window instead.