http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> What is the recommended data type t...
# questions-and-troubleshooting
m
@Rocky What is the recommended data type to represent floating point values in primary key tables? This is for data science/machine learning usage.
r
For StarRocks Primary Key tables, floating-point data types like 
FLOAT
, 
DOUBLE
, and 
DECIMAL
are not supported as primary key columns. This limitation exists because floating-point types are subject to precision errors (e.g., $0.1 + 0.2 \neq 0.3$), which makes them unsuitable for the strict equality checks required by a primary key index to handle upserts and deletes reliably. Recommended Workarounds for DS/ML Usage If your data science or machine learning workflow requires representing a floating-point value as part of a unique identifier in a Primary Key table, here are the recommended approaches: 1. Scaling to Integer (Recommended) This is the most efficient method for performance and memory. Multiply your floating-point value by a fixed power of 10 to preserve the required precision and store it as a 
BIGINT
. * Example: If you need 4 decimal places of precision, multiply by 10,000. * Value 
123.4567
$\rightarrow$ store as 
1234567
(
BIGINT
). * Pros: Minimal memory footprint (8 bytes for 
BIGINT
), extremely fast indexing. 2. Surrogate Key + Value Column (Best Practice for ML) In most machine learning datasets, it is best practice to use a stable surrogate key (like a 
BIGINT
ID or a 
VARCHAR
UUID) as the Primary Key and store the floating-point values in Value Columns. * Value columns in Primary Key tables do support 
FLOAT
, 
DOUBLE
, and 
DECIMAL
. * This approach avoids the risks of floating-point comparison while keeping your model features in their native format. 3. String Representation (
VARCHAR
) If you cannot use a surrogate key or integer scaling, you can store the float as a 
VARCHAR
. * Pros: Preserves the exact literal representation. * Cons: Significantly higher memory usage for the Primary Key index (which is often stored in memory or a persistent index cache) and slower join/upsert performance compared to integers. Summary of Supported Types for Primary Keys To keep your Primary Key table performing optimally (especially for large ML datasets), stick to these supported types for your 
PRIMARY KEY
definition: * Integers: 
TINYINT
, 
SMALLINT
, 
INT
, 
BIGINT
, 
LARGEINT
* Strings: 
VARCHAR
, 
CHAR
* Date/Time: 
DATE
, 
DATETIME
* Boolean: 
BOOLEAN
Note for Vector Search: If you are using StarRocks for vector embeddings (e.g., for recommendation systems), the embeddings themselves should be stored in an 
ARRAY<FLOAT>
column as a value, while the record's unique ID serves as the Primary Key. References • table_design/table_types/primary_key_table.md • table_design/table_types/table_capabilities.md • table_design/StarRocks_table_design.md • best_practices/primarykey_table.md
m
@Rocky I meant normal columns, not primary key. float double or decimal for DS usage. primary keys themselves are strings.
r
For general Data Science (DS) and Machine Learning (ML) usage in StarRocks, the recommendation depends on whether you prioritize performance/compatibility or absolute precision. Since your Primary Keys are strings (e.g., UUIDs or IDs), you have full flexibility for your feature columns. 1. The "Standard" Choice: 
DOUBLE
 For the vast majority of DS/ML use cases (e.g., training features, model outputs, probabilities), 
DOUBLE
 is the recommended type. * Why: ML frameworks (like NumPy, PyTorch, and TensorFlow) default to 64-bit floats (`float64`/`double`). Using 
DOUBLE
ensures your data in StarRocks matches the precision used in your training environment. * Performance: Floating-point math is hardware-accelerated. StarRocks performs aggregations and filters much faster on 
DOUBLE
than on 
DECIMAL
. * Caveat: Be aware of "precision drift." If you sum billions of rows, the result might differ slightly from the mathematical exact value due to how binary floating-point math works. 2. The "Precision" Choice: 
DECIMAL
 If your ML model is operating on financial data, accounting records, or currency, use 
DECIMAL(P, S)
. * Why: 
DECIMAL
is a fixed-point type that avoids the $0.1 + 0.2 \neq 0.3$ problem. It is "Fast DECIMAL" in StarRocks, which is highly optimized. * Performance: Roughly 2–3x slower than 
DOUBLE
for complex calculations, but it guarantees exactness. * Recommendation: Use 
DECIMAL(18, 4)
or 
DECIMAL(38, 9)
depending on your required scale. 3. The "Storage Efficient" Choice: 
FLOAT
 If you are dealing with massive datasets (billions/trillions of rows) where memory and disk I/O are bottlenecks, and the features don't require high precision (e.g., sensor data with only 2–3 significant digits), use 
FLOAT
. * Pros: Uses 4 bytes instead of 8 (for 
DOUBLE
). * Cons: Lower precision (approx. 7 decimal digits). 4. Special Case: Vector Embeddings If your DS usage involves Vector Search (Similarity Search), you should not use individual float columns. Instead, use: * 
ARRAY<FLOAT>
: This is the required type for storing embeddings/vectors to be used with StarRocks' vector indexed functions (like 
cosine_similarity
). ────────── Summary Recommendation Table Use Case | Recommended Type | Why? General ML Features | 
DOUBLE
| Best balance of speed and precision; standard for ML. Embeddings / Vectors | 
ARRAY<FLOAT>
| Required for StarRocks Vector Search. Financial / Exact DS | 
DECIMAL
| Prevents rounding errors in financial modeling. High-Volume Sensor Data | 
FLOAT
| Saves 50% storage; precision is usually sufficient. Pro-tip for ML: If you are exporting data to a Python environment (via Connector or Spark), 
DOUBLE
maps natively to 
float64
, which is the most stable path for your data pipelines. References • knowledge/usage/best-practice.md • faq/Sql_faq.md • sql-reference/data-types/numeric/DECIMAL.md • sql-reference/System_variable.md
m
@Rocky WIll colocation groups significantly boost join performance on ~400million by ~400 million record joins? I'm maintaining a separate table for additional data science metadata from the main requests table. They technically can share the same table and avoid the need for joins since the primary key is exactly the same. However, table separation allows for separate ddl so: 1. data doesn't balloon as column cardinality for ds metadata increase 2. I can maintain separate TTL since ds metadata information no longer needed after data enrichment during the joins/ I am joining the tables on a column called auction_id that will guarantee same number of buckets in the hash distribution key
r
Yes, Colocation Groups will significantly boost join performance for a 400 million by 400 million record join. In a distributed database, the most expensive part of a large-scale join is typically the Shuffle (Exchange) phase, where data is moved across the network to ensure matching keys reside on the same node. By using Colocation Groups, you effectively transform a distributed shuffle join into a Local Hash Join, which can be orders of magnitude faster at this scale. Why this is the right architecture for your DS/ML use case: 1. Elimination of Network Overhead: For 400M rows, a standard shuffle join would require hashing and sending millions of records across your BE nodes. Colocation ensures that the data for 
auction_id = 'X'
in Table A and Table B is already physically stored on the same machine. 2. Table Separation (DDL & TTL): Your logic for keeping them separate is sound. You get the performance of a single wide table (since joins are local) while maintaining the flexibility of separate tables: * TTL Management: You can set a shorter 
storage_cooldown_time
or use Partition-level TTL on the metadata table without affecting the main requests table. * Column Cardinality: You avoid "column bloat" in your main table, which keeps scans of the main table faster for non-DS queries. 3. Scalability: High cardinality on 
auction_id
is actually an advantage here. It ensures a very even distribution of data across your buckets, preventing "hot spots" where one BE node has significantly more work than others during the join. Implementation Checklist for "Significantly Boosted" Performance: To ensure the Colocate Join actually triggers, you must satisfy these strict requirements: * Identical Distribution Key: Both tables must be 
DISTRIBUTED BY HASH(auction_id)
. The data type of 
auction_id
must be identical in both (e.g., both 
VARCHAR(64)
). * Identical Bucket Count: Both tables must have the exact same number of 
BUCKETS
. * The Property: You must include the 
colocate_with
property in the 
PROPERTIES
block of your 
CREATE TABLE
statement:
Copy code
sql
    PROPERTIES (
        "colocate_with" = "ds_metadata_group"
    )
