http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Is one main limitation of StarRocks...
# questions-and-troubleshooting
m
@Rocky Is one main limitation of StarRocks vs ELasticSearch as an analytical engine that it performs worse for array lookups? For elastic search, because every element has an inverted index, doing array lookups is very fast, getting documents where the array column contains a specified value. Another example is we want counts of records where its array column has a specific value too. I tried GIN but it's not as effective as I hoped for it to be because my array values contain string, so I cant use a simple parser and have to do very inefficient dict_gram_nums.
r
While you are correct that native 
ARRAY
data types in StarRocks do not currently support GIN (inverted) indexing, this is a known design characteristic that can be optimized by using a different modeling pattern. 1. The Limitation In StarRocks, the GIN index is currently restricted to 
CHAR
, 
VARCHAR
, and 
STRING
types. When you use the native 
ARRAY<STRING>
type, lookups like 
array_contains(col, 'value')
rely on vectorized scans rather than an inverted index. For high-cardinality arrays or very large datasets, this will be slower than a search engine that indexes every array element. 2. The High-Performance Pattern: String-Based Arrays To achieve search-engine-like performance for array-style lookups, the recommended pattern in StarRocks is to store your array as a space-separated (or delimiter-separated) string instead of a native 
ARRAY
type. * Data Model: Store 
['apple', 'orange']
as a string 
"apple orange"
. * Indexing: Create a GIN index on this string column using a 
parser
(e.g., 
standard
or 
english
). * Query: Use the 
MATCH
predicate: 
SELECT count(*) FROM table WHERE col MATCH 'apple'
. This approach allows StarRocks to tokenize the string and build an inverted index where each "array element" is a token. This converts the scan into a sub-100ms index lookup, matching the behavior of search engines. 3. Why 
dict_gram_nums
feels inefficient The 
dict_gram_nums
parameter is intended for wildcard and substring queries (like 
MATCH '%abc%'
) on the dictionary of a built-in GIN index. * If you are using 
parser = "none"
and relying on 
dict_gram_nums
, StarRocks is effectively doing an N-gram search over the entire string, which is computationally more expensive and creates a much larger index. * Solution: If your "array elements" are distinct words, switch to 
parser = "standard"
(or 
english
) and ensure your elements are separated by spaces. This allows the index to store exact tokens. You can then use 
MATCH 'exact_value'
without needing N-gram acceleration, as the index will directly point to the relevant rows. Summary Comparison Feature | Elasticsearch | StarRocks (Native Array) | StarRocks (Tokenized String + GIN) Lookup Speed | Very Fast (Inverted Index) | Moderate (Vectorized Scan) | Very Fast (Inverted Index) Analytic Speed | Moderate | Fast (Vectorized) | Very Fast (Vectorized) Index on Elements | Automatic | Not Supported | Supported via 
MATCH
Model Flexibility | High | High | Requires string formatting If your primary goal is finding/counting records where a specific value exists in a set, the Tokenized String + GIN path is the intended way to reach parity with search engines while maintaining the superior analytical performance of the StarRocks vectorized engine. References • table_design/indexes/inverted_index.md • sql-reference/data-types/semi_structured/Array.md • best_practices/query_tuning/schema_tuning.md
m
@Rocky THe issue is my array elements contain dashes '-' and underscores '_'. I can't use english parser and perform exact match
r
You are correct that this is a current architectural trade-off in StarRocks when compared to a search engine like Elasticsearch. In Elasticsearch, every element of an array is treated as a first-class "term" in the inverted index by default. In StarRocks, the 
ARRAY
type is optimized for vectorized analytical processing (scans, aggregations, and transformations) rather than point lookups. 1. Why GIN with 
dict_gram_nums
is failing your case The 
dict_gram_num
parameter is a "dictionary-on-dictionary" optimization. It's meant to accelerate substring queries (e.g., finding 
_rock_
in 
StarRocks
) by breaking unique string values into N-grams. * The Issue: If your array elements are specific IDs or tags (like 
user-123_abc
), the N-gram approach generates a massive amount of metadata for substrings you don't actually care about. * The Parser Issue: As you found, the 
english
and 
standard
parsers tokenize (split) at dashes 
-
and underscores 
_
. This turns 
user-123_abc
into three separate tokens (
user
, 
123
, 
abc
), making an exact match for the full string impossible with a simple 
MATCH
query. 2. High-Performance Alternatives in StarRocks To achieve Elasticsearch-level lookup speeds for elements containing special characters, you should consider these two patterns: A. The "Normalized" Pattern (Highest Performance) Instead of storing elements in a native 
ARRAY
column, unnest the data into a separate detail table or use a Duplicate Key table where each row contains one element. * Schema: 
(record_id, element_string)
* Indexing: Create a GIN index on 
element_string
with 
"parser" = "none"
. * Why this works: When 
parser
is 
none
, the index treats the entire string (including 
-
and 
_
) as a single token. Lookups using 
=
or 
IN
are then pushed down to the inverted index, resulting in sub-100ms response times even on billions of rows. * Query: 
SELECT record_id FROM table WHERE element_string = 'user-123_abc'
B. The "Bitmap" Pattern (For low-to-medium cardinality) If your array elements come from a finite set (e.g., fewer than 1 million unique tags), you can map those strings to integers and store them in a 
BITMAP
column. * Performance: Bitmaps are the fastest way in StarRocks to perform "set contains" queries. * Complexity: Requires maintaining a mapping table (Global Dictionary) to convert your strings to IDs before loading. 3. Current Limitations & Roadmap * Native ARRAY GIN: StarRocks does not currently support building an inverted index directly on the elements of a native 
ARRAY<STRING>
type. The 
array_contains()
function relies on a vectorized scan of the column, which is significantly faster than a traditional row-store scan but slower than an inverted index. * Wait for Whitespace Parser: A 
whitespace
parser (which would solve your dash/underscore issue in string-based arrays) is a frequently requested feature in the community and is on the long-term roadmap for the full-text engine. Summary Recommendation If your primary workload is "finding records where array contains X" and your elements have special characters: 1. Normalize your data model so each array element is a row in a Duplicate Key table. 2. Use GIN with 
parser = "none"
 on that column. 3. This bypasses the N-gram overhead of 
