Adding a new column changes more than the shape of a table. It alters queries, indexes, constraints, and application code. In a production environment, the wrong migration can lock rows, stall services, or break deployments. The goal is to add the column with zero downtime, consistent data, and minimal risk.
Start with the migration plan. Determine the column type, nullability, and default. Avoid defaults that cause full-table rewrites on large datasets. For PostgreSQL, ADD COLUMN with a DEFAULT and NOT NULL can rewrite every row; instead, add it as nullable, backfill in batches, then enforce constraints. For MySQL, watch for table-level locks during schema changes and use tools like pt-online-schema-change or native online DDL if available.
Update application code to handle the new column before writing to it. Deploy in two stages: first read from the old structure and tolerate missing data, then write to the new column once it’s fully populated. This prevents null references and race conditions.
Indexing should be deferred until after the column is populated. Building an index on a live production table can impact performance. Test index creation in a staging environment that mirrors production scale.