Online Schema MigrationSchema changes deployed live. Every engine.
Online schema migration is two problems, not one. The engine decides what locks an ALTER takes. Deployment decides what to do when those locks aren't acceptable.
Online schema migration architecture
Two layers. One outcome.
Engine layer
What the database does natively when you run ALTER. MySQL picks between INSTANT, INPLACE, and COPY. Postgres takes a lock mode. Each engine decides — quietly — whether your migration blocks writers, stalls replicas, or finishes in milliseconds.
Deployment layer
What you reach for when the engine alone isn't enough. gh-ost and pt-online-schema-change shadow the table on a separate write path. Blue-green keeps two databases in sync and flips traffic. Both buy you migrations the engine can't do online — at the cost of running two systems instead of one.
Online schema migration guide
Read in order.
- 01
Start with MySQL Online DDL
INSTANT, INPLACE, COPY — what each one rewrites, what each one locks, and when MySQL falls back.
- 02
Reach for gh-ost when COPY is too costly
On a billion-row table, MySQL's COPY fallback can take hours and stall replicas. gh-ost reads the binlog, builds a shadow table, and swaps it in.
- 03
Postgres works differently
Different lock model, different playbook. Sequence ALTERs correctly and most go online without help — there isn't a Postgres-side gh-ost if you need one.
Online schema migration for MySQL
MySQL.
MySQL has the largest online-migration toolkit: three native algorithms plus gh-ost and pt-online-schema-change, hardened by a decade of production use at GitHub, Percona, and the operators who came after.
MySQL Online DDL
INSTANT, INPLACE, COPY — what each costs, and when MySQL silently downgrades to the slowest one.
Read →Schema migration best practice
The operational side: when to batch, when to throttle, how to keep replicas in step.
Read →gh-ost online schema migration
GitHub's binlog-driven shadow-and-cutover tool. Reach for it when Online DDL falls back to COPY.
Read →gh-ost vs pt-online-schema-change
Outgrown Online DDL? Pick between binlog-based gh-ost and trigger-based pt-osc.
Read →
Online schema migration in Bytebase
Migrations reviewed, sequenced, applied.
Bytebase reviews schema changes before they run, sequences them across environments, and picks Online DDL or gh-ost for you based on table size and policy. One workflow for the engine path and the deployment path — instead of writing your own migration tool around the database.
One workflow. Every engine.
Online schema migration questions
Common questions.
- What is online schema migration?
- Schema changes — added columns, new indexes, type widenings — that run while the database keeps serving reads and writes. The catch: most engines lock something for some part of the ALTER. "Online" really means the locks are short and tolerable, not that no locks happen at all.
- When should I use MySQL's Online DDL vs gh-ost or pt-online-schema-change?
- Start with Online DDL — it's free, in the engine, and covers most cases through INSTANT or INPLACE. The trouble is COPY: MySQL falls back to it silently on operations the other algorithms can't do in place, and on a large table that means hours of rebuild and replica lag. That's when you pull in gh-ost or pt-online-schema-change. Read the gh-ost limitations post first — both tools have edges around foreign keys, generated columns, and certain replication setups.
- Does Postgres need an external tool like gh-ost?
- Usually no — but that's a tooling gap, not engine superiority. Postgres has no mature general-purpose equivalent to gh-ost or pt-online-schema-change. pg_repack and pg_squeeze cover table reorganization, but neither replays arbitrary ALTERs while writers continue. In practice this works because most Postgres ALTERs go online natively if you sequence operations correctly — adding nullable columns, creating indexes CONCURRENTLY, splitting transformations into compatible steps. When a change can't go online natively, the fallback is blue-green deployment, not a Postgres-side gh-ost.
Every post in the series.
MySQL.
- 01
MySQL Online DDL: A Practical Guide
Worked examples of every common ALTER, mapped to which algorithm MySQL actually picks.
- 02
MySQL Schema Migration Best Practice
Field-tested checklist for the operational side — lock duration, replica lag, batching.
- 03
gh-ost Based Online Schema Migration for MySQL
How gh-ost reads the binlog, builds a shadow table, and atomically swaps in a new schema.
- 04
gh-ost vs pt-online-schema-change
Trigger-based vs binlog-based. Cut-over contention, foreign keys, runtime control, resumability.
- 05
gh-ost Limitations You Need to Know Before Production
Foreign keys, generated columns, replication topology — the gotchas before you run it on prod.