Adding a new column sounds simple, but in production systems it can trigger downtime, lock tables, or break integrations. Databases handle schema changes differently, and understanding those differences is the line between a smooth deployment and an outage.
In SQL databases like PostgreSQL, adding a nullable column without a default is fast. The metadata changes in place. But adding a column with a default value can rewrite the entire table, blocking reads and writes until it finishes. MySQL and MariaDB may behave similarly, but under certain storage engines, the operation locks the table outright. In distributed databases, schema changes can cause version drift across nodes unless rolled out in phases.
For large tables, always measure the impact before deployment. Use staging environments with production-scale data. Check query plans and replication lag. In PostgreSQL, consider the two-step approach: first add the column as nullable with no default, then backfill in small batches, and finally set the default and constraints. In MySQL, leverage ALGORITHM=INPLACE where supported, but test to ensure the database does not revert to COPY mode.