Adding a new column sounds simple. It isn’t, if you care about uptime, data integrity, and performance. Whether you work with PostgreSQL, MySQL, or a distributed database, the way you add a column affects your system now and in the future. Schema changes are one of the most common sources of production pain. Slow queries, locks, bad defaults — all of them can come from a poorly executed ALTER TABLE.
The first choice is blocking vs. non-blocking migrations. In small tables, ALTER TABLE ... ADD COLUMN is instant. On large datasets, it may hold locks for seconds, minutes, or hours. That can block writes and stall the app. Some engines like PostgreSQL allow adding a nullable column without a default almost instantly, then backfilling later in small batches. In MySQL, using ALGORITHM=INPLACE or INSTANT when supported can avoid downtime.
Defaults need special care. Setting a default on ADD COLUMN can rewrite the whole table. Instead, add the column as NULL, backfill it in batches, and then add the default constraint. This sequence avoids locking. If you need a NOT NULL constraint, apply it after all rows are populated.