MySQL INSTANT DDL: What Is Instant and What Still Copies the Table

You add a column to a large orders table on MySQL 8.4 and the ALTER TABLE returns at once. A week later a type change on the same table runs for hours, or an "instant" change sits in Waiting for table metadata lock while every query on the table queues behind it. All three are expected. Since MySQL 8.0.29, InnoDB can add and drop columns at any position with ALGORITHM=INSTANT, but many routine changes still rebuild or copy the table, and even an instant change needs a brief exclusive metadata lock. This guide covers what is instant on MySQL 8.4 LTS and 9.7 LTS, how to make MySQL refuse a slow path, the row-version limit, and when gh-ost or pt-online-schema-change is still the right tool.

Scope and review date: November 2026. Covers MySQL 8.0.30 and later 8.0 releases, 8.4 LTS, and 9.7 LTS (generally available since 21 April 2026). Calendar-versioned Innovation releases (26.7 and later) are out of scope. Statements were checked against the MySQL reference manuals, the MySQL error reference, and the gh-ost, Percona Toolkit, and Percona XtraBackup documentation. They were not run in a lab.

What does ALGORITHM=INSTANT actually change?

An instant operation changes only the table definition. The MySQL 8.4 ALTER TABLE reference says instant operations "only modify metadata in the data dictionary", table data "is unaffected", concurrent DML is permitted, and "an exclusive metadata lock on the table may be taken briefly during the execution phase". Existing rows are not rewritten. For an instant add or drop, InnoDB records a new row version and reads older rows through that version metadata.

The feature arrived in steps. MySQL 8.0.12 added instant ADD COLUMN for the last column only. 8.0.28 added instant column rename. 8.0.29 added instant add at any position, instant DROP COLUMN, and row versions. The 8.0.29 release notes now say that release was removed "due to a critical issue that could cause data in InnoDB tables having added columns to be interpreted incorrectly", and point users to 8.0.30. MySQL 8.4 keeps the 8.0.29 rules; 9.1.0 raised the row-version limit, and 9.7 LTS inherits that.

SELECT VERSION();
SELECT @@old_alter_table;

Interpretation: 8.0.30 or later, any 8.4.x, or any 9.7.x supports instant add and drop at any position. If old_alter_table returns 1, an ALTER TABLE without an ALGORITHM clause uses COPY; an explicit clause overrides it.

Which ALTER TABLE operations are instant in MySQL 8.4 and 9.7?

The 8.4 online DDL operations page and the matching 9.7 page mark these as instant:

ALTER TABLE shop.orders
  ADD COLUMN note VARCHAR(64) NULL AFTER status,
  DROP COLUMN legacy_flag,
  ALGORITHM=INSTANT;

ALTER TABLE shop.orders RENAME COLUMN ref TO external_ref, ALGORITHM=INSTANT;

ALTER TABLE shop.orders ALTER COLUMN status SET DEFAULT 'new', ALGORITHM=INSTANT;

-- channel was ENUM('web','app','pos') NOT NULL
ALTER TABLE shop.orders
  MODIFY COLUMN channel ENUM('web','app','pos','partner') NOT NULL,
  ALGORITHM=INSTANT;

Interpretation: all four statements change metadata only. The first uses one row version for both column changes. The rename, the default, and the ENUM change use none, because the manual counts versions only when columns are added or dropped. The MODIFY must repeat the column's other attributes exactly, or it becomes a different change.

Which changes still rebuild or copy the table?

These have no instant path in 8.4 or 9.7, per the manual's online DDL tables:

The VARCHAR boundary is in bytes: a VARCHAR(64) in utf8mb4 can need 256 bytes, so it already uses two length bytes. Instant add also fails if the internal column count would pass 1022 or the maximum row size would be exceeded. This query lists tables where instant add and drop never work:

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.ROW_FORMAT
FROM information_schema.TABLES AS t
WHERE t.ENGINE = 'InnoDB'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'sys')
  AND (t.ROW_FORMAT = 'Compressed'
       OR EXISTS (SELECT 1
                  FROM information_schema.STATISTICS AS s
                  WHERE s.TABLE_SCHEMA = t.TABLE_SCHEMA
                    AND s.TABLE_NAME = t.TABLE_NAME
                    AND s.INDEX_TYPE = 'FULLTEXT'));

