Adding a new column to a database sounds simple. It rarely is. Schema changes can lock tables, cascade errors, and stall deployments. The risk grows with data size, transaction volume, and uptime requirements. Choosing the right strategy is more than a syntax choice — it is the difference between a safe migration and a system outage.
First, decide if the new column is nullable or has a default value. Non-nullable columns with no default will force the database to rewrite the entire table. This can lead to downtime. Add columns as nullable, then backfill data in controlled batches. Once every row has a value, enforce NOT NULL.
Second, think about indexes. Adding an indexed column on creation is costly. It is better to create the column, populate it, then build the index separately. This allows you to monitor for performance issues and reduce lock times.
In PostgreSQL, ALTER TABLE ... ADD COLUMN is usually fast when the column is nullable without a default. In MySQL, the impact depends on the storage engine and version — recent versions support instant column addition for some cases. For very large tables, use an online schema change tool like gh-ost or pt-online-schema-change.