Adding a new column sounds simple. In production, it is not. Schema migrations can lock tables, slow queries, and trigger unwanted downtime. The right approach depends on the size of the dataset, the database engine, and the requirements for zero-downtime deployment.
In PostgreSQL, adding a nullable column is fast because it doesn’t rewrite the table. Adding a column with a default value can be costly; the database fills every row with that value. MySQL behaves differently. Some operations are instant with modern storage engines, while others require a full table copy. Understanding these differences is critical when working at scale.
When designing a new column, define its data type with precision. Avoid generic types that waste space or cause conversion overhead later. Consider indexing, but weigh the write performance cost. If the column will be part of a hot query path, plan the index strategy before rollout.