Adding a new column is one of the most common yet critical schema changes. Done right, it’s invisible to the user and painless for the system. Done wrong, it can lock tables, drop performance, or trigger downtime. The key is to treat the new column as a controlled deployment, not an afterthought.
Define the column with the smallest data type that supports the intended use. Avoid NULL defaults unless necessary. If the column must be populated from existing data, backfill in batches to prevent long-running locks. For large datasets, write scripts that run incrementally and commit often.
When adding a new column to production, create it without constraints that demand immediate validation. Add those constraints later, once the data is ready. This avoids blocking writes and keeps the application responsive. Update application code to write to both the old and new fields if you plan a gradual migration. Only switch reads to the new column after the data is confirmed correct.