@Rocky if I use DISTRIBUTED BY HASH without specifying number of buckets - how does it work exactly, will each partition have optimal number of buckets automatically depending on the size of it? Will it shrink/grow as ingestion / compaction runs? What are the limits of automated bucket count mechanism

When you use

DISTRIBUTED BY HASH

without specifying the number of buckets, StarRocks uses an automatic bucketing mechanism. Here is how it works exactly: 1. How the count is determined The bucket count is determined at the time a partition is created. The logic depends on whether it's a new table or an existing table adding new partitions: * Initial Calculation (New Tables/First Partitions): If the table has fewer than 5 partitions, StarRocks uses a formula based on the number of BE (Backend) nodes ($X$): * $X \le 12$ BEs: Buckets = $2X$ * $12 < X \le 24$ BEs: Buckets = $1.5X$ * $24 < X \le 36$ BEs: Buckets = $36$ * $X > 36$ BEs: Buckets = $\min(X, 48)$ * Adaptive Calculation (Existing Tables): For a table that already has data, StarRocks looks at the 5 most recent partitions. It estimates the data volume and sets the bucket count for the new partition accordingly to keep bucket sizes within an optimal range. 2. Does it shrink or grow? No, not for existing partitions. * Fixed per Partition: For Hash Bucketing, the number of buckets for a specific partition is fixed once that partition is created. It will not shrink or grow as ingestion or compaction runs for that specific partition. * Evolution over Time: While an existing partition's bucket count is static, new partitions created later (e.g., a new day's partition) may have a different bucket count than older ones if your data volume has significantly increased or decreased. 3. Limits and Precautions * 100 GB Limit: The official recommendation is that if the raw data size of a single partition is expected to exceed 100 GB, you should manually specify the bucket count rather than relying on the automatic mechanism. * Upper Cap: The automatic logic generally caps the bucket count at a relatively low number (often 48 or based on BE count) to avoid creating too many small tablets, which can hurt metadata performance. * Dynamic Growth is for Random Bucketing: Note that StarRocks v3.2+ introduced a "Dynamic Increase" feature (using the

bucket_size

property), but this currently only applies to Random Bucketing in Duplicate Key tables, not Hash Bucketing. Summary Table Feature | Behavior for

DISTRIBUTED BY HASH

Initial Count | Based on BE node count. New Partitions | Based on the size of the last 5 partitions. Growth/Shrink | None. Fixed once the partition is created. Manual Override | Recommended if partition size > 100 GB. To see the current bucket count for your partitions, you can run:

sql
SHOW PARTITIONS FROM <table_name>;

References • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md • table_design/data_distribution/feature-support-data-distribution.md

@Rocky is range based bucketing a suitable alternative to dynamic hash bucketing, if I have partitions of very different sizes and I want small partitions to have fewer buckets/tablets and large partitions to split efficiently

In StarRocks, there is no feature called "range-based bucketing." Data distribution is strictly two-tiered: 1. Partitioning (usually Range or List) and 2. Bucketing (Hash or Random). However, you can achieve your goal of having small partitions with fewer buckets and large partitions with more buckets using the following two strategies: 1. Automatic Hash Bucketing (The "Adaptive" Way) If you use

DISTRIBUTED BY HASH(...)

without specifying the bucket count, StarRocks already does what you want for newly created partitions. * How it works: When a new partition is created (e.g., a new day in a time-series table), StarRocks looks at the data volume of the last 5 partitions. If your data volume is growing over time, StarRocks will automatically increase the bucket count for the next partition it creates. * Suitability: This is the best choice if you need query performance (joins, aggregations, and bucket pruning). * Limitation: It is not "elastic" for an existing partition. If a partition is created today with 10 buckets and you suddenly ingest 10x more data into that specific day, it will stay at 10 buckets. 2. Random Bucketing with

bucket_size

(The "Truly Dynamic" Way) If your partitions have wildly unpredictable sizes or you want them to grow/split automatically as data comes in, Random Bucketing (available in v3.2+ for Duplicate Key tables only) is the closest alternative to what you described. * How it works: You set a target size per bucket using

