Adding a new column seems simple, but the wrong approach will slow queries, break deployments, or cause downtime. In production systems, schema changes must be precise, atomic, and reversible. The right steps reduce risk and keep migrations fast even with tables holding millions of rows.
Start by defining the exact column name, data type, and default value. This avoids relying on implicit database behavior that can change between versions. Use nullable columns when possible to skip expensive table rewrites. For large datasets, break the operation into two phases: first add the column as nullable, then backfill in batches, and finally set constraints.
Always run the migration in a staging environment with realistic data volumes. Measure query plans before and after. For indexed columns, create the index in a separate transaction to avoid locks that block writes. If you use PostgreSQL, consider CONCURRENTLY when adding indexes, but watch for its restrictions on transaction usage.