Hologres:CREATE LOGICAL PARTITION TABLE

Limits

• Only Hologres V3.1 and later instances support logical partitioned tables.

• Logical partitioned tables support only LIST partitioning. You can select a maximum of two columns as partition keys.

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

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

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

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

• When you write data to a logical partitioned table, the corresponding partitions are created automatically. Note the following limits and suggestions:

  • Optimize data write jobs and 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 an excessive number of partitions from being created.

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

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

    • For batch imports, a single import task supports a maximum of 50 partitions. Exceeding this limit results in the Bulkload partition count exceed limit, partition count is xxx, limit is xxx error.

    • When you import data using Fixed Plan, if more than 30 partitions in a single table are being written to simultaneously, new write requests for partitions are throttled. The requests are automatically submitted after a few seconds.

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

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

    • If more than 50 partitions are created per minute, new requests to create partitions are throttled. The requests are automatically submitted after a few seconds.

    • If more than 100 partitions are created per minute, subsequent requests to create partitions fail with the mem partition count exceed reject limit error.

    • For memory tables, if the total number of partitions being written to simultaneously using Fixed Plan reaches 500, new write requests for partitions are throttled and are automatically submitted after a few seconds. The total number of partitions is the sum of the following value calculated for each table: `Number of partitions written to in a single table × Number of shards / Number of workers`. For row-column hybrid tables, this value is multiplied by 2.

    • In memory tables, if the total number of partitions being written to simultaneously using a Fixed Plan reaches 5,000, new requests to write to partitions fail with a mem partition count exceed reject limit error. This total is the sum for all tables of the value calculated by `(Number of partitions written to per table × Number of shards / Number of workers)`. For hybrid row-column tables, this value is multiplied by 2.

Precautions

• Do not create partitions with a small amount of data, such as less than 100 million rows. Otherwise, query acceleration is not significant, and many small files are more likely to be generated. We recommend that you choose a larger partition granularity.

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

• Because the TRUNCATE operation does 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 partitioned table. A partition exists only if it contains data. When all data in a partition is deleted, the partition is automatically deleted.

  Note

  Because data cleanup in Hologres is an asynchronous operation, partitions are also deleted asynchronously.

• To modify the properties of a logical partitioned table, use the REBUILD syntax. The system automatically splits the task and executes it sequentially by partition. To reshard a logical partitioned table, which means modifying its Table Group, do not use the HG_MOVE_TABLE_TO_TABLE_GROUP stored procedure. For more information, see Manage Table Groups and shard counts.

Create a logical partitioned table

-- Create a logical partitioned 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 partitioned tables

When you manage data in a logical partitioned table, the following locks are applied:

• Batch import or update of a specified partition, or TRUNCATE of a specified partition: A partition metadata lock is applied. This lock does not affect data management operations on other partitions.

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

• Data write, update, or delete operations that use Fixed Plan: Row locks are applied. These operations can be affected by batch import, update, or delete operations on partitions or tables. They do not affect other data write, update, or delete operations that also use Fixed Plan.

Query a logical partitioned table

Unlike physical partitioned tables, logical partitioned tables support the partition_require_filter property. If this property is set to TRUE, queries on the logical partitioned table must include a partition filter condition.

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

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

• To query a logical partitioned table without a partition filter condition, you must set the partition_require_filter property of the parent table to FALSE.

  SELECT * FROM public.hologres_logical_parent_1;

Other operations

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

• hologres.hg_list_logical_partition('<table_name>'): Lists all partitions of a logical partitioned 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 hot and cold storage used by all partitions in a logical partitioned 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.

  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 hot and cold storage for all logical partitions in a table.

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

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

• View the Data Definition Language (DDL) statement of the logical partitioned table.

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

• View the properties of the parent table of the logical partitioned table.

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

• View the largest partition of the logical partitioned table.

  Note

  Data cleanup and partition cleanup for logical partitioned tables are asynchronous operations. To clear the data in 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 partitioned table to a logical partitioned table