Instant add column - PolarDB - Alibaba Cloud Documentation Center

Traditional methods of executing add column operations require rebuilding the entire table data, consuming significant system resources. PolarDB MySQL Edition clusters offer the instant add column feature, enabling rapid completion of add column operations on tables regardless of size.

The instant add column feature utilizes the INSTANT algorithm for DDL operations. It modifies only the metadata in the data dictionary, without altering or copying historical data or rebuilding the table. As a result, the process is not constrained by table size, and the DDL operation can be completed within seconds.

Prerequisites

The cluster version must be one of the following:

PolarDB MySQL Edition version 5.6, revision 5.6.1.0.43 or higher.

Note

The instant add column feature for version 5.6 of PolarDB MySQL Edition is currently in the grayscale phase. To enable it, please navigate to the Quota Center, locate the quota name using the quota ID polardb_mysql_iac_56, and click request in the corresponding operation column.
PolarDB MySQL Edition version 5.7, revision 5.7.1.0.6 or higher.

Note

To use this feature on the PolarDB MySQL Edition version 5.7 cluster, you must enable the loose_innodb_support_instant_add_column parameter.
PolarDB MySQL Edition Version 8.0.

Note

By default, the PolarDB MySQL Edition version 8.0 cluster supports the instant add column feature without requiring any parameter configuration.

You can confirm the revision version of the cluster by querying the version number.

Limits

The new column must be the last column in the table.
Adding a virtual column:
- PolarDB MySQL Edition versions 5.6 and 5.7 are not supported.
- PolarDB MySQL Edition version 8.0 is supported.
Instantly adding columns to partitioned tables:
- PolarDB MySQL Edition version 5.6 is not supported.
- PolarDB MySQL Edition supports versions 5.7 and 8.0.
  
  Note
  
  The instant add column feature for partitioned tables in PolarDB MySQL Edition version 5.7 is currently in the grayscale phase. To enable it, please visit the Quota Center, search for the quota name using the quota ID polarM_57_iac_on_partition_table, and click request in the corresponding operation column.
Tables with full-text indexes or columnstore indexes are not supported.
Tables with the implicit_primary_key option enabled and without a custom primary key are not supported.
Concurrently executing other DDL operations, such as adding indexes, with instant add column operations in the same SQL statement is not supported.

Usage method

Parameter configuration

PolarDB MySQL Edition versions 5.6 and 5.7 clusters: To utilize the instant add column feature, you can enable the parameter loose_innodb_support_instant_add_column.

Parameter	Level	Description

Parameter

Level

Description

loose_innodb_support_instant_add_column

Global

The switch for the instant add column feature, with the following value range:

ON: Enable the instant add column feature.
OFF (default): Disable the instant add column feature.

PolarDB MySQL Edition version 8.0 clusters: There is no need to configure this parameter to utilize the instant add column feature directly.

Usage syntax

To enforce the use of the instant add column feature, specify ALGORITHM=INSTANT in your statement. An example is as follows:
```
ALTER TABLE <table_name> ADD COLUMN <column_name> int, ALGORITHM=INSTANT;
```
Note

If the error ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. appears, the current add column operation cannot proceed with the INSTANT algorithm. Ensure the loose_innodb_support_instant_add_column parameter is enabled and review the limits.
If ALGORITHM is not specified or ALGORITHM=DEFAULT is used, PolarDB automatically selects the quickest algorithm to perform the add column operation. An example of the statement is as follows:
```
ALTER TABLE <table_name> ADD COLUMN <column_name> int, ALGORITHM=DEFAULT;
ALTER TABLE <table_name> ADD COLUMN <column_name> int;
```
Note

The selection priority for PolarDB algorithms is INSTANT, followed by INPLACE, and then COPY.

View information of columns added through INSTANT algorithm

PolarDB MySQL Edition versions 5.6 and 5.7 clusters: The INFORMATION_SCHEMA database now includes a new INNODB_SYS_INSTANT_COLUMNS table. This table provides details on columns that have been added using the INSTANT algorithm, including column names, ordinal numbers, and default values in binary format. To verify the information of newly added columns, you can use the following statement to view the table's contents.
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INSTANT_COLUMNS;
```
Note

After adding columns to a table using the INSTANT algorithm, if a table rebuild DDL operation like DROP COLUMN is executed, all INSTANT column records for that table will be purged from the INNODB_SYS_INSTANT_COLUMNS table.
PolarDB MySQL Edition version 8.0 clusters: You can retrieve the column information of a table using the statement below. If the has_default column in the query result shows a 1, this signifies that the column has been added using the INSTANT algorithm.
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "<database_name>/<table_name>");
```

Contact us

If you have any questions about DDL operations, contact us.