Bucketing
A concise field guide to choosing between Hash Bucketing and Random Bucketing in StarRocks, including their mechanics, trade‑offs, and recommended use cases.
Quick‑Look Comparison
Aspect | Hash Bucketing | Random Bucketing |
---|---|---|
Example | DISTRIBUTED BY HASH(id) BUCKETS 16 | DISTRIBUTED BY RANDOM |
Key declaration | Required HASH(col1, …) | None – rows assigned round‑robin |
Initial bucket count when omitted | Auto‑chosen at CREATE, then fixed | Auto‑chosen at CREATE; can grow if bucket_size set |
Tablet split / shrink | Manual ALTER … BUCKETS | Automatic split ⇢ growth only (≥ v3.2) |
Skew resistance | Depends on key cardinality | High – uniform by design |
Bucket pruning | ✅ (filters, joins) | 🚫 (full tablet scan) |
Colocate joins | ✅ | 🚫 |
Local aggregation / bucket-shuffle joins | ✅ | 🚫 |
Supported table types | All | Duplicate Key tables only |
Hash Bucketing
How it Works
Rows are assigned to tablets by hashing one or more columns. Tablet count is fixed after creation unless manually altered.
Requirements
- Must pick a stable, evenly, high‑cardinality key up front. The cardinality should typically be 1000 times more than the number of BE nodes to prevent data skew among hash buckets.
- Choose an appropriate bucket size initially, ideally ranging between 1 to 10 GB.
Strengths
- Query locality – selective filters and joins touch fewer tablets.
- Colocate joins – fact/dim tables can share hash keys for high‑speed joins.
- Predictable layout – rows with the same key always land together.
- Local aggregation & bucket‑shuffle joins – identical hash layout across partitions enables local aggregation and reduces data shuffle costs for large join
Weaknesses
- Vulnerable to hot tablets if data distribution skews.
- Tablet count is static; scaling requires maintenance DDL.
- Insufficient tablets can adversely affect data ingestion, data compaction, and query execution parallelism.
- Excessive use of tablets will expand the metadata footprint.
Example: Dimension‑Fact Join and Tablet Pruning
-- Fact table partitioned and hash‑bucketed by (customer_id)
CREATE TABLE sales (
sale_id bigint,
customer_id int,
sale_date date,
amount decimal(10,2)
) ENGINE = OLAP
DISTRIBUTED BY HASH(customer_id) BUCKETS 48
PARTITION BY date_trunc('DAY', sale_date)
PROPERTIES ("colocate_with" = "group1");
-- Dimension table hash‑bucketed on the same key and bucket count colocated with the sales table
CREATE TABLE customers (
customer_id int,
region varchar(32),
status tinyint
) ENGINE = OLAP
DISTRIBUTED BY HASH(customer_id) BUCKETS 48
PROPERTIES ("colocate_with" = "group1");
-- StarRocks can do tablet pruning
SELECT sum(amount)
FROM sales
WHERE customer_id = 123
-- StarRocks can do local aggregation
SELECT customer_id, sum(amount) AS total_amount
FROM sales
GROUP BY customer_id
ORDER BY total_amount DESC LIMIT 100;
-- StarRocks can do colocate join
SELECT c.region, sum(s.amount)
FROM sales s JOIN customers c USING (customer_id)
WHERE s.sale_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY c.region;
What do you gain from this example?
- Tablet pruning: The customer_id predicate
WHERE customer_id = 123
enables bucket pruning, allowing the query to access only a single tablet, which lowers latency & CPU cycles, especially for point-lookups. - Local aggregation: when the hash distribution key is a subset of the aggregation key, StarRocks can bypass the shuffle aggregation phase, reducing the overall cost.
- Colocated join: because both tables share bucket number and key, each BE can join its local pair of tablets without network shuffle.
When to Use
- Stable schemas with well‑known distribution filter/join keys.
- Data warehousing workloads that benefit from bucket pruning.
- You need some specific optimization like colocate join/bucket shuffle join/local aggregation
- You are using Aggregate/Primary Key tables.
Random Bucketing
How it Works
Rows are assigned round‑robin; no key specified. With PROPERTIES ("bucket_size"="<bytes>")
, StarRocks dynamically splits tablets as partitions grow (v3.2+).
Strengths
- Zero design debt–no keys, no bucket math.
- Skew‑proof writes–uniform pressure across disks & BEs.
- Elastic growth–tablet splits keep ingest fast as data or cluster grows.
Weaknesses
- No bucket pruning–every query scans all tablets in a partition.
- No colocated joins–keyless layout prevents locality.
- Limited to Duplicate Key tables today.
When to Use
- Log/event or multi‑tenant SaaS tables where keys change or skew.
- Write‑heavy pipelines where uniform ingest throughput is critical.
Operational Guidelines
- Pick a bucket size (e.g., 1 GiB) for random bucketing to enable auto‑split.
- For hash bucketing, monitor tablet size; re‑shard before tablets exceed 5–10 GiB