< Rocky> We re running StarRocks `4 1 1 14b7e3f` We set `ena StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> _We're running StarRocks `4.1.1-14b...

Stas Grinberg

06/04/2026, 9:26 AM

@Rocky _We're running StarRocks

4.1.1-14b7e3f

. We set

enable_recursive_cte = true

but recursive CTEs still fail with "Recursive CTE is not supported". Is this a known issue with this build?_

Rocky

06/04/2026, 9:27 AM

Recursive Common Table Expression (CTE) support was officially introduced in StarRocks v4.1. In version

4.1.1-14b7e3f

, the feature is available but is disabled by default. If you have set

enable_recursive_cte = true

and still receive the "Recursive CTE is not supported" error, please check the following: 1. Mandatory
RECURSIVE
Keyword StarRocks requires the

RECURSIVE

keyword in the CTE definition. A standard

WITH

clause that references itself will not be recognized as a recursive CTE and may trigger this error. * Correct:

WITH RECURSIVE cte_name AS (...)

* Incorrect:

WITH cte_name AS (...)

2. Variable Scope and Verification Ensure the variable is active in your current session. If you set it globally, it only applies to new connections. * Check the current session value:

SHOW VARIABLES LIKE 'enable_recursive_cte';

* If it shows

false

, run:

SET enable_recursive_cte = true;

(without

GLOBAL

) in the same session. * Alternatively, try the query hint to force it:

Copy code

sql
    WITH RECURSIVE my_cte AS (...)
    SELECT /_+ SET_VAR(enable_recursive_cte=true) _/ * FROM my_cte;

3. Structural Limitations Even with the flag enabled, certain complex structures are not yet supported and may trigger the "not supported" message: * Nested Recursive CTEs: You cannot define a recursive CTE inside another recursive CTE. * Recursive Member Restrictions: The recursive part (the part after the

UNION ALL

) generally cannot contain: *

GROUP BY

DISTINCT

, or Aggregate functions. * Multiple references to the CTE itself (it should only be referenced once in the

FROM

JOIN

clause). * Outer joins where the recursive CTE is on the null-producing side. * UNION vs UNION ALL: While the documentation indicates both are supported, ensure your anchor and recursive members have exactly matching column types and counts. 4. Optimizer (CBO) Requirement Recursive CTEs rely on the Cost-Based Optimizer. Ensure it is not disabled in your session: *

SET enable_cbo = true;

If your query follows the standard

WITH RECURSIVE ... UNION ALL

pattern and the variable is confirmed

true

but the error persists, it is likely hitting a specific structural edge case not yet covered in the v4.1.x release. Reference: StarRocks SELECT Documentation - RECURSIVE CTE References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/System_variable.md

Stas Grinberg

06/04/2026, 1:00 PM

@Rocky _We're using Superset connected to StarRocks. We need to run a

WITH RECURSIVE

CTE but it requires

SET enable_recursive_cte = true

in the same session. Superset resets the session between statements so the SET doesn't persist. Our tables are in catalog

def

, schema

org_5gmd9crgrozzvaxh_detailed

. Is there a way to set

enable_recursive_cte = true

as a default session variable in the StarRocks connection config, or set

org_5gmd9crgrozzvaxh_detailed

as the default database in the Superset database connection settings?_

Rocky

06/04/2026, 1:00 PM

To ensure

enable_recursive_cte = true

persists for your Superset sessions and to correctly set your default database, you can use the following methods: 1. Set the variable as a User Property (Recommended) Since StarRocks v3.3.3, you can attach session variables directly to a user profile. This is ideal for BI tools like Superset because the variable is automatically applied every time that user connects, regardless of session resets. Run this in a StarRocks terminal (replace

'your_superset_user'

with the actual username):

Copy code

sql
ALTER USER 'your_superset_user' SET PROPERTIES ("session.enable_recursive_cte" = "true");

*Note: This takes effect for all new connections made by that user.* 2. Set the variable Globally If you want recursive CTEs enabled for all users across the entire cluster, set it as a global variable. This requires

ADMIN

privileges.

Copy code

sql
SET GLOBAL enable_recursive_cte = true;

Note: This affects all new sessions created after the command is run. 3. Configure the Default Catalog and Database In Superset's Database Connection settings, you can specify both the catalog and the database in the SQLAlchemy URI. For your specific path (

def.org_5gmd9crgrozzvaxh_detailed

), use the following format: SQLAlchemy URI:

Copy code

text
starrocks://<User>:<Password>@<Host>:<Port>/def.org_5gmd9crgrozzvaxh_detailed

If the

in the database name causes parsing issues in certain driver versions, you can also set the default catalog/database as user properties:

Copy code

sql
ALTER USER 'your_superset_user' SET PROPERTIES (
    "catalog" = "def", 
    "database" = "org_5gmd9crgrozzvaxh_detailed"
);

4. Per-Query Alternative (Hint) If you cannot modify user properties or global settings, you can force the variable for a specific query using a

SET_VAR

hint. This is useful for one-off charts in Superset's SQL Lab:

Copy code

sql
WITH RECURSIVE my_cte AS (
    ...
)
SELECT /*+ SET_VAR(enable_recursive_cte=true) */ * FROM my_cte;

Summary of Recommendations: * Use ALTER USER ... SET PROPERTIES
to bind the recursive CTE setting and the default database to the specific user Superset uses. This is the most "set and forget" method for BI integrations. " method for BI integrations.

Rocky

06/04/2026, 2:32 PM

For a recursive path traversal (graph query) on 30M records, performance depends heavily on how quickly StarRocks can perform the join between the CTE and the base table in each iteration. Here are the optimized settings and data modeling recommendations for your query: 1. Optimized Table Design (Most Impact) The recursive part of your query joins

ep.next_id

r.source_id

while filtering on

integration_id

. To make this efficient: * Use a Primary Key Table: If possible, use the Primary Key model for

relations_graph

. This provides a persistent index that makes the join lookups significantly faster than the Duplicate Key model. * Key Order: Ensure

integration_id

and

source_id

are the first two columns in your Primary Key or Sort Key. This allows the storage engine to prune data at the storage level.

Copy code

sql
    -- Example for Primary Key table
    PRIMARY KEY (integration_id, source_id, target_id)
    DISTRIBUTED BY HASH(source_id) -- Or HASH(integration_id, source_id)

* Partitioning: If you have many unique

integration_id

values, partition the table by
integration_id
. This ensures the query only scans the 30M records belonging to that specific integration, rather than the entire table. 2. Indexing Strategy If you cannot change the table model, add these indexes to speed up the join and filter: * Bloom Filter Index: Add a Bloom Filter on

source_id

and

integration_id

. This helps skip data blocks that don't contain the requested IDs during the recursive join.

Copy code

sql
    ALTER TABLE relations_graph SET ("bloom_filter_columns" = "integration_id, source_id");

* Bitmap Index: If

relation_type

source_type

have low cardinality (few distinct values), add Bitmap indexes to them. 3. Recommended Session Variables In addition to the ones you've used, set these to optimize the execution plan:

Copy code

sql
-- Force the optimizer to use the most efficient join path
SET enable_cbo = true;

-- If the query is complex, increase the timeout (default is 300s)
SET query_timeout = 600;

-- Prevent the recursive member from producing too many rows
-- This is a safety cap for your 30M record dataset
SET recursive_cte_max_depth = 5;

4. Query Refinement Your query is already well-structured by including the

integration_id

filter in both the anchor and recursive members. However, check the following: * Column Pruning: In the recursive member, you are selecting

r.source_type

r.target_type

, and

r.relation_type

. If these are large strings, they increase the memory overhead of the intermediate CTE results. Only select columns you absolutely need. * Join Type: Verify in the

EXPLAIN

plan that the join is using a Hash Join. Since you are filtering by a specific

integration_id

, the working set for each depth should be relatively small. 5. Check the Execution Plan Run

EXPLAIN ANALYZE <your_query>

and look for: 1. TableSpecScan: Ensure

integration_id

is actually pruning partitions/tablets. 2. Join Reorder: Ensure the recursive CTE results are the "Build Side" (smaller side) and the

relations_graph

is the "Probe Side" (large side) if the working set per depth is small. If the query is still slow, it is likely due to a "Shuffle Join" happening at every level. If your data is distributed by

source_id

, StarRocks might be able to use a more efficient local join if the distribution matches the join key. References • knowledge/usage/best-practice.md • using_starrocks/accelerate-cardinality-preserving-joins.md • table_design/indexes/Bitmap_index.md

Stas Grinberg

06/07/2026, 2:20 PM

@Rocky _Hey, we're running analytical graph traversal queries on our StarRocks 4.1.1 shared_data cluster. We have 3 CN nodes, each with 16 cores and 25.9GB memory limit._ We noticed the memory cache is at 100% on all CNs (
MemUsage: 5.1GB/5.1GB
) while disk cache is only ~10% used (250GB / 2.5TB). Questions: 1. Is the memory cache being full expected/normal, or is it a concern? 2. Should we adjust any cache settings to improve query performance for large recursive/graph queries? 3. Are there any other CN settings you'd recommend reviewing for our workload (recursive CTEs, 35M+ row tables, depth-4 graph traversals)?

Rocky

06/07/2026, 2:21 PM

In StarRocks 4.1.1 (shared-data), the behavior you are seeing is standard for a cloud-native architecture. Here is the breakdown for your 16-core / 26GB CN nodes: 1. Is 100% memory cache usage normal? Yes, it is expected and generally desired. StarRocks uses a Data Cache (specifically a Block Cache/Page Cache) to store hot data blocks from remote storage (S3/OSS). * The 5.1GB Limit: This is likely exactly 20% of your 25.9GB total RAM (the default for

datacache_mem_size

storage_page_cache_limit

). * LRU Mechanism: As an LRU (Least Recently Used) cache, it is designed to fill up completely and stay full. When the cache is "100% used," StarRocks simply evicts the oldest, least-used data to make room for new data. * Concern Level: It is only a concern if your Cache Hit Rate is consistently low, which would force the CN to fetch data from the local disk cache (~10ms) or remote object storage (~100ms+), significantly slowing down your recursive queries. 2. Should you adjust cache settings? For large graph traversals (depth-4 on 35M+ rows), the recursive joins generate massive intermediate result sets. * Memory Trade-off: Increasing the memory cache (e.g., to 30-40%) can help performance if the same "hot" blocks are reused across recursive iterations. However, recursive CTEs are extremely memory-intensive for Query Execution (building hash tables for joins). * Recommendation: Keep the memory cache at 20-25%. If you increase it too much, you may starve the query execution engine, leading to "Memory Limit Exceeded" (OOM) errors during the depth-4 join. * Disk Cache: Your disk cache (250GB/2.5TB) is healthy. Since it's only 10% used, your entire 35M row table likely already fits on the local SSD. This is good—it means "cache misses" in RAM only have to go to local disk, not the cloud. 3. Recommended CN Settings for Graph Traversals For deep recursive CTEs on 35M+ rows, focus on execution memory and parallelism rather than just cache: * query_mem_limit
: Ensure this is high enough. On a 26GB node, a single complex recursive query might need 10-15GB.

Copy code

sql
    SET query_mem_limit = 16106127360; -- 15GB

* parallel_fragment_exec_instance_num
: For 16-core nodes, set this to

. Recursive CTEs benefit from high parallelism in the join phase of each iteration. * Enable Spill to Disk: If your depth-4 traversal hits memory limits, enable spilling. This allows the recursive join to complete using disk for intermediate results instead of failing.

Copy code

sql
    SET enable_spill = true;

* recursive_cte_max_depth
: Ensure this is set to at least

(default is 5, but verify if you plan to go deeper). * Check Hit Rates: To see if your cache is actually effective, run:

Copy code

sql
    SELECT * FROM information_schema.be_metrics WHERE name LIKE "%cache_hit_ratio%";

Summary: Your 100% RAM cache usage is a sign that the system is using all available "buffer" space. For your specific graph workload, ensure the execution engine has enough remaining RAM (the other 75-80%) to handle the large joins required by the recursive CTE. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • knowledge/usage/best-practice.md • administration/management/resource_management/spill_to_disk.md

Stas Grinberg

06/11/2026, 3:53 PM

Hi @Rocky, we're running StarRocks 4.1.1 in shared-data mode (FE + CN nodes, no BE, S3 object storage with NVMe local cache). We have your all-arch dashboard imported but I'm not sure which sections are relevant for CN vs BE-only. A few questions: 1. _Do CN nodes emit`starrocks_be_*`metrics (memory, CPU, compaction, tasks)? Which of the BE/BE Memory/BE tasks rows apply to CN?_ 2. For shared-data specifically — what are the most important metrics to monitor day-to-day? (e.g. cache hit rate, warmup, publish version latency, S3 I/O) 3. Do you have a dashboard built specifically for shared-data / lake mode, or a recommended set of panels for it? We already have the general lake dashboard (Metadata, Metacache, Vacuum, Publish Version, Abort Txn) and the Starlet dashboard. Looking for the right FE+CN health overview to complement those.

Rocky

06/11/2026, 3:53 PM

In StarRocks 4.1.1 (shared-data mode), the CN (Compute Node) is essentially a "storage-less" version of the BE. Because they share the same codebase for query execution and caching, most metrics and dashboard panels are applicable to both. *1. Do CN nodes emit

starrocks_be_*

metrics?* Yes. Even though they are called "Compute Nodes," they expose their metrics via the same Prometheus endpoint (

/metrics

) using the

starrocks_be_*

prefix. * Applies to CN: Memory (Query/Cache/Metadata), CPU, RPC latency, Task counts, and Pipeline execution metrics. * Irrelevant for CN: Compaction (mostly), Replica repair/Clone tasks, and local disk management (except for the Data Cache partition). * Key Distinction: On a CN, the

tablet_num

metric represents the number of cached tablets currently being served or held in the local cache, rather than tablets persisted on local disks. 2. Most Important Shared-Data Metrics (Day-to-Day) For a shared-data (lake mode) cluster, your performance is bound by Cache efficiency and Object Storage (S3) latency. Monitor these categories: * Data Cache (Crucial): * `block_cache_hit_rate`: Aim for >90% for warm workloads. * `block_cache_miss_bytes`: High values indicate queries are hitting S3, which increases latency. *

datacache_disk_used_bytes

vs `datacache_disk_quota_bytes`: If your disk cache is 100% full, the LRU policy is evicting data; monitor if this causes "Cache Thrashing." * Starlet / S3 I/O: * `fslib_read_io_latency`: Monitor the P99 latency of S3 reads. If S3 is slow, your query performance will drop. * `fslib_s3_single_upload_latency`: High latency here affects Commit/Publish speed for data loads. * Metadata (FE): * `starrocks_fe_tablet_meta_cache_hit_ratio`: Shared-data clusters cache tablet metadata in FE memory. A low hit rate here slows down query planning. * `publish_version_latency`: Monitor this to ensure data becomes visible quickly after a load. 3. Recommended Dashboards Since you already have the "General Lake" and "Starlet" dashboards, you have the right components. For a holistic FE+CN health view, verify you are using these specific templates: 1. Shared-data Cluster - General: This is the "FE+CN" overview. It combines cluster health with shared-data specific states (like Metacache and Vacuuming). 2. Shared-data Cluster - Starlet: Use this for deep-dives into S3 performance and local NVMe cache health. 3. All Architecture Dashboard: Use the "BE Memory" and "BE Tasks" rows here to monitor the CNs. Specifically, watch the "Query Mem" vs "Data Cache Mem" to ensure your recursive graph queries have enough execution memory. Pro-Tip for Graph Queries: On your Grafana dashboard, keep a close eye on the "Pipeline Driver Execution Time" and "Scan Throughput." If recursive queries are slow but cache hits are high, the bottleneck is likely CPU/Memory in the join phase. If cache hits are low, look at the "Starlet" dashboard to see if S3 is throttling your CN requests. References • administration/management/monitoring/metrics-shared-data.md • administration/management/monitoring/Monitor_and_Alert.md • quick_start/shared-data.md • deployment/shared_data/s3.md • deployment/shared_data/minio.md

Stas Grinberg

06/14/2026, 2:35 PM

Hey @Rocky, we're designing an RBAC / access control flow for our StarRocks v4.1.1 shared-data deployment on Kubernetes. We use Azure AD for identity and Apono for access request/approval workflows (similar to PAM). We already have Superset integrated with Azure AD (users log in to Superset via Azure AD SSO). Superset currently connects to StarRocks with a single shared service account. The goal: when a user requests access to a specific database/role in Apono, it automatically grants that StarRocks role to their user, and revokes it when the TTL expires — so enforcement is at StarRocks level, not Superset session level. A few questions: 1. User authentication — what's the recommended way to authenticate ~50 internal analysts to StarRocks? Since we already have Azure AD + Superset SSO, is there a way to propagate the user's Azure AD identity into StarRocks (e.g., LDAP, impersonation, or per-user connections in Superset)? Or is username+password the only practical option for direct StarRocks access? 2. _RBAC model — is it better to use StarRocks native roles (

CREATE ROLE

GRANT role TO USER

) or rely on catalog/DB-level grants directly on users? We want roles like

analytics_read

finance_read

data_engineer

scoped to specific databases._ 3. _Programmatic GRANT/REVOKE — what's the safest way for an external system (Apono webhook → Lambda) to execute `GRANT`/`REVOKE` commands against StarRocks? Should we use the MySQL protocol (port 9030) with a dedicated

apono_admin

service account, or is there an HTTP API on port 8030 that supports DDL commands?_ 4. Any gotchas with role grants in shared-data mode we should know about?

Rocky

06/14/2026, 2:35 PM

For a StarRocks v4.1.1 shared-data cluster integrated with Azure AD, Superset, and Apono, here is the recommended architecture and best practices for your JIT (Just-In-Time) access flow. 1. User Authentication & Identity Propagation Since you have ~50 analysts logging into Superset via Azure AD, you have two main paths: * Recommended: LDAP Authentication (via Azure AD DS). Azure AD does not support LDAP directly, but Azure AD Domain Services (Azure AD DS) does. You can configure StarRocks to authenticate users against this LDAP endpoint. * The User Flow: Create users in StarRocks with

IDENTIFIED WITH authentication_ldap_simple

. * Superset Identity Propagation: Enable "User Impersonation" in the Superset database connection settings. The StarRocks SQLAlchemy driver supports this by using

EXECUTE AS <user>

at the start of the session. This ensures that even though Superset uses one service account to connect, the actual query runs under the analyst's identity for auditing and RBAC enforcement. * Alternative: Matching Local Users. If Azure AD DS is not an option, Apono can create the user in StarRocks (with a synced password or random one) when access is requested. This is simpler to set up but requires managing passwords. 2. RBAC Model: Native Roles You should absolutely use StarRocks Native Roles rather than direct user-level grants. * Design Pattern: Create a set of persistent roles (e.g.,

finance_read

marketing_dev

). Grant these roles the necessary permissions on databases/catalogs once. * JIT Execution: Apono should only execute

GRANT ROLE finance_read TO USER analyst_a@'%'

when the request is approved, and

REVOKE

it later. * Default Roles: Use

ALTER USER analyst_a DEFAULT ROLE finance_read

so the user doesn't have to manually execute

SET ROLE

when they log in from Superset. 3. Programmatic GRANT/REVOKE * The Protocol: You must use the MySQL protocol (port 9030) for administrative commands like

