Adding a new column in a production database should be simple, but in high-traffic systems the cost of getting it wrong is brutal. Schema changes block writes, lock tables, and cause downtime if handled without care. The right approach is predictable and safe, even under load.
Start by defining the new column in a way that avoids table locks. In PostgreSQL, adding a column with a default value rewrites the table—which can be a disaster on large datasets. Instead, first add the column as NULL. Then backfill data in small, controlled batches. Finally, add the NOT NULL constraint and default once the backfill is complete.
In MySQL, use ALGORITHM=INPLACE or ALGORITHM=INSTANT when possible, and verify that the storage engine and column type support it. Monitor replication lag, since schema changes on primary nodes can cascade poorly to replicas.
Treat adding a new column as part of an atomic deployment. Coordinate your application code to handle both old and new schemas during the rollout. Double-check query performance after the change; even a simple column can shift execution plans.