Adding a new column in a live system feels simple on paper. In production, it can grind services to a halt if done wrong. Databases grow, queries scale, and schema changes ripple through every dependent process. A poorly handled migration can spike latency, lock tables, or cause dropped requests.
The first choice is the migration strategy. For small tables, an ALTER TABLE ADD COLUMN may be enough. On large or high-traffic tables, that same command can trigger downtime. Online schema changes, phased rollouts, or creating the column in a shadow table are safer paths. Techniques like pt-online-schema-change for MySQL or gh-ost reduce lock risk by creating a new table with the column, backfilling data in chunks, then swapping it in.
A new column should not exist in isolation. Update the write paths first so incoming data fills the column from day one. Then migrate historical data in batches to avoid performance cliffs. Add indexes only after backfill is complete to prevent massive index build times during high load.
Plan for nullability. If the new column is NOT NULL, set a sensible default or backfill before adding constraints. Avoid schema drift between environments by applying the same migration scripts in dev, staging, and production with automated verification.