GRANT

REVOKE

CREATE USER

, or

ALTER ROLE

. * The HTTP SQL API (Port 8030): While StarRocks has an HTTP SQL API, it is currently limited to

SELECT

SHOW

EXPLAIN

, and

KILL

. It does not support DDL or DCL commands like

GRANT

. * Security: Create a dedicated service account (e.g.,

apono_admin

) and grant it the system-defined user_admin
role. This role provides exactly the permissions needed to manage users and roles without full

cluster_admin

(root) privileges. 4. Critical "Gotchas" * Avoid "Security Integration" for Native RBAC: There is a feature in StarRocks called

CREATE SECURITY INTEGRATION

(for LDAP/OAuth2 auto-mapping). Warning: As of v4.x, if you use this feature, StarRocks requires an external authorizer like Apache Ranger. If you want to use StarRocks' native

GRANT/REVOKE

commands, you must manually (or via Apono) create the user accounts in StarRocks first and link them to LDAP/Azure AD. * Metadata Consistency: In shared-data mode, all RBAC metadata is stored in the FE (Frontend) memory and persisted to the metadata log. Ensure your FE nodes are configured with high availability (3 FEs) to avoid losing access control data. * Host Wildcards: When Apono creates or grants to users, ensure the host part of the user identity (

user@'host'

) matches the network source of your Superset nodes. Using

'%'

is common for Kubernetes deployments where CN/FE IPs rotate. Summary of Workflow 1. Apono receives a request for

finance_read

. 2. Apono connects to StarRocks FE (9030) as

apono_admin

. 3. Apono runs:

CREATE USER IF NOT EXISTS analyst_a IDENTIFIED WITH authentication_ldap_simple;

4. Apono runs:

GRANT ROLE finance_read TO USER analyst_a;

5. Analyst logs into Superset. Superset initiates a session and runs

EXECUTE AS analyst_a

. 6. StarRocks validates the analyst against Azure AD (LDAP) and applies the

finance_read

permissions. References

Rocky

06/14/2026, 2:35 PM

• administration/user_privs/authorization/User_privilege.md • administration/user_privs/authentication/ldap_authentication.md • sql-reference/http_sql_api.md • integrations/BI_integrations/Superset.md References • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_item.md • best_practices/authentication_authorization.md • administration/user_privs/authorization/user_privs.md

Stas Grinberg

06/20/2026, 7:35 AM

@Rocky, what are the concerns needs to be taken if in some use cases like updates we are tending to use native primary key table instead of external tables on iceberg + material view

Rocky

06/20/2026, 7:35 AM

Choosing StarRocks Native Primary Key (PK) tables over an Iceberg + Materialized View (MV) approach for update-heavy use cases offers significant performance advantages, but it introduces several architectural concerns you must plan for—especially in a shared-data (cloud-native) environment. 1. Memory Consumption (Primary Key Index) In StarRocks, the Primary Key index is stored entirely in memory on the CN nodes to enable ultra-fast upserts. * The Concern: For large tables, the index consumes significant RAM (roughly 30-40 bytes per unique key). * Risk: If your primary keys are long strings (e.g., UUIDs) or your table grows to billions of rows, the index can starve the query execution engine of memory. * Mitigation: Use integer-based primary keys where possible. In shared-data mode, ensure your CN nodes have enough memory to hold the index for all "warm" tablets. 2. Lock Contention & Write Concurrency Native PK tables use a row-level locking mechanism during the "Apply" phase of an upsert. * The Concern: If you have many concurrent small batches updating the same rows, you may see high

commit_latency

or lock wait times. * Comparison: Iceberg + MV handles updates via batch rewrites (CoW) or delete files (MoR), which are higher latency but don't hold real-time locks in the same way. * Guidance: Batch your updates (e.g., 5s-30s intervals) rather than sending single-row inserts. 3. Data Lake Interoperability (The "Walled Garden") * The Concern: StarRocks native tables use a proprietary storage format on S3. Other engines (Spark, Trino, Flink) cannot read this data directly from S3. * Comparison: With Iceberg, your "source of truth" remains open. If you move to Native PK tables, StarRocks becomes the sole gatekeeper for that data. * Mitigation: If external access is required, you will need to use the StarRocks External Connector or export data back to Iceberg periodically. 4. Compaction Overheads in Shared-Data Mode In shared-data mode, the CN nodes perform the compaction of the "Delete-and-Insert" versions of PK tables. * The Concern: Frequent updates generate many small versions. If compaction cannot keep up with the update rate, query performance will degrade (as the system has to merge more versions at read time). * Monitoring: Keep an eye on

max_tablet_version_count

. If it climbs too high, your CN nodes need more CPU or the update frequency is too aggressive. 5. Partial Update Limitations If your "updates" only affect a few columns (e.g., updating a status flag in a 100-column table): * Native PK: Supports Partial Updates natively (via

partial_update = true

). This is extremely efficient as it avoids reading the full row. * Iceberg + MV: Generally requires a full row rewrite or complex join logic in the MV, which is much more compute-intensive. Summary Comparison Feature | Native PK Table | Iceberg + Materialized View Update Latency | Near real-time (Seconds) | Batch-oriented (Minutes) Query Performance | Highest (Direct index) | High (via MV pre-aggregation) Memory Cost | High (PK Index in RAM) | Low (Metadata only) Interoperability | Low (SR Proprietary) | High (Open Format) Complexity | Simple (Standard DML) | High (MV Refresh + Iceberg Management) Recommendation: If your primary goal is low-latency updates and high-speed point/analytical queries within StarRocks, the Native PK table is the superior choice. Only stick with Iceberg + MV if you have a hard requirement for the data to be queryable by other tools (like Spark) or if your PK index is too large for your CN node's memory. References • table_design/table_types/primary_key_table.md • administration/management/resource_management/Memory_Management.md

Rocky

06/20/2026, 7:35 AM

• loading/loading_introduction/data_model_introduction.md • using_starrocks/query_iceberg.md • using_starrocks/Materialized_view.md

Stas Grinberg

06/20/2026, 7:48 AM

@Rocky, looks like the RAM for our CNs is the most critical metrics to be concerned. can you elaborate in what use cases it is used most

Rocky

06/20/2026, 7:48 AM

On a StarRocks CN (Compute Node), RAM is the primary "workspace" for almost every operation since there is no local persistent storage for data. While NVMe handles the Data Cache, the RAM is used for the logic, the indexes, and the metadata that makes those queries fast. Here are the four specific use cases where RAM is used most: 1. Primary Key Index (The "Upsert" Engine) This is typically the #1 consumer of RAM if you use Primary Key tables. * The Use Case: Real-time data ingestion with updates and deletes. To ensure uniqueness and find the "old" version of a row to mark it as deleted, StarRocks stores a mapping of

Primary Key -> Row Location

. * Why it eats RAM: In v4.1.1, even with "Persistent Index" enabled, StarRocks keeps a portion of the index (the L0 level or a cache of the index) in RAM to maintain high ingestion speeds. * Impact: If you have billions of rows with long string-based primary keys, this index can grow to hundreds of GBs. Without enough RAM, ingestion will slow down significantly or fail (OOM). 2. Query Execution Pool (Intermediate Results) This is the RAM used by the Pipeline Engine during query processing. * The Use Case: Complex analytical queries involving large Joins, Aggregations (GROUP BY), or Window Functions. * Why it eats RAM: When you join two large tables, StarRocks builds a Hash Table in memory. If your join keys have high cardinality (many unique values), these hash tables can consume all available query memory. * Shared-Data Specific: Since data is pulled from S3/Data Cache into memory buffers for processing, high-concurrency queries will multiply this RAM usage. 3. Metadata Cache (Shared-Data Orchestration) In a shared-data cluster, the CN doesn't "own" the data, but it needs to know exactly where every file and tablet is located on S3. * The Use Case: Just-in-time query planning and file access. * Why it eats RAM: CNs cache Tablet Metadata, Rowset Metadata, and Column Readers. In a cluster with millions of small files or high-frequency "Publish Version" events (frequent commits), the metadata memory (

table_meta

) can become substantial. * Monitoring: Look for the

table_meta

component in the

mem_tracker

. 4. Data Cache Index & Buffers (I/O Acceleration) While the actual data blocks are on NVMe, the "brains" of the cache are in RAM. * The Use Case: High-throughput scanning of the Data Cache. * Why it eats RAM: * Cache Index: To quickly find if a block is in the NVMe cache, StarRocks maintains an index in RAM. * I/O Buffers: When data is read from S3 (Cache Miss), it is buffered in RAM before being written to the NVMe disk and sent to the query engine. * Chunk Pool: StarRocks uses a "Chunk" based memory allocator. Frequent scans of large datasets will see high memory churn in the chunk pool. ────────── How to see exactly what's using your RAM: You can get a real-time breakdown of these components by visiting the CN's internal memory tracker: 1. Overall Breakdown:

http://<CN_IP>:8040/mem_tracker

2. PK Table Specifics:

http://<CN_IP>:8040/mem_tracker?type=update

Rocky's Tip: If you see

query_pool

spiking, focus on optimizing your SQL (Joins/Aggs). If you see

update

(under the

process

tracker) staying high even when no queries are running, your Primary Key Index is the culprit—not your queries—is your main RAM bottleneck.** References • administration/management/monitoring/metrics-shared-data.md • deployment/shared_data/s3.md • deployment/shared_data/minio.md • knowledge/admin_and_operation/shared_data_compaction.md

Stas Grinberg

07/02/2026, 4:19 PM

@Rocky how the kafka connector works in details? maybe we will use both external catalogs and native for primary key tables update

Rocky

07/02/2026, 4:20 PM

In a StarRocks deployment, the StarRocks Sink Connector for Kafka (the Kafka Connector) is an external application running within the Kafka Connect framework. It is specifically designed to handle high-frequency streams and CDC (Change Data Capture) data. Here is a breakdown of how it works and how it integrates with both Native PK tables and External Catalogs. 1. Architecture: How the Connector Moves Data The connector acts as a bridge between the Kafka ecosystem and the StarRocks Stream Load interface. 1. Buffering: The connector consumes messages from Kafka and buffers them in memory. 2. Triggering: When a buffer threshold is met (e.g.,

bufferflush.maxbytes

bufferflush.intervalms

), the connector opens an HTTP connection to the StarRocks Frontend (FE). 3. Stream Load: The FE redirects the connector to a specific Compute Node (CN), and the connector "pushes" the batch via a

PUT

request to the Stream Load API (

/api/{db}/{table}/_stream_load

). 4. Acknowledgment: Once the CN writes the data and the transaction is committed, the connector commits the Kafka offsets. 2. Native Primary Key (PK) Table Integration The Kafka Connector is the best way to handle UPSERT and DELETE operations from CDC tools like Debezium. * *The

*op

Mapping:* StarRocks PK tables use a hidden column called

*op

to determine the operation (0 for Upsert, 1 for Delete). * Debezium Transforms: The connector includes a built-in transform (

AddOpFieldForDebeziumRecord

) that automatically extracts the operation type from the Debezium metadata and maps it to the

__op

field in the Stream Load request. * Partial Updates: You can configure the connector to perform partial updates (updating only specific columns) by setting

sink.properties.partial_update=true

. 3. Integration with External Catalogs (e.g., Iceberg) This is a critical architectural distinction: The StarRocks Kafka Connector cannot load data directly into an External Catalog (Iceberg/Hive/Delta). The Stream Load protocol used by the connector is strictly for StarRocks' native storage engine. To get Kafka data into an External Catalog via StarRocks, you must use one of the following patterns: * Pattern A: The Bridge (Recommended for Updates) 1. Kafka → Connector → StarRocks Native PK Table: Load the raw stream into a native StarRocks table for real-time performance and easy update handling. 2. Native Table → Materialized View → Iceberg: Create a Materialized View (MV) on top of the native table that targets your Iceberg catalog. StarRocks will handle the background "sync" from the native storage to the data lake. * Pattern B: Direct Catalog Write (Batch-oriented) If you don't need real-time updates and just want to move data, you can use a scheduled StarRocks job:

Copy code

sql
    INSERT INTO iceberg_catalog.db.table
    SELECT * FROM native_starrocks_table
    WHERE load_time > ...

COLUMNS

) | Advanced (built-in CDC transforms) External Catalogs | Native Tables Only | Native Tables Only Format Support | CSV, JSON, Avro | Anything (via Kafka Converters) Exactly-Once | Built-in | Supported via Transactional Stream Load Recommendation for your Use Case: Since you are using shared-data mode on K8s: 1. Use the Kafka Sink Connector to ingest into Native PK Tables. This gives you the best control over memory usage (buffering) and handles the "Upsert" logic at the connector level. 2. If you need that data in Iceberg, use StarRocks Materialized Views or Export tasks to move the data from the Native PK table to the Iceberg Catalog.

Rocky

07/02/2026, 4:20 PM

3. Monitoring: Monitor the

stream_load

metrics on your CN nodes. Since RAM is your primary concern, ensure the connector's

bufferflush.maxbytes

is tuned so that you don't send massive bursts that spike the CN's query memory pool. References • loading/Kafka-connector-starrocks.md • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • data_source/catalog/iceberg/iceberg_catalog.md References • data_source/External_table.md • loading/Loading_intro.md

Stas Grinberg

07/02/2026, 4:27 PM

@Rocky is it used dibizume for kafka connector? what is the minimum batch flash time?

Rocky

07/02/2026, 4:27 PM

Yes, the StarRocks Kafka Connector is designed to work seamlessly with Debezium for CDC (Change Data Capture) scenarios, particularly when updating Primary Key tables. Debezium Integration The connector includes a built-in transform specifically for Debezium data: * Transform:

com.starrocks.connector.kafka.transforms.AddOpFieldForDebeziumRecord

. * Function: It automatically parses the Debezium record metadata and maps the operation (create, update, delete) to StarRocks' internal

__op

column. This allows you to sync deletes and updates from source databases like MySQL, PostgreSQL, or Oracle directly into StarRocks Native PK tables. * Example Workflow: 1. Debezium captures a change and sends a JSON/Avro message to Kafka. 2. StarRocks Kafka Connector pulls the message. 3. The

AddOpField

transform identifies if it's an

(update) or

(delete). 4. The record is sent to StarRocks via Stream Load with the correct

__op

flag. Minimum Batch Flush Time The frequency at which data is flushed from the connector to StarRocks is controlled by the

bufferflush.intervalms

parameter. * Minimum Value: 1,000 ms (1 second). * Default Value: 1,000 ms. * Range:

[1000, 3600000]

(1 second to 1 hour). Important Note on Performance: While you can set it to 1 second, doing so creates many small versions in StarRocks. For high-throughput systems, it is often recommended to increase this to 5,000ms - 30,000ms to reduce the overhead on the CN nodes' memory (compaction) and the FE's transaction management. Data is also flushed if the buffer size reaches

bufferflush.maxbytes

(default 90 MB) before the time interval expires. References • loading/Kafka-connector-starrocks.md • loading/Load_to_Primary_Key_tables.md References • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/Spark-connector-starrocks.md

Stas Grinberg

07/02/2026, 4:29 PM

@Rocky in our architecture, tge data are jsons already in kafka

Rocky

07/02/2026, 4:30 PM

Since your data is already in JSON format in Kafka, the StarRocks Sink Connector is the most efficient way to handle it. It natively supports JSON and maps directly to your Primary Key (PK) tables. 1. How the Connector Handles JSON Because your data is already JSON, you don't need complex converters. You can use the standard Kafka Connect

JsonConverter

. * Mapping: By default, the connector maps JSON keys to StarRocks table columns with the same names. * Custom Mapping: If your JSON keys don't match your table columns, you can use

sink.properties.jsonpaths

to define the mapping. * Nested JSON: If you have nested fields, you can either: 1. Flatten them using Kafka Connect SMTs (Single Message Transforms). 2. Use

jsonpaths

(e.g.,

$.user.id

) to point to the nested data during the Stream Load process. 2. Using Debezium for Updates If your Kafka JSON messages are generated by Debezium, they contain a complex structure (with

before

after

, and

op

fields). The connector has a built-in transform to simplify this for StarRocks PK tables: Key Configuration for Debezium JSON:

Copy code

properties
# Extract the 'after' state as the main record
transforms=unwrap,addfield
transforms.unwrap.type=io.debezium.transforms.ExtractNewRecordState

# Map Debezium 'op' (c, u, d) to StarRocks '__op' (0, 0, 1)
transforms.addfield.type=com.starrocks.connector.kafka.transforms.AddOpFieldForDebeziumRecord

* Upserts (c, u): Automatically mapped to

__op = 0

. * Deletes (d): Automatically mapped to

__op = 1

. 3. Minimum Batch Flush Time As discussed, the minimum time interval is 1,000 ms (1 second). * bufferflush.intervalms
: Default/Min is

. * bufferflush.maxbytes
: Default is

94371840

(90 MB). * Recommendation: If you set this too low (e.g., exactly 1s), you will create a high number of small "versions" in StarRocks. This increases RAM usage on CNs for compaction and metadata. For a balance of "near real-time" and performance, 5,000 ms to 10,000 ms is usually the "sweet spot." 4. Native PK Tables vs. External Catalogs This is a critical architectural point for your "Shared-Data" setup: * Native PK Tables: These are the only tables that support true real-time Upserts and Deletes within StarRocks. If you use the Kafka Connector, you should target these for your operational data. * External Catalogs (Iceberg/Glue): StarRocks treats these as "ReadOnly" or "Append-Only" from the engine's perspective in most cases. You cannot use the Kafka Sink Connector to perform UPSERTS/DELETES directly into an Iceberg table via StarRocks. * Hybrid Strategy: 1. Ingest Kafka JSON into a Native PK Table using the connector. 2. Synchronize to Iceberg/External Catalogs periodically if needed for long-term lake storage using an

INSERT INTO ... SELECT

job or a Materialized View. Summary of Properties for your `connector.properties`:

Copy code

properties
connector.class=com.starrocks.connector.kafka.StarRocksSinkConnector
key.converter=org.apache.kafka.connect.json.JsonConverter
value.converter=org.apache.kafka.connect.json.JsonConverter
value.converter.schemas.enable=false

# Stream Load Specifics
sink.properties.format=json
sink.properties.strip_outer_array=true
sink.properties.partial_update=true  # If you only update some columns

References • loading/Kafka-connector-starrocks.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md References • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md

Rocky

07/02/2026, 4:30 PM

• sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md

Stas Grinberg

07/02/2026, 4:48 PM

@Rocky is it possible to extract speaific columns from json in kafka and load only them to primary key table? also what if we have hight load or lag in kafka, is it possible to scale? what about errors and dlq

Rocky

07/02/2026, 4:48 PM

Yes, you can extract specific columns, scale for high load, and handle errors using the following mechanisms: 1. Extracting Specific Columns from JSON Since the Kafka Sink Connector uses the Stream Load API internally, you can use the same transformation parameters. If your Kafka JSON has 10 fields but your StarRocks Primary Key table only needs 3, you configure them in the

sink.properties.*

section of your connector config. * sink.properties.jsonpaths
: Specifies which JSON keys to extract. * sink.properties.columns
: Maps those keys to StarRocks columns and allows for basic transformations (like

id, name, age=age+1

). Example Configuration:

Copy code

