The migration crashed at 02:14. The logs pointed to one cause: a missing new column.
Adding a new column seems simple. In reality, it can block deploys, lock rows, or wreck a production database if handled without precision. A poorly executed ALTER TABLE on a large dataset can trigger hours of downtime. Before making changes, you need a clear plan for schema evolution that keeps the application online and data consistent.
The safest way to add a new column is to treat it as a multi-step operation. First, create the column with a default of NULL and no constraints. This ensures the ALTER TABLE completes fast, avoiding table rewrites. Next, backfill data in controlled batches using an id-based range query or a job queue. Only after the backfill is complete should you add constraints, indexes, or defaults that require a table scan.
Different databases handle new column additions differently. PostgreSQL can add a nullable column instantly, but adding one with a non-null default rewrites the full table. MySQL behaves similarly, with some variations depending on storage engine. Understanding these behaviors is critical to avoid unexpected locking or performance degradation.