Modify Hologres Table Storage Properties via REBUILD - Hologres - Alibaba Cloud - Hologres

Syntax

Statement format

ASYNC REBUILD TABLE [ IF EXISTS ] <table_name>
    [ WITH ( <rebuild_parameter> [= <value>] [, ... ] )]  
    <action> [, ... ];
    
WHERE action IS ONE OF:
    ADD [ COLUMN ] <column_name> <data_type> [ column_constraint [ ... ] ]
    ALTER [ COLUMN ] <column_name> [ SET DATA ] TYPE <data_type> [ USING <expression> ]
    ALTER [ COLUMN ] <column_name> SET DEFAULT <expression>
    ALTER [ COLUMN ] <column_name> DROP DEFAULT
    ALTER [ COLUMN ] <column_name> { SET | DROP } NOT NULL
    ALTER PRIMARY KEY (<column_name> [, ...])
    TO [LOGICAL] PARTITION [BY LIST(<column_name> [, <column_name>])]
    SET ( <parameter> [= <value>] [, ... ] )

WHERE rebuild_parameter IS ONE OF:
    keep_source
    binlog_mode
    rebuild_guc_<guc_name> = '<guc_value>'

Parameters

Parameter	Sub-item	Description
ASYNC		The REBUILD task is executed asynchronously. After the task is executed, it returns a `query_id`. You can use the `query_id` to monitor the execution status of the task. Synchronous execution is not currently supported.
table_name		The name of the target table to be rebuilt.
column_name		The column name of the target table.
data_type		The data type of the column.
action	ADD COLUMN	Adds a column. You can add NOT NULL columns and set default values.
	ALTER COLUMN TYPE	Modifies the data type of a column.
	ALTER COLUMN SET/DROP DEFAULT	Sets or removes the default value for a column. NULL values in existing data will not change.
	ALTER COLUMN SET/DROP NOT NULL	Sets or removes the NOT NULL constraint for a column.
	ALTER PRIMARY KEY	Modifies the primary key of the table. If there is a data conflict with the new primary key, an error will be reported during asynchronous execution. Monitor the execution status of the task promptly.
	TO [LOGICAL] PARTITION	Converts the table to a logical/physical partitioned table, supporting the following scenarios: Convert a standard table to a physical partitioned table. You must specify a partition key. Convert a standard table to a logical partitioned table. You must specify a partition key. Modify the partition key of a physical partitioned table. Convert a physical partitioned table to a logical partitioned table. You can determine whether to modify the partition key. You cannot convert a logical partitioned table to a physical partitioned table.
	SET ( <parameter> [= <value>])	Modifies table properties. Common scenarios: Modify the distribution key `distribution_key`. Modify the segment key `event_time_column`. Modify the clustering index `clustering_key`. Modify the table storage format `orientation`: Convert between row-oriented storage, column-oriented storage, and row-column hybrid storage. Modify the `table_group` of the table. All other table properties are supported for modification. You do not need to use REBUILD to modify bitmap indexes `bitmap_columns` or dictionary encoding columns `dictionary_encoding_columns`. Instead, use the ALTER TABLE syntax.
WITH (<rebuild_parameter> [= <value>])		Sets REBUILD task-related parameters. Common parameters: keep_source: You do not need to set a value. After the conversion, the original table is not deleted and is renamed to `tmp_rebuild_old_<query_id>_<unique_id>_<table_name>`. `binlog_mode`: Allows `REBUILD` to run on a table with Binlog enabled. To prevent Binlog data loss, you must follow the procedure in the Examples section. `rebuild_guc_hg_computing_resource='serverless'`: Executes the `REBUILD` task using a serverless resource. This avoids using your instance's resources and improves task stability. `rebuild_guc_<guc_name>='<guc_value>'`: Specifies other GUC parameters for the task. For more information, see GUC parameters.

Precautions

