The migration ran clean until one table broke. The error was simple: the app needed a new column.
Adding a new column sounds trivial. It can be dangerous if done on a live system with high traffic. The wrong approach locks rows, blocks writes, and stalls the database. The right approach keeps queries fast and safe.
First, decide how the column will be used. Define its data type, nullability, and default value. Avoid TEXT or BLOB unless required. Use a schema migration tool that supports transactional DDL when possible. For massive tables, consider adding the column without defaults, then backfilling in batches to prevent long locks.
Second, handle application-level changes. Update ORM models, serialization logic, and API contracts. If the column is part of critical paths, make those changes backward-compatible before the migration. Deploy code that reads both the old and new schema until the migration completes.
Third, test the migration in a staging environment with production-like data. Measure execution time. Simulate live traffic during migration. Watch query plans to ensure indexes stay efficient.
Finally, monitor in real time as it runs in production. Keep rollback scripts ready. A well-planned new column does not crash the system or block updates.
Want to run schema migrations like this without the risk? See it live in minutes at hoop.dev.