When adding a new column to a production database, speed and safety are everything. Schema changes can lock tables, block writes, and cause downtime if done without care. The right approach depends on database type, data volume, and availability requirements.
In PostgreSQL, adding a nullable column without a default is fast because it only updates metadata. Adding a column with a default value writes to every row, which can be costly for large datasets. MySQL behaves differently. Some versions rewrite the entire table for any schema change. Newer releases with ALGORITHM=INPLACE or INSTANT can avoid that overhead. Always check the execution plan before running ALTER TABLE in production.
For zero-downtime migrations, tools like pg_repack, pg_online_schema_change, or gh-ost can stage changes in the background. Another option is to deploy in multiple steps: first add the new column as nullable, backfill data in small batches, then add constraints or defaults later. This staged method reduces locking and keeps the application responsive.