* Replica Count: The number of replicas (
replication_num
) must be the same for both tables. How to verify it’s working Run an 
EXPLAIN
on your join query. Look for the 
Hash Join
 node: * Success: You will see 
colocate: true
. * Failure: You will see an 
EXCHANGE
node above the join, and the 
Hash Join
will say 
colocate: false
. One "Data Science" Caveat If your DS metadata table is significantly smaller (e.g., only a few million rows) than the main table, StarRocks might occasionally choose a Broadcast Join instead. However, at 400M vs 400M, a Colocate Join is the most stable and performant path. Recommendation: Proceed with the separate tables and a Colocation Group. It is the "best of both worlds" for performance and maintenance strategy for ML feature enrichment. References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md • administration/management/resource_management/Colocation_Group.md • table_design/Data_distribution.md References • using_starrocks/Colocate_join.md
m
@Rocky How can I perform benchmark of colocate vs no colocate joins? I can only think of adding fixed bucket distribution and colocation after the initial test to prove my point that colocation helps join performance.
r
To perform a rigorous benchmark of Colocate Join vs. Shuffle Join without the need to recreate or modify your tables, you can use StarRocks' session variables. This allows you to toggle the colocation optimization off and on for the exact same dataset. At a scale of 400 million by 400 million rows, the difference will be highly visible in the "Shuffle/Exchange" phase of the query. Step 1: Force a Shuffle Join (The "Before" Test) You can disable the colocation optimization at the session level. This forces StarRocks to treat the tables as if they are distributed on different nodes, triggering a network shuffle.
Copy code
sql
-- Disable colocation for this session
SET disable_colocate_join = true;

-- Enable profiling to see exactly where time is spent
SET enable_profile = true;

-- Run your join query
SELECT COUNT(*) 
FROM table_main a 
JOIN table_ds_metadata b ON a.auction_id = b.auction_id;
Step 2: Enable Colocate Join (The "After" Test) Now, turn the optimization back on (this is the default behavior if your tables have the 
colocate_with
property).
Copy code
sql
-- Re-enable colocation
SET disable_colocate_join = false;

-- Run the same query
SELECT COUNT(*) 
FROM table_main a 
JOIN table_ds_metadata b ON a.auction_id = b.auction_id;
Step 3: Analyze the Performance Difference To prove the benefit, you should look at two things: the Execution Plan and the Query Profile. 1. Check the Plan (
EXPLAIN
) Run 
EXPLAIN <your_query>;
for both settings. * With Colocation: You should see 
colocate: true
in the 
HASH JOIN
node and no 
EXCHANGE
node between the table scans and the join. * Without Colocation: You will see an 
EXCHANGE
node (Shuffle), and the join will say 
colocate: false
. 2. Inspect the Profile (
SHOW QUERY PROFILE
) After running the queries with 
enable_profile = true
, go to the StarRocks Manager UI or use 
SHOW QUERY PROFILE;
. * In the Shuffle Join (Step 1): Look for the 
EXCHANGE_NODE
. At 400M rows, you will see significant Network Time and CPU Time spent serializing/deserializing data for the shuffle. * In the Colocate Join (Step 2): The 
EXCHANGE_NODE
will be gone. The 
HASH_JOIN_NODE
will start processing almost immediately after the scan, as data is already local. Benchmarking Tips for 400M Rows 1. Warm-up: Run each query twice. The first run might include metadata loading or cold disk reads. Use the second run for your "official" benchmark time. 2. Monitor BE Metrics: During the Shuffle Join test, you will likely see a massive spike in network throughput (TX/RX) on your Backend (BE) nodes. In the Colocate Join test, network traffic should remain low. 3. Data Skew: If one query is significantly slower than expected even with colocation, check for data skew on 
auction_id
. You can check this with:
Copy code
sql
    SELECT TABLET_ID, ROW_COUNT FROM information_schema.be_tablets WHERE TABLE_NAME = 'table_main';
If some tablets are much larger than others, colocation will still be bottlenecked by the slowest (largest) tablet. Conclusion: Using 
SET disable_colocate_join = true;
is the cleanest way to demonstrate the performance gain to your team, as it keeps all other variables (hardware, data, indexes) identical. References • using_starrocks/Colocate_join.md • sql-reference/System_variable.md • best_practices/query_tuning/schema_tuning.md
m
@Rocky What happens when I alter my table to use fixed distribution buckets? Does it perform background operation for tablet redistribution of existing data?
r
When you alter a table to use a fixed number of buckets (or change the bucketing column), StarRocks handles this as an Optimize operation, which is a background asynchronous job. Here is exactly what happens and how to manage it: 1. Background Data Redistribution Yes, if you apply the change to existing partitions, StarRocks will perform a background redistribution. It does not just change a metadata flag; it physically creates new tablets (buckets) and re-shuffles the existing data into them to match the new distribution logic. * Job Type: This is categorized as an 
OPTIMIZE
job. * How to monitor: You can track the progress (0% to 100%) using:
Copy code
sql
    SHOW ALTER TABLE OPTIMIZE;
