< Rocky> I have a big limitation for my migration from elast StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> I have a big limitation for my migr...

Minn Fong

07/10/2026, 2:51 PM

@Rocky I have a big limitation for my migration from elastic search to starrocks for my bid request dataset as a DSP. One of the key features that analysts often use is to lookup requests containing a certain deal_id. There is a field called deal_ids and it is easy to lookup in elastic search with the kql

deal_ids: <deal_id>

. This is fast due to the inverted index? But now, deal_ids are in an array<string> data structure in starrocks. Since the way the database is designed is fundamentally different, it costs a lot of cpu resource to parse my data and find records that are relevant to the deal_id being looked up. My dataset is ~5.5 billion records with ~550 columns in a 24 hour timespan. Its TTL is 24 hours. Would using GIN and a varchar column, space separating the deal ids be a significant improvement in terms of lookup?

Rocky

07/10/2026, 2:51 PM

Yes, your intuition is correct. Using a

VARCHAR

column with space-separated IDs and a GIN (Generalized Inverted Index) will provide a significant performance improvement over a raw

ARRAY<STRING>

scan for your use case. In StarRocks,

ARRAY

columns do not currently support secondary indexes like Bitmap, Bloom Filter, or GIN. Queries using

array_contains()

unnest()

on 5.5 billion records will force a full data scan and row-by-row parsing, which is why you are seeing high CPU usage. Recommended Strategy To achieve Elasticsearch-like lookup speeds for your deal IDs, you should: 1. Transform to VARCHAR: Store your

deal_ids

as a space-separated

VARCHAR

STRING

(e.g.,

"deal123 deal456 deal789"

). 2. Create a GIN Index: Apply a GIN index on this column. Since deal IDs are typically alphanumeric, you can use the

english

standard

parser which tokenizes by space and punctuation.

Copy code

sql
    -- 1. Enable GIN in FE (if not already)
    ADMIN SET FRONTEND CONFIG ("enable_experimental_gin" = "true");

    -- 2. Create table with GIN index
    CREATE TABLE bid_requests (
        request_id BIGINT,
        deal_ids_str VARCHAR(65533),
        INDEX idx_deals (deal_ids_str) USING GIN("parser" = "english")
    )
    DUPLICATE KEY(request_id)
    DISTRIBUTED BY HASH(request_id)
    PROPERTIES ("replicated_storage" = "false");

3. Use the
MATCH
Operator: To leverage the index, you must use the

MATCH

predicate instead of

LIKE

Copy code

sql
    -- This will use the GIN index
    SELECT * FROM bid_requests
    WHERE deal_ids_str MATCH 'deal123';

Why this is better * Reduced I/O & CPU: Instead of scanning all 5.5 billion rows to parse array blocks, StarRocks uses the GIN index to pinpoint only the relevant row IDs (similar to the inverted index in ES). * Filter Efficiency: You can verify the acceleration in the Query Profile by checking

GinFilterRows

. If your

deal_id

is specific, the index will filter out >99% of the data before the scan even begins. Implementation Notes * Version requirement: Full-text GIN indexes were introduced in StarRocks v3.0+. Ensure you are on a recent version for the best stability. * Space separation: Ensure the delimiter you choose is recognized by the parser (space is standard for `english`/`standard`). * Variable: Ensure the system variable

enable_gin_filter

is set to

true

(default in newer versions) for the index to be used during execution. If you must keep the

ARRAY

type for other analytical functions, you can store both (a

VARCHAR

for indexed lookups and the

ARRAY

for complex array functions), though this will increase storage footprint. However, for a 24-hour TTL, the storage cost is usually negligible compared to the CPU savings. References • knowledge/usage/best-practice.md • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md

Minn Fong

07/10/2026, 2:53 PM

@Rocky What if my table is already created? I can execute ALTER TABLE ADD COLUMN command but what is the process for adding GIN index for this new column

Rocky

07/10/2026, 2:53 PM

If your table is already created, you can add the GIN index to a new column in two steps. Note that adding an index is an asynchronous operation in StarRocks. Step 1: Add the new VARCHAR column First, add the column that will store your space-separated deal IDs.

Copy code

sql
ALTER TABLE your_table_name 
ADD COLUMN deal_ids_str VARCHAR(65533) DEFAULT "";

Step 2: Add the GIN Index Next, create the GIN index on that column. You must specify the

parser

. For space-separated IDs,

english

standard

is recommended as they tokenize on non-alphanumeric characters.

Copy code

sql
-- 1. Ensure the feature is enabled (FE config)
ADMIN SET FRONTEND CONFIG ("enable_experimental_gin" = "true");