dict_gram_nums
and provides the exact-match, high-speed lookup behavior you are looking for. For updates on native ARRAY indexing, you can follow the StarRocks GitHub Issue #58362 regarding array performance improvements. References • table_design/indexes/inverted_index.md
m
@Rocky But that means dual writing data to a duplicate key table. I have ~5.5 billion records a day with each record on average having 4-5 deals. Expanding this to a (record_id, element_string) approach will be gigantic and lead to lots of unnecessary writes. It doesn't seem feasible
r
You've hit on a core architectural difference between an inverted search engine and a columnar analytical engine. In StarRocks, the 
ARRAY
type is built for vectorized analytical processing (scans and math), while Elasticsearch is built for point lookups (indexing every term). For a dataset of 5.5 billion records/day, you are correct that traditional normalization or inefficient N-gram indexing will not scale. Here is the breakdown of why your current approach is struggling and the "StarRocks-native" ways to solve this without manual dual-writing. 1. Why GIN with 
dict_gram_nums
is failing The 
dict_gram_nums
property in StarRocks' built-in GIN index is designed to accelerate substring/wildcard queries on the dictionary itself. * The Problem: For 5.5B records, breaking every unique string into N-grams creates an astronomical number of index entries. If your elements have high cardinality (e.g., unique IDs), the index size can easily exceed the data size. * The Parser Problem: The 
english
and 
standard
parsers are hardcoded to split on 
-
and 
_
. This is why you can't get an exact match for 
user-123_abc
. Currently, StarRocks does not yet have a 
whitespace
or 
custom
delimiter parser for GIN. 2. Solution A: N-Gram Bloom Filter (No Extra Table) If you want to keep the data in a single table and avoid tokenization issues, the N-Gram Bloom Filter is often more efficient than a GIN index for simple "contains" checks on strings. * Mechanism: It creates a bloom filter of N-grams for the string. It doesn't "tokenize" based on grammar; it just sees the raw bytes. * Modeling: 1. Create a Generated Column that converts your array to a space-separated string: 
ALTER TABLE tbl ADD COLUMN tags_str STRING AS (array_to_string(tags_col, ' '));
2. Add an N-Gram Bloom Filter on that string: 
ALTER TABLE tbl SET ("ngram_bloom_filter_columns" = "tags_str");
* Query: Use 
LIKE
with padded spaces to ensure exact element matching: 
WHERE tags_str LIKE '% user-123_abc %'
* Performance: This allows StarRocks to skip entire data blocks that don't contain those bytes, which is much faster than a full scan and uses significantly less storage than a GIN index. 3. Solution B: Asynchronous Materialized View (Automated Normalization) Since you are worried about the "dual-write" overhead and storage, you should use an Asynchronous Materialized View (MV) instead of a manual duplicate table. * How it works: You write only to your main table. StarRocks automatically manages the "normalization" in the background. * Definition:
Copy code
sql
    CREATE MATERIALIZED VIEW tags_lookup_mv
    REFRESH ASYNC
    PARTITION BY (dt) -- Match your base table's partitioning
    AS
    SELECT record_id, unnest_tag
    FROM base_table, unnest(tags_col) as unnest_tag;
