Best practices: Importing data to logical partitioned tables-Hologres(Hologres)-阿里云帮助中心

Writing to multiple partitions of a logical partitioned table at the same time generates large numbers of small files, which trigger frequent compactions and can destabilize your instance. This guide shows you how to import data safely by choosing the right method for your source and partition range.

Never import data into multiple partitions using a single SQL statement. Always import one partition at a time, or use a stored procedure designed for sequential partition import.

How it works

When data is imported into Hologres, it is first loaded into the memtable (memory table). Once enough data accumulates in memory, Hologres flushes it to disk and compacts the files. For details, see INSERT.

For logical partitioned tables, data from different partitions is stored in separate files. Writing to many partitions simultaneously produces a large number of small files, triggering compaction storms that can overload your instance and degrade performance and stability. For details on the compaction process, see Compact data files (beta).

Import constraints

Import method	Partition limit
Batch import	One partition at a time
Fixed Plan import	Up to five partitions simultaneously
Historical partition refresh or batch update	By partition, during off-peak periods

Load historical data

Choose your import method based on two factors: where your source data lives, and whether you need to define the partition range dynamically.

Decision path:

Use `hg_insert_overwrite` first — the simplest and most stable option for data accessible from Hologres (internal tables, MaxCompute foreign tables, OSS foreign tables).
Fall back to manual sequential import — if hg_insert_overwrite is not available.
Use a custom stored procedure — if the partition range is dynamic and hard to enumerate upfront (for example, "the last N days").

Option 1: `hg_insert_overwrite` stored procedure (recommended)

Use the hg_insert_overwrite stored procedure to overwrite specific partitions in a single call. For details, see Implement INSERT OVERWRITE via hg_insert_overwrite.

-- Create the destination table (logical partitioned table).
CREATE TABLE public.tableA_lp(
  a TEXT,
  b INT,
  c TIMESTAMP,
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- Create the source table with physical child partitions.
BEGIN;
CREATE TABLE public.tableB(
  a TEXT,
  b INT,
  c TIMESTAMP,
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  PARTITION BY LIST(ds);
CALL set_table_property('public.tableB', 'orientation', 'column');
CREATE TABLE public.holo_child_3a PARTITION OF public.tableB FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_3b PARTITION OF public.tableB FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3c PARTITION OF public.tableB FOR VALUES IN('20201217');
COMMIT;

INSERT INTO public.holo_child_3a VALUES('a',1,'2034-10-19','a','20201215');
INSERT INTO public.holo_child_3b VALUES('b',2,'2034-10-20','b','20201216');
INSERT INTO public.holo_child_3c VALUES('c',3,'2034-10-21','c','20201217');

-- Overwrite the target partitions from the source table.
CALL hg_insert_overwrite('public.tableA_lp' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);

Option 2: Manual sequential import

If hg_insert_overwrite is not available, import data partition by partition using the hg_internal_copy_data_before and hg_internal_copy_data_after stored procedures. These procedures adjust compaction parameters around the import to improve stability and efficiency.

Always call hg_internal_copy_data_before before starting the import and hg_internal_copy_data_after after all partitions are loaded. Do not import multiple partitions in a single SQL statement.

-- Adjust the compaction parameter before starting the import.
CALL hg_internal_copy_data_before('public.tableA_lp');

-- Import data one partition at a time.
INSERT INTO public.tableA_lp PARTITION (ds = '20250601') SELECT * FROM public.tableB WHERE ds = '20250601';
INSERT INTO public.tableA_lp PARTITION (ds = '20250602') SELECT * FROM public.tableB WHERE ds = '20250602';
...

-- Restore the compaction parameters after all data is imported.
CALL hg_internal_copy_data_after('public.tableA_lp');

Option 3: Custom stored procedure with INSERT OVERWRITE

Use this option when the partition range is dynamic and hard to enumerate upfront — for example, when importing the most recent N days of data. The stored procedure calculates the partition range at runtime and executes the import sequentially, one partition at a time. This method ensures that the import task is both flexible and stable.

The example below defines a procedure that overwrites logical partitions for the last last_x_days days, including today. The target table has a single DATE partition key column named ds; the source table has a column with the same name.

-- Create sample source and target tables.
CREATE TABLE src(a INT, ds DATE);
CREATE TABLE target(a INT, ds DATE NOT NULL) logical PARTITION BY list(ds);

-- Define the stored procedure.
CREATE OR REPLACE PROCEDURE insert_overwrite_wrapper(target_table regclass, select_sql TEXT, last_x_days INT)
LANGUAGE 'plpgsql'
AS $$
DECLARE
    insert_sql TEXT;
    range_days DATE[];
    element DATE;

BEGIN
    IF last_x_days = 0 THEN
      RETURN;
    END IF;

    -- Calculate the target partitions: from (current_date - last_x_days + 1) to current_date.
    SELECT ARRAY_AGG(generate_series::DATE) INTO range_days FROM generate_series(CURRENT_DATE - last_x_days + 1, CURRENT_DATE, '1 day');

    -- Start a new transaction.
    COMMIT;
    raise notice 'begin;';

    -- Enable the DML transaction.
    SET LOCAL hg_experimental_enable_transaction = on;

    -- Execute INSERT OVERWRITE for each partition sequentially.
    FOREACH element IN ARRAY range_days LOOP
        insert_sql = 'INSERT OVERWRITE ' || quote_ident(target_table::text)
        || ' PARTITION (ds = ' || quote_literal(element::text) || ')'
        || ' ' || select_sql || ' where ds = ' || quote_literal(element::text) || ';';
        EXECUTE insert_sql;
        raise notice '%', insert_sql;
    END LOOP;
    COMMIT;
    raise notice 'end;';
END;
$$;

-- Import data from the last 30 partitions.
CALL insert_overwrite_wrapper('target','select * from src', '30');