Online Schema Migrations in MySQL: Native, gh-ost, pt-online-schema-change

ALTER TABLE on a billion-row table is the canonical "this is going to be a long night" moment for a DBA. MySQL 5.6 added online DDL for many operations. MySQL 8.0 added ALGORITHM=INSTANT for ADD/DROP COLUMN and a few other cases, making them effectively free. But many operations still rewrite the entire table: changing a column type, converting character sets, adding an index to a big InnoDB table on 5.7, modifying a primary key. For those, the right tool is one of three: native online DDL, gh-ost (built by GitHub), or pt-online-schema-change (Percona Toolkit). Each has a different mechanism, a different failure mode, and a different operational profile. Below is the comparison from running each in production over the years, with the cases where each is the right answer.

Which online schema migration tool should I use for MySQL?

Use native online DDL for any operation MySQL 8.0+ can do with ALGORITHM=INSTANT (ADD COLUMN, DROP COLUMN, RENAME COLUMN since 8.0.29, change column default). These complete in milliseconds regardless of table size and need no external tool. Use gh-ost for any heavy ALTER (column type change, character set conversion, adding an index on a multi-GB table) on a topology with replicas. gh-ost is triggerless: it reads the binary log of a replica to capture changes, which means zero impact on the primary and clean handling of foreign keys. Use pt-online-schema-change when you're on a single-instance MySQL with no replicas, or when you need to ALTER something gh-ost doesn't support (table without a primary key, foreign-key parent tables). pt-osc is trigger-based, which is operationally simpler but adds load to the primary. Never use any of these without first checking if the operation can run with ALGORITHM=INSTANT — that's always faster and safer.

Jump to:

• What MySQL 8.0 made instant
• When native online DDL is enough
• How gh-ost works
• How pt-online-schema-change works
• The decision matrix
• Concrete examples: same ALTER, three tools
• Replication considerations
• Foreign-key handling
• Throttling and back-off
• What to do next
• FAQ

What MySQL 8.0 made instant

ALGORITHM=INSTANT adds or removes the column from the table metadata only. No table rewrite, no data movement, no per-row work. The operation is O(1) regardless of table size.

8.0 supported INSTANT for these operations:

For these, run the ALTER unmodified. MySQL picks INSTANT automatically when possible. To make sure:

ALTER TABLE my_table ADD COLUMN new_col VARCHAR(255) DEFAULT NULL, ALGORITHM=INSTANT;

If the operation can't be INSTANT (because the column has a non-NULL default that requires rewriting, or it's an unsupported operation), the ALTER fails fast and tells you which algorithm would actually work.

When native online DDL is enough

Beyond INSTANT, MySQL's ALGORITHM=INPLACE, LOCK=NONE covers many heavier operations without a table rewrite or without a long lock:

For "INPLACE without rewrite" operations, native is the right answer. They run concurrent with traffic, only a brief metadata lock at the start and end.

For "INPLACE with rewrite," native is workable for small-to-medium tables (under 50 GB or so) but the operation can still take hours and consumes I/O. This is where external tools start to matter.

The diagnostic: run EXPLAIN ANALYZE ALTER ... or just attempt the ALTER on a copy of the table. MySQL tells you which algorithm it would use:

ALTER TABLE my_table MODIFY COLUMN status VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation.
-- Try ALGORITHM=COPY.

That error means native online won't work — pick gh-ost or pt-osc.

How gh-ost works