* Online Operation: The table remains available for both reads and writes during this process. StarRocks uses a "double-write" or similar mechanism during the transition to ensure data consistency without downtime. 2. The Two Ways to Alter Buckets Since you are likely doing this to enable a Colocation Group, you need to be careful about which command you use: A. Global Redistribution (Recommended for Colocation) If you want to move 400M existing rows into a colocation group, you must use:
Copy code
sql
ALTER TABLE your_table 
DISTRIBUTED BY HASH(auction_id) BUCKETS 64
PROPERTIES("colocate_with" = "ds_group");
* What happens: This triggers the background job for all existing partitions. Once the job finishes, the data is physically aligned, and Colocate Joins will start working for all historical data. B. Future-Only Change (The 
DEFAULT
keyword) Available in newer versions (v3.5.8+), you can change only the template for future partitions:
Copy code
sql
ALTER TABLE your_table 
DISTRIBUTED BY HASH(auction_id) DEFAULT BUCKETS 64;
* What happens: This does not redistribute existing data. Only new partitions created after this command will have 64 buckets. * Warning: This will not fix your join performance for existing data, as the bucket counts won't match across tables for old partitions. 3. Performance Impact during Alteration * Resources: The redistribution is a heavy I/O and CPU operation (it's essentially a re-shuffle). If your cluster is already at 90% CPU, this will slow down queries. * Completion Time: For 400M rows, the time depends on your disk speed and BE count, but it typically finishes in minutes to an hour on a healthy cluster. Pro-Tip for your Benchmark Since you plan to benchmark "before vs. after," you can: 1. Run your join on the original table (Shuffle Join). 2. Run the 
ALTER TABLE ... BUCKETS X PROPERTIES("colocate_with" = "...")
command. 3. Wait for 
SHOW ALTER TABLE OPTIMIZE
to show 
State: FINISHED
. 4. Run the join again. You will see a massive drop in query latency and the disappearance of the 
EXCHANGE
node in the 
EXPLAIN
plan. Summary * Background? Yes. * Redistributes existing data? Yes (unless you use the 
DEFAULT
keyword). * Blocking? No, it's an online operation. * Monitoring? Use 
SHOW ALTER TABLE OPTIMIZE
. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md
m
@Rocky Give me step by step to just fix bucket distribution for future insertions. My 24 hour TTL will take care of existing data not having proper distribution. I will call the colocation group requests since it is the parent table containing all bid stream metadata that data science pipeline needs
r
To achieve your goal of fixing the distribution for future data while avoiding a massive redistribution of your existing 400M+ rows, follow these steps. Important Technical Note A Colocation Group (CG) requires all partitions in all tables within the group to have the exact same bucket count. If you try to enable colocation while you have "mixed" bucket counts (e.g., old partitions with 10 buckets and new ones with 64), StarRocks will either reject the command or trigger a background redistribution of the old data to force it into the 64-bucket group. Since you are comfortable waiting 24 hours for your TTL to expire old data, here is the "No-Redistribution" path: Step 1: Update the Default Bucket Count (Future-only) Run this for both your parent table (
requests
) and your DS metadata table. This command (available in v3.5.8+ and v4.0+) changes the "template" for future partitions without touching existing data.
Copy code
sql
-- For the main table
ALTER TABLE table_main 
DISTRIBUTED BY HASH(auction_id) DEFAULT BUCKETS 64;

-- For the DS metadata table
ALTER TABLE table_ds_metadata 
DISTRIBUTED BY HASH(auction_id) DEFAULT BUCKETS 64;
* Result: All partitions created from this moment forward will have 64 buckets. Existing partitions remain as they are. Step 2: Wait for your TTL (24 Hours) Wait for your TTL to prune the old partitions that were created with the old/automatic bucket counts. * To verify you are ready for colocation, check that all partitions now have the same bucket count:
Copy code
sql
    SHOW PARTITIONS FROM table_main;
    -- Ensure the 'Buckets' column shows '64' for every single partition.
Step 3: Enable the Colocation Group Once the old "mismatched" partitions are gone, you can link the tables into a Colocation Group named 
requests
.
Copy code
sql
-- Add the main table to the group
ALTER TABLE table_main SET ("colocate_with" = "requests");

-- Add the DS metadata table to the same group
ALTER TABLE table_ds_metadata SET ("colocate_with" = "requests");
Why this works for your DS use case: 1. Zero I/O Spike: By using 
DEFAULT BUCKETS
, you skip the heavy 
OPTIMIZE
job that would normally re-shuffle 400M rows. 2. Clean Transition: During the 24-hour wait, your joins will still work (using a standard Shuffle Join). Once you hit Step 3, the join performance will "magically" boost as it switches to a Local Colocate Join. 3. Data Science Flexibility: Your metadata table stays separate for DDL/TTL purposes, but is physically aligned with the bid stream data for performance. Verification After completing Step 3, verify the colocation status:
Copy code
sql
-- 1. Check if the group is stable
SHOW PROC '/colocation_group';

-- 2. Check the query plan for 'colocate: true'
EXPLAIN SELECT * FROM table_main a JOIN table_ds_metadata b ON a.auction_id = b.auction_id;
Warning: If you have Dynamic Partitioning enabled, ensure you also update the dynamic partition property, as it can sometimes override the table default:
Copy code
sql
ALTER TABLE table_main SET ("dynamic_partition.buckets" = "64");
References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Colocate_join.md • table_design/data_distribution/Data_distribution.md References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
m
@Rocky ALTER TABLE table_main SET ("colocate_with" = "requests"); I can run the above command in advance right? just on the main table to first create the colocation group
r
Short answer: No, you should not run the 
colocate_with
command in advance. If you run the 
ALTER TABLE ... SET ("colocate_with" = "requests")
command while your table still has "mixed" bucket counts (old partitions with the auto-calculated number and new partitions with 64), StarRocks will likely do one of two things—both of which you want to avoid: 1. Trigger Immediate Redistribution: If StarRocks accepts the command, it will realize the old partitions don't match the 64-bucket requirement for the colocation group. It will then automatically start a background 
OPTIMIZE
job to redistribute those 400M rows immediately to "fix" them. This is exactly the heavy I/O spike you are trying to avoid. 2. Reject the Command: StarRocks may return an error stating that all partitions in a colocated table must have the same bucket count. The Correct Timeline To ensure zero background redistribution of your existing 400M rows, follow this strict sequence: 1. NOW: Set the Default (Future-Only)
Copy code
sql
    ALTER TABLE table_main DISTRIBUTED BY HASH(auction_id) DEFAULT BUCKETS 64;
This is safe. It only changes the metadata for partitions created from this point forward. 2. NEXT 24 HOURS: Let the TTL work * New partitions are born with 64 buckets. * Old partitions (with the wrong bucket count) are dropped as they expire. * Crucial: Check your progress occasionally with 
SHOW PARTITIONS FROM table_main;
. Look at the 
Buckets
column. Once every single row in that list says 
64
, you are ready. 3. AFTER 24 HOURS: Enable Colocation
Copy code
sql
    -- This will now be near-instant and trigger no data movement
    ALTER TABLE table_main SET ("colocate_with" = "requests");
    ALTER TABLE table_ds_metadata SET ("colocate_with" = "requests");
Why "Wait and See" is better for your DS Team If you enable colocation too early, you risk an Unstable colocation group. When a group is unstable (due to background redistribution/migration), StarRocks disables Colocate Join optimization and falls back to standard Shuffle Joins anyway. By waiting for your TTL to "naturally" clean the table, you ensure that the moment you turn on colocation, it is 100% stable and the performance boost is immediate for your data science pipeline. Pro-Tip: Check Group Stability After you eventually run the command in Step 3, you can verify that StarRocks is happy and colocation is active by running:
Copy code
sql
SHOW PROC '/colocation_group';
Look for the 
IsStable
 column. If it is 
true
, your colocation is healthy and your joins will be lightning-be lightning fast. References • using_starrocks/Colocate_join.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md
m
@Rocky Most of my tablets in show partitions are not balanced. Is this something to be concerned about?
r
Whether "unbalanced" tablets are a concern depends entirely on where you are seeing the imbalance. In StarRocks, there are three common types of "unbalance," only one of which is a major problem for your Data Science pipeline. 1. Data Skew across Tablets (The Real Concern) If you run 
SHOW TABLETS FROM table_name;
and see that some tablets have significantly more data or rows than others within the same partition, you have Data Skew. * Why it happens: Your 
DISTRIBUTED BY HASH(auction_id)
key might have "hot values" (e.g., one 
auction_id
represents 50% of your traffic). * Why it's a problem: In a distributed join, the speed of the query is limited by the slowest (largest) tablet. If one tablet is 10GB and the others are 100MB, the BE node processing that 10GB tablet will bottleneck your entire DS pipeline. * How to check:
Copy code
sql
    -- Check the size of tablets in a specific partition
    SHOW TABLETS FROM table_main PARTITION (p20231027);
