Online Schema Change for Tables with Triggers: pt-osc and gh-ost Workarounds

Your orders table has grown to 900 million rows and 480 GB on disk, and the product team needs a new VARCHAR(64) column added before the quarter-end reporting run — three days from now. A naive ALTER TABLE would acquire a metadata lock, block all writes for the better part of six hours, and almost certainly trigger a P0 incident before it finishes. You reach for pt-online-schema-change, run the pre-flight check, and immediately hit an error: the table already has three application triggers, and pt-osc refuses to proceed. This scenario plays out in production engineering teams every week, and navigating it requires understanding exactly how each tool — pt-osc, gh-ost, and MySQL's own native DDL — handles the constraints you cannot remove.

Why Online Schema Changes Exist

MySQL's ALTER TABLE — even with ALGORITHM=INPLACE — holds a brief exclusive metadata lock (MDL) at the start of the operation to drain active transactions, and again at the end to swap the rebuilt table into place. For most operations that require a full table rebuild, the engine must read every row of the original table, write it into a new table with the updated structure, and then rename the tables atomically. On a 10 GB table this takes seconds. On a 500 GB table it takes hours. During that entire period, any write that arrives is queued behind the MDL, and on a busy application server with a saturated connection pool, those queued connections back up instantly and exhaust all available connections within minutes.

Online schema change tools avoid this by moving the long-running work outside the critical path of your application's MDL. They build a new table in the background, copy rows incrementally, and defer the final table swap to the shortest possible window. The difference between a six-hour outage and a two-second cutover lock is the difference between a scheduled maintenance window and an unscheduled P0.

pt-online-schema-change: Trigger-Based Copy

pt-online-schema-change (pt-osc), part of Percona Toolkit, was the industry standard for MySQL online schema changes for over a decade. Its mechanism is straightforward and auditable:

1. Create a new empty table (_tablename_new) with the target schema.
2. Create three AFTER triggers on the original table — AFTER INSERT, AFTER UPDATE, and AFTER DELETE — that replicate any DML on the original to the new table in real time.
3. Copy rows from the original to the new table in small chunks, ordered by primary key, with configurable chunk size and sleep intervals to throttle I/O.
4. When the row copy is complete, acquire a brief lock, rename _tablename_new to the original name (and the original to _tablename_old), and release the lock.
5. Drop the old table.

# Basic pt-osc invocation — add a column to a large table
pt-online-schema-change \
  --host=db-primary.internal \
  --user=dba \
  --ask-pass \
  --alter="ADD COLUMN last_status_change DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" \
  --execute \
  --chunk-size=1000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --check-interval=5 \
  D=myapp,t=orders

The trigger approach has one additional consequence: triggers fire synchronously inside the original transaction. Every write to the source table pays the cost of an additional write to the new table before the transaction can commit. On a high-write table this adds latency to your application's critical path during the entire migration window.

gh-ost: Trigger-Free Binlog Streaming

