Partitioning Type Selection

RANGE Partitioning

RANGE partitioning is best suited for datasets with continuous, sequential characteristics where queries frequently target specific contiguous ranges.

HASH Partitioning

HASH partitioning is ideal when data does not follow obvious distribution rules and lacks clear range characteristics. The HASH algorithm randomly distributes rows across partitions based on the partition key value.

• Benefits of using HASH partitioning
  • Even data distribution: It ensures balanced data across partitions and supports parallel access.
  • Partition pruning: Equality queries on the partition key benefit from reduced lookup cost.
  • Avoiding I/O bottlenecks: Random distribution prevents I/O bottlenecks.
• Partition key selection
  • A unique or nearly unique column, or a combination of columns
• Partition count selection

  Power-of-two partition count: Specify the total number of partitions or subpartitions as a power of two (for example, 2, 4, 8, 16, 32, 64, or 128).

LIST Partitioning

LIST partitioning is based on enumerated values. When the partition key consists of a definite, discrete set of categorical values and queries or O&M operations target these values, LIST partitioning is the clearest and most efficient choice. It enables fast access to targeted groups of data while allowing flexible and independent management of each category. LIST partitioning is well-suited for the following scenarios:

• Enumerated or categorical fields: The partition key consists of enumerated or categorical fields, such as region with values like East, West, North, and South.
• Filtering by specific values: Queries often filter on specific values such as WHERE region = 'East' or WHERE status IN ('paid', 'shipped').
• Exact partition pruning: Only relevant partitions are scanned, avoiding full table scans.
• Independent management: Data can be managed by business domain and independent maintenance operations can be performed on specific partitions, for example, prioritizing backups on partitions storing VIP users' data.
• Stable value sets: Although new values can be added to existing or new partitions, frequently changing partition definitions (for example, on a daily basis) increase management overhead.

LIST DEFAULT HASH Partitioning

LIST DEFAULT HASH partitioning is recommended when your data meets all of the following criteria:

1. LIST rules cannot cover all possible values.
   • The partition key may have many or even unpredictable values.
   • Creating a LIST partition for each value makes partition management complex. In addition, the table structure must be continuously updated when new values are inserted. Otherwise, insertion fails because no matching partition exists.
2. LIST DEFAULT HASH partitioning ensures system stability and flexibility using DEFAULT partitioning.

   It receives all data that does not match any LIST rule, ensuring system stability and flexibility.

3. Data follows a "Long-Tail" or "80/20" distribution.
   • A small number of high-frequency values (such as top customers or popular products) account for most of the data volume (about 80%), whereas the majority of low-frequency values (such as regular users) contribute only a small portion (about 20%).
   • Storing all low-frequency values in a single large DEFAULT partition may cause that partition to become oversized and create new performance bottlenecks.
4. Using LIST DEFAULT HASH partitioning achieves load balancing.

   The DEFAULT partition is further divided into multiple HASH subpartitions, distributing large volumes of low-frequency data evenly across subpartitions to prevent data skew and achieve load balancing.

Example

Suppose that your application dataset contains many and unpredictable possible values for the partition key and the data distribution follows a long-tail pattern. For example, you have a customer table where a small number of top customers account for most of the transactions, whereas the majority of regular customers contribute only a small amount. LIST DEFAULT HASH partitioning allows you to:

• Use LIST partitioning rules to cover known top customers.
• Use a DEFAULT partition to receive regular customers' data not matched by LIST partitioning rules.
• Further divide the DEFAULT partition into multiple HASH subpartitions to evenly distribute low-frequency data and avoid performance bottlenecks.

INTERVAL Partitioning

INTERVAL partitioning is an extension of RANGE partitioning. It automatically creates new INTERVAL partitions when data arrives. This eliminates the need to manually create partitions and simplifies the maintenance of RANGE partitions.

• Behavior during data insertion
  • RANGE partitioned table: If inserted data exceeds the range of existing partitions, the insertion fails and returns an error.
  • INTERVAL RANGE partitioned table: If inserted data exceeds the range of existing partitions, the database automatically creates new partitions according to the interval specified in the INTERVAL clause.