json
{
    "name": "sr-sink-connector",
    "config": {
        "connector.class": "com.starrocks.connector.kafka.StarRocksSinkConnector",
        "topics": "raw_json_topic",
        "sink.properties.format": "json",
        "sink.properties.jsonpaths": "[\"$.user_id\", \"$.event_name\", \"$.payload.status\"]",
        "sink.properties.columns": "id, event, status"
    }
}

2. Scaling for High Load or Lag The connector scales horizontally within the Kafka Connect framework: * tasks.max
: This is the primary scaling lever. Increase this to match the number of partitions in your Kafka topic. Each "task" runs in its own thread and consumes from a subset of partitions. * Worker Scaling: You can add more Kafka Connect worker nodes to your cluster. Kafka Connect will automatically rebalance the tasks across all available workers. * Batch Tuning: If you have high lag, increase

bufferflush.maxbytes

(e.g., to 200MB or 500MB) and

bufferflush.intervalms

. Larger batches are more efficient for StarRocks to process, especially for Primary Key tables which require more CPU for compaction. 3. Error Handling and DLQ (Dead Letter Queue) Because this is a standard Kafka Connect Sink, it leverages the built-in error-handling features of the Kafka Connect framework: * Framework DLQ: You can route problematic messages (e.g., malformed JSON) to a Dead Letter Queue by adding these to your connector config:

Copy code

properties
    errors.tolerance = all
    errors.deadletterqueue.topic.name = sr_sink_dlq
    errors.deadletterqueue.context.headers.enable = true

* StarRocks Filter Ratio: If the data format is correct but it fails StarRocks' internal validation (e.g., a string is too long for a

VARCHAR(10)

column), you can use

sink.properties.max_filter_ratio

. * By default, it is

(any error fails the whole batch). * Setting it to

1.0

allows the batch to proceed even if some rows are invalid. * At-Least-Once: The connector guarantees that data is not lost. It only commits Kafka offsets after StarRocks confirms the Stream Load transaction is successful. Summary for Primary Key Tables When using JSON with Primary Key tables, keep in mind: 1. Partial Updates: If your JSON only contains a few columns of the table, set

sink.properties.partial_update = true

. 2. Order Matters: To ensure updates happen in the correct order, ensure that messages with the same Primary Key stay in the same Kafka partition (using a consistent Kafka partition key). References • loading/Kafka-connector-starrocks.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/RoutineLoad.md References • loading/Kafka-connector-starrocks.md • loading/RoutineLoad.md

Rocky

07/02/2026, 4:48 PM

• sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • quick_start/routine-load.md References • loading/Kafka-connector-starrocks.md • faq/loading/Routine_load_faq.md

Stas Grinberg

07/02/2026, 4:52 PM

@Rocky, kafka connect is framework as open source on our eks cluster?

Rocky

07/02/2026, 4:52 PM

Yes, Kafka Connect is an open-source framework that is part of the Apache Kafka project. You can absolutely run it on your EKS (Amazon Elastic Kubernetes Service) cluster. In your architecture, Kafka Connect acts as the middle layer: it pulls the JSON data from your Kafka topics and "sinks" it into StarRocks using the StarRocks Kafka Connector. How to run it on EKS There are two common ways to deploy Kafka Connect on Kubernetes/EKS: 1. Strimzi (Recommended): * Strimzi is the industry-standard CNCF operator for running Kafka on Kubernetes. * It provides a

KafkaConnect

Custom Resource (CRD). * You can use Strimzi’s "Build" feature to automatically create a Docker image that includes the StarRocks Connector JAR file. 2. Custom Docker Image: * If you don't use Strimzi, you can build a standard Docker image based on

confluentinc/cp-kafka-connect

apache/kafka

. * You simply add the

starrocks-connector-for-kafka-xxx-with-dependencies.jar

into the

/usr/share/java/kafka-connect-starrocks

(or your configured

plugin.path

) directory in the Dockerfile. * You then deploy this image as a standard Kubernetes

Deployment

. Why use the Connector on EKS vs. Routine Load? StarRocks also has a built-in feature called Routine Load which doesn't require Kafka Connect. However, since you are on EKS and interested in advanced features, here is the comparison: Feature | ***Kafka Sink Connector** (on EKS)* | ***Routine Load** (Built-in)* Transformations | Supports complex SMTs and Debezium unwrapping. | Basic SQL-like transformations only. Error Handling | Full DLQ support (via Kafka Connect framework). | Limited error log (max 10 rows). Scaling | Scales horizontally via Kafka Connect Workers. | Scales via StarRocks BE nodes. Ecosystem | Integrated with the Kafka/Debezium ecosystem. | Self-contained within StarRocks. Summary for your setup: * Framework: Use the open-source Kafka Connect distributed mode. * Plugin: Download the StarRocks Kafka Connector JAR and add it to your Kafka Connect plugins. * Deployment: Deploy on EKS using Strimzi or a Helm chart for Kafka Connect. * Configuration: Use the

sink.properties

discussed previously to filter your JSON columns and handle scaling/errors. References • loading/Kafka-connector-starrocks.md • loading/RoutineLoad.md • deployment/sr_operator.md References • loading/Kafka-connector-starrocks.md • loading/RoutineLoad.md • deployment/sr_operator.md • administration/management/FE_configuration.md References • unloading/Spark_connector.md • loading/Flink-connector-starrocks.md • loading/Kafka-connector-starrocks.md

Stas Grinberg

07/02/2026, 6:21 PM

@Rocky is it one stream per target table?

Rocky

07/02/2026, 6:21 PM

No, you do not need a separate connector instance (or "stream") for every target table. A single StarRocks Kafka Sink Connector can handle multiple topics and map them to multiple StarRocks tables simultaneously. 1. Multi-Table Configuration You can define the mapping using the

topics

and

starrocks.topic2table.map

parameters in a single configuration file. * topics
: A comma-separated list of Kafka topics you want to consume. * starrocks.topic2table.map
: A mapping of which topic goes to which table. Example Configuration:

Copy code

json
{
    "name": "multi-table-sink",
    "config": {
        "connector.class": "com.starrocks.connector.kafka.StarRocksSinkConnector",
        "topics": "orders_topic, users_topic, products_topic",
        "starrocks.database.name": "ecommerce_db",
        "starrocks.topic2table.map": "orders_topic:orders, users_topic:users, products_topic:dim_products",
        ...
    }
}

In this example, one connector process manages data flow for three different tables. 2. How it works under the hood Even though you use one connector instance, here is how the data flows: * Logical Separation: The connector internally creates separate "Stream Load" tasks for each table. * Task Scaling: If you set

tasks.max=10

, Kafka Connect will distribute the partitions of all listed topics across those 10 tasks. * Transactionality: Each table is loaded independently. If the load for

orders

fails, it does not necessarily stop the load for

users

. 3. One Topic to Multiple Tables? The standard connector maps one topic to one table. If your Kafka topic contains a mix of different data types (e.g., "events" topic containing both

click

and

purchase

JSONs) and you want to split them into separate StarRocks tables, you have two options: 1. Kafka Connect SMT (Single Message Transform): Use a "Router" transform to redirect messages to different topics based on a field value before they reach the StarRocks Sink. 2. Multiple Connector Instances: Define two connectors reading the same topic, each with a different filter/transformation to pick only the relevant rows for its specific target table. Summary * Many Topics $\rightarrow$ Many Tables: Use one connector instance. * One Topic $\rightarrow$ Many Tables: Use multiple connector instances (or SMT routers). * One Topic $\rightarrow$ One Table: The simplest and most common 1:1 setup. References • loading/Kafka-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/Flink-connector-starrocks.md References • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md

Rocky

07/02/2026, 6:21 PM

• faq/loading/Routine_load_faq.md • quick_start/routine-load.md • administration/management/FE_configuration.md • administration/Management.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • unloading/Spark_connector.md • deployment/sr_operator.md • loading/Load_to_Primary_Key_tables.md References • loading/Kafka-connector-starrocks.md

Stas Grinberg

07/03/2026, 5:25 AM

@Rocky is there any option to route events to multi tenant Database in starrocks depending on account id in kafka topic message

Rocky

07/03/2026, 5:25 AM

The StarRocks Kafka Sink Connector does not natively support routing messages to different databases based on a field (like

account_id

) within a single connector instance. The

starrocks.database.name

parameter is a mandatory, static configuration that ties one connector instance to one specific StarRocks database. However, depending on your architecture requirements, there are three common ways to achieve multi-tenant routing: 1. The "One Connector per Database" Approach (Recommended) This is the standard way to handle multi-tenancy with Kafka Connect. You deploy a separate connector configuration for each tenant/database. * Pros: Best isolation; if one tenant's load fails or lags, it doesn't affect others. * Cons: Higher management overhead if you have hundreds of tenants. * Implementation: Use an upstream Kafka Router SMT (Single Message Transform) to split your main topic into per-tenant topics (e.g.,

events_tenantA

events_tenantB

), then point each connector to its specific topic and database. 2. The Flink Connector Approach (Most Flexible) If your requirements involve high-scale, dynamic routing to many databases/tables without creating hundreds of connectors, Apache Flink with the StarRocks Flink Connector is the better tool. * Unlike the Kafka Sink Connector, the Flink Connector can dynamically resolve the

database-name

and

table-name

at runtime for every record. * You can write a simple Flink SQL or DataStream job that reads the

account_id

and uses it to define the target database string. 3. Routing to Different Tables (Same Database) If you are willing to store all tenants in the same database but in different tables (e.g.,

tenant_123_orders

), you can use a trick with Kafka Connect: 1. Use a RegexRouter SMT: Transform the topic name of the record based on the

account_id

field. 2. Default Mapping: By default, the StarRocks connector sinks data to a table that matches the record's topic name. 3. Result: Records with

account_id: 101

get their topic renamed to

orders_101

by the SMT, and the connector automatically loads them into the

orders_101

table in the configured database. Summary of Options Requirement | Solution Manageable # of Tenants | One Kafka Sink Connector instance per database. High # of Tenants (Dynamic) | Use the StarRocks Flink Connector. Multi-tenancy via Tables | Use Kafka Sink Connector + RegexRouter SMT. For your EKS setup, using Flink on Kubernetes (via the Flink Operator) alongside StarRocks is a very common pattern for complex, multi-tenant ingestion pipelines. References • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md References • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md

Rocky

07/03/2026, 5:25 AM

• sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • quick_start/routine-load.md • administration/management/FE_configuration.md • administration/Management.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • unloading/Spark_connector.md • deployment/sr_operator.md • loading/Load_to_Primary_Key_tables.md References • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • quick_start/routine-load.md • administration/management/FE_configuration.md • administration/Management.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • unloading/Spark_connector.md • deployment/sr_operator.md • loading/Load_to_Primary_Key_tables.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md

Rocky

07/03/2026, 5:25 AM

• sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • quick_start/routine-load.md • administration/management/FE_configuration.md • administration/Management.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • unloading/Spark_connector.md • deployment/sr_operator.md • loading/Load_to_Primary_Key_tables.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • loading/Kafka-connector-starrocks.md • loading/Flink-connector-starrocks.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md • quick_start/routine-load.md • administration/management/FE_configuration.md • administration/Management.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • unloading/Spark_connector.md • deployment/sr_operator.md • loading/Load_to_Primary_Key_tables.md References • loading/Kafka-connector-starrocks.md

Stas Grinberg

07/03/2026, 5:30 AM

@Rocky, no python streaming connector?

Rocky

07/03/2026, 5:34 AM

There is no standalone "pure Python" streaming library in the same way there is a Kafka Sink Connector or a Flink Connector JAR. However, you have three excellent options for streaming data into StarRocks using Python: 1. PySpark or PyFlink (Recommended for high volume) If you are already using a processing engine on EKS, both the official Spark and Flink connectors support Python APIs. * PySpark: Use the StarRocks Spark Connector. It supports Structured Streaming in Python. * PyFlink: The StarRocks Flink Connector explicitly supports the Python API. You can define a StarRocks sink in your PyFlink job to stream data directly. 2. Direct Stream Load via Python (Most Lightweight) Since StarRocks' primary ingestion method (Stream Load) is just a standard HTTP PUT interface, you can write a very efficient streaming ingestor in pure Python without any heavy dependencies. This is the common approach for custom Python microservices. Example using the
requests
library:

Copy code

python
import requests
import json

# Your data batch (JSON format)
data = [
    {"id": 1, "name": "Rocky", "score": 100},
    {"id": 2, "name": "StarRocks", "score": 200}
]

# Convert to newline-delimited JSON (NDJSON)
body = "\n".join([json.dumps(r) for r in data])

url = "http://<FE_HOST>:8030/api/ecommerce_db/orders_table/_stream_load"
headers = {
    "format": "json",
    "strip_outer_array": "false",
    "Expect": "100-continue",
    "Authorization": "Basic <base64_encoded_creds>"
}

response = requests.put(url, data=body, headers=headers)
print(response.json())

3. StarRocks Python Client (SQLAlchemy) There is an official

starrocks

package on PyPI (

pip install starrocks

). * Purpose: It is primarily a SQLAlchemy dialect. * Use case: Best for app development, ORM, or small-batch inserts. It is not designed for high-throughput streaming (thousands of rows per second), as it uses the MySQL protocol for

INSERT

statements rather than the optimized Stream Load API. Summary: Which one to choose? If you want... | Use... Highest throughput / ETL | PyFlink or PySpark with the StarRocks Connector. A simple microservice | Pure Python with

requests

calling the Stream Load API. Django/Flask integration | StarRocks Python Client (SQLAlchemy). References • loading/Spark-connector-starrocks.md • loading/Flink-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md References • loading/Spark-connector-starrocks.md • loading/Flink-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md References • loading/Spark-connector-starrocks.md • loading/Flink-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/StreamLoad.md • loading/Loading_intro.md • loading/Load_to_Primary_Key_tables.md • loading/Spark-connector-starrocks.md

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Rocky

07/03/2026, 5:34 AM

Stas Grinberg

07/08/2026, 9:53 AM

@Rocky, is it possible to stream the data from kafka to starrocks using python without using batch buffering? if yes, what is the performance and risks?

Rocky

07/08/2026, 9:54 AM

Technically, yes, you can stream data record-by-record from Kafka to StarRocks using Python, but you should not do it in a production environment. In StarRocks, every single ingestion request (even for one row) creates a new data version. Attempting to ingest record-by-record without buffering will quickly lead to system instability. How it is technically possible You can use a Kafka library (like

confluent-kafka

kafka-python

) to consume messages and immediately send each one to StarRocks using the Stream Load API (an HTTP PUT request).

Copy code

python
# WARNING: Do NOT use this record-by-record pattern in production
for message in consumer:
    # Immediately send 1 row via HTTP PUT to StarRocks
    requests.put("<http://fe:8030/api/db/table/_stream_load>", data=message.value)

────────── The Risks (Why you shouldn't do it) 1. "Too Many Tablet Versions" Error This is the most common failure. StarRocks creates a "version" for every committed transaction. If you ingest 1,000 rows per second individually, you create 1,000 versions per second. The background compaction process cannot keep up with this rate. Once a tablet hits the version limit (default is 1,000), all further writes to that table will be blocked. 2. High CPU & Network Overhead Each HTTP request involves SSL/TLS handshakes, header parsing, and transaction management overhead. Sending 1,000 separate requests instead of one batch of 1,000 rows increases the CPU and network overhead by orders of magnitude. 3. Transaction Limits StarRocks has a default limit on concurrent transactions (e.g.,

max_running_txn_num_per_db

usually defaults to 100). High-frequency individual loads will likely exhaust this pool, causing your Python script to receive "too many transactions" errors. ────────── Performance Comparison Feature | Record-by-Record (1 row) | Micro-Batch (e.g., 5s or 10MB) Throughput | Extremely Low | Extremely High Latency | < 100ms (but fragile) | 1s – 10s (stable) Stability | Will crash the cluster/table | Production-grade Compaction | Overwhelmed immediately | Healthy and efficient ────────── The Recommended "Pythonic" Way If you must use Python, you should implement a simple time-based or size-based buffer in your code. 1. Collect messages in a list. 2. Flush the list to StarRocks when it reaches either 5–10 MB or 5–10 seconds have passed. If you want a managed solution that handles this automatically, use the StarRocks Kafka Sink Connector or Routine Load. They are designed to manage these buffers and ensure the cluster remains healthy while maintaining near-real-time latency. References • loading/StreamLoad.md • faq/loading/Loading_faq.md • loading/Kafka-connector-starrocks.md • loading/RoutineLoad.md References • loading/Kafka-connector-starrocks.md • quick_start/routine-load.md • loading/Spark-connector-starrocks.md

Stas Grinberg

07/09/2026, 10:41 AM

@Rocky We're running StarRocks in shared-data mode (CN nodes, no BE). We want to implement workload isolation between ingestion writes and analytical queries. The docs say resource groups are a BE feature only — no mention of CN support. Questions: 1. Are resource groups supported on CN nodes in shared-data mode (we're on 4.1.1)? 2. If not, what's the recommended approach for workload isolation on CN — multiple CN pools?

Rocky

07/09/2026, 10:42 AM

In StarRocks shared-data mode (version 3.x and above), workload isolation is one of the primary architectural advantages. Here are the answers to your specific questions: 1. Are Resource Groups supported on CN nodes? Yes. While many StarRocks documentation pages use the term "BE" (Backend) as the default worker node type, in shared-data mode, Compute Nodes (CN) inherit the execution engine logic of BEs. Resource Groups (Workload Groups) are fully supported on CN nodes. You can define a resource group with specific

cpu_weight

mem_limit

, and

concurrency_limit

, and the CN nodes will enforce these limits during query execution. * Version Note: You mentioned version 4.1.1. In the 3.x branch (up to 3.3+), StarRocks has significantly enhanced these features, including CPU Hard Limits (

exclusive_cpu_cores

) which are also supported on CNs to prevent a single workload from "noisy neighbor" behavior. 2. Recommended Approach: Compute Groups (Multi-Warehouse) While Resource Groups provide logical isolation (sharing the same hardware but limiting consumption), the recommended approach for true physical isolation in shared-data mode is using Compute Groups (also known as the Multi-Warehouse architecture). Since CN nodes are stateless and data is stored in shared object storage (S3/HDFS), you can deploy multiple independent pools of CN nodes. How to implement multiple CN pools: 1. Deployment (Kubernetes/Operator): If you are using the StarRocks Kubernetes Operator, you can define multiple

StarRocksComputeNodeGroup

resources in your CRD. * Create one group named

ingest-pool

(e.g., 3 nodes). * Create another group named

query-pool

(e.g., 10 nodes). 2. Binding Workloads: You can route traffic to these specific pools: * By Session: Use the command

SET warehouse = 'ingest-pool';

before running your ingestion jobs. * By Resource Group: In the shared-data architecture, you can bind a Resource Group to a specific Compute Group. This allows you to say "All queries from the 'analytics' user must run on the 'query-pool' CNs." Summary of Isolation Strategies Strategy | Method | Isolation Level | Use Case Logical | Resource Groups | Soft/Hard limits on same nodes. | Small-to-medium clusters where hardware sharing is preferred. Physical | Compute Groups | Separate CN pools for different workloads. | Recommended. Preventing heavy ingestion from impacting interactive query latency. Rocky's Advice: For your version (4.1.1/3.3+), you should definitely look into Compute Groups. This allows you to scale your "analytical pool" up for business hours and down at night, while keeping a small, steady "ingestion pool" running 24/7, with zero interference between them. References • administration/management/resource_management/resource_group.md • introduction/Architecture.md • deployment/shared_data/feature-support-shared-data.md • faq/resource_isolation_faq.md References • deployment/shared_data/feature-support-shared-data.md • deployment/shared_data/s3.md • introduction/Architecture.md • deployment/shared_data/gcs.md • deployment/shared_data/hdfs.md • deployment/shared_data/minio.md • quick_start/shared-data.md

