Instant ADD COLUMN - PolarDB - Alibaba Cloud Documentation Center

This topic describes the Instant ADD COLUMN feature and how to use the feature to instantly add columns to a table.

When you add a column to a table in a traditional manner, data nodes must rebuild data in all physical shards. This process requires a large amount of system resources. PolarDB-X provides the Instant ADD COLUMN feature. The feature adds a column to a table by updating only the definition of the table and keeping the existing data unchanged. The feature ensures that you can quickly add columns to a table regardless of the table size.

Prerequisites

Your instance is a PolarDB-X 2.0 instance that runs V5.4.13-16504348 or later.

By default, the feature is disabled for instances whose data node (DN) version is 5.7. To enable the feature for such instances, you must set the support_instant_add_column parameter to ON. For more information, see the "Usage" section of this topic.
For instances whose DN version is 8.0, the feature is automatically enabled.

Note

To obtain information about the DN version, log on to the PolarDB-X console, go to the Configuration Management > Basic Information page of the instance, and then view the value of the Compatibility parameter in the Configuration Information section.

Limits

You cannot use the feature to add a primary key column.
You cannot use the feature to add a column to a table for which the ROW_FORMAT attribute is set to COMPRESSED.
Note
Execute the SHOW CREATE TABLE statement to check whether the ROW_FORMAT attribute of a table is set to COMPRESSED.
You cannot use the feature to add a column to a table that contains a full-text index.
You cannot use the feature to add a column to a table that contains physical partitions.
Note
Execute the SHOW CREATE TABLE statement to check whether a table contains the LOCAL PARTITION BY clause.
For instances whose DN version is 5.7, you can execute the INSTANT ADD COLUMN statement to add a column only at the end of existing columns.
For the feature to take effect, the ALTER TABLE statement must contain only operations that add columns. If other operations are included in the same ALTER TABLE statement, the feature does not take effect.

Usage

Parameters (DN version: 5.7)

Parameter

Level

Description

loose_innodb_support_instant_add_column

Global

Specifies whether to enable the Instant ADD COLUMN feature. Valid values:

ON
OFF (default)

To enable the Instant ADD COLUMN feature, perform the following operations:

Log on to the PolarDB-X console, go to the Configuration Management > Parameter Settings > Storage Layer page, and then set the loose_innodb_support_instant_add_column parameter to ON.

Statements

To enable the Instant ADD COLUMN feature when you add a column, specify ALGORITHM = INSTANT in the ALTER TABLE ADD COLUMN statement. If you specify other algorithms such as NPLACE or COPY, the Instant ADD COLUMN feature is disabled.

By default, the Instant ADD COLUMN feature is enabled for instances whose DN version is 8.0. The feature is enabled even if you do not specify ALGORITHM=INSTANT in the ALTER TABLE ADD COLUMN statement. Sample statement:
```
ALTER TABLE test ADD COLUMN col1 int;
```
To forcibly enable the Instant ADD COLUMN feature, specify ALGORITHM=INSTANT in the ALTER TABLE ADD COLUMN statement. Sample statement:
```
ALTER TABLE test ADD COLUMN col2 int, ALGORITHM=INSTANT;
```
To disable the Instant ADD COLUMN feature, specify ALGORITHM=INPLACE or ALGORITHM=COPY in the ALTER TABLE ADD COLUMN statement. Sample statement:
```
ALTER TABLE test ADD COLUMN col3 int, ALGORITHM=INPLACE;
ALTER TABLE test ADD COLUMN col4 int, ALGORITHM=COPY;
```