* Fix: If the skew is severe, you may need to add a second column to your distribution key to improve entropy: 
DISTRIBUTED BY HASH(auction_id, event_time)
. 2. Imbalance across Partitions (Normal) If you see in 
SHOW PARTITIONS
that 
2023-10-27
has 100GB of data but 
2023-10-20
has only 10GB, this is completely normal for time-series data. * Concern Level: Low. * Why: Traffic naturally fluctuates by day or hour. StarRocks handles this fine. As long as the data within each partition is spread evenly across its 64 buckets, your queries will stay fast. 3. Physical Tablet Distribution (Managed by StarRocks) If you are looking at the cluster level (e.g., 
SHOW PROC '/backends'
) and see that 
BE-1
has 5,000 tablets while 
BE-2
has 2,000 tablets, the cluster is physically unbalanced. * Concern Level: Medium. * Why: This usually happens after adding new BE nodes. * Fix: You don't need to do anything. StarRocks has a background Tablet Scheduler that automatically migrates tablets from high-load nodes to low-load nodes. You can monitor this with:
Copy code
sql
    SHOW PROC '/cluster_balance/history';
Summary for your Case: Since you are about to move to a Colocation Group, tablet balance becomes even more important: 1. Check for Skew: Ensure 
auction_id
is a high-cardinality ID. If it is, your 64 buckets will naturally be "balanced" enough. 2. Wait for TTL: Your current "unbalanced" tablets in old partitions will be deleted anyway. 3. Monitor New Partitions: Once you start creating the new partitions with 64 buckets, run 
SHOW TABLETS
on one of the new ones. If the 
DataSize
column shows roughly equal numbers for all 64 tablets, you are in perfect shape for your benchmark. Pro-Tip: If you see 
DataSize
differences of < 20% between tablets, don't worry about it. If you see one tablet is 5x larger than others, you have a distribution key problem. References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky Is it mandatory to share same replica num for tables involved in colocation joins?
r
Yes, it is mandatory for all tables in a Colocation Group to have the same number of replicas (copies). The core requirement for a Colocate Join is that StarRocks must guarantee that corresponding tablets (buckets) from different tables are stored on the exact same physical BE nodes. If one table has 3 replicas and the other has 2, StarRocks cannot maintain this physical alignment, and the colocation will break. Why this is a strict requirement: In StarRocks, a Colocation Group Schema (CGS) is defined by the "Parent Table" (the first table in the group). This schema locks in three parameters: 1. Bucketing Key: The data types and order of the 
DISTRIBUTED BY
columns. 2. Bucket Count: The number of buckets (e.g., 64). 3. Replica Count: The number of copies of the data (e.g., 3). What happens if you try to use a different number? If you try to add a table to an existing group using 
SET ("colocate_with" = "group_name")
and that table has a different 
replication_num
, StarRocks will reject the command with an error. The metadata check ensures that the table property matches the group's schema before it allows the table to join. Action Plan for your setup: Since you are fixing your distribution to 64 buckets for your DS pipeline, ensure both the parent and child tables use the same replication factor:
Copy code
sql
-- Check your parent table's replication number
SHOW CREATE TABLE table_main; 

-- Ensure your metadata table matches it
ALTER TABLE table_ds_metadata SET ("replication_num" = "3"); -- Use whatever the parent has
Note: If you need to change the replication number of the entire group later, you must change it for every table in the group individually. StarRocks will then handle the background migration to keep them physically aligned. References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/query_tuning/schema_tuning.md References • using_starrocks/Colocate_join.md
m
@Rocky how do foreign keys help optimize denormalized views by reducing unnecessary joins? Show example
r
In StarRocks, Foreign Keys (FKs) act as optimization hints that enable a feature called Join Elimination (or Table Pruning). When you define a denormalized View that joins multiple tables, the optimizer uses these FK hints to determine if a join is "cardinality-preserving." If a query against that View doesn't actually need columns from one of the joined tables, StarRocks can prune that table entirely from the execution plan, significantly reducing I/O and CPU usage. How it Works For StarRocks to eliminate a join (specifically an 
INNER JOIN
), it needs to know two things: 1. Uniqueness: Every row in the "parent" table is unique (Primary Key/Unique Key). 2. Existence: Every row in the "child" table has a matching row in the parent table (Foreign Key). Because StarRocks does not enforce these constraints during data ingestion for performance reasons, you must ensure the data integrity yourself. The FK acts as a promise to the optimizer: _"I guarantee every 
dept_id
in the employees table exists in the departments table."_ ────────── Example: Optimizing a "Wide" View Imagine you have a denormalized view used by a BI tool that joins a large 
sales
table with a 
store_details
table. 1. Setup the Tables with Hints
Copy code
sql
-- Parent Table: Stores
CREATE TABLE stores (
    store_id INT NOT NULL,
    store_name VARCHAR(100),
    region VARCHAR(50)
) 
PRIMARY KEY (store_id)
DISTRIBUTED BY HASH(store_id) BUCKETS 10;

-- Child Table: Sales (Note the FK hint in PROPERTIES)
CREATE TABLE sales (
    sale_id INT NOT NULL,
    store_id INT NOT NULL,
    amount DECIMAL(10, 2),
    sale_date DATE
) 
DUPLICATE KEY(sale_id)
DISTRIBUTED BY HASH(sale_id) BUCKETS 100
PROPERTIES (
    "foreign_key_constraints" = "(store_id) REFERENCES stores(store_id)"
);
2. Create the Denormalized View Users and BI tools use this view to avoid writing joins manually.
Copy code
sql
CREATE VIEW v_sales_report AS
SELECT 
    s.sale_id,
    s.amount,
    s.sale_date,
    st.store_name,
    st.region
FROM sales s
INNER JOIN stores st ON s.store_id = st.store_id;
3. The Optimization in Action If a user runs a query that only asks for sales data, StarRocks will see the Foreign Key and realize the join to 
stores
won't change the row count or the result. The Query:
Copy code
sql
-- The user queries the view, but doesn't select store_name or region
SELECT SUM(amount) FROM v_sales_report;
The Execution Plan (Simplified): Without the Foreign Key, StarRocks would perform a 
HASH JOIN
between 
sales
and 
stores
. With the Foreign Key, the 
EXPLAIN
plan will show:
Copy code
text
- AGGREGATE (SUM)
  - SCAN sales  <-- Notice 'stores' is completely missing!
