@Rocky I have data that I want to partition nicely to improve query latency. I will definitely be including day partitioning in the partition columns. To determine how to partition my data, I want to understand what is the ideal number of partitions? I understand we do not want too few or too many partitions. At what sort of cardinality, causes there to be too many partitions that it negatively impacts StarRocks?

To optimize StarRocks performance and query latency, finding the right balance for your partition count is essential. Here is a breakdown of the ideal numbers, the thresholds for performance degradation, and the impacts of having too many partitions. 1. The Ideal Number of Partitions The general "sweet spot" for most StarRocks tables is between 100 and 10,000 (10² to 10⁴) partitions per table. * Daily Partitioning: This is the standard for most BI and reporting use cases. It allows for efficient data pruning and easy lifecycle management (TTL). * Hourly Partitioning: Only recommended if your data volume is massive (e.g., IoT bursts where you have multiple tablets' worth of data every hour) or if you need to expire data every few hours. * Weekly/Monthly Partitioning: Recommended if your daily data volume is very small (less than a few hundred MBs per day). Keeping metadata thin helps the Frontend (FE) stay snappy. 2. At what cardinality does it impact StarRocks? There are two critical "Red Flag" thresholds to keep in mind: * Total Partitions (>100,000): If a single table (or the system as a whole) exceeds 100,000 partitions, you will likely see a significant memory footprint on the Frontend (FE). Since partitions are catalog objects tracked in memory, having too many causes high Garbage Collection (GC) pressure and slower metadata operations. * Total Tablets per BE (>200,000): Remember that

Total Tablets = Partitions × Buckets × Replication Factor

. If your partition cardinality is high, it multiplies your tablet count. Exceeding 200,000 tablets per Backend (BE) node can cause slow compaction and high CPU usage for metadata reporting. 3. Why "Too Many" Partitions Hurt Performance If you choose a high-cardinality column (like

user_id

or

session_id

) as a partition key, you will hit these issues: 1. FE Metadata Overhead: The FE has to manage the versioning, planning, and scheduling for every partition. High cardinality turns a simple query plan into a heavy metadata task. 2. Write Amplification: Each load job creates a new "version" for every partition it touches. If you write small amounts of data to 5,000 partitions in one batch, StarRocks has to manage 5,000 small files and versions, which slows down the system. 3. Small File Problem: Partitions that are too small (e.g., only a few MBs) lead to many tiny files. StarRocks performs best when data is compacted into larger chunks (aim for 100 MB to 1 GB per bucket). 4. Rule of Thumb for Your Case Since you are using day partitioning, follow these guidelines: * Data Size: Aim to keep each partition under 100 GB. * Tablet Count: Keep each partition under 20,000 tablets across all replicas. * Batch Limits: StarRocks has a default safety limit of 4,096 partitions that can be created in a single batch (configurable via

max_partitions_in_one_batch

). If you find yourself needing more than this in one load, your granularity is likely too fine. Recommendation: If you have 3 years of data partitioned by day, you will have ~1,100 partitions. This is perfectly within the "Ideal" range. If you find your partition count creeping toward 100k, consider moving to Monthly partitioning for historical data and keeping only recent data in Daily partitions. References • faq/operation_maintenance_faq.md • knowledge/trouble_shooting/fe_memory_problems.md • best_practices/partitioning.md • faq/Others.md • knowledge/admin_and_operation/shared_data_compaction.md

@Rocky Is there any difference in query performance between partitions and tablets (distributed by). If I have a table where cardinality is too high to use

user_id

as a partition column, I could manually bucket these into partitions (say 10 different partitions). At that point, is there any difference between tablets that split data in a similar way?

While it might seem tempting to manually "bucket" high-cardinality data into partitions (e.g., using a

user_group

column), there are significant architectural and performance differences between Partitions and Tablets (Buckets) in StarRocks. The short answer: Do not use partitions to mimic buckets. For high-cardinality columns like

user_id

, you should almost always use Bucketing (Distributed By). Here is the breakdown of why they differ in performance and functionality: 1. Query Optimizations (The Biggest Difference) StarRocks has specific execution-engine optimizations that work only with Hash Bucketing: * Colocated Joins: If you bucket two tables (e.g.,

sales

and

users

) on the same

user_id

with the same number of buckets, StarRocks can perform a "local join." This means the data never leaves the BE node, eliminating network shuffle. If you use "manual partitions," the engine will likely have to shuffle the data across the network to perform the join. * Bucket-Shuffle Joins: Even if only one table is bucketed on the join key, StarRocks can shuffle the other table to match the bucket distribution, which is significantly faster than a full shuffle join. * Local Aggregation: If you

GROUP BY user_id

and the table is bucketed by

user_id

, StarRocks can perform the aggregation locally on each BE without a second phase of global aggregation. 2. Planner Visibility & Pruning * Partitions: These are "first-class" catalog objects. The Frontend (FE) sees them during the planning phase. Partition pruning is very powerful and supports range, list, and expression-based filters. * Tablets (Buckets): Pruning happens at a slightly later stage. While StarRocks can prune buckets (e.g.,

WHERE user_id = 123

will only hit one bucket), it is primarily designed for parallelism. * The Risk: If you create 10 manual partitions for

user_id

groups and then 1 bucket each, you are limiting your parallelism to only 10 "tasks" for that data slice. If you have a 20-node cluster, 10 nodes will sit idle. 3. Metadata Overhead * Partitions are "Heavy": Every partition has its own versioning, metadata entry in the FE, and lifecycle management. Creating "pseudo-buckets" as partitions unnecessarily bloats the FE memory and can slow down metadata operations (like

SHOW TABLES

or query planning). * Tablets are "Light": Tablets are the physical storage units. While you shouldn't have millions of them, they are much more efficient for the FE to handle than the same number of partitions. 4. Lifecycle vs. Distribution * Partitioning is for Lifecycle: Use it for things you want to drop, archive, or backup as a unit (e.g., "Delete all data from January"). * Bucketing is for Distribution: Use it to spread the load and optimize joins/aggregations on high-cardinality keys. Summary Recommendation Feature | Manual "Bucket" via Partition | Standard Hash Bucketing Join Performance | Slow (Network Shuffle) | Fast (Colocated / Bucket Shuffle) Aggregation | High overhead | Local (Fast) FE Metadata | Heavy / Risks GC issues | Lean / Efficient Flexibility | Hard to manage | Automatic Your ideal schema should look like this:

sql
CREATE TABLE my_table (
    dt DATE,
    user_id BIGINT,
    ...
)
PARTITION BY (dt) -- For day partitioning (Lifecycle)
DISTRIBUTED BY HASH(user_id) BUCKETS 32; -- For user_id (Performance/Join optimization)

Stick to Day Partitioning for your time-series needs and Hash Bucketing for your

user_id

performance. This gives you the best of both worlds: efficient data pruning by date and high-speed local joins/aggregations on user data on the user level. References • best_practices/partitioning.md • best_practices/bucketing.md