Hologres:REBUILD

Parameters

Supported partition conversions

Performance and scheduling

• REBUILD rewrites table data and consumes significant computing resources. Run REBUILD tasks during off-peak hours, or use rebuild_guc_hg_computing_resource='serverless' to keep instance resources available for production workloads.

• Combine multiple changes into a single REBUILD task to reduce overhead.

• After submitting a REBUILD task, the submission completes quickly and returns a query_id. If the submission takes longer than expected, the instance may have many asynchronous scheduling tasks running. Wait for the query_id to be returned before checking the task execution status.

Write availability

• The table is read-only during part of the rebuild (steps set_readonly through swap). No writes are accepted during this period.

• Starting from V4.1, Dynamic Table technology significantly reduces the read-only window. All of the following conditions must be met:

  V4.1 conditions for reduced read-only duration

  • The table has a primary key before the rebuild, and the new primary key includes all columns of the original primary key.

  • The table uses column store or row-column hybrid store before the rebuild.

  • The table has no generated columns after the rebuild.

  • If the table is a physical partitioned table before the rebuild, the partition key must remain unchanged.

  • If the table becomes a logical partitioned table after the rebuild, it can have only one partition key.

Physical partitioned tables

After rebuilding a physical partitioned table, the following properties are not carried over to the new table. Set them again after the task completes:

• The auto_partitioning property on the parent table.

• Per-child-table properties such as keep_alive.

• Properties independently set on individual child tables (such as bitmap_columns and dictionary_encoding_columns) revert to the parent table's values.

Limitations

REBUILD is not supported for tables that have:

• Column-store optimization on JSONB columns, or vector field column constraints.

• Full-text indexes or global secondary indexes.

• Columns of the Serial or Bigserial type.

• Dynamic Table or materialized view dependencies. (Standard view dependencies are supported.)

Rebuild a table without binary logging enabled

-- Create a table and insert 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 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 Serverless resources, set distribution_key and clustering_key, and switch 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 table to a logical partitioned table with ds as the partition key
ASYNC REBUILD TABLE rebuild_test
    ALTER COLUMN ds SET NOT NULL,
    TO LOGICAL PARTITION BY LIST(ds);

Rebuild a table with binary logging enabled

REBUILD does not preserve historical binary logging data. By default, running REBUILD on a table with binary logging enabled is blocked. Use the binlog_mode parameter and follow this procedure to make sure downstream consumers finish processing existing data before the table is swapped.

1. Run REBUILD with binlog_mode:

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

2. The task pauses automatically after the set_readonly step. At this point, the table is read-only — no new binary logging data is generated. Query the progress to confirm:

   SELECT step, status, progress FROM hologres.rebuild_progress('<query_id>');

   Expected output:

                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 all downstream consumers to finish processing the existing binary log data. Then resume the task:

   RESUME '<query_id>';

After the rebuild completes, binary logging is automatically enabled on the new table. Restart downstream consumers and begin consuming from lsn = 0.

View task progress

Query the hologres.rebuild_progress system table to check the status of a running or completed REBUILD task:

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

The table is fully rebuilt only after all subtasks show done. The following table describes the system table columns:

The following screenshot shows an example of the query result:

Stop and resume a task

-- Pause a running REBUILD task
SUSPEND '<query_id>';

-- Resume a paused or interrupted REBUILD task
RESUME '<query_id>';

Handle task failures

If a REBUILD task fails or is paused with SUSPEND, resume it with RESUME, or clean up and restore the table manually:

1. Clean up temporary tables created during the REBUILD:

   CALL hg_clean_rebuild_tmp_tables('<query_id>');

2. If the source table was set to read-only before the task was interrupted, restore write access:

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