How do you implement database migrations safely in production environments?
Database migration safety prevents data loss and downtime during schema changes. Principles: every change is a versioned migration file, migrations are tested on production-size data before deployment, destructive changes are multi-phase, and rollback procedures are documented. Safe migration patterns: adding a column (always safe, use DEFAULT for non-null columns), adding an index (use CONCURRENTLY option in PostgreSQL to avoid locking: CREATE INDEX CONCURRENTLY), and adding a table (always safe). Dangerous operations requiring caution: dropping a column (first stop writing in application, deploy, wait for all instances to update, then drop column in next release), renaming a column (create new column, backfill, update application to use new column, then drop old column in next release), changing column type (add new column with desired type, backfill with CAST, update application, drop old column), and adding NOT NULL constraint (first ensure all existing rows have values, then add constraint). Multi-phase migration pattern: Phase 1: add new column/table alongside old. Phase 2: deploy application writing to both old and new. Phase 3: backfill new from old. Phase 4: deploy application reading from new. Phase 5: stop writing to old. Phase 6: drop old (next release cycle). Zero-downtime deployment: use online migration tools (pgosc for PostgreSQL, gh-ost for MySQL) for table alterations on large tables, implement blue-green database deployments for major schema changes, and use views to abstract schema changes from application code. Testing: test migrations on a copy of production data (including volume), measure migration execution time, verify application compatibility with both pre and post-migration schema, and test rollback procedures. Monitoring during migration: track table lock duration, replication lag, query performance, and application error rates. Have rollback ready if metrics exceed thresholds.