Adding a new column is simple in theory. In production, it can be high-stakes. A single schema change can block writes, lock tables, or introduce silent bugs if not executed with precision. Understanding how to add, backfill, and deploy a column without downtime is critical to maintaining uptime and data integrity.
A new column begins with the migration definition. In SQL, ALTER TABLE ... ADD COLUMN is the baseline, but the exact syntax and constraints depend on the database. Postgres allows concurrent operations in some cases, but adding defaults or not-null constraints may lock the table. MySQL can rebuild the entire table if certain conditions are met. Always check the DDL’s impact before running it on production.
The safest approach is to add the column with a null default and run a separate process to backfill values in small batches. This avoids large locks and reduces replication lag. Once backfilled, apply the not-null constraint in its own migration. For indexed columns, add indexes only after the bulk writes complete to minimize load.