All Products
Search

This Product

    PolarDB:What is the Instant modify column feature of PolarDB?

    Document Center

    PolarDB:What is the Instant modify column feature of PolarDB?

    Last Updated:Mar 30, 2026

    Standard MySQL uses the COPY algorithm to modify a column's data type, which rebuilds the entire table on a single thread while blocking all writes. For large tables, this can take minutes to hours. PolarDB for MySQL's Instant Modify Column feature avoids this by updating only the table's metadata — the operation completes in seconds regardless of table size.

    Instant Modify Column is in canary release. To use it, go to Quota Center, find the quota polarm_82_instant_modify_column, and click Apply.

    Supported versions

    Parameters

    Parameter Level Description Default
    loose_innodb_polar_instant_modify_column_enable Global Enables or disables Instant Modify Column. Valid values: ON, OFF OFF
    loose_innodb_polar_instant_modify_column_max_times Global Maximum number of times the feature can modify column types on a single table. Range: 1–64 16

    Supported operations

    Instant Modify Column supports only increasing column length for the following data types. Use this table to verify whether your operation is supported before running it.

    Column type Supported change Supported example Unsupported example
    TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT Increase to a wider integer type INT → BIGINT BIGINT → INT
    VARCHAR(N) Increase length (N to N+M, where M > 0) VARCHAR(50) → VARCHAR(300) VARCHAR(300) → VARCHAR(50)
    CHAR(N) Increase length (N to N+M, where M > 0) CHAR(30) → CHAR(50) CHAR(50) → CHAR(30)

    The following additional restrictions apply:

    Restriction Details
    Row format Only COMPACT or DYNAMIC row format
    Indexed columns Columns with an index cannot be modified
    Partitioned tables Columns in partitioned tables cannot be modified
    Full-text or spatial indexes Columns in tables with full-text or spatial indexes cannot be modified
    Combined DDL Instant Modify Column cannot be combined with other DDL operations in the same statement

    If your operation does not meet these conditions and you specify ALGORITHM=INSTANT, the following error is returned:

    ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY/INPLACE
    Tip: Before running a DDL statement, use EXPLAIN DDL to check whether the INSTANT algorithm is supported for that operation.

    Modify a column type instantly

    Force the INSTANT algorithm (recommended)

    Specify ALGORITHM=INSTANT to enforce Instant Modify Column. If the operation cannot use INSTANT, an error is returned immediately rather than silently falling back to a slower algorithm.

    CREATE TABLE t(a int);

-- Verify the current table structure
SHOW CREATE TABLE t;
-- Output:
-- CREATE TABLE `t` (
--   `a` int(11) DEFAULT NULL
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Modify the column type using the INSTANT algorithm
ALTER TABLE t MODIFY COLUMN a bigint DEFAULT NULL, ALGORITHM=INSTANT;

    Let PolarDB choose the algorithm automatically

    Omit the algorithm clause or specify ALGORITHM=DEFAULT to let PolarDB for MySQL select the most efficient algorithm automatically.

    ALTER TABLE test.test_table MODIFY COLUMN test_column bigint, ALGORITHM=DEFAULT;
ALTER TABLE test.test_table MODIFY COLUMN test_column bigint;

    PolarDB for MySQL evaluates algorithms in the following priority order:

    Priority Algorithm Behavior
    1 (highest) INSTANT Updates only metadata. Completes in seconds, regardless of table size. No table rebuild.
    2 INPLACE Allows concurrent reads and writes during the DDL operation. Parallel DDL can further accelerate it.
    3 (lowest) COPY Rebuilds the entire table. The table is read-only during the operation.

    View instantly modified columns

    The INFORMATION_SCHEMA.INNODB_INSTANT_COLUMNS table records all columns in the current cluster that have been modified using Instant Modify Column. Only columns modified by this feature appear in the table.

    SELECT * FROM INFORMATION_SCHEMA.INNODB_INSTANT_COLUMNS;

    The table schema:

    SHOW CREATE TABLE INFORMATION_SCHEMA.INNODB_INSTANT_COLUMNS;
-- CREATE TEMPORARY TABLE `INNODB_INSTANT_COLUMNS` (
--   `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
--   `TABLE_NAME` varchar(193) NOT NULL DEFAULT '',
--   `SCHEMA_NAME` varchar(193) NOT NULL DEFAULT '',
--   `COLUMN_NAME` varchar(193) NOT NULL DEFAULT '',
--   `MTYPE` int(11) NOT NULL DEFAULT '0',
--   `PRTYPE` int(11) NOT NULL DEFAULT '0',
--   `LEN` int(11) NOT NULL DEFAULT '0',
--   `HAS_DEFAULT` int(1) NOT NULL DEFAULT '0',
--   `DEFAULT_VALUE` mediumblob,
--   `CURRENT_VERSION` int(11) NOT NULL DEFAULT '0',
--   `OLD_VERSION_INFO` blob
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    Field descriptions:

    Field Description
    TABLE_ID Table ID
    TABLE_NAME Table name
    SCHEMA_NAME Schema name
    COLUMN_NAME Column name
    MTYPE Column type
    PRTYPE Precise column type or attributes
    LEN Column length
    HAS_DEFAULT Whether the column has a default value
    DEFAULT_VALUE Default value of the column
    CURRENT_VERSION Version information of the column
    OLD_VERSION_INFO Historical type information of the column
    If a DDL operation that requires a full table rebuild (such as DROP COLUMN) is performed on a column previously modified by Instant Modify Column, that column's record is removed from INNODB_INSTANT_COLUMNS.