Hologres:CREATE LOGICAL PARTITION TABLE

Limits

• Only Hologres V3.1 and later supports logical partition tables.

• Logical partition tables support only LIST partitioning. You can specify up to two partition key columns.

• The partition key for a logical partition table supports the following data types: INT, TEXT, VARCHAR, DATE, TIMESTAMP, and TIMESTAMPTZ.

• The partition key for a logical partition table must have the NOT NULL property.

• The partition key for a logical partition table can be a generated column.

• A single logical partition table supports a maximum of 5,200 partitions. A database supports a maximum of 200,000 logical partitions in total.

• Because logical partition tables automatically create partitions when you write data, the following limits and recommendations apply:

  • Optimize data write jobs and use Data Quality to avoid dirty data. For example, if you partition data by day, avoid data with timestamps other than 00:00:00. This practice prevents the excessive expansion of partitions.

  • Write data to partitions sequentially to avoid writing to too many partitions at the same time.

  • The following limits apply to import jobs for a single table:

    • During a batch import, a single import job can import data into a maximum of 50 partitions. If this limit is exceeded, the error message Bulkload partition count exceed limit, partition count is xxx, limit is xxx is returned.

    • When you import data using a Fixed Plan, if the number of partitions being written to simultaneously in a single table exceeds 30, new write requests are throttled. The requests are automatically committed after a few seconds.

    • When you import data using a Fixed Plan, if the number of partitions being written to simultaneously in a single table exceeds 100, new write requests fail with the error message mem partition count exceed reject limit.

  • The following throttling mechanisms apply at the instance or compute group level:

    • If more than 50 new partitions are created per minute, new partition creation requests are throttled. The requests are automatically committed after a few seconds.

    • If more than 100 new partitions are created per minute, new partition creation requests fail with the error message mem partition count exceed reject limit.

    • For in-memory tables, when you use a Fixed Plan, if the total number of partitions being written to simultaneously reaches 500, new write requests are throttled. The requests are automatically committed after a few seconds. The total number is calculated using the following formula: (Number of partitions written to in a single table × Number of shards / Number of workers). For tables that use both row and column storage, multiply the result by 2. Then, sum the results for all tables.

    • For in-memory tables, when you use a Fixed Plan, if the total number of partitions being written to simultaneously reaches 5,000, new write requests fail with the error message mem partition count exceed reject limit. The total number is calculated using the following formula: (Number of partitions written to in a single table × Number of shards / Number of workers). For tables that use both row and column storage, multiply the result by 2. Then, sum the results for all tables.

Notes

• Avoid using partitions with a very small data volume, such as less than 100 million rows. This practice can reduce query acceleration performance and create many small files. Instead, use a larger partition granularity.

• If you frequently need to replace all data in a partition using operations such as `TRUNCATE` or `INSERT OVERWRITE`, use partitioned tables. They provide better performance and help avoid large-scale delete operations.

• `TRUNCATE` operations do not support binary logging. You must execute the SET hg_experimental_generate_binlog = off command at the session level.

• You do not need to manually create partitions for a logical partition table. A partition exists only if it contains data. When all data in a partition is deleted, the partition is also automatically deleted.

  Note

  Hologres performs data cleanup as an asynchronous operation. Therefore, partitions are also deleted asynchronously.

• To modify the properties of a logical partition table, use the REBUILD syntax. The system automatically splits the task and executes it sequentially by partition. To perform a resharding operation, which modifies the table's table group, do not use the `HG_MOVE_TABLE_TO_TABLE_GROUP` stored procedure. For more information, see Table Group and shard count operations.

Create a logical partition table

-- Statement to create a logical parent partition 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>
 [, ...]
)];

Manage data in a logical partition table

The following locks are acquired when you manage data in a logical partition table:

• Batch import or update for a specified partition, or `TRUNCATE` for a specified partition: A partition metadata lock is acquired. Data management in other partitions is not affected.

• Batch import or update without specifying a partition, `TRUNCATE` without specifying a partition, or any `DELETE` operation: A table lock is acquired. Other data management operations must wait for the lock to be released.

• Data write, update, or delete operations using a Fixed Plan: Row locks are acquired. These operations can interfere with batch import, update, or delete operations on partitions or tables. They do not affect other data write, update, or delete operations that use a Fixed Plan.

Query a logical partition table

Unlike physical partition tables, logical partition tables support the `partition_require_filter` property. If this property is set to TRUE, the search statement must include a filter condition on the partition key when you query the table.

• Query a logical partition table with a filter condition on the partition key.

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

• Query a logical partition table without a filter condition on the partition key. This requires the `partition_require_filter` property of the parent table to be FALSE.

  SELECT * FROM public.hologres_logical_parent_1;

Other operations

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

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

• hologres.hg_logical_partitioned_table_properties: Lists all logical partitions and their property configurations in the current instance.

• hologres.hg_partition_file_status('<table_name>'): Starting from Hologres V3.1.4, you can use this function to query the amount of data stored in hot and cold storage for all partitions in a logical partition table.

Examples:

• View all logical partitions of a table.

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

• View the property configurations of all logical partitions of a table.

Note: This query displays only the configurations of subpartitions that differ from the parent table. If the result is empty, the subpartitions have no special configurations.

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

• View the current amount of data in hot and cold storage for all logical partitions in the table.

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

In addition to the preceding system tables, a logical partition table is a physical table. It is compatible with other Hologres system tables and supports the same metadata query methods as standard tables. The following examples show how to query metadata:

• View the DDL statement of a logical partition table.

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

• View the properties of the parent table of a logical partition table.

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

• View the largest partition of a logical partition table.

  Note

  Data cleanup and partition cleanup for logical partition tables are asynchronous operations. To clear data from the largest partition, use the `INSERT OVERWRITE` operation to delete the data. Otherwise, the `MAX_PT` function might return an incorrect result.

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

References

• ALTER LOGICAL PARTITION TABLE

• DROP LOGICAL PARTITION TABLE

• Migrate a physical partition table to a logical partition table