Hologres:CREATE LOGICAL PARTITION TABLE

Limitations

• Only LIST partitioning is supported. RANGE and HASH are not supported.

• A partition key can include one or two columns.

• Supported partition key data types: INT, TEXT, VARCHAR, DATE, TIMESTAMP, and TIMESTAMPTZ.

• Partition keys must be NOT NULL.

• The partition key of a logical partition table supports generated columns.

• A single logical partition table supports up to 5,200 partitions. A database supports up to 200,000 logical partitions total.

Usage notes

• Partition granularity: Avoid partitions with fewer than 100 million rows. Fine-grained partitions reduce query acceleration benefits and increase the risk of small file accumulation. For example, partitioning by day is reasonable for high-volume workloads; partitioning by client ID or sub-hour intervals is usually too fine.

• Write pattern: Write data to partitions sequentially. Avoid writing to many partitions simultaneously.

• Data quality: Keep incoming data clean. Dirty data—such as timestamps that don't fall at midnight when using daily partitions—can cause unexpected partition growth.

• Partition lifecycle: Do not manually create or delete partitions. Partitions exist only when they contain data. When all data in a partition is deleted, Hologres removes the partition asynchronously.

• TRUNCATE and Binlog: TRUNCATE does not generate Binlog. To disable Binlog generation for a session, run SET hg_experimental_generate_binlog = off before TRUNCATE.

• Table property changes: To modify table properties on a logical partition table, use the REBUILD syntax. The backend automatically splits the task and executes it sequentially by partition. For resharding operations (for example, moving the table to a different Table Group), do not use the HG_MOVE_TABLE_TO_TABLE_GROUP stored procedure. See the Table Group and Shard Count Operation Guide for the correct approach.

Create a logical partition table

-- Create a logical partition parent table
CREATE TABLE [IF NOT EXISTS] [<schema_name>.]<table_name> ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
LOGICAL PARTITION BY LIST(<partition_column_1> [, <partition_column_2>])
[WITH(
  <property_name> = <property_value>
 [, ...]
)];

CREATE TABLE public.hologres_logical_parent_1 (
    a TEXT,
    b INT,
    c TIMESTAMP,
    ds DATE NOT NULL,
    PRIMARY KEY (b, ds))
LOGICAL PARTITION BY LIST (ds)
WITH (
    orientation = 'column',
    distribution_key = 'b',
    partition_expiration_time = '30 day',
    partition_keep_hot_window = '15 day',
    partition_require_filter = TRUE,
    binlog_level = 'replica',
    partition_generate_binlog_window = '3 day'
);

CREATE TABLE public.hologres_logical_parent_2 (
    a TEXT,
    b INT,
    c TIMESTAMP,
    ds TIMESTAMP GENERATED ALWAYS AS (date_trunc('day', c)) STORED NOT NULL,
    PRIMARY KEY (b, ds))
LOGICAL PARTITION BY LIST (ds)
WITH (
    orientation = 'column',
    distribution_key = 'b',
    partition_expiration_time = '30 day',
    partition_keep_hot_window = '15 day',
    partition_require_filter = TRUE,
    binlog_level = 'replica',
    partition_generate_binlog_window = '3 day'
);

CREATE TABLE public.hologres_logical_parent_3 (
    a TEXT,
    b INT,
    yy TEXT NOT NULL,
    mm TEXT NOT NULL)
LOGICAL PARTITION BY LIST (yy, mm)
WITH (
    orientation = 'column',
    distribution_key = 'b',
    partition_require_filter = TRUE
);

Manage data

-- Write data; Hologres creates partitions automatically
INSERT INTO public.hologres_logical_parent_2
VALUES
    ('a', 1, '2025-03-16 10:00:00'),
    ('b', 2, '2025-03-17 11:00:00'),
    ('c', 3, '2025-03-18 12:00:00'),
    ('d', 4, '2025-03-19 13:00:00'),
    ('e', 5, '2025-03-20 14:00:00');

-- Delete rows (table lock)
DELETE FROM public.hologres_logical_parent_2 WHERE ds = '2025-03-20';

-- Truncate the entire parent table (no Binlog generated)
SET hg_experimental_generate_binlog = off;
TRUNCATE public.hologres_logical_parent_2;

