Online DDL - PolarDB - Alibaba Cloud Documentation Center

Online data definition language (DDL) allows the concurrent execution of DDL operations and other transactions. Online DDL minimizes the locking duration on database tables, which in turn enhances the overall concurrency and performance of the database.

Prerequisites

The compute node version is 5.4.16-16717637 or later, which allows asynchronous multi-write for logically executed DDL operations. For more information, see Release notes.
For information about the required versions and conditions for DDL operations that are described in this topic, see Partition statements, Instant ADD COLUMN, Change the column types without locking tables, Sequence, Generated columns, How to use global secondary indexes, and Table group statements.

Overview

DDL operations in a PolarDB-X instance can be classified into the following two categories:

Physically executed: DDL operations are pushed down to data nodes for execution. These DDL operations include CREATE LOCAL INDEX, ALTER/ADD COLUMN, and CREATE/DROP TABLE. Compute nodes only perform shard-level forwarding of physical statements, coordination, and metadata management. The implementation principles and online features of these DDL operations are similar to those of MySQL DDL operations.

Logically executed: Compute nodes perform the complete DDL operations. These DDL operations include GLOBAL INDEX, CREATE/DROP PARTITION, CREATE/DROP PRIMARY KEY, and Change the column types without locking tables. When a DDL operation of this type is executed, the system creates a temporary table. The system copies existing data in the original table to the temporary table and simultaneously captures and synchronizes incremental data updates to the temporary table. After the temporary table is adequately populated with both the historical and the incremental data, the system seamlessly switches the data flow from the original table to the temporary table. Compute nodes handle the data backfilling, incremental multi-write, and metadata management processes. DDL operations of this type do not lock tables.

The following content analyzes the online DDL features from three dimensions based on their impact on other SQL operations:

Whether is the table locked?
- Online DDL operations block data manipulation language (DML) operations only for a few seconds during the table switch and allow DML operations to be executed during most of the execution period.
- Non-online DDL operations lock the table throughout the execution process during which you cannot perform DML operations on the table.
Is data backfilled?
- DDL operations that involve only metadata changes, such as INSTANT ADD COLUMN and RENAME TABLE, can be completed instantaneously without the need to backfill data.
- DDL operations such as DROP TABLE and DROP INDEX only modify metadata and delete related physical files. These operations are completed in a short time without the need to backfill data.
- Other DDL operations than INSTANT operations, table deletion operations, and index deletion operations copy and write the original data to new clustered indexes or secondary indexes during physical or logical execution. The execution occupies a specific amount of cluster resources. The execution duration varies based on the data amount, concurrency level, and instance specifications.
Are synchronized multi-writes of online updates required?
- DDL operations that are logically executed backfill data into a temporary table and simultaneously multiple-write online DML traffic to the temporary table in a synchronous manner. The online DML traffic may conflict with the backfilled data. In addition, multi-writes are handled by using the distributed transaction approach to ensure atomicity, which causes the DML operations to be executed as logical operations. This leads to a decrease in the performance of online operations. To address this issue, PolarDB-X implements asynchronous multi-write for logically executed DDL operations such as MOVE/SPLIT PARTITION, Change the column types without locking tables, and REBALANCE. The multi-write DML traffic is first cached and then asynchronously executed after data backfilling is complete, which minimizes multi-write conflicts and the duration of performance degradation. Whether or not these DDL operations support asynchronous multi-write is described in the Online DDL features section of this topic.
- By default, native online DDL operations that are physically executed on data nodes, backfill data, and do not lock tables implement native asynchronous multi-write by using online logs. Whether or not these DDL operations support asynchronous multi-write is not described in the Online DDL features section of this topic.
- Other types of DDL operations do not require multiple writes. Whether or not these DDL operations support asynchronous multi-write is not described in the Online DDL features section of this topic.

Online DDL operations

Important

When you use DDL statements to perform table and column operations, especially by using the MODIFY COLUMN and CHANGE COLUMN statements, you must specify the complete column attributes to avoid unexpected column modifications caused by missing attributes.

Index operations

Operation	Logically executed	Locks the table	Rebuilds the table	Backfills data	Supports asynchronous 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	Supports asynchronous 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 that does not have GSIs (TRUNCATE TABLE WITHOUT GSI)	No	No	Yes	No	-
Truncate a table that has GSIs (TRUNCATE TABLE WITH GSI)	Yes	No	Yes	No	-

Partition operations

