The migration failed because the schema didn’t match. You stare at the error log. The query was simple—just add a new column. But the database has rules, and every change has a cost.
Adding a new column is common, but mistakes here spill into production. Before execution, confirm the exact column name, data type, default value, and nullability. Use ALTER TABLE with precision. Double-check indexes; decide whether the new field needs an index now or only when query patterns demand it.
In PostgreSQL, adding a new column with a default on a large table can lock writes. In MySQL, certain ALTER operations rebuild the entire table, which can block reads and writes. Understand the engine you’re using. Avoid downtime by using concurrent operations or zero-downtime migration tools. Always run changes in a staging environment first.
When designing a new column, think about storage and query performance. NUMERIC and VARCHAR behave differently in terms of space and speed. Avoid unnecessary wide columns. Choose the smallest type that will hold every possible value. Set constraints to enforce data integrity at the database level; don’t trust application code alone.