List partitioning

Since v3.1, StarRocks supports list partitioning. Data is partitioned based on a predefined value list for each partition, which can accelerate queries and facilitate management according to enumerated values.

Introduction

You need to explicitly specify the column values list in each partition. These values do not need to be continuous, unlike the continuous time or numeric range required in Range Partitioning. During data loading, StarRocks will store the data in the corresponding partition based on the mapping between the data's partitioning column values and the predefined column values for each partition.

List partitioning is suitable for storing data whose columns contain a small number of enum values, and you often query and manage data based on these enum values. For example, columns represent geographical locations, states, and categories. Each value in a column represents an independent category. By partitioning data based on enum values, you can improve query performance and facilitate data management.

List partitioning is particularly useful for scenarios where a partition needs to include multiple values for each partitioning column. For example, if a table includes the City column representing the city where an individual is from, and you often query and manage data by states and cities. At table creation, you can use the City column as the partitioning column for List Partitioning and specify that data of various cities within the same state are placed in one partition, for example PARTITION pCalifornia VALUES IN ("Los Angeles", "San Francisco", "San Diego"). This method can accelerate queries based on states and cities while facilitating data management.

If a partition only needs to contain data with the same value of each partitioning column, it is recommended to use expression partitioning.

Comparison between list partitioning and expression partitioning

The main difference between list partitioning and expression partitioning (recommended) is that list partitioning requires you to manually create partitions one by one. On the other hand, expression partitioning can automatically create partitions during loading to simplify partitioning. And in most cases, expression partitioning can replace list partitioning. The specific comparison between the two is shown in the following table:

Usage

Syntax

PARTITION BY LIST (partition_columns)（
    PARTITION <partition_name> VALUES IN (value_list)
    [, ...]
)

partition_columns::= 
    <column> [,<column> [, ...] ]

value_list ::=
    value_item [, value_item [, ...] ]

value_item ::=
    { <value> | ( <value> [, <value>, [, ...] ] ) }    

Parameters

Examples

Example 1: Suppose you frequently query details of data center billing based on states or cities. At table creation, you can specify the partitioning column as city and specify that each partition stores data of cities within the same state. This method can accelerate queries for specific states or cities and facilitate data management.

CREATE TABLE t_recharge_detail1 (
    id bigint,
    user_id bigint,
    recharge_money decimal(32,2), 
    city varchar(20) not null,
    dt varchar(20) not null
)
DUPLICATE KEY(id)
PARTITION BY LIST (city) (
   PARTITION pLos_Angeles VALUES IN ("Los Angeles"),
   PARTITION pSan_Francisco VALUES IN ("San Francisco")
)
DISTRIBUTED BY HASH(`id`);

Example 2: Suppose you frequently query details of data center billing based on time ranges and specific states or cities. During table creation, you can specify the partitioning columns as dt and city. This way, data of specific dates and specific states or cities is stored into the same partition, improving query speed and facilitating data management.

CREATE TABLE t_recharge_detail4 (
    id bigint,
    user_id bigint,
    recharge_money decimal(32,2), 
    city varchar(20) not null,
    dt varchar(20) not null
) ENGINE=OLAP
DUPLICATE KEY(id)
PARTITION BY LIST (dt,city) (
   PARTITION p202204_California VALUES IN (
       ("2022-04-01", "Los Angeles"),
       ("2022-04-01", "San Francisco"),
       ("2022-04-02", "Los Angeles"),
       ("2022-04-02", "San Francisco")
    ),
   PARTITION p202204_Texas VALUES IN (
       ("2022-04-01", "Houston"),
       ("2022-04-01", "Dallas"),
       ("2022-04-02", "Houston"),
       ("2022-04-02", "Dallas")
   )
)
DISTRIBUTED BY HASH(`id`);

Limits

• List partitioning does support dynamic partitoning and creating multiple partitions at a time.
• Currently, StarRocks's shared-data mode does not support this feature.
• When the ALTER TABLE <table_name> DROP PARTITION <partition_name>; statement is used to delete a partition created by using list partitioning, data in the partition is directly removed and cannot be recovered.
• Currently you cannot backup and restore partitions created by the list partitioning.
• Currently, StarRocks does not support creating asynchronous materialized views with base tables created with the list partitioning strategy.