Adding a new column looks trivial, but in real systems, it can break deploys, block API responses, and lock tables under load. The right approach depends on scale, downtime tolerance, and migration tools.
Start by defining the new column in your schema change plan. For non-nullable columns, add them as nullable first, backfill in batches, and then apply constraints. This prevents long locks and avoids blocking writes. For high-traffic systems, use online schema change tools like gh-ost or pt-online-schema-change to reduce risk.
When altering large tables, always test the new column migration on a snapshot of production data. Measure execution time and watch for deadlocks, replication lag, or storage spikes. Never assume the migration you run locally will behave the same under real workloads.
When adding a new column to APIs, deploy in two phases. First, add the column in the database. Second, update code to read from and write to the column. This supports rolling deploys and avoids null reference issues.