Key Benefits for Denormalized Views: * Reduced Resource Usage: By skipping unnecessary joins, you save memory (no hash tables for the join) and network traffic (no shuffling of the parent table). * One View, Many Use Cases: You can build a single "Master View" with 10+ joins. If a query only needs 2 of those tables, StarRocks prunes the other 8, providing the performance of a manual, optimized query. * Star Schema Performance: It allows you to maintain a clean Star Schema design while giving users the simplicity of a single "Flat" table interface. Important Note: For LEFT JOINs, you only need the Unique/Primary Key constraint on the joined table to enable pruning. For INNER JOINs, the Foreign Key constraint is mandatory. References • using_starrocks/accelerate-cardinality-preserving-joins.md
m
@Rocky aside from colocation joins, are there any other optimizations for join performance? I plan to join 2 400x400 million record tables. They share the same primary key which is received_hour, auction_id and imp_key. Only auction id and imp key is required for uniqueness. Imp key is often times just 1 (60% of the time)
r
Joining two 400M-row tables is a significant workload, and while Colocation Joins are the "Gold Standard," they aren't always possible (e.g., if you need to join on different columns). Given your data profile (High cardinality 
auction_id
, but heavily skewed 
imp_key
at 60%), here are the four most powerful optimizations beyond colocation: 1. Skew Join V2 (Addressing the 
imp_key = 1
issue) Since 
imp_key
is 1 for 60% of your data, a standard Shuffle Join will send 240M rows to a single BE node, causing an OOM (Out of Memory) or extreme slowness. StarRocks has a Skew Join hint that tells the optimizer to treat specific values differently. It will broadcast the matching "skewed" rows from the right table to all nodes while shuffling the rest normally. Example Syntax:
Copy code
sql
SELECT * 
FROM table_a a
JOIN [skew|a.imp_key(1)] table_b b  -- Tell StarRocks '1' is skewed
  ON a.auction_id = b.auction_id 
  AND a.imp_key = b.imp_key;
