The query came in fast: add a new column. No delays, no excuses. The database had to be ready before the next deploy.
A new column sounds simple. It rarely is. Schema changes can block deploys, cause downtime, and create race conditions between code and data. The right approach depends on table size, index usage, and whether you need backfill.
First, define the column in a migration script. Always make changes explicit: column name, type, default value if any, and null constraints. Avoid shortcuts in production. Even one wrong default can lock a table for seconds or minutes under load.
For large tables, use online schema change tools or phased rollouts. Tools like pt-online-schema-change or native ALTER with ALGORITHM=INPLACE can help avoid full table locks. Test these changes on a staging environment with production-size data, not just dev fixtures.
If the new column holds non-null critical data, deploy it in two steps:
- Create the column nullable.
- Backfill in small batches, using job queues or background workers.
- Apply the NOT NULL constraint after backfill completes.
This minimizes downtime and avoids replication lag in read replicas. Monitor query performance during each step. Adding an index on the new column can be done later if needed, but never inside the same migration for high-traffic systems.
Version your migrations. Make sure application code reads from both old and new schemas during the transition. Only remove old paths once the deploy is stable and you’ve validated the new column across all environments.
A new column is more than a schema update. It’s a live change to shared state in production. Treat it with discipline, test at scale, and automate where possible.
See how to do zero-downtime schema migrations and spin up production-ready databases in minutes at hoop.dev.