Understanding MySQL Instant Add Column: How It Works and Its Limitations

In the previous article "MySQL DDL Why Is It Costly?" we introduced that adding a column traditionally requires rebuilding the entire table, which incurs heavy I/O and time consumption.

Instant Add Column ("立刻加列") is a solution introduced by the Tencent team that allows adding a column instantly by only updating the data dictionary without rebuilding the table.

In the traditional case, adding a column requires:

Increasing each row’s length to accommodate the new column data.

Rebuilding the table space for the changed rows (the gray‑blue part in the diagram).

Updating the column definition in the data dictionary.

This process is expensive because it involves massive I/O and time.

Instant Add Column works as follows (see diagram):

Only the data dictionary is changed: a new column definition is added and a default value is set.

When reading rows after the instant addition:

The existing rows still have only the original three columns; MySQL appends the default value of the new column to the result set.

For rows written after the change, a new instant flag and a "column count" field are stored, allowing the engine to know the row’s format.

Reading a row with the new format involves checking the instant flag in the row header to determine the column count and then reading the appropriate number of columns.

When incompatible DDL operations (e.g., dropping a column, adding a primary key) are performed, the table must be rebuilt, losing the instant advantage.

The limitations of Instant Add Column are:

The new column can only be added at the end of the table; column ordering cannot be specified.

It cannot add primary‑key columns because that would require a table rebuild.

Compressed table formats are not supported.

In summary, the instant method is efficient because it does not modify row structures, it "fakes" the new column for old rows, and it uses a new row format with an instant flag and column‑count field to distinguish old and new data.

However, the "instant" nature does not mean zero cost; a lock on the data dictionary is still required, and incompatible DDL will trigger a full rebuild.

Open questions remain, such as whether the fake‑column approach can be extended to support more complex DDL operations without rebuilding.