Adding a new column sounds simple. It isn’t. The cost of doing it wrong is downtime, data loss, and engineering hours you can’t get back. Schema changes are dangerous because the database is always in production. Every ALTER TABLE is an operation against live traffic.
The first decision is whether the new column can be added without locking. For small tables, the trade-off may be trivial. For large datasets, a blocking schema change can take minutes or hours, locking out reads and writes. Tools like pt-online-schema-change or native online DDL features can reduce or eliminate downtime.
When defining the new column, set defaults carefully. Backfilling millions of rows in one transaction is a common cause of table locks. Instead, consider adding the new column as nullable, then backfill in small batches before enforcing NOT NULL. This approach reduces lock contention and replication lag.
Indexes for new columns require the same caution. Create them online if your database supports it, or build asynchronously to avoid locking the table. Beware of implicit index creation when adding constraints—unique constraints, for example, generate indexes behind the scenes.