Adding a new column is one of the most common schema changes, but in production it can be one of the most dangerous. The wrong approach locks tables, slows queries, or causes downtime. The right approach is fast, consistent, and does not interrupt service.
Start by defining the exact name, type, and constraints. Be explicit. Decide if the new column should allow null values, have a default, or be indexed. Changes without defaults may require backfilling. Backfilling millions of rows during peak hours is a mistake. Stage it.
For relational databases like PostgreSQL, adding a nullable column with no default is near-instant. Adding a column with a default will rewrite the table by default and block writes. To avoid this, first add the column as nullable, then update it in batches, and only after that set the default and constraints. This pattern avoids downtime while ensuring data integrity.
In MySQL, similar principles apply, but engine settings and storage formats matter. Newer versions improve online DDL operations, but not for every case. Test the change on a staging clone before touching production. Measure the impact of ALTER TABLE on actual data sizes and indexes.