Stas Grinberg

07/22/2026, 12:35 PM

@Rocky For a Primary Key table loaded via Stream Load JSON: if we store nested protobuf fields as
STRUCT
, how do we ALTER the table to add a new nested field inside an existing STRUCT — and is that production-safe, or should we use
JSON
instead for evolving nested data?

Rocky

07/22/2026, 12:35 PM

Yes, you can add nested fields to a

STRUCT

, but there is a major version-specific caveat regarding your shared-data mode deployment. 1. How to ALTER a STRUCT (v3.2.10+ / v3.3.2+) StarRocks recently introduced the ability to modify specific fields within a

STRUCT

without redefining the entire column. Syntax:

Copy code

sql
-- Requirement: Table must have "fast_schema_evolution" = "true"
ALTER TABLE your_table MODIFY COLUMN nested_data 
ADD FIELD sub_field.new_column INT AFTER existing_column;

The Shared-Data Catch: As of the current documentation (v3.3.x), the

MODIFY COLUMN ... ADD/DROP FIELD

syntax is only supported in shared-nothing clusters. In shared-data mode (which you are running), this specific DDL is not yet supported for

STRUCT

types. ────────── 2. Is
STRUCT
or
JSON
better for your case? For a Primary Key table with evolving nested Protobuf data in shared-data mode, the choice depends on your priority: Option A: The
JSON
Type + "Flat JSON" (Highly Recommended for Production) Starting from v3.3.3, StarRocks shared-data clusters support Flat JSON. * How it works: You define the column as

JSON

. At load time, StarRocks automatically detects common keys and "flattens" them into hidden sub-columns. * Performance: You get the performance of a

STRUCT

(columnar storage) for frequently used fields, but the flexibility of

JSON

. * Evolution: When your Protobuf schema adds a new field, you do nothing at the database level. StarRocks will simply start detecting and flattening the new key once it appears in the Stream Load. * Safety: 100% production-safe. No DDLs, no blocking operations, and no schema-matching errors during Stream Load. Option B: The
STRUCT
Type * Performance: Best performance for complex nested queries (even without Flat JSON). * Evolution: In shared-data mode, if you need to add a nested field, you would likely have to add a new top-level column or perform a more complex migration, as sub-field

ALTER

is restricted. * Risk:

STRUCT

is "brittle." If your Stream Load JSON contains a new field that isn't in your

STRUCT

definition, the load might fail or the data might be dropped depending on your

jsonpaths

and

ignore_json_size

settings. Summary Recommendation Since you are in shared-data mode and expect the schema to evolve: 1. Use the
JSON
data type. 2. Enable Flat JSON (it is enabled by default in v4.0+, but in v3.3.x you should ensure it's active). 3. Ensure
fast_schema_evolution
is enabled on your table properties. This combination gives you the "best of both worlds": the query speed of a structured schema and the zero-maintenance evolution of a semi-structured type. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/data-types/semi_structured/JSON.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/data-types/semi_structured/JSON.md • loading/StreamLoad.md

Rocky

07/22/2026, 12:35 PM

• table_design/table_types/primary_key_table.md References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md

Stas Grinberg

07/26/2026, 4:12 PM

@Rocky how would you recommend to read kafka trigger messages about ready scan data in iceberg table that now ready to be loaded to native primary key table. the iceberg tables are external catalog in starrocks and could have 300m records for biggest tables. the primary key tables also constantly have upserts with small streaming data

Rocky

07/26/2026, 4:12 PM

To handle 300M records from Iceberg while maintaining constant real-time upserts in a Primary Key (PK) table, the recommended architecture is an Event-Driven Orchestrator using

INSERT INTO SELECT

. Because StarRocks does not currently have a built-in "Kafka-to-Iceberg-to-Native" automation, you should use a lightweight Python script or an orchestrator like Airflow to bridge the gap. 1. The Recommended Workflow 1. Consume Kafka Trigger: Your Python consumer waits for a message indicating an Iceberg partition is ready. 2. Execute Bulk Load: The consumer issues an

INSERT INTO SELECT

statement to StarRocks via a standard MySQL/Postgres client. 3. Isolation: Ensure this bulk load runs on a dedicated Compute Group (separate from your real-time ingestion nodes) to avoid impacting query performance.

Copy code

sql
-- Example SQL executed by your orchestrator
INSERT INTO native_pk_table 
SELECT * FROM iceberg_catalog.db.source_table 
WHERE partition_key = '2023-10-27';

2. Performance & Optimization (The 300M Row Case) * Partition-by-Partition Loading: Never try to load 300M rows in a single SQL statement if you can avoid it. Ingesting by partition reduces memory pressure on the CN nodes and prevents the creation of massive, un-compacted versions. * Persistent Index: For PK tables of this scale, ensure

enable_persistent_index = true

is set in the table properties. This moves the Primary Key index from memory to disk (SSD), preventing OOM errors during the heavy

Delete+Insert

operations required by the bulk load. * Spill to Disk: Set

SET enable_spill_to_disk = true;

for the session performing the Iceberg read. This allows StarRocks to handle large joins or sorts in the

SELECT

part of the query without crashing if memory limits are hit. 3. Handling Concurrent Real-time Upserts StarRocks Primary Key tables are designed for high-concurrency writes. The bulk

INSERT INTO SELECT

and your small Stream Loads can run at the same time. * Atomicity: Each

INSERT INTO SELECT

is a single transaction. If it fails, no data is written. * The "Last Commit Wins" Rule: If a row with PK

is being updated by a Stream Load and the Iceberg load simultaneously, the version from the transaction that commits last will be the one stored in the table. * Compaction Management: Since you are in shared-data mode, monitor the "compaction score." A massive bulk load adds many data versions. If the score hits the

lake_ingest_slowdown_threshold

(default 100), your small streaming loads will see increased latency as StarRocks intentionally slows down to allow compaction to catch up. 4. Risks to Monitor 1. Transaction Limits: Ensure the number of concurrent partition loads doesn't exceed

max_running_txn_num_per_db

. 2. Object Storage Bandwidth: Since both Iceberg and your shared-data StarRocks tables likely live on S3/GCS, a 300M row move is IO-intensive. Check your cloud provider's bucket limits if you plan to run many of these in parallel. 3. Schema Mismatch: If your Iceberg schema evolves (Protobuf fields added), your

INSERT INTO SELECT

might fail unless you have

fast_schema_evolution

enabled and your SQL explicitly handles the mapping. Rocky's Pro-Tip: Use Compute Groups to separate these. Create an

ingest_bulk_cn

group for the Iceberg loads and an

ingest_realtime_cn

group for the Kafka Stream Loads. This ensures that the massive CPU/Memory spikes from processing 300M records don't block your tiny, high-frequency updates. References • table_design/table_types/primary_key_table.md • administration/management/resource_management/resource_group.md • loading/InsertInto.md

Rocky

07/26/2026, 4:12 PM

• data_source/catalog/iceberg_catalog.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • loading/Kafka-connector-starrocks.md • loading/Loading_intro.md • sql-reference/sql-statements/data-manipulation/INSERT.md • data_source/catalog/iceberg_catalog.md • deployment/shared_data/feature-support-shared-data.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md

Stas Grinberg

07/26/2026, 4:18 PM

@Rocky is it recommended to split the big batch from iceberg to chunks and load in parallel

Rocky

07/26/2026, 4:18 PM

For a 300M record batch from Iceberg into a StarRocks Primary Key (PK) table, the short answer is: splitting is recommended if you hit timeouts or memory limits, but you should split into "large partitions," not "small chunks." Here is the recommended strategy for your shared-data cluster: 1. Avoid "Micro-Chunking" Do not split the 300M rows into many small parallel jobs (e.g., thousands of small

INSERT

statements). * The Versioning Problem: Each

INSERT INTO

creates a new data version. In shared-data mode, if your "compaction score" exceeds 100, StarRocks will slow down all ingestion. Many small parallel loads will trigger this threshold much faster than one large load. * Compaction Pressure: Large batches are actually easier for StarRocks to compact. The system is designed to handle millions of rows in a single commit. 2. Recommended: Split by Iceberg Partition If 300M rows in a single

INSERT INTO SELECT

causes your CN nodes to OOM or hits the

insert_timeout

(default 4 hours), the best approach is to loop through your Iceberg partitions one by one (or 2–3 in parallel):

Copy code

sql
-- Loop through partitions in your orchestrator
INSERT INTO native_pk_table 
SELECT * FROM iceberg_catalog.db.large_table 
WHERE dt = '2023-10-01';

3. Production Configuration for 300M+ Rows To ensure the 300M row load doesn't crash your cluster or block your real-time Kafka upserts: * Enable Persistent Index: This is critical for PK tables at this scale. It moves the Primary Key index from CN memory to local disk (or object storage). * Set

enable_persistent_index = true

. * In shared-data mode (v3.3.2+), consider

persistent_index_type = CLOUD_NATIVE

to keep the index on S3/GCS so it's shared across CNs. * Isolate via Compute Groups: Run the Iceberg load on a dedicated CN pool. This ensures the heavy CPU usage of the "Iceberg Scan + PK Upsert" doesn't increase latency for your analytical queries or real-time Kafka stream loads. * Increase Timeouts: If you decide to do it in one or two giant batches:

Copy code

sql
    SET insert_timeout = 28800; -- Increase to 8 hours
    SET query_timeout = 28800;

* Spill to Disk: If your CN nodes have limited RAM, enable spilling for the session:

Copy code

sql
    SET enable_spill_to_disk = true;

4. How to monitor the load? While the load is running, use these commands to ensure you aren't hitting the "slowdown" threshold:

Copy code

sql
-- Check if compaction is falling behind (Shared-data mode)
SHOW PROC '/transactions/<your_db>/running';

If you see

ErrMsg

Partition's compaction score is larger than 100.0, delay commit

, it means you are loading too many chunks too fast. In that case, reduce parallelism and increase the size of each batch. Summary * Is it recommended to split? Yes, if the single 300M batch is unstable, but split into large logical blocks (like 10M–50M rows per batch/partition). * Parallelism? Keep it low (2–4 concurrent

INSERT INTO

tasks). Let StarRocks' internal pipeline engine handle the parallelism within the query rather than trying to manage too many concurrent transactions yourself. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • administration/management/compaction.md • loading/InsertInto.md • faq/loading/Insert_into_faq.md References • best_practices/primarykey_table.md • administration/management/compaction.md

Rocky

07/26/2026, 4:18 PM

• table_design/table_types/primary_key_table.md

Stas Grinberg

07/26/2026, 4:22 PM

@Rocky, the newest data in iceberg also haven't been compared. is it matter for inserts to starrocks or starrocks is responsible for his own compaction

Rocky

07/26/2026, 4:23 PM

It does not matter for the correctness of the data, but it does matter for the speed of the load. Here is the breakdown of how the two systems interact: 1. Does it matter that Iceberg is uncompacted? Yes, for Performance: If your Iceberg table has many small files or many "delete files" (positional/equality deletes) that haven't been compacted, StarRocks will have to work harder to read the data. * Metadata Overhead: StarRocks needs to scan more manifest files and metadata. * I/O Overhead: StarRocks needs to open and close many small files on S3/HDFS, which is much slower than reading a few large Parquet files. * Computation: If there are uncompacted deletes in Iceberg, StarRocks must resolve those deletes on-the-fly during the

SELECT

phase, which consumes more CPU on your CN nodes. No, for Data Integrity: Even if Iceberg is messy, StarRocks will read the "current state" of the Iceberg table correctly. If you are loading into a Primary Key table, StarRocks will use its own internal logic to ensure that if a row is updated twice in your batch, only the latest version is stored. 2. Is StarRocks responsible for its own compaction? Yes. Once the data leaves Iceberg and is written into a native StarRocks table via

INSERT INTO

, StarRocks is 100% responsible for its own management. * The Ingestion Process: When you run the

INSERT

, StarRocks writes new data segments to your object storage (S3/GCS). Each

INSERT

or Stream Load creates a "version." * Internal Compaction: StarRocks' Frontend (FE) automatically schedules background compaction tasks. It will pick up those new versions and merge them into larger, optimized segments. * Independence: StarRocks compaction is completely independent of Iceberg. Even if you never compact your Iceberg table, StarRocks will keep its own native data optimized and fast for queries. Recommendation If your Iceberg loading process is too slow, check the Profile of the

INSERT

statement. * If you see a very high

ScanTime

and thousands of

FileOpened

metrics, the "uncompacted" state of Iceberg is your bottleneck. _ In that case, running an Iceberg rewrite/compaction job _before* the StarRocks load will significantly speed up the ingestion. Summary: StarRocks manages its own health, but "garbage in" (uncompacted source) means "slow load." References • data_source/catalog/iceberg_catalog.md • administration/management/compaction.md • table_design/table_types/primary_key_table.md • deployment/shared_data/feature-support-shared-data.md shared-data) References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • deployment/shared_data/feature-support-shared-data.md

Stas Grinberg

07/30/2026, 9:07 AM

@Rocky ANALYZE TABLE org_xxx_detailed.spans insert statistic job failed due to too many failed tasks: 1/2, the last failure is com.starrocks.common.DdlException: Getting analyzing error. Detail message: Table column_statistics is not found. what does it means?

Rocky

07/30/2026, 9:08 AM

This error indicates that StarRocks' internal system for storing Cost-Based Optimizer (CBO) statistics is broken because it cannot find the mandatory system table

_statistics_.column_statistics

. When you run

ANALYZE TABLE

, StarRocks performs a background scan and then tries to

INSERT

those results into an internal table called

column_statistics

inside the

_statistics_

database. The error you see means the SQL planner failed to find that table during the internal insert. Why this happens 1. Cluster Initialization Issue: In some cases, particularly in Shared-Data mode or after an upgrade/deployment, the internal

_statistics_

database or its tables may fail to initialize properly. 2. Accidental Deletion: Although these are system databases, if a user with

root

privileges accidentally dropped the

_statistics_

database or the

column_statistics

table, the

ANALYZE

command will fail. 3. Metadata Inconsistency: The FE (Frontend) metadata might be out of sync or corrupted, leading it to believe the table doesn't exist even if it was there previously. How to Diagnose First, verify if the database and table exist:

Copy code

sql
-- Check if the database exists
SHOW DATABASES LIKE '_statistics_';

-- Check if the table exists inside it
SHOW TABLES FROM _statistics_ LIKE 'column_statistics';

How to Fix If the table or database is missing, you may need to recreate it. Caution: Manual modification of system databases should be done carefully. 1. Recreate the
_statistics_
database If

SHOW DATABASES

does not return

_statistics_

, try to create it (StarRocks usually handles the internal schema once the database exists):

Copy code

sql
CREATE DATABASE IF NOT EXISTS _statistics_;

2. Re-trigger Table Creation In some versions, restarting the Leader FE node forces a check of system tables and may recreate missing ones. 3. Manual Table Creation (If the database exists but the table is gone) If the database exists but the table is truly missing, you can attempt to recreate it using the internal schema. The schema for

column_statistics

is complex (it uses the Primary Key model). If you are using a standard version (e.g., 3.x), the table definition usually looks like this:

Copy code

sql
CREATE TABLE _statistics_.column_statistics (
    table_id      BIGINT       NOT NULL,
    partition_id  BIGINT       NOT NULL,
    column_name   VARCHAR(65533) NOT NULL,
    db_id         BIGINT       NOT NULL,
    table_name    VARCHAR(65533) NOT NULL,
    partition_name VARCHAR(65533) NOT NULL,
    row_count     BIGINT       NULL,
    data_size     BIGINT       NULL,
    ndv           BIGINT       NULL,
    null_count    BIGINT       NULL,
    max           VARCHAR(65533) NULL,
    min           VARCHAR(65533) NULL,
    update_time   DATETIME     NOT NULL
) ENGINE=OLAP
PRIMARY KEY(table_id, partition_id, column_name)
DISTRIBUTED BY HASH(table_id, partition_id, column_name)
PROPERTIES ("replication_num" = "3"); -- Adjust replication_num to your cluster's default

Note: The exact schema can vary slightly between versions (e.g., v2.x vs v3.x). It is highly recommended to contact StarRocks support or the community Slack if you are unsure of your specific version's system schema. Summary * The Error: Your cluster is missing the system table where it saves statistics. * Immediate Action: Check if

_statistics_.column_statistics

exists. * Resolution: Restart the Leader FE or manually recreate the

_statistics_

database/table. References • using_starrocks/Cost_based_optimizer.md • sql-reference/information_schema/statistics.md • sql-reference/information_schema/column_stats_usage.md

Stas Grinberg

08/03/2026, 7:18 AM

@Rocky how many parallel queries we can run against 3 CNs and 3 FE without loosing performance response time

Rocky

08/03/2026, 7:18 AM

There is no single "magic number" because it depends entirely on your query complexity and hardware specs, but for a standard 3 CN + 3 FE setup, here are the general benchmarks and how to determine your limit: 1. Estimated Capacity by Query Type * Simple Point Lookups (e.g., PK Table, Row-Store): You can reach 10,000+ QPS (Queries Per Second). In this scenario, the bottleneck is usually the FE's CPU for parsing or network RPC, not the CNs. * Medium Analytical Queries (e.g., small joins, filters on indexed columns): Typically 100–500 concurrent queries. * Heavy OLAP/ETL Queries (e.g., large joins, 100M+ row scans): Usually 5–20 concurrent queries. Heavy queries consume significant CPU and Memory; running too many at once leads to "Resource Exhaustion" (OOM) or massive context-switching overhead. 2. How to Protect Performance To ensure you don't "lose performance" (i.e., prevent a sudden spike in latency), you should use Resource Groups and Query Queues (available in v2.5+): * Global Concurrency Limit: Set

query_queue_concurrency_limit

(default is 0/unlimited). For 3 CNs, a safe starting point is often

3 * (CPU cores per CN)

. * Resource Groups: Create separate groups for "High-Frequency API" queries and "Heavy Analysis."

Copy code

sql
    CREATE RESOURCE GROUP 'api_group'
    PROPERTIES (
        "cpu_core_limit" = "10",
        "mem_limit" = "0.2", -- 20% of total memory
        "concurrency_limit" = "100" -- Block/Queue queries after 100
    );

3. Scaling FEs vs. CNs * FE (Frontends): 3 FEs are excellent for High Availability. If you find your FE CPU is high while CNs are idle, it means the FE is struggling with query planning or metadata. You can add Observer FEs to scale this further without affecting the Raft quorum. * CN (Compute Nodes): Scaling from 3 to 6 CNs will linearly increase your throughput for heavy analytical queries. 4. Important Tuning for High Concurrency If you plan to run high concurrency (hundreds of parallel requests), apply these settings: 1. Enable Pipeline Engine: (Default in 3.x) It handles many small tasks much better than the old engine. 2. Adjust RPC: Set the BE/CN config

