Online Schema Changes in MySQL: INSTANT and INPLACE DDL, the gh-ost Binlog Copy, Replica-Lag Throttling and the Cut-Over

Core engineering · Practical technical guide · 20 October 2026

How to change a large MySQL table without an outage: which ALTER operations are instant or in place in MySQL 8.4, the metadata lock each still needs, how gh-ost copies rows and tails the binary log without triggers, throttles on replica lag and cuts over safely.

Three ways to change a busy table

A column must be added to a 400-million-row orders table that takes writes all day. MySQL offers three broad approaches. Native online DDL lets InnoDB perform many ALTER TABLE operations instantly or in place while reads and writes continue. Trigger-based tools such as pt-online-schema-change build a copy of the table and keep it in sync with triggers. gh-ost builds the same copy but keeps it in sync by reading the binary log, with no triggers at all. Which one fits depends on the operation, on how much replication lag you can tolerate, and on how the final switch interacts with metadata locks.

Native online DDL in MySQL 8.4

The MySQL 8.4 manual lists, for each operation, whether it can run instantly, in place, whether it rebuilds the table and whether it permits concurrent DML. INSTANT is the default algorithm in 8.4, and it can add a column at any position in the table by changing only metadata. Each instant add or drop creates a new row version, and the manual caps the number of row versions at 64, 255 as of MySQL 9.1; once the limit is reached, instant ADD COLUMN and DROP COLUMN are rejected and the table must be rebuilt with COPY or INPLACE. Adding a secondary index runs in place and permits concurrent DML. Changing a column's data type is supported only with COPY, which rebuilds the table and does not permit concurrent DML.

Operation	Algorithm in 8.4	Concurrent DML	Practical cost
Add a column	INSTANT	Yes	Metadata only, until 64 row versions
Drop a column	INSTANT	Yes	Metadata only, same row-version limit
Add a secondary index	INPLACE, no rebuild	Yes	Reads the whole table and builds the index
Change a column data type	COPY	No	Full rebuild with writes blocked: use an external tool

Name the algorithm explicitly: the statement fails instead of silently falling back to a slower one.

ALTER TABLE orders ADD COLUMN gift_note VARCHAR(200) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at), ALGORITHM=INPLACE, LOCK=NONE;

Naming the algorithm is the cheapest safety measure in this article. If the operation cannot run with the algorithm you asked for, MySQL returns an error rather than quietly choosing a table copy.

Every ALTER still queues for a metadata lock

Online does not mean lock-free. The manual's section on online DDL performance describes three phases. Initialization takes a shared upgradeable metadata lock; execution may briefly take an exclusive one; and the commit phase upgrades to an exclusive metadata lock to swap in the new table definition. The exclusive lock is brief once granted, but it must wait for every transaction that already holds a metadata lock on the table. The manual's example spells out the consequence: a long-running or idle transaction makes the ALTER wait, and a pending exclusive metadata lock blocks subsequent transactions on the table. A one-second ALTER behind a ten-minute report query becomes a ten-minute outage for every new query on that table. It is the same lock-queue failure that PostgreSQL DDL behind a long transaction produces, and the remedy is the same: set lock_wait_timeout low for the migration session and retry with back-off.

Replication adds a second cost. The ALTER is written to the binary log as a statement and executed again on each replica after it finishes on the source. An in-place index build that took forty minutes on the source occupies the replica's applier for a similar time, and depending on how the replica applies transactions, the lag can grow for the whole duration.

How gh-ost copies without triggers

gh-ost's triggerless design document explains the approach. It creates a ghost table with the new schema, then does two things concurrently from a single writer. It copies existing rows from the original table to the ghost table in chunks, iterating over a primary key or another unique key shared by both tables, 1,000 rows per chunk by default. And it connects to a server as if it were a replica, streams row-based binary log events, filters the ones for the original table, and applies them to the ghost table. Because the source sees writes to the ghost table only from gh-ost's own connection, the migration's write load can be paused completely: when gh-ost throttles it stops copying and stops applying, leaving only a heartbeat write.

