Adding a new column to a database is simple in theory, dangerous in practice. One wrong move and your service stalls. Locks, schema drift, and inconsistent data appear without warning. Speed matters, but so does safety.
Start by knowing your schema state. Query your database’s metadata tables to confirm existing columns. For PostgreSQL, check information_schema.columns. For MySQL, use SHOW COLUMNS. Avoid assuming you know the schema; inspect it.
When creating the new column, decide if it will be nullable. Setting NOT NULL on a large table can lock writes. In production, add the column as nullable first. Backfill in smaller batches, then set constraints once data is complete.
For zero-downtime changes, use transactional DDL only if supported and tested. Otherwise, apply a phased migration:
- Add the new column, no default.
- Backfill in controlled batches.
- Add indexes only after backfill, to prevent heavy write contention.
- Add constraints last.
Be aware of how ORMs interact with schema changes. Regenerate models to include the new column, but avoid deploying code that writes to it before the column exists in production. The deployment order matters more than the code itself.