Adding a new column is one of the most common database changes. It should be simple. Often, it isn’t. Poor planning can lock tables, block writes, and stall deploys. Done right, it’s fast, safe, and repeatable.
A new column can store fresh data, support new features, or optimize queries. But adding it without downtime takes planning. On large datasets, ALTER TABLE commands can block for minutes or hours. For high-traffic systems, that’s unacceptable.
The safest approach is an additive migration. First, create the column as nullable or with a default that does not rewrite the entire table. For Postgres, avoid defaults that cause a full table rewrite; initialize values in a separate step. For MySQL, consider ALGORITHM=INPLACE or INSTANT options where available.
Once the new column exists, backfill data in small batches to reduce lock contention. Use indexed updates only if necessary. Track progress in logs or metrics so you can resume if interrupted.