Online Schema Change Tools

There is a table size past which a native ALTER TABLE stops being a schema change and becomes an outage. On MySQL an operation that falls back to ALGORITHM=COPY rebuilds the entire table while holding a metadata lock that queues every read and write behind it; on PostgreSQL a rewriting ALTER takes an ACCESS EXCLUSIVE lock for the full duration of the copy. On a 200-million-row table that is minutes to hours of stalled traffic. External online schema change tools — gh-ost and pt-online-schema-change on MySQL, pg_repack on PostgreSQL — exist to sidestep that lock entirely: they build a new copy of the table in the background, keep it in sync with live writes, and swap it in under a lock measured in milliseconds. This guide serves DBAs who own the production database’s availability, platform engineers who script the change into a deploy, and anyone who has watched a “quick ALTER” pin the connection pool at 100% during peak traffic.

These tools are the escalation path for the exact case where the transactional versus non-transactional DDL behavior of your engine forces a full rewrite, and where the online options your migration tool comparison surfaced (ALGORITHM=INPLACE, CREATE INDEX CONCURRENTLY) do not cover the change you need — a column type change, a primary-key modification, or reclaiming bloat that a plain VACUUM cannot. They are not a replacement for the expand-and-contract methodology; they are how you execute the single rewriting step inside it without downtime.

Concept & Mechanism

All three tools implement the same shape — copy, sync, cutover — but through two structurally different mechanisms, and the difference decides how they behave under load.

The trigger-based approach (pt-online-schema-change). Percona Toolkit’s pt-online-schema-change creates an empty copy of the target table with the desired schema already applied — the “shadow” or _new table. It then installs AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the original table so that every live write is mirrored into the shadow table as it happens. In parallel it copies existing rows in small chunks, INSERT ... SELECT bounded by primary-key ranges. Because the triggers keep the shadow current while the chunked copy catches up on history, the two tables converge. The cutover is an atomic RENAME TABLE original TO _old, _new TO original, which MySQL executes under a brief metadata lock. The cost of this model is that the triggers run synchronously inside every application transaction on the original table, adding write latency for the entire duration of the copy, and that triggers interact badly with existing triggers and with foreign keys — a constraint significant enough that handling foreign keys with pt-online-schema-change is its own topic.

The binlog-based approach (gh-ost). GitHub’s gh-ost avoids triggers entirely. It creates the same shadow table but populates it two ways: a chunked row copy of existing data, and a stream of changes read from the MySQL binary log by connecting as a replica. gh-ost sees each committed write in the binlog and applies the equivalent change to the shadow table asynchronously, outside the application’s transaction path. Because nothing runs inside the application’s write — no synchronous trigger — the production workload feels almost no added latency, and gh-ost can throttle itself by watching replication lag and pausing the copy when a replica falls behind. The trade-off is that gh-ost requires row-based binary logging and a replication connection, and its asynchronous apply means the shadow table trails live writes by a small, measurable lag that must reach zero at cutover.

The reorganization approach (pg_repack). PostgreSQL’s problem is usually not an unsupported ALTER but bloat — dead tuples left by UPDATE/DELETE that autovacuum reclaims logically but does not return to the operating system, leaving a table physically larger than its live data. pg_repack rebuilds the table and its indexes into a fresh, compact copy while holding only brief locks at the start and end. It creates a log table, installs a trigger to capture concurrent changes, copies rows into a new heap ordered as requested, replays the captured changes, and then swaps the relation files under a short ACCESS EXCLUSIVE lock. It is the online equivalent of VACUUM FULL — which would otherwise lock the table for its entire run — and the full workflow lives in removing table bloat online with pg_repack.

Prerequisites & Decision Criteria

Reach for an online tool only when the native path genuinely locks. A metadata-only column add on MySQL 8.0 (ALGORITHM=INSTANT) or a PostgreSQL CREATE INDEX CONCURRENTLY needs no external tooling and should never be wrapped in one. Confirm the change actually forces a rewrite before adding a tool that copies the whole table. The checklist below scopes the decision, and the matrix picks the tool.

