Adding a new column is simple in theory, but in production it can feel like surgery. Done wrong, it locks tables, drops queries, and leaves your API gasping. The right approach is zero-downtime. That means understanding how your database engine handles schema changes and planning each step with precision.
In PostgreSQL, ALTER TABLE ADD COLUMN is usually fast if the column is nullable or has a default of NULL. But adding a NOT NULL constraint or a default with a computed value can rewrite every row and cause long locks. In MySQL, an ADD COLUMN can trigger a table copy depending on the storage engine, column order, and version. Modern versions with ALGORITHM=INSTANT can add columns instantly, but only under specific conditions.
For mission-critical databases, roll out new columns in stages. First, add the column as nullable without defaults. Then backfill it in small batches with background jobs or scheduled updates. Only when the data is complete should you apply NOT NULL constraints or indexes.