All Products
Search

This Product

    PolarDB:Online DDL

    Document Center

    PolarDB:Online DDL

    Last Updated:Apr 02, 2026

    Schema changes on a live database carry risk: lock the table for too long and your application stalls. Online DDL in PolarDB-X minimizes table locking so DDL operations and DML transactions run concurrently, keeping your database responsive during schema changes.

    Prerequisites

    Before running Online DDL operations, confirm the following:

    How it works

    PolarDB-X executes DDL in two ways depending on the operation type.

    Physically executed DDL is pushed down to data nodes. The compute node handles shard-level forwarding, coordination, and metadata management. Operations in this category — CREATE LOCAL INDEX, ALTER/ADD COLUMN, and CREATE/DROP TABLE — follow the same online execution principles as MySQL DDL.

    Logically executed DDL runs entirely on compute nodes. When a logically executed DDL operation starts, the system creates a temporary table, copies existing data into it, and simultaneously captures incremental DML updates. Once both historical and incremental data are synchronized, the system switches traffic from the original table to the temporary table — without locking it. Operations in this category include GLOBAL INDEX, CREATE/DROP PARTITION, CREATE/DROP PRIMARY KEY, and Change the column types without locking tables.

    How each DDL type affects your workload

    To evaluate the impact of a DDL operation on your database, check three things:

    Table locking

    • Online DDL blocks DML for only a few seconds during the final table switch. DML runs freely for most of the execution period.

    • Non-online DDL locks the table for the entire duration.

    Data backfilling

    • Operations that only change metadata — such as INSTANT ADD COLUMN and RENAME TABLE — complete instantly with no backfill.

    • DROP TABLE and DROP INDEX modify metadata and remove physical files quickly, also with no backfill.

    • All other DDL operations copy original data into new clustered indexes or secondary indexes. Execution time depends on data volume, concurrency, and instance specifications.

    Synchronous multi-write

    Logically executed DDL operations that backfill data must also write concurrent DML traffic to the temporary table. Because these multi-writes use distributed transactions to ensure atomicity, DML operations run as logical operations during this period — which reduces online DML performance.

    To minimize this impact, PolarDB-X implements asynchronous multi-write for MOVE/SPLIT PARTITION, Change the column types without locking tables, and REBALANCE. DML traffic is cached and replayed after backfilling completes, reducing conflicts and shortening the performance degradation window.

    For physically executed DDL that backfills data without locking, native asynchronous multi-write is handled automatically via online logs. Operations that don't backfill data require no multi-write.

    Online DDL feature reference

    Important

    When using MODIFY COLUMN or CHANGE COLUMN, always specify the complete column attributes. Omitting attributes can cause unexpected column modifications. Run SHOW FULL CREATE TABLE TABLE_NAME to view complete column definitions before making changes.

    Index operations

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Create a local secondary index (LSI) No No No Yes —
    Delete an LSI No No No No —
    Rename an LSI No No No No —
    Create a global secondary index (GSI) Yes No Yes Yes No
    Delete a GSI Yes No No No —
    Rename a GSI Yes No No No —

    Table operations

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Modify the row format (ROW_FORMAT) No No Yes Yes —
    OPTIMIZE TABLE No No Yes Yes —
    Rename a table No No No No —
    Modify the default character set No No Yes Yes —
    Convert the character set No Yes Yes Yes —
    Truncate a table without GSIs (TRUNCATE TABLE WITHOUT GSI) No No Yes No —
    Truncate a table with GSIs (TRUNCATE TABLE WITH GSI) Yes No Yes No —

    Partition operations

    The online DDL features in this table apply to both table groups and tables.
    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Migrate a partition Yes No Yes (relevant partitions only) Yes Yes*
    Split a partition Yes No Yes (relevant partitions only) Yes
    Merge partitions Yes No Yes (relevant partitions only) Yes No
    Add a partition Yes No No No† —
    Delete a partition Yes No No No‡ —
    Reorganize partitions Yes No Yes (relevant partitions only) Yes No
    Clear a partition Yes No No No —
    Rename a partition Yes No No No —
    Modify a partition Yes No Yes (relevant partitions only) Yes No

    \* Async multi-write conditions for migrate partition:

    • Logical tables and GSIs with an explicit or implicit primary key: supported.

    • Globally unique secondary indexes (no primary key): not supported.

    † Backfill behavior when adding a partition:

    • RANGE or RANGE COLUMNS table without a MAXVALUE partition: no backfill needed. Otherwise, specific partition data is backfilled.

    • LIST or LIST COLUMNS table without a DEFAULT partition: no backfill needed. Otherwise, specific partition data is backfilled.

    ‡ Backfill behavior when deleting a partition:

    • RANGE or RANGE COLUMNS table: no backfill when deleting the last partition; backfill required for other partitions.

    • LIST or LIST COLUMNS table without a DEFAULT partition: no backfill needed. Otherwise, backfill is required.

    Sequence operations

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Create a sequence Yes No No No —
    Delete a sequence Yes No No No —
    Modify sequence attributes Yes No No No —
    Convert the sequence type Yes No No No —

    Common column operations

    Important

    Column operations are classified into common column operations (unrelated to the primary key, partition keys, or generated columns), primary key and partition key changes, and generated column operations. Run SHOW FULL CREATE TABLE TABLE_NAME to view complete column attributes before determining which category an operation belongs to.

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Add a column (see Note 1) No No Yes Yes —
    Delete a column No No Yes Yes —
    Rename a column No No No No —
    Change the order of columns No No Yes Yes —
    Change the default value of a column No No No No —
    Delete the default value of a column No No No No —
    Modify the data type of a column No Yes Yes Yes —
    Extend the length of a VARCHAR column (see Note 2 and Note 3) No No No No —
    Extend the length of a CHAR column (see Note 3) No Yes Yes Yes —
    Change a column to NULL No No Yes Yes —
    Change a column to NOT NULL* No No Yes Yes —

    \* If SQL_MODE contains strict_trans_tables or strict_all_tables, this is an online operation. Otherwise, the COPY algorithm is used, which locks the table and copies data.

    Note 1 — Algorithm options for column addition:

    When ALGORITHM=INSTANT or ALGORITHM=OMC is explicitly specified, the behavior changes:

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Instant ADD COLUMN No No No No —
    Change the column type online Yes No Yes Yes Yes

    Note 2 — VARCHAR extension on 5.7 vs. 8.0 data nodes:

    5.7 data nodes support extending VARCHAR column length with the INPLACE algorithm. 8.0 data nodes do not.

    Storage overhead changes at the 256-byte boundary: columns at 255 bytes or fewer use 1 byte for length storage; columns at 256 bytes or more use 2 bytes. Extending a VARCHAR column across this boundary requires more than a metadata change.

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Extend VARCHAR length to more than 256 bytes No No Yes Yes —

    Note 3 — CHAR extension on 5.7 vs. 8.0 data nodes:

    5.7 data nodes support extending CHAR column length with the INPLACE algorithm. 8.0 data nodes do not.

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Extend CHAR column length No No Yes Yes —

    Primary key and sharding column operations

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Add a primary key Yes No Yes Yes No
    Delete the original primary key and add a new primary key Yes No Yes Yes No
    Change the sharding method, number of shards, or sharding columns Yes No Yes (GSI or primary table recreated) Yes No
    Change the sharding column definition of a GSI or primary table Yes No Yes (GSI or primary table recreated) Yes No

    Generated column operations

    Operation Logically executed Locks the table Rebuilds the table Backfills data Async multi-write
    Add a virtual column No No No No —
    Remove a virtual column No No No No —
    Add a stored column No Yes Yes Yes —
    Remove a stored column No No Yes Yes —
    Add a logical column Yes No Yes Yes No
    Remove a logical column Yes No Yes Yes —

    Control online execution behavior

    PolarDB-X gives you direct control over which execution algorithm a DDL operation uses:

    • To add a column without backfilling data, specify ALGORITHM=INSTANT. This uses the Instant ADD COLUMN feature, which completes by modifying only metadata.

    • To modify a column online without locking the table, specify ALGORITHM=OMC. This uses logical execution (online modify column).

    • Logically executed DDL runs online by default — no special specification required.

    • For physically executed DDL (common column operations and local index operations) to run online, specify ALGORITHM=INPLACE. If the operation doesn't support online execution, an error is returned. If it does, the operation runs directly.

    What to expect during a large-table DDL

    For DDL operations that backfill data — such as creating a GSI on a large table or splitting a partition — be aware of the following:

    • DML performance impact during backfill: For logically executed DDL operations without asynchronous multi-write support, online DML runs as logical operations until backfilling completes. Plan large-table DDL during low-traffic windows if possible.

    • Resource consumption: Backfilling copies data to new indexes. Execution time depends on data volume, concurrency, and instance specifications.

    • The table remains accessible: Online DDL blocks DML only for a few seconds during the final table switch. Your application continues to read and write throughout the operation.

    Related topics