The migration ran clean until it hit the table that refused to grow. A new column was needed, but the schema was in production, the queries were hot, and downtime was not an option.
Adding a new column sounds simple. It’s not. Every database engine handles schema changes differently. On some, ALTER TABLE ADD COLUMN locks the entire table until it writes metadata. On others, it rewrites the table on disk. For high-traffic systems, either can mean lost requests, blocked writes, and angry pagers.
The first step is to know your database’s capabilities. PostgreSQL can add nullable columns instantly, but adding a NOT NULL column with a default value rewrites the table. MySQL behaves differently depending on its version and storage engine. Even cloud-hosted services may impose their own constraints.
The safest approach is to introduce the new column in phases.
- Add the column as nullable, with no default.
- Backfill the data in batches, avoiding heavy locks.
- Once complete, set constraints and defaults in a follow-up migration.
For systems with zero downtime requirements, run migrations during off-peak hours or use tools that perform online schema changes. In MySQL, gh-ost and pt-online-schema-change are proven options. In PostgreSQL, logical replication and rolling updates can achieve similar results.
Application code also needs coordination. New code should handle the presence or absence of the new column gracefully until the migration is fully deployed. Feature flags help control rollout and reduce risk.
Schema changes are inevitable in product growth. The key is to make them repeatable, observable, and reversible. With the right process, adding a new column becomes a controlled operation instead of a firefight.
See how hoop.dev can help you run safe, instant migrations and watch your new column go live in minutes.