brpc_connection_type = pooled

to reduce network overhead under high concurrency. 3. Client-Side: Use a connection pool (like HikariCP) to keep connections open, as FE connection overhead can slow down response times. Summary Recommendation Start by testing with 50-100 parallel threads. * If your CPU usage is < 60% and latency is stable, increase by 50. * If you see "Memory Limit Exceeded" or latency spikes, enable Query Queues to cap the concurrency at the current level. This ensures that extra queries wait in a queue rather than crashing the nodes or slowing down everyone else. References • administration/management/resource_management/query_queues.md • administration/management/resource_management/resource_group.md • faq/Others.md • deployment/plan_cluster.md References • administration/management/resource_management/query_queues.md • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • best_practices/audit_log_resource_group.md

Stas Grinberg

08/05/2026, 6:52 AM

@Rocky we use starrocks to load data from external catalog (iceberg table) to starrocks primary key table but we must run on iceberg table for most updated snapshot. is there any way to sync FE for specific table from iceberg before we do the load do not the general 5 minutes sync?

Rocky

08/05/2026, 6:52 AM

Yes, you can manually trigger a metadata synchronization for a specific Iceberg table to avoid waiting for the periodic background refresh. 1. Manual Refresh Command The most direct way to sync the Frontend (FE) metadata for a specific Iceberg table is to use the

REFRESH EXTERNAL TABLE

command. This forces StarRocks to clear its cached metadata (like the current snapshot ID and manifest locations) and fetch the latest state from the Iceberg catalog (Hive Metastore, Glue, etc.). Syntax:

Copy code

sql
REFRESH EXTERNAL TABLE <catalog_name>.<database_name>.<table_name>;

Example:

Copy code

sql
REFRESH EXTERNAL TABLE iceberg_catalog.analytics_db.user_events;

After running this command, the next

SELECT

INSERT INTO ... SELECT

statement will use the absolute newest snapshot available in Iceberg. 2. Tuning Periodic Sync If you find yourself needing to do this frequently, you can also reduce the global refresh interval for the Iceberg catalog. The "5 minutes" you mentioned is likely the default

background_refresh_metadata_interval_millis

. You can modify this interval at the FE level:

Copy code

sql
-- Set interval to 1 minute (60,000 ms)
ADMIN SET FRONTEND CONFIG ("background_refresh_metadata_interval_millis" = "60000");

3. Alternative: Bypass Cache (Catalog Level) If you require "real-time" metadata and the

REFRESH

command is not integrated into your pipeline, you can disable the metadata cache for that specific catalog. This ensures StarRocks always fetches the latest snapshot from the source, though it will add a slight delay (latency) to the query planning phase.

Copy code

sql
ALTER CATALOG <iceberg_catalog_name> SET ("enable_iceberg_metadata_cache" = "false");

Important Considerations * Privileges: You must have the

ALTER

privilege on the external table to execute the

REFRESH

command. * Load Performance: If you are loading data from Iceberg into a StarRocks Primary Key table, remember that even if you have the latest snapshot, if the Iceberg table has many uncompacted "delete files," the load process will be slower as StarRocks must resolve those deletes on-the-fly. Summary: Run

REFRESH EXTERNAL TABLE catalog.db.table;

immediately before your

INSERT

statement to ensure your load uses the latest data. References • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md • administration/management/resource_management/query_queues.md • data_source/catalog/iceberg/iceberg_meta_table.md References • data_source/catalog/maxcompute_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/hudi_catalog.md • data_source/catalog/iceberg/iceberg_meta_table.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md

Stas Grinberg

08/05/2026, 3:22 PM

@Rocky _we're doing

INSERT INTO <native_pk_table> (col1, col2, ...) SELECT ... FROM <external_iceberg_catalog_table>

, explicitly listing every real column (not a subset) plus

PROPERTIES("merge_condition" = "event_timestamp")

, to get the same conditional-update behavior Stream Load's

merge_condition

header gives us today (reject an incoming row if its

event_timestamp

isn't ≥ what's already stored for that PK). Docs say specifying a column list on a PK table triggers "Partial Update" mode instead of "Full Upsert" — since we're listing every column anyway, does

merge_condition

still behave identically to the Full-Upsert/Stream-Load case, or is there a gotcha when Partial Update + merge_condition are combined even when the column list happens to cover every column?_

Rocky

08/05/2026, 3:23 PM

In short: Yes, it behaves identically, provided you are on StarRocks v3.1.3 or later. Even though specifying a column list triggers "Partial Update" mode (starting in v3.3.1), since your column list is exhaustive (covers every column), the end result is functionally identical to a Full Upsert. Here is the breakdown of how these features interact and the few "gotchas" to keep in mind: 1. Version Compatibility * Before v3.1.3: Partial Updates and

merge_condition

were mutually exclusive. Attempting to use both would result in an error or ignored properties. * v3.1.3 and later: StarRocks explicitly added support for combining Partial Updates with

merge_condition

. * v3.3.1 and later: Specifying a column list in an

INSERT INTO

statement on a Primary Key table automatically triggers the Partial Update internal logic. 2. Behavior with Exhaustive Column Lists If you specify every single column in the table within your

INSERT INTO

list: * If the row exists: StarRocks checks the

merge_condition

. If

incoming_timestamp >= existing_timestamp

, it updates the row. Since your list includes all columns, every column is overwritten with the new data—exactly like a Full Upsert. * If the row does not exist: StarRocks inserts the new row. Since you provided all columns, no default values are needed, and the row is created exactly as provided. 3. Are there any "Gotchas"? Since you are explicitly listing every column, you have avoided the most common pitfalls, but here are the remaining ones: * Column Coverage: Ensure that the column specified in

merge_condition

(e.g.,

event_timestamp

) is actually included in your

INSERT INTO (...)

column list. If it’s missing from the list, the condition cannot be evaluated against the incoming data. * Internal Execution Mode: In "Partial Update" mode, StarRocks may use a different execution path (often "row mode" which involves a read-before-write to check the existing version). However, because you are using

merge_condition

, StarRocks must perform a lookup of the existing record anyway to compare the timestamps. Therefore, there is no significant performance "penalty" for being in Partial Update mode versus Full Upsert mode when

merge_condition

is active. * Implicit vs. Explicit: If you want to avoid any ambiguity about "Partial Update" mode, you can technically omit the column list:

Copy code

sql
    INSERT INTO <native_pk_table>
    PROPERTIES("merge_condition" = "event_timestamp")
    SELECT * FROM <external_iceberg_table>;

This triggers a Full Upsert by default. However, your current approach of explicitly listing columns is safer for long-term schema evolution (e.g., if columns are added to the Iceberg table that you don't want in StarRocks yet). Summary Your logic is sound. As long as you are on v3.1.3+, the

merge_condition

will correctly reject "older" rows based on your timestamp, even if the

INSERT

is technically processed through the Partial Update internal framework due to the presence of a column list. References • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/System_variable.md (enable_insert_partial_update)) • loading/Spark-connector-starrocks.md References • loading/Load_to_Primary_Key_tables.md • loading/Spark-connector-starrocks.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md

Stas Grinberg

08/05/2026, 5:00 PM

@Rocky — we're now doing

INSERT INTO <native_pk_table> PROPERTIES("merge_condition"="event_timestamp") SELECT ... FROM <external_iceberg_catalog>.ns.table WHERE integration_id=? AND scan_id=? AND batch_timestamp BETWEEN ? AND ?

, single statement, 40M–90M rows per run, no sharding, any tuning on this?

Rocky

08/05/2026, 5:00 PM

Loading 40M–90M rows in a single

INSERT INTO ... SELECT

statement from an external Iceberg catalog into a Primary Key (PK) table is a heavy operation. To ensure this runs reliably on your 3-CN cluster without hitting timeouts or memory limits, apply the following tuning strategies: 1. Increase Transaction Timeouts Large loads often exceed the default 1-hour transaction limit. Set these in your session before running the load:

Copy code

sql
-- Increase timeout to 8 hours (28800 seconds)
SET insert_timeout = 28800;
-- Ensure the FE doesn't time out the plan
SET new_planner_optimize_timeout = 30000;

2. Primary Key Table Memory Tuning Loading 90M rows into a PK table requires significant memory to track the "Primary Key Index." * Enable Persistent Index: This is mandatory for large updates to prevent Out-Of-Memory (OOM) errors. It offloads the PK index from RAM to disk (SSD recommended).

Copy code

sql
    ALTER TABLE <native_pk_table> SET ("enable_persistent_index" = "true");

* Memory Limit: If the load fails with "Memory limit exceeded," increase the session limit:

Copy code

sql
    SET query_mem_limit = 16 _ 1024 _ 1024 * 1024; -- 16GB or more depending on CN RAM

3. Optimize the Iceberg Scan Since your query has specific filters (

integration_id

scan_id

batch_timestamp

), ensure StarRocks is pruning data effectively: * Parallelism: Increase the number of scan threads to speed up reading from S3/HDFS.

Copy code

sql
    SET pipeline_dop = 0; -- Allows StarRocks to auto-scale parallelism based on CPU cores

* Predicate Pushdown: Use

EXPLAIN

to verify that your

WHERE

clauses are actually being pushed down to the Iceberg scan. You should see

IcebergScanNode

with

PREDICATES

. * Metadata Freshness: (As discussed before) ensure you run

REFRESH EXTERNAL TABLE

right before this load so the plan uses the latest Iceberg manifest files. 4. Enable "Spill to Disk" For 90M rows, the data shuffle (moving rows from the scan nodes to the nodes owning specific PK buckets) can be memory-intensive. Enabling intermediate spilling can prevent OOM:

Copy code

sql
SET enable_spill = true;

5. Internal Load Tuning (CN Config) If you have access to the

cn.conf

(Compute Node configuration), you can tune the writer buffer to handle the "Merge Condition" logic more efficiently: * `write_buffer_size`: Increasing this (e.g., to

209715200

for 200MB) can reduce the number of small files generated during the load, which speeds up the final commit. * `transaction_apply_worker_count`: If CPU is under-utilized during the load, increasing this allows more threads to process the PK upsert logic concurrently. Summary Strategy 1. Mandatory: Enable

persistent_index

on the target table. 2. Session: Run

SET insert_timeout = 28800; SET query_mem_limit = <high_value>; SET enable_spill = true;

. 3. Monitor: Check the StarRocks Web UI (FE port 8030) under "Queries" or "Load" to see the progress. If it's still slow, check for "Lock Wait" or "Compaction Score" in the

show proc '/transactions/<db>/running'

output. References • loading/InsertInto.md • best_practices/primarykey_table.md • faq/loading/Insert_into_faq.md • loading/loading_introduction/loading_considerations.md • data_source/catalog/iceberg/iceberg_catalog.md References • loading/InsertInto.md • best_practices/primarykey_table.md • faq/loading/Insert_into_faq.md

Rocky

08/05/2026, 5:00 PM

• loading/loading_introduction/loading_considerations.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/System_variable.md • administration/management/resource_management/query_queues.md • faq/loading/Loading_faq.md • data_source/feature-support-data-lake-analytics.md • unloading/Spark_connector.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md • unloading/Spark_connector.md

Stas Grinberg

08/05/2026, 5:17 PM

@Rocky we are going to do that: 1. A way to split one trigger's rows into disjoint chunks. Since PKs are unique per row, the cleanest approach is a hash-mod filter on the primary key column added to the

WHERE

clause, e.g.: ... AND murmur_hash3_32(pk_id) % 4 = 0 -- shard 0 of 4 This gives a roughly even ~25%-each split, and because chunks are disjoint by PK, there's no write conflict between them even with

merge_condition