PROPERTIES("bucket_size" = "1073741824")

(e.g., 1GB). StarRocks will start with a small number of buckets and automatically split/add more tablets as the partition grows. * Suitability: Best for log-style data or multi-tenant tables where one partition might be 10MB and another 10TB. * Trade-off: You lose Bucket Pruning and Colocate Joins. Queries will always scan all tablets in the partition because there is no hash-key-to-bucket mapping. ────────── Which one should you choose? If your priority is... | Use this strategy Join/Filter Performance | Automatic Hash Bucketing. It handles "small vs large" partitions well by adjusting the count at partition creation time. Unpredictable Data Growth | Random Bucketing +

bucket_size

. It is the only way to let a single partition "grow" its bucket count after it has been created. Aggregating/Primary Keys | Automatic Hash Bucketing. Random bucketing is currently only supported for Duplicate Key tables. Pro-tip: If you use Automatic Hash Bucketing and find a specific historical partition has grown too large for its bucket count, you can manually re-bucket just that partition:

sql
ALTER TABLE my_table MODIFY PARTITION p20231001 SET ("distribution_type" = "HASH", "bucket_num" = "64");

References • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/feature-support-data-distribution.md References • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/feature-support-data-distribution.md

@Rocky I'm talking about "enable_range_distribution" option

Yes, the Range-based Distribution semantic (enabled via

enable_range_distribution

) is exactly the suitable alternative you are looking for. It is designed to solve the limitations of static Hash bucketing, especially in multi-tenant or unpredictable data growth scenarios. Introduced in StarRocks v4.1, here is how it works and how it compares to your requirements: 1. How it works exactly When you set the FE configuration

enable_range_distribution = true

and create a table without a

DISTRIBUTED BY

clause (but with an explicit key type like

PRIMARY KEY

or

UNIQUE KEY

), StarRocks switches from Hash bucketing to Range-based bucketing. * Logic: Data is distributed to tablets based on the range of values in your key columns (similar to how Range partitioning works, but at the tablet level). * Initial state: A new partition can start with a very small number of tablets (even just one). 2. Automatic Splitting (Dynamic Growth) Unlike Hash bucketing (where the bucket count is fixed at partition creation), Range-based distribution is truly dynamic: * Splitting: As ingestion runs and a tablet grows beyond a certain threshold (default

tablet_reshard_target_size = 10GB

), StarRocks will automatically split that tablet into two. * Efficiency: This ensures that small partitions stay small (few tablets) while large partitions "split" into many tablets to maintain parallelism. * Compaction: The system monitors tablet sizes during background processes and can trigger these splits without requiring manual

ALTER TABLE

