The migration finished at 3:17 a.m., but the data layout was wrong. The fix was simple: add a new column. The challenge was doing it without slowing the database to a crawl.
A new column in a production table can be dangerous. Schema changes lock tables. Locks block queries. Blocked queries cascade into downtime. Before changing the schema, you need a plan that accounts for scale, load, and replication.
First, check the database engine’s documentation on adding columns. In PostgreSQL, ALTER TABLE ADD COLUMN runs fast when default values are NULL. But setting a default non-null value rewrites the table, which can take minutes or hours on large datasets. In MySQL, online DDL operations can help, but not every storage engine supports them. Always test in a staging environment with production-sized data before touching live tables.
If the column needs a default, consider adding it as NULL, backfilling data in batches, and then applying a constraint later. This avoids locking large tables for long periods and keeps the deployment safe under heavy traffic. Track progress with monitoring tools and make sure replicas apply changes without lag.