@Rocky There was a blog about scaling StarRocks on Amazon EKS with KEDA and Karpenter - https://aws.amazon.com/blogs/containers/scaling-starrocks-on-amazon-eks-with-keda-and-karpenter-for-enterprise-olap-workloads/. I am considering this architecture for my use case but I don't think it's feasible. The ingestion in the blog is via a Spark job to S3 as opposed to mine where data will flow from the stream loading application to the CN nodes via stream load with merge commit async. This ensures timely response to the stream loading application and efficient S3 PUTs to minimize API costs. As a result, the architecture mentioned gives karpenter a machine pool of r6i.24xlarge , m6in.24xlarge , r6in.24xlarge , m5n.24xlarge machines for the BE and CN nodes in the hybrid setup. However, since NVMe will be critical for my realtime ingestion throughput, I need i4i machines, preferably 8xlarge but 4x could potentially be acceptable. Would this setup work with Karpenter being given i4i.4xlarge and i4i.8xlarge node pools only? For reference, my current plan is to provision 8 i4i.8xlarge machines, hence total of 60TB of NVMe. My dataset has a TTL of 24 hours, occupying ~10TB of storage. Update throughput is ~10k/s across different types of events. About 30 columns are updated from these mixture of events in my ~550 column table. Provide feedback on whether the KEDA + Karpenter architecture is suitable or I should stick with reserved instances

The architecture you are considering is technically feasible, and your intuition about the limitations of the original blog post (which used Spark-to-S3) is correct for real-time use cases. StarRocks' Shared-Data (Compute Node) architecture was specifically optimized in recent versions (v3.2+) to handle real-time ingestion directly via CN nodes. However, for a high-throughput Primary Key workload (10k/s updates), the "stateless" nature of CN nodes is a double-edged sword. Here is the feedback on your proposed setup: 1. Ingestion Feasibility (Stream Load + Merge Commit) Your plan to use Stream Load with Merge Commit Async is the correct way to handle high-concurrency ingestion in a Shared-Data cluster. * Efficiency: This feature (introduced in v3.4) merges many small stream loads into a single transaction. This drastically reduces S3 PUT/POST costs and prevents "Too many versions" errors, which are common in real-time update scenarios. * CN Role: In Shared-Data mode, CN nodes receive the data, write it to their local cache (NVMe), and concurrently upload it to S3. You do not need a Spark job as an intermediary. 2. Primary Key Table & Karpenter Scaling This is the most critical part of your architecture. In a Shared-Data cluster, the Primary Key Index is the bottleneck for scaling. * Index Strategy: You must use Cloud-Native Persistent Index (

"persistent_index_type" = "CLOUD_NATIVE"

). * Why: If you use the default local persistent index, every time Karpenter scales a node out, the new node has to rebuild/re-download the index from scratch for the shards it takes over. * Cloud-Native Index: Stores the index in S3 and uses the local NVMe only as a cache. This makes CN nodes much more "elastic" and significantly speeds up the "warming" process when Karpenter provisions a new

i4i

node. * Scaling Impact: Even with a cloud-native index, scaling events (especially scale-down) will cause performance "hiccups." When Karpenter terminates an

i4i

node, the ~7.5TB of cached data on that node's NVMe is lost. The remaining nodes or new nodes must pull that data back from S3, which might momentarily spike ingestion latency for your 10k/s update stream. 3. Instance Choice:

i4i

vs. Reserved * NVMe Capacity: Your plan for 60TB of NVMe for a 10TB dataset (with 24h TTL) is excellent. It ensures a ~100% cache hit rate, meaning after the initial warming, your Shared-Data performance will be indistinguishable from a Shared-Nothing (Reserved Instance) setup. * Karpenter vs. Reserved: * Stick with Reserved (or a large "Min" pool): If your 10k/s update load is constant. The overhead of warming the cache and index on new nodes for a high-update table is non-trivial. Constant scaling will lead to inconsistent ingestion latencies. * Use KEDA + Karpenter: If your query/ingestion load has massive swings (e.g., 1k/s at night, 20k/s during the day). In this case, use KEDA to scale but set a very long

scaleDown

stabilization window (e.g., 30–60 minutes) to prevent aggressive node churn and cache loss. Feedback Summary * Feasibility: Yes. It is feasible and arguably the "modern" way to run StarRocks on EKS. * Critical Configs: 1. Enable

merge_commit_async

for Stream Load. 2. Set

persistent_index_type = CLOUD_NATIVE

for your table. 3. Set

datacache.enable = true

to utilize the

i4i

NVMe. * Architecture Verdict: If you can afford the cost, Reserved Instances (or an EKS Managed Node Group with a fixed size) provide the most stable performance for 10k/s updates. Use Karpenter only if you genuinely need to save costs during low-traffic periods, and be prepared for "cold-cache" latency when scaling. starts" on new. References • deployment/sr_operator.md • administration/management/monitoring/alert.md

@Rocky But with this query structure, wouldn't the percentages be inaccurate? Because the denominator is the count of rows after unnesting, but it should be the original total row count?

You are correct. When you use

unnest()

, it performs a lateral join that multiplies the number of rows based on the number of elements in the array. Using a simple

COUNT(*)

after unnesting will give you the total number of unnested elements, not the total number of original records. To get accurate percentages relative to the original row count, you can use one of the following three patterns: 1. The "Count Distinct" Approach (Simplest) If your table has a unique identifier (e.g.,

id

or

request_id

), use

COUNT(DISTINCT id)

as your numerator or denominator. This effectively "de-duplicates" the multiplied rows.

sql
SELECT 
    unnested_col,
    COUNT(DISTINCT id) AS original_row_matches,
    (SELECT COUNT(*) FROM your_table) AS total_original_rows,
    COUNT(DISTINCT id) * 100.0 / (SELECT COUNT(*) FROM your_table) AS percentage
