Adding a new column in a production database is never just a schema change. It’s a transaction against reality. Done poorly, it can lock your rows, break application logic, or bottleneck deployments. Done well, it extends your data model without downtime or risk.
Start with the migration. Use ALTER TABLE for minimal changes but run it in off-peak hours or behind feature flags. For large datasets, create the column as nullable, then backfill in batches to avoid table-wide locks. Always track the operation with metrics and alerting—silent failures here become systemic defects later.
Name the new column with precision. Avoid vague terms; choose identifiers that make sense for both humans and machines. Define the correct data type from the start. Changing it later usually requires a reload or copy, which is costly. Add constraints only after the data is in place, or your insert operations will stall during the backfill.