@Samuel Friedman any insights you can share from your POC with StarRocks as a data warehouse? I am new to data warehouse concepts itself , we have a need for real-time analytics, right now we are using SR as query engine and iceberg for lakehouse and our queries are taking more time than the SLA, so looking out for options to use SR as warehouse, any insights you can share is much appreciated

hey @Radhika Pothukuchi - Here are a few tips: (1) Cache aggressively if you have the local SSD space for it on your CNs: Before making big changes (moving your storage), perhaps just see if you can simply cache everything from the lake? We’re using shared data on s3 - so basically we are laked. The key for us has been to make sure important data is cached:

SUBMIT TASK cache_important_table
SCHEDULE EVERY(INTERVAL 1 HOUR) AS
CACHE SELECT *
FROM dbname.important_table
PROPERTIES("verbose"="true");

One table we cache has 100mn rows x 55 columns. This isn’t very ‘big’ and it’s no problem to cache the entire thing. I think our cache for that table is less than 15gb (on each CN). Aggregates and spot queries are very fast when cached. Like 0.1 seconds. (2) Use bloom_filter_columns We also use the

bloom_filter_columns

property on columns that need sub-second spot query results but have high cardinality:

CREATE TABLE
...
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true", 
    "compression" = "LZ4",
    "bloom_filter_columns" = "parent_id, email, _record_lookup_concat, order_date"
);

(3) Use EXPLAIN ANALYZE with Claude/GPT When a query isn’t performant, we’ve run EXPLAIN ANALYZE and fed the results to an LLM. The output from EXPLAIN is fairly readable, but it can feel intimidating after a long day- so- ChatGPT 5 has been very effective at translating the EXPLAIN output and recommending concrete actions, such as: (4) Watch out for data type mis-matches in WHERE/JOIN conditions Starrocks is lenient when you say something like:

WHERE order_date = '2025-11-02'

the above work fine. But SR will perform much better if you do

WHERE order_date = CAST('2025-11-02' AS date)

(5) Network latency matters We’ve found it’s important for our realtime application(s) to sit “next to” starrocks. For instance, even though we had a Web application in AWS us-east-1… and Starrocks in us-east-1… they were in different VPCs. Between the VPCs was still something like 250-300ms in latency. We cut that to effectively zero with a direct route between the VPCs. (Or put SR in the same VPC as your app.) Question RE caching for you: For the lookups you need to feel ‘real time’, what’s the biggest table and how many rows x columns does it have? And about how many records are added daily?

Thanks a lot @Samuel Friedman for the detailed tips, looks promising. We would like to try optimizing lakehouse approach too before deciding on internal storage. Caching is one area we still need to make more effort as you mentioned like aggressive caching. We are expecting around 500 million row dataset in each reporting table, about 500 users at a given point of time fetching data from these tables. Incremental could be around 40k records.

Depending on how wide those tables are, you may be able to just cache everything. Or, if you know the query patterns, effectively limit the cache to what is used most. I really like the SR cache concept- simply write a query, and then put the

CACHE

keyword in front of it - and schedule it if you like.