The migration broke at 2:14 a.m. The logs showed a missing column. You knew it meant blocked deploys, delayed releases, and a backlog swelling by the hour. Adding a new column should be simple, but in production systems, one wrong move can lock tables, drop indexes, or leave the database in a half-broken state. Precision matters.
When you create a new column, the first choice is scope. Will it be nullable to avoid downtime, or non-nullable for strict data integrity? For large tables, a blocking ALTER TABLE can freeze queries. Many teams use phased rollouts: add the column as nullable, backfill in controlled batches, then enforce constraints after verification.
Naming conventions are not just style. A clear, consistent column name reduces query complexity and prevents collisions with reserved keywords. Always check your ORM mappings and serialization code; a new column at the database layer must flow cleanly through the app layer without silent drops or type mismatches.
Data type selection is permanent in practice. Changing it later is risky and resource-intensive. Choose the smallest type that fits current and foreseeable values. For timestamps, always store in UTC. For strings, lock down encoding early to avoid corruption in multi-language systems.