The migration failed halfway. The logs showed a single cause: a missing new column.
Adding a new column is one of the most common schema changes in production systems. Done wrong, it can trigger downtime, lock critical tables, or cause application errors. Done right, it’s seamless, safe, and reversible. The key is precision at every step.
Start by defining the new column in your migration script. Choose the correct data type and constraints. If the column is non-nullable, provide a sensible default or backfill values before enforcing constraints. In large datasets, adding a column with a default value can lock the table for longer than your maintenance window allows. Mitigate that by adding the column as nullable first, then populating it in controlled batches. Only after the data is in place should you alter the column to be non-nullable.
Indexing a new column should be its own deliberate step. An index on a large table can be expensive to build. In PostgreSQL, use CREATE INDEX CONCURRENTLY to avoid write locks. In MySQL, check whether your storage engine supports online indexing. Always test on a staging environment with realistic data to measure the performance impact.