-- 2. Add the index
ALTER TABLE your_table_name 
ADD INDEX idx_deals (deal_ids_str) USING GIN("parser" = "english");

Important Considerations 1. Populating the Data * Duplicate Key Tables: If your table uses the

DUPLICATE KEY

model (typical for logs), you cannot use an

UPDATE

statement to fill the new column from the existing

ARRAY

column. You will need to either: * Modify your ingestion pipeline (Flink/Routine Load) to start populating this new column for all new data. * Since your TTL is only 24 hours, the simplest path is often to just let the new data flow into the new column; the old rows (without the string column) will expire naturally within a day. * Primary Key Tables: If you are using the Primary Key model, you can run:

Copy code

sql
    UPDATE your_table_name SET deal_ids_str = array_to_string(deal_ids, ' ');

2. Storage Property (
replicated_storage
) For StarRocks versions earlier than v4.0, GIN indexes require the table property

replicated_storage

to be set to

false

. You can check this with

SHOW CREATE TABLE

. If it is

true

, you cannot add a GIN index without recreating the table. In v4.0 and later, this is handled automatically. 3. Monitoring the Progress Since this is an asynchronous task, you can monitor the status of the index creation (which involves building the index for existing data) using:

Copy code

sql
SHOW ALTER TABLE COLUMN;

The state will change from

RUNNING

FINISHED

once the index is fully built and ready for use with the

MATCH

operator. References • table_design/indexes/inverted_index.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md • table_design/indexes/inverted_index.md • table_design/indexes/vector_index.md

Minn Fong

07/10/2026, 3:06 PM

@Rocky

Copy code

PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"partition_live_number" = "24",
"replicated_storage" = "true",
"replication_num" = "1"
);

This is my primary key table property. Since replication_num is 1, safe to set replicated storage to false?

Rocky

07/10/2026, 3:06 PM

Yes, it is safe to set
replicated_storage
to
false
since your

replication_num

is 1. When you have only one replica, the concept of "single leader replication" (replicated storage) is effectively moot because there are no secondary replicas to synchronize with. Changing this property will not impact your data availability or consistency in a single-replica setup. However, there are a few critical details regarding your plan to add a GIN index to an existing Primary Key table: 1. Version Dependency for Primary Key Tables Inverted indexes (GIN) were originally limited to Duplicate Key tables. Support for Primary Key tables was introduced in StarRocks v4.0. * If you are on v4.0 or later: You can add a GIN index to your Primary Key table. * If you are on v3.x or earlier: GIN indexes are only supported on Duplicate Key tables. You would need to migrate your data to a Duplicate Key table to use this feature. 2. Is setting it to
false
necessary? The requirement for

replicated_storage = false

is specific to the underlying indexing engine (CLucene) used by GIN: * In v4.0 and later: StarRocks handles this automatically. When you create a GIN index, the system internally disables the

replicated_storage

mechanism for that table. You do not need to manually change it. * In versions < 4.0 (for Duplicate Key tables): You must manually set it to

false

before or during index creation. 3. How to check and update You can check your version and then apply the change if needed:

Copy code

sql
-- Check your StarRocks version
SELECT current_version();

-- If you need to manually change it (usually not required in v4.0+)
ALTER TABLE your_table_name SET ("replicated_storage" = "false");

Recommendation for your 5.5B record dataset: Since your TTL is only 24 hours, if you find you are on a version older than 4.0 (where PK tables don't support GIN), you might consider: 1. Upgrading to v4.0+ to get GIN support on your current PK table. 2. Re-creating the table as a Duplicate Key table. For a DSP bid request dataset that is primarily "append-only" logs with a short TTL, a Duplicate Key table is often more efficient than a Primary Key table and has more mature support for all index types. References • table_design/indexes/inverted_index.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md • table_design/table_types/table_capabilities.md

Minn Fong

07/10/2026, 3:10 PM

@Rocky My deal_ids currently is in comma separated form (golang slice converted to string representation). Is it better to convert it to space separated for GIN lookup performance or comma will perform equally fine

Rocky

07/10/2026, 3:10 PM

Whether you use a comma or a space as a separator, the performance of the GIN (Generalized Inverted Index) lookup will be nearly identical in StarRocks. This is because of how the StarRocks GIN parsers (

english

and

standard

) operate: 1. Both are treated as Delimiters The

english

and

standard

parsers are designed to tokenize text based on punctuation and whitespace. * The
english
parser tokenizes on "any non-alphabetic character." Since both commas and spaces are non-alphabetic, the parser will treat both as delimiters and create separate tokens for your deal IDs. * The
standard
parser uses the Unicode Text Segmentation algorithm, which likewise treats commas and spaces as word breaks. For example, both

"deal1,deal2"

and

"deal1 deal2"

will result in the same index tokens:

deal1

and

deal2

. 2. Important Caveat: Internal ID Characters While comma vs. space doesn't matter, characters inside your deal IDs do. If your deal IDs contain non-alphabetic characters like underscores (

) or hyphens (

), the

