Adding a new column should be simple, but production reality often proves otherwise. Schema changes can lock tables, impact query performance, or trigger failed deployments. The right approach reduces downtime, keeps data intact, and supports continuous delivery.
Start by defining the exact purpose of the new column. Decide its data type, default values, and nullability. For large tables, avoid blocking writes by using non-locking migration techniques. In PostgreSQL, adding a nullable column without a default is fast. Setting a default for existing rows later with an UPDATE avoids long locks. In MySQL, use ALGORITHM=INPLACE where supported. Always test in a staging environment with production-like data before execution.
Backfill operations deserve special care. Run them in batches to limit impact on replication and I/O. Monitor slow query logs during the migration. If the new column will be indexed, consider delaying index creation until data is populated to avoid excessive write load.