Adding a new column sounds simple. It is not. In production systems with live traffic, schema changes can lock tables, trigger long-running migrations, or break dependent services. Execution speed and zero downtime are critical.
A new column in SQL changes the table definition at the database level. In MySQL and PostgreSQL, ALTER TABLE can be instant for nullable columns without defaults, but will rewrite the full table if you add defaults or constraints. Large datasets magnify this cost. Always assess the size of the table and the locking behavior of your engine before you run the command.
For MySQL, use ALTER TABLE ... ADD COLUMN with care. If available, enable ALGORITHM=INSTANT or ALGORITHM=INPLACE to avoid a full copy. On supported versions of PostgreSQL, adding a NULL column without a default is metadata-only and near instant. Adding a default requires a table rewrite unless you use the newer implicit default approach.
Backward compatibility matters. Add the new column in one deployment, backfill data in batches, then enable defaults and constraints in a later step. This pattern reduces lock time. Use feature flags or conditional logic to ensure the application can handle both old and new schemas during the rollout.