http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I got a use case where I store hot ...
# questions-and-troubleshooting
e
@Rocky I got a use case where I store hot data in starRocks native tables, now if lets say for the last week I got there 10 millions rows, how can I make queries fast -> queries that are by time for the last 7 days but have order by what ever columns the user chooses ? the table is primary key -> since redpanada connect ingests data to it from a kafka topic and we cant have duplicates how can we achieve sub second queries (need to return like results for page one, so lets say 100 rows)
r
To achieve sub-second queries on a Primary Key table with ~10 million rows of hot data, you should focus on three areas: physical data layout, decoupling sort keys, and Top-N optimization. At 10 million rows, StarRocks can easily handle "arbitrary" 
ORDER BY
clauses in sub-second time, provided you prune the data effectively using the time filter. 1. Effective Partitioning Since your queries always filter by the "last 7 days," you must use Range Partitioning on your time column. This ensures that the storage engine only scans the relevant 7 days of data, ignoring all historical data. * Action: Define your table with 
PARTITION BY date_trunc('day', your_time_column)
. * Result: The query engine will only hit the tablets containing the last 7 days, reducing the 10M rows to an even smaller set of "active" data. 2. Decouple the Sort Key from the Primary Key In StarRocks v3.0+, Primary Key tables allow you to specify a Sort Key (
ORDER BY
) that is different from the Primary Key. * Crucial Step: Put your time column first in the 
ORDER BY
clause of the table definition. * Why? This clusters the data physically by time. When you filter for the last 7 days, StarRocks can use the Prefix Index and Zone Maps to perform a very narrow range scan, reading only the specific blocks needed. * Example:
Copy code
sql
    CREATE TABLE hot_data (
        event_id BIGINT NOT NULL,
        event_time DATETIME NOT NULL,
        user_id INT,
        category VARCHAR(65533),
        price DECIMAL(10, 2)
    )
    PRIMARY KEY (event_id)
    PARTITION BY date_trunc('day', event_time)
    DISTRIBUTED BY HASH(event_id)
    ORDER BY (event_time, event_id) -- 'event_time' first for pruning
    PROPERTIES ("enable_persistent_index" = "true");
3. Take Advantage of Top-N Optimization When a user chooses an arbitrary column for 
ORDER BY
(e.g., 
ORDER BY price DESC LIMIT 100
), StarRocks uses a Top-N operator. * How it works: Instead of sorting all 10 million rows, each parallel execution thread maintains a small heap of 100 rows. This is extremely efficient and memory-light. * Performance: Sorting 10 million rows (especially if filtered by a partition) to find the top 100 usually takes well under 100-200ms on a standard StarRocks cluster because the data is processed in a vectorized manner. 4. Use Inverted Indexes (v3.3+) If your "arbitrary columns" are also used for filtering (e.g., 
WHERE category = 'electronics' AND event_time > ... ORDER BY price
), consider adding an Inverted Index on those columns. * Inverted indexes allow StarRocks to quickly identify the row IDs that match the filter without scanning the entire 7-day partition. 5. Pagination Best Practices For "Page 1" (first 100 rows), 
ORDER BY ... LIMIT 100
is optimal. * If users navigate to deep pages (e.g., Page 1000), performance can degrade. For deep pagination, it is better to use a "seek" method (e.g., 
WHERE last_value_from_prev_page > ...
) rather than a high 
OFFSET
. Summary Recommendation 1. Partition by day on your time column. 2. Set your Sort Key (
ORDER BY
in table creation) to start with the time column. 3. Ensure Persistent Index is enabled (
"enable_persistent_index" = "true"
) to keep Redpanda ingestion fast without impacting query memory. 4. Standard SQL 
ORDER BY any_column LIMIT 100
will naturally hit the Top-N optimization and should meet your sub-second requirement easily for 10M rows. References • best_practices/table_clustering.md • table_design/indexes/Bitmap_index.md
e
@Rocky you said "Set your Sort Key (
ORDER BY
in table creation) to start with the time column." is it ok if it starts with the user and then the time ? since queries are for a certain user also you said: Standard SQL 
ORDER BY any_column LIMIT 100
will naturally hit the Top-N optimization and should meet your sub-second requirement easily for 10M rows. -> why does it hit it ? do I need to do something for it to happen ? also Use Inverted Indexes (v3.3+) - > why does Inverted Indexes help ? im using starRocks 4.0.10
r
Yes, it is absolutely fine to put the user first. Here is the detailed breakdown of how to structure this for your 10M row table in StarRocks 4.0.10. 1. Sort Key Order: 
(user_id, event_time)
vs 
(event_time, user_id)
 Since your queries are typically for a specific user, you should definitely put 