• How to choose

  INTERVAL partitioning is recommended if your workload meets the following criteria:

  • Typical scenarios: Your application needs to process time-series data, such as system logs, IoT sensor data, and financial transaction records, where data volumes are massive and grow continuously.
  • Core requirements: Data grows at fixed time intervals (for example, daily, monthly, or yearly) and follows distinct hot/cold data access patterns (historical data is cold and recent data is hot).
  • Maintenance objectives: Partitions must be created and expanded automatically to eliminate manual intervention and ensure that future writes never fail due to missing partitions.
• Examples

  Assume that the partitioning interval is set to one month and the newly inserted data is two months beyond the current transition point (the upper boundary of the existing partitions). The database automatically creates the partition for the target month and the partitions for the intermediate months.

  For example, suppose that you create an INTERVAL RANGE partitioned table with a one-month interval and a transition point of September 15, 2021. If you insert data dated December 10, 2021, the database automatically creates three partitions covering the range from September 15, 2021, to December 15, 2021, and inserts the data into its corresponding partition.

RANGE-HASH Partitioning

RANGE-HASH partitioning is recommended when you need to manage large tables by time (or an ordered range) and the data within a single RANGE partition needs to be further distributed to balance workloads and optimize equality queries.

Examples

• Scenario: Large-Scale Logging System
  • Partitioning: RANGE partitioning is performed by log_date (day) to facilitate deletion of data older than 90 days.
  • Subpartitioning: Hundreds of millions of logs are written each day and queries are typically WHERE log_date = '2026-05-15' AND user_id = 12345.
    • Problem: If the table is partitioned only by day, each partition may still contain billions of rows, requiring a full partition scan and causing all writes to contend for the same partition.
    • Solution: Within each daily RANGE partition, HASH subpartitioning is performed by user_id (for example, 64 subpartitions). Equality queries can target a specific subpartition and writes are distributed across subpartitions to reduce lock conflicts.
• Scenario: Order Table
  • Partitioning: RANGE partitioning is performed by order_month to facilitate archiving of historical orders.
  • Subpartitioning: Orders within the same month are HASH subpartitioned by customer_id. This prevents a single subpartition from becoming too large due to high-volume activity from a single customer and also supports fast queries for a customer's orders in that month.

RANGE-RANGE Partitioning

RANGE-RANGE partitioning is suitable for applications that store time-related data across multiple time dimensions. These applications typically access data over time ranges rather than at specific time points, and sometimes use both time dimensions simultaneously.

• Application scenarios (two dimensions)

  Table 3 Application scenarios of RANGE-RANGE partitioning

  Partitioning	When to Use It
  Partitioning	Data is partitioned by ordered ranges, such as time intervals (year/month/day) or sequential ID ranges.
  Subpartitioning	Data is further partitioned by ordered ranges such as price ranges, score ranges, longitude/latitude ranges, or another time granularity (such as hours).

• Advantages

  Table 4 Advantages of RANGE-RANGE partitioning

  Advantage	Description
  Both columns in the query pattern are range conditions.	Range condition: Query conditions use range operators (such as BETWEEN, <, or >). Partition pruning: RANGE-RANGE partitioning supports pruning based on both range conditions, delivering optimal performance. Comparison with other partitioning strategies: HASH partitioning: Range queries scan all subpartitions, causing resource waste. LIST partitioning: It is not suitable for queries covering continuous ranges.
  Data is independently maintained in each partition.	Flexible subpartitioning allows data within each partition to be managed independently based on different ranges. Example: high-value orders When data volume within a monthly partition is exceptionally large, you can further create subpartitions based on price ranges. This allows you to create indexes for the high-value order subpartitions or migrate them to high-speed storage.

• Examples

  Sales data

  • Partitioning: partitioned by sale_date (quarterly)
  • Subpartitioning: subpartitioned by amount ranges (for example, 0–100, 100–1000, and 1000+) within each quarter

  Query example

  WHERE sale_date BETWEEN Q1 AND Q2 AND amount BETWEEN 200 AND 500;

  The database first identifies the Q1 and Q2 partitions and then scans only the amount subpartitions within those partitions, avoiding full-table scans or full scans across entire partitions.

RANGE-LIST Partitioning

RANGE-LIST partitioning is suitable for scenarios where data grows continuously along a time (or ordered range) dimension and data within each range must be logically grouped and managed by discrete categories. It provides the rolling management of RANGE partitioning and the precise grouping of LIST partitioning, making it ideal for "time + workload type" data models.

WHERE order_date BETWEEN ... AND order_status IN (...);