Adding a new column is common, but the wrong approach will lock rows, drop performance, or cause partial writes. Done right, it becomes a zero-downtime operation. Done wrong, it can grind production to a halt.
First, define the new column in your schema. In SQL databases like PostgreSQL or MySQL, use ALTER TABLE to add it. For large datasets, add the column without constraints or defaults to avoid table rewrites. Then backfill data in small batches. This prevents write locks and keeps replication lag under control.
In PostgreSQL, adding a column without a default is fast because it only updates metadata. If you must set a default, do it in a later step using an UPDATE statement in chunks, followed by altering the column to set the default for future inserts. In MySQL, consider using ONLINE or INPLACE algorithms when possible to avoid blocking writes.
In distributed databases, schema changes need to be coordinated across nodes. Use versioned migrations and staged rollouts so application code can handle both the old and new schema during transitions. Avoid implicit casting in migrations, as it can trigger unexpected schema rewrites.