Adding a new column in a production database can feel simple in theory but carries consequences for performance, reliability, and deploy safety. In relational databases like PostgreSQL or MySQL, the correct approach depends on the column’s default values, data type, and whether the schema change will lock the table. For large datasets, a naïve ALTER TABLE ADD COLUMN can lead to downtime.
Best practice starts with assessing the scope. Adding a nullable column with no default is usually instantaneous. Adding a column with a non-null default often forces a full table rewrite. In PostgreSQL, ADD COLUMN ... DEFAULT writes every row; in MySQL, behavior depends on the storage engine. The safe path is to add the column as nullable, backfill data in small batches, then enforce constraints with a separate migration.
For zero-downtime pipelines, tools like gh-ost or pt-online-schema-change in MySQL, and logical replication strategies in PostgreSQL, reduce risk. When adding columns to high-traffic tables, wrap the change in feature flags or deploy toggles so that application code does not query or write to the new field until it is fully online and populated.