Interpretation: each row is a compressed table or a table with a FULLTEXT index. Plan column changes on these as rebuilds from the start.

How do I make MySQL fail instead of silently copying the table?

Without an ALGORITHM clause, MySQL uses INSTANT if every clause supports it, otherwise INPLACE, otherwise COPY, with no warning. One non-instant clause turns an instant column add into a full rebuild. The ALTER TABLE reference says an explicit clause "requires the operation to use the specified algorithm for clauses and storage engines that support it, or fail with an error otherwise". With INSTANT, only LOCK=DEFAULT is permitted.

-- Instant or nothing
ALTER TABLE shop.orders
  ADD COLUMN note VARCHAR(64) NULL,
  ALGORITHM=INSTANT;

-- A rebuild chosen on purpose; writes continue.
-- Needs strict SQL mode and fails if the column holds NULLs.
ALTER TABLE shop.orders
  MODIFY COLUMN note VARCHAR(64) NOT NULL DEFAULT '',
  ALGORITHM=INPLACE, LOCK=NONE;

Interpretation: the first statement finishes as a metadata change or returns an error and changes nothing. The second tells reviewers that it rebuilds the table, and LOCK=NONE makes it fail if concurrent DML cannot be allowed.

How many instant column changes can one table take?

Each instant add or drop statement increments TOTAL_ROW_VERSIONS in INFORMATION_SCHEMA.INNODB_TABLES. The 8.4 INNODB_TABLES reference says: "The maximum TOTAL_ROW_VERSIONS value is 64. This maximum value changes from 64 to 255 as of MySQL 9.1.0." A table-rebuilding ALTER TABLE or OPTIMIZE TABLE resets it to 0. The older INSTANT_COLS column "is no longer used", so do not monitor it.

SELECT NAME,
       TOTAL_ROW_VERSIONS,
       64 - TOTAL_ROW_VERSIONS AS remaining_on_8_4
FROM information_schema.INNODB_TABLES
WHERE TOTAL_ROW_VERSIONS > 0
ORDER BY TOTAL_ROW_VERSIONS DESC
LIMIT 20;

Interpretation: NAME is schema/table. On 9.7, use 255. A table with few versions left should be rebuilt at a time you choose, before a release needs the instant path.

-- Null rebuild in place, writes continue; resets TOTAL_ROW_VERSIONS
ALTER TABLE shop.orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

-- OPTIMIZE TABLE maps to the same rebuild for InnoDB
OPTIMIZE TABLE shop.orders;

Interpretation: both rewrite the whole table, so run time scales with size and replicas replay the rebuild after the source finishes. On a large, busy table, run it through gh-ost or pt-online-schema-change with --alter "ENGINE=InnoDB" so it can be throttled.

Why does an instant ALTER still block queries?

The 8.4 online DDL concurrency page says the commit phase upgrades the metadata lock (MDL) to exclusive "to evict the old table definition and commit the new one". The ALTER must first wait for every transaction holding a metadata lock on the table, and "a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table."

So the change takes milliseconds, but the wait does not. One session that ran BEGIN; SELECT ... FROM orders; and went idle holds a shared lock; the ALTER queues behind it, and every new query queues behind the ALTER. The lock_wait_timeout default is 31536000 seconds, one year.

Before you start, list the oldest open transactions:

SELECT trx_mysql_thread_id AS processlist_id,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       LEFT(trx_query, 80) AS current_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started
LIMIT 10;

Interpretation: an old trx_started with a NULL current_query is an idle transaction. It shows as Sleep in the process list while still holding its locks, and it is the usual cause of a stuck ALTER.

If an ALTER is already waiting, sys.schema_table_lock_waits names the blocker:

SELECT waiting_pid, waiting_query_secs, waiting_query,
       blocking_pid, blocking_lock_type,
       sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE object_schema = 'shop' AND object_name = 'orders';

Interpretation: rows whose waiting_query is an ordinary SELECT or INSERT are the queue. The row whose waiting query is the ALTER shows the real blocker in blocking_pid. The metadata lock instrument behind this view is enabled by default in 8.4.

