Adding a new column in a database should be fast, reliable, and free of side effects. Yet in production systems, the process can trigger locks, break queries, or stall deployments. The solution depends on choosing the right migration strategy and understanding how your schema changes interact with live traffic.
A new column in SQL can be defined with an ALTER TABLE statement. On small tables, this is trivial. On large, high-traffic tables, it can block reads and writes. Engineers avoid downtime by using online schema change tools, background migrations, or staging systems where the column exists but is unused until deployment.
When adding a new column with a default value, the database may rewrite the entire table. This is fast for some engines, slow for others. PostgreSQL, for example, can now add a column with a constant default in constant time, but MySQL historically required a full table rebuild unless specific configurations or tools were used.
It is best to break the change into discrete steps. First, add the new column as nullable. Then backfill the data in batches. Finally, set the column to NOT NULL if required. This avoids long locks and preserves performance.