Large wide tables can create storage hot spots and degrade query performance when all data lands in a single index shard. Partition indexing solves this by splitting a search index into smaller, independently managed partitions. When you create a search index, specify a partitioning policy so the server distributes data automatically. During queries, the system uses partition pruning to scan only the partitions that contain matching data.
Two policies are available:
Hash partitioning — routes rows to partitions by hashing a partition key column. Equality queries on that column trigger precise partition pruning.
Time partitioning — groups records by time range. New partitions are created automatically on a rolling schedule, and expired partitions are deleted based on a Time to Live (TTL) policy.
Prerequisites
Before you begin, ensure that you have:
Choose a partitioning policy
Use this table to select the right policy for your workload before creating an index.
| Hash partitioning | Time partitioning | |
|---|---|---|
| Best for | Equality queries on a high-cardinality column (device ID, store ID) | Time series data with range queries on a timestamp column (IoV, order records, message logs) |
| Partition pruning | Triggered only by equality conditions (=). | Triggered by time range conditions. Scans only the partitions that cover the specified period. |
| Data lifecycle | Partitions persist; no built-in TTL | Partitions created automatically on a rolling schedule; old partitions deleted by TTL |
| Partition key change | Not allowed after index creation | Not applicable |
| Limitations | Up to three levels of nesting; cannot combine with time partitioning | Requires a timestamp column in the index |
Set up a test table
All examples in this topic use the following table. Create it before running the examples.
CREATE TABLE IF NOT EXISTS search_table (
user_id BIGINT,
storeId VARCHAR,
goodsId VARCHAR,
goodsPrice SMALLINT,
orderTime BIGINT,
info VARCHAR,
PRIMARY KEY (user_id ASC)
);Hash partitioning
Hash partitioning routes rows to partitions by hashing the partition key. Rows with identical key values always land in the same partition group, so an equality query on that key scans only the relevant partitions.
A hash partition configuration cannot be changed after the index is created. Estimate your data volume and target partition count before creating the index.
Size partitions correctly
Each partition should hold 50 million to 100 million records with a storage size of 30 GB to 50 GB. The default partition count is twice the number of search nodes.
Create a hash-partitioned index
Default (partition by primary key)
Without a PARTITION BY clause, the index partitions data by the wide table's primary key.
CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice);Partition by a specific column
Partition by storeId with 64 partitions. Use this when queries always include an equality condition on storeId.
CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice)
PARTITION BY hash(storeId) PARTITIONS 64;Use multi-level partitioning to fix data skew
If the primary partition key causes uneven data distribution (hot spots), add a secondary partition key to spread data more evenly across partitions.
Wide table engine version 2.8.1 or later is required.
Search engine version 3.9.22 or later is required.
Multi-level partitioning cannot be combined with time partitioning.
How it works
Each partition key gets a salt_factor — a small integer that controls how finely that key is hashed. The total number of partitions must be a power of 2 (2^N), and the sum of all salt_factor values must equal N.
For example, with 2 billion records and a target of 50 million records per partition, you need approximately 40 partitions. Round up to 64 (2^6), so N = 6 and the salt factors must sum to 6.
Tradeoff: Increasing the salt_factor for one key improves data distribution along that dimension but reduces pruning precision when you query by a different key. For example, if storeId has salt_factor=2, queries on storeId alone prune to 1/4 (1/2^2) of all partitions. Increasing it to salt_factor=3 prunes to 1/8 of all partitions — but leaves less room for other keys to contribute to the distribution.
Multi-level hash partition example
Partition by storeId (salt_factor=2) and goodsId (salt_factor=4), with 64 total partitions. Rows with the same storeId but different goodsId values are distributed across 1/4 of all partitions.
CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice)
PARTITION BY hash(storeId(salt_factor=2), goodsId(salt_factor=4)) PARTITIONS 64;Use `_id` as the secondary partition key
If no suitable secondary business column is available, use _id (the wide table's composite primary key) as the secondary partition key to break up hot spots.
CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice)
PARTITION BY hash(storeId(salt_factor=2), _id(salt_factor=4)) PARTITIONS 64;Time partitioning
Time partitioning organizes index data into partitions by time range — for example, by week or month. The system automatically creates new partitions on a rolling schedule and removes partitions whose data has exceeded the TTL.
Partition lifecycle:
Create — New partitions are created automatically based on
RANGE_TIME_PARTITION_INTERVAL.Retain — Partition data is kept for the number of days defined by
RANGE_TIME_PARTITION_TTL.Delete — Partitions older than the TTL are automatically removed.
Create a time-partitioned index
Weekly partitions, 90-day retention
Partitions start from 30 days before index creation. A new partition is created every 7 days. Data older than 90 days is deleted automatically.
CREATE INDEX idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice, orderTime)
PARTITION BY RANGE time(orderTime) PARTITIONS 4
WITH (
indexState=ACTIVE,
RANGE_TIME_PARTITION_START='30',
RANGE_TIME_PARTITION_INTERVAL='7',
RANGE_TIME_PARTITION_TTL='90'
);Monthly partitions, 6-month retention, second-precision timestamps
Partitions start from 180 days before index creation. A new partition is created every 30 days. Data older than 180 days is deleted automatically. The orderTime column stores Unix timestamps in seconds (10-digit values).
CREATE INDEX idx USING SEARCH ON search_table (storeId, goodsId, goodsPrice, orderTime)
PARTITION BY RANGE time(orderTime) PARTITIONS 4
WITH (
indexState=ACTIVE,
RANGE_TIME_PARTITION_START='180',
RANGE_TIME_PARTITION_INTERVAL='30',
RANGE_TIME_PARTITION_TTL='180',
RANGE_TIME_PARTITION_FIELD_TIMEUNIT='s'
);Time partitioning parameters
| Parameter | Required | Description |
|---|---|---|
RANGE_TIME_PARTITION_START | Yes | Number of days before index creation from which to start creating partitions. If the timestamp of historical data is earlier than the partition start time, an error is reported. |
RANGE_TIME_PARTITION_INTERVAL | Yes | Interval in days between automatically created partitions. For example, 7 creates a new partition every week. |
RANGE_TIME_PARTITION_TTL | No | Number of days to retain partition data. Partitions older than this value are deleted automatically. |
RANGE_TIME_PARTITION_FIELD_TIMEUNIT | No | Time unit of the partition field. Default: ms (milliseconds, 13-digit timestamp). Set to s for second-precision timestamps (10-digit). |
RANGE_TIME_PARTITION_MAX_OVERLAP | No | Maximum allowed interval in days between a future timestamp and the current time. If not set, future timestamps have no upper limit. |
RANGE_TIME_PARTITION_FORMAT | No | Write format of the time partition field. Default: date_optional_time||epoch_millis. Takes effect when the partition field is of the VARCHAR type. |
RANGE_TIME_PARTITION_START sets the historical boundary for indexing. For example, assume the earliest date in the orderTime column is March 16, 2021. To retain all historical data, calculate the number of days between March 16, 2021, and the day you create the index, then set that value as RANGE_TIME_PARTITION_START. If the timestamp of historical data is earlier than the partition start time, an error is reported.