@Rocky Is there any way I can add indexes/make queries faster with fiters of columns inside JSON nested fields? I see 4.0+ there is flat JSON, which on filter works like direct column but it is 2level nested field any way to make query fast?

To speed up queries that filter on nested fields (even at the 2nd level or deeper), you have several options in StarRocks. While Flat JSON (v4.0+) handles top-level common keys automatically, it may not recursively flatten deep nested paths into standard type columns by default. Here are the best ways to optimize filters for 2-level nested fields: 1. Generated Columns (Recommended for specific paths) The most effective way to index a specific nested field is to use Generated Columns. This allows you to extract a nested value into its own physical column during ingestion. * How it works: You define a standard column that is automatically populated by an expression (e.g.,

json_col->'a'->'b'

). * Performance Benefit: Once the field is a real column, you can: * Add it to the Sort Key (

DUPLICATE KEY

) for efficient range filtering. * Add a Bitmap Index (for low cardinality) or Bloom Filter (for high cardinality). * Add an Inverted Index (available from v3.3 for VARCHAR). Example:

sql
CREATE TABLE users (
    id INT,
    data JSON,
    -- Extract 2nd-level field 'city' from {"address": {"city": "NY"}}
    city VARCHAR(50) AS json_string(json_query(data, "$.address.city"))
) 
DUPLICATE KEY(id, city) -- Sort by city to make filters extremely fast
DISTRIBUTED BY HASH(id);

2. Verify and Tune Flat JSON In StarRocks 4.0, Flat JSON is enabled by default. It detects common keys and stores them as sub-columns. * Check effectiveness: Use

SELECT flat_json_meta(json_col) FROM table[_META_];

to see which paths StarRocks has automatically flattened. * Nesting Depth: Currently, Flat JSON primarily materializes common keys at the root. If your 2nd-level field is inside a common object, that object might be extracted as a JSON sub-column, but the fields inside it might not be converted to standard types (INT/STRING) automatically. * Optimization Variable: Ensure path rewriting is enabled so StarRocks can use sub-columns instead of parsing the whole blob:

sql
    SET cbo_json_v2_rewrite = true; -- Rewrites get_json_* to sub-column access
    SET cbo_prune_json_subfield = true; -- Enables sub-field pruning

3. Inverted Indexes (v3.3+) If your nested fields are strings and you need flexible "contains" or "match" filtering, you can create an Inverted Index. * Note: You typically apply the Inverted Index to a Generated Column (as shown in Step 1) rather than the raw JSON column itself to ensure the engine knows exactly what to index. Summary Checklist 1. For high-frequency filters: Use a Generated Column + Sort Key. This is the fastest possible way (O(log N) or better). 2. For point lookups on many unique values: Use a Generated Column + Bloom Filter Index. 3. For ad-hoc queries: Rely on Flat JSON (v4.0), but check

flat_json_meta

to ensure your fields are being caught. If they aren't, you can increase

flat_json.column.max

(default 100) via

ALTER TABLE ... SET ("flat_json.column.max" = "200")

. References • using_starrocks/Flat_json.md • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md • sql-reference/data-types/semi_structured/JSON.md

@Rocky is bloom filter/bitmap index, which is recommended for filters with LIKE or join columns or group by col?

To optimize your queries in StarRocks using indexes, it is important to distinguish between data pruning (filtering rows early) and the actual execution of joins or aggregations. Here is the recommendation for each specific scenario: 1. Filters with

LIKE

Standard Bloom Filter and Bitmap indexes do not support

LIKE

. * Recommended Index: Inverted Index (v3.3+) or N-gram Bloom Filter. * Inverted Index (GIN): Best for general text search. Use the

builtin

implementation with

dict_gram_num

(e.g.,

2

or

3

) to accelerate wildcard searches like

WHERE col LIKE '%abc%'

. * N-gram Bloom Filter: A specialized version of a Bloom filter that tokenizes strings into "grams." It accelerates

LIKE

queries if the search string is longer than the

gram_num

defined during index creation. * Example (Inverted Index):

sql
    CREATE TABLE t (
        v VARCHAR(255),
        INDEX idx_v (v) USING GIN("dict_gram_num" = "2")
    ) DUPLICATE KEY(v);

