Adding a new column is one of the most common schema changes, but it can also break production if handled carelessly. The process seems simple—define the column, assign its type, and set defaults—but in a system handling real traffic and heavy writes, the details matter.
Start with a migration that is reversible. In SQL, use ALTER TABLE to add the new column, but avoid adding constraints or non-null defaults in the same command for large tables. This prevents locks that can halt queries. Instead, add the column nullable, backfill in batches, and then add constraints after the data is safe.
If you use Postgres, adding a nullable column with a default that is constant is fast because it only updates metadata, not every row. MySQL changes vary depending on the storage engine, so test on replica data first. Always benchmark the migration against realistic data sizes.
When adding a column to a highly active table, coordinate deployments so that application code ignores or gracefully handles the absence of the field until the migration completes. Deploy schema changes first, then the code that reads or writes the new column. This two-step deployment avoids runtime errors from mismatched schema and application expectations.