-- Import into a specified partition
INSERT INTO public.hologres_logical_parent_1
PARTITION (ds = '2025-03-16')
VALUES
    ('a', 1, '2025-03-16 10:00:00', '2025-03-16');

-- Rows that don't match the specified partition are silently skipped (no error)
INSERT INTO public.hologres_logical_parent_1
PARTITION (ds = '2025-03-16')
VALUES
    ('a', 1, '2025-03-16 10:00:00', '2025-03-16'),
    ('b', 2, '2025-03-17 11:00:00', '2025-03-17');

-- Delete rows from specific partitions (table lock)
DELETE FROM public.hologres_logical_parent_1 WHERE ds = '2025-03-16' or ds = '2025-03-17';

-- Truncate specific partitions (partition lock, no Binlog generated)
-- Not supported in compute group DML; run in the leader compute group.
SET hg_experimental_generate_binlog = off;
TRUNCATE public.hologres_logical_parent_1 PARTITION (ds = '2025-03-16') PARTITION (ds = '2025-03-17');

Query logical partition tables

-- Query with a partition filter condition
SELECT * FROM public.hologres_logical_parent_1 WHERE ds = '2025-03-16';

-- Query without a partition filter condition
-- Requires partition_require_filter = FALSE on the parent table
SELECT * FROM public.hologres_logical_parent_1;

If partition_require_filter is set to TRUE, queries without a partition filter condition fail.

View metadata

Hologres provides system tables and functions to query metadata for logical partition tables.

Check whether a table is a logical partition table:

SELECT *
FROM hologres.hg_table_properties
WHERE
    table_name = '<table_name>'
    AND property_key = 'is_logical_partitioned_table'
    AND property_value = 'true';

List all partitions:

SELECT * FROM hologres.hg_list_logical_partition('<schema_name>.<table_name>');

List partition property configurations:

This query returns only configurations that differ between child partitions and the parent table. An empty result means no partition-level overrides are set.

SELECT *
FROM hologres.hg_logical_partitioned_table_properties
WHERE
    table_namespace = '<schema_name>'
    AND table_name = '<table_name>'
ORDER BY partition DESC;

View hot and cold storage sizes:

SELECT * FROM hologres.hg_partition_file_status('<schema_name>.<table_name>');

Logical partitions are also compatible with standard Hologres system tables.

View the DDL for a logical partition table:

SELECT hg_dump_script('<schema_name>.<table_name>');

View parent table properties:

SELECT *
FROM hologres.hg_table_properties
WHERE
    table_namespace = '<schema_name>'
    AND table_name = '<table_name>';

View the largest partition:

Because data cleanup and partition cleanup are asynchronous, MAX_PT may return incorrect results if the largest partition has been emptied. Use INSERT OVERWRITE to delete data to avoid this issue.

SELECT MAX_PT('<schema_name>.<table_name>');

Check whether any partition table exceeds the partition limit:

CREATE OR REPLACE PROCEDURE check_logical_partition_count()
LANGUAGE 'plpgsql'
AS $$
DECLARE
    table_max_partition_count bigint;
    table_partition_count bigint;
    exceeded_logical_partition_limit boolean;
    row_record record;
BEGIN
    SELECT substring(result FROM '^[^:]*: (\d+)')::INTEGER INTO table_max_partition_count
        FROM hg_admin_command('get_global_flag', 'flag=table_max_partition_count') AS result;

    RAISE NOTICE 'table_max_partition_count=%', table_max_partition_count;

    FOR row_record IN
        SELECT table_namespace, table_name
            FROM hologres.hg_table_properties
            WHERE property_key = 'is_logical_partitioned_table' AND (property_value = 'true' OR property_value = 't')
    LOOP
        SELECT count(*) INTO table_partition_count
            FROM hologres.hg_list_logical_partition(quote_ident(row_record.table_namespace) || '.' || quote_ident(row_record.table_name));
        IF table_partition_count > table_max_partition_count THEN
            RAISE NOTICE 'table %.% partition count exceeds limit (% > %)', row_record.table_namespace, row_record.table_name, table_partition_count, table_max_partition_count;
        END IF;
    END LOOP;
END;
$$;

CALL check_logical_partition_count();

What's next

• ALTER LOGICAL PARTITION TABLE

• DROP LOGICAL PARTITION TABLE

• Migrate a physical partition table to a logical partition table