Adding a new column to a database is a small change with outsized consequences. Schema migrations can be seamless or catastrophic. Lock contention, downtime, and stale reads are waiting for anyone who treats it as a checklist task. The key is precision.
Start by choosing the right migration strategy. For small tables, an immediate ALTER TABLE ... ADD COLUMN is often fine. For large or heavily used tables, use an online migration tool or a two-step process: add the column as nullable with no default, backfill in batches, then apply constraints if needed. This approach avoids long locks and keeps the system responsive.
Define the column type with care. Avoid types that force rewrites on backfill, and align defaults with application behavior to prevent unexpected null handling. If indexing is required, delay the index creation until after data backfill to avoid compounding write pressure during the migration.