user_id
first in your 
ORDER BY
(sort key) definition. * Why 
(user_id, event_time)
is better for you: StarRocks builds a Prefix Index based on the sort key. If 
user_id
is first, StarRocks can jump directly to that user's data on disk. If 
event_time
is second, the data for that specific user is already physically sorted by time, making the 7-day range filter extremely fast. * The Trade-off: * User-First: Best for 
WHERE user_id = X AND event_time > Y
. * Time-First: Best for global queries like 
WHERE event_time > Y
(across all users). * Recommendation: Use 
ORDER BY (user_id, event_time)
. Since you only have 10M rows, even a global 7-day query will still be very fast because of the Range Partitioning on the time column. 2. Top-N Optimization: Why it "just works" You don't need to do anything special to enable Top-N optimization; it is an internal execution strategy triggered by the 
ORDER BY ... LIMIT N
pattern. * How it works: Without a 
LIMIT
, the database would have to sort all 10 million rows (a "Full Sort"). With a 
LIMIT 100
, StarRocks uses a Top-N operator. Instead of sorting everything, each parallel thread keeps a "min-heap" (or max-heap) of only the 100 best rows it has seen so far. * Runtime Filter: In StarRocks 4.0, a 
TopN Runtime Filter
is automatically generated. As the engine finds the "top 100" rows, it knows the "worst" value of that top 100. It pushes this value down to the scan layer to discard other data blocks that couldn't possibly make it into the top 100. * Memory Efficiency: This keeps memory usage extremely low, even if you are sorting by a column that isn't part of the sort key. 3. Why Inverted Indexes help (v4.0.10) Inverted indexes (supported for Primary Key tables starting in v4.0) primarily help with the filtering (
WHERE
) part of your query, not the ordering. * The "Needle in a Haystack" problem: If you add a filter that isn't in your sort key (e.g., 
WHERE status = 'failed'
), StarRocks would normally have to scan the entire 7-day partition to find matching rows. * How Inverted Indexes solve it: They work like a book index. They tell StarRocks exactly which Row IDs contain the value 
'failed'
. * Performance Impact: By using the index to find the matching rows first, the engine only has to pull the 
ORDER BY
column values for those specific rows to perform the Top-N sort. This turns a "scan 10M rows" operation into a "look up 5k rows and sort them" operation. Final Summary for your Setup (v4.0.10)
Copy code
sql
CREATE TABLE your_table (
    user_id BIGINT NOT NULL,
    event_time DATETIME NOT NULL,
    ...
)
PRIMARY KEY (pk_id)
PARTITION BY date_trunc('day', event_time) -- Keep the hot 7 days pruned
DISTRIBUTED BY HASH(pk_id)                 -- Matches Redpanda/Kafka logic
ORDER BY (user_id, event_time)             -- Optimized for user-specific lookups
PROPERTIES (
    "enable_persistent_index" = "true"      -- Essential for PK table performance
);

