Adding a new column sounds simple, but in production systems, the details matter. Schema changes can block writes, lock tables, spike latency, and trigger downtime. Choosing the right approach means faster delivery and fewer operations headaches.
A new column in SQL can be added with ALTER TABLE, but that default path can fail under load. Online schema migration tools like pt-online-schema-change or gh-ost rewrite tables in the background, letting you add columns without locking. Some databases, like PostgreSQL, can add certain types of columns instantly if they have default null values. For high-traffic applications, you should avoid operations that rewrite the entire table unless absolutely required.
Planning matters. Decide the column type, nullability, and default early. Columns with defaults that require writing to every row can slow down migrations. Break up changes into phases: first add the new column as nullable, then backfill data in batches, then apply constraints and indexes. This reduces risk and avoids single large blocking steps.
For analytical workloads, you may trade some migration complexity for the benefits of denormalization—adding a new column to store precalculated values improves query speed at the cost of larger storage.