Adding a new column to a production database is simple in theory. In practice, it can break queries, slow responses, or block deployments if done carelessly. The right approach keeps zero downtime and full data integrity.
First, define the new column with exact types and constraints. Avoid nullable defaults unless intentional. Consider if backfilling data is needed before exposing the column to application code.
Second, use an additive migration. Add the column in one step. Backfill in small batches to avoid table locks. Then update the application to use the new column only after the data is complete.
Third, index only when necessary. Adding an index too early can block writes and stall deployments. If indexing is required, add it after the backfill with an online index build if your database supports it.