Hologres:Migrate from physical to logical partitioned tables

When to migrate

Migrate to a logical partitioned table if your physical partitioned table meets either of the following conditions:

• A table group contains more than 10,000 child partitioned tables, causing large metadata scale.

• New partitions are added daily, resulting in frequent DDL operations.

Choose a migration approach

Migrate using dual-write verification (recommended)

This approach keeps the old and new tables running in parallel during migration, minimizing business impact.

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

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

BEGIN;
ALTER TABLE <source_table_name> RENAME TO <source_table_name_archive>;
ALTER TABLE <target_table_name> RENAME TO <source_table_name>;
COMMIT;

Migrate using REBUILD (not recommended)

When REBUILD completes, the physical partitioned table is renamed to tmp_rebuild_old_<query_id>_<unique_id>_<table_name>. Related tasks may generate errors until you complete task adaptation.

1. Stop write tasks for the physical partitioned table.

2. Run REBUILD to convert the table:

   -- 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 (retains the original).
   ASYNC REBUILD TABLE user_profile WITH (keep_source) TO logical partition;

   For more information, see REBUILD.

3. Update write tasks and restart them.

4. Adapt query tasks to the new logical partitioned table.

5. Adapt O&M tasks to the new logical partitioned table.

Table structure conversion

When creating a logical partitioned table from an existing physical partitioned table DDL, make the following changes:

• Add the LOGICAL keyword before PARTITION BY.

• Add NOT NULL to the partition key column.

• (Optional) Add a second partition key. Physical partitioned tables support one partition key; logical partitioned tables support up to two.

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;

Logical partitioned table — one partition key:

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

Logical partitioned table — two partition keys (splitting ds into yy for year and mm for month):

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

Import task adaptation

If your import task writes to the parent table via Fixed Plan: no changes needed.

If your import task writes to child tables: make the following changes:

• Remove statements that create child tables. Logical partitioned tables have no physical child tables.

• Change the target table name from the child table name to the parent table name.

-- 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';

-- Updated: logical partitioned table
INSERT INTO user_profile_lp_1 SELECT a, b, ds FROM <source_table> WHERE ds = '202505';

-- Original: physical partitioned table (temporary table method)
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';

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;

-- Original: physical partitioned table (hg_insert_overwrite method)
CALL hg_insert_overwrite('user_profile', '202505', $$SELECT a, b, mm FROM <source_table> WHERE ds='202505'$$);

-- Updated: logical partitioned table (native INSERT OVERWRITE)
INSERT OVERWRITE user_profile_lp_1 PARTITION (ds = '202505') SELECT a, b, ds FROM <source_table> WHERE ds='202505';

Query task adaptation

If your query targets the parent table: no changes needed.

If your query targets a child table: change it to query the parent table and add a partition filter condition.

-- Original: physical partitioned table
SELECT * FROM user_profile_202504;

-- Updated: logical partitioned table
SELECT * FROM user_profile_lp_1 WHERE ds = '202504';

O&M task adaptation

Some O&M operations use different system tables or functions for logical partitioned tables.

Property and parameter mapping

The following table maps dynamic partition management parameters and other table properties between physical and logical partitioned tables.