2. Bucket Shuffle Join If you cannot colocate both tables (perhaps 
table_b
is used in other joins with different keys), you can use a Bucket Shuffle Join. * How it works: Instead of shuffling both tables (Network cost: A + B), StarRocks only shuffles the "Right" table to the nodes where the "Left" table's buckets already live (Network cost: B only). * Requirement: The join condition must include the 
DISTRIBUTED BY
column of the left table. Example Hint:
Copy code
sql
SELECT * FROM table_a JOIN [BUCKET] table_b ON table_a.auction_id = table_b.auction_id;
3. Global Runtime Filters (RF) For 400M rows, you want to stop data from even leaving the scan node. StarRocks uses Runtime Filters to build a "bloom filter" on the smaller side of the join and push it down to the scan of the larger side. * Optimization: Ensure 
enable_global_runtime_filter = true;
is set. * Impact: If 
table_b
has a filter (e.g., 
WHERE status = 'active'
) that reduces its 400M rows to 10M, the Runtime Filter will tell the scan of 
table_a
to skip any 
auction_id
that isn't in that 10M set before the data is shuffled. 4. Distribution Key Strategy (The "Skew" Fix) Since 
imp_key
is 1 most of the time, do not use 
imp_key
alone as your distribution key. * Recommended: 
DISTRIBUTED BY HASH(auction_id)
. * Since 
auction_id
is high-cardinality, it will distribute the data evenly across buckets. Even if 
imp_key
is always 1, those rows will be spread across all nodes because their 
auction_id
values are different. * Avoid: 
DISTRIBUTED BY HASH(received_hour, auction_id)
. * Including a time-based column like 
received_hour
in the hash key is usually a mistake because it breaks "tablet pruning" when you query a specific hour. Use 
PARTITION BY (received_hour)
instead. 5. Materialized Views (MV) If you frequently join these two specific 400M tables on the same keys, an Asynchronous Materialized View is the best "set it and forget it" optimization. * StarRocks will pre-compute the join in the background. * The optimizer can automatically rewrite your queries to use the MV instead of the raw tables, giving you sub-second response times for what would have been a massive join. Summary Checklist for your 400M x 400M Join: 1. Check Skew: Run 
SELECT imp_key, count(*) FROM table GROUP BY 1 ORDER BY 2 DESC
to confirm the skew. Use the 
[skew]
hint if one value dominates. 2. Verify Plan: Run 
EXPLAIN
on your query. Look for 
BUCKET_SHUFFLE
or 
COLOCATE
. If you see 
SHUFFLE
on both sides, the network is your bottleneck. 3. Key Alignment: Ensure the 
DISTRIBUTED BY
key of at least one table matches your Join condition. 4. PK Table: Since you mentioned 
auction_id
+ 
imp_key
is unique, make sure those are defined as the Primary Key if you are using the *Primary Key Engine*Primary Key Storage Engine****, as this allows for extremely fast point lookups during joins. References • using_starrocks/skew_join_v2.md
m
@Rocky but auction id is sort of the parent key, very high cardinality as it is unix nano based. Imp key is the child key. Does this change your verdict. We can use auction as the primary join. Most records are unique by auction, only sometimes 1 auction have multiple imps,
r
Since 
auction_id
is high-cardinality (unix nano based) and acts as the "parent" key, it actually makes your optimization strategy much easier and more robust. The fact that 
imp_key
is skewed (60% are '1') becomes a non-issue if you choose your distribution key correctly. 1. The Verdict: Use 
auction_id
as the Distribution Key You should distribute both tables by 
HASH(auction_id)
. * Why? Even though 60% of your rows have 
imp_key = 1
, those rows will have unique 
auction_id
values. By hashing on 
auction_id
, StarRocks will spread those "imp_key = 1" rows evenly across all your BE nodes and buckets. * The Result: You avoid the "hot node" problem completely while still enabling Colocate Joins. 2. How to handle the Join Predicate Even if you only distribute by 
auction_id
, you can still join on both columns:
Copy code
sql
SELECT ...
FROM table_a a
JOIN table_b b ON a.auction_id = b.auction_id AND a.imp_key = b.imp_key
How StarRocks handles this: 1. Colocation: Because both tables are distributed by 
auction_id
, StarRocks knows that any rows with the same 
auction_id
are physically located on the same machine. 2. Local Join: Once the data is co-located on a node, the engine performs a local join. It will first match the 
auction_id
and then use 
imp_key
as a secondary filter/match within that local data set. 3. Performance: This is extremely fast because there is zero network shuffle for a 400M x 400M join. 3. Recommended Table Schema For two 400M tables, I recommend this structure to balance performance and uniqueness:
Copy code
sql
CREATE TABLE table_a (
    received_hour DATETIME,
    auction_id BIGINT,  -- Unix nano
    imp_key INT,
    ...
)
PRIMARY KEY (received_hour, auction_id, imp_key)
PARTITION BY (received_hour) -- Use this for time-based pruning
DISTRIBUTED BY HASH(auction_id) BUCKETS 64 -- Same for both tables
PROPERTIES (
    "colocate_with" = "auction_join_group"
);
4. Why this works better for "One-to-Many" Since you mentioned "most records are unique by auction, only sometimes 1 auction has multiple imps": * Memory Efficiency: During the join, the hash table built in memory will be very "lean." Since 
auction_id
is almost unique, the hash buckets will typically contain only 1 (or very few) entries, making lookups nearly $O(1)$. * Skew Handling: If you had distributed by 
imp_key
, the node responsible for 
imp_key = 1
would have to hold 240M entries (60% of 400M) in its local memory hash table, likely causing a crash. By using 
auction_id
, that 60% is spread across all 64 buckets. Key Takeaways for your 400M x 400M Join: * Distribution Key: Always pick the high-cardinality 
auction_id
. * Join Type: You will achieve a Colocate Join, which is the fastest possible join in StarRocks. * Partitioning: Use 
received_hour
for Partition Pruning (so you only join today's 400M rows, not the entire history). * Data Type: Ensure 
auction_id
is a 
BIGINT
(not 
STRING
) to minimize the memory footprint of the join hash table. References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • using_starrocks/Colocate_join.md References • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • using_starrocks/query_acceleration_with_auto_increment.md
m
@Rocky Is the asynchronous data relocation task when I add more BE nodes computationally intensive?
r
When you add new BE nodes, StarRocks triggers an automatic process called Tablet Rebalancing. This process is generally not computationally (CPU) intensive, but it can be I/O and Network intensive. Here is a breakdown of what happens and how it affects your cluster: 1. Where the Load Goes * Network: This is the primary bottleneck. StarRocks must physically copy (clone) tablet files from high-load BE nodes to the new BE nodes. If your tables are 400M+ rows, this involves moving gigabytes or terabytes of data across your internal network. * Disk I/O: Both the source node (reading the data) and the destination node (writing the data) will see a spike in disk I/O. * CPU: CPU usage is relatively low because it’s mostly file transfers. The CPU is only used for basic file checksums and metadata updates. 2. Impact on Query Performance While rebalancing is designed to run in the background, you may notice two specific impacts: * Colocate Join Degradation: This is the most critical for your use case. If you use Colocate Joins, the affected "Colocation Groups" will be marked as Unstable during the relocation. While unstable, queries will degrade from Colocate Joins to standard Shuffle Joins, which will significantly increase query latency and CPU usage for your 400M x 400M joins. * Tail Latency: The increased Disk I/O and network traffic can cause slight increases in "p99" query latencies for other concurrent workloads. 3. How to Control the Load If you are worried about the impact on your production environment, you can throttle the rebalancing speed using these parameters: FE Parameters (Control Concurrency): * `tablet_sched_slot_num_per_path`: Controls how many concurrent clone tasks can happen per disk (default is 
2
). Increase this for faster rebalancing, or decrease it to reduce I/O impact. * `tablet_sched_max_balancing_tablets`: The total number of tablets being moved across the cluster at once (default 
500
). * `tablet_sched_disable_balance`: You can set this to 
true
during peak hours to stop rebalancing entirely and set it back to 
false
at night. BE Parameters (Control Bandwidth): * `max_download_speed_kbps`: Limits the network bandwidth used for cloning on each BE (default is ~50MB/s). 4. How to Monitor Progress You can track the rebalancing status with these commands: * Check BE Load: 
SHOW PROC '/backends';
(Look at the 
TabletNum
and 
DataUsedCapacity
columns to see them evening out). * Check Active Tasks: 
SHOW PROC '/cluster_balance';
(Shows exactly which tablets are currently moving). * Check Colocate Status: 
SHOW PROC '/colocation_group';
(Check the 
IsStable
column; if it's 
false
, your optimized joins are temporarily disabled). Recommendation for Your Scale: Since you are joining 400M row tables, I recommend adding BE nodes during off-peak hours. Once you add the nodes, wait for the 
colocation_group
to return to 
IsStable = true
before running your heavy performance benchmarks or critical reports, as the shift from a Colocate Join to a Shuffle Join is a much bigger performance hit than the rebalancing task itself. References • administration/management/BE_configuration.md • administration/management/resource_management/Replica.md • administration/management/monitoring/metrics.md • administration/management/FE_configuration.md
m
@Rocky Does it matter if I pre-convert my boolean fields to int (1/0) before json marshalling for insertion to starrocks? Is it marginal gains?
r
Converting boolean fields to integers (`1`/`0`) before JSON marshalling for StarRocks ingestion provides marginal gains in most cases, but there is one specific scenario (Flat JSON) where it might actually be a disadvantage or a subtle optimization depending on your version. Here is the breakdown based on how StarRocks handles these types: 1. If your destination column is typed as 
BOOLEAN
 If your StarRocks table schema defines the column as 
BOOLEAN
(which is internally a 1-byte 
TINYINT
): * Ingestion: StarRocks' JSON loader is highly optimized. It will correctly map JSON 
true
to 
1
and 
false
to 
0
during the parsing phase. * Performance: The CPU difference between parsing the string 
"true"
vs the character 
"1"
is negligible at the scale of 400M rows. * Bandwidth: You save 3-4 bytes per field per row by using `1`/`0`. For 400M rows, that’s roughly 1.2 GB to 1.6 GB of network transfer saved. If you have many boolean fields, this can add up to a faster 
Stream Load
. * Verdict: Marginal. Do it if it’s easy in your pipeline, but don't re-architect for it. 2. If you are using the 
JSON
Data Type (and "Flat JSON") If you are storing these fields inside a StarRocks 
JSON
column (rather than a flat table column) and using the Flat JSON feature (enabled by default in v4.0+): * Type Inference: StarRocks automatically "flattens" JSON keys into hidden sub-columns for performance. * If you provide `1`/`0`, StarRocks will likely infer the sub-column as a 
BIGINT
 (8 bytes). * If you provide `true`/`false`, older versions of Flat JSON might keep it as a 
JSON
 type sub-field (which is less optimized than a standard numeric type). * The Irony: Converting 
true
to 
1
might make your queries faster (because it becomes a standard numeric 
BIGINT
sub-column), but it actually increases storage usage (1 byte for a real boolean vs 8 bytes for a flattened 
BIGINT
). * Verdict: If performance is the priority for JSON-path queries (e.g., 
WHERE data->'is_valid' = 1
), then `1`/`0` is slightly better for current Flat JSON inference. 3. Client-Side Marshalling Overhead In most languages (Go, Java, Python), the overhead of a conditional check to convert a 
bool
to an 
int
during JSON construction is usually offset by the fact that writing a single digit (
1
) to the output buffer is faster than writing a 4-character string (
true
). Summary Recommendation * Use 
1
and 
0
if: You are constrained by network bandwidth or are using the 
JSON
data type and want to ensure the fields are treated as numbers for faster filtering. * Stick with 
true
and 
false
if: You want your raw data to be more readable and you are loading into a typed 
BOOLEAN
column (StarRocks handles this perfectly). Pro-tip for your 400M row tables: If these booleans are frequently used in 
WHERE
clauses (e.g., 
is_deleted = false
), the most significant performance gain isn't the data type—it's ensuring these columns are not the first column in your 
DUPLICATE KEY
or 
PRIMARY KEY
list if they have low cardinality, as they make for poor indexes. However, they are excellent candidates for Bitmap Indexes, which can drastically speed up filtering on boolean-like data. References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md • loading/Json_loading.md
m
@Rocky
Copy code
[42000][1064] Partitions in table requests have different buckets number

PartitionId,PartitionName,VisibleVersion,VisibleVersionTime,VisibleVersionHash,State,PartitionKey,Range,DistributionKey,Buckets,ReplicationNum,StorageMedium,CooldownTime,LastConsistencyCheckTime,DataSize,StorageSize,IsInMemory,RowCount,DataVersion,VersionEpoch,VersionTxnType,TabletBalanced
6470613,p2026050320,3065,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-03 20:00:00]; ..types: [DATETIME]; keys: [2026-05-03 21:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,316.1GB,316.1GB,false,189100124,3065,419433768781086720,TXN_NORMAL,false
6472035,p2026050321,2990,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-03 21:00:00]; ..types: [DATETIME]; keys: [2026-05-03 22:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,304.2GB,304.2GB,false,183859872,2990,419441318452789248,TXN_NORMAL,false
6473456,p2026050322,2906,2026-05-04 19:40:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-03 22:00:00]; ..types: [DATETIME]; keys: [2026-05-03 23:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,293.3GB,293.3GB,false,179002156,2906,419448868122394624,TXN_NORMAL,false
6474877,p2026050323,2875,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-03 23:00:00]; ..types: [DATETIME]; keys: [2026-05-04 00:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,285.5GB,285.5GB,false,175045057,2875,419456416969916416,TXN_NORMAL,false
6476306,p2026050400,2950,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 00:00:00]; ..types: [DATETIME]; keys: [2026-05-04 01:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,302.1GB,302.1GB,false,185349434,2950,419463968122208256,TXN_NORMAL,false
6477727,p2026050401,2900,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 01:00:00]; ..types: [DATETIME]; keys: [2026-05-04 02:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,291GB,291GB,false,178906457,2900,419471521036107776,TXN_NORMAL,false
6479139,p2026050402,2805,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 02:00:00]; ..types: [DATETIME]; keys: [2026-05-04 03:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,263.3GB,263.3GB,false,166609800,2805,419479066647724032,TXN_NORMAL,false
6480555,p2026050403,2494,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 03:00:00]; ..types: [DATETIME]; keys: [2026-05-04 04:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,213.4GB,213.4GB,false,131193717,2494,419486616929697792,TXN_NORMAL,false
6481972,p2026050404,2796,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 04:00:00]; ..types: [DATETIME]; keys: [2026-05-04 05:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,242.3GB,242.3GB,false,140818712,2796,419494168681775104,TXN_NORMAL,false
6483389,p2026050405,2699,2026-05-04 19:40:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 05:00:00]; ..types: [DATETIME]; keys: [2026-05-04 06:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,212.6GB,212.6GB,false,123013601,2699,419501716803682304,TXN_NORMAL,false
6484809,p2026050406,2432,2026-05-04 19:38:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 06:00:00]; ..types: [DATETIME]; keys: [2026-05-04 07:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,180.5GB,180.5GB,false,103165234,2432,419509268293615616,TXN_NORMAL,false
6486250,p2026050407,2275,2026-05-04 19:37:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 07:00:00]; ..types: [DATETIME]; keys: [2026-05-04 08:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,172GB,172GB,false,97268540,2275,419516822839099392,TXN_NORMAL,false
6487658,p2026050408,2168,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 08:00:00]; ..types: [DATETIME]; keys: [2026-05-04 09:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,167GB,167GB,false,95078333,2168,419524367918039040,TXN_NORMAL,false
6489070,p2026050409,2173,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 09:00:00]; ..types: [DATETIME]; keys: [2026-05-04 10:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,188.6GB,188.6GB,false,107772199,2173,419531916878807040,TXN_NORMAL,false
6490481,p2026050410,2313,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 10:00:00]; ..types: [DATETIME]; keys: [2026-05-04 11:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,232.1GB,232.1GB,false,136120151,2313,419539465199943680,TXN_NORMAL,false
6491913,p2026050411,2531,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 11:00:00]; ..types: [DATETIME]; keys: [2026-05-04 12:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,257.7GB,257.7GB,false,156825844,2531,419547016178171904,TXN_NORMAL,false
6493333,p2026050412,2394,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 12:00:00]; ..types: [DATETIME]; keys: [2026-05-04 13:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,276.7GB,276.7GB,false,167405106,2394,419554565690490880,TXN_NORMAL,false
6494752,p2026050413,2430,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 13:00:00]; ..types: [DATETIME]; keys: [2026-05-04 14:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,276.3GB,276.3GB,false,166794214,2430,419562119137067008,TXN_NORMAL,false
6496169,p2026050414,2299,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 14:00:00]; ..types: [DATETIME]; keys: [2026-05-04 15:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,285.5GB,285.5GB,false,171983896,2299,419569664155189248,TXN_NORMAL,false
6497589,p2026050415,2166,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 15:00:00]; ..types: [DATETIME]; keys: [2026-05-04 16:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,271.8GB,271.8GB,false,164450827,2166,419577215108251648,TXN_NORMAL,false
6499008,p2026050416,2124,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 16:00:00]; ..types: [DATETIME]; keys: [2026-05-04 17:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,274.4GB,274.4GB,false,167095943,2124,419584766757568512,TXN_NORMAL,false
6500429,p2026050417,1868,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 17:00:00]; ..types: [DATETIME]; keys: [2026-05-04 18:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,277.3GB,277.3GB,false,169547358,1868,419592315141619712,TXN_NORMAL,false
6501849,p2026050418,1368,2026-05-04 19:41:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 18:00:00]; ..types: [DATETIME]; keys: [2026-05-04 19:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,355.2GB,355.2GB,false,217943669,1368,419599864609898496,TXN_NORMAL,false
6503272,p2026050419,632,2026-05-04 19:41:23,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-05-04 19:00:00]; ..types: [DATETIME]; keys: [2026-05-04 20:00:00]; ),auction_id,128,1,HDD,9999-12-31 15:59:59,,225.5GB,225.5GB,false,142157113,632,419607422288527360,TXN_NORMAL,false
Bucket number is all 128
r
The error 
[42000][1064] Partitions in table requests have different buckets number
occurs because StarRocks requires strict uniformity of bucket counts across all partitions when a table is part of a Colocation Group (i.e., it has the 
"colocate_with"
property). Even though the 24 partitions in your snippet all show 
128
buckets, the error persists for one of the following reasons: 1. Hidden or Historical Partitions There is likely a partition not included in your snippet (perhaps an old one or an automatically created future one) that has a different bucket count. * How to check: Run this query to find the culprit:
Copy code
sql
    -- Check for any partition that does NOT have 128 buckets
    SHOW PARTITIONS FROM requests;
    -- Look specifically for any row where the 'Buckets' column is not 128.