* Indexing: You can then put a Bitmap Index or a GIN Index (parser=none) on the 
unnest_tag
column in the MV. * Benefit: Because 
parser=none
is used, 
-
and 
_
are preserved as a single token. Lookups become extremely fast point-queries. StarRocks can even perform Query Rewrite, so if you query the base table, the optimizer can automatically route the "contains" check to the MV. 4. Comparison for High-Volume (5.5B/day) Strategy | Index Size | Lookup Speed | Write Impact | Handling 
-
and 
_
 Native Array Scan | 0 | Moderate | None | Perfect GIN (dict_gram_nums) | Very Large | Fast | High | Difficult N-Gram Bloom Filter | Small | Fast | Low | Perfect Async MV + Index | Moderate | Very Fast | Background | Perfect Recommendation For your specific volume and character constraints: 1. Try the N-Gram Bloom Filter first. It is the lowest-effort change and handles special characters natively without the tokenization headaches of GIN.
2. If you need sub-100ms counts for specific values, use the Async Materialized View. This offloads the "expansion" work to a background process and provides the inverted-index performance you expect from Elasticsearch without complicating your application logic. References • table_design/indexes/inverted_index.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_MATERIALIZED_VIEW.md • table_design/indexes/Bloomfilter_index.md • using_starrocks/Materialized_view.md References • sql-reference/data-types/semi_structured/JSON.md • using_starrocks/Flat_json.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md
m
@Rocky Can you explain how to implement the bitmap column approach? My deal_ids cardinality is relatively low. ~50k only
r
The Bitmap column approach is the industry-standard way to handle "set membership" queries in StarRocks at your scale (billions of rows). Because your cardinality is low (~50k), this is extremely storage-efficient and performs lookups in constant time (O(1)) rather than scanning arrays. Here is the step-by-step implementation. Step 1: Mapping String IDs to Integers StarRocks 
BITMAP
columns require 
BIGINT
inputs. You have two ways to map your 
deal_id
(with 
-
and 
_
) to integers: * Option A: 
BITMAP_HASH64
(Easiest & Fastest) For a cardinality of 50k, the probability of a collision with a 64-bit hash is practically zero ($10^{-10}$). This requires no extra tables and no manual dictionary maintenance. * Option B: Dictionary Table (100% Exact) If you must have zero risk, create a Primary Key table with an 
AUTO_INCREMENT
column.
Copy code
sql
    CREATE TABLE deal_dict (
        deal_id_str STRING,
        deal_id_int BIGINT AUTO_INCREMENT
    ) PRIMARY KEY (deal_id_str)
    DISTRIBUTED BY HASH(deal_id_str);
Step 2: The Main Table Design Define your main table with a 
BITMAP
column. Use the 
BITMAP_UNION
aggregation type if you want to collapse records, or 
DUPLICATE KEY
if you need to keep every 5.5B records distinct.
Copy code
sql
CREATE TABLE analytical_table (
    record_id BIGINT,
    event_time DATETIME,
    -- Other columns...
    deal_bitmap BITMAP -- Storing the set of deal IDs
) 
DUPLICATE KEY (record_id)
DISTRIBUTED BY HASH(record_id);
Step 3: Loading Data (Converting Array to Bitmap) Since you are loading 5.5B records/day, you want this conversion to happen at ingestion time. Using 
BITMAP_HASH64
(No overhead): If you are loading via 
INSERT INTO ... SELECT
or 
Stream Load
, you can convert your 
ARRAY<STRING>
to a 
BITMAP
like this:
Copy code
sql
INSERT INTO analytical_table
SELECT 
    record_id, 
    event_time,
    -- Convert array elements to hashes and aggregate into one bitmap per row
    (SELECT bitmap_union(bitmap_hash64(unnest_col)) FROM unnest(deal_ids_array) AS unnest_col)
