Hologres:REBUILD (Beta)

Command 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

Run REBUILD on a table without binary logging

-- 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 NOT NULL column and set 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 Serverless resources to run the Rebuild task. 
-- Set the distribution_key and clustering_key for the table, and change the orientation to row-column hybrid store.
ASYNC REBUILD TABLE rebuild_test 
WITH (
    rebuild_guc_hg_computing_resource = 'serverless'
)
SET (
    distribution_key = 'a',
    clustering_key = 'a',
    orientation = 'row,column'
);

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

Run REBUILD on a table with binary logging

The REBUILD command does not retain historical binary logging (Binlog) data. Therefore, you cannot run REBUILD on a table with binary logging enabled by default. To perform this operation, you must configure the binlog_mode parameter in the REBUILD task and follow the steps below. This process ensures that downstream clients have consumed all historical Binlog data before the table is rebuilt.

1. Run REBUILD.

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

2. For a REBUILD task that includes the binlog_mode parameter, the task automatically pauses after the set_readonly step is complete. You can check the progress by running the following SQL command. At this point, the system has set the table to read-only. You cannot write data 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)

3. Wait for the downstream client to finish consuming the existing Binlog data. Then, manually run the following SQL command to resume the REBUILD task. During this process, the source table remains read-only, and no new Binlog data is generated.

   RESUME '<query_id>';

   After the REBUILD task is successfully completed, binary logging is automatically enabled for the new table. You can then restart the downstream Binlog consumption task. The consumption of the new table's Binlog must start from lsn = 0.

Check the REBUILD task status

REBUILD tasks run asynchronously. When you submit a task, the system returns a success status for the submission and a query_id. You must check the status of the asynchronous sub-tasks in the hologres.rebuild_progress system table. The REBUILD operation is complete only after all sub-tasks have succeeded. You can use the following command to check the status:

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

The columns of the system table and their descriptions are as follows:

The following is a sample result:

Stop and restart a REBUILD task

• Pause an asynchronous REBUILD task.

  SUSPEND '<query_id>';

• Resume an asynchronous task that was paused using the CANCEL command.

  RESUME '<query_id>';

Handle REBUILD task exceptions

If a REBUILD task is interrupted by an error or manually paused with the SUSPEND command, you can use the RESUME command to continue the task. Alternatively, you can follow these steps to end the task and recover the source table.

• Use 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 was set to read-only, use the following command to restore write operations to the table.

  ALTER TABLE <table_name> SET (readonly = false);