• The change forces a full-table rewrite (type change, PK change, non-INSTANT add on MySQL) or reclaims bloat a plain VACUUM cannot return.
• The table is large enough that the native lock duration exceeds your lock budget (typically millions of rows and up).
• There is enough free disk for a full second copy of the table plus its indexes — both mechanisms roughly double the table’s on-disk footprint during the run.
• The primary key is a single-column or otherwise chunkable key; all three tools copy in primary-key ranges and struggle without one.
• For gh-ost: binlog_format = ROW and a replication user are available.
• For pt-online-schema-change: foreign keys referencing the table are inventoried and a rebuild strategy is chosen (rebuild_constraints vs drop_swap).
• The run is scheduled for a lower-write window so the copy finishes before it accumulates unbounded change backlog.

The head-to-head reasoning for the two MySQL tools — when the trigger overhead of pt-osc is acceptable versus when gh-ost’s lag-aware, trigger-free copy is worth its binlog requirement — is worked through in gh-ost vs pt-online-schema-change. Whichever you pick, treat the change like any other versioned migration: the tool executes the DDL, but ordering and immutability still belong to your schema version control ledger.

Step-by-Step Procedure

The procedure below runs an online column change on MySQL with gh-ost, then a PostgreSQL bloat rebuild with pg_repack. Every command is meant to run against a database you have first rehearsed on a production-like copy per your environment parity baseline — never first against production.

1. Confirm the native path would lock. Before invoking any tool, prove the change is a rewrite. On MySQL, run the ALTER with ALGORITHM=INSTANT and let it fail fast rather than silently falling back to a copy.

-- MySQL 8.0 · run as the migration role · safe: fails instantly if a rewrite is required
-- If this errors, the change needs an online tool; it will NOT quietly rewrite the table.
ALTER TABLE orders
  MODIFY COLUMN total_amount DECIMAL(14,2) NOT NULL,
  ALGORITHM=INSTANT, LOCK=NONE;
-- Expect: "ALGORITHM=INSTANT is not supported. Reason: Cannot change column type."

Verify before proceeding: the statement fails with an unsupported-algorithm error, confirming a native run would fall back to COPY and lock.

2. Dry-run gh-ost. gh-ost’s --test-on-replica and --execute are mutually exclusive; always dry-run first (omit --execute) to validate connectivity, binlog format, and the generated shadow schema.

# bash · run from a host that can reach the primary and read the binlog · no --execute = dry run
# Requires: binlog_format=ROW, a replication-capable user, single-column PK on `orders`.
gh-ost \
  --host=primary.db.internal --user=gh_ost --password="$GHOST_PW" \
  --database=shop --table=orders \
  --alter="MODIFY COLUMN total_amount DECIMAL(14,2) NOT NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=80" \
  --max-lag-millis=1500 \
  --allow-on-master \
  --initially-drop-ghost-table --initially-drop-old-table
# Dry run: gh-ost validates and exits without touching data. Add --execute to run for real.

Verify before proceeding: the dry run reports the row count, estimated duration, and prints Migrating ...; Ghost table is ... without errors; the shadow table _orders_gho is created and dropped cleanly.

3. Execute with throttling armed. Add --execute. gh-ost copies in chunks and applies binlog events, pausing whenever replica lag exceeds --max-lag-millis or load exceeds --max-load. Leave it running and watch its status output.

# bash · run inside tmux/screen so a dropped SSH session does not kill the migration
# gh-ost writes a .sock file; you can throttle or cut over interactively through it.
gh-ost ... --execute
# Pause manually at any time:
echo throttle | nc -U /tmp/gh-ost.shop.orders.sock
echo no-throttle | nc -U /tmp/gh-ost.shop.orders.sock

Verify before proceeding: the periodic status line shows copy: N/Total climbing to 100% and lag: <max-lag-millis>, meaning the shadow table has caught up to live writes.

4. Cut over. By default gh-ost cuts over automatically once the copy completes and lag is within budget, swapping _orders_gho into place under a brief lock. To control timing, run with --postpone-cut-over-flag-file and remove the flag file when you are ready.

# bash · the cutover itself is a sub-second table swap; trigger it at a quiet moment
rm /tmp/ghost.postpone.flag   # releases gh-ost to perform the atomic cut-over

Verify before proceeding: gh-ost logs Done migrating shop.orders; the original table now carries the new column type and the _orders_del table holds the pre-swap original.

5. PostgreSQL: rebuild bloat with pg_repack. For the Postgres side, pg_repack runs as a client that drives the rebuild; it must connect as a superuser (or a role with the extension installed) and the extension must exist in the target database.