upserts running concurrently. 2. Loop vs. parallel — different trade-offs: • Sequential loop(4 INSERTs, one after another): simple, each statement handles less data (helps with the OOM/timeout risk • Parallel(4 concurrent`aiomysql`connections via`asyncio.gather`, each running one shard): actually reduces wall-clock time by spreading the load across your 3 CNs. But it multiplies concurrent load on the cluster — and since multiple worker Jobs (different accounts/triggers) can_already_run concurrently in dispatch mode, sharding-in-parallel on top of that means e.g. 5 simultaneous triggers × 4 shards = 20 concurrent big scans hitting the same 3-CN cluster instead of 5.

Rocky

08/05/2026, 5:17 PM

This is a solid architectural approach for handling high-volume updates on a small (3-CN) cluster. Below is a critique of your strategy, along with specific StarRocks-specific "gotchas" and optimizations. 1. The Sharding Logic: Hash vs. Partitioning Your

murmur_hash3_32(pk_id) % 4

approach is logically correct for creating disjoint sets, but there is a performance "tax": * Scan Amplification (The "Gotcha"): When you run 4 shards using a hash filter, StarRocks has to scan the entire Iceberg table (or at least the columns in your

WHERE

and

SELECT

clauses) four separate times. For 90M rows, this results in significant S3/HDFS I/O and CPU overhead. * Recommendation: If your Iceberg table is partitioned by a column (e.g.,

date

region

), it is much more efficient to shard your

INSERT

statements by those partitions. _ _Example:*

WHERE date = '2023-10-01'

(Shard 1),

WHERE date = '2023-10-02'

(Shard 2). * This allows StarRocks to use Partition Pruning, reading only the necessary files for each shard. If you must use the hash approach: * Use `ABS()`:

murmur_hash3_32

can return negative integers. Use

ABS(murmur_hash3_32(pk_id)) % 4

to ensure your buckets are calculated correctly. * Better Hash: If you are on v3.2+, use

XX_HASH3_64(pk_id)

. It is faster and better optimized for modern CPUs (AVX2). 2. Sequential vs. Parallel: The 3-CN Bottleneck With only 3 Compute Nodes (CNs), your primary bottleneck will be Memory (for the PK Index) and Disk I/O (for Compaction). Scenario A: Sequential (Safer) * Why: Each

INSERT

gets the full resources of the 3 CNs. It minimizes the risk of "Compaction Score" errors (where the BE/CN stops accepting writes because it can't keep up with merging the new data). * Tuning: Since you're doing 40-90M rows, ensure

enable_persistent_index = true

is set on the StarRocks table. This offloads the PK index to disk, preventing OOMs during these large batches. Scenario B: Parallel (Risky but Faster) * The Conflict Myth: You mentioned "no write conflict." While there is no data conflict (because PKs are disjoint), there is lock contention. In StarRocks PK tables, a write lock is held at the tablet level during the "apply" phase. If your parallel shards happen to hit the same tablets (which is likely if they share the same bucket key), they will queue up behind each other during the final commit phase anyway. * Cluster Stress: 4 parallel scans of 90M rows = 360M rows worth of S3 traffic hitting 3 CNs at once. This might saturate your network bandwidth or disk throughput. 3. Recommended Hybrid Strategy Instead of 4-way parallel, I recommend a "2x2" approach or a controlled sequential loop: 1. Prioritize Partitioning: If you can split the Iceberg load by a partition column, do that first to avoid the 4x scan penalty. 2. Persistent Index: Ensure this is ON. It is the single biggest factor in preventing OOMs for 90M row PK loads. 3. Intermediate Commit (Sequential): Use a loop. If Shard 1 takes 5 minutes, Shard 2 starts immediately after. This keeps the "Compaction Score" healthy. If you run them all at once, you might hit the

too many versions

error, causing the entire 90M row job to fail. 4. Transaction Groups: If you use

asyncio.gather

, limit the concurrency (semaphore) to 2. This provides some parallelism (to utilize idle CPU during S3 wait times) without overwhelming the 3-node cluster's write buffers. Summary of Tuning Commands for this Strategy:

Copy code

sql
-- Run these before your loop/parallel tasks
SET insert_timeout = 36000;
SET query_mem_limit = 16106127360; -- 15GB
SET enable_spill = true;

-- On the table itself
ALTER TABLE <your_pk_table> SET ("enable_persistent_index" = "true");

Verdict: Go with a Sequential Loop (or Concurrency = 2) and try to replace the

HASH % 4

with Iceberg Partition filters to save on massive I/O costs. References

Rocky

08/05/2026, 5:17 PM

• best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md

Stas Grinberg

08/06/2026, 4:25 AM

@Rocky Hey Rocky — quick sanity check before we build this. We're adding a delete/reconciliation step after our existing partition-pruned INSERT (per your earlier sharding advice), to clean up rows no longer present upstream (source only sends current-state snapshots, never deletes). Per trigger, 4 statements against one PK table (resources, ~250-300M rows, shared-data, single PK resource_id, no PARTITION BY): -- 1. scratch table: just the PK, nothing else CREATE TABLE org_123_detailed._reconcile_staging_resources_<scan_id> ( resource_id VARCHAR(256) NOT NULL ) DUPLICATE KEY(resource_id) DISTRIBUTED BY HASH(resource_id) BUCKETS 8; -- 2. fill it from the external Iceberg catalog, same partition-pruned filters as our real INSERT INSERT INTO org_123_detailed._reconcile_staging_resources_<scan_id> SELECT resource_id FROM org_123_detailed.ctem.resources WHERE integration_id = ? AND scan_id = ? AND batch_timestamp >= ? AND batch_timestamp < ?; -- 3. anti-join delete: native-vs-native, no second Iceberg read DELETE FROM org_123_detailed.resources t WHERE t.integration_id = ? AND NOT EXISTS (SELECT 1 FROM org_123_detailed._reconcile_staging_resources_<scan_id> s WHERE s.resource_id = t.resource_id); -- 4. cleanup DROP TABLE org_123_detailed._reconcile_staging_resources_<scan_id>; Notes: resource_id isn't in the target's PARTITION BY (there isn't one), so step 3 filters by integration_id only — no partition pruning available target-side. This can match/delete millions of rows in one DELETE. We separately gate the whole thing on a watermark check (skip if this scan is older than the last one we reconciled), so step 3 only ever runs once per scan, in order. Is this a sound/efficient pattern at that scale on a PK table, or would you recommend partitioning resources too, batching the delete, or something else entirely?

Rocky

08/06/2026, 4:26 AM

This is a sound pattern for reconciliation (a "diff-based delete"), and you’ve already hit on a key optimization by using the same

DISTRIBUTED BY HASH(resource_id)

for your staging table. This ensures a Colocated Join during the

DELETE

step, which avoids shuffling millions of rows across the network. However, at the scale of 300M rows on a 3-CN cluster, there are a few "gotchas" and a more efficient "Metadata" alternative you should consider. 1. The Colocated Join Advantage Your current step 3 (

DELETE ... WHERE NOT EXISTS

) will perform a Local Hash Anti-Join. Since both tables are hashed on the same key (

resource_id

), each CN only compares its local data. This is the most efficient way to run a join-based delete. 2. The "Gotcha": Compaction & Versioning Deleting millions of rows on a Primary Key table without partitions creates a heavy load on the Compaction process: * Delete Markers: Each deleted row creates a "tombstone" in the Delete Vector. * Global Compaction: Because there are no partitions, compaction must eventually merge these markers across the entire 300M-row table. * Risk: If you run these reconciliation scans frequently (e.g., every few minutes), you may hit a

too many versions

error or see a spike in "Compaction Score" which will eventually throttle your

INSERT

performance. 3. A More Efficient Alternative: The "Metadata Logic" If your source always sends a full current-state snapshot, you can avoid the staging table and the expensive anti-join entirely by adding a

last_scan_id

(or

batch_id

) column to your target table. The Pattern: 1. Upsert:

INSERT INTO target (..., scan_id) SELECT ..., <current_scan_id> FROM iceberg ...

_ This updates all _existing* rows to the new

scan_id

and inserts new ones. 2. Reconcile (Delete):

DELETE FROM target WHERE integration_id = ? AND scan_id < <current_scan_id>;

* Anything still carrying an old

scan_id

for that integration is, by definition, missing from the latest snapshot. Why this is usually better: * No Staging Table: Saves the I/O of writing 90M rows to a temporary table. * Simple Filter vs. Join: A

DELETE

based on a scalar comparison (

scan_id < X

) is significantly lighter on CPU and memory than a Hash Anti-Join against 90M rows. * Atomicity: You can even wrap these in a single transaction if needed. 4. Critical Tuning for your Scale Regardless of which method you choose, these settings are mandatory for a 3-CN cluster at this volume: * Enable Persistent Index: Without this, the PK index for 300M rows + a 90M row join will almost certainly OOM your CNs.

Copy code

sql
    ALTER TABLE resources SET ("enable_persistent_index" = "true");

* Memory Limit: Ensure the

DELETE

query has enough headroom.

Copy code

sql
    SET query_mem_limit = 16 _ 1024 _ 1024 * 1024; -- 16GB+

* Batching the Delete? You asked if you should batch. If you use the

NOT EXISTS

join, batching is difficult. If you use the

scan_id < X

logic, you can easily batch by adding a hash-mod filter (similar to your previous sharding idea) to the

DELETE

statement if it’s too heavy for a single transaction. Recommendation * If you can change the schema: Use the Metadata Logic (
scan_id
). It is the standard "best practice" for snapshot reconciliation in StarRocks. * If you cannot change the schema: Your current staging table approach is the next best thing. Just ensure Persistent Index is ON and monitor the

Compaction Score

using

SHOW PROC '/statistic';

to ensure the 3-CN cluster is keeping up with the deletions. References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

Rocky

08/06/2026, 4:26 AM

Stas Grinberg

09/03/2026, 12:17 PM

@Rocky , another question — this time about Iceberg compaction. We've got a small-file problem on one of our tenants: two tables with ~879,000 files holding only ~57GB total (so averaging around 65KB/file). No compaction or snapshot-expiry job exists for these tables today. We're deciding between two paths: using StarRocks' native `rewrite_data_files`/`rewrite_manifests`/`expire_snapshots` procedures (v4.0), or running compaction externally via Spark on EMR. One extra wrinkle: this is the same tenant behind the FE memory/GC issue we asked you about earlier, so we're wary of adding more load onto that FE while it's still unstable. What would you recommend — StarRocks-native compaction, external Spark/EMR, or something else? And is there anything about the

rewrite_data_files

procedure we should know at this scale (largest tables we'd run it against are in the hundreds-of-thousands-of-files range) before trying it?

Rocky

09/03/2026, 12:18 PM

Given your current FE instability and the scale of the problem (~900k files), I recommend running this initial heavy-lift compaction externally via Spark (EMR) rather than using the StarRocks native procedures. Once you have "cleared the debt" and reduced the file count to a healthy range (files in the hundreds or low thousands), you can then switch to StarRocks' native procedures for ongoing maintenance. Why External (Spark/EMR) is safer right now 1. FE Metadata Pressure: With ~879,000 files, even a simple

SELECT

can tax an unstable FE. StarRocks' native

rewrite_data_files

must first parse the metadata for all these files to plan the compaction. On a cluster already struggling with GC/memory, this planning phase could trigger a "Stop the World" GC or an OOM. 2. Commit Overhead: Each compaction "batch" in StarRocks (default 10GB) requires a commit to the Iceberg catalog. If you have 900k small files, the metadata rewrite at the end of the job will be massive. Spark is historically more robust at handling the "commit conflict" and metadata serialization at this specific scale. 3. Isolation: By using EMR, you offload the heavy CPU (shuffling/merging) and Memory (metadata parsing) costs entirely away from your 3-CN StarRocks cluster. If you decide to use StarRocks Native (
v4.0+
) If you must use StarRocks, do not run a single global command. Use these guardrails to protect your FE: * Shard by Partition: Use the

WHERE

clause to compact one partition (or a range) at a time. This keeps the metadata handled by the FE in each request much smaller.

Copy code

sql
    ALTER TABLE iceberg_table EXECUTE rewrite_data_files()
    WHERE event_date = '2023-01-01';

* Aggressive File Filtering: The default

min_file_size_bytes

is 256MB. Since your files are ~65KB, you should set a smaller target for the first run to keep batches manageable. * Monitor the FE: Watch

QueryFEAllocatedMemory

and the

fe.gc.log

. If you see the FE memory spiking during the planning phase (before any data actually moves), abort and move to Spark. Recommended Recovery Plan 1. Phase 1 (External): Run a Spark job (e.g.,

spark.actions().rewriteDataFiles(table)

) on EMR. Use the

bin-pack

strategy. This will collapse your 879k files into ~120-200 large files. 2. Phase 2 (Metadata Cleanup): Run

expire_snapshots

and

remove_orphan_files

via StarRocks. These are metadata-only and are generally safer for the FE than a full data rewrite.

Copy code

sql
    -- Retain only the last 2 days of history to purge old small-file metadata
    ALTER TABLE iceberg_table EXECUTE expire_snapshots(retain_last = 5);

3. Phase 3 (Native Maintenance): Once the table is healthy, schedule a weekly

rewrite_data_files

and

rewrite_manifests

inside StarRocks to prevent the "small file debt" from accumulating again. Summary: Your FE is currently too "brittle" to handle a 900k-file metadata parse. Use Spark for the initial cleanup to give your StarRocks cluster room to breathe. References • data_source/catalog/iceberg/procedures.md • knowledge/trouble_shooting/fe_memory_problems.md • faq/operation_maintenance_faq.md • administration/management/compaction.md • administration/management/FE_configuration.md • faq/Others.md

Stas Grinberg

09/06/2026, 1:03 PM

@Rocky, what is the best practice for backups the starrocks data in production?

Rocky

09/06/2026, 1:03 PM

Best practices for backing up a production StarRocks cluster depend on your architecture (Shared-nothing vs. Shared-data). A complete production backup strategy must cover three layers: Data, Metadata, and Users/Privileges. 1. Data Backup (Internal Tables) For standard "Shared-nothing" clusters, use the native

BACKUP

and

RESTORE

commands. * Repository First: Create a remote repository (S3, HDFS, or GCS) to store snapshots.

Copy code

sql
    CREATE REPOSITORY `s3_backup`
    WITH BROKER `broker_name` -- Or direct S3 properties in v3.x+
    ON LOCATION "<s3://my-bucket/starrocks_backups>"
    PROPERTIES ("aws.s3.access_key" = "...", "aws.s3.secret_key" = "...");

* Shard Large Tables: For tables >100GB, do not back up the entire database in one command. Instead, back up by Partition. _ _Reason:* If a 10TB backup fails at 90%, you have to restart the whole job. Backing up one month or one week of data at a time allows for easier retries. * Incremental Logic: Since StarRocks does not have an automated "incremental" flag, implement this by backing up only the newest partitions (e.g.,

PARTITION (p202310)

) in your daily cron job. * Low-Load Window: Backups generate snapshots on BE nodes, which consumes CPU and Disk I/O. Schedule these during off-peak hours. 2. FE Metadata Backup (Critical) The

BACKUP

command only saves the data snapshots; it does not protect the cluster's core state (schemas, load jobs, etc.) if the entire cluster's FE nodes are lost. * Backup `meta_dir`: Regularly (daily) back up the

meta/

directory of your Leader FE node. * Automate Images: Ensure

ALTER SYSTEM CREATE IMAGE;

is running periodically (default is every 50,000 journal entries) so that your metadata directory contains a condensed snapshot. * Off-site Storage: Ship the compressed

meta/

directory to a different region or bucket than your data backups. 3. Shared-Data (Cloud-Native) Specifics If you are using a Shared-data cluster (v3.0+), the standard

BACKUP

command is currently not supported. * Disaster Recovery: Shared-data stores everything in S3/HDFS. Your "backup" is the durability of the object store itself. * Metadata Rollback: StarRocks provides a "second-level recovery" mechanism. If data is corrupted or accidentally deleted, use

ADMIN REPAIR TABLE

to roll back the tablet metadata to a previous healthy version. * Object Storage Versioning: Enable versioning or object-locking on your S3 bucket to prevent accidental deletion of the underlying data files. 4. User and Privilege Backup The

BACKUP

command does not include users, roles, or resource groups. * Manual Export: Periodically run a script to export your RBAC configuration. A common practice is a bash script that iterates through

SHOW USERS

and runs

SHOW GRANTS FOR <user>

for each. * Git-Ops: Keep your

CREATE USER

and

GRANT

DDLs in a version-controlled repository (Git) so you can recreate the security layer on a new cluster instantly. 5. Performance Tuning (BE level) If your backups are too slow, adjust these in `be.conf`: * `upload_worker_count`: Increase this (default is CPU cores) to speed up S3 uploads. * `make_snapshot_worker_count`: Increase this to speed up local snapshot creation before the upload starts. Summary Checklist: 1. Repository: S3/HDFS setup. 2. Frequency: Daily for metadata; Partition-based for large data. 3. Validation: Perform a

RESTORE

to a test cluster at least once a quarter to ensure your snapshots are actually valid. References • administration/management/Backup_and_restore.md • administration/Meta_recovery.md • administration/user_privs/authorization/privilege_faq.md

Rocky

09/06/2026, 1:03 PM

• administration/management/resource_management/Replica.md References • administration/user_privs/authorization/privilege_faq.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/privilege_item.md • unloading/Export.md

Stas Grinberg

09/06/2026, 1:28 PM

@Rocky starrocks supports time travel in time for native tables?

Rocky

09/06/2026, 1:28 PM

Currently, StarRocks does not support native Time Travel (using SQL syntax like

FOR SYSTEM_TIME AS OF

AS OF TIMESTAMP

) for its internal/native tables (Primary Key, Duplicate Key, etc.). However, the answer depends on which table type you are using and your specific use case: 1. Iceberg Tables (External Catalog) If you use the Iceberg Catalog, StarRocks does support full Time Travel starting from v3.4.0. You can query historical snapshots using:

Copy code

sql
-- Query by snapshot ID
SELECT * FROM iceberg_table FOR VERSION AS OF '123456789';

-- Query by timestamp
SELECT * FROM iceberg_table FOR TIMESTAMP AS OF '2024-01-01 12:00:00';

2. Native Duplicate Key Tables Duplicate Key tables are append-only. They do not overwrite data; they just keep adding rows. While there isn't a special "Time Travel" keyword, you can effectively perform time travel by including a timestamp column in your table design and filtering on it:

Copy code

sql
-- "Time Travel" to a specific point in time by filtering on your own business timestamp
SELECT * FROM logs_table WHERE event_time <= '2024-08-01 00:00:00';

3. Native Primary Key Tables Primary Key (PK) tables use a "Delete+Insert" strategy. Once a row is updated or deleted, the old version is marked for deletion and eventually physically removed during Compaction. * There is no user-facing feature to query a specific historical version of a Primary Key table. * Once a transaction is committed and compaction occurs, the old versions are gone. 4. ASOF JOIN (v4.0+) If your goal is to find the "closest matching historical record" (e.g., matching a trade to the most recent stock price), StarRocks introduced ASOF JOIN in v4.0. While not true "Time Travel" for a single table, it is the standard way to handle time-series reconciliation. Summary Feature | Native Tables (PK/Dup) | Iceberg Catalog VERSION AS OF
| ❌ No | ✅ Yes (v3.4+) TIMESTAMP AS OF
| ❌ No | ✅ Yes (v3.4+) Historical Data | Only in Duplicate Key tables via manual filters | Full Snapshot support Recommendation: If Time Travel is a hard requirement for your architecture (e.g., for data auditing or reproducing reports), the best practice is to store that data in an Iceberg format and query it via the StarRocks Iceberg Catalog. References • best_practices/query_tuning/query_hint.md • sql-reference/System_variable.md

Stas Grinberg

09/06/2026, 1:30 PM

@Rocky no new versions of starrocks supported time travel?

Rocky

09/06/2026, 1:30 PM

As of the latest releases (including v4.0 and v4.1), StarRocks does not yet support native Time Travel (the

FOR SYSTEM_TIME AS OF

syntax) for its internal OLAP tables (Primary Key or Duplicate Key tables). However, there is significant movement on this front in the 2026 roadmap: 1. The Current State (v4.0 / v4.1) * Iceberg Catalog: Fully supports Time Travel using snapshots or timestamps (since v3.4). * Native Tables: Still lack native point-in-time query capabilities. _ You can recover a _dropped* table using the Recycle Bin (

RECOVER TABLE

), but you cannot query a table as it appeared 2 hours ago. * Shared-data (Cloud-native) clusters have a metadata recovery mechanism, but it is an administrative "repair" operation, not a SQL-level Time Travel feature. 2. Roadmap: Native Time Travel is Coming According to the StarRocks 2026 Roadmap, "Time travel & enhanced snapshot" is a priority feature specifically for Native Storage (shared-data clusters). * Expected Behavior: This will likely allow you to query native StarRocks tables at a specific point in time or snapshot ID, similar to how you currently query Iceberg tables. * Architecture Requirement: This feature is primarily targeted at the shared-data (cloud-native) architecture, as the underlying object storage (S3/GCS) makes versioned snapshot management much more efficient than the shared-nothing (local disk) model. 3. Workarounds if you need it today If you are on v4.0 and need "time-travel-like" functionality for native data: * Duplicate Key + Timestamp: Use a

created_at

column and always include a filter (

WHERE created_at <= '...'

). This is the most common pattern for auditing. * Manual Snapshots (Shared-Nothing): You can use the

BACKUP

command to create named snapshots in S3/HDFS. To "time travel," you would have to

RESTORE

that snapshot to a new temporary table. * External Tables: If point-in-time auditing is a strict requirement for a specific dataset, the best practice remains writing that data to Iceberg via StarRocks and querying it through the Iceberg Catalog. Summary: You aren't missing a hidden setting; it simply hasn't landed for native tables yet. If your cluster is Shared-Data, keep an eye on the upcoming v4.x minor releases in late 2026. References • data_source/catalog/iceberg/iceberg_timetravel.md • release_notes/release-4.0.md • table_design/table_types/table_capabilities.md • administration/Meta_recovery.md • administration/management/Backup_and_restore.md References • release_notes/release-4.0.md

Stas Grinberg

09/25/2026, 4:55 AM

Hey @Rocky we need a sanity check on how we move data from our StarRocks lake FE (Iceberg / shared-data CNs) to our native FE (PK tables / BEs). Two separate clusters, two FEs. Same AWS account, tenant buckets already used by both. What we are doing now 1. Lake:

REFRESH

COUNT(*)

on the Iceberg table, then

INSERT INTO FILES("path"="s3://…/datalake/tmp/<run>/shard-n/", parquet, zstd)

SELECT … FROM <iceberg catalog>.ctem.<table>

2. Native:

INSERT INTO org_*_detailed.<table> … PROPERTIES("merge_condition"="event_timestamp")

SELECT … FROM FILES("path"="s3://…/shard-n/*")

3. Worker deletes the prefix after a successful import. Lake FILES uses instance profile (CN IMDS). Native FILES uses IRSA (

use_instance_profile=false

use_aws_sdk_default_behavior=true

) because BEs have no IMDS. Native IRSA already has

GetObject

on the whole tenant bucket. Why we did not do

INSERT INTO native SELECT FROM iceberg

on one FE One FE cannot see both clusters. Lake has the Iceberg catalogs; native has the PK tables. What we are seeing • Lake CNs OOM /

5609 Backend node not found

on concurrent unloads (playground 1×8Gi, stg 2×16Gi). FE stays up. • When unload returns OK, native almost always fails

5600 No files were found

datalake/tmp/…/shard-0/*

(StarRocks uses that for empty prefix and AccessDenied). • Tiny

COUNT(*)

(hundreds of rows) hits the 300s

query_timeout

when the single CN is wedged. Question Is lake

INSERT INTO FILES

→ S3 scratch → native

INSERT FROM FILES

the right StarRocks way to copy a filtered Iceberg result into a PK table on another cluster, or is there a better supported path (Broker Load /

FILES

+ pipe,

EXPORT

, a shared storage volume, something else)? If FILES is the path: any requirements we are missing so native can see what lake wrote (auth flags, file layout, empty-file behavior), and is

INSERT INTO FILES

expected to be this heavy on CNs vs a

COUNT(*)

Rocky

09/25/2026, 4:56 AM

This is a classic "inter-cluster ETL" scenario. While your current scratch-space approach is technically valid, you are hitting the limitations of the synchronous

INSERT INTO FILES

path and common S3 permission pitfalls. 1. The "Right" StarRocks Way: Direct Iceberg Mounting Since both clusters share the same AWS account and S3 tenant, the Native cluster does not need the Lake cluster as a middleman. Instead of unloading to S3 and reloading: 1. Mount the Iceberg Catalog on the Native FE: Create an external catalog on the Native cluster pointing to the same Iceberg metastore (Glue/HMS).

Copy code

sql
    CREATE EXTERNAL CATALOG lake_iceberg
    PROPERTIES (
        "type" = "iceberg",
        "iceberg.catalog.type" = "glue", -- or hive
        "aws.s3.use_instance_profile" = "false",
        "aws.s3.use_aws_sdk_default_behavior" = "true",
        "aws.s3.region" = "us-east-1"
    );

2. Direct Insert: Run the

INSERT

directly on the Native cluster:

Copy code

sql
    INSERT INTO org_detailed.table ...
    SELECT ... FROM lake_iceberg.db.table
    WHERE <filter_conditions>;

Why this is better: * Single Cluster Resource Management: All memory/CPU is managed by the Native cluster's BEs. * No Scratch Files: You eliminate the S3 write/list/delete overhead and the "No files found" errors. * Predicate Pushdown: The Native cluster can push filters directly to S3/Iceberg. ────────── 2. Troubleshooting your current S3 Scratch Path If you must keep the separate clusters (e.g., due to networking or strict security isolation), here is why your current path is failing: A. Error
5600 No files were found
(Permissions) You mentioned the Native IRSA has

GetObject

on the "whole bucket." FILES()
requires
s3:ListBucket
to work. *

s3:GetObject

only allows reading a specific file if you know the exact name. _

FILES("path"="s3://.../_")

needs to list the directory to find the files. * Fix: Ensure your IRSA policy includes:

Copy code

json
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": ["arn:aws:s3:::your-bucket-name"]
    }

_(Note: Resource for ListBucket is the bucket ARN, not the

/_

path.)* B. OOM and
5609 Backend node not found

INSERT INTO FILES

is heavy because it is a synchronous sink. * Memory Pressure: The CN must buffer rows, build Parquet row groups, and manage multipart uploads. On small nodes (8Gi/16Gi), concurrent unloads will trigger the Linux OOM killer or cause StarRocks to hit its internal

query_mem_limit

, crashing the backend process (hence "node not found"). * Solution: 1. Use

EXPORT

instead of

INSERT INTO FILES

EXPORT

is asynchronous, managed by the FE, and handles retries and large data volumes much better. 2. If you stick with

INSERT INTO FILES

, wrap it in

SUBMIT TASK

(v3.2+) to run it as an asynchronous background job, and set

SET query_mem_limit = <large_value>;

for that session. C. The "Empty Prefix" Behavior If the Lake

SELECT

returns 0 rows,

INSERT INTO FILES

typically completes "successfully" but writes nothing to S3. * The Native cluster then tries to

SELECT FROM FILES

on an empty prefix and returns

5600 No files found

. * Sanity Check: Always check if the source

COUNT(*)

> 0 before starting the transfer or handle the "No files found" error as a "no-op" in your worker script. Summary Checklist 1. Preferred: Create the Iceberg Catalog on the Native cluster and pull data directly. 2. Fallback (Scratch): Add

s3:ListBucket

to the Native IRSA. 3. Stability: Switch from

INSERT INTO FILES

EXPORT

for the "Lake" side to avoid OOM on CNs.

EXPORT

is specifically designed for stable data movement. 4. Resource Tuning: If you continue with

INSERT INTO FILES

, lower the concurrency with

SET parallel_fragment_exec_instance_num = 1;

to reduce the number of active Parquet encoders. References

Rocky

09/25/2026, 4:56 AM

• loading/s3.md • unloading/unload_using_insert_into_files.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/aws_iam_policies.md • unloading/Export.md References • data_source/catalog/iceberg/iceberg_catalog.md • unloading/unload_using_insert_into_files.md • loading/s3.md • sql-reference/aws_iam_policies.md • unloading/Export.md References • data_source/catalog/iceberg/iceberg_catalog.md • quick_start/iceberg.md • data_source/catalog/unified_catalog.md • data_source/catalog/catalog_overview.md

Stas Grinberg

09/25/2026, 5:00 AM

@Rocky we already ran that way. Prod native is the serving/PK cluster. When every Iceberg org catalog sat on that same FE, metadata refresh/sync load became the problem (many warehouses, constant

REFRESH

/ file-list chatter). That is why lake FE exists: Iceberg catalogs + maintenance there, native FE only PK tables. So we cannot attach

lake_iceberg

on native and

INSERT INTO org_detailed.t SELECT … FROM iceberg

— that is the overload we moved off. Given two FEs must stay: 1. Is lake

EXPORT

→ S3 → native

INSERT FROM FILES

/ Broker Load the supported hop, vs

INSERT INTO FILES

? 2. Any other official two-cluster pattern that does not register Iceberg catalogs on the native FE?

Rocky

09/25/2026, 5:00 AM

Your current setup is hitting two different walls: resource exhaustion on the unloading side (Lake) and S3 listing/consistency issues on the loading side (Native). Since you cannot register the Iceberg catalogs on the Native FE due to metadata overhead, here are the two "official" StarRocks paths for inter-cluster movement, ranked by stability and efficiency. 1. The "StarRocks External Table" Path (The Direct Push) Instead of using S3 as a middleman, you can create a link from the Lake cluster to the Native cluster. This allows the Lake cluster to "push" data directly into the Native tables via RPC. * How it works: On the Lake FE, you create an external StarRocks table that points to the target table on the Native FE. * The Command:

Copy code

sql
    -- Run this on the Lake Cluster
    CREATE EXTERNAL TABLE native_target_link (
        id INT,
        event_timestamp DATETIME,
        ...
    ) ENGINE=olap
    PROPERTIES (
        "host" = "<Native_FE_Leader_IP>",
        "port" = "9020", -- RPC port
        "user" = "export_user",
        "password" = "...",
        "database" = "org_detailed",
        "table" = "your_pk_table"
    );

    -- Perform the move
    INSERT INTO native_target_link
    SELECT * FROM iceberg_catalog.db.table
    WHERE ...;

* Why this is better: * No S3 Scratch Space: No files to list, no

ListBucket

permissions to worry about, and no "No files found" errors. * Memory Management: The Lake cluster acts as a client. It streams data to the Native cluster's BEs, which handle the Primary Key indexing and

merge_condition

. * Network: Requires connectivity between Lake BEs/CNs and Native FE (9020) + Native BEs (8060). ────────── 2. The "Export + Pipe" Path (The Asynchronous Middleman) If security policies prevent direct cluster-to-cluster RPC, you should move from synchronous commands (

INSERT INTO FILES

) to asynchronous ones. A. Lake Side: Switch to
EXPORT

INSERT INTO FILES

is a synchronous sink that forces CNs to buffer data for Parquet encoding. On small nodes (8Gi/16Gi), this is why you see

Backend node not found

(the process crashed). * Use
EXPORT
instead: It is handled asynchronously by the FE and is much more resilient for large unloads.

Copy code

sql
    EXPORT TABLE iceberg_catalog.db.table
    PARTITION (p1) -- optional
    WHERE ...
    TO "s3://.../datalake/tmp/run_id/"
    PROPERTIES (
        "column_separator" = ",",
        "format" = "parquet"
    );

B. Native Side: Switch to
PIPE

INSERT INTO SELECT FROM FILES()

is synchronous. If the Lake cluster hasn't finished writing all files or the S3 manifest,

FILES()

will return the

5600 No files were found

error. * Use `PIPE`: A Pipe is designed to "watch" a prefix and ingest files as they appear. It handles retries and partial failures automatically.

Copy code

sql
    CREATE PIPE my_import_pipe
    PROPERTIES ("AUTO_INGEST" = "FALSE") -- Triggered manually or by SQS
    AS
    INSERT INTO org_detailed.table
    SELECT _ FROM FILES("path" = "s3://.../datalake/tmp/run_id/_", ...);

────────── 3. Sanity Check on your
5600
Error If you must stick with

FILES()

, the "No files found" error is almost certainly one of two things: 1. Missing `s3:ListBucket`: Even if you have

GetObject

, StarRocks cannot expand

without

ListBucket

permission on the bucket ARN (not the path). 2. S3 Eventual Consistency: If the Lake cluster just finished writing, the S3 List index might not be updated yet.

FILES()

is a one-shot command; if it misses the window, it fails. Summary Recommendation 1. Try the "StarRocks External Table" first. It is the most "StarRocks-native" way to bridge two clusters without metadata bloat or S3 overhead. 2. If direct RPC is blocked, move to Lake
EXPORT
(to stop OOMs) and Native
PIPE
or
Broker Load
(to handle the S3-loading asynchronously). References

Rocky

09/25/2026, 5:00 AM

• data_source/External_table.md • unloading/Export.md • loading/loading_introduction/loading_concepts.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • unloading/unload_using_insert_into_files.md • administration/data_migration_tool.md • loading/BrokerLoad.md References • loading/loading_introduction/loading_concepts.md • loading/minio.md • loading/hdfs_load.md • loading/s3.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md

Stas Grinberg

09/25/2026, 5:06 AM

@Rocky does

INSERT INTO <external> … PROPERTIES("merge_condition"="event_timestamp")

work from iceberg clusters? Our native tables are PK + merge. If not, this path is unusable.

Rocky

09/25/2026, 5:07 AM

Yes,

INSERT INTO <external_table> ... PROPERTIES("merge_condition"="...")

is supported starting from StarRocks v3.4, and it is the recommended way to solve your OOM and "No files found" issues while keeping your FEs separate. Here is the breakdown of why this works and why it solves your current pain points: 1. Does it work for External Tables? Yes. When you use a StarRocks External Table (

ENGINE=olap

pointing to your Native cluster), an

INSERT INTO

statement on the Lake cluster acts as a "push" mechanism. * Version Requirement: You must be on v3.4.0 or later for the

INSERT ... PROPERTIES

syntax. * Property Propagation: The Lake FE parses the

merge_condition

property and transmits it via RPC to the Native cluster. The Native cluster then treats the incoming data stream exactly like a

Stream Load

that has the

merge_condition

header set. * Partial Updates: Note that if you also need partial updates, you simply specify the column list in the

INSERT

(e.g.,

INSERT INTO target_link (id, event_timestamp, val) ...

). 2. Why this is the "Right Way" for your 2-FE architecture: This path avoids the "Scratch Space" (S3) entirely, which eliminates your two biggest issues: * No more OOMs / 5609 Errors:

INSERT INTO FILES

is memory-intensive because the Lake CNs must buffer data and encode complex Parquet row groups before uploading. In contrast,

INSERT INTO <external_olap>

streams data as an RPC stream. The Lake nodes act as a simple client, shifting the heavy lifting (PK indexing and merging) to the Native cluster's BEs. * No more 5600 "No files found": This error happens because of S3 eventual consistency or missing

ListBucket

permissions when using

FILES()

. Since the data travels directly from Lake CN -> Native BE over the network, you bypass S3 listing issues and timing races. 3. Requirements for this path: To make this work, your Lake CNs/BEs must be able to talk to the Native cluster over the following ports: * Native FE RPC Port: (Default

) — for metadata and plan handshake. * Native BE bRPC Port: (Default

) — for the actual data transfer. The SQL pattern:

Copy code

sql
-- On the Lake FE:
-- 1. Create the link (only needs to be done once)
CREATE EXTERNAL TABLE native_link (
    id BIGINT,
    event_timestamp DATETIME,
    ...
) ENGINE=olap
PROPERTIES (
    "host" = "<Native_FE_IP>",
    "port" = "9020",
    "user" = "export_user",
    "password" = "...",
    "database" = "org_detailed",
    "table" = "your_pk_table"
);

-- 2. Run the push (supported v3.4+)
INSERT INTO native_link 
PROPERTIES ("merge_condition" = "event_timestamp")
SELECT ... 
FROM iceberg_catalog.db.table
WHERE ...;

If you are on a version older than v3.4: If you cannot upgrade to v3.4 yet, the

PROPERTIES

clause is not available in

INSERT

. In that case, you have two choices: 1. Use
SET enable_upsert_strict_mode = true
(and other session variables), but

merge_condition

specifically usually requires the

PROPERTIES

syntax or a

Stream Load

header. 2. Use a Broker Load on the Native cluster to pull from S3. Broker Load is asynchronous and supports

merge_condition

in its

PROPERTIES

block. This is more stable than

INSERT FROM FILES()

for large transfers. Recommendation: If you are on v3.4+, use the StarRocks External Table path. It is significantly more efficient for your 2-FE setup and was specifically designed for "pushing" data between isolated clusters. References • data_source/External_table.md • loading/Load_to_Primary_Key_tables.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md

Rocky

09/25/2026, 5:07 AM

• release_notes/release-3.4.md References • loading/Load_to_Primary_Key_tables.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • data_source/External_table.md • loading/BrokerLoad.md • release_notes/release-3.4.md References • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Load_to_Primary_Key_tables.md • loading/Spark-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md

Stas Grinberg

09/29/2026, 6:02 AM

hey @Rocky, follow-up — we will not use

ENGINE=OLAP

external insert A→B (shared-data PK /

getTablets()

). we already have the scan data as parquet on S3 (written before the trigger):

<s3://zenity-customer-data>-…/{integrationId}/{scanId}/violations/*.parquet

(same for

resources

relations

) plan: on cluster B only (native, shared-data PK):

Copy code

INSERT INTO org_x_detailed.violations (…)
PROPERTIES("merge_condition"="event_timestamp")
SELECT … FROM FILES("path"="s3://…/{integrationId}/{scanId}/violations/*", "format"="parquet");

questions: 1. is

INSERT … FROM FILES

into a cloud-native PK table supported on 4.1.4, with

merge_condition

? 2. any catch with a prefix glob (

…/violations/*

) vs one file per statement? 3. B’s CN/FE need S3 read on that bucket — anything special for shared-data besides normal

FILES()

creds? we are not unloading Iceberg to scratch. these files already exist.

Rocky

09/29/2026, 6:02 AM

This approach is solid and aligns with the modern StarRocks loading pattern for Primary Key (PK) tables. Here is the sanity check on your specific plan: 1. Support for
INSERT ... FROM FILES
with
merge_condition
Yes, this is supported. * Version Check: While "4.1.4" is likely a typo (the current latest stable range is v3.3/v3.4), the functionality you need was finalized in v3.4.0. * Syntax: The

INSERT INTO ... PROPERTIES("merge_condition" = "...") SELECT ...

syntax is the official way to perform conditional updates via

INSERT

. * PK Integration: When you run this, StarRocks treats the

SELECT FROM FILES

result like a stream of data. The

merge_condition

ensures that an incoming record only overwrites an existing one if its

event_timestamp

is greater than or equal to the one currently stored. *2. The Prefix Glob (

.../violations/*

)* This is actually the preferred way to load data compared to one file per statement. * Transaction Efficiency:

INSERT

is a transactional operation. Using a glob allows StarRocks to ingest all files in that directory in a single transaction. * Compaction Health: In Primary Key tables, every

INSERT

creates a new rowset version. Running one

INSERT

per file (e.g., 100 files = 100 inserts) would create massive version metadata pressure and trigger aggressive compaction. A single globbed

INSERT

is much "healthier" for the BEs. * Schema Unionization (v3.2+):

FILES()

will automatically sample the files under the prefix to detect the schema. If some files have extra columns (common in evolving datasets), StarRocks can union them automatically. 3. S3 Permissions & Shared-Data (Cloud-Native) There is nothing "special" required for shared-data clusters versus shared-nothing clusters when using

FILES()

, but there is a common point of confusion regarding credentials: * Internal vs. External: Your cluster's Internal Storage Volume (where the PK table data lives) and the External S3 Bucket (where your Parquet files are) are treated separately. * Credential Chain: If you don't provide explicit keys in the

FILES()

function, StarRocks will use the default AWS SDK behavior. In your case, since Cluster B uses IRSA (

use_instance_profile=false

), ensure the IAM Role associated with the Native cluster's BE/CN service account has: 1.

s3:GetObject

and

s3:GetBucketLocation

on the files. 2. CRITICAL:

s3:ListBucket

on the bucket ARN (e.g.,

arn:aws:s3:::zenity-customer-data

). Without

ListBucket

, the glob

will fail with

5600 No files were found

because StarRocks cannot expand the wildcard. * Shared-Data Context: Your Native cluster will write its own internal table metadata and data to its own storage volume, but it will use its compute (CNs) to pull the Parquet data from your scan bucket via the

FILES()

call. Recommended Syntax

Copy code

sql
INSERT INTO org_x_detailed.violations
PROPERTIES (
    "merge_condition" = "event_timestamp",
    "strict_mode" = "true",
    "timeout" = "3600" -- Increase if the scan is large
)
SELECT * FROM FILES(
    "path" = "<s3://zenity-customer-data/.../{integrationId}/{scanId}/violations/*>",
    "format" = "parquet",
    "use_aws_sdk_default_behavior" = "true" -- Force IRSA use
);

One final "catch": If the glob

.../violations/*

matches zero files, the

INSERT

will fail with an error. If your pipeline triggers this even when scans might be empty, you should wrap the call in a check or handle the exception in your worker. References • release_notes/release-3.4.md • release_notes/release-3.1.md • sql-reference/sql-functions/table-functions/files.md

Stas Grinberg

10/05/2026, 7:48 AM

@Rocky On shared-data Primary Key tables: can compaction remove a segment/SST that the persistent index or tablet metadata still references? If publish then fails (“file not found” / “load primary index failed”), it retries forever and later commits on that table never publish. Is that expected? How do we unblock it without DROP TABLE, and how do we stop it happening again?

Rocky

10/05/2026, 7:49 AM

In a StarRocks shared-data (cloud-native) cluster, the situation you're describing—where compaction or metadata cleanup accidentally removes a file (segment or SST) that the primary key index or tablet metadata still references—is a known edge case that leads to a "Publish Version" deadlock. 1. Is this behavior expected? It is not the intended behavior, but it is a known failure mode in earlier versions of the shared-data architecture (specifically before v3.2.8 and v3.3.2). * The Conflict: In shared-data mode, compaction creates a new version of the metadata. If a race condition occurs where the cleanup service (Garbage Collector) deletes an S3/Object Storage file before the FE has successfully "published" the version that references it, the BE/CN will fail to load the tablet or its index. * The "Retry Forever" Loop: StarRocks transactions are strictly serialized at the partition level. Transaction $N+1$ cannot be published until Transaction $N$ is visible. If Transaction $N$ (often a compaction task) fails to publish because of a "file not found" error, the system will retry it indefinitely to maintain data consistency. This effectively "freezes" the table for any new writes. 2. How to Unblock without
DROP TABLE
Starting with StarRocks v3.5.0 (and improved in v4.0), there is a dedicated mechanism to repair metadata for cloud-native tables. Step A: Diagnose the state Check which tablet is causing the blockage and what files are missing:

Copy code

sql
-- Check for unhealthy tablets
ADMIN SHOW TABLET STATUS FROM your_table_name;

-- Look for "segment file not found" or "load primary index failed" in BE/CN logs

Step B: Use
ADMIN REPAIR TABLE
(v3.5+) This command was specifically designed for shared-data clusters to handle metadata corruption or missing file pointers. It probes historical metadata versions and rolls back the tablet metadata to the last "consistent" state where all files exist on S3.

Copy code

sql
ADMIN REPAIR TABLE your_database.your_table_name;

* Mechanism: The FE coordinates CNs to probe historical versions in reverse order. It then performs a "second-level recovery" by updating the metadata pointer to a known-good historical state. No data movement is required. Step C: Manual "Task Cleanup" (Alternative) If you are on an older version and the transaction is a stuck compaction task: 1. Identify the Transaction ID (

TxnID

) from

SHOW PROC '/compactions'

. 2. Attempt to cancel it:

CANCEL COMPACTION WHERE TXN_ID = <TxnID>;

3. If it remains stuck in the "Publish" phase, follow the shared-data cleanup protocol: * Disable compaction:

ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "0");

* Restart all BE/CN nodes to clear memory-resident stuck tasks. * Re-enable compaction:

ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "-1");

3. How to prevent it from happening again 1. Upgrade: Ensure you are at least on v3.2.8 or v3.3.2+. These versions included critical fixes for "metadata cleanup issues when migrating/compacting PK tables with index." 2. Persistent Index Type: If you are using

enable_persistent_index = true

, ensure

persistent_index_type

is set to

CLOUD_NATIVE

(supported since v3.3.2). This stores the index itself on object storage, reducing the chance of local disk SST loss causing a metadata mismatch. 3. Adjust GC Settings: If your object storage has high latency or eventual consistency (rare for S3, more common for OSS/HDFS), increase the "metadata retention" time to prevent the GC from being too aggressive:

Copy code

sql
    -- Increase metadata retention (default is often 24h)
    ADMIN SET FRONTEND CONFIG ("lake_metadata_cache_limit_minutes" = "2880"); -- 48 hours

Rocky

10/05/2026, 7:49 AM

Summary Recommendation: If the table is currently frozen, try ADMIN REPAIR TABLE
first. If that command is not available in your version, you likely need to clear the stuck transaction via the "disable compaction + restart" method. References • knowledge/admin_and_operation/shared_data_compaction.md • faq/shared_data_faq.md

Stas Grinberg

10/12/2026, 10:01 AM

@Rocky 1. Why is

cpu_weight_percent

showing null after creating with

"cpu_weight" = "4"

? Is

cpu_weight

supported in the current StarRocks version deployed? 2. What's the correct property name to set a soft CPU weight/priority for a resource group in this version? 3. Does resource group CPU throttling apply to S3 network I/O from

INSERT FROM FILES

? Or only CPU compute?

Rocky

10/12/2026, 10:01 AM

1. Why
cpu_weight_percent
is null There is no property called

cpu_weight_percent

in the

CREATE RESOURCE GROUP

syntax. You likely see this column in a system view (like

information_schema.resource_groups

). * Calculation Logic: In StarRocks, you define the relative weight (e.g.,

"cpu_weight" = "4"

). The actual "percentage" of CPU time a group gets is dynamic: it depends on the sum of weights of all active resource groups at that exact moment. * Why it shows Null: Depending on your version, this column might be a legacy placeholder or only populated when the cluster is under load and the scheduler calculates the active shares. As long as

cpu_weight

shows your value (e.g.,

), the resource group is configured correctly. 2. Correct Property Name (Version Dependent) The property name changed in recent versions to support both soft and hard limits: * StarRocks v3.3.5 and later: * Soft Limit (Shared): Use

"cpu_weight"

. This is a scheduling weight relative to other groups. * Hard Limit (Exclusive): Use

"exclusive_cpu_cores"

. This reserves specific cores that cannot be shared. * StarRocks versions before v3.3.5: * The property was historically named "cpu_core_limit"
. While

cpu_weight

is the modern alias, if you are on an older version, the system may expect

cpu_core_limit

. Recommendation: Check your version with

SELECT VERSION();

. If you are on a version < 3.3.5 and

cpu_weight

isn't behaving as expected, try using

"cpu_core_limit"

. 3. CPU Throttling and S3 Network I/O CPU throttling does apply to the data loading process in

INSERT FROM FILES

, but it works indirectly: * The Scan Phase:

INSERT FROM FILES

triggers a

SELECT

from the external files. This phase involves downloading, decompressing, and parsing Parquet/ORC data. These tasks are CPU-intensive and run in the

pip_scan

thread pool. * Throttling Effect: Resource groups strictly limit the CPU time given to these thread pools. If you set a low

cpu_weight

, the BE/CN will allocate fewer CPU cycles to the threads responsible for parsing the S3 data. * Result: This effectively throttles the ingestion speed. While the network card itself isn't "throttled," the CPU's ability to process the incoming data stream is capped, which limits how fast StarRocks can pull data from S3. Note on `query_type`: Ensure your Resource Group classifier includes

query_type = 'insert'

query_type = 'select'

(since

INSERT FROM FILES

uses a select scan) to ensure the load is correctly mapped to the group. References • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_RESOURCE_GROUP.md References • sql-reference/sql-statements/cluster-management/resource_group/SHOW_RESOURCE_GROUP.md • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_USAGE_RESOURCE_GROUPS.md

Rocky

10/12/2026, 10:01 AM

• sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md

Stas Grinberg

10/12/2026, 11:02 AM

@Rocky What

cpu_weight

value would you recommend relative to

default_wg

— what's the effective weight of normal query sessions so we can calibrate the INSERT group properly?

Rocky

10/12/2026, 11:02 AM

To calibrate your

INSERT

group, you need to know the baseline for the system-defined groups. 1. The Effective Weight of
default_wg
By default, the

default_wg

(which handles all normal query sessions not matching a classifier) has a

cpu_weight

equal to the total number of CPU cores on the BE. * Example: If your BE/CN nodes have 32 cores, the weight of

default_wg

is 32. * Implication: If you create an ingestion group with

"cpu_weight" = "4"

, and both groups are under heavy load, the CPU split will be: * Normal Queries (
default_wg
): $32 / (32 + 4) = 88.8\%$ * Ingestion Group: $4 / (32 + 4) = 11.1\%$ 2. Recommended Calibration For a background

INSERT FROM FILES

process that shouldn't impact user experience, a weight between 10% and 25% of the total core count is usually the sweet spot. Target Priority | Calculation (Weight) | Example (for 32-core BE) Low (Background) | $0.1 \times \text{Cores}$ |

cpu_weight = 3

Medium (Balanced) | $0.25 \times \text{Cores}$ |

cpu_weight = 8

High (Standard) | $0.5 \times \text{Cores}$ |

cpu_weight = 16

Your current setting of
4
is likely very conservative (around 10% on a 32-core machine or 6% on a 64-core machine). If your loads are taking too long, you can safely bump this to 8 or 12 without noticeably "starving" normal query sessions. 3. How to Verify Active Weights You can check the actual weight values being used by the system with this query:

Copy code

sql
SELECT name, cpu_weight 
FROM information_schema.resource_groups;

_Note: If

cpu_weight

shows

for

default_wg

, it means it is dynamically using the core count._ 4. Critical Catch:
query_type
For

INSERT FROM FILES

to actually be governed by this group, your resource group must have the correct classifier. Because

FILES()

is technically a

SELECT

that feeds an

INSERT

, you should use:

Copy code

sql
ALTER RESOURCE GROUP your_insert_group 
ADD CLASSIFIER (query_type = 'insert');
-- AND/OR
ADD CLASSIFIER (query_type = 'select', user = 'your_loader_user');

If you only use

query_type = 'insert'

, StarRocks might only throttle the final "write" phase (which is lightweight). Adding a classifier for the specific user or role running the

FILES()

scan ensures the CPU-heavy parsing and decompression are also throttled. References • administration/management/resource_management/resource_group.md • best_practices/audit_log_resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md

Stas Grinberg

10/21/2026, 1:25 PM

@Rocky We're on StarRocks 4.1.6, shared-data mode. Base tables (native OLAP, NOT Iceberg): - spans: PRIMARY KEY (span_id, event_timestamp), PARTITION BY date_trunc('day', event_timestamp), Stream Load upserts - findings: same pattern, joined on thread_id = session_id We want an async MV with ONE row per (session_id, integration_id): GROUP BY session_id, integration_id with min/max/count/sum, group_concat(DISTINCT ...), and LEFT JOIN findings. Questions for 4.1.6 specifically: 1) For this unpartitioned session-level MV on native PK tables, does every refresh (SCHEDULE EVERY 10 MINUTE or ON_BASE_TABLE_CHANGE) full-scan/rebuild the entire MV, or can StarRocks refresh only sessions touched by newly loaded spans? 2) Is there any supported way in 4.1.6 to do key-based / incremental maintenance for this shape on native tables (not Iceberg INCREMENTAL)? 3) If full rebuild is required, what's the recommended pattern for a sessions list UI that needs unique session_id and ~minutes freshness on a ~200GB spans table? (partitioned day MV + query-time merge vs unpartitioned session MV vs manual TASK)

Rocky

10/21/2026, 1:25 PM

For StarRocks 4.1.6 (assuming you are referring to the latest 3.x/4.x evolution path), the behavior of asynchronous Materialized Views (MVs) on native Primary Key (PK) tables follows these rules: 1. Full-scan vs. Incremental Refresh In your specific case (unpartitioned session-level MV), the refresh will be a full rebuild of the entire MV. * The Problem: Because your MV is unpartitioned, StarRocks cannot determine which "segments" of the MV to update. Even if the base table

spans

is partitioned by day, an unpartitioned MV treats the entire result set as a single unit. * The Aggregation Constraint:

group_concat(DISTINCT ...)

is a blocking operator for incremental maintenance. Unlike

SUM

COUNT

, which are commutative,

DISTINCT

string concatenation requires the system to see the full set of values for that

session_id

to ensure correctness during an update. * The Result: Every 10 minutes, StarRocks will execute the query:

SELECT ... FROM spans LEFT JOIN findings GROUP BY session_id, integration_id

. On a 200GB table, this will be extremely resource-intensive. 2. Is there a supported "Incremental" way for this shape? There is no "key-based" incremental refresh that magically updates only the rows for

session_id = 'XYZ'

. However, you can achieve partition-level incremental refresh by aligning the MV with the base table: * Aligned Partitioning: If you define the MV with

PARTITION BY date_trunc('day', event_timestamp)

, StarRocks can detect that only the "Today" partition of

spans

has changed. It will then only re-run the

GROUP BY

for "Today's" data and overwrite that specific partition in the MV. * The Limitation: If a

session_id

spans across two days (e.g., starts at 11:55 PM, ends at 12:05 AM), a partition-aligned MV will have two rows for that session (one in each day's partition). 3. Recommended Pattern for "Sessions List" UI For a sessions UI requiring unique

session_id

and minutes-level freshness on a 200GB dataset, a single unpartitioned MV is usually a performance trap. Use the Nested / Rollup Pattern: Step A: Create a Partitioned "Intermediate" MV Create an MV that is partitioned by day. This MV handles the "heavy lifting" of the Join and the initial aggregation. To handle the

DISTINCT

requirement, use

array_agg_state

(available in v3.4/4.0+) which allows you to store the "intermediate state" of a distinct list.

Copy code

sql
CREATE MATERIALIZED VIEW mv_sessions_daily
PARTITION BY event_day
REFRESH ASYNC EVERY (INTERVAL 10 MINUTE)
AS
SELECT 
    date_trunc('day', event_timestamp) as event_day,
    session_id,
    integration_id,
    min(event_timestamp) as start_time,
    max(event_timestamp) as end_time,
    count(*) as event_count,
    -- Store the state for later merging
    array_agg_state(DISTINCT some_col) as distinct_items_state 
FROM spans
LEFT JOIN findings ON spans.thread_id = findings.session_id
GROUP BY event_day, session_id, integration_id;

* Benefit: This MV only refreshes the daily partitions that actually changed. Refreshing 500MB of "today's" data is much faster than 200GB. Step B: Query-Time Rollup (View) Create a logical

VIEW

(not an MV) that merges the daily results. Because the intermediate MV has already reduced the data by 90%+, this final merge is fast enough for a UI.

Copy code

sql
CREATE VIEW sessions_ui AS
SELECT 
    session_id,
    integration_id,
    min(start_time) as first_event,
    max(end_time) as last_event,
    sum(event_count) as total_events,
    -- Merge the states and convert to string
    array_to_string(array_agg_union(distinct_items_state), ',') as distinct_items
FROM mv_sessions_daily
GROUP BY session_id, integration_id;

Summary of Strategy for 4.1.6: 1. Stop using unpartitioned MVs for high-cardinality

session_id

on large tables; they will always full-rebuild. 2. Partition the MV by Day to enable incremental partition refresh.

Rocky

10/21/2026, 1:25 PM

3. Use Aggregate States (

array_agg_state

bitmap_union

) to handle

DISTINCT

logic across partition boundaries. 4. Query-time merge at the top layer to handle sessions that cross the midnight boundary. If you must have a single-row-per-session physical table, the only other option is a manual
INSERT INTO ... SELECT
task that uses a

WHERE event_timestamp > now() - interval 15 minute

filter, but this risks missing data if the "Load" is delayed or if you need to handle "updates" to older sessions. The Nested MV pattern is the standard "StarRocks way" for this scale. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md

Stas Grinberg

10/23/2026, 4:25 AM

@Rocky we finished the sessions-list POC on 4.1.6 shared-data (~105M rows, biggest org). Sharing results vs your partition / late-mat advice. A) Unpartitioned PK

PRIMARY KEY (session_pk)

with

session_pk = xx_hash3_128(session_id, integration_id)

ORDER BY (last_activity, session_pk)

WHERE last_activity >= now() - 30 day ORDER BY last_activity DESC, session_pk DESC LIMIT 50; • Time ~165–330ms •

OLAP_SCAN OutputRows

~20–23M • Allocated mem ~5.7–9.5 GB • TopN RF 0% B) Day-partitioned

PRIMARY KEY (session_pk, activity_day)

PARTITION BY date_trunc('day', activity_day)

one row per session (DELETE old day + INSERT on load) Filter on

last_activity

only →

partitions=147/147

(no prune). Filter on

activity_day

(30d) →

partitions=29/147

(prune works): • Time ~170–190ms • Scan ~33–35M rows • Mem ~9 GB • Still full window scan → Top-N; no “stop after newest day at 50 rows” C) Config

late_materialization_ratio = 10

on all CNs. Session

enable_global_late_materialization

only helped a bit (9.5→5.7GB). Questions 1. On 4.1.6, is DESC + LIMIT short-circuit across day partitions expected not to fire when the WHERE covers ~30 partitions (even with

ORDER BY last_activity DESC

)? Under what query shape would we see scan � with

ORDER BY last_activity DESC

)? Under what query shape would we see scan ≪ day cardinality? 2. For a 30d sessions UI on ~30M rows in-window, do you still recommend partitions, or unpartitioned + sort key (simpler upsert) +

late_materialization_ratio=1000

? 3. Any other first-page pattern to avoid multi-GB Top-N for this grain? We’re leaning unpartitioned for v1 unless you see a clear partition short-circuit we’re missing.

Rocky

10/23/2026, 4:25 AM

Your POC results confirm a common observation in StarRocks: Primary Key (PK) tables prioritize upsert speed and point-lookup over aggressive range-scan short-circuiting. Here is the breakdown for StarRocks 4.1.6 (and the 3.x architecture it’s based on) regarding your sessions-list scenario: 1. Why Short-Circuiting isn't firing (Partitioned vs. Unpartitioned) You are expecting the storage engine to stop scanning as soon as it finds 50 rows in the "newest" day. However, this rarely happens across partitions for PK tables due to Version Merging: * The "Latest Version" Constraint: In a PK table, StarRocks must resolve multiple versions of the same PK. Even if the "Today" partition has 50 rows, the engine technically doesn't know if a "More Recent" version of those rows (or a deletion) exists until it processes the relevant data segments. * Global vs. Local Sort: While each partition is sorted internally by your

ORDER BY

key, the global Top-N operator usually waits to receive all candidate rows from the 30 scanned partitions before finalizing the 50-row limit. * When would scan be ≪ cardinality? Only if your

WHERE

clause effectively prunes the data (e.g., searching for a specific

integration_id

that only exists in 1% of files). For a "First Page" view that filters only by time, StarRocks will almost always scan the entire time-window defined in your

WHERE

clause. 2. Unpartitioned vs. Partitioned for 30M rows For a dataset of ~100M total / 30M windowed, Unpartitioned PK is the winner. * Simplicity: You avoid the "Delete-from-yesterday, Insert-to-today" dance when a session crosses the midnight boundary. * Pruning is a Wash: As you saw, your UI requires a 30-day window. Pruning 147 partitions down to 29 saves metadata overhead, but the actual I/O cost of scanning the 30M rows is roughly identical to the unpartitioned table where the Sort Key (

last_activity

) clusters those 30M rows together anyway. * Late Materialization: In an unpartitioned table, the sort-key is truly global. With

late_materialization

enabled, StarRocks only reads the

last_activity

and

session_pk

columns for the 30M rows, performs the Top-N, and only then fetches the remaining columns for the final 50 rows. 3. Tuning Memory and the "Multi-GB Top-N" The 5.7–9.5 GB memory usage is coming from the Top-N operator holding those 20M–30M candidate rows before it can safely discard them. To bring this down: * Aggressive Late Materialization: Instead of

ratio = 10

, try forcing it for this session:

SET late_materialization_ratio = 1;

(or even lower). This forces the engine to treat almost any query as a candidate for late materialization, ensuring only the sort-key columns sit in memory during the Top-N phase. * The "Double-Look" Pattern: If the memory overhead is still too high for a multi-tenant UI, use the "Internal Late Materialization" manual pattern:

Copy code

sql
    SELECT * FROM sessions
    WHERE session_pk IN (
        SELECT session_pk FROM sessions
        WHERE last_activity > now() - interval 30 day
        ORDER BY last_activity DESC
        LIMIT 50
    );

In StarRocks 3.x/4.x, this often outperforms a flat

SELECT *

because the inner query handles the massive Top-N using only the 16-byte PKs, and the outer query does 50 high-speed point lookups. 4. Recommendation for v1 Go Unpartitioned. * Your performance (165ms) is already excellent for a 100M row table. * The maintenance overhead of managing day-partitions for a PK table where "rows move days" (session updates) is high and provides zero performance gain for a 30-day window query. * Focus on

late_materialization_ratio

to keep the memory footprint stable as your "biggest org" grows from 100M to 1B rows. One final check:

Rocky

10/23/2026, 4:25 AM

Check if your

last_activity

is actually the first column in your

PRIMARY KEY

ORDER BY

clause. For PK tables, the physical sort order is strictly defined by the PK/Sort Key column order. If

session_pk

is first, the

ORDER BY last_activity

will be much slower. It should be:

PRIMARY KEY (last_activity, session_pk)

ORDER BY (last_activity, session_pk)

Stas Grinberg

10/23/2026, 4:34 AM

@Rocky thanks — we’ll go unpartitioned for v1. Two corrections from docs/our setup: 1.

late_materialization_ratio

is BE/CN config, not

SET

. Docs say 1000 forces late mat; 1 does not (near disable). Ours is already

. 2. We intentionally use

PRIMARY KEY (session_pk)

ORDER BY (last_activity, session_pk)

— putting

last_activity

in the PK breaks upserts when activity moves. We’ll try the IN (SELECT … LIMIT 50) double-look for mem. If you still want a CN

late_materialization_ratio

change, confirm 1000 not 1.

Rocky

10/23/2026, 4:34 AM

You're absolutely right on both counts—thanks for the corrections. I appreciate the precision on the version-specific behavior. 1.
late_materialization_ratio
Correction You are correct. The logic is: * 0: Disables it. * 1000: Forces it (treats every query as highly selective). * 10 (Default): Only triggers late materialization if the filter is very aggressive. Since this is a BE/CN config (static, requires restart), you should indeed try bumping it to 1000 on your test cluster to see if the engine-level late materialization solves the memory bloat. If it works, it’s the "cleanest" fix as it requires no SQL changes. 2. PK vs. Sort Key Setup Your choice of

PRIMARY KEY (session_pk)

with

ORDER BY (last_activity, session_pk)

is the correct architectural move for a Sessions UI. * By keeping

last_activity

out of the PK, you avoid the "delete+insert" overhead during upserts when a session becomes active again. * The

ORDER BY

clause still ensures that the data is physically clustered by time, which is why your 165ms scan is so efficient despite the 100M row count. 3. The "Double-Look" Pattern for Memory If you don't want to change global BE configs (or if 1000 doesn't bring the memory down enough), the

IN (SELECT ... LIMIT 50)

pattern is your best bet. Why it helps here: Even with

late_materialization_ratio = 1000

, the Top-N operator sometimes still holds "wide" row pointers in memory. By using the

IN

subquery, you force a two-step process: 1. Step 1: The inner query finds the 50

session_pk

values. This uses almost zero memory because it only sorts 16-byte hashes. 2. Step 2: The outer query performs 50 point-lookups (or a very small range scan) to get the "fat" columns (the

group_concat

results, metadata, etc.). Summary for v1: * Table: Unpartitioned PK. * Optimization: Try

late_materialization_ratio = 1000

on a CN first. * Fallback: If memory remains high, implement the

WHERE session_pk IN (SELECT ... ORDER BY last_activity DESC LIMIT 50)

pattern in your UI's data access layer. This approach scales remarkably well. We've seen this handle billions of rows while keeping the "first page" response under 200ms. References • administration/management/BE_configuration.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md • administration/management/resource_management/resource_group.md References • sql-reference/System_variable.md • administration/management/BE_configuration.md • using_starrocks/async_mv/Materialized_view.md

2 Views

Open in Slack

Previous Next