Adding a new column seems simple, but the wrong approach can lock tables, block writes, or cause downtime. Modern systems handle billions of rows. A careless migration can stall an entire application. The right method is precise, fast, and safe.
First, define the purpose of the new column. Decide on data type, nullability, and default values. These choices affect storage, query performance, and index strategies. Avoid generic types when possible—match the column definition to the exact data it will store.
Next, choose a migration strategy. For smaller datasets, a direct ALTER TABLE may be fine. For large production tables, use an online schema change tool such as pt-online-schema-change or gh-ost. These tools create a copy of the table, apply the change, and swap it in with minimal locking.
Consider backward compatibility. Deploy the new column in a way that old code can still run. This usually means adding the column unused at first, updating the application to write to it, then rolling out the reads. Staged rollouts let you validate performance and correctness before making the column critical.