@Rocky we are over-bucketing our native table, currently set to 64 when really we only need 12. Explain the implications of running an ALTER to reduce the buckets and whether using AUTO is a better approach if we expect ingestion rates to fluctuate over the year.

Hi there! I'm Rocky. Over-bucketing is a common challenge, but luckily StarRocks has become much more flexible in handling this in recent versions (v3.2+). Reducing your bucket count from 64 to 12 is a great move if your tablet sizes are currently too small (e.g., significantly under 100MB-1GB per tablet). Here’s the breakdown of what happens when you run that

ALTER

and whether

AUTO

is the right path for your fluctuating ingestion. 1. Implications of Running

ALTER TABLE

to Reduce Buckets When you run

ALTER TABLE ... DISTRIBUTED BY HASH(...) BUCKETS 12

, here is what happens under the hood: * Data Rewrite (Asynchronous): This is not just a metadata change. StarRocks must redistribute your data into the new number of buckets. This triggers an asynchronous "Schema Change" job. * Resource Consumption: During the rewrite, the cluster will consume additional CPU, IO, and Disk space (temporarily) because it creates new tablets and copies the data before deleting the old 64-bucket versions. * Query Impact: The table remains readable during this process, but you might see a slight performance hit due to the background background tasks. * Scope: * If you target the whole table, every partition will be rewritten. _ If you only care about future data, you can use

ALTER TABLE ... SET ("default.buckets" = "12")

(available in v3.2.11+ / v3.3+) or the

DEFAULT BUCKETS

syntax in v4.0. This changes the plan for _new* partitions without rewriting old ones. 2. Is

AUTO

Bucketing Better for Fluctuating Ingestion? For your scenario where ingestion rates fluctuate over the year,

AUTO

bucketing is generally the superior approach. Here’s why: * How it works: StarRocks determines the bucket count for a new partition by looking at the data volume of the last 5 partitions. * Adaptability: If your ingestion spikes during a "busy season," StarRocks will see the larger data volume in recent partitions and automatically increase the bucket count for the next ones. When volume drops, it scales them back down. * Efficiency: It prevents the "small file problem" during low-volume periods while ensuring high parallelism during high-volume periods. Recommendation: Instead of hardcoding 12, switch to

AUTO

mode:

sql
ALTER TABLE your_table_name DISTRIBUTED BY HASH(your_key); 
-- Omitting the BUCKETS keyword enables AUTO mode

3. Extra Pro-Tip: Random Bucketing (Duplicate Tables Only) If your table is a Duplicate Key table and you are on v3.2+, you can take this a step further with Random Bucketing combined with `bucket_size`:

sql
ALTER TABLE your_table_name DISTRIBUTED BY RANDOM;
ALTER TABLE your_table_name SET ("bucket_size" = "1073741824"); -- 1GB

In this mode, StarRocks can dynamically increase the number of buckets during a single load if it detects the volume is exceeding your target

bucket_size

. This is the most "elastic" way to handle highly unpredictable fluctuations. Summary Checklist * To fix existing over-bucketing: Use

ALTER TABLE ... BUCKETS 12

(be prepared for a background data rewrite). * To handle future fluctuations: Use

AUTO

(omit the bucket number in the ALTER). * To avoid rewriting history: Use

SET ("default.buckets" = "12")

so only new partitions are affected. Let me know if you need the specific syntax for your table type! References • knowledge/usage/best-practice.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/feature-support-data-distribution.md

Does AUTO only use previous partition sizes? What if we have had the last 5 partitions (lets say weekly) have an equivalent of 2 buckets (~2GB) ingested but this new partition (week) we are seeing a spike to 20GB, would it know to adapt to 20 buckets or use the previous 2 buckets? @Rocky

For AUTO bucketing in StarRocks, the behavior depends on whether you are using Hash Bucketing (the default/common method) or Random Bucketing (available since v3.1). 1. Hash Bucketing (Fixed for the Partition) If you are using Hash Bucketing (e.g.,

DISTRIBUTED BY HASH(id)

), the number of buckets is fixed at the moment the partition is created. * How it decides: StarRocks typically looks at the data volume of the last 5 partitions. * The Spike Scenario: If your last 5 partitions were 2GB each, and you ingest 20GB into a new partition, StarRocks will still create that new partition with a bucket count optimized for 2GB (e.g., 2 buckets). * Result: That specific 20GB partition will be "under-bucketed" (2 buckets for 20GB = 10GB per tablet), which may lead to slower query performance for that specific week. * Adaptation: The system only "learns" from this spike for future partitions. The next week's partition would see the 20GB history and scale up accordingly. 2. Random Bucketing (Dynamic Adaptation) If you are using Random Bucketing (available for Duplicate Key tables since v3.2) and have set the

bucket_size

property, StarRocks can adapt on-the-fly. * How it works: By setting

PROPERTIES("bucket_size" = "1073741824")

(1GB), StarRocks will dynamically increase the number of buckets during the data load as the volume grows. * The Spike Scenario: Even if previous partitions were small, when the 20GB spike occurs, StarRocks will realize the data is exceeding the 1GB-per-bucket target and will automatically create additional buckets (via sub-partitions) to accommodate the 20GB load. * Result: You will end up with approximately 20 buckets for that 20GB partition. Summary & Recommendations * If you use Hash Bucketing: The system relies on historical volume. If you know a massive spike is coming (like a 10x increase), it is better to manually create that specific partition with a higher bucket count using

ALTER TABLE ... ADD PARTITION ... DISTRIBUTED BY HASH(...) BUCKETS 20

. * If you use Random Bucketing: Ensure you are on v3.2+ and have defined a

