Operationsintermediate

Schema Migration Tools

What a migration tool must provide, how the common ones differ, and the online schema change tools for large tables.

3 min readIntermediateUpdated 31 Jul 2026Edit this page

Schema changes must be versioned, reviewed and applied the same way in every environment. That is what a migration tool provides; the specific tool matters less than using one consistently.

What a tool must provide

Ordered, versioned migrations stored in the repository.
A record of what has been applied, in the database itself.
A lock so two application instances starting simultaneously do not apply the same migration twice.
Checksums, so an already-applied migration cannot be edited silently.
A dry run or plan output for review.

Common tools

Tool	Style	Notes
Flyway	SQL files, versioned	Simple and explicit; wide engine support
Liquibase	XML/YAML/SQL changesets	Abstraction across engines; more machinery
Alembic	Python, autogenerated	Integrates with SQLAlchemy; review generated output
golang-migrate	SQL files, up and down	Minimal, CLI-first
Rails / Django / Laravel	Framework-native	Convenient; still needs the practices below
Atlas / Skeema	Declarative	Desired-state schema; generates the diff
Sqitch	Dependency-ordered	Explicit dependencies rather than sequential numbering

Practices independent of the tool

One logical change per migration, so a failure is easy to understand and resume.
Set a lock timeout in the migration session so DDL fails fast instead of blocking the table behind a long query.
Separate schema changes from data backfills. Backfills are batched, resumable jobs — not part of the deploy's critical path.
Never edit an applied migration. Write a new one.
Run migrations as a role that owns the objects, distinct from the application role.

Online schema change for large tables

When a change requires a table rewrite in MySQL or MariaDB and the table is too large to lock:

# gh-ost: reads the binlog, no triggers on the original table.
gh-ost \
  --host=db.internal --database=shop --table=orders \
  --alter="ADD COLUMN dispatch_note TEXT" \
  --allow-on-master --initially-drop-ghost-table \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=200 \
  --cut-over=atomic --execute
 
# pt-online-schema-change: trigger-based; older and widely deployed.
pt-online-schema-change \
  --alter "ADD COLUMN dispatch_note TEXT" \
  D=shop,t=orders --execute

Both build a shadow table, copy rows in chunks, keep it current, and swap at the end. gh-ost throttles against real server load, which is why it is generally preferred where the binlog is available.

PostgreSQL needs these less often — most common changes are metadata-only — but pg_repack fills the equivalent role for rewriting a bloated table without a long exclusive lock.