ApsaraDB RDS for MySQL supports Instant ADD COLUMN - ApsaraDB RDS

ApsaraDB RDS for MySQL supports the Instance ADD COLUMN statement to optimize column addition. This statement allows you to add columns within seconds regardless of the data volume and frees you from recreating a table. The Instant ADD COLUMN statement consumes few additional system resources and does not cause table locking or blocking. The statement is suitable in scenarios in which table schemas must be frequently extended and high business continuity is required.

Feature description

ApsaraDB RDS for MySQL supports the Instance ADD COLUMN statement to optimize the ADD COLUMN operation. The optimization is implemented by modifying the metadata of a data dictionary. This statement allows you to add columns within seconds regardless of the data volume. This frees you from executing traditional DDLs to modify the entire table or recreate a table.

The following table compares Instant ADD COLUMN and traditional column addition.

Comparison item	Traditional column addition (COPY or INPLACE mode)	Instant ADD COLUMN (INSTANT mode)
Time to add columns	Full table data needs to be rebuilt. The time to rebuild full table data is proportional to the table size.	Only metadata is modified. The modification can be complete within seconds.
Resource consumption	A large number of system resources, such as I/O and memory resources, are temporarily occupied.	Few additional resources are occupied.
Business impact	Online workloads may be blocked by long-running transactions or in high concurrency scenarios.	No table locking or blocking occurs.
Table size limit	Columns cannot be quickly added to large tables.	Columns cannot be quickly added to tables regardless of the table sizes.

Prerequisites

The minor engine version of your RDS instance must meet one of the following conditions. If the minor engine version does not meet the requirements, you can update the minor engine version.

If your RDS instance runs MySQL 8.0, all minor engine versions are supported.
If your RDS instance runs MySQL 5.7, the minor engine version must be later than or equal to 20250331.

Limits

Engine: Only the InnoDB engine is supported.
Table type: Compressed tables, tables on which full-text indexes are created, or temporary tables are not supported.
Operation: Multiple operations cannot be merged. For example, you cannot create indexes when you add columns.

Column position:

MySQL version	Minor engine version	Column position
MySQL 5.7	Later than or equal to 20250331	By default, the new column is the last column.
MySQL 8.0	Earlier than 20230630	By default, the new column is the last column.
MySQL 8.0	Later than or equal to 20230630	When you add a column, you can specify the position for the column.

Enable Instant ADD COLUMN

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which your RDS instance resides. Then, find the RDS instance and click the ID of the RDS instance.
In the left-side navigation pane, click Parameters.
On the Modifiable Parameters tab of the page that appears, find the loose_innodb_instant_ddl_enabled parameter, change the value to ON in the Running Value column, and then click OK.
Click Apply Changes. In the dialog box that appears, select the time range for the new value to take effect and then click OK.
Note
The modification of the loose_innodb_instant_ddl_enabled parameter immediately takes effect without the need to restart the RDS instance.

Related procedures

Use Instant ADD COLUMN

Important

After you execute the Instant ADD COLUMN statement, if another DDL triggers a table rebuild, the information about the column that is added by using the Instant ADD COLUMN statement is deleted.

You can use the ALGORITHM=INSTANT setting to forcefully enable Instant ADD COLUMN.

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

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

View tables on which Instant ADD COLUMN is executed

MySQL 5.7:

SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE INSTANT_COLS > 0;

MySQL 8.0:

-- The minor engine version is earlier than 20230630.
SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE INSTANT_COLS > 0;

-- The minor engine version is later than or equal to 20230630.
SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

View columns that are added by using Instant ADD COLUMN

MySQL 5.7:
In MySQL 5.7, the INNODB_SYS_INSTANT_COLUMNS table is added to the INFORMATION_SCHEMA database. You can execute the following statements to view information about the columns that are added by using Instant ADD COLUMN:
```
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 execute the following SQL statements to view column information about a table. If 1 is displayed in the HAS_DEFAULT column in the query result, the column is added by using Instant ADD COLUMN.
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = 
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "<database_name>/<table_name>");
```