commands or data re-importing. 3. Suitable Alternative? It is a superior alternative to "dynamic hash bucketing" (which doesn't strictly exist for Hash) because: * No Manual Tuning: You don't need to guess the number of buckets for different-sized tenants or days. * Prevents Data Skew: If one specific key (e.g., a massive Tenant ID) causes a tablet to explode in size, that range will be split, whereas in Hash bucketing, that hash-collision would be stuck in one bucket forever. * Supports Pruning: Unlike Random Bucketing (which also grows), Range-based distribution still supports tablet pruning. If your query filters on the distribution key, StarRocks knows exactly which range-tablets to scan. 4. Limits and Requirements * Version: Requires StarRocks v4.1 or higher. * Configuration: You must set

enable_range_distribution = true

in your FE configuration. * Deployment Mode: In v4.1, this feature is primarily optimized for and supported in Shared-data (cloud-native) mode. * Table Type: Currently focused on tables with explicit keys (Primary Key, Unique Key, Duplicate Key with an

ORDER BY

clause). * Omit `DISTRIBUTED BY`: To trigger this behavior, you must not specify a

DISTRIBUTED BY

clause in your

CREATE TABLE

statement. Example Usage:

sql
-- 1. Enable the feature in FE
-- ADMIN SET FRONTEND CONFIG ("enable_range_distribution" = "true");

-- 2. Create table WITHOUT "DISTRIBUTED BY"
CREATE TABLE multi_tenant_data (
    tenant_id INT,
    event_time DATETIME,
    data VARIANT
) PRIMARY KEY (tenant_id, event_time)
PARTITION BY (event_time); 
-- Because DISTRIBUTED BY is missing, it uses Range-based distribution.

Summary Comparison Feature | Automatic Hash Bucketing | Random Bucketing | Range-based Distribution Bucket Count | Fixed at partition creation | Dynamic (v3.2+) | Dynamic (v4.1+) Tablet Splitting | No | Yes | Yes Tablet Pruning | Yes | No | Yes Best For | Stable, predictable volumes | Log data (Duplicate Key) | Variable partition sizes References • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md

@Rocky any downsides to setting tablet target size to lower values? Wouldn't compaction and tablet pruning be more efficient with smaller tablet, metadata will grow, but if we have 10Gb tablets it would also limit cluster processing concurrency, right? As I understand only one CN will be able to process that tablet and 10Gb can be relatively big and take some time to process with one instance, no?

Setting the tablet target size (e.g.,

tablet_reshard_target_size

) to a lower value is a common tuning step, but it involves a specific set of trade-offs. Your intuition regarding concurrency and metadata is correct. Here is a breakdown of the downsides and mechanics: 1. The "Single Node" Concurrency Limit You are correct: A single tablet can only be processed by one BE/CN node at a time. * The Problem: If you have a 100-node cluster but your partition only has 10 tablets (because they are 10GB each), 90 nodes will sit idle during the scan phase of a query. * The Mitigation: StarRocks has a feature called

enable_tablet_internal_parallel

(usually

true

by default). This allows a single node to use multiple CPU cores to scan one tablet. While this helps saturate the resources of the node holding the tablet, it does not help with cluster-wide horizontal scaling. * Recommendation: If you have a large cluster (many nodes), you should ensure the number of tablets is at least 1x to 3x the total number of CPU cores in your cluster to ensure all nodes can participate in a high-concurrency query. 2. Metadata and Management Overhead This is the most significant downside to very small tablets (e.g., < 512MB). * FE Memory/CPU: The Frontend (FE) must track the metadata (location, version, schema) for every tablet. If you have millions of tablets, FE memory usage will spike, and operations like

SHOW TABLE STATUS

or schema changes will become very slow. * BE Compaction: Each tablet is an independent unit for compaction. Having 10,000 tablets of 100MB instead of 100 tablets of 10GB means the BE has to manage 100x more compaction tasks. This can lead to "Compaction Task Queuing," where the system cannot merge small files fast enough to keep up with ingestion. * FD (File Descriptor) Limits: Each tablet consists of multiple segment files. More tablets = more files = higher chance of hitting OS file descriptor limits. 3. Compaction Efficiency Smaller tablets do not necessarily make compaction more efficient; in fact, they often make it less efficient: * Write Amplification: Compaction is most efficient when it can merge large chunks of data. Very small tablets result in many small IO operations. * Version Limits: StarRocks has a limit on the number of "versions" (data increments) per tablet. If you have many small tablets and ingest data frequently, you are more likely to hit the

too many versions

error because each small tablet must track its own versioning independently. 4. Tablet Pruning Efficiency * Range-based pruning: Smaller tablets provide finer-grained pruning. If your queries usually look for a very specific range of keys, having 1GB tablets instead of 10GB tablets means you might read 90% less data. * Diminishing Returns: Once a tablet is small enough to fit comfortably in the Data Cache (SSD/RAM), making it even smaller provides negligible performance gains for pruning but continues to increase metadata costs. Summary: Finding the "Sweet Spot" Tablet Size | Best For | Risks < 512 MB | Very small tables, highly filtered lookups. | Metadata explosion, slow FE, compaction bottlenecks. 1 GB - 2 GB | Default for many users; good balance. | Good for clusters with 3–10 nodes. 5 GB - 10 GB | Official Recommendation for high-throughput. | May limit parallelism on massive clusters (>20 nodes) if tablet count is low. Practical Advice: If you find that 10GB tablets are causing "idle nodes" during queries, don't jump straight to 100MB. Try setting your target size to 1GB or 2GB. This is usually small enough to ensure even distribution across a large cluster while keeping metadata and compaction overhead manageable. To check if your tablets are too big for your cluster's concurrency, look at the Query Profile. If you see a few nodes doing all the work while others have 0

ScanRanges

, you need more (smaller) tablets. References

@Rocky having that in mind - does it make sense then to have larger CN than several smaller ones, if core / total memory count is the same - it's better to have bigger instances so it can handle larger tablets better on its own?

In the context of StarRocks and large tablets (like 10GB), there is a nuanced trade-off between vertical scaling (fewer, larger nodes) and horizontal scaling (more, smaller nodes). 1. Processing a Single Large Tablet: Larger Nodes Win Since a single tablet can only be assigned to one node (BE or CN) at a time, the processing speed of that specific tablet is limited by the resources of that single node. * Internal Parallelism: StarRocks uses

enable_tablet_internal_parallel

(default

true

) to allow a node to use multiple threads to scan a single tablet. It does this by scanning different segments (files within the tablet, usually ~256MB each) in parallel. * The Advantage of Large Nodes: If you have a 10GB tablet (~40 segments) and a node with 32 cores, StarRocks can theoretically fire off 32 scan threads on that one node for that one tablet. A smaller node with only 8 cores would be limited to 8 threads, making the scan of that specific tablet significantly slower. 2. Cluster-Wide Concurrency: Smaller Nodes are Often Better While a single large node is better for a single large tablet, "many small nodes" are typically better for overall cluster throughput and high-concurrency workloads. * MPP Efficiency: StarRocks is an MPP (Massively Parallel Processing) database. It excels at "shuffling" data between nodes. Having more nodes increases the number of "data exchange" points and distributed join possibilities. * Failure Impact: If you have 2 massive nodes and one fails, you lose 50% of your capacity and trigger a massive data re-balance. If you have 10 smaller nodes and one fails, you only lose 10% of your capacity. * Hotspot Resilience: If a specific range of data (e.g., a "hot" Tenant ID) is stored in one tablet, a larger node can throw more CPU at it. However, if that node becomes a bottleneck for other tasks (like ingestion), more nodes would have helped distribute that load better. 3. The "Sweet Spot" for CN/BE Sizing For StarRocks, the current recommendation is usually to avoid "micro-nodes" but also avoid "super-massive" single nodes. * Recommended Instance Size: Usually 16 to 32 cores per node is considered the "sweet spot." * Less than 8 cores: Not recommended for production; the overhead of background tasks (compaction, metadata heartbeats) eats too much of the node's potential. * More than 64 cores: You may start to see diminishing returns due to internal lock contention or NUMA effects, and the "failure domain" becomes too large. Does it make sense to have larger instances for 10GB tablets? Yes, to a point. If you have committed to 10GB tablets (to keep metadata low or because you have massive partitions), you should ensure your nodes have enough cores (ideally 16+) to take advantage of

enable_tablet_internal_parallel

. However, if you have the choice between: 1. One 64-core node 2. Four 16-core nodes Choose Option 2. Even with 10GB tablets, the 4x16-core setup will likely perform better for real-world workloads because it provides better cluster-wide parallelism, higher aggregate I/O bandwidth, and better fault tolerance. The 16-core node is still "big enough" to process a 10GB tablet efficiently. Summary Checklist * Use Larger Nodes (Vertical) if: You have very few, very large tablets and your queries are primarily "Single-User/Large Scan" where you want one query to use every core on a node for one tablet. * Use More Nodes (Horizontal) if: You have many users, many concurrent queries, or use complex joins (shuffling) frequently. * The Rule of Thumb: Aim for 16–32 cores and 64GB–128GB RAM per node. This is large enough to handle 10GB tablets comfortably without making your cluster fragile or limiting MPP efficiency. References • deployment/plan_cluster.md