FROM source_load_table;
Step 4: High-Speed Lookups To perform the "contains" check you described (getting documents or counts where the array contains a specific value), use the 
BITMAP_CONTAINS
function. To find the count of records containing a specific deal: 1. Get the integer ID (or hash) for the target string. 2. Query the bitmap.
Copy code
sql
-- Using the hash method
SELECT count(*) 
FROM analytical_table 
WHERE bitmap_contains(deal_bitmap, bitmap_hash64('my-deal-id_123'));
Why this solves your problem: 1. Exact Matching: Since 
bitmap_hash64
or a dictionary table treats the whole string as one token, characters like 
-
and 
_
are preserved. No parser/tokenization issues. 2. Performance: 
BITMAP_CONTAINS
does not scan the array. It performs a bitwise check against the Roaring Bitmap structure, which is the same technology Elasticsearch uses internally for its bitsets. 3. Storage: For 4-5 deals per record, the 
BITMAP
column will only add a few dozen bytes per row, significantly less than storing raw strings or a giant GIN index. 4. Count Acceleration: StarRocks is highly optimized for 
BITMAP_UNION_COUNT(deal_bitmap)
, allowing you to perform complex "How many unique deals did these 1B users see?" queries in seconds. Recommendation: For 50k cardinality, use Option A (
BITMAP_HASH64
). It eliminates the complexity of managing a 5.5B-row join to a dictionary table while giving you the sub-second lookup performance you need. References • sql-reference/sql-functions/bitmap-functions/bitmap_contains.md • using_starrocks/distinct_values/Using_bitmap.md
m
@Rocky This means I must maintain my deal_id bitmap dictionary to always be in sync? I must know the finite set mapping. If there is a stray deal not stored in my internal DB (coming from SSP), it will be unmapped, hence set membership via bitmap wont work?
r
You are correct that maintaining a manual dictionary for 5.5 billion records a day is an operational nightmare. However, the 
BITMAP_HASH64
 approach is specifically designed to solve this without needing a dictionary or "syncing" anything. 1. How 
BITMAP_HASH64
handles "Stray" IDs Unlike a dictionary table (which maps 
string -> ID
), 
BITMAP_HASH64
is a deterministic mathematical function. * No Sync Needed: It does not look up a value in a database. It calculates a 64-bit integer directly from the bytes of your string (including 
-
and 
_
). * Stray Deals: If a new deal ID arrives from an SSP that you've never seen before, the function will hash it to a specific 64-bit integer. When you query for that "stray" ID later using the same function, it will result in the same integer. * Consistency: Because the hash is deterministic, 
BITMAP_HASH64('deal-123')
will always equal the same value, whether it's during ingestion or 5 years from now during a query. 2. Implementation with No Dictionary You only need your main table. No extra database or lookup table is required. The Ingestion (Duplicate Key Table):
Copy code
sql
INSERT INTO analytical_table
SELECT 
    record_id, 
    -- We unnest the array, hash each string, and aggregate them into one bitmap per row
    (SELECT bitmap_union(bitmap_hash64(unnest_id)) FROM unnest(deal_ids_array) AS unnest_id)
FROM raw_data_stream;
The Query:
Copy code
sql
-- This query works instantly for ANY deal_id, even ones you didn't know existed
SELECT count(*) 
FROM analytical_table 
WHERE bitmap_contains(deal_bitmap, bitmap_hash64('new-ssp-deal_999'));
3. Will there be collisions? With a cardinality of 50k unique deal IDs, the risk of two different strings hashing to the same 64-bit integer is mathematically negligible. * A 64-bit space has $1.8 \times 10^{19}$ possible values. * With 50,000 values, the probability of a collision is approximately $1$ in $10^{10}$ (one in 10 billion). * Even if a collision occurred, it would only mean a query for 
deal-A
might occasionally include a record for 
deal-B
. For analytical use cases, this is usually acceptable given the massive performance and simplicity gains. 4. Why this is the best fit for 5.5B records/day 1. Stateless: You don't have to manage state, lookups, or "unknown" values. 2. Special Characters: Because it hashes the raw string, dashes and underscores are irrelevant—they are just part of the input bytes. 3. Storage: It stores a compressed Roaring Bitmap. For 4-5 deals per record, this is incredibly compact. 4. Speed: 
bitmap_contains
is a bitwise operation. It is significantly faster than any string-based 
array_contains
or 
LIKE
query at your scale. Summary: Skip the dictionary table entirely. Use 
BITMAP_HASH64
to convert your array into a bitmap column during load. It handles "stray" IDs automatically and preserves exact matches for strings containing special characters. References • sql-reference/sql-functions/bitmap-functions/bitmap_hash64.md • sql-reference/sql-functions/bitmap-functions/bitmap_contains.md • using_starrocks/distinct_values/Using_bitmap.md • sql-reference/data-types/other-data-types/BITMAP.md References • using_starrocks/distinct_values/Using_bitmap.md • sql-reference/sql-functions/bitmap-functions/bitmap_hash64.md
2 Views
Open in Slack
PreviousNext
Powered by