2. Mismatch in Table-Level Default When you use 
colocate_with
, the table's default bucket count (defined in the 
DISTRIBUTED BY
clause of the 
CREATE TABLE
statement) must match the bucket count of all its partitions. If you created the table with 
BUCKETS 64
but manually added partitions with 
BUCKETS 128
, StarRocks will throw this error during joins. * How to check: Run 
SHOW CREATE TABLE requests;
and look at the 
DISTRIBUTED BY
clause at the bottom. 3. Dynamic Partitioning Config Mismatch If you are using Dynamic Partitioning, check the table properties. If 
dynamic_partition.buckets
is set to something other than 
128
, any new partition created by the system will have the wrong count, breaking the colocation group. * How to check:
Copy code
sql
    SHOW CREATE TABLE requests;
    -- Look for "dynamic_partition.buckets" = "..."
────────── How to Fix It You can unify the bucket count for the entire table (all existing partitions + the table default) using a single command (available in StarRocks v3.2+):
Copy code
sql
-- This will force the table-level default and ALL existing partitions to 128 buckets
ALTER TABLE requests DISTRIBUTED BY HASH(auction_id) BUCKETS 128;
Note: This is an asynchronous operation. You can monitor its progress with 
SHOW ALTER TABLE COLUMN;
. If you are on a version older than v3.2: You cannot alter the bucket count of existing partitions. You would need to: 1. Create a new temporary table with the correct 
BUCKETS 128
setting. 2. Use 
INSERT INTO ... SELECT * FROM ...
to move the data. 3. Swap the tables using 
ALTER TABLE ... REPLACE WITH ...
. Key Takeaway for Colocation: For a Colocate Join to work, three things must be identical across all tables in the group and all their partitions: 1. The Distribution Key types and order (e.g., both must be 
HASH(auction_id)
). 2. The Bucket Count (e.g., both must be 
128
). 3. The Replication Number (e.g., both must be 
1
). References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md References • using_starrocks/Colocate_join.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md
m
@Rocky I verified all buckets 128 in show partitions from requests. This is my table properties
Copy code
PRIMARY KEY(`received_hour`, `auction_id`, `imp_key`)
PARTITION BY date_trunc('hour', received_hour)
DISTRIBUTED BY HASH(`auction_id`) BUCKETS 128
ORDER BY(`received`, `auction_id`)
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"partition_live_number" = "24",
"replicated_storage" = "true",
"replication_num" = "1"
);
r
This error 
Partitions in table requests have different buckets number
is a strict validation check in StarRocks. It typically occurs when you are performing a Join (specifically a Colocate Join or Bucket Shuffle Join) and the StarRocks optimizer detects that not all partitions of the table have the same bucket count. Even though your snippet shows 
128
for those 24 partitions, there are three hidden places where a mismatch usually lurks: 1. The Table-Level "Template" Bucket Count In StarRocks, the table has a "default" bucket count defined in its metadata. If you created the table with 
BUCKETS 64
and then later manually added partitions with 
BUCKETS 128
(or vice-versa), the table is considered "inconsistent" for colocation optimizations. * Check this: Run 
SHOW CREATE TABLE requests;
* Look for: The 
DISTRIBUTED BY HASH(...) BUCKETS XX
line at the bottom. If that number is not 128, that is your problem, even if all your current partitions are 128. 2. The "placeholder" or Hidden Partitions Since you are using 
PARTITION BY date_trunc(...)
and 
"partition_live_number" = "24"
, StarRocks is automatically managing partitions. * It is possible that a partition was just created or is being retired that has a different bucket count. * How to find the culprit: Run this query to find the specific partition that doesn't match:
Copy code
sql
    -- Note: Buckets might be in a different column index depending on version,
    -- but this helps you scan the whole list quickly.
    SHOW PARTITIONS FROM requests;
