Schema migration

A schema migration is a versioned change to the structure of a database, such as creating a table, adding a column or building an index. Each migration is a script stored with the application code. A migration tool applies pending scripts in order and records which versions ran, so every environment ends up with the same schema.

How it works

Tools such as Flyway, Liquibase and golang-migrate keep a history table with the version of each applied script. On deploy, the tool compares that table with the scripts in the repository and runs the missing ones. For changes that must not break running code, teams use expand and contract:

1. Add the new column or table, and keep the old one.

2. Deploy code that writes to both and backfill existing rows.

3. Switch reads to the new structure.

4. Drop the old column in a later release.

Why it matters in production

On a large table, a single ALTER TABLE can lock writes or rebuild the table for hours. On a 400 million row table it can also cause replication lag on replicas. Online schema change tools and small, reversible steps let teams change the schema without downtime.

See also

• Change data capture

• Preview environment

• Infrastructure as code

• MongoDB oplog

On the One2N blog

• A practical guide for database schema migrations

• Zero downtime MySQL schema migrations for 400M row table

• Database reliability: zero downtime schema migrations with MySQL

References

1. Evolutionary database design, by Martin Fowler and Pramod Sadalage

2. MySQL reference manual: InnoDB online DDL