FROM your_table, unnest(array_col) AS unnested_col
GROUP BY unnested_col;

2. The Window Function Pattern (Efficient) You can calculate the total row count before the unnesting occurs using a window function in a Common Table Expression (CTE). This ensures that filters applied to the base table are reflected in the denominator.

sql
WITH base AS (
    SELECT 
        *, 
        COUNT(*) OVER() AS total_original_rows -- Calculated BEFORE unnesting
    FROM your_table
    WHERE event_date = '2023-10-01' -- Filters here apply to both
)
SELECT 
    unnested_col,
    COUNT(DISTINCT id) AS match_count,
    MAX(total_original_rows) AS denominator,
    COUNT(DISTINCT id) * 100.0 / MAX(total_original_rows) AS percentage
FROM base, unnest(array_col) AS unnested_col
GROUP BY unnested_col;

3. Handling Empty/Null Arrays (v3.2.7+) If some rows have

NULL

or empty arrays, a standard

unnest

(comma join) will drop those rows entirely from the result set. If those rows should still be part of your "original total," use `LEFT JOIN ... ON TRUE`:

sql
SELECT 
    unnested_col,
    COUNT(DISTINCT id) AS matches,
    (SELECT COUNT(*) FROM your_table) AS original_total
FROM your_table 
LEFT JOIN unnest(array_col) AS unnested_col ON TRUE
GROUP BY unnested_col;

_Note: In this case,

unnested_col

will be

NULL

for rows that had empty/null arrays, allowing you to see what percentage of your data had no tags/elements._ Summary * Problem:

unnest

= multiplication.

COUNT(*)

= count of elements. * Solution: Use

COUNT(DISTINCT id)

for the numerator and calculate the denominator from the base table (using a subquery or

OVER()

) before the join happens. References • sql-reference/sql-functions/array-functions/unnest.md • using_starrocks/distinct_values/Using_HLL.md • using_starrocks/Lateral_join.md • sql-reference/sql-functions/aggregate-functions/count.md

@Rocky SO fix

WITH flattened AS (
    -- Step 1: Unnest the array first
    SELECT
        t.unnest AS genre
    FROM requests.requests,
    UNNEST(cleaned_genre) AS t
    WHERE is_audio = 1
)
SELECT
    genre,
    COUNT(*) AS genre_count,
    -- Step 2: Use window function on the result of the aggregation
    SUM(COUNT(*)) OVER() AS total_genre_hits,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS percentage
FROM flattened
GROUP BY genre
ORDER BY genre_count DESC;

to count rows before unnest. Or would approx count distinct be more efficient since it uses HLL

To fix your query, you need to calculate the original total row count before the

UNNEST

operation. In your current version, the CTE already contains the multiplied rows, making the window function

SUM(COUNT(*)) OVER()

return the total number of genre elements found, not the total number of requests. Fixed Query (Window Function Approach) This approach calculates the true denominator inside a CTE before the unnesting occurs.

sql
WITH base_data AS (
    SELECT 
        cleaned_genre,
        -- Calculate total rows matching filters BEFORE unnesting
        COUNT(*) OVER() AS original_total_count 
    FROM requests.requests
    WHERE is_audio = 1
)
SELECT
    genre,
    COUNT(*) AS genre_count,
    MAX(original_total_count) AS total_requests,
    ROUND(COUNT(*) * 100.0 / MAX(original_total_count), 2) AS percentage
FROM base_data, UNNEST(cleaned_genre) AS t(genre)
GROUP BY genre
ORDER BY genre_count DESC;

Is

APPROX_COUNT_DISTINCT

more efficient? Yes, for the numerator, but likely not necessary for the denominator. 1. For the Denominator (Total Rows): A simple

COUNT(_)

or

COUNT(_) OVER()

is extremely fast in StarRocks. You don't need an approximate count here because it doesn't involve a "distinct" operation. 2. For the Numerator (Count per Genre): If your goal is "how many unique requests contained this genre," and your table is massive (billions of rows),

COUNT(DISTINCT request_id)

can be slow because it triggers a data shuffle. * Recommendation: Use

ndv()

(StarRocks' implementation of HyperLogLog) if you need a speed boost and can tolerate a ~1-2% error rate. Optimized Query with

ndv()

If you find the

GROUP BY

is slow due to high cardinality, use this:

sql
WITH base AS (
    SELECT 
        cleaned_genre,
        request_id,
        (SELECT COUNT(*) FROM requests.requests WHERE is_audio = 1) AS total_rows
    FROM requests.requests
    WHERE is_audio = 1
)
SELECT 
    t.genre,
    -- Use ndv() for approximate unique counts per genre
    ndv(request_id) AS approx_request_matches, 
    MAX(total_rows) AS total_rows,
    ROUND(ndv(request_id) * 100.0 / MAX(total_rows), 2) AS percentage
FROM base, UNNEST(cleaned_genre) AS t(genre)
GROUP BY t.genre
ORDER BY approx_request_matches DESC;

Summary of Choice * Use the Window Function fixed query if you want 100% accuracy and your dataset is in the millions/low billions. * Use

ndv()

if you are processing tens of billions of rows or have memory pressure on your BE nodes, as it avoids the expensive global dictionary/shuffle required by

COUNT(DISTINCT)

. References • sql-reference/sql-functions/aggregate-functions/approx_count_distinct.md • using_starrocks/distinct_values/Using_HLL.md • sql-reference/sql-functions/array-functions/unnest.md References • using_starrocks/distinct_values/Using_bitmap.md • using_starrocks/distinct_values/Using_HLL.md • table_design/indexes/Bitmap_index.md