Operation	Logically executed	Locks the table	Rebuilds the table	Backfills data	Supports asynchronous multi-write
Migrate a partition	Yes	No	Yes (Only relevant partitions are recreated.)	Yes	Yes Note Logical tables and GSIs have an explicit or implicit primary key, and therefore support this feature. By default, a globally unique secondary index does not have a primary key and does not support this feature.
Split a partition	Yes	No	Yes (Only relevant partitions are recreated.)	Yes
Merge partitions	Yes	No	Yes (Only relevant partitions are recreated.)	Yes	No
Add a partition Note If no `MAXVALUE` partition is defined for a table that is partitioned by using the RANGE or RANGE COLUMNS partitioning method, you do not need to backfill data when you add a partition to the table. Otherwise, you need to backfill specific partition data. If no `DEFAULT` partition is defined for a table that is partitioned by using the LIST or LIST COLUMNS partitioning method, you do not need to backfill data when you add a partition to the table. Otherwise, you need to backfill specific partition data.	Yes	No	No	No	-
Delete a partition Note Data backfilling is not required if you delete the last partition of a table that is partitioned by using the RANGE or RANGE COLUMNS partitioning method. Data backfilling is required if you delete other partitions. If no `DEFAULT` partition is defined for a table that is partitioned by using the LIST or LIST COLUMNS partitioning method, you do not need to backfill data when you delete a partition from the table. Otherwise, you need to backfill data.	Yes	No	No	No	-
Reorganize partitions	Yes	No	Yes (Only relevant partitions are recreated.)	Yes	No
Clear a partition	Yes	No	No	No	-
Rename a partition	Yes	No	No	No	-
Modify a partition	Yes	No	Yes (Only relevant partitions are recreated.)	Yes	No

Note

The online DDL features of partition operations described in the preceding table apply to both table groups and tables.

Sequence change operations

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

Common column operations

Important

Column operations are classified into common column operations (column operations that are not related to the primary key, partition keys, or generated columns), primary key and partition key change operations, and generated column operations. You can execute the SHOW FULL CREATE TABLE TABLE_NAME statement to view the complete column attributes to determine the category to which a column operation belongs.

Operation	Logically executed	Locks the table	Rebuilds the table	Backfills data	Supports asynchronous multi-write
Add a column. For more information, 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. For more information, see Note 2 and Note 3.	No	No	No	No	-
Extend the length of a char column. For more information, see Note 3.	No	Yes	Yes	Yes	-
Change the value in a column to NULL	No	No	Yes	Yes	-
Change the value in a column to NOT NULL Note If the `SQL_MODE` option contains the `strict_trans_tables` or `strict_all_tables` flag, the operation is an online operation. Otherwise, the `COPY` algorithm is used to lock the table and copy data.	No	No	Yes	Yes	-

Note

PolarDB-X allows you to use the instant algorithm to add a column by modifying only the metadata of a database. Change operations on specific columns can be logically executed online. The following table describes the online execution features of the operations when ALGORITHM=INSTANT or ALGORITHM=OMC is explicitly specified.
Operation
Logically executed
Locks the table
Rebuilds the table
Backfills data
Supports asynchronous multi-write
Instant ADD COLUMN
No
No
No
No
-
Change the column type online
Yes
No
Yes
Yes
Yes
By default, PolarDB-X 5.7 data nodes allow you to extend the length of VARCHAR columns by using the INPLACE algorithm. However, this feature is not supported on 8.0 data nodes. If the length of a VARCHAR column is 255 bytes or less, it requires only one byte for storage. If the length is 256 bytes or more, it requires two bytes for storage. If you want to extend the length of a VARCHAR column from less than or equal to 255 bytes to equal to or greater than 256 bytes, the change cannot be made by only metadata modification. Conversely, if you want to reduce the length of a VARCHAR column from over 256 bytes to 255 bytes or less, the change can be done by only metadata modification.
Operation
Logically executed
Locks the table
Rebuilds the table
Backfills data
Supports asynchronous multi-write
Extend the length of a VARCHAR column to be greater than 256 bytes
No
No
Yes
Yes
-
By default, PolarDB-X 5.7 data nodes allow you to extend the length of CHAR columns by using the INPLACE algorithm. However, this feature is not supported on 8.0 data nodes. The following table describes the online execution features of the operation.
Operation
Logically executed
Locks the table
Rebuilds the table
Backfills data
Supports asynchronous multi-write
Extend the length of a CHAR column
No
No
Yes
Yes
-

Primary key and sharding column operations

Operation	Logically executed	Locks the table	Rebuilds the table	Backfills data	Supports asynchronous 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 (The GSI or primary table involved in the sharding change is recreated.)	Yes	No
Change the definition for the sharding column of a GSI or primary table	Yes	No	Yes (The GSI or primary table that uses the sharding column is recreated.)	Yes	No

Generated column operations

Operation	Logically executed	Locks the table	Rebuilds the table	Backfills data	Supports asynchronous multi-write
Add a virtual column (VIRTUAL column)	No	No	No	No	-
Remove a virtual column (VIRTUAL column)	No	No	No	No	-
Add a storage column (STORED column)	No	Yes	Yes	Yes	-
Remove a storage column (STORED column)	No	No	Yes	Yes	-
Add a logical column (LOGICAL column)	Yes	No	Yes	Yes	No
Remove a logical column (LOGICAL column)	Yes	No	Yes	Yes	-

Manage the online execution features

PolarDB-X allows you to manage the online execution feature by using the following methods:

If you want to add a column, you can use the Instant ADD COLUMN feature by specifying ALGORITHM=INSTANT.
If you want to modify a column online, you can specify ALGORITHM=OMC to use the logical execution method.
By default, logically executed DDL statements are executed online. No special specifications are required.
For online execution of common column operations that are physically executed and local index DDL operations, you can specify ALGORITHM=INPLACE for the operations. If the operations do not support online execution, an error occurs. Otherwise, the operations are directly executed.