Adding a new column is one of the most common schema changes, yet it still breaks production when handled poorly. The key is to make the change without locking writes, losing data, or blocking other migrations.
First, determine the exact data type and default value. Use the smallest type that supports the required range. Avoid TEXT or BLOB unless absolutely necessary. Defaults should match real usage patterns; avoid NULL unless it has semantic meaning.
Second, plan the migration. In PostgreSQL or MySQL, ALTER TABLE ... ADD COLUMN can lock the table. For large datasets, consider online migration tools like pg_online_schema_change, gh-ost, or pt-online-schema-change. Break the change into two steps: add the column without constraints, then backfill in batches, then add indexes or constraints later.
Third, update the application code. Deploy the schema change before code that writes to the new column. This prevents runtime errors when old code runs against a new schema. For distributed systems, handle backward and forward compatibility—code should tolerate the column being absent or unset until the migration is complete.