Adding a new column is simple in theory. One line of SQL. But in production systems with real traffic, the details matter. The wrong approach locks rows, slows writes, and risks downtime. The right approach keeps the system online, the latency low, and the rollout clean.
Start with schema design. Decide the column’s data type, default value, and constraints. Avoid nullable columns if possible—they complicate indexing and increase storage overhead. Choose names that are explicit and consistent with existing patterns. Review the impact of the new column on queries, indexes, and replication.
In PostgreSQL, ALTER TABLE ADD COLUMN is fast for metadata but slow if it needs to rewrite the table. Adding a column with a non-null default writes to every row. In MySQL, ALTER TABLE often rebuilds the table unless you use algorithms like INPLACE or INSTANT (available in newer versions). For large data sets, this difference is decisive.
For zero-downtime changes, feature-flag the column usage. First, deploy code that can handle both the old and new schema. Add the column without defaults. Backfill in small batches to avoid locking. Once backfilled, change constraints or defaults in a separate migration. Finally, enable the feature in code and clean up old paths.