The migration script failed at midnight. Logs showed a missing column in the production database. The fix was simple: add a new column. The challenge was doing it without downtime, data loss, or breaking dependent code.
Adding a new column sounds trivial until constraints, indexes, nullability, and default values come into play. In relational databases, schema changes are operations on live systems. A poorly planned ALTER TABLE can lock rows for minutes, block critical writes, or trigger cascading failures.
When adding a new column, the safest route starts with understanding the database engine. MySQL, PostgreSQL, and SQL Server handle schema changes differently. PostgreSQL can often add a nullable column instantly because it stores metadata without rewriting the table. MySQL’s behavior depends on storage engine and version. With large tables, use ONLINE or INPLACE algorithms if available to avoid table copies.
Decide if the new column should allow nulls. If not, add it as nullable first, backfill data in small batches, then alter it to NOT NULL. For columns with a default value, consider setting the default after backfill to avoid full table writes during the initial add.