Hologres:CREATE LOGICAL PARTITION TABLE

Limits

• Only Hologres instances running version 3.1 or later support logical partition tables.

• Logical partitioned tables support only LIST partitioning and allow you to specify a partition key consisting of up to two columns.

• Supported data types for partition keys include INT, TEXT, VARCHAR, DATE, TIMESTAMP, and TIMESTAMPTZ.

• Partition keys must be defined as NOT NULL.

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

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

• When you write data to a logical partition table, Hologres automatically creates the required partitions. The following limits and recommendations apply:

  • Optimize your data ingestion jobs and data quality. Avoid dirty data—for example, avoid timestamps that do not fall at midnight when using daily partitions. This helps prevent excessive partition growth.

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

  • Batch import jobs for a single table have the following limits:

    • During batch import, a single job supports importing up to 50 partitions. If you exceed this limit, Hologres returns the error: Bulkload partition count exceed limit, partition count is xxx, limit is xxx.

    • When using Fixed Plan to import data, if a single table writes to more than 30 partitions simultaneously, new partition write requests trigger throttling. Hologres waits several seconds before automatically submitting the request.

    • When using Fixed Plan to import data, if a single table writes to more than 100 partitions simultaneously, new partition write requests fail with the error: mem partition count exceed reject limit.

  • To avoid overloading a worker node due to too many unpersisted partitions, each worker node enforces the following limits:

    • If the number of unpersisted partitions exceeds 500, new partition write requests trigger throttling. Hologres waits several seconds before automatically submitting the request.

    • If the number of unpersisted partitions exceeds 5,000, new partition write requests fail with the error: mem partition count exceed reject limit.

    • Note: An “unpersisted partition” is a partition written to memory but not yet flushed to disk. The count includes user partitions × shard count × index count per table. Shard count includes replica shards. Therefore, this limit depends on shard distribution and how the table is distributed across nodes in the instance.

Important notes

• Avoid creating partitions with very small data volumes—for example, less than 100 million rows. Small partitions reduce query acceleration benefits and increase the risk of generating many small files. Use coarser-grained partitions instead.

• If you frequently replace all data in a partition—for example, using TRUNCATE or INSERT OVERWRITE—use a partitioned table. For this scenario, TRUNCATE and INSERT OVERWRITE perform better because they avoid large-scale delete operations.

• The TRUNCATE statement does not generate binary logs (Binlog). To disable Binlog generation for a session, run: SET hg_experimental_generate_binlog = off.

• You do not manually create or delete partitions for logical partition tables. Partitions exist only when they contain data. When all data in a partition is deleted, the partition is automatically removed.

  Note

  Because Hologres cleans up data asynchronously, partitions are also removed asynchronously.

• To modify the table properties of a logical partitioned table, use the REBUILD syntax. The backend automatically splits tasks and executes them sequentially by partition. Specifically, to perform Resharding operations on a logical partitioned table—for example, modifying the table’s Table Group—do not use the original HG_MOVE_TABLE_TO_TABLE_GROUP stored procedure. For more information, see Table Group and Shard Count Operation Guide.

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>
 [, ...]
)];

Data management for logical partition tables

When managing data in logical partition tables, the following locks apply:

• Batch import or update for a specified partition, or TRUNCATE for a specified partition: Partition lock. Other partitions remain unaffected.

• Batch import or update without specifying a partition, TRUNCATE without specifying a partition, or any DELETE: Table lock. Other data management operations wait until the lock is released.

• Data write, update, or delete using Fixed Plan: Row lock. These operations conflict with batch import, update, or delete operations on partitions or tables. They do not conflict with other Fixed Plan operations.

Query logical partition tables

Unlike physical partition tables, logical partition tables let you configure the partition_require_filter property. If you set this property to TRUE, queries must include a partition filter condition.

• Query a logical partition table with a partition filter condition.

  SELECT * FROM public.hologres_logical_parent_1 WHERE ds = '2025-03-16';

• Query a logical partition table without a partition filter condition. This requires the parent table’s partition_require_filter property to be set to FALSE.

  SELECT * FROM public.hologres_logical_parent_1;

Other operations

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

• hologres.hg_table_properties: View table properties.

• hologres.hg_list_logical_partition('<table_name>'): List all partitions in a logical partition table.

• hologres.hg_logical_partitioned_table_properties: List all logical partitions and their properties in the current instance.

• hologres.hg_partition_file_status('<table_name>'): Query hot and cold storage sizes and file counts for all partitions in a logical partition table. Supported in Hologres version 3.1.4 and later.

Examples:

• Determine 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 logical partitions for a table.

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

• List all logical partition property configurations for a table.

Note: This query shows only configurations that differ between child partitions and the parent table. An empty result means no special configuration exists for child partitions.

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

• View current hot and cold storage sizes and file counts for all logical partitions in a table.

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

In addition to the system tables described earlier, logical partitions—as physical tables—are also compatible with other Hologres system tables and support the same metadata query methods as standard tables. Example:

• 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 for a logical partition table.

  Note

  Because data cleanup and partition cleanup are asynchronous, max_pt may return incorrect results if the largest partition has been emptied. To avoid this, use INSERT OVERWRITE to delete data instead of other methods.

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

• Check whether the number of partitions exceeds the limit.

  Call the stored procedure check_logical_partition_count() to check whether any logical partition table in the current instance 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();

References

• ALTER LOGICAL PARTITION TABLE

• DROP LOGICAL PARTITION TABLE

• Migrate a physical partition table to a logical partition table