This topic describes Instant ADD COLUMN and how to use this 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 supports Instant ADD COLUMN. When you add a column by using this feature, you can modify only the definition of the table without the need to modify the existing data. This way, you can add a column to a table regardless of the size of the table.

Prerequisites

  • A PolarDB-X 2.0 instance of V5.4.13-16504348 or a later version is created. PolarDB-X 2.0 V5.4.13-16504348 and later versions support Instant ADD COLUMN.
  • Instant ADD COLUMN is enabled. By default, Instant ADD COLUMN is disabled.

Limits

  • You cannot use this feature to add a primary key column.
  • You cannot use this feature to add a column to a table for which the ROW_FORMAT attribute is set to COMPRESSED.
    Note You can execute the SHOW CREATE TABLE statement to query whether the ROW_FORMAT attribute of a table is set to COMPRESSED.
  • You cannot use this feature to add a column to a table that has a full-text index.
  • This feature takes effect when you use the ALTER TABLE statement only to add a column. If you specify other operations in the ALTER TABLE statement, the Instant ADD COLUMN clause does not take effect.

Enable Instant ADD COLUMN

Connect to the PolarDB-X instance and execute the following statement to enable Instant ADD COLUMN:

SET enable_set_global=TRUE;
SET global support_instant_add_column=ON;

Parameters

Parameter Level Description
support_instant_add_column Global Specifies whether to enable Instant ADD COLUMN. Valid values:
  • ON: enables Instant ADD COLUMN.
  • OFF: disables Instant ADD COLUMN. This value is the default value.

Use Instant ADD COLUMN

In the following sample ALTER TABLE statements, Instant ADD COLUMN is used.
  • If an ALTER TABLE statement that does not include the ALGORITHM parameter is executed, Instant ADD COLUMN is used. Sample statement:
    ALTER TABLE test ADD COLUMN col2 int;
  • If an ALTER TABLE statement in which the ALGORITHM parameter is set to DEFAULT is executed, Instant ADD COLUMN is used. Sample statement:
    ALTER TABLE test ADD COLUMN col3 int, ALGORITHM=DEFAULT;
  • If an ALTER TABLE statement in which the ALGORITHM parameter is set to INSTANT is executed, Instant ADD COLUMN is used. Sample statement:
    ALTER TABLE test ADD COLUMN col1 int, ALGORITHM=INSTANT;
Note
If you specify other values, such as INPLACE or COPY, as the value of the ALGORITHM parameter, Instant ADD COLUMN is not used. PolarDB-X uses a common method to add the specified column. Sample statements:
ALTER TABLE test ADD COLUMN col4 int, ALGORITHM=INPLACE;
ALTER TABLE test ADD COLUMN col5 int, ALGORITHM=COPY;