@Rocky [ Q2. For full-scan aggregation workloads, what are the trade-offs between using fixed bucketing vs. time-based partitioning, and is it still beneficial to keep coarse (e.g., YEAR) partitioning with automatic bucketing for future CN scale-out? ] due to the query characteristics, we always perform a full scan over the entire dataset for aggregation. In this case, when there are many time-based partitions, we’ve observed that the Schedule Time actually becomes longer, which negatively impacts performance. As a result, we tested using YEAR-level partitioning or even no partitioning at all, and found that both performed significantly better than DAY-level partitioning. Additionally, compared to YEAR partitioning, we achieved even better performance by removing time-based partitioning entirely and instead using a fixed number of buckets (e.g., 48). (For reference, the automatic bucket setting resulted in 16 buckets.) Given this, I have a few questions: • Are there any trade-offs or downsides when using fixed bucketing? • Even if we don’t use fine-grained partitions, would it still be beneficial to keep YEAR-level partitioning with automatic bucketing, especially considering future CN node scale-out?

Hi there! This is Rocky, and I'd be happy to help clarify the trade-offs between partitioning and bucketing for your full-scan aggregation workloads. Your observation is spot on: for full-scan workloads, having too many partitions (like DAY-level) creates a massive amount of metadata for the Frontend (FE) to process. Every partition multiplied by the bucket count creates a "tablet." If you have 3 years of daily data with 16 buckets, that’s over 17,000 tablets that the scheduler must handle for a single query. This is exactly why you're seeing longer Schedule Time. Here is a breakdown of your specific questions: 1. Trade-offs or downsides when using fixed bucketing? Using a fixed number of buckets (e.g.,

BUCKETS 48

) instead of automatic bucketing has several implications: * Pro: Guaranteed Parallelism: Fixed bucketing ensures that even with small datasets or few nodes, you have enough tablets to saturate your CPU cores (Pipeline DOP). Automatic bucketing sometimes targets a "safe" tablet size (100MB–1GB) which might result in too few tablets for high-concurrency scanning on large machines. * Con: Manual Scaling: If you expand your cluster significantly (e.g., from 8 nodes to 40 nodes), a fixed 48 buckets might become a bottleneck because you won't have enough tablets to distribute across all new nodes effectively. * Con: Tablet Size Risk: As your data grows, fixed buckets mean your tablets will grow indefinitely. If a single tablet exceeds 10 GB (compressed), you may encounter performance degradation in data compaction and significantly slower data recovery if a node fails. * Con: Potential Skew: If your

DISTRIBUTED BY

key has low cardinality or distribution skew, a fixed bucket count makes it harder to "smooth out" that skew compared to a higher, more granular bucket count. 2. Is YEAR-level partitioning + Automatic Bucketing beneficial for future CN scale-out? Yes, keeping YEAR-level partitioning is highly recommended, even if you don't use it for pruning. Here’s why it’s better than "no partitioning": * Query Cache Efficiency: StarRocks' Query Cache (especially the Partition-level Cache) works by caching results for individual partitions. If you use YEAR partitioning, StarRocks can cache the results of 2023 and 2024. When you query again, it only needs to scan the "active" 2025 partition and merge it with the cached results. With "no partitioning," any new data ingestion invalidates the cache for the entire table. * Future Scale-out (Auto-Bucketing): Automatic bucketing in StarRocks (v3.0+) calculates the bucket count based on the number of BE/CN nodes at the time the partition is created. If you scale your CN nodes next year, the new YEAR partition will automatically be created with a higher bucket count to match your larger cluster, while your older partitions remain stable. * Reduced Scheduling Overhead: Since you only have one partition per year, the total tablet count remains low (e.g., 3 years * 48 buckets = 144 tablets), which keeps Schedule Time near-instant while still giving you the benefits of a structured table. * Data Lifecycle (TTL): Even if you don't need it now, YEAR partitioning allows you to easily drop old data (

DROP PARTITION

) or move older years to cheaper cold storage (Tiered Storage) without rewriting the whole table. Rocky’s Recommendation: Since you found that 48 buckets performed better than the automatic 16, you can actually "nudge" the automatic bucketing to be more aggressive while keeping the benefits of YEAR partitioning: 1. Use YEAR-level partitioning. 2. Use Automatic Bucketing with a hint: In recent versions, you can set the

bucket_size

property. By setting a smaller

bucket_size

(e.g.,

100MB

), StarRocks will be forced to create more buckets for your data volume, effectively giving you that higher parallelism you desire while remaining "automatic" for future growth.