database-migration

A reliable database migration script requires explicit UP and DOWN logic to ensure reversibility. You should always test the rollback path in staging before applying schema changes to production.

The expand-and-contract pattern handles backward-incompatible transformations by splitting them into separate deployments. It expands the schema first, migrates data, and then contracts by removing old columns.

Safe data backfill in large tables requires batched updates to minimize locking and maintain performance. You should validate the batch update process in staging before executing it against production data.

Adding indexes without locking requires following zero-downtime practices that avoid unsafe locking operations. You should generate migration scripts that explicitly validate lock duration and performance impact.

You should avoid dropping a column immediately after schema changes because it breaks backward compatibility. Use expand-and-contract approaches to delay the drop until all dependent application code is updated.

The best way to validate a database migration is using a checklist-driven approach to verify zero-downtime compliance and rollback behavior. You must test the explicit UP and DOWN logic in staging first.