2. Join Columns Indexes do not directly speed up the "hash join" algorithm itself, but they are critical for Runtime Filter performance and Data Pruning. * Recommended Index: Bloom Filter Index. * If you filter the "small" side of a join, StarRocks creates a Runtime Filter (effectively a dynamic Bloom filter) to prune data on the "large" side. * A persistent Bloom Filter Index is highly recommended for high-cardinality columns (like IDs) that are used in point lookups or as join keys. It helps StarRocks skip entire data pages that don't contain the necessary keys, reducing the IO load for the join. * Recommendation: Place join columns in the Sort Key (the first few columns of the table definition) to enable the system's built-in Prefix Index. 3. Group By Columns Similar to joins, indexes speed up

GROUP BY

by reducing the amount of data the engine has to aggregate. * Recommended Index: Prefix Index (Sort Key). * StarRocks stores data sorted by the keys defined in the

DUPLICATE/PRIMARY/UNIQUE KEY

. If your

GROUP BY

column is part of the Sort Key, the engine can read data in an ordered fashion, which is significantly faster and uses less memory for the aggregation. * Bitmap Index: Use this if your

GROUP BY

columns have low cardinality (e.g.,

city

,

gender

,

status

) and you are filtering by them. It is excellent for filtering out rows before the grouping happens. Summary Comparison Query Type | Recommended Index | Why? LIKE '%abc%' | Inverted Index / N-gram BF | Standard BF/Bitmap only support exact matches or ranges. Join Columns | Sort Key + Bloom Filter | Prunes data blocks during scan; Sort Key enables Prefix Index. Group By | Sort Key | Data is pre-sorted, making hash aggregation much more efficient. Point Filters (

=

,

IN

) | Bloom Filter (High Card) or Bitmap (Low Card) | Skips data pages efficiently. Pro Tip: Always prioritize the Sort Key (Prefix Index). Manual indexes (Bloom/Bitmap) should be added only if the column cannot be part of the Sort Key (e.g., you already have several columns in the sort key, or the column is a long). or the column is a

VARCHAR

