Adding a new column sounds simple—until you hit the real constraints. Production data. Zero downtime. Backward compatibility. When you create a new column in a relational database like PostgreSQL, MySQL, or SQL Server, the cost is often not the code itself, but the migration path.
The first question: blocking or non-blocking migration? In PostgreSQL, adding a column without a default is fast, but adding a column with a default value may rewrite the entire table. At scale, that’s hours of locked writes. The right pattern is often to add the column without defaults, backfill in batches, then enforce constraints in a later migration.
For MySQL, especially with older storage engines, altering big tables can block queries. Online schema change tools like pt-online-schema-change or gh-ost exist for exactly this reason. They copy and swap tables to avoid downtime, but require care with triggers, replication lag, and failover.
Data type and nullability matter. A nullable column is easier to add than a NOT NULL column with a default. If you enforce NOT NULL, always populate and verify first. Naming also counts—avoid renames in the same change; introduce the new column, migrate references, then drop the old one only when certain.