gh-ost (GitHub's online schema migration tool) is triggerless. The flow:

1. Create a shadow table (_tablename_gho) with the desired schema.
2. Copy rows from the original table to the shadow table in chunks.
3. Subscribe to the binary log of a replica (or the primary, but a replica is preferred). Every INSERT/UPDATE/DELETE on the original is replayed on the shadow.
4. When the copy completes and replay catches up, run an atomic table rename: original → _tablename_del, shadow → tablename.
5. Drop the _del table (or keep it, gh-ost has a flag).

The key property: the primary is not modified during the migration. No triggers, no extra writes. The work is done off-stage, then a fast rename at the end.

Run on a typical schema:

gh-ost \
    --host=replica.db.example.com \
    --user=root \
    --password=YOUR_PASSWORD \
    --database=mydb \
    --table=users \
    --alter="MODIFY COLUMN email VARCHAR(320) NOT NULL" \
    --allow-on-master \
    --max-load='Threads_running=25' \
    --critical-load='Threads_running=50' \
    --chunk-size=1000 \
    --switch-to-rbr \
    --execute

The --max-load and --critical-load flags throttle gh-ost when the primary is busy, so the migration backs off automatically under load.

What gh-ost does not support:

• Foreign keys as parent (a table referenced by FK on another table). It can handle child tables with FKs pointing out, but not parents.
• Tables with no primary key or unique key.
• Triggers on the original table (gh-ost won't run; remove them, run gh-ost, recreate triggers via gh-ost's --hooks mechanism).

When those constraints aren't an issue, gh-ost is what I reach for first on any large-table ALTER.

How pt-online-schema-change works

pt-online-schema-change (from Percona Toolkit) is trigger-based. The flow:

1. Create a shadow table with the desired schema.
2. Install three triggers on the original (AFTER INSERT, AFTER UPDATE, AFTER DELETE) that mirror every change into the shadow.
3. Copy existing rows from original to shadow in chunks.
4. Atomic rename: original → old, shadow → original. Drop old.

The triggers run synchronously on every write to the original table during the migration, which:

• Adds latency to every INSERT/UPDATE/DELETE on the original.
• Doubles the write load (every write also writes to the shadow).
• Risks deadlocks if your application does concurrent transactions on overlapping rows.

Run:

pt-online-schema-change \
    --alter "MODIFY COLUMN email VARCHAR(320) NOT NULL" \
    --execute \
    h=db.example.com,D=mydb,t=users

What pt-osc does support that gh-ost doesn't:

• Parent tables with foreign keys. pt-osc has flags (--alter-foreign-keys-method) to handle the FK reconnection.
• Tables without explicit primary keys (it uses any unique index).
• Some weirder schema shapes that gh-ost rejects.

When pt-osc is the right pick:

• You have only one MySQL instance (no replicas). gh-ost prefers to read from a replica's binary log.
• The table is a parent of foreign keys you can't or won't drop.
• The application can tolerate trigger overhead on every write.

The decision matrix

Operation possible with ALGORITHM=INSTANT?
  Yes → native, ALTER TABLE ... ALGORITHM=INSTANT
  No → continue

Operation possible with ALGORITHM=INPLACE, LOCK=NONE, no rewrite?
  Yes → native, ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE
  No → continue

Have replicas + table is not an FK parent + has primary key?
  Yes → gh-ost
  No → continue

Single instance, or table is an FK parent, or no primary key?
  → pt-online-schema-change

Run the matrix top-to-bottom. Don't skip levels because of habit; native is always faster than external when it works.

Concrete examples: same ALTER, three tools

The example: convert a users table's email column from VARCHAR(255) to VARCHAR(320) (the RFC-allowed maximum email length). 50 million rows, 12 GB on disk.

Native (MySQL 8.0):

ALTER TABLE users MODIFY COLUMN email VARCHAR(320) NOT NULL,
    ALGORITHM=INPLACE, LOCK=NONE;

On 8.0+ this rewrites the table inplace. Concurrent reads and writes continue. Wall-clock time: ~45-90 minutes on SSD. The table is fully usable throughout.

gh-ost:

gh-ost \
    --host=replica.db.example.com \
    --user=root --password=$DB_PASS \
    --database=mydb --table=users \
    --alter="MODIFY COLUMN email VARCHAR(320) NOT NULL" \
    --allow-on-master --execute

Wall-clock time: ~60-120 minutes on the same hardware. The primary sees zero schema-related load (the work happens via the replica's binlog). The replication path is what's busy.

pt-online-schema-change:

pt-online-schema-change \
    --alter "MODIFY COLUMN email VARCHAR(320) NOT NULL" \
    --execute \
    h=primary.db.example.com,D=mydb,t=users

Wall-clock time: ~75-150 minutes. The primary sees doubled write load (every UPDATE triggers a shadow-table UPDATE) for the duration. Application latency on writes goes up 20-50%.

For this specific operation on 8.0+, native is the fastest, cleanest, and lowest-risk option. Use it. The external tools are for when native doesn't work.

Replication considerations

For replicated environments, gh-ost is the friendliest. The migration is structured as binlog replay, which is exactly what replicas already do.

For an instance with no replicas, native or pt-osc are the only options.

Foreign-key handling

The case that bites people. If orders.user_id is a foreign key to users.id, and you want to ALTER the users table:

• Native handles this transparently. The FK is just metadata; rewriting the parent table doesn't disturb the constraint.
• gh-ost refuses to run. The shadow table doesn't have the same FK references and gh-ost won't bridge them. The workaround is dropping the FK before migration, running gh-ost, recreating the FK.
• pt-osc handles this through the --alter-foreign-keys-method flag. Two strategies: rebuild_constraints (rebuilds the FK constraints after the rename, slow but transactional) or drop_swap (drops the FK during the swap, fast but briefly leaves data un-protected).

For FK-heavy schemas, pt-osc is generally less painful than gh-ost.

Throttling and back-off

Both gh-ost and pt-osc have load-based throttling so the migration backs off when the primary is busy.

# gh-ost throttle by Threads_running on the primary
gh-ost ... \
    --max-load='Threads_running=25,Threads_connected=200' \
    --critical-load='Threads_running=50,Threads_connected=500'

# gh-ost throttle by replication lag on a specific replica
gh-ost ... \
    --throttle-control-replicas='replica1.db,replica2.db' \
    --max-lag-millis=1500

# pt-osc throttle by Threads_running
pt-online-schema-change ... \
    --max-load Threads_running=25 \
    --critical-load Threads_running=50

Set conservative thresholds. The migration will take longer but the application stays responsive.

What to do next

After your ALTER:

• MySQL Cheat Sheet — for the diagnostic queries.
• How to Change a MySQL Column Type — the foundational ALTER TABLE patterns.
• How to Add a Column in MySQL — for the common ADD COLUMN case (which is INSTANT on 8.0+).
• utf8 to utf8mb4 Migration in MySQL — a classic case where you need an online tool.

External references:

• gh-ost on GitHub
• pt-online-schema-change documentation
• MySQL Reference Manual: Online DDL Operations
• The original gh-ost announcement (GitHub Engineering, 2016)

FAQ