When you add a new column to a production table, your goal is zero downtime, no data loss, and minimal locking. Start by defining the schema change in a migration file you can version control. Always check the database engine’s docs for how it handles ALTER TABLE operations. In MySQL, adding a new column might lock the full table depending on the data type and position. In PostgreSQL, adding a nullable column with a default NULL is instant, but a non-null default can rewrite the table.
Choose data types carefully. Every new column has a storage cost and performance impact. Keep indexes lean. Avoid creating indexes on a new column until you’ve measured query patterns. Adding both at once can double the migration cost.
If the new column needs to be backfilled with existing data, batch the updates to avoid write amplification and cache churn. Use incremental scripts with limits and delays between runs. In distributed systems, ensure your application code can handle the column’s absence during deploy rollouts. Feature-flag the use of the new column in queries until all nodes agree on schema.