english

parser may split a single ID into multiple tokens (e.g.,

DEAL_123

becomes

deal

and

). * Recommendation: Use the standard
parser for deal IDs. It is generally more robust for alphanumeric strings and handles mixed characters better than the basic

english

parser. 3. Lowercase Behavior Be aware that both the

english

and

standard

parsers convert all text to lowercase during indexing. * If your deal IDs are

Deal_A1

, they will be stored in the index as

deal_a1

. * Your

MATCH

query must use the lowercase version to hit the index:

WHERE deal_ids_str MATCH 'deal_a1'

. Summary Checklist * Separator: Use whatever is easiest for your pipeline (comma or space is fine). * Parser: Use

USING GIN("parser" = "standard")

. * Query: Use the

MATCH

operator and ensure the search term is lowercase. * Primary Key Table: Ensure you are on StarRocks v4.0 or later, as that is when GIN support for Primary Key tables was introduced. Example Configuration

Copy code

sql
ALTER TABLE bid_requests 
ADD INDEX idx_deals (deal_ids_str) USING GIN("parser" = "standard");

-- Query using the index
SELECT * FROM bid_requests 
WHERE deal_ids_str MATCH 'my_deal_id_123';

References • table_design/indexes/inverted_index.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md • sql-reference/data-types/semi_structured/Array.md References • sql-reference/data-types/semi_structured/Array.md • table_design/indexes/inverted_index.md

Minn Fong

07/10/2026, 3:13 PM

@Rocky A lot of my deal_ids have dashes (-) in them. MGNI-AP-34366 is an example. Some have underscore (_) too. How should I structure the data now? Would the GIN still be effective?

Rocky

07/10/2026, 3:14 PM

With deal IDs like

MGNI-AP-34366

, you need to be careful with tokenization. Characters like dashes (

) and underscores (

) are considered delimiters by the standard text parsers, which would break your IDs into pieces (e.g.,

mgni

ap

). Since you are using a Primary Key table (which requires StarRocks v4.0+) and have a massive dataset (5.5B records), here is the best way to structure your data and index for performance: Recommended Approach: Built-in GIN with
parser
=
none
If you are on StarRocks v4.1 or later, you should use the Built-in implementation of the inverted index with the

none

parser and

dict_gram_num

. This combination is specifically designed to handle strings with special characters and accelerate substring matches (

LIKE

MATCH

). * Data Format: Stick with your current comma-separated or space-separated string. * How it works: By setting

parser

none

, the index treats each entry (the whole string of IDs) as a single item but then creates an n-gram dictionary index on top of it. This makes searching for a specific ID within that string extremely fast without worrying about dashes or underscores being split. Implementation:

Copy code

sql
-- 1. Ensure experimental GIN is enabled
ADMIN SET FRONTEND CONFIG ("enable_experimental_gin" = "true");

-- 2. Add the index using the BUILTIN implementation (v4.1+)
ALTER TABLE your_table_name 
ADD INDEX idx_deals (deal_ids_str) 
USING GIN (
    "parser" = "none", 
    "imp_lib" = "builtin", 
    "dict_gram_num" = "3"
);

Why this is effective: 1. Exact Matches: Since

parser = none

, the string

MGNI-AP-34366

remains intact. 2. No Delimiter Issues: Dashes and underscores are stored as-is. 3. Fast Substring Search: The

dict_gram_num

creates 3-character "grams" (like

MGN

GNI

NI-

, etc.) which StarRocks uses to jump directly to the correct rows, avoiding a full table scan. 4. Query Syntax: You can use either

LIKE

MATCH

. Both will be accelerated:

Copy code

sql
    SELECT * FROM your_table_name
    WHERE deal_ids_str MATCH '%MGNI-AP-34366%';

If you are on StarRocks v4.0 (but not yet 4.1) The built-in

imp_lib

and

dict_gram_num

were introduced in v4.1. If you are on v4.0, you are limited to the CLucene implementation. * The Problem: In v4.0,

parser = none

does not support n-gram acceleration for

LIKE '%...%'

