The migration had stalled. The schema change everyone avoided was now blocking the release. The fix: add a new column.
Adding a new column to a database sounds simple. In production, it can cause downtime, lock tables, or break code paths you forgot existed. The right approach depends on table size, database engine, and traffic patterns.
For PostgreSQL, ALTER TABLE ... ADD COLUMN is often instant if you provide a default of NULL. But adding a non-null column with a default forces a rewrite. On large tables, that rewrite can lock writes for minutes or hours. The safe path is to add the column as nullable, backfill in small batches, then enforce constraints in a separate migration.
MySQL behaves differently. In older versions, adding columns can trigger a full table copy. Newer releases with ALGORITHM=INSTANT reduce this cost, but not every operation is supported. Test the exact statement against a replica before running it in production.
In distributed databases like CockroachDB, ADD COLUMN is asynchronous but still needs monitoring. Schema changes must propagate across nodes. Even online operations can cause brief spikes in latency. Watch your metrics and roll out during low-traffic windows.
The code side matters as much as the database. Deploy application changes that can handle both old and new schemas before backfilling. Avoid assuming the column exists until you confirm it in production. Backfills should be idempotent and resumable.
The safest pattern:
- Add the new column as nullable.
- Deploy code that reads from both schemas gracefully.
- Backfill in controlled, monitored batches.
- Add indexes only after the data is populated.
- Apply constraints last.
This phased approach prevents downtime and lets you roll back without losing data. The goal is to ship the new column without your users noticing anything changed—except that everything still works.
You can test this workflow end-to-end without touching your production database. See it live in minutes at hoop.dev.