Enable the asynchronous execution of DDL operations by using the ASYNC option-PolarDB(PolarDB)-阿里云帮助中心

Long-running DDL operations — such as adding an index, modifying a column type, or splitting a partition — rewrite or reorganize table data and can take minutes to hours. Running them synchronously blocks the session and can affect production workloads.

PolarDB-X 2.0 supports the ASYNC=TRUE option for DDL statements. Append it to a DDL statement to submit the operation as a background job and return control to the client immediately, without waiting for the operation to complete.

Prerequisites

Before you begin, ensure that:

The compute node version of your PolarDB-X 2.0 instance is 5.4.18-17142802 or later

To check or upgrade your instance version, see Check and upgrade the instance version. For version details, see Version release notes.

Submit an async DDL job

Append ASYNC=TRUE to any supported DDL statement. PolarDB-X 2.0 starts a background job and returns immediately:

ALTER TABLE orders ADD INDEX idx_status(status) ASYNC=TRUE;

If both ASYNC=TRUE and a hint are specified in the same DDL statement, ASYNC=TRUE takes precedence.

Track and manage a running job

After submitting an async DDL job, use SHOW DDL to get the job ID and monitor progress:

SHOW DDL;

The output includes the job ID (JOB_ID) for each running or queued DDL job. Use the job ID with the following management statements to control the job:

Statement	Description
`PAUSE DDL <JOB_ID>`	Pauses a running DDL job
`CONTINUE DDL <JOB_ID>`	Resumes a paused DDL job
`CANCEL DDL <JOB_ID>`	Cancels a DDL job that has not yet completed
`ROLLBACK DDL <JOB_ID>`	Rolls back a DDL job

You can also submit these management commands asynchronously:

CONTINUE DDL <JOB_ID> ASYNC=TRUE;
CANCEL DDL <JOB_ID> ASYNC=TRUE;
ROLLBACK DDL <JOB_ID> ASYNC=TRUE;
PAUSE DDL <JOB_ID> ASYNC=TRUE;
PAUSE REBALANCE <JOB_ID> ASYNC=TRUE;
CONTINUE REBALANCE <JOB_ID> ASYNC=TRUE;
CANCEL REBALANCE <JOB_ID> ASYNC=TRUE;

For a full reference, see DDL management statements.

Supported DDL statements

Both AUTO mode and DRDS mode databases support the ASYNC=TRUE option.

ASYNC=TRUE works with DDL operations that rewrite or reorganize table data — such as building indexes or moving partitions. These operations take significant time and benefit from asynchronous execution. DDL operations that only modify schema metadata (such as CREATE TABLE or DROP TABLE) complete quickly and do not support ASYNC=TRUE.

OPTIMIZE TABLE

OPTIMIZE TABLE <table_name> ASYNC=TRUE;

ANALYZE TABLE

ANALYZE TABLE <table_name> ASYNC=TRUE;

ALTER TABLE

All ALTER TABLE statements support ASYNC=TRUE. The following examples cover common operations.

Add an index:

ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>) ASYNC=TRUE;

For index options, see Indexes.

Add a global secondary index (GSI):

ALTER TABLE <table_name> ADD GLOBAL INDEX <gsi_name>(<column_name>)
  PARTITION BY KEY(<column_name>) PARTITIONS <part_num> ASYNC=TRUE;

For GSI options, see Global secondary index (GSI).

Modify or drop a column:

ALTER TABLE <table_name> MODIFY COLUMN <column_name> BIGINT ASYNC=TRUE;
ALTER TABLE <table_name> DROP COLUMN <column_name> ASYNC=TRUE;

Split a partition:

ALTER TABLE <table_name> SPLIT PARTITION <partition_name1> INTO (
  PARTITION <partition_name2> VALUES LESS THAN (<column_value>),
  PARTITION <partition_name3> VALUES LESS THAN (<column_value>)
) ASYNC=TRUE;

Move partitions:

-- Move one or more partitions to a different data node
ALTER TABLE <table_name>
  MOVE PARTITIONS <partition_name>[, ..., <partition_name>] TO <DN_ID> ASYNC=TRUE;

-- Move partitions at the table group level
ALTER TABLEGROUP <tablegroup_name>
  MOVE PARTITIONS <partition_name> TO <DN_ID> ASYNC=TRUE;

DN_ID is the ID of the target data node. For details, see Partition splitting and Partition migration.

Change a table's table group:

ALTER TABLE <table_name> SET tablegroup=<tablegroup_name> ASYNC=TRUE;

For table group options, see Table groups.

CREATE INDEX

CREATE GLOBAL INDEX <index_name> ON <table_name> (<column_name>)
  PARTITION BY HASH(<column_name>) PARTITIONS <part_num> ASYNC=TRUE;

CREATE INDEX <index_name> ON <table_name>(<column_name>) ASYNC=TRUE;

For syntax details, see CREATE INDEX (AUTO mode) and CREATE INDEX (DRDS mode).

Unsupported DDL statements

The following statements do not support ASYNC=TRUE. They only modify schema metadata without reorganizing table data, so they complete quickly and do not require asynchronous execution:

CREATE TABLE / DROP TABLE
CREATE VIEW / DROP VIEW
CREATE PROCEDURE / DROP PROCEDURE
CREATE DATABASE / DROP DATABASE
CREATE TABLEGROUP / DROP TABLEGROUP
CREATE SEQUENCE / DROP SEQUENCE / ALTER SEQUENCE

Background

Previously, you could enable asynchronous DDL execution only by using a hint:

/*+TDDL:cmd_extra(PURE_ASYNC_DDL_MODE = true)*/ ALTER TABLE ...

This approach had two drawbacks:

The hint syntax is verbose and easy to misread.
When using a command-line client, you had to include the -c flag for the hint to take effect — an easy step to overlook.

The ASYNC=TRUE option replaces this workflow with a concise, readable syntax.