The requirements follow from the design. A server must provide row-based binary logs with a FULL row image; when gh-ost connects to a replica, that replica must log the changes it applies. Foreign key constraints and triggers on the migrated table are not supported, and the two tables must share a primary or unique key without NULL values.

Throttling on replica lag

gh-ost measures lag with its own heartbeat: it writes a timestamp to its changelog table and reads it back on the replicas it watches. When any controlled replica lags more than --max-lag-millis, gh-ost throttles; when all are below it, the copy resumes. --max-load throttles on server status variables such as Threads_running, --critical-load aborts the migration or hibernates on more serious thresholds, and a flag file or an interactive command throttles by hand. The documentation warns that --throttle-control-replicas must list replicas inside the migrated table's replication chain, because gh-ost does not verify them and an unrelated host will report healthy lag.

Illustrative invocation: connect to a replica, migrate on the source, hold the cut-over until the flag file is removed.

gh-ost \
  --host=replica-1.db.internal --database=shop --table=orders \
  --alter="MODIFY COLUMN total_cents BIGINT NOT NULL" \
  --assume-rbr --chunk-size=1000 --max-lag-millis=1500 \
  --max-load=Threads_running=40 --critical-load=Threads_running=200 \
  --cut-over-lock-timeout-seconds=3 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
  --execute

Worked example: how long the copy really takes

The numbers are illustrative. The orders table has 400 million rows and the data type change needs a full rebuild, so native DDL would block writes; gh-ost is the right tool. In a test the unthrottled copy reaches about 20,000 rows per second, which suggests 400,000,000 / 20,000 = 20,000 seconds, a little under six hours. During business hours replica lag crosses the threshold often and gh-ost is throttled about 40 percent of the time, so the copy runs at an average of 12,000 rows per second and takes roughly nine and a quarter hours. Meanwhile the application writes about 3,000 order rows per second at peak, all of which gh-ost must apply from the binary log through the same single writer, so the share of the writer available for copying shrinks further at peak.

The planning conclusions are concrete. Start the migration in the evening so the copy uses the quiet hours. Watch the estimated time remaining rather than the percentage, because the rate changes with load. Use the postpone flag so the cut-over happens when someone is watching, not at 4 a.m. when the copy happens to finish. And if the application's sustained write rate ever approaches the writer's capacity, the copy will make little progress; that is the signal to schedule the change for a lower-traffic period.

The cut-over

When the copy is complete and the binary log backlog is applied, the tables must swap. MySQL supports an atomic RENAME of two tables, but does not let a connection rename tables it holds under lock, and gh-ost must stop writes long enough to apply the last events. Its cut-over document describes the solution: one connection locks the original table, another issues the atomic RENAME, and a sentry table blocks the RENAME until gh-ost has applied the backlog. The operation either succeeds, with pending queries blocked briefly and then continuing on the new table, or fails on a timeout or a dead connection, in which case the original table is still in place, the waiting queries proceed, and gh-ost tries again. --cut-over-lock-timeout-seconds, 3 by default, bounds how long locks are held per attempt.

The usual reason a cut-over keeps failing is the one from the native DDL section: a long transaction holding a metadata lock on the original table. Find it in performance_schema.metadata_locks and the process list before you remove the postpone flag.

Failure modes

Relying on the default algorithm and getting a table copy that blocks writes; always name ALGORITHM and LOCK.
Reaching the 64 row-version limit after many instant column changes, so the next instant ALTER fails during an incident; track it and rebuild deliberately.
A long or idle transaction in front of the exclusive metadata lock, blocking every new query on the table.
Running gh-ost on a table with foreign keys or triggers, which it does not support.
Watching the wrong replicas for lag, so the replicas that serve reads fall far behind.
Letting the cut-over run unattended at the end of a long copy instead of postponing it to a watched window.

Sources and scope

Checked against these sources on 20 October 2026. Examples and figures are illustrative, not measurements.

Name ALGORITHM and LOCK on every ALTER, keep lock_wait_timeout low, use gh-ost for rebuilds that block writes, throttle on the right replicas and postpone the cut-over to a watched window.