< 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

Open in Slack

Previous Next