-- Add this if you have filters on other columns
ALTER TABLE your_table ADD INDEX idx_status (status) USING INVERTED;
With 10M rows, a query like: 
SELECT * FROM your_table WHERE user_id = 123 AND event_time > now() - interval 7 day ORDER BY some_other_col LIMIT 100;
should easily run in less than 50ms. References • table_design/indexes/inverted_index.md • best_practices/query_tuning/schema_tuning.md • best_practices/table_clustering.md
e
@Rocky if I run this on 1 be -> still I get sub second results ?
r
Yes, even on 1 Backend (BE), you can absolutely achieve sub-second results for this use case. For a dataset of 10 million rows, StarRocks is extremely efficient. In fact, 10 million rows is considered a "small" dataset for StarRocks' vectorized engine, which is designed to handle billions. Here is why it will stay fast on a single node and what to keep in mind: 1. High Throughput per Core StarRocks typically processes between 10 million to 100 million rows per second per CPU core. * If your query filters for a specific user and the last 7 days, you are likely only scanning a few thousand rows. This will finish in milliseconds. * Even if you do a "global" query (all users) for the last 7 days and it has to scan 2 million rows, a single 16-core BE can process that data almost instantly. 2. Top-N on 1 BE The Top-N optimization (for your 
ORDER BY ... LIMIT 100
) works perfectly on a single node. * Because all the data for that partition is local to that one BE, there is zero network "shuffle" or "merge" overhead between nodes. * The BE will use a vectorized heap sort to find the top 100 rows. For 10M rows total, even without an index on the sort column, this is a very "cheap" operation for the CPU. 3. Hardware Requirements for 1 BE To ensure you get that sub-second performance on a single node, make sure your BE meets these criteria: * AVX2 Support: Ensure your CPU supports the AVX2 instruction set (most modern CPUs do). StarRocks uses this for SIMD vectorization, which is what makes the scanning and sorting so fast. * SSD/NVMe: Since you are using a Primary Key table with a Persistent Index, disk I/O is important. SSDs will ensure that the index lookups and data writes from Redpanda don't block your queries. * Memory: For 10M rows, you don't need a massive amount of RAM. A BE with 16GB–32GB of RAM is more than enough for this data volume and concurrent ingestion. 4. Primary Key Table Advantage Since you are ingesting from Redpanda/Kafka, the Primary Key table is the right choice. * In StarRocks 4.0, the Persistent Index (
"enable_persistent_index" = "true"
) keeps the primary key index on disk (with a small cache in RAM). * This prevents the "memory explosion" that often happens in other databases when you have high-frequency upserts, keeping the node stable even if it's the only one in the cluster. Summary of your "Sub-Second" Query Path: 1. FE (Frontend) receives the query and prunes partitions (only looks at the last 7 days). 2. BE (Backend) uses the Sort Key to jump to the 
user_id
range. 3. Vectorized Engine scans the relevant rows and uses Top-N to pick the 100 rows for the requested column. 4. Result is returned in ~20ms - 200ms. Pro-tip for 1 BE: If you notice latency increasing as you add more data, check your Tablet Count. For a single BE, you don't need hundreds of tablets. Aim for about 8–16 tablets per partition to match the number of CPU cores on your machine. References • best_practices/primarykey_table.md • best_practices/table_clustering.md
e
@Rocky if i got 50 columns that are varchar(65533) and 1 column that is a big nested struct -> what you said is still true ?
r
The answer is still yes, but with a few important technical caveats. When you move from a few narrow columns to 50 wide 
VARCHAR(65533)
columns and a large nested 
STRUCT
, you are shifting from a "light" query to a "heavy I/O" query. To keep it sub-second on a single Backend (BE) for 10M rows, here is how StarRocks 4.0.10 handles this and what you should do: 1. Late Materialization (The "Secret Sauce") StarRocks uses a technique called Late Materialization. Even if your table is very wide, the engine does not read all 50 columns and the 
STRUCT
immediately. * How it helps you: It first scans only the columns needed for your 
WHERE
clause (time/user) and your 
ORDER BY
clause. * The Result: It identifies the "Top 100" Row IDs first. Only after it knows exactly which 100 rows are the winners does it go back to disk to fetch the 50 wide `VARCHAR`s and the 
STRUCT
for just those 100 rows. * Performance: This prevents the BE from loading gigabytes of data into memory for rows that will eventually be discarded by the 
LIMIT 100
. 2. Columnar vs. Row-Column Hybrid Storage Since you are using StarRocks 4.0, you have access to a powerful new feature: Row-Column Hybrid Storage. * The Problem: In a standard columnar format, fetching "all columns" for 100 rows (a 
SELECT *
) requires many small random reads across 50 different files. * The Solution: If your query pattern is always "get the first 100 rows with all details," you can enable the Row Store prefix. This stores a copy of the whole row together. * Action: Add 
"store_row_column" = "true"
to your table properties. This makes 
SELECT *
significantly faster for small 
LIMIT
queries at the cost of some extra disk space. 3. Nested STRUCT Performance In v4.0, StarRocks has optimized the processing of complex types. * The 
STRUCT
column is stored in a specialized way that allows StarRocks to skip it entirely during the filtering phase. * However, 
STRUCT
data is usually large. If you don't need the whole struct in every query, use 
struct_column.field_name
to only pull the sub-field you need. This drastically reduces I/O. 4. Memory Warning: 
VARCHAR(65533)
 Even though StarRocks only stores the actual string length on disk, it may pre-allocate memory based on the defined length during certain execution phases (like complex joins or large sorts). * Risk: If you have 50 columns all defined as 