that would truncate the prefix index truncation point` field matches is. Reference: • table_design/indexes/inverted_index.md • table_design/indexes/Ngram_Bloom_Filter_Index.md • table_design/indexes/Bitmap_index.md References • table_design/indexes/indexes.md • best_practices/query_tuning/schema_tuning.md

@Rocky on autoscaling up/down tablets are redistributed to alive compute nodes in shared data, if a table has 3 buckets only and compute nodes previosly are 7 (assume 0,4, 6 are tablet distribution ndoes) , after scaling to 2 nodes only 0,1 are there. How does it redistribute the tablets among compute nodes?

In StarRocks Shared-Data (Compute-Storage Separation) architecture, the redistribution of tablets when scaling up or down works fundamentally differently than in Shared-Nothing mode. Here is how the 3 tablets would be redistributed among your 2 remaining nodes (Node 0 and Node 1): 1. "Redistribution" is Logical, Not Physical Unlike Shared-Nothing (where scaling requires physically moving gigabytes of data between disks), in Shared-Data mode, the data stays in remote storage (S3/HDFS). * Redistribution is simply a metadata update in the Frontend (FE). * The FE's Tablet Scheduler changes the mapping of which Compute Node (CN) is "responsible" for which tablet. 2. Consistent Hashing and Cache Affinity StarRocks uses a Consistent Hashing (or stable mapping) algorithm to assign tablets to CNs. The goal is to minimize re-mapping and preserve the Local Data Cache. In your specific scenario: * Original State (7 nodes): Tablets were on Node 0, Node 4, and Node 6. * Target State (2 nodes): Only Node 0 and Node 1 are alive. What happens to the tablets: 1. Node 0 Tablet: Since Node 0 is still alive, the tablet already assigned to it will stay on Node 0. This ensures that the data already cached on Node 0's local disk remains valid and queries stay fast. 2. Nodes 4 & 6 Tablets: Since these nodes are gone, the 2 tablets they were hosting are now "orphaned." 3. Reassignment: The Tablet Scheduler will assign these 2 orphaned tablets to the remaining nodes (0 and 1). To maintain balance (3 tablets / 2 nodes), it will aim for a distribution of 2:1. * Node 0 will likely end up with 2 tablets (its original + one from Node 4/6). * Node 1 will end up with 1 tablet (from Node 4/6). 3. Impact on Performance (The "Cold" Cache) When a tablet is moved to a new node (like Node 1), that node does not have the data in its local cache yet. * The first query hitting those reassigned tablets will experience a Cache Miss. * Node 1 will pull the data from S3/remote storage, execute the query, and simultaneously populate its local cache. * Subsequent queries will return to sub-second performance once the cache is "warmed up." Summary Table: 3 Tablets, 7 $\rightarrow$ 2 Nodes Tablet | Original Node | New Node | Reason Tablet A | Node 0 | Node 0 | Node still alive; preserves cache affinity. Tablet B | Node 4 | Node 1 (likely) | Node 4 is dead; moved to achieve balance. Tablet C | Node 6 | Node 0 or 1 | Node 6 is dead; reassigned to fill remaining capacity. Pro Tip: If you frequently scale down to very few nodes, consider increasing your bucket count (tablets). With only 3 tablets, you can only ever utilize 3 CPU cores/nodes for a single query. Increasing the bucket count allows for better parallelism and more granular load balancing during autoscaling. References • introduction/Architecture.md • faq/shared_data_faq.md • deployment/shared_data/s3.md • administration/management/resource_management/Replica.md References • table_design/data_distribution/Data_distribution.md • using_starrocks/Colocate_join.md • best_practices/bucketing.md

@Rocky the same scenario from scaling up from 2 nodes to 7nodes, how does the tablet redistribution happens? currently node 0 has 2 tablets assigned and node 1 has 1.

When scaling up from 2 nodes to 7 nodes in Shared-Data mode, the redistribution of tablets follows these principles: 1. Logical Rebalancing Because the data resides in remote storage (S3/HDFS), StarRocks does not "move" files. Instead, the Frontend (FE) Tablet Scheduler updates its internal mapping. The goal is to distribute the workload (tablet ownership) as evenly as possible across the new capacity. 2. The Step-by-Step Redistribution In your scenario (3 tablets total, scaling 2 $\rightarrow$ 7 nodes): * Starting Point: Node 0 has 2 tablets; Node 1 has 1 tablet. * Detection: The FE detects 5 new Compute Nodes (Nodes 2, 3, 4, 5, 6) with 0 tablets. * Reassignment: To achieve balance, the FE will attempt to move tablets from the "heaviest" nodes to the "lightest" nodes. * Node 0 is over-taxed (2 tablets) compared to the new cluster average (~0.43 tablets per node). * Node 1 is also technically above average, but since there are only 3 tablets for 7 nodes, the most balanced state is one tablet per node for three unique nodes. * Result: 1. The FE will likely pick one tablet from Node 0 and reassign it to a new node (e.g., Node 2). 2. Node 0 keeps 1 tablet. 3. Node 1 keeps 1 tablet. 4. Node 2 (newly added) is assigned 1 tablet. 5. Nodes 3–6 remain idle (hosting 0 tablets) because there aren't enough tablets to go around. 3. Cache Warming (The "Cold" Node) The tablet that was moved from Node 0 to Node 2 will experience a cache miss on its first few queries. * Node 0 still has the data for that tablet on its local disk, but it is no longer allowed to process queries for it. * Node 2 must pull the data from remote storage to its local disk cache. * This is why, immediately after scaling up, you might see a brief "warm-up" period where some queries are slightly slower. 4. Why "Bucketing" Matters for Scaling With only 3 tablets, your cluster can only utilize 3 nodes for any single query on that table, regardless of having 7 nodes available. * Bottleneck: Even after scaling to 7 nodes, 4 nodes will do nothing for this specific table. * Recommendation: To fully utilize a 7-node cluster, you should have at least 7 to 14+ tablets (usually 2x the number of CPU cores or nodes is a good rule of thumb). This allows the FE to give every node at least one piece of the work. Summary of Parameters This behavior is controlled by the following FE configuration: * `lake_enable_balance_tablets_between_workers`: Default is

true

. This allows the FE to move tablet assignments between Compute Nodes to maintain balance. * `tablet_sched_disable_balance`: If set to

true

, no redistribution will happen, and Node 0 would continue to handle 2/3 of the load alone. References • administration/management/FE_configuration.md • faq/shared_data_faq.md • administration/management/compaction.md • administration/management/resource_management/Replica.md References • administration/management/resource_management/Replica.md • administration/management/compaction.md • table_design/data_distribution/Data_distribution.md • using_starrocks/Colocate_join.md • administration/management/FE_configuration.md

@Rocky If my version is 4.1.x and my table has x buckets distributed by x , does it still split the tablets based on size? or if no distribution of tablets mentioend in table, does it automatically split big tablets ?

In StarRocks v4.1.x, there are significant changes to how tablets are handled, particularly for Shared-Data (Cloud-Native) clusters. The answer depends on whether you are using the traditional Hash Distribution or the newly introduced Range-based Data Distribution. 1. If you specify

DISTRIBUTED BY HASH(...) BUCKETS x

* Static Count: Traditionally, Hash Distribution uses a fixed number of buckets. If you specify

BUCKETS 10

, StarRocks will create 10 tablets and keep them at 10. * Manual Split (v4.1+): In v4.1, you can now manually trigger a split for specific "hot" or "large" tablets using the new syntax:

ALTER TABLE <name> SPLIT TABLET <id>;

. This allows you to split a single oversized tablet without re-sharding the entire table. * Large Tablet Support: Starting in v4.1, StarRocks has increased the recommended maximum size for a single tablet to 100 GB (previously 1-10 GB) to reduce metadata overhead. 2. If you specify no distribution (Automatic Bucketing) If you omit the

DISTRIBUTED BY

clause or the

BUCKETS

keyword, StarRocks uses Automatic Tablet Distribution: * Initial Calculation: StarRocks automatically calculates an initial bucket count based on your cluster size (number of compute nodes) and the historical data volume of the table. * Dynamic Growth (Random Bucketing): For Duplicate Key tables using Random Bucketing (default if no key is specified), you can set the property

"bucket_size" = "1073741824"

(1 GB). This tells StarRocks to automatically split and add buckets as data is loaded and the partition grows. 3. The New "Adaptive" Splitting (v4.1+ Feature) StarRocks v4.1 introduces a headline feature called Adaptive Multi-Tenant Data Management for Shared-Data clusters. This changes the "split" behavior fundamentally: * Range-based Distribution: Instead of hashing, you can distribute data by Range. This is enabled via the FE config

enable_range_distribution = true

. * Automatic Splitting: When this is active, StarRocks automatically monitors tablet size. When a tablet exceeds the

tablet_reshard_target_size

(default 10 GB), the system splits that specific range into two new tablets automatically. * No Manual Intervention: Unlike Hash bucketing, this does not require any

ALTER TABLE

commands; the system "observes" the growth and corrects the layout at runtime. Summary Table for v4.1.x Feature | Hash Bucketing | Random Bucketing | Range Distribution (New in 4.1) Bucket Count | Fixed (unless manual

ALTER

) | Dynamic (if

bucket_size

set) | Fully Automatic Splitting | Manual

ALTER TABLE ... SPLIT

| Automatic during Load | Automatic (Adaptive) Use Case | Joins/Aggs on specific keys | Log data / Append only | Multi-tenant / Dynamic growth Target Size | Up to 100 GB | ~1-4 GB (per split) | 10 GB (default) Recommendation: If you are on v4.1.x with a Shared-Data cluster and want the system to handle big tablets for you, look into the Range Distribution feature or use Random Bucketing with a defined

bucket_size

. If you must use Hash Distribution for performance (like Colocate Joins), you should monitor tablet sizes and use the new

ALTER TABLE ... SPLIT

command for any tablet that grows beyond 100 GB. References • table_design/data_distribution/Data_distribution.md • release_notes/release-4.0.md • table_design/data_distribution/feature-support-data-distribution.md • best_practices/bucketing.md • administration/management/FE_configuration.md

@Rocky auto splitting with range distribution doesn't work for hash bucketing? works only for distributed by random?

In StarRocks v4.1.x, the automatic splitting and merging behavior is specific to the distribution method you choose. To answer your question directly: No, the "Range Distribution" auto-splitting logic does not work for Hash bucketing. They are two fundamentally different ways of organizing data. Here is how the splitting logic differs across the three distribution methods available in v4.1: 1. Range Distribution (New in v4.1) This is the only method that supports fully automatic, adaptive splitting and merging based on data size and skew. * How it works: Instead of hashing a key, StarRocks divides data into "ranges" based on your Sort Keys (or Primary Keys). * Auto-splitting: When a specific range (tablet) grows too large (e.g., exceeds the 10GB default target), the system automatically splits that range into two new tablets. * How to use it: You must enable the FE configuration

enable_range_distribution = true

. Then, create a table with a key type (e.g.,

PRIMARY KEY

or

DUPLICATE KEY

) but omit the

DISTRIBUTED BY

clause. 2. Hash Bucketing Hash bucketing is static by design because the mapping of a row to a bucket depends on a fixed hash function (

Hash(key) % Bucket_Count

). * Auto-splitting? No. If you define

DISTRIBUTED BY HASH(id)

, the number of buckets remains fixed because changing it would require re-hashing every single row in the table. * Manual Splitting (New in v4.1): While it isn't "automatic," v4.1 introduced the ability to manually split a hash tablet if it becomes a bottleneck:

sql
    ALTER TABLE my_table SPLIT TABLET <tablet_id>;

This allows you to break one large hash bucket into two without re-sharding the entire table. 3. Random Bucketing * Auto-splitting? Sort of. Since v3.2, if you use

DISTRIBUTED BY RANDOM

and set the property

"bucket_size" = "1073741824"

(1GB), StarRocks will dynamically add buckets during a data load if the volume is large. * Difference from Range: Random distribution doesn't "split" based on value ranges; it simply opens more buckets to write to as the volume grows to keep individual tablets from getting too big. Summary Comparison Feature | ***Range Distribution*** | ***Hash Bucketing*** | ***Random Bucketing*** Auto-Split? | Yes (Adaptive) | No | Yes (Dynamic Increase) Trigger | Tablet size/Data skew | N/A (Manual only) | Data volume during load Best For | Multi-tenant, varying sizes | Join performance (Colocation) | Log data, simple appends Setup | Omit

DISTRIBUTED BY

|

DISTRIBUTED BY HASH

|

DISTRIBUTED BY RANDOM

Conclusion: If your goal is to have the system automatically manage tablet sizes as data grows without you intervention, you should use the new Range Distribution in v4.1. If you must use Hash (for query performance/sharding), you will need to monitor tablet sizes and use the manual

SPLIT TABLET

command or set a high enough initial bucket count. References • table_design/data_distribution/Data_distribution.md • release_notes/release-4.0.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • table_design/data_distribution/dynamic_partitioning.md • table_design/data_distribution/Data_distribution.md

@Rocky if pk is uuid, what is recommended?

For a Primary Key (PK) table where the key is a UUID, the recommendation depends on whether you value static predictable performance (Hash) or automatic management (Range/Adaptive). 1. Recommendation: Use UUID v7 (Time-Ordered) If you have the choice, use UUID v7 (via the

uuid_v7()

function) instead of the traditional random UUID v4. * Why: UUID v7 is time-ordered. This provides better index locality, reduces disk fragmentation, and makes "Range Distribution" (in v4.1) much more efficient because new data naturally appends to the "end" of the range rather than being scattered randomly across all tablets. 2. Table Type & Indexing (Mandatory) UUIDs are long strings. If you have millions of rows, storing their PK index in memory will be extremely expensive. * Enable Persistent Index: You must set

"enable_persistent_index" = "true"

. This offloads the PK index from RAM to disk (SSD). * Cloud-Native Index: In a Shared-Data (v4.1) cluster, use

"persistent_index_type" = "CLOUD_NATIVE"

. This stores the index on S3/HDFS, meaning when you scale from 2 to 7 nodes, the new nodes can "see" the index immediately without rebuilding it from scratch. 3. Distribution Strategy (The v4.1.x Choice) Option A: Range Distribution (New in v4.1 - Recommended for Auto-Scaling) In v4.1, you can omit the

DISTRIBUTED BY

clause to let StarRocks manage tablets automatically. * How to do it: Set

FE config enable_range_distribution = true

, then create the table:

sql
    CREATE TABLE users (
        user_uuid varchar(36) NOT NULL,
        ...
    ) PRIMARY KEY (user_uuid)
    -- Omit DISTRIBUTED BY clause
    PROPERTIES (
        "enable_persistent_index" = "true",
        "persistent_index_type" = "CLOUD_NATIVE"
    );

* Behavior: StarRocks will monitor tablet sizes and automatically split them when they reach a target size (default 10GB). This is ideal for your 2-to-7 node scaling scenario because the system will split large tablets as needed to occupy the new nodes. Option B: Hash Distribution (Traditional) If you need specific performance characteristics (like Colocate Joins), use Hash. * How to do it:

DISTRIBUTED BY HASH(user_uuid) BUCKETS <X>

. * UUID Performance: Since UUIDs have extremely high cardinality, Hash distribution will be perfectly uniform. You will never have data skew. * Scaling Note: Remember that with Hash, the bucket count is fixed. If you create a table with only 3 buckets, scaling to 7 nodes will leave 4 nodes idle for that table. You would need to set a high enough bucket count (e.g., 20–40) upfront to allow for future growth. Summary of Recommendations Feature | Recommendation UUID Version | v7 (Time-ordered) for better performance. Table Type | Primary Key Table. Distribution | Range (Adaptive) in v4.1 to allow auto-splitting during scaling. Storage | Persistent Index (CLOUD_NATIVE) to save RAM and speed up scaling. Tablet Size | Target 10GB per tablet (controlled by

tablet_reshard_target_size

). References • table_design/table_types/primary_key_table.md • sql-reference/sql-functions/utility-functions/uuidv7.md • table_design/data_distribution/Data_distribution.md • best_practices/primarykey_table.md • administration/management/BE_configuration.md • best_practices/table_clustering.md • best_practices/partitioning.md

@Rocky how is the effect of colocate join in shared data architecture? does it always cache in same nodes to reduce the shuffle during joins ?

In the StarRocks Shared-Data (cloud-native) architecture, Colocate Join remains a powerful optimization. While the storage is decoupled, the system uses deterministic scheduling to achieve the same effect as local storage. 1. Does it reduce shuffle? Yes. Even though data lives in S3/Object Storage, the FE (Frontend) ensures that tablets belonging to the same "Bucket Sequence" across different tables in a Colocation Group are always scheduled to the same Compute Node (CN). * Because the two joining data slices are processed on the same physical CPU/RAM of a single CN, there is zero network shuffle between nodes. * The execution plan will show

colocate: true

on the Hash Join node, confirming the shuffle was skipped. 2. Does it always cache on the same nodes? Yes, by design. * Locality Binding: StarRocks maintains a mapping between specific buckets and specific CN nodes. When you query Bucket #5 of Table A and Table B, the FE will always send those tasks to the same CN. * Cache Convergence: Because that CN is the "home" for those specific buckets, its local Data Cache (on SSD/NVMe) will store the blocks for both tables. After the initial read from remote storage, subsequent joins will find both sides of the join in the local cache, making the performance indistinguishable from a shared-nothing cluster. 3. How does scaling (2 nodes to 7 nodes) affect this? This is where Shared-Data differs from Shared-Nothing: * Rebalancing: When you scale your cluster from 2 to 7 nodes, the FE will redistribute the "Bucket-to-CN" mapping to use the new nodes. * Temporary Shuffle: During the rebalance, the Colocation Group is marked as

Unstable

. For a short window, StarRocks will automatically fall back to a normal Shuffle Join to ensure query correctness while the mapping is in flux. * Cache Warmup: Once the mapping stabilizes on the 7 nodes, the new CNs will start pulling data from S3 to populate their local caches. You can use the

CACHE SELECT

(Warmup) feature to speed this up. Key Limitations in v4.1.x * Hash only: Colocate Join only works with Hash Distribution. The new "Range Distribution" (Adaptive Multi-tenant) does not support Colocate Joins because its tablet boundaries are dynamic and may not align across different tables. * Consistency: To keep the "Colocate" property, all tables in the group must have the exact same number of buckets and compatible distribution key types. Summary: In a Shared-Data cluster, Colocate Join provides the "best of both worlds"—the performance of local joins (via consistent CN scheduling and local caching) with the elasticity of cloud-native storage. References • using_starrocks/Colocate_join.md • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md • faq/shared_data_faq.md References • using_starrocks/Colocate_join.md • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md

@Rocky for colocation, all tables must have same number of tablets? what if I merge few partition tablets/what if I redistribute tablets for a specific partition? does it not work for colocation?

For Colocation Joins to work, StarRocks enforces strict rules on how data is laid out. Here is the breakdown of how tablet counts and redistribution affect colocation: 1. Does every table need the same number of tablets? Yes and No. You have to distinguish between Buckets and Tablets: * Buckets (Logical): All tables in a Colocation Group must have the exact same number of buckets (e.g.,

BUCKETS 16

). * Tablets (Physical): A tablet is the physical data file for a specific (Partition + Bucket) combination. * If Table A has 10 partitions and 16 buckets, it has 160 tablets. * If Table B has 1 partition and 16 buckets, it has 16 tablets. * This is allowed. As long as they share the same bucket count (16), StarRocks can colocate the 16 tablets of Table B with the corresponding 16 tablets of any partition in Table A. 2. Can you merge or split tablets in a Colocation Group? In StarRocks v4.1, you can manually split a hash tablet using

ALTER TABLE ... SPLIT TABLET

. However: * It will break Colocation: If you split Tablet #5 of Table A into two, but Tablet #5 of Table B remains a single tablet, they no longer have a 1-to-1 mapping. * System Rejection: For tables explicitly marked with

"colocate_with"

, StarRocks generally rejects operations that violate the Group Schema (like changing the bucket count for just one table). To perform such an operation, you would first have to remove the table from the group:

ALTER TABLE tbl SET ("colocate_with" = "");

. 3. What if I redistribute tablets for a specific partition? Starting in v3.2/4.0, StarRocks allows you to change the bucket count for a specific partition (e.g.,

ALTER TABLE tbl PARTITION p1 DISTRIBUTED BY HASH(id) BUCKETS 32

). * The Conflict: Colocation requires that all partitions in a table follow the same mapping as the first partition of the group's "Parent Table." * The Result: If you change the bucket count or distribution of a single partition so that it no longer matches the Colocation Group Schema: 1. The FE will detect the mismatch. 2. The Colocation Group will likely be marked as Unstable. 3. Queries involving that specific partition will degrade to a normal Shuffle Join (network transfer required), even if other partitions in the same table remain colocated. Summary Table: Actions and Colocation Impact Action | Impact on Colocation Join Different Partition Counts | Supported. (e.g., 10 partitions join with 1 partition). Different Bucket Counts | Not Supported. (Tables must have the same

BUCKETS

value). Split/Merge Tablet | Breaks Colocation. (Destroys the 1-to-1 bucket mapping). Redistribute 1 Partition | Breaks Colocation for that Partition. (The mapping becomes inconsistent). Recommendation: If you have highly skewed partitions that need different bucket counts or manual splitting, the Range Distribution (available in v4.1) is a better fit. It handles "hot" ranges by splitting them automatically. However, keep in mind that *Range Distribution does not currently support Colocate Joins*; it relies on StarRocks' high-speed internal shuffle. References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • administration/management/FE_configuration.md References

@Rocky can I get number fo tablets by compute node in prometheus metrics?

Yes, you can monitor the number of tablets per node (BE or CN) using Prometheus. Recommended Metric The primary metric for this is

starrocks_fe_tablet_num

. Although it is exposed via the FE (Frontend) Prometheus endpoint, it tracks the tablet distribution across all backends. * Metric Name:

starrocks_fe_tablet_num

* Type: Gauge (Instantaneous) * Labels: Usually includes

be_address

or

be_id

(representing the specific BE or CN). * Description: Indicates the number of tablets currently hosted or managed by each node. Why is it an FE metric? In StarRocks, the FE maintains the TabletInvertedIndex, which is the authoritative record of where every tablet replica or shard is located. This makes the FE the most reliable source for cluster-wide tablet distribution metrics, especially in Shared-Data (cloud-native) architectures where Compute Nodes (CN) are elastically added or removed. Alternative: Information Schema If you need to see the exact count per node via SQL for a quick check, you can query the

information_schema.be_tablets

table:

sql
SELECT BE_ID, COUNT(TABLET_ID) AS tablet_count 
FROM information_schema.be_tablets 
GROUP BY BE_ID;

Monitoring via BE Endpoint While the BE also exposes metrics (like

tablet_meta_mem_usage

or

tablet_max_compaction_score

), the simple "total count of tablets" is most commonly and reliably scraped from the FE's aggregated view. If you are using Shared-Data, the FE metric

starrocks_fe_tablet_num

is the specific one introduced (in v4.0+) to provide observability for tablet distribution across CN nodes. References • administration/management/monitoring/metrics.md • sql-reference/information_schema/be_tablets.md • administration/management/FE_configuration.md • faq/Deploy_faq.md References • administration/management/monitoring/metrics.md • sql-reference/information_schema/be_tablets.md • faq/Deploy_faq.md References • administration/management/monitoring/metrics.md • sql-reference/information_schema/be_tablets.md • faq/Deploy_faq.md