Comparison of pt-online-schema-change, gh-ost, and MySQL Online DDL for Online Schema Changes

As business systems evolve rapidly, table structures change frequently, and traditional ALTER operations can cause heavy locking, replication lag, and migration failures. Online schema‑change tools such as pt‑online‑schema‑change (pt‑osc) and gh‑ost provide non‑blocking alternatives, while MySQL's native ONLINE DDL offers built‑in support from version 5.6 onward.

1. pt‑online‑schema‑change

pt‑osc creates a shadow table, applies the DDL on it, and synchronizes changes from the original table using triggers.

Workflow:

Create a shadow table _t1_new with the same structure as t1 .

Apply the ALTER on the empty shadow table.

Create INSERT, DELETE, and UPDATE triggers on t1 to copy DML to the shadow table.

Copy existing rows in chunks using INSERT LOW_PRIORITY IGNORE INTO `_t1_new` ... SELECT ... LOCK IN SHARE MODE .

Analyze the shadow table, rename tables, and drop the original.

Example trigger creation:

CREATE TRIGGER `pt_osc_test_t1_ins` AFTER INSERT ON `test`.`t1`
FOR EACH ROW REPLACE INTO `test`.`_t1_new` (`id`,`col1`,`col2`,`col3`)
VALUES (NEW.`id`,NEW.`col1`,NEW.`col2`,NEW.`col3`);

Limitations & Risks:

Original table must have a primary key or unique key.

No existing triggers are allowed.

Only works on InnoDB tables in Percona XtraDB Cluster with wsrep_OSU_method=TOI .

Changing primary‑key or unique‑key column names can break the DELETE trigger, leading to data inconsistency.

Potential lock contention during the final rename phase.

2. gh‑ost

gh‑ost avoids triggers by streaming row‑based binlog events from a replica and applying them to a shadow table.

Workflow:

Create a shadow table _t1_gho and apply the ALTER.

Start a binlog streamer that reads changes from the replica and replays them on the shadow table.

Copy existing rows in chunks using INSERT IGNORE INTO `_t1_gho` ... SELECT ... LOCK IN SHARE MODE .

Lock the original table, rename tables atomically, and stop the streamer.

Example data‑copy statement:

INSERT /* gh-ost `test`.`t1` */ IGNORE INTO `test`.`_t1_gho` (`id`,`c1`,`c2`,`c3`)
SELECT `id`,`c1`,`c2`,`c3` FROM `test`.`t1` FORCE INDEX(`PRIMARY`)
WHERE `id` > _binary'8991' AND `id` <= _binary'9990' LOCK IN SHARE MODE;

Limitations & Risks:

Requires a replica with row‑based binlog and binlog_row_image=FULL .

Target table must have a non‑NULL primary or unique key.

Does not support foreign keys or existing triggers.

Renaming primary‑key columns may abort the operation unless explicitly approved.

Locking the table during the cut‑over can cause lock‑wait timeouts under heavy write load.

3. MySQL ONLINE DDL

MySQL provides native online DDL that can operate in rebuild or no‑rebuild modes, depending on whether the operation requires a full table copy.

Phases:

Prepare: Create temporary metadata, acquire exclusive MDL lock, and decide the execution method.

DDL Execution: Release the exclusive lock, copy or rebuild data while applying incremental changes.

Commit: Re‑acquire exclusive lock, finalize metadata, and rename temporary files.

Limitations & Risks:

Large tables can cause significant replication lag and increased load.

Some DDL still blocks DML; temporary online‑alter logs may overflow, causing failure and rollback.

In clustered environments (PXC, MGR) long‑running DDL can lead to node desynchronization and flow‑control issues.

4. Summary

All three approaches—pt‑osc, gh‑ost, and native MySQL ONLINE DDL—require careful planning of disk space, load monitoring, and risk mitigation. For very large tables, using an online tool that creates a shadow copy (pt‑osc or gh‑ost) isolates the DDL work but doubles storage needs, while native ONLINE DDL avoids extra copies but may still impact performance and replication. Choosing the right tool depends on the specific operation, cluster topology, and tolerance for temporary load and lock contention.