SET SESSION lock_wait_timeout = 3;
ALTER TABLE shop.orders ADD COLUMN note VARCHAR(64) NULL, ALGORITHM=INSTANT;
-- If blocked for 3 seconds:
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Interpretation: the worst case is now a 3-second queue. Retry after a pause, and only on error 1205; any other error means the change is not possible as written.

Do replicas, backups, and online schema tools handle instant DDL?

Replicas

DDL is logged as a statement, and each replica runs the same ALTER, ALGORITHM clause included. The online DDL limitations page notes that an operation "must finish running on the source before it is run on the replica", which is a short delay for an instant change. The replica's applier needs the same exclusive metadata lock, so a long report query on a replica holds up replication. If a replica's table differs, for example a FULLTEXT index only on a reporting replica, the replayed ALGORITHM=INSTANT statement can fail there and stop the applier. Watch SHOW REPLICA STATUS and sys.schema_table_lock_waits on each replica until the change is applied.

Physical backups

Percona XtraBackup 8.0.29 refused to back up servers with instantly added or dropped columns, reporting "Found tables with row versions due to INSTANT ADD/DROP columns". Per Percona's page on that error, XtraBackup 8.0.32-25 allows instant columns for MySQL 8.0.32, while 8.0.31 and lower stay affected; the same page says XtraBackup 8.0 reached end of life in June 2026. Use the 8.4 series for 8.4 servers. For 9.7, Percona released XtraBackup 9.7.1-rc1 on 15 July 2026 as a release candidate; as of November 2026, check for a generally available build before relying on it. With any tool, restore a backup taken after instant changes and query the changed tables.

gh-ost

The gh-ost flag documentation describes --attempt-instant-ddl, disabled by default: gh-ost "will automatically fallback to the normal DDL process if the attempt to use instant DDL is unsuccessful", and "may need to acquire a metadata lock at the start of the operation". It tries the statement because "it is not reliable to parse the ALTER statement to determine if it is instant or not".

gh-ost \
  --host=replica-01.internal \
  --database=shop --table=orders \
  --alter="ADD COLUMN note VARCHAR(64) NULL" \
  --attempt-instant-ddl \
  --max-load=Threads_running=50 \
  --execute

Interpretation: if the instant attempt succeeds, gh-ost finishes without a row copy. If not, it runs its normal throttled copy and cut-over. Confirm flags with gh-ost --help for your version.

pt-online-schema-change

pt-online-schema-change always creates "an empty copy of the table to alter", copies rows in chunks, keeps the copy current with triggers, and swaps the tables. It does not try an instant path, so do not send instant-capable changes through it. It sets lock_wait_timeout=60 and innodb_lock_wait_timeout=1 by default so it loses lock contention instead of causing it.

pt-online-schema-change \
  --alter "MODIFY qty BIGINT NOT NULL" \
  --max-lag 5 \
  --dry-run \
  D=shop,t=orders

Interpretation: --dry-run creates and alters the new table without copying data or swapping, which validates the ALTER clause. Use --execute for the real run.

How do I roll out an instant schema change safely?

An instant change is low risk when the table qualifies and nothing holds locks. This runbook checks both before the one step that takes the exclusive lock.

When should I still use gh-ost or pt-online-schema-change?

The limitations page says native online DDL has "no mechanism to pause an online DDL operation or to throttle I/O or CPU usage", rollback "can be expensive", and long operations "can cause replication lag". The external tools cover those gaps.

gh-ost needs row-based binary logs, and its requirements page lists foreign keys and triggers as unsupported. Our posts on tables with triggers and expand-contract migrations cover patterns around those limits.

Frequently asked questions

Key takeaways

Plan MySQL schema changes with JusDB

Schema changes go wrong in the gap between what the developer expects and what the server picks: an "instant" change that rebuilds, or an instant change that queues the table behind an idle session. Knowing which path a statement takes before it reaches production removes most of that risk.

JusDB's MySQL consulting team can review planned ALTER TABLE statements, confirm which can use native online DDL with ALGORITHM=INSTANT or INPLACE, and plan gh-ost or pt-online-schema-change runs for large-table changes that still copy data.

Related reading

• gh-ost: trigger-free online MySQL schema migrations
• pt-online-schema-change for MySQL ALTER TABLE
• MySQL InnoDB lock monitoring: detecting and killing blocking transactions
• MySQL 8.4 Reference Manual: Online DDL Operations