Only asynchronous execution (ASYNC) is supported, which does not require long-term connection occupation.
After you submit a REBUILD task, it is executed quickly and returns the query_id of the task. You can use this query_id to check the execution status of a REBUILD task. If the task does not complete successfully for a while after submission, this may be because a large number of asynchronous scheduling tasks are running on the current instance. We recommend that you wait for a period of time and check the task execution status after the query_id is returned.
Using the REBUILD feature to modify table parameters involves underlying data redistribution, which consumes computing resources. Therefore, it is recommended that you execute REBUILD tasks during off-peak business hours, or use serverless computing resources for this operation to ensure business stability.
During a REBUILD task, the table is read-only and not writable. Starting from Hologres V4.1, REBUILD uses Dynamic Table technology for incremental updates, which significantly shortens the read-only window. The target table must meet the following requirements to avoid a long write downtime:
- The table must have a primary key before the REBUILD operation, and the new primary key must include all columns from the original primary key.
- The table must use column-oriented storage or row-column hybrid storage before the REBUILD operation.
- The table must not contain any generated columns after the REBUILD operation.
- If the table is a physical partitioned table before the REBUILD operation, its partition key must remain unchanged.
- If the table becomes a logical partitioned table after the REBUILD operation, it can have only one partition key.
To reduce overhead, modify multiple parameters in a single REBUILD task.
After a physical partitioned table is rebuilt, its dynamic partition management properties are not inherited. You must manually set the following properties after the rebuild is complete:
- The auto_partitioning property of the parent table.
- The child table's keep_alive and other attributes.
- After you rebuild a physical partitioned table, any properties that were independently set on its child tables are not inherited and instead revert to the settings of the parent table, such as bitmap_columns and dictionary_encoding_columns.
REBUILD is not supported for the following tables:
- Tables with special column property settings, such as columnar storage optimization for JSONB columns, or column constraints, such as vector columns.
- Tables with a full-text index or a global secondary index.
- Tables that contain columns of the Serial or Bigserial data type.
- Tables that are referenced by a Dynamic Table or a materialized view. Tables referenced by a view are supported.

Examples

Rebuild a table without Binlog

-- Create a table and import data.
CREATE TABLE rebuild_test (
    a TEXT,
    b TEXT,
    ds TEXT
);

INSERT INTO rebuild_test VALUES ('1', '1', '2025-04-01'), ('2', '2', '2025-04-02'), ('3', '3', '2025-04-03');

-- Add a non-null column with a default value.
ASYNC REBUILD TABLE rebuild_test ADD COLUMN c text NOT NULL DEFAULT 'a';

-- Change the primary key to column a.
ASYNC REBUILD TABLE rebuild_test ALTER PRIMARY KEY (a);

-- Use a serverless resource to run the REBUILD task, set the distribution_key and clustering_key for the table, and change the storage to row-column hybrid.
ASYNC REBUILD TABLE rebuild_test 
WITH (
    rebuild_guc_hg_computing_resource = 'serverless'
)
SET (
    distribution_key = 'a',
    clustering_key = 'a',
    orientation = 'row,column'
);

-- Convert a non-partitioned table to a logical partitioned table, set the partition key to ds, and add a NOT NULL constraint to the ds column.
ASYNC REBUILD TABLE rebuild_test 
    ALTER COLUMN ds SET NOT NULL,
    TO LOGICAL PARTITION BY LIST(ds);

Rebuild a table with Binlog

REBUILD does not preserve historical Binlog data. Therefore, REBUILD is not supported by default for tables with Binlog enabled. You must specify the binlog_mode parameter and follow the steps below to ensure that downstream systems have fully consumed the historical Binlog data.

Run the REBUILD command.

ASYNC REBUILD TABLE rebuild_test 
WITH (
  binlog_mode
)
<YOUR_ACTION>;

If the binlog_mode parameter is set for a REBUILD task, the task automatically pauses after the set_readonly step is complete. You can run an SQL query to check the progress. At this point, the system has set the table to read-only. This means that data cannot be written to the table and no new Binlog data is generated.

