Adding a new column sounds simple. In reality, it’s one of the most disruptive schema changes a production database can face. Downtime, lock contention, and replication lag are waiting to ambush you if you get it wrong.
The safest approach depends on your database engine. In PostgreSQL, ALTER TABLE ADD COLUMN can be instant if you’re adding a nullable column with a default of NULL. In MySQL, older versions may lock the entire table unless you use ALGORITHM=INPLACE or an online schema change tool like gh-ost or pt-online-schema-change.
For large datasets, adding a column with a non-null default can rewrite the whole table. In high-traffic systems, this can spike CPU, fill I/O queues, and block writers. The way to avoid it is to create the column without a default, backfill it in controlled batches, and add the default and constraints afterward.
In distributed databases, such as CockroachDB or YugabyteDB, schema changes are transactional but still subject to coordination costs across nodes. Plan for them. Test your migration on a replica or staging environment with production-like load before touching live data.