bucket_size

in your table properties to enable this "elastic" growth. * General Rule: If a partition's raw data size is expected to exceed 100GB, StarRocks documentation generally recommends manually setting the bucket count rather than relying on

AUTO

to ensure optimal tablet sizing (typically 100MB to 1GB per tablet for best performance). References • knowledge/usage/best-practice.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • table_design/data_distribution/feature-support-data-distribution.md • best_practices/query_tuning/schema_tuning.md

@Rocky Are you sure running

sql
ALTER TABLE your_table_name DISTRIBUTED BY HASH(your_key); 
-- Omitting the BUCKETS keyword enables AUTO mode

Will rewrite all the history and not just set it for future partitions? I ran on a test table and the buckets sizes did not change.

Yes, running

ALTER TABLE your_table_name DISTRIBUTED BY HASH(your_key);

(without the

BUCKETS

keyword) triggers an asynchronous background job to rewrite existing partitions to use StarRocks' Automatic Bucketing logic. Here are the details regarding your observation and how this process works: 1. It is an Asynchronous Operation When you run this command, StarRocks does not immediately rewrite the data. Instead, it creates an "Optimize" job. You can check the status of this job using:

sql
SHOW ALTER TABLE OPTIMIZE;

If the state is still

RUNNING

or

PENDING

, the table is still using its old bucket configuration for existing data. Once it reaches

FINISHED

, the new bucket counts will be reflected. 2. Does it rewrite "History"? * Without `DEFAULT`: Yes. If you omit both

BUCKETS

and the

DEFAULT

keyword, StarRocks applies the change to all existing partitions in the table. This involves a data shuffle and rewrite. * With

DEFAULT

(v3.5.8+): If you use

ALTER TABLE ... DISTRIBUTED BY HASH(...) DEFAULT BUCKETS ...

, it only sets the configuration for future partitions and does not touch existing ones. 3. Why did the bucket sizes/counts not change? If you ran the command and the job has finished, but you see no change in bucket counts, it is likely due to one of these reasons: * Auto-Bucketing Logic: Since you omitted the

BUCKETS

keyword, you enabled Automatic Bucketing. In this mode, StarRocks calculates the number of buckets based on the data volume in each partition and the number of BE nodes. If your test table has very little data, the "Auto" logic may have calculated that the optimal number of buckets is the same as what you already had (often 1 bucket for very small partitions). * Shared-Data Mode: Note that "Optimize Table" (changing distribution of existing data) is currently supported only for Shared-Nothing clusters (as of v3.2/v3.3). If you are using a Shared-Data cluster, this operation may behave differently or might not be supported for existing data depending on your version. How to verify To see the current bucket configuration of your partitions, you can run:

sql
SHOW PARTITIONS FROM your_table_name;

Check the

Buckets

column. If the

SHOW ALTER TABLE OPTIMIZE

job is

FINISHED

, this column will show the new counts. To see the physical tablets being created, you can use

SHOW TABLETS FROM your_table_name;

. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • faq/Sql_faq.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md

@Rocky We are running in Shared-Date mode! Explain exactly how to enable AUTO for old partitions in a primary key table. Or do i need to run with explicit bucket count for historical data and then change to AUTO for future?

In StarRocks (specifically version 3.2 and later), you can enable AUTO bucketing for both existing and future partitions in a Primary Key table. You do not need to manually calculate bucket counts for historical data and then switch. Since you are in Shared-Data mode, StarRocks handles the data reorganization efficiently, but note that changing the bucket count for a Primary Key table is a "Schema Change" operation because the bucket count determines the mapping of keys to tablets. Here is exactly how to do it: 1. Enable AUTO for All Existing Partitions To convert all existing partitions to use the AUTO bucket mechanism (where StarRocks determines the count based on data volume), run the following command. Crucially, omit the

BUCKETS

keyword:

sql
ALTER TABLE <table_name> 
DISTRIBUTED BY HASH(<your_distribution_key>);

* What happens: StarRocks will trigger a background job to reorganize the data in all existing partitions. It will look at the data volume of each partition and assign an appropriate number of buckets (aiming for roughly 1GB - 2GB per tablet). * Verification: You can monitor the progress with

SHOW ALTER TABLE COLUMN;

. 2. Enable AUTO for Specific Old Partitions If you have a massive table and only want to convert a subset of old partitions to AUTO bucketing (perhaps to save time), you can target them specifically:

sql
ALTER TABLE <table_name> 
PARTITIONS (p20230101, p20230102) 
DISTRIBUTED BY HASH(<your_distribution_key>);

3. Ensure Future Partitions use AUTO Running the

ALTER TABLE

command without the

BUCKETS

clause in Step 1 usually sets the table's default distribution to AUTO. For any partitions created after this (manually or via Dynamic Partitioning), StarRocks will automatically determine the bucket count. 4. Special Note: Dynamic Bucket Size (Optional) In newer versions, you can also set a target "bucket size" to give the AUTO-tuning a hint. This is especially useful in Shared-Data mode to balance performance and tablet count:

sql
-- This tells StarRocks to aim for ~2GB per bucket when calculating AUTO counts
ALTER TABLE <table_name> SET ("bucket_size" = "2147483648");

Summary of Strategy * Don't use explicit bucket counts for history if your goal is AUTO. * Do run the

ALTER TABLE ... DISTRIBUTED BY HASH(...)

command without the

BUCKETS

property. * Be aware that for Primary Key tables, this will trigger a data reshuffle (Schema Change job) to re-distribute the keys into the new number of buckets. Monitor this job to ensure it completes before performing other major DDLs. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md