Look specifically for any row where the 
Buckets
column is empty, 0, or something other than 128. 3. Expression-Based Partitioning Mismatch You are using 
PARTITION BY date_trunc('hour', received_hour)
. If you have any data that falls outside the 24-hour window, or if you have NULL values in the 
received_hour
column, StarRocks might have created a "default" or "catch-all" partition that doesn't follow the 128-bucket rule. ────────── The Fix To resolve this and unify the entire table (metadata + all partitions), you should run the following 
ALTER
command. This will force the table-level default and all existing partitions to 128:
Copy code
sql
-- This synchronizes the table metadata and all partitions to exactly 128 buckets
ALTER TABLE requests DISTRIBUTED BY HASH(auction_id) BUCKETS 128;
Why is this happening now? If you recently added BE nodes or changed the bucket count, and you are joining this table with another 400M row table, StarRocks is trying to perform a Colocate Join to keep the query fast. If the bucket counts are not perfectly uniform across every single partition of 
requests
, the Colocate Join optimization fails with this specific error to prevent incorrect results or massive data shuffles. Note on `replicated_storage`: Since you have 
"replicated_storage" = "true"
, you are likely on a StarRocks 3.x architecture. In this mode, bucket uniformity is even more critical for partition management and high availability. References • table_design/data_distribution/Data_distribution.md • using_starrocks/Colocate_join.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/resource_management/Replica.md References • using_starrocks/Colocate_join.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md
m
@Rocky I've already ran alter table requests but i used default buckets 128 instead of just buckets 128
r
That is exactly the cause of the issue. In StarRocks (specifically from v3.5.8 and v4.0.1 onwards), the 
DEFAULT BUCKETS
keyword behaves differently than the standard 
BUCKETS
keyword in an 
ALTER TABLE
statement: 1. 
DEFAULT BUCKETS 128
: This only updates the table’s metadata template. It ensures that any future partitions created (e.g., by your 
date_trunc
expression or dynamic partitioning) will have 128 buckets. It does not change or convert the bucket count for your existing 24 partitions. 2. 
BUCKETS 128
(without 
DEFAULT
): This triggers a background data conversion (schema change) to re-bucket all existing partitions to 128, as well as updating the table default. Why you are seeing the error Even though your 
SHOW PARTITIONS
output shows 
128
for those 24 partitions, the error 
Partitions in table requests have different buckets number
is triggered because: * The Table Property Mismatch: StarRocks' optimizer (when planning a join) compares the bucket count of the partitions being scanned against the table-level distribution property. If you used 
DEFAULT BUCKETS
, the table property might still be out of sync with what the FE expects for consistent optimization (like Bucket Shuffle Join). * The "Shadow" or "NULL" Partition: With expression partitioning (
date_trunc
), StarRocks sometimes maintains a hidden or catch-all partition. If that partition was created under the old bucket count, it will cause this mismatch. How to Fix It You need to run the 
ALTER
command without the 
DEFAULT
keyword to ensure the entire table and its metadata are synchronized:
Copy code
sql
-- This will force a synchronization of all existing partitions 
-- and the table metadata to exactly 128.
ALTER TABLE requests DISTRIBUTED BY HASH(auction_id) BUCKETS 128;
Important Note on 
replicated_storage
 Since your DDL shows 
"replicated_storage" = "true"
, if you are running in StarRocks Shared-Data (Cloud Native) mode: * StarRocks does not currently support re-bucketing existing data via 
ALTER TABLE
. * If the command above returns an error saying it's unsupported, you will have to: 1. Create a new table with the correct 
BUCKETS 128
. 2. 
INSERT INTO new_table SELECT * FROM requests
. 3. 
ALTER TABLE requests REPLACE WITH new_table
. Recommended Verification To find exactly which part of your table is "different," run these two commands: 1. Check for any outlier partitions:
Copy code
sql
    SHOW PARTITIONS FROM requests WHERE Buckets != 128;
2. Check the table-level template:
Copy code
sql
    SHOW CREATE TABLE requests;
    -- Scroll to the bottom and see if it says "DISTRIBUTED BY HASH(...) BUCKETS 128"
If 
SHOW CREATE TABLE
still shows a different number at the bottom, the 
DEFAULT
command didn't fully satisfy the optimizer's requirement for a uniform bucket layout. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Colocate_join.md • table_design/data_distribution/Data_distribution.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/data_distribution/Data_distribution.md
Open in Slack
PreviousNext
Powered by