The migration failed on the last step. The schema was fine, the indexes were fine, but the app choked—because the new column wasn’t where the code expected it to be.
Adding a new column sounds trivial, but in production environments, it is a sharp edge. Database engines handle schema changes differently. Some block writes while altering tables. Others allow concurrent operations but strain I/O. If the size and type of your new column are wrong, you can trigger table rewrites, lock contention, or cascading data shifts across replicas.
The first question is always: nullable or not? Making a new column non-nullable with no default forces the engine to populate every row. On large tables, that’s a full table lock. In high-traffic systems, this can stall transactions, spike latency, or even cause failovers. Adding with NULL and backfilling in controlled batches avoids downtime. It also lets you index after the data is in place, reducing pressure on the system.
Data type drives both performance and cost. Choosing a wider type than necessary increases storage and slows scans. Choose the smallest type that holds the required range and precision. For text, ensure consistent collation to avoid hidden mismatches with existing indexes or constraints.