postgres=# SELECT step, status, progress FROM hologres.rebuild_progress('<query_id>');
             step              | status | progress 
-------------------------------+--------+----------
 prepare                       | done   | 1/1
 create_tmp_table              | done   | 1/1
 get_src_table_snapshot        | done   | 1/1
 insert                        | done   | 1/1
 set_readonly                  | done   | 1/1
 check_snapshot                |        | 0/1
 re-insert                     |        | -
 check_additional_child_table  |        | -
 create_additional_child_table |        | -
 insert_additional_child_table |        | -
 swap                          |        | 0/1
(11 rows)

Wait for the downstream client to finish consuming all existing Binlog data. Then, manually resume the REBUILD task by running the following SQL statement. During this process, the source table remains in read-only mode and does not generate new Binlog data.
```
RESUME '<query_id>';
```
After the REBUILD task is complete, Binlog is automatically enabled for the new table. You can then restart the downstream Binlog consumption task and start consuming from lsn = 0.

Monitoring and O&M

View the execution status of a REBUILD task

The REBUILD task runs asynchronously. After the task is successfully submitted, it returns a success status and a query_id. You need to query the hologres.rebuild_progress system table to view the status of the asynchronous subtasks. The REBUILD operation for the table is complete only when all subtasks are successful. The command is as follows:

SELECT * FROM hologres.rebuild_progress('<rebuild_query_id>');

The following table describes the columns of the system table.

Column name	Description
job_name	The query_id of the REBUILD task.
step_id	The step ID. The subtasks of REBUILD are executed sequentially according to step IDs.
step	The step name: prepare: Make task preparations. create_tmp_table: Create a temporary table. get_src_table_snapshot: Get a data snapshot of the original table. insert: Import historical data into the temporary table. set_readonly: Set the table to be rebuilt to read-only. This stops data writes. check_snapshot: Compares the current data snapshot of the table with the one obtained in Step 3. If they are different, the process proceeds to the `re-insert` step. re-insert: Import incremental data. check_additional_child_table: Check if any new child tables have been created by users from the start of REBUILD until now. This step is only for physical partitioned tables. create_additional_child_table: Create potential new child tables for the temporary table. This step is only for physical partitioned tables. insert_additional_child_table: Import historical data into the new temporary child tables. This step is only for physical partitioned tables. swap: Replace the original table with the temporary table.
status	The status of the subtask. done: Completed. doing: In progress. NULL: No need to execute this step, or currently unable to determine if this step needs to be executed. error: Execution failed. Check the error message in the message field.
progress	Subtask progress: `m/n`. In this format, `n` represents the total number of substeps to be executed, and `m` represents the number of substeps that have been executed. This total number is generally positively correlated with the number of partitions.
start_time	The start time of the subtask.
end_time	The end time of the subtask.
queryid	The query_id of the subtask.
pid	The service process ID.
message	The message of the subtask. If the subtask reports an error, the error message is recorded in this field.

The following figure shows the sample result.

opopo

Stop and restart REBUILD tasks

Stop the REBUILD asynchronous task.
```
SUSPEND '<query_id>';
```
Resumes the asynchronous task that was set to CANCEL.
```
RESUME '<query_id>';
```

Handle REBUILD task exceptions

If a REBUILD task is interrupted by an error or you manually stop it by using the SUSPEND command, you can either resume it with the RESUME command or follow these steps to terminate the task and restore the source table.

Run the following command to clean up the temporary tables created during the REBUILD process.
```
CALL hg_clean_rebuild_tmp_tables('<query_id>');
```
If the task was interrupted after the source table became read-only, run the appropriate command to re-enable writes.
- For Hologres versions earlier than V4.1:
```
ALTER TABLE <table_name> SET (readonly = false);
```
- For Hologres V4.1 and later:
```
ALTER TABLE <table_name> SET RESET (ddl_options,write_options);
```