Instant add column for ApsaraDB RDS for MySQL - ApsaraDB RDS

The Instant ADD COLUMN feature for ApsaraDB RDS for MySQL quickly adds columns by modifying only the metadata. This method avoids a full table rebuild, completes the operation in seconds regardless of table size, and uses minimal system resources. It does not lock tables or block business operations, making it ideal for scenarios requiring frequent schema extensions and high business continuity.

How it works

Instant ADD COLUMN for ApsaraDB RDS for MySQL optimizes the ADD COLUMN operation by modifying metadata in the data dictionary. This feature avoids the full data copy or table rebuild required by traditional DDL operations, and adds columns in seconds, regardless of table size.

The following table compares the core advantages of Instant ADD COLUMN with traditional methods.

Comparison item	Traditional method (COPY or INPLACE algorithm)	Instant add column (INSTANT algorithm)
Time to add a column	Requires a full table rebuild, which takes time proportional to the table size.	Modifies only metadata, completing the operation in seconds.
Resource consumption	Temporarily consumes significant system resources, such as I/O and memory.	Consumes negligible additional resources.
Business impact	May block online operations during long-running transactions or in high-concurrency scenarios.	Does not cause table locking or blocking.
Table size limitation	Slow when adding columns to large tables.	Quickly adds columns to tables of any size.

Prerequisites

To use the Instant ADD COLUMN feature, your instance must meet the following version requirements. If your minor engine version is not supported, you can update the minor engine version.

MySQL 8.4
MySQL 8.0
MySQL 5.7 with a minor engine version of 20250331 or later.

The following limitations apply when you use the Instant ADD COLUMN feature:

Engine limitation: Only the InnoDB engine is supported.
Table type limitation: Compressed tables, tables with full-text indexes, and temporary tables are not supported.
Operation limitation: You cannot combine multiple operations in a single statement, such as adding a column and creating an index.
Read-only instance limitation: If you use Instant ADD COLUMN on a high-availability primary instance that has read-only instances, you must set the loose_innodb_instant_ddl_enabled parameter to ON on both the primary instance and all its read-only instances. Otherwise, replication to the read-only instances will be interrupted.

Default column position:

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

When you use Instant ADD COLUMN for instances of the following versions, you must ensure that the data table does not contain an implicit primary key:
- MySQL 8.4
- MySQL 8.0 with a minor engine version earlier than 20230630
- MySQL 5.7

Enable instant add column

MySQL 8.0 and 8.4 enable Instant ADD COLUMN by default. You can use this feature without modifying any parameters. For MySQL 5.7, refer to the following steps to enable Instant ADD COLUMN:

Go to the Instances page, select a region, and then click the ID of the target instance.
In the left-side navigation pane, click Parameters.
On the Editable Parameters tab, find the loose_innodb_instant_ddl_enabled parameter. In the Running Parameter Value column, set the value to ON.
Note
The change to the loose_innodb_instant_ddl_enabled parameter takes effect immediately without requiring an instance restart.
Click Apply Changes. In the dialog box that appears, specify when you want the change to take effect, and then click OK.

Related operations

Use instant add column

Use ALGORITHM=INSTANT to explicitly enable Instant ADD COLUMN:

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

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

View modified tables

MySQL 5.7:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE INSTANT_COLS > 0;

MySQL 8.0 and 8.4:

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

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

View added columns

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 columns added by the Instant ADD COLUMN 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 and 8.4:
Run the following SQL statement to view a table's column information. A value of 1 in the HAS_DEFAULT column indicates the column was added using the Instant ADD COLUMN feature.
```
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 still receive the following error when I try to add a column: "Feature not supported: 1845 ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE"

Cause: If a table does not have a primary key or a unique key, ApsaraDB RDS for MySQL adds an implicit primary key to the end of the table to ensure replication efficiency. This effectively turns the ADD COLUMN statement into a positional operation, because the implicit primary key must remain the last column. However, MySQL 5.7 and MySQL 8.0 instances with minor engine versions earlier than 20230630 do not support positional column addition with ALGORITHM=INSTANT. This conflict results in the error.

Solution: When you use Instant ADD COLUMN on a MySQL 5.7 or MySQL 8.0 instance with a minor engine version earlier than 20230630, make sure that the target table does not have an implicit primary key.