data-migration

Zero-downtime migration uses a five-step process: add the new column, dual-write to old and new columns, backfill historical data, switch reads to the new column, then drop the old column. This keeps the application running throughout the change.

Backfill by running an UPDATE statement that copies values from the old column to the new one where the new column is NULL, for example: UPDATE users SET email_new = email WHERE email_new IS NULL. Run backfills in batches for large tables.

Dual-write means the application writes new data to both the old and new columns simultaneously during migration. This keeps both schemas in sync while historical data is backfilled, allowing a safe cutover of reads.

Rollback by reversing each migration step in order: switch reads back to the old column, stop dual-writes, and drop the new column if needed. Planning rollback procedures before executing the migration is essential for safe recovery.

Always test migrations in staging before production, especially for schema changes on large tables, data transformations, and cross-system moves. Staging tests validate backfill correctness, performance impact, and rollback procedures.