@Rocky What data types does the beta inverted index feature in starrocks 4.1 support? Is it only varchar or array varchar as well? I have a big use case where I need fast array varchar lookups

In StarRocks 4.1, the built-in inverted index feature (which is in beta) currently supports scalar string types only. According to the official documentation, the supported data types for an inverted index are: * VARCHAR * STRING * CHAR Key Details: * No ARRAY Support for Inverted Index: As of version 4.1, there is no documented support for creating a full-text inverted index (GIN) on

ARRAY<VARCHAR>

columns. The index is designed to tokenize and search within individual string fields using the

MATCH

predicate. * Built-in vs. CLucene: Starting with v4.1, StarRocks provides a native built-in implementation (based on bitmap indexing) in addition to the traditional CLucene-based one. The built-in version is required for shared-data (cloud-native) clusters. * Alternatives for Array Lookups: While the inverted index doesn't support arrays, StarRocks 4.1 includes other optimizations for `ARRAY<VARCHAR>`: * Low-Cardinality Dictionary Optimization: For

ARRAY<VARCHAR>

, StarRocks can apply dictionary decoding to speed up filtering if the column has low cardinality (enabled via the

enable_low_card_dictionary_optimize_on_array_varchar

session variable). * Native Array Functions: You can use functions like

array_contains()

or

array_overlap()

for lookups. These are optimized for performance but do not utilize the full-text inverted index mechanism. If your use case relies on full-text tokenization within array elements (e.g., searching for a word inside any string in an array), you may currently need to flatten the data or store the array as a single space-separated

STRING

column to leverage the inverted index. References • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md

@Rocky So inverted index would work with LIKE operands? better than array_contains?/

In StarRocks 4.1, the inverted index can work with

LIKE

operands, but only under specific configuration and data type constraints. It behaves differently than

array_contains

and is generally much faster for keyword lookups if you can structure your data to use it. 1. Does the Inverted Index work with

LIKE

? Yes, but with caveats: * When `'parser' = 'none'`:

LIKE

is fully supported and accelerated by the inverted index. In this mode,

LIKE

and

MATCH

are semantically equivalent. Both must follow the format

WHERE col LIKE '%keyword%'

. * When a Tokenizer is used (e.g.,

'parser' = 'standard'

): Only the

MATCH

predicate is officially supported for full-text acceleration. While

LIKE

might still work as a standard SQL operator, it will likely not utilize the inverted index, leading to a full scan. * Restriction: Both

LIKE

and

MATCH

only support string literals for acceleration; expressions are not supported. 2. Is it better than

array_contains

? For performance, yes, but they are fundamentally different: *

array_contains

Performance: This function operates on

ARRAY<VARCHAR>

columns. Currently, inverted indexes do not support ARRAY types. Therefore,

array_contains

cannot be accelerated by an inverted index. It relies on scanning the data (though it can be optimized by low-cardinality dictionary decoding or ZoneMap indexes if the conditions allow). * Inverted Index Performance: Because the inverted index (based on bitmap/CLucene) maps keywords directly to row IDs, a

MATCH

or

LIKE

query on an indexed

VARCHAR

column will be significantly faster than an

array_contains

scan on a large dataset. Summary Comparison Feature |

array_contains

| Inverted Index (

MATCH

/

LIKE

) Supported Data Type |

ARRAY<VARCHAR>

|

VARCHAR

,

STRING

,

CHAR

Index Acceleration | None (uses scans/ZoneMap) | Inverted Index (Bitmap/CLucene) Typical Use Case | Set membership in a column | High-performance keyword search Speed (Large Data) | Slower (Scanning) | Faster (O(1) or O(log N) lookup) Recommendation for your Use Case Since you need fast array lookups and the inverted index doesn't support arrays: 1. If the array elements are distinct "tags" or "keywords": Consider flattening the array into a single

STRING

column with space-separated values. Create an inverted index on this string column and use

WHERE col MATCH 'your_tag'

. This will likely give you the performance boost you are looking for. 2. If you must use `ARRAY`: Ensure you are on v4.1 and check if your data qualifies for "Low-Cardinality Dictionary Optimization" by setting

enable_low_card_dictionary_optimize_on_array_varchar = true

. References • table_design/indexes/inverted_index.md • sql-reference/sql-functions/array-functions/array_contains.md • best_practices/query_tuning/schema_tuning.md References • sql-reference/sql-functions/array-functions/array_contains_all.md • sql-reference/sql-functions/array-functions/array_contains_seq.md • table_design/indexes/indexes.md • sql-reference/sql-functions/array-functions/array_contains.md

@Rocky Does it work with comma separate values?

The inverted index feature in StarRocks 4.1 does work effectively with comma-separated values (CSV), provided you use the correct parser. This is actually a recommended way to achieve "array-like" lookup performance for types like

ARRAY<VARCHAR>

which are not yet supported by the inverted index. How it works with CSV To use the inverted index for comma-separated strings, you should use the

english

or

standard

parser: 1. Tokenization: *

