PolarDB:Guide to creating a columnstore index

Partitioning methods

Partitioning divides large tables into smaller, independently managed segments, improving query performance and data management for large datasets. The supported syntax for partitioning CCIs is:

PARTITION BY
    HASH({column_name | partition_func(column_name)})
  | KEY(column_list)
  | RANGE({column_name | partition_func(column_name)})
  | RANGE COLUMNS(column_list)
  | LIST({column_name | partition_func(column_name)})
  | LIST COLUMNS(column_list)

The available partitioning types are:

• RANGE: Divides data based on value ranges of a column. For example, partition a sales history table by year so that all records from the same year share a partition.

• LIST: Divides data based on whether column values fall within a predefined set. This is common in SaaS scenarios—for example, partitioning a user table by country.

• HASH / KEY: Divides data based on the hash value of a column, distributing rows evenly across partitions. Use this when data distribution is unpredictable or when even distribution is required.

For a complete reference, see Partitioning types.

Choose a partitioning method

CCIs are designed primarily to accelerate AP queries involving aggregations and joins. To take full advantage of columnar parallel scanning, use hash partitioning in most cases.

Follow these guidelines for specific scenarios:

For range- or list-partitioned tables: if row-oriented storage meets your query requirements, prefer it over columnar partitioning.

For time-based subpartitions, see Subpartitioning. Avoid creating subpartitions unless queries heavily rely on time-range conditions.

Configure the number of partitions

Calculate the number of partitions using this formula:

Number of computing nodes × Number of cores per compute node

The default is 16 partitions—do not use this default for production workloads. You can set the partition count higher than the formula result to accommodate future data growth.

Select a partition key

The partition key determines how data is distributed across partitions. A poor partition key leads to data skew, which concentrates load on a single partition and degrades performance.

Choose a column with evenly distributed values, such as:

• Transaction ID

• Device ID

• User ID

• Auto-increment column

Avoid time-based columns (date, timestamp) as the partition key. Most writes tend to concentrate in the same time period, causing data skew. Time-range queries also end up hitting a single partition rather than distributing load across nodes. If time filtering is important for your queries, use the time column as a subpartition key instead.

Choose a column frequently used in `JOIN` or `GROUP BY` to minimize data redistribution overhead. For example, if you analyze historical orders by customer, use the customer ID column.

Choose a column frequently used in equality or non-range conditions to enable partition pruning.

Each table has one partition key, which can consist of multiple fields. Fewer fields in the partition key generally improve adaptability in complex query scenarios.

How it works

The sort key controls how data is ordered within the CCI file. Each column data block stores the minimum and maximum values of the data it contains. When a query runs, the Pruner component evaluates these min/max values against the query conditions and classifies each block as:

• Relevant: must be scanned

• Possibly relevant: scanned as a candidate

• Irrelevant: skipped entirely

A well-chosen sort key increases the proportion of blocks Pruner can skip, directly reducing scan volume and improving query speed.

For details on Pruner, see Configure a filter algorithm for IMCI-based queries.

Select a sort key

How it works

Dictionary encoding converts string values into integers, which speeds up GROUP BY and FILTER operations, improves compression ratios, and reduces storage costs.

Specify columns for dictionary encoding when creating a CCI:

-- Specify columns for dictionary encoding.
DICTIONARY_COLUMNS='col1,col2';

-- Create a CCI with dictionary encoding.
CREATE CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`)
    PARTITION BY HASH(`order_id`) PARTITIONS 16
    DICTIONARY_COLUMNS='order_id,seller_id';

Select columns for dictionary encoding

Encode columns with low cardinality—columns with a small number of distinct values relative to total row count, such as gender or region.

Avoid encoding all character columns. Columns with high cardinality offer minimal compression benefit and add encoding and decoding overhead.