gh-ost (GitHub's Online Schema Transmogrifier) was built specifically to address the trigger problem. It is completely trigger-free. Instead of attaching triggers to the source table, gh-ost connects to a MySQL replica as a fake replica using the replication protocol and streams the binary log. Every row-format DML event on the source table appears in the binlog, gh-ost reads it, and applies it to the ghost table. The ghost table catches up to the source table through this binlog streaming rather than through triggers.

# gh-ost running against a replica for binlog streaming
# (never connects to primary for reads — reduces primary load)
gh-ost \
  --host=db-replica.internal \
  --port=3306 \
  --user=gh-ost \
  --password="$(cat /etc/gh-ost-password)" \
  --database=myapp \
  --table=orders \
  --alter="ADD COLUMN last_status_change DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" \
  --assume-rbr \
  --chunk-size=1000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --switch-to-rbr \
  --allow-on-master=false \
  --execute

Because gh-ost reads from the binlog rather than installing triggers, it works correctly on tables with existing triggers — those triggers fire for the application's DML as usual, and gh-ost's binlog reader sees the resulting row changes independently. The two mechanisms do not interact.

gh-ost also provides a control socket during migration. You can pause and resume the row copy, change chunk size and throttle parameters on the fly, and — crucially — control when the cutover happens:

# Interact with a running gh-ost migration via the unix socket
echo "status" | socat - /tmp/gh-ost.myapp.orders.sock
echo "throttle" | socat - /tmp/gh-ost.myapp.orders.sock
echo "unthrottle" | socat - /tmp/gh-ost.myapp.orders.sock
echo "chunk-size=500" | socat - /tmp/gh-ost.myapp.orders.sock

# Initiate the cutover manually when you are ready
echo "unpostpone" | socat - /tmp/gh-ost.myapp.orders.sock

MySQL 8.0 INSTANT DDL: The Zero-Cost Alternative

Before reaching for an external tool, always check whether your change qualifies for ALGORITHM=INSTANT. Operations that are truly instant — meaning MySQL modifies only the table's metadata in the data dictionary with no row reads or writes — include:

• Adding a column at the end of the table (MySQL 8.0.12+; any position in MySQL 8.0.29+)
• Dropping a column (MySQL 8.0.29+, using a new "instant" drop mechanism)
• Changing a column's default value
• Modifying an ENUM or SET column by appending new values at the end of the value list
• Renaming a column (since MySQL 8.0)
• Changing the number of virtual generated columns
• Changing index visibility (ALTER TABLE t ALTER INDEX idx INVISIBLE)

-- Instant: add a column at the end (8.0.12+)
ALTER TABLE orders
  ADD COLUMN last_status_change DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ALGORITHM=INSTANT;

-- Instant: change a default value
ALTER TABLE orders
  ALTER COLUMN status SET DEFAULT 'pending',
  ALGORITHM=INSTANT;

-- Instant: rename a column
ALTER TABLE orders
  RENAME COLUMN user_id TO customer_id,
  ALGORITHM=INSTANT;

-- Non-instant: adding an index always requires INPLACE at minimum
ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

ALGORITHM=INPLACE vs ALGORITHM=COPY vs ALGORITHM=INSTANT

When you omit the ALGORITHM clause, MySQL chooses the best available algorithm automatically. Understanding what each algorithm does — and when MySQL falls back — is essential for predicting the impact of any ALTER TABLE statement:

Always specify the algorithm explicitly in production scripts and let MySQL return an error if the operation cannot meet your algorithm requirement — rather than silently falling back to a more impactful algorithm:

-- This will fail with an error if INSTANT is not available
-- Better to know early than to discover COPY is running on a 400 GB table
ALTER TABLE orders
  ADD COLUMN risk_score TINYINT UNSIGNED DEFAULT NULL,
  ALGORITHM=INSTANT;

-- If INSTANT fails, try INPLACE with LOCK=NONE
-- This will fail if the operation requires a table lock
ALTER TABLE orders
  ADD COLUMN risk_score TINYINT UNSIGNED DEFAULT NULL,
  ALGORITHM=INPLACE,
  LOCK=NONE;

Tables with Existing Triggers: Choosing Between pt-osc and gh-ost

The decision tree for tables that carry application triggers is short: use gh-ost. pt-osc simply cannot run. But the answer deserves more nuance for teams considering which tool to standardize on.

pt-osc is simpler to set up and understand. It requires no replica access, works directly against the primary, and has fewer moving parts. For a small engineering team running infrequent migrations on tables without existing triggers, it is the lower-friction choice. Its throttle controls are coarser — it pauses the chunk copy based on server load metrics — but they are adequate for most workloads.

gh-ost is more complex to operate but provides capabilities that matter at scale: the postponable cutover, the control socket, replica-lag-aware throttling, and complete trigger independence. For any table that already has triggers, gh-ost is not a preference — it is the only viable external tool.

Foreign Key Constraints and Online Schema Changes

Foreign keys introduce a significant complication for both tools. pt-osc's default behavior is to drop all foreign keys from the original table, recreate them on the new table (with renamed constraints to avoid conflicts), and then attempt to rename the constraints back after cutover. This works for simple single-level FK relationships but becomes unreliable — and sometimes flat-out fails — with multi-level cascades or FK constraints that reference the table being altered from other tables.

gh-ost takes a different stance: it does not manage foreign keys at all by default. If you attempt a gh-ost migration on a table that is referenced by a FK in another table, gh-ost will detect this and refuse to proceed unless you explicitly pass --skip-foreign-key-checks. When you use that flag, gh-ost proceeds with the migration but does not move or recreate the foreign key relationships — leaving you responsible for rebuilding them afterward.

-- After a gh-ost migration on a table referenced by FKs,
-- you may need to rebuild the FK from the child table
-- Check for dangling FK references post-migration:
SELECT
  rc.CONSTRAINT_NAME,
  rc.TABLE_NAME AS child_table,
  rc.REFERENCED_TABLE_NAME AS parent_table,
  rc.DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
WHERE rc.CONSTRAINT_SCHEMA = DATABASE()
ORDER BY rc.TABLE_NAME;

Monitoring Progress of Long-Running ALTER TABLE

For native ALTER TABLE operations running under ALGORITHM=INPLACE, MySQL exposes detailed progress through Performance Schema:

-- Monitor an in-progress ALTER TABLE in real time
SELECT
  event_name,
  work_completed,
  work_estimated,
  ROUND(work_completed / NULLIF(work_estimated, 0) * 100, 1) AS pct_done,
  TIMEDIFF(NOW(), date_started) AS elapsed
FROM performance_schema.events_stages_current
WHERE event_name LIKE 'stage/innodb/alter%'
  AND work_estimated > 0;

-- Alternative: sys schema view (MySQL 8.0+)
SELECT
  thd_id,
  conn_id,
  state,
  current_statement,
  statement_latency,
  progress
FROM sys.session
WHERE command = 'Query'
  AND current_statement LIKE 'ALTER TABLE%';

For gh-ost migrations, progress is available directly from the control socket and from the log output:

# Real-time gh-ost status including ETA
echo "status" | socat - /tmp/gh-ost.myapp.orders.sock

# Or tail the gh-ost log for periodic progress lines
# gh-ost logs progress every 60 seconds by default:
# -- Copy: 48,231,000/900,000,000 (5.4%); State: migrating;
#    ETA: 2025-03-15 04:27:00 UTC

Cutover Strategy: Minimizing the Lock Window

The cutover — when the ghost table is renamed to replace the original — is the most dangerous moment in any online schema change. Both tools acquire an exclusive lock on the original table, rename the tables, and release the lock. Any write that arrives during this window is queued. The goal is to make this window as short as possible.

pt-osc's cutover is automatic and uncontrolled: when the chunk copy is complete, it immediately attempts the rename. The typical lock duration is under two seconds, but on a loaded server with many active transactions, it can be longer as MySQL waits for active transactions to commit before granting the exclusive lock.

gh-ost's cutover is far more controllable. When the row copy finishes, gh-ost enters a "ready to cut over" state and waits for your signal (or for the postpone flag file to be removed). It continues streaming binlog events the entire time, keeping the ghost table perfectly current. When you trigger the cutover, gh-ost uses an atomic two-step rename: it creates a temporary _del table, acquires the lock, renames the original to _del and the ghost to the original name simultaneously (using MySQL's multi-table rename atomicity guarantee), and then drops the _del table. The lock window is typically under one second.

# Controlled gh-ost cutover workflow:
# 1. Start migration with postpone flag
touch /tmp/gh-ost.postpone.myapp.orders
gh-ost ... --postpone-cut-over-flag-file=/tmp/gh-ost.postpone.myapp.orders --execute

# 2. Monitor until row copy is complete and gh-ost is waiting
echo "status" | socat - /tmp/gh-ost.myapp.orders.sock
# Look for: "Waiting for cut-over signal..."

# 3. At your chosen moment (off-peak, after application deploy, etc.):
rm /tmp/gh-ost.postpone.myapp.orders
# gh-ost proceeds with the cutover immediately

Practical Decision Guide: pt-osc vs gh-ost vs Native ALTER TABLE

Online schema changes on large MySQL tables — especially tables with triggers, complex foreign key graphs, or aggressive write throughput — are among the highest-stakes operational tasks a DBA team performs. Getting the tool selection wrong, misunderstanding the lock model, or misjudging the cutover window can turn a routine schema migration into a customer-facing incident. JusDB works with engineering teams to design and execute safe schema migrations at scale — from audit and tool selection through execution, cutover coordination, and post-migration validation. If your team is planning a large DDL operation and wants expert eyes on the migration plan before it runs, reach out to JusDB.