. It only speeds up exact matches (

). * Workaround: You would have to use

parser = standard

, but your query would need to look for the tokens. Because of the risk of false positives (e.g., a search for

MGNI-AP-34366

might match a row with

AP-MGNI-34366

), you would need to combine

MATCH

with a secondary

LIKE

check:

Copy code

sql
    -- Less efficient workaround for v4.0
    SELECT * FROM your_table_name
    WHERE deal_ids_str MATCH 'mgni-ap-34366'  -- Filters quickly using index
      AND deal_ids_str LIKE '%MGNI-AP-34366%'; -- Refines to ensure exact sequence

Summary Recommendation 1. Check Version: Run

SELECT current_version();

. 2. Use v4.1+: If possible, use v4.1+ to leverage the Built-in GIN index with

parser = none

and

dict_gram_num

. This is the most "future-proof" and performant way to handle IDs with special characters. 3. Casing: Note that

parser = none

is usually case-sensitive. If your deal IDs have mixed casing, ensure your query matches the casing in the data. References • table_design/indexes/inverted_index.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/indexes/Ngram_Bloom_Filter_Index.md References • table_design/indexes/inverted_index.md

Rocky

07/10/2026, 3:14 PM

• table_design/indexes/Ngram_Bloom_Filter_Index.md

Minn Fong

07/10/2026, 3:17 PM

@Rocky COmpare the performance of looking up array values vs using the CLucene based inverted index and the v4.1 built in native inverted index implementation for my deal_ids use case.

Rocky

07/10/2026, 3:17 PM

For a dataset of 5.5 billion records with high-cardinality IDs containing special characters (like

MGNI-AP-34366

), here is the performance comparison for your

deal_ids

lookup: 1. Array Values (
ARRAY<STRING>
) * Performance: Slowest (Scan-based) * How it works: You would use

array_contains(deal_ids, 'MGNI-AP-34366')

. * The Catch: In StarRocks, full-text inverted indexes (GIN) currently support only

CHAR

VARCHAR

, and

STRING

types. They cannot be applied to
ARRAY
columns for element-level acceleration. * Result: To find a deal ID in an array across 5.5B rows, StarRocks must perform a full column scan. At your scale, this will likely be too slow for interactive queries. 2. CLucene-based Inverted Index (Default in v3.x/v4.0) * Performance: Fast (Index-based) * How it works: You store IDs in a comma-separated string and use

WHERE deal_ids_str MATCH 'id'

. * The Catch: * Tokenization: Since your IDs have dashes (

), standard parsers will split

MGNI-AP-34366

into three tokens (

mgni

ap

). A search for the full ID might return false positives if another row has those same three pieces in a different order. * Overhead: CLucene is an external C++ library. While fast, it has higher overhead for communication with the StarRocks engine compared to native implementations. * Shared-Data: It is not supported if you move to a shared-data (Cloud Native) architecture. 3. Built-in Native Inverted Index (v4.1+) * Performance: Highest (Optimized for Substrings) * How it works: Store IDs in a string and use the

builtin

implementation with the

dict_gram_num

parameter. * Why it wins for your case: * N-Gram Acceleration: By setting

"dict_gram_num" = "3"

, StarRocks builds an n-gram dictionary index on top of the inverted index. This allows it to accelerate

MATCH '%MGNI-AP-34366%'

LIKE

queries extremely efficiently, even with dashes and underscores. * Native Execution: It is built directly into the StarRocks storage engine (using bitmapped technology), resulting in lower latency and better CPU efficiency than CLucene. * Flexibility: It handles both shared-nothing and shared-data clusters. ────────── Summary Table Feature | Array Column | CLucene Inverted Index | Built-in Inverted Index (v4.1+) Search Method |

array_contains()

MATCH

MATCH

LIKE

Copy code

sql
ALTER TABLE bid_requests 
ADD INDEX idx_deals (deal_ids_str) 
USING GIN (
    "imp_lib" = "builtin", 
    "parser" = "none", 
    "dict_gram_num" = "3"
);

-- Query using the index
SELECT * FROM bid_requests WHERE deal_ids_str MATCH '%MGNI-AP-34366%';

References • table_design/indexes/inverted_index.md • sql-reference/sql-functions/array-functions/array_contains.md • table_design/indexes/Bitmap_index.md References • sql-reference/data-types/semi_structured/Array.md • table_design/indexes/Bitmap_index.md • knowledge/usage/best-practice.md

Rocky

07/10/2026, 3:17 PM

• loading/Spark-connector-starrocks.md

Open in Slack

Previous Next