Description of the instant column addition feature for RDS MySQL - ApsaraDB RDS

The instant column addition feature of ApsaraDB RDS for MySQL lets you quickly add columns by changing only the table's metadata, which avoids rebuilding the entire table. The operation completes in seconds, regardless of the table's data volume. This feature uses minimal system resources, does not lock tables, and does not block business operations. It is ideal for scenarios that require frequent table schema extension and high business continuity.

Feature description

Instant column addition for ApsaraDB RDS for MySQL optimizes the ADD COLUMN operation by changing metadata in the data dictionary. This feature avoids modifying or rebuilding the entire table, which is a requirement for traditional Data Definition Language (DDL) operations. As a result, the column addition completes in seconds, regardless of the data volume of the table.

The following table compares instant column addition with traditional column addition:

Comparison Item	Traditional column addition (Copy or Inplace mode)	RDS instant column addition (Instant mode)
Column addition time	Requires a full table rebuild. The time required is proportional to the table size.	The operation modifies only metadata and completes in seconds.
Resource consumption	Temporarily uses a large amount of system resources, such as I/O and memory.	Uses almost no extra resources.
Business impact	May block online business operations in scenarios with long-running transactions or high concurrency.	Does not lock tables or block operations.
Table size limit	Does not support fast column addition for large tables.	Supports fast column addition for tables of any size.

Applicability

Your instance must meet the following version requirements to use the instant column addition feature. If your instance does not meet the requirements, upgrade the minor engine version.

MySQL 8.0: All minor engine versions are supported.
MySQL 5.7: Minor engine version 20250331 or later.

The instant column addition feature has the following limits:

Engine limit: Only the InnoDB engine is supported.
Table type limit: This feature is not supported for compressed tables, tables with full-text indexes, or temporary tables.
Operation limit: Merging multiple operations is not supported. For example, you cannot add a column and an index at the same time.
Read-only instance limit: If you use instant column addition on a high-availability primary instance that has read-only instances attached, you must set the loose_innodb_instant_ddl_enabled parameter to ON on both the primary instance and the read-only instances. Otherwise, replication to the read-only instances is interrupted.

Default column position:

MySQL version	Minor engine version	Column position
5.7	20250331 or later	The last column is selected by default.
8.0	Earlier than 20230630	The default is the last column.
8.0	20230630 or later	You can specify the position of the new column.

Enable instant column addition

Instant column addition is enabled by default in MySQL 8.0, and you can use this feature without modifying any parameters. For MySQL 5.7, you must follow the steps below to enable instant column addition:

Go to the RDS Instances page, select a region, and then click the ID of the target instance.
In the left navigation pane, click Parameters.
On the Editable Parameters tab, find the loose_innodb_instant_ddl_enabled parameter and set the Running Parameter Value to ON.
Note
Changes to the loose_innodb_instant_ddl_enabled parameter take effect immediately without restarting the instance.
Click Submit Parameters. In the dialog box, select a time range for the change to take effect and click OK.

Related operations

Use instant column addition

To force instant column addition, specify ALGORITHM=INSTANT:

ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> <constraints>, ALGORITHM = INSTANT;

If you do not specify ALGORITHM, ApsaraDB RDS for MySQL selects the optimal mode at runtime based on the current conditions:
```
ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> <constraints>;
```

View tables where instant column addition was performed

MySQL 5.7:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE INSTANT_COLS > 0;

MySQL 8.0:

-- For minor engine versions earlier than 20230630
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE INSTANT_COLS > 0;

-- For minor engine versions 20230630 or later
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

View columns added using instant column addition

MySQL 5.7:
In MySQL 5.7, the INNODB_SYS_INSTANT_COLUMNS table is added to the INFORMATION_SCHEMA database. You can run the following SQL statement to view information about columns that were added using the instant column addition feature.
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INSTANT_COLUMNS WHERE TABLE_ID = 
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME = "<database_name>/<table_name>");
```
MySQL 8.0:
You can run the following SQL statement to view column information for the table. If the value of the HAS_DEFAULT column in the query result is 1, the column was added using the instant column addition feature.
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = 
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "<database_name>/<table_name>");
```