PolarDB:Instant column addition at the end of a table

Force the INSTANT algorithm

Specify ALGORITHM=INSTANT to ensure the operation uses the INSTANT algorithm:

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

If the cluster or table does not support instant column addition, the statement returns:

ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

If you see this error, check that loose_innodb_support_instant_add_column is enabled and review the Limitations.

Let PolarDB select the algorithm automatically

Omit the ALGORITHM clause or specify ALGORITHM=DEFAULT to let PolarDB select the fastest available algorithm. PolarDB evaluates algorithms in this order: INSTANT > INPLACE > COPY.

ALTER TABLE <table_name> ADD COLUMN <column_name> int, ALGORITHM=DEFAULT;
ALTER TABLE <table_name> ADD COLUMN <column_name> int;

PolarDB for MySQL 5.6 and 5.7

Query the INNODB_SYS_INSTANT_COLUMNS table in INFORMATION_SCHEMA. This table stores column names, ordinal positions, and default values (in binary format) for columns added using the INSTANT algorithm:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INSTANT_COLUMNS;

PolarDB for MySQL 8.0

Query INFORMATION_SCHEMA.INNODB_COLUMNS and check the has_default column. A value of 1 indicates the column was 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>"
);