Adding a new column is a routine task, but in production systems, there is no margin for error. A single schema change can lock tables, block writes, or cause deployment rollbacks. Understanding the right way to add a new column is the difference between a smooth release and hours of downtime.
When you add a new column to a table, first decide on its type, constraints, and defaults. Avoid using non-null with no default on large datasets. This forces a full table rewrite. Use NULL initially, then backfill values in batches, then set the constraint in a later migration. This reduces lock time and minimizes the impact on live traffic.
For large tables, use online schema change tools such as pt-online-schema-change for MySQL or gh-ost. In PostgreSQL, adding a nullable column without a default is nearly instant. But adding with a default in a single operation rewrites the data. Split these steps to keep performance steady.
Always coordinate the new column with application code changes. Deploy the schema update before the code that writes to it. Allow time to monitor for replication lag and performance changes. Once stable, update read paths to use the new column.