Adding a new column is one of the most common database changes, but it can break deploys, slow queries, or cause downtime if done wrong. In production systems, the risk is real. The key is to understand how your database engine processes schema changes and to choose the safest, fastest approach.
A new column in SQL changes the table’s definition by updating the schema metadata. In some databases, this is instant for nullable columns with defaults. In others, it rewrites the entire table. On large datasets, that can mean hours of blocking.
For PostgreSQL, adding a new nullable column without a default is fast. Adding a column with a default in older versions locks the table while rewriting all rows. Using DEFAULT NULL avoids that rewrite. Then, backfill data in batches with controlled transactions. Starting with PostgreSQL 11, adding DEFAULT with NOT NULL can be processed without a rewrite, but test it in staging.
MySQL supports ALTER TABLE ADD COLUMN, but many storage engines still copy the table unless you use ALGORITHM=INPLACE or ALGORITHM=INSTANT (available for certain column types in MySQL 8.0+). Even with instant operation, index changes or constraints can force a copy.