65533
, a query that forces a massive sort in memory could technically hit memory limits if Top-N optimization isn't perfectly applied. * Advice: If a column will only ever hold 200 characters, define it as 
VARCHAR(200)
. It won't change the disk size, but it makes the execution engine much safer and more efficient with RAM. 5. Summary of Recommended Table Definition for v4.0.10
Copy code
sql
CREATE TABLE your_table (
    user_id BIGINT NOT NULL,
    event_time DATETIME NOT NULL,
    big_data_struct STRUCT<...>, 
    -- 50 other wide varchars
)
PRIMARY KEY (pk_id)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(pk_id)
ORDER BY (user_id, event_time)
PROPERTIES (
    "enable_persistent_index" = "true",
    "store_row_column" = "true" -- Highly recommended for SELECT * with LIMIT 100
);
Conclusion for 1 BE: If you use Late Materialization (default) and Row-Column Hybrid Storage (
store_row_column
), a query for 100 rows out of 10M will still be sub-second because the engine only does heavy lifting for the final 100 rows, not the millions it filters through-put of the entire 10 million. References • administration/management/BE_configuration.md • faq/Sql_faq.md • table_design/indexes/Bitmap_index.md • best_practices/query_tuning/schema_tuning.md
e
@Rocky "In my profile, late materialization only defers the STRUCT column. The 12 VARCHAR columns are still read for all 1M rows (DictDecodeCount: 9.8M). My query sorts by 
eventstarttime DESC, eventuid DESC
— so only those 2 columns are needed for sort. Why aren't the other VARCHARs deferred until after the Top-N identifies the 101 winners? Is the LEFT JOIN preventing full late materialization?"
r
You are seeing exactly how StarRocks differentiates between "Heavy" columns (like 
STRUCT
) and "Standard" columns (like 
VARCHAR
). In StarRocks 4.0.10, there are actually two different mechanisms at play, and your 
LEFT JOIN
is likely the reason the 
VARCHAR
columns are being pulled in early. Here is why your 
STRUCT
is deferred but your `VARCHAR`s are not, and how to fix it: 1. The "Ratio" Problem (BE Config) StarRocks uses two different internal thresholds to decide when to use late materialization at the Scan level: * 
metric_late_materialization_ratio
(Default: 1000): This applies to complex types like 
STRUCT
, 
JSON
, and 
ARRAY
. A value of 1000 means "Always use late materialization" regardless of how many rows you are filtering. This is why your 
STRUCT
is deferred. * 
late_materialization_ratio
(Default: 10): This applies to standard types like 
VARCHAR
. A value of 10 is very conservative; it only triggers late materialization if your 
WHERE
clause is highly selective (e.g., filtering out 99% of the data). Since you are likely scanning a 7-day window that contains most of the data in those partitions, the engine decides it's "cheaper" to just read the 
VARCHAR
columns upfront rather than doing two separate I/O passes. 2. The 
LEFT JOIN
Payload When a 
JOIN
is involved, the query planner has to decide when to "stitch" the row together. * By default, StarRocks materializes all columns needed for the query output before the join so they can be passed through the join operator as a complete "chunk." * If your 
ORDER BY ... LIMIT
is happening after the join in the execution plan, the scan node will read those 12 
VARCHAR
columns for every row that qualifies for the join, leading to that high 
DictDecodeCount
. 3. How to fix it (v4.0.10) You can force the engine to be more aggressive with late materialization using these two settings: A. Enable Join Late Materialization (Session Variable) This is the most likely fix for your scenario. It tells the planner to only carry the join keys through the join and fetch the "payload" columns (your 12 varchars) only for the rows that actually match.
Copy code
sql
SET join_late_materialization = true;
B. Check for Top-N Pushdown Run 
EXPLAIN
on your query. Look for the 
TOP-N
operator. * Good: If 
TOP-N
is nested inside the 
OLAP_SCAN_NODE
or immediately above it (before the Join), the 
VARCHAR
columns should be deferred. * Bad: If 
TOP-N
is at the very top of the plan (after the Join), the engine is joining 1M rows before sorting them. Why would Top-N pushdown fail? If you have a 
WHERE
clause on the Right table of your 
LEFT JOIN
, StarRocks cannot push the 
LIMIT
down to the Left table because a row might be in the "Top 100" but get filtered out by the join condition. 4. Summary Recommendation To get those 12 
VARCHAR
columns to defer like the 
STRUCT
, try these session variables: 1. 
SET join_late_materialization = true;
 (Enables deferring for joins). 2. Check your 
