Adding a new column can look simple, but in production systems it carries real weight. Schema migrations must avoid downtime, avoid locking tables, and maintain compatibility for existing reads and writes. A careless ALTER TABLE can block queries, spike CPU, and degrade service.
The first step is deciding the column’s type and constraints. Choose the smallest type that fits your data. Add nullability unless you have an immediate value for every row. Backfill data in small batches to prevent transaction bloat.
In relational databases like PostgreSQL or MySQL, adding a new column with no default is often fast. Adding a column with a default value may rewrite the entire table depending on the engine and version. For distributed databases, schema changes may involve cluster-wide coordination, so apply them during low-traffic windows when possible.
Version your schema migrations. Deploy code that can handle both old and new schemas before you roll the migration. Write migrations so they can run forward and backward without destroying data. Store migration scripts in version control, tag them, and track which migrations are applied in each environment.