Overview
A schema migration changes the structure or constraints of a database used by a running application. The risky part is often not the SQL itself, but the interaction between old application code, new code and a database that may be serving traffic throughout deployment.
Use an expand-and-contract sequence
For a breaking column rename, do not immediately rename the column while old code is still running. First add the new column, deploy code that can read and write both forms, backfill data, verify consistency, then switch reads to the new column. Remove the old column only in a later release after no deployed code depends on it.
Keep migrations repeatable and observable
Each migration should have a unique version and a recorded execution status. Avoid relying on an operator to remember which statements already ran. For long backfills, process bounded batches and record progress.
Understand DDL behavior
Transactional guarantees and online schema-change options vary by database engine, version and operation. Some DDL statements cause implicit commits. Do not assume a ROLLBACK can undo every schema change. Review the exact MariaDB or MySQL version documentation before production changes.
Deploy compatible application code
During a rolling deployment, old and new application instances may coexist. New code should tolerate the previous schema until all instances are updated. Avoid deploying code that requires a column before the migration is complete.
Test on realistic data
Measure lock duration, disk requirements and runtime on a staging copy. Large table changes can affect availability. Take a verified backup and prepare a recovery plan; a backup that has never been restored is not a tested recovery process.
Checklist
- Use versioned migration files.
- Prefer expand, backfill, switch, contract.
- Check DDL behavior for the installed server version.
- Make backfills batched and resumable.
- Deploy code compatible with both schema states.
- Test restore and rollback procedures.