# bash · run outside any transaction · needs the pg_repack extension + elevated privileges
# Rebuilds the table and all its indexes into a fresh compact heap, online.
pg_repack --host=primary.db.internal --dbname=shop \
  --table=public.orders --jobs=4 --no-order
# --jobs parallelizes index builds; --no-order skips clustering for speed.

Verify before proceeding: pg_repack logs INFO: repacking table "public.orders" and exits 0, having held ACCESS EXCLUSIVE only at the initial trigger install and final swap.

Verification & Observability

Confirm success from the database’s own state, not the tool’s exit message. Check that the swap completed, no shadow objects were orphaned, and the new definition is live.

• The target table’s definition matches the requested change: SHOW CREATE TABLE orders\G (MySQL) or \d+ orders (PostgreSQL).
• No orphaned shadow or log tables remain: no _orders_gho, _orders_del, _orders_new, or log_* / pg_repack temp objects linger.
• Row counts match between the pre-change baseline and the rebuilt table (allowing for live inserts during the run).
• Replica lag returned to baseline after cutover and did not spike a follower out of budget.
• For pg_repack: table size shrank — compare pg_total_relation_size before and after.

-- MySQL 8.0 · read-only · run from a second session during and after the run
-- Watch the migration thread's metadata lock; it should be held only at cut-over.
SELECT OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'orders';
SHOW PROCESSLIST;   -- look for gh-ost/pt-osc copy threads and their state

-- PostgreSQL · read-only · confirm the rebuild reclaimed space and left no locks
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size;
SELECT pid, mode, granted FROM pg_locks
  JOIN pg_class ON pg_locks.relation = pg_class.oid
  WHERE pg_class.relname = 'orders' AND NOT granted;   -- expect zero rows

Both MySQL tools drive their throttle off replica lag, so watch it directly while the copy runs; sustained lag means the copy is outrunning your followers, exactly the failure mode that backfill optimization tunes chunk size against.

-- MySQL 8.0 · read-only · run on the replica while the copy is in progress
SHOW REPLICA STATUS\G   -- watch Seconds_Behind_Source stay within your throttle budget

Rollback Path

The safest property of all three tools is that until cutover, the original table is untouched and authoritative — rollback before the swap is simply “stop the tool.”

Before cutover. Kill the gh-ost or pt-osc process. gh-ost leaves the _gho shadow and, because it never wrote to the original, no cleanup of live data is needed — drop the shadow table. pt-osc is the case that needs care: its triggers must be removed, or every write to the original keeps mirroring into a now-orphaned shadow. Use its own cleanup rather than dropping tables by hand.

-- MySQL 8.0 · migration role · safe: original table is authoritative pre-cutover
-- Remove pt-osc's leftover triggers, then drop the abandoned shadow table.
DROP TRIGGER IF EXISTS pt_osc_shop_orders_ins;
DROP TRIGGER IF EXISTS pt_osc_shop_orders_upd;
DROP TRIGGER IF EXISTS pt_osc_shop_orders_del;
DROP TABLE IF EXISTS _orders_new;

After cutover. The swap already happened, so rollback means reversing the change forward, not resurrecting the old table. gh-ost and pt-osc keep the pre-swap original as _orders_del / _orders_old briefly; if you must revert immediately and no writes have hit the new table, you can rename it back — but the correct, auditable path is a new forward migration that restores the prior definition, consistent with treating rollback as a forward contract rather than a destructive DROP.

-- MySQL 8.0 · migration role · EMERGENCY only, and only if no writes hit the new table yet
-- Prefer a forward migration; this raw swap-back loses any rows written post-cutover.
RENAME TABLE orders TO _orders_bad, _orders_del TO orders;

Rollback is safe only when: the reverse definition does not drop data the application still reads, no writes have landed on the post-cutover table that the old shape cannot hold, and the throttle/lag state is clean. If any of those fail, halt and restore from a snapshot instead. For pg_repack there is effectively nothing to roll back — a killed run leaves the original relation intact and only needs its temporary objects cleaned with pg_repack --drop.

Common Errors & Fixes

Error 1846: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type (MySQL native ALTER). Root cause: the change cannot be done in place, so a native run would fall back to ALGORITHM=COPY and lock the table. Fix: this is the signal to run the change through gh-ost or pt-online-schema-change rather than natively.

