Adding a new column in a production database is simple to describe but dangerous to execute. The wrong ALTER TABLE can lock writes, block reads, or bring down critical services. The goal is zero downtime. The method depends on your database engine, the size of your dataset, and the load on your system.
In PostgreSQL, adding a new column with a default value and NOT NULL constraint will rewrite the whole table. That’s not cheap. Instead, add the column without constraints, backfill the data in controlled batches, then set constraints once complete. MySQL and MariaDB behave differently, but the principle is the same: avoid operations that trigger full table copies on large datasets in production hours.
If you work with migrations, keep them small and reversible. Wrap all schema changes, including new column additions, in tested migration scripts. Use feature flags or conditional code to handle the transition period when both old and new schema versions are in use. Monitor query plans before and after the change. Index the new column only if it’s required, as index creation can hold locks as well.