Schema changes are among the riskiest routine operations in software. A single ALTER TABLE can lock a busy table, break an older application version still running during a deploy or quietly lose data. Most incidents follow a few familiar patterns, and a handful of rules prevents the majority of them.
The details depend on the database engine and version, because locking behaviour and online DDL support differ between PostgreSQL, MySQL, SQL Server and others. Always check the documentation for your exact version.
1. Keep every change in versioned migration files
Write schema changes as migration scripts stored in version control and applied by a migration tool, such as Flyway, Liquibase or the migration system of your framework. Each environment then reaches the same schema through the same steps, and you can see exactly what changed and when. Avoid manual changes on production.
2. Prefer additive changes
Adding a nullable column, a new table or a new index is usually low risk because existing code ignores it. Renaming or dropping a column, changing a type or adding a constraint can break running code. When possible, express a change as a sequence of additive steps.
3. Use expand and contract for breaking changes
To rename a column safely, add the new column (expand), write to both columns, backfill old rows, switch reads to the new column, stop writing the old one and finally drop it (contract). Each step is deployed separately, so old and new application versions can run side by side during a rollout.
4. Know which statements take heavy locks
Some operations require an exclusive lock and may rewrite the whole table, blocking reads or writes for the duration. Examples vary by engine, but type changes and some constraint additions are common culprits. Even a fast statement can cause an outage if it waits for a lock behind a long-running query while new queries queue behind it. Setting a short lock timeout for migration sessions lets the change fail quickly and retry instead of blocking traffic.
5. Build indexes without blocking writes
Creating an index on a large table can take a long time. Many databases offer a non-blocking variant, such as CREATE INDEX CONCURRENTLY in PostgreSQL or online index operations in MySQL's InnoDB and in some SQL Server editions. These options usually take longer and have caveats, such as not running inside a transaction, but they keep the application available.
6. Be careful with defaults and NOT NULL
Adding a column with a default value used to rewrite the whole table in several engines; newer versions often handle constant defaults as a metadata-only change. Adding a NOT NULL constraint requires checking every row. A common safe pattern is to add the column as nullable, backfill it, then add the constraint, using a "not valid then validate" approach where the engine supports it.
7. Backfill in batches
Updating millions of rows in one statement creates long transactions, large undo or write-ahead logs and replication lag. Backfill in small batches, for example a few thousand rows at a time by primary key range, with a short pause between batches. Make the backfill restartable so it can resume after an interruption.
8. Keep code and schema backward compatible
During a deploy, old and new application instances often run at the same time. The schema must work for both. Deploy schema changes that the new code needs before the code, and remove things the old code used only after all old instances are gone.
9. Test on realistic data
A migration that takes a second on a small development database can take an hour on production. Test against a copy or a representative sample with similar size and distribution, and measure duration and lock behaviour. Review the generated SQL rather than trusting a framework to produce efficient statements.
10. Have a backup and a rollback path
Confirm that a recent, restorable backup exists before significant changes. Decide in advance how you would undo the change: a reverse migration, a feature flag or restoring a table from backup. Destructive steps, such as dropping a column, should come last and only after the data is confirmed unused.
Key takeaways
- Version every change and prefer additive, multi-step migrations.
- Understand locking for your engine and use lock timeouts and online index builds.
- Backfill in batches and keep the schema compatible with old and new code.
- Test on realistic data and prepare a rollback before you start.