Schema changes are simple in theory: add, migrate, backfill, deploy. In production, they can grind traffic, stall queues, and break downstream code. Adding a new column to a database is not just an ALTER TABLE—it’s a shift in how your system moves data at scale. Mistakes here ripple fast.
The process starts with definition. Choose the right data type, nullability, and default values. Each choice has cost: storage, lock time, CPU. In PostgreSQL, adding a nullable column without a default is near-instant, but adding with a default rewrites the table. In MySQL, older storage engines lock during ALTER, blocking reads and writes until completion.
Name the column with precision. Columns are forever in business logic. Avoid abbreviations and overloaded terms. If the new column holds computed or derived data, assess whether it belongs in the table or in a view.
Plan the migration in steps. First, introduce the column with minimal locking. Second, deploy application code that starts writing to it without reading from it. Third, backfill the data in small batches to avoid saturating I/O. Finally, deploy the read path once the column is fully populated.