FATAL Existing --ghost table found, and --initially-drop-ghost-table is not set (gh-ost). Root cause: a previous run was interrupted and left its _gho shadow table behind. Fix: confirm no migration is running, then re-run with --initially-drop-ghost-table --initially-drop-old-table, or drop the leftover _orders_gho / _orders_del tables manually.

Cannot find replication log; is binlog_format set to ROW? / Error 1236 (gh-ost). Root cause: gh-ost reads the binary log to stream changes and requires binlog_format=ROW; a STATEMENT or MIXED format, or a purged binlog position, breaks the stream. Fix: set binlog_format=ROW and binlog_row_image=FULL, ensure the replication user has REPLICATION SLAVE, and restart the run.

Error creating triggers: foreign key constraint is incorrectly formed / pt-osc refuses with child FKs. Root cause: the target table is referenced by foreign keys, and swapping the table underneath them breaks the constraint unless the tool rebuilds or re-points them. Fix: choose an explicit --alter-foreign-keys-method (rebuild_constraints or drop_swap) after reading handling foreign keys with pt-online-schema-change; do not force it blindly.

ERROR: pg_repack failed with "ERROR: permission denied for schema repack" or extension "pg_repack" is not installed. Root cause: the extension is missing in the target database, or the connecting role lacks the privileges pg_repack needs to install its trigger and swap relfilenodes. Fix: CREATE EXTENSION pg_repack; in the target database as a superuser, and connect pg_repack as a role with the required privileges — the client and server extension versions must also match exactly, or it aborts with a version-mismatch error.

Child Pages in This Section

Three deeper guides sit under this one. When you have narrowed the MySQL choice to the two dominant tools and need the trade-off spelled out, gh-ost vs pt-online-schema-change compares the trigger model against the binlog model on write-latency overhead, throttling behavior, and operational failure modes so you can defend the pick. Because the single hardest edge case for the trigger-based tool is referential integrity, handling foreign keys with pt-online-schema-change walks through the rebuild_constraints and drop_swap methods, the brief window each opens, and how to verify no child rows are orphaned across the swap. And on the PostgreSQL side, where the usual driver is reclaiming space rather than altering structure, removing table bloat online with pg_repack covers measuring bloat, running the rebuild without a VACUUM FULL lock, and cleaning up after an interrupted run.

This guide is one of the foundations in Database Migration Fundamentals & Tool Selection — the section that defines what a safe, ordered, lock-aware migration is before you scale it to a table this large.

Frequently Asked Questions

When should I use an online schema change tool instead of a native ALTER? Only when the native change would take a long lock — a full-table rewrite such as a column type change, a primary-key modification, a non-INSTANT add on MySQL, or reclaiming bloat that a plain VACUUM cannot return to the OS. Metadata-only changes (ALGORITHM=INSTANT, CREATE INDEX CONCURRENTLY) already avoid the lock and should never be wrapped in an external tool, which only adds a full table copy and doubled disk usage for no benefit.

What is the practical difference between gh-ost and pt-online-schema-change? The sync mechanism. pt-online-schema-change installs triggers on the original table, so every live write runs extra work synchronously inside the application’s transaction, adding latency for the whole run. gh-ost reads the binary log as a replica and applies changes out-of-band, so the production workload feels almost no added latency and gh-ost can throttle on replica lag — at the cost of requiring binlog_format=ROW and a replication connection.

Do these tools cause any downtime at all? There is a single brief lock at cutover — a RENAME TABLE on MySQL or an ACCESS EXCLUSIVE swap in pg_repack — typically milliseconds. If the tool cannot acquire that lock quickly because a long transaction is holding the table, the swap waits, which is why you cut over at a low-write moment and set a short lock-wait timeout so a blocked cutover fails fast rather than queueing traffic behind it.

How much extra disk do I need to run one safely? Plan for roughly a full second copy of the table plus its indexes, because all three tools build a complete new heap alongside the original before swapping. Verify free space before starting; a run that exhausts disk mid-copy aborts and leaves shadow objects you must clean up manually.

Related

• gh-ost vs pt-online-schema-change
• Handling Foreign Keys with pt-online-schema-change
• Removing Table Bloat Online with pg_repack
• Transactional vs Non-Transactional DBs
• Migration Tool Comparison

Up one level: Database Migration Fundamentals & Tool Selection