Hologres:Migrate data from physical to logical partitioned tables

Table structure conversion

Compared with a physical partitioned table, a logical partitioned table involves the following conversion operations in terms of table creation statements:

• The LOGICAL keyword and the NOT NULL constraint for the partition key need to be added to the DDL statement.

• (Optional) A physical partitioned table supports only one partition key, while a logical partitioned table supports up to two partition keys. You can increase the number of partition keys as needed.

The following sample code provides an example:

• Create a physical partitioned table:

  BEGIN;
  CREATE TABLE user_profile (
      a TEXT,
      b TEXT,
      ds TEXT
  )
  PARTITION BY LIST (ds);
  CREATE TABLE user_profile_202503 PARTITION OF user_profile FOR VALUES IN ('202503');
  CREATE TABLE user_profile_202504 PARTITION OF user_profile FOR VALUES IN ('202504');
  COMMIT;

• Create a logical partitioned table:

  • Keep one partition key.

    CREATE TABLE user_profile_lp_1 (
        a TEXT,
        b TEXT,
        ds TEXT NOT NULL)
    LOGICAL PARTITION BY LIST (ds);

  • Change the ds field to the yy and mm fields to indicate the year and month, keeping two partition keys.

    CREATE TABLE user_profile_lp_2 (
        a TEXT,
        b TEXT,
        yy TEXT NOT NULL,
        mm TEXT NOT NULL)
    LOGICAL PARTITION BY LIST (yy, mm);

Table mapping

In terms of table properties and partition properties, the mappings between logical partitioned tables and physical partitioned tables are shown in the following table.

CLONE

If your table structure does not need to be changed and the data volume is moderate, we recommend that you use the CLONE syntax.

Sample statement:

CALL hg_clone_to_logical_partition('user_profile', 'user_profile_logical');

If you are migrating a large amount of data and the operation takes a long time, it is recommended to use the ASYNC command to execute the task in the background. After the command is successfully submitted, it will return the query_id corresponding to the CALL. You can use this query_id to further check the execution status of the asynchronous task in Query Insight.

SET statement_timeout = 0;
ASYNC CALL hg_clone_to_logical_partition('user_profile', 'user_profile_logical');

Manual migration

If your table structure needs to be changed, or the data volume is large and data migration pressure is high, we recommend that you use the manual migration method for more flexible control over the migration pace.

Migration steps:

1. Create a table.

   For more information, see Create a logical partitioned table in this topic.

2. Import historical data. hg_insert_overwrite is recommended.

   CALL hg_insert_overwrite('logical_partition_table', '{20250601, 20250602}'::text[], 'SELECT * FROM tb');

REBUILD syntax (not recommended)

If you can accept a longer change time window and allow some read and write tasks to fail, you can use the REBUILD syntax for migration. For more information, see REBUILD (Beta).

Take note of the following points:

• When you use the REBUILD feature to migrate a partitioned table, the table name of the logical partitioned table after migration remains unchanged. The name of the original physical partitioned table will be changed to tmp_rebuild_old_<query_id>_<unique_id>_<table_name>.

• After migration is complete, you need to further adapt the remaining items, including import tasks, query tasks, and O&M tasks.

• Before adaptation work is completed, related tasks may generate errors due to compatibility issues. Therefore, this method is not recommended for migrating partitioned tables.

Sample statements:

-- Add the NOT NULL constraint to the partition field.
ASYNC REBUILD TABLE user_profile ALTER ds SET NOT NULL;

-- Convert the physical partitioned table to a logical partitioned table and retain the original physical partitioned table.
ASYNC REBUILD TABLE user_profile WITH (keep_source) TO logical partition;

Scenario 1: Create a child table (partition) and import data

-- Import task for the original physical partitioned table
CREATE TABLE user_profile_202505 PARTITION OF user_profile FOR VALUES IN ('202505');
INSERT INTO user_profile_202505 SELECT a, b, ds FROM <source_table> WHERE ds = '202505';

-- Import task for the new logical partitioned table
INSERT INTO user_profile_lp_1 SELECT a, b, ds FROM <source_table> WHERE ds = '202505';

Scenario 2: Refresh data in a child table (partition)

-- Refresh task for the original physical partitioned table (creating a temporary table to implement INSERT OVERWRITE)
-- Create a temporary table and import data.
CREATE TABLE tmp_user_profile_202505(a text, b text, ds text);
INSERT INTO tmp_user_profile_202505 SELECT a, b, ds FROM <source_table> WHERE ds = '202505';

-- RENAME the temporary table and ATTACH it to the parent table.
BEGIN;
DROP TABLE IF EXISTS user_profile_202505;
ALTER TABLE tmp_user_profile_202505 RENAME TO user_profile_202505;
ALTER TABLE user_profile ATTACH PARTITION user_profile_202505 FOR VALUES IN ('202505');
COMMIT;

-- Refresh task for the original physical partitioned table (using the hg_insert_overwrite command)
CALL hg_insert_overwrite('user_profile' , '202505', $$SELECT a, b, mm FROM <source_table> WHERE ds='202505'$$);

-- Refresh task for the new logical partitioned table (using native Insert Overwrite syntax)
INSERT OVERWRITE user_profile_lp_1 PARTITION (ds = '202505') SELECT a, b, ds FROM <source_table> WHERE ds='202505';