parser

=

english

: This parser tokenizes text at any non-alphabetic character. Since a comma (

,

) is non-alphabetic, a string like

"apple,banana,cherry"

is automatically broken into three separate index entries:

apple

,

banana

, and

cherry

. *

parser

=

standard

: This uses the Unicode Text Segmentation algorithm, which also treats commas as delimiters in most contexts. 2. Querying: Once indexed, you can search for individual items using the

MATCH

predicates: *

WHERE col MATCH 'apple'

— Finds rows containing "apple" anywhere in the CSV list. _

WHERE col MATCH_ANY 'apple, banana'

— Finds rows containing _either* "apple" or "banana". _

WHERE col MATCH_ALL 'apple, banana'

— Finds rows containing _both* tags. Comparison to Array Lookups For your specific use case of fast

ARRAY<VARCHAR>

lookups: * Performance: Using a

VARCHAR

column with a comma-separated list and an inverted index is significantly faster than using

array_contains

on an

ARRAY

column. The inverted index allows StarRocks to jump directly to the relevant row IDs, whereas

array_contains

typically requires a scan (unless dictionary optimization applies). * Recommendation: If your primary goal is search speed and your array elements are keywords/tags, flattening your arrays into a single comma-separated

VARCHAR

or

STRING

column and adding an inverted index is a high-performance alternative. Example Setup

sql
-- 1. Enable the experimental feature
ADMIN SET FRONTEND CONFIG ("enable_experimental_gin" = "true");

-- 2. Create table with a CSV string column and inverted index
CREATE TABLE tags_table (
    id BIGINT,
    tags_csv VARCHAR(65533),
    INDEX idx_tags (tags_csv) USING GIN("parser" = "english")
) 
DISTRIBUTED BY HASH(id);

-- 3. Fast lookup (uses the index)
SELECT * FROM tags_table WHERE tags_csv MATCH 'search_term';

Note: If you are using a shared-data (cloud-native) cluster in v4.1, remember to specify

"imp_lib" = "builtin"

in your index properties, as the older CLucene-based implementation is only supported for shared-nothing (Local Storage) clusters. References • table_design/indexes/inverted_index.md • sql-reference/sql-functions/array-functions/array_contains.md References • table_design/indexes/inverted_index.md

@Rocky WHat is the cost of building the inverted index? Is it more computationally intensive during ingest itme?

Building an inverted index in StarRocks 4.1 comes with a trade-off: you gain significant query performance for keyword lookups (like your CSV/array use case), but at the expense of higher resource consumption during data ingestion. 1. Ingestion Performance (CPU & Memory) Building an inverted index is more computationally intensive than standard columnar writes. During the load process (Stream Load, Broker Load, etc.), the system must perform additional steps: * Tokenization: The text is parsed into individual terms (e.g., splitting a comma-separated string into words). This is a CPU-bound task. * Sorting & Dictionary Building: The terms must be sorted and mapped to row IDs. * Memory Pressure: The indexing process requires extra memory to hold term dictionaries and posting lists before they are flushed to disk. You may see higher memory usage on BE (Backend) nodes during heavy ingestion. 2. Write Amplification (Storage Cost) Inverted indexes are "storage-heavy" compared to simple ZoneMap or Bloom Filter indexes. * Disk Space: Depending on the data cardinality and the chosen parser, the index size can be substantial. In practice, it often adds 30% to 50% (or more) to the total storage size of the indexed column. * Built-in Index & N-Grams: If you use the new built-in index (v4.1) with the

dict_gram_num

parameter enabled, the storage cost increases further. A smaller

dict_gram_num

(like

2

) creates many more index entries to speed up wildcard searches, significantly increasing the index's disk footprint. 3. Impact on Ingestion Throughput Because of the extra CPU work, you will likely see a decrease in maximum ingestion throughput (MB/s or rows/s). If your system is already CPU-bound during loads, adding an inverted index could increase ingestion latency. Summary of Costs Cost Factor | Impact | Why? CPU Usage | High | Tokenization and sorting of terms during load. Memory | Moderate | Storing index structures in the memtable before flushing. Disk Space | High | Significant write amplification (often 30%+ of data size). Throughput | Decreased | The additional processing overhead slows down the write path. Recommendation for your Use Case: Since you have a "big use case" for fast lookups: 1. Only index columns you actually search on: Do not add inverted indexes to every string column. 2. Use the

builtin

implementation: For StarRocks 4.1, the built-in index is more tightly integrated and required for shared-data clusters. 3. Monitor with Query Profile: Use

GinFilterRows

and

GinFilter

in the query profile to verify if the index is actually being used and providing enough benefit to justify the ingestion cost. 4. Test Ingestion Impact: Run a benchmark load with and without the index to quantify the throughput drop for your specific hardware and data. References • table_design/indexes/inverted_index.md • administration/management/monitoring/metrics.md • best_practices/query_tuning/schema_tuning.md References • table_design/indexes/inverted_index.md • administration/management/monitoring/metrics.md