Database Migration Plan Template

Expand-Contract is a three-phase approach for making schema changes without downtime. In the Expand phase, you add the new structure (column, table) without removing the old one. In the Migrate phase, you backfill data and update the application to use both old and new. In the Contract phase, you remove the old structure after all reads and writes use the new one. This pattern is longer but avoids any moment where the database and application are out of sync.

Run the migration on a staging database with production-scale data. If you do not have a production-scale staging database, test on a subset and extrapolate. A backfill UPDATE on 1M rows at 5,000 per batch with 100ms pauses takes approximately 4 minutes. Scale linearly for larger tables, then add 50% buffer for production load.

Use tools like `pt-online-schema-change` (MySQL) or `pg_repack` (Postgres) when modifying columns on tables with more than 1M rows. Standard ALTER TABLE statements on large tables can lock writes for seconds to minutes. Online DDL tools create a shadow copy, replay changes, and swap tables atomically.

This is the hardest case. If the forward migration added a column and new application code has written values to it, dropping the column loses that data. The safest approach is to keep the column and deploy a fix rather than rolling back the schema. Only execute a schema rollback if the new column has not received any production writes.

PMs should understand the timeline impact and risk level but not the technical implementation. Use the risk assessment section of this template during sprint planning to flag migrations that may delay feature delivery or require off-hours execution. The PM's role is to ensure the business understands the trade-off between speed and safety.