WHERE
clause: Ensure you aren't filtering on the right-side table in a way that prevents Top-N pushdown. 3. If you have access to BE configs: (Admin only) Increasing 
late_materialization_ratio
to 
100
or 
1000
will force the Scan node to defer those `VARCHAR`s just like it does for the 
STRUCT
. Note on `DictDecodeCount`: This metric confirms the engine is performing "Dictionary Decoding" for those strings. If you see 9.8M, it means the engine is preparing those strings for the Join operator. Turning on 
join_late_materialization
should drop this number significantly. References • administration/management/BE_configuration.md • sql-reference/System_variable.md
e
@Rocky is there a downside to making late_materialization_ratio = 100 ?
r
Yes, there is a potential downside. Setting 
late_materialization_ratio = 100
(up from the default of 
10
) makes the engine significantly more aggressive about choosing the late materialization path. While this is exactly what you want for your current "Top-N" query, it can backfire on other types of queries. Here is the trade-off: The Downside: "The Double-Read Penalty" Late materialization is a gamble on selectivity. It splits a single read into two phases: 1. Phase 1: Read only the filter columns (e.g., 
user_id
, 
event_time
) to find matching Row IDs. 2. Phase 2: Go back to disk to fetch the "payload" columns (your 50 `VARCHAR`s) only for the survivors. The downside occurs when Phase 1 doesn't filter enough rows. * Scenario (Bad for Late Mat): You run a report that needs to touch 80% of the rows in a partition (low selectivity). * Early Materialization (Default/Ratio 10): The BE reads all columns in one efficient, high-throughput sequential scan. * Late Materialization (Ratio 100): The BE reads the filter columns, realizes 80% of rows matched, and then has to perform a second massive I/O pass to get the 
VARCHAR
columns. This essentially doubles the disk I/O and adds significant CPU overhead to "stitch" the rows back together. Why the default is 
10
 StarRocks sets the default to 
10
because, for most analytical workloads, it is safer to over-read columns sequentially than to risk thousands of small, random I/O "lookups" for rows that weren't actually filtered out. When 
100
is dangerous You should be careful with a global 
100
setting if you have queries like: * Full Table Exports: 
SELECT * FROM table
with no 
WHERE
clause. * Large Aggregations: 
SELECT category, SUM(price) FROM table GROUP BY category
where almost every row is part of the result. * Non-Selective Filters: 
WHERE status != 'deleted'
(where 99% of rows are not deleted). Recommendation: Session vs. Global Instead of changing the BE config globally (which requires a restart and affects everyone), you can test this behavior at the session level first to see the impact on your specific "heavy" query:
Copy code
sql
-- This variable exists in newer versions to control the threshold per-query
SET late_materialization_ratio = 100; 

-- If that variable isn't available in your specific sub-version, 
-- use the Join hint mentioned before:
SET join_late_materialization = true;
How to verify: Run your query with 
SET profile_level = 1;
. Look at the Scan Node in the profile: * If 
LateMaterialization
is working, you will see a high 
PushdownStorageFilter
count and a very low number of rows for the "heavy" columns. _ If the query time _increases* on your 1 BE, it means the disk is struggling with the two-pass approach, and you should stick to 
store_row_column = true
instead. Summary Making it 
100
is great for your current Top-N query because you are filtering millions of rows down to 100. It is bad for "scan-heavy" queries where you need to process most of the data anyway. References • administration/management/BE_configuration.md • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md • best_practices/query_tuning/query_profile_operator_metrics.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
3 Views
Open in Slack
PreviousNext
Powered by