RDS MySQL Instant ADD COLUMN feature description - ApsaraDB RDS

RDS for MySQL supports Instant ADD COLUMN, a feature that adds columns in seconds by modifying only the metadata. It avoids full table rebuilds and works regardless of table size. Instant ADD COLUMN consumes almost no additional system resources, does not lock tables, and does not block operations. Use it when you frequently extend table schemas and require high business continuity.

Feature description

RDS for MySQL supports Instant ADD COLUMN to optimize the ADD COLUMN operation. Instead of changing or rebuilding all table data, it modifies only the metadata in the data dictionary. This enables you to add columns in seconds, regardless of table size.

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

Comparison item	Traditional column addition (COPY or INPLACE mode)	RDS Instant ADD COLUMN (INSTANT mode)
Time to add columns	Requires a full table rebuild. Time increases with table size.	Modifies only metadata. Completes in seconds.
Resource consumption	Temporarily uses large amounts of system resources, such as I/O and memory.	Uses almost no additional resources.
Business impact	May block online workloads during long-running transactions or under high concurrency.	Does not lock tables or block operations.
Table size limit	Fast column addition is not supported for large tables.	Works on tables of any size.

Applicable scope

To use Instant ADD COLUMN, your RDS instance must meet one of the following version requirements. If your minor engine version does not meet the requirements, you can update the minor engine version.

MySQL 8.0: All minor engine versions are supported.
MySQL 5.7: The minor engine version must be 20250331 or later.

Instant ADD COLUMN has the following limits:

Engine: Only the InnoDB engine is supported.
Table type: Compressed tables, tables with full-text indexes, and temporary tables are not supported.
Operation: Multiple operations cannot be combined. For example, you cannot create an index while adding a column.
Read-only instance: If your primary instance uses high availability and has read-only instances attached, set loose_innodb_instant_ddl_enabled to ON on both the primary and read-only instances. Otherwise, replication may stop on the read-only instances.

Default column position:

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

For MySQL 5.7 or MySQL 8.0 (earlier than 20230630), ensure that no implicit primary key exists in the table.

Enable Instant ADD COLUMN

MySQL 8.0 enables Instant ADD COLUMN by default. You do not need to change any parameters. For MySQL 5.7, follow these steps:

Go to the RDS Instances page. Select the region where your instance resides. Then click the ID of your target instance.
In the navigation pane on the left, click Parameters.
On the Modifiable Parameters tab, search for loose_innodb_instant_ddl_enabled. In the Running Value column, set the value to ON.
Note
Changing loose_innodb_instant_ddl_enabled takes effect immediately. No restart is required.
Click Apply Changes. In the dialog box, select when the change should take effect. Then click OK.

Related operations

Use Instant ADD COLUMN

Force Instant ADD COLUMN by specifying ALGORITHM=INSTANT:

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

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

View tables that used Instant ADD COLUMN

MySQL 5.7:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE INSTANT_COLS > 0;

MySQL 8.0:

-- Minor engine version earlier than 20230630
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE INSTANT_COLS > 0;

-- Minor engine version 20230630 or later
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

View columns added by Instant ADD COLUMN

MySQL 5.7:

MySQL 5.7 adds the INNODB_SYS_INSTANT_COLUMNS table to the INFORMATION_SCHEMA database. Run this SQL to view columns added by 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:

Run this SQL to view column details for a table. If the HAS_DEFAULT column shows 1, the column was added 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>");

FAQ

Q1: My instance meets the requirements for Instant ADD COLUMN, but I get this error when adding a column: Feature not supported: 1845 ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

Cause: If a table has no primary key or unique key, RDS for MySQL adds an implicit primary key to improve replication efficiency. By default, this implicit primary key is the last column. That forces Instant ADD COLUMN to place the new column at a specific position. However, MySQL 5.7 and MySQL 8.0 (earlier than 20230630) do not support placing columns at specific positions.

Solution: For MySQL 5.7 or MySQL 8.0 (earlier than 20230630), make sure no implicit primary key exists in the table.