Instant Column Addition in MySQL 8.0: Principles, Usage, Performance and Limitations

Introduction

Changing table structures is a common requirement in production, and in MySQL it is performed with ALTER statements, which belong to DDL operations.

DDL Pain Points

Typical DDL actions such as adding or dropping indexes or columns often block writes and can cause metadata lock waits, especially in master‑slave setups where long‑running DDL may create noticeable replication lag.

Previous Solutions

Older MySQL versions (5.5 and earlier) used a copy algorithm that rebuilt the whole table, consuming extra disk space and blocking writes. MySQL 5.6/5.7 introduced Online‑DDL with an inplace‑rebuild algorithm, which avoided write blocking but still required significant time and space.

Third‑Party Tools

Tools like Percona's pt‑online‑schema‑change , Facebook's online‑schema‑change , and GitHub's gh‑ost create a new table, sync data, and rename tables to avoid blocking, though they still use an ALTER at the final switch and can encounter metadata locks.

MySQL 8.0 Instant Column Addition

Since MySQL 8.0.12, the instant algorithm allows adding a column without rebuilding the table; only metadata is updated.

Principle

The engine stores extra flags in each row to indicate whether the row was created after the instant column was added, and records the total number of columns. When reading rows, the server consults the metadata to synthesize the missing instant column values.

Supported Statements

Select – checks the flag and may fetch instant column data from metadata.

Insert – records flag and column count.

Delete – behaves as before.

Update – may be rewritten to delete + insert if the instant‑column count changes.

Usage

In MySQL 8.0.12 and later, the following ALTER TABLE uses the instant algorithm by default:

Add column (cannot drop a regular column instantly).

Add or drop a virtual column.

Change a column default value.

Modify ENUM or SET definitions.

Change index type (B‑tree, hash).

Rename table with ALTER syntax.

Example to view instant‑column information:

SELECT * FROM information_schema.innodb_tables WHERE table_id = 1192;

Example of adding an instant column:

ALTER TABLE t1 ADD COLUMN c4 INT UNSIGNED NOT NULL DEFAULT 1, ALGORITHM=INSTANT;

After execution, INSTANT_COLS for the table changes from 0 to the number of instant columns added.

Limitations

If an ALTER mixes instant‑supported and unsupported operations, the whole statement fails.

Instant columns can only be added at the end of the table; the AFTER clause is not allowed.

Compressed InnoDB tables, tables with full‑text indexes, temporary tables, and tables using the old file‑per‑table format cannot use instant.

Instant columns are not compatible with older MySQL versions (physical backups cannot restore them).

Corrupt tables or indexes must be fixed/rebuilt before upgrading to use instant.

Version Comparison

The table below (originally an image) compares metadata lock (MDL) behavior for column addition across MySQL 5.5 (copy), 5.6/5.7 (inplace‑rebuild), and 8.0 (instant).

Simple Performance Test

Test environment: single instance, 40 CPU cores, 256 GB RAM, SSD; 10 million rows; no external tools; comparing copy, inplace‑rebuild, and instant algorithms on MySQL 8.0.12.

Results show instant finishes almost instantly, copy takes ~130 s, and inplace takes ~35 s.

Related Articles

MySQL 8.0 New Feature: Clone Plugin

MySQL 8.0 New Feature: NOWAIT and SKIP LOCK