< Rocky> this create table CREATE TABLE <http default catalo StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> this create table CREATE TABLE <def...

Stas Grinberg

05/30/2026, 4:55 AM

@Rocky this create table CREATE TABLE default_catalog.org_5gmd9crgrozzvaxh_metadata.sanitized_steps_pk (

event_timestamp

datetime NOT NULL,

id

varchar(512) NOT NULL,

source_pipeline

varchar(256) NULL,

thread_id

varchar(512) NULL,

type

varchar(256) NULL,

service

varchar(256) NULL,

ingested_at

varchar(512) NULL,

integration_id

varchar(512) NULL,

main_actor

struct<display_name varchar(1048576), email varchar(1048576), id varchar(1048576)> NULL,

bot_details

struct<display_name varchar(1048576), platform_info struct<resource_id varchar(1048576)>> NULL,

client_context

struct<client_application varchar(1048576), conversation_type varchar(1048576)> NULL,

inventory_environment

struct<id varchar(1048576), display_name varchar(1048576)> NULL ) DUPLICATE KEY(event_timestamp) PARTITION BY date_trunc('day', event_timestamp) DISTRIBUTED BY RANDOM BUCKETS 16 PROPERTIES ( "compression" = "LZ4", "datacache.enable" = "true", "replication_num" = "1", "storage_volume" = "org_5gmd9crgrozzvaxh_metadata_volume" ); still working as only for asc order in sub second, the desc is 2.5+ sec and only after it cached. maybe some settings or any other i am missing to get the same performance for desc order on query?

Rocky

05/30/2026, 4:56 AM

For your table structure,

event_timestamp

is the primary sort key (due to

DUPLICATE KEY(event_timestamp)

), which should theoretically allow both

ASC

and

DESC

queries to be very fast. If

ASC

is sub-second but

DESC

takes 2.5 seconds (even after caching), it typically indicates that the query is either scanning more data than necessary or missing a specific Top-N optimization that is only triggering for the ascending direction. Here are the settings and optimizations you should check: 1. Enable Top-N Filter Optimization StarRocks 3.3+ introduced the Top-N Runtime Filter, which can significantly speed up

ORDER BY ... LIMIT

queries by pushing the filtering logic down to the storage layer. * Check variable:

enable_topn_filter

(Default:

true

in 3.3+). * Try setting:

Copy code

sql
    SET enable_topn_filter = true;
    SET enable_topn_runtime_filter = true; -- Available in newer versions

2. Check Partition Pruning Because you use

PARTITION BY date_trunc('day', event_timestamp)

, StarRocks should ideally scan the most recent partitions first when querying

DESC

. * The Issue: If the optimizer doesn't recognize that it can stop after finding the latest

rows in the newest partition, it might scan all partitions. * Optimization: Try adding a

WHERE

clause that limits the scan to a reasonable range (e.g., the last 7 days). Even if the query is meant to be global, a broad

WHERE

clause often helps the optimizer prioritize the correct partitions.

Copy code

sql
    SELECT * FROM sanitized_steps_pk
    WHERE event_timestamp > '2024-01-01' -- dummy range to assist pruner
    ORDER BY event_timestamp DESC LIMIT 100;

3. Late Materialization & Wide Columns Your table contains several wide columns (

varchar(1048576)

) and nested structs. _ In a

SELECT _

query, the engine must read all these columns. * If the

DESC

scan is not hitting the "Short-circuit" or "Fast-scan" path, it may be materializing all rows before sorting. * Try: Compare performance by selecting only the key columns. If

SELECT event_timestamp, id ...

is fast but

SELECT *

is slow, the overhead is in reading the large

struct

and

varchar

data from disk/cache. 4. Compare Query Plans (
EXPLAIN ANALYZE
) Run

EXPLAIN ANALYZE

on both the

ASC

and

DESC

queries and compare the output. Look for: * Short-circuit: See if

ShortKeyFilter

Short-circuit

appears in the

ASC

plan but is missing in

DESC

. * PushdownTopN: Check if the

TOP-N

operator is pushed down into the

OlapScanNode

. * Scan statistics: Check

RowsRead

and

RowsReturned

. If

DESC

reads millions of rows while

ASC

reads only hundreds, the limit pushdown is failing for the descending scan. 5. Session Variables for Sorting If your version is slightly older, the "Reverse Scan" optimization (reading a segment backwards) might be less efficient than a forward scan. You can try adjusting the parallelism: *

SET parallel_fragment_exec_instance_num = 16;

(or match your bucket count). *

SET topn_opt_limit_threshold = 1000;

(Adjust the threshold for Top-N optimization). Summary Recommendation If you are on a version older than 3.3, I highly recommend upgrading, as many Top-N and sort-key optimizations for

DUPLICATE KEY

tables were significantly improved in the 3.x series. One specific property to check: Ensure your query has a

LIMIT

ORDER BY DESC

without a

LIMIT

is a full sort and will always be significantly slower than a point or range scan. References • sql-reference/System_variable.md • knowledge/usage/best-practice.md • faq/Sql_faq.md • sql-reference/sql-functions/array-functions/array_top_n.md

Rocky

05/30/2026, 4:56 AM

• sql-reference/sql-statements/table_bucket_part_index/SELECT.md • best_practices/table_clustering.md

Stas Grinberg

05/31/2026, 11:54 AM

@Rocky We're running StarRocks 4.0.9 in shared-data mode on Kubernetes (3 CN nodes, 150GB local disk each).

CREATE TABLE

is hanging indefinitely — it never times out, it just runs forever. No error message. We suspect the local disk cache is full — all 3 CNs show

DiskUsage: 150GB/150GB

Rocky

05/31/2026, 11:54 AM

In StarRocks shared-data mode (Cloud-Native), 100% disk usage on CN nodes is a critical state that often leads to RPC timeouts or "zombie" nodes, which can definitely cause metadata operations like

CREATE TABLE

to hang. 1. Can a full DataCache disk block
CREATE TABLE
? Yes. While table metadata is stored on the FE and data is stored in object storage (S3/HDFS), the

CREATE TABLE

process often involves the FE communicating with CN nodes for bucket inference or initial tablet validation. * If the local disk is at 100% capacity, the CN process may be unable to write its own logs, create temporary files, or process RPC requests from the FE. * The FE stays in a "waiting" state for the CNs to respond, leading to the indefinite hang you are seeing. 2. How to free disk space without restarting CN pods You can clear the cache or force an eviction by dynamically updating the configuration via SQL. This is safer than manually deleting files while the process is running. Run the following from your MySQL client:

Copy code

sql
-- 1. Get the BE_ID (CN_ID) for your nodes
SHOW BACKENDS; 

-- 2. Temporarily set the cache size to 0 to trigger an immediate purge
-- Replace <CN_ID> with the ID from the previous step
UPDATE be_configs SET VALUE="0" WHERE NAME="datacache_disk_size" AND BE_ID=<CN_ID>;

-- 3. After disk space is freed (monitor with your tools), set it back to a safe limit (e.g., 120G)
UPDATE be_configs SET VALUE="120G" WHERE NAME="datacache_disk_size" AND BE_ID=<CN_ID>;

_Note: If you want to apply this to all nodes at once, omit the

AND BE_ID=...

clause._ 3. Recommended way to cap DataCache To prevent this from happening again, you should cap the cache usage in your

cn.conf

(or via the Helm chart / Operator env vars) so that there is always "headroom" for logs and system operations. Recommended Configurations: Modify your CN configuration with one of these two parameters: * Option A: Percentage-based (Recommended for K8s) Set

starlet_star_cache_disk_size_percent = 80

. This ensures StarRocks only uses 80% of the available disk, leaving 20% for the OS and logs. * Option B: Hard Limit Set

datacache_disk_size = 120G

(assuming a 150GB disk). Additional Safeguards: StarRocks has built-in auto-eviction logic. Ensure these are set to leave a buffer: *

datacache_disk_high_level = 90

(Triggers eviction when 90% full). *

datacache_disk_safe_level = 80

(Evicts data until 80% remains). Summary Checklist 1. Check CN Logs: Look for "No space left on device" errors in

<http://cn.INFO|cn.INFO>

cn.WARNING

. 2. Clear Cache: Use the

UPDATE be_configs

SQL command above. 3. Update Configs: Set

starlet_star_cache_disk_size_percent

in your deployment configuration to prevent future lockupsets. hangs. References • administration/management/BE_configuration.md • deployment/shared_data/feature-support-shared-data.md • using_starrocks/caching/block_cache.md • data_source/data_cache.md

Stas Grinberg

05/31/2026, 12:17 PM

@Rocky A quick follow-up on the datacache disk params for StarRocks 4.0.9 shared-data (CN nodes): 1. Are

datacache_disk_high_level

datacache_disk_safe_level

still the correct names in v4.0.9, or were they renamed to

disk_high_level

disk_safe_level

? 2. Does

starlet_star_cache_disk_size_percent

actually take effect in v4.0.9 shared-data mode, or is it ignored when

datacache_unified_instance_enable=true

(which I believe is the v4 default)? 3. If the percentage param is ignored — is

datacache_disk_size = 360G

(hard limit) the correct way to cap cache on a 400Gi disk? Thanks

Rocky

05/31/2026, 12:17 PM

Since you are running StarRocks v4.0.9, you are using the unified Data Cache architecture (introduced in v3.4/v4.0) where several older parameters have been renamed or superseded. 1. Parameter Renaming Yes, your suspicion is correct. In v4.0 onwards, the following parameters have been renamed to be more concise: *

datacache_disk_high_level

→ disk_high_level
(Default: 90) *

datacache_disk_safe_level

→ disk_safe_level
(Default: 80) *

datacache_disk_low_level

→ disk_low_level
(Default: 60) When disk usage reaches

disk_high_level

, the system triggers an automatic eviction to bring usage back down to

disk_safe_level

. 2. Is
starlet_star_cache_disk_size_percent
ignored? Yes. In v4.0.9, where

datacache_unified_instance_enable

true

by default, the legacy StarOS-specific parameter

starlet_star_cache_disk_size_percent

is ignored. The system now relies on the unified datacache_disk_size
configuration to manage space for both cloud-native tables (shared-data) and external catalogs. 3. Recommended Way to Cap Cache The correct way to cap your cache in v4.0.9 is using

datacache_disk_size

. This parameter is now flexible and supports both absolute values and percentages: * Option A (Percentage):

datacache_disk_size = 80%

* Option B (Hard Limit):

datacache_disk_size = 360G

Important Note for Shared-Data Mode: In shared-data (CN) mode, the

datacache_disk_size

limit applies per disk if you have multiple paths in

storage_root_path

. For example, if you have two disks and set

datacache_disk_size = 360G

, StarRocks will attempt to use up to 720GB total. Recommendation for your 400Gi disk: To ensure you leave headroom for logs and metadata, set:

Copy code

properties
datacache_disk_size = 80%
# OR
datacache_disk_size = 320G

This ensures the Data Cache target is well below your 400Gi physical limit, giving the OS and StarRocks room to breathe even when the cache is "full." Summary Comparison (v4.0.9) Feature | Old Parameter (Pre-v4) | ***New Parameter (v4.0+)*** Max Disk Cache |

starlet_star_cache_disk_size_percent

datacache_disk_size

(supports % or size) Eviction Threshold |

datacache_disk_high_level

disk_high_level

Safe Recovery Level |

datacache_disk_safe_level

disk_safe_level

To verify your current effective limits, you can run:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE '%disk%';
-- And for CN nodes
SELECT * FROM information_schema.be_configs WHERE NAME LIKE '%disk%';

References • using_starrocks/caching/block_cache.md • administration/management/BE_configuration.md • deployment/shared_data/s3.md • deployment/shared_data/hdfs.md • deployment/shared_data/feature-support-shared-data.md • deployment/shared_data/azure.md • deployment/shared_data/minio.md • deployment/shared_data/gcs.md

Stas Grinberg

05/31/2026, 2:58 PM

@Rocky 1. We've been fighting issues all day and I think we finally found the root cause. 2. Situation: ◦ 3 CN nodes, 150GB EBS disk each (shared-data mode, 4.0.9) ◦ All 3 CN disks are always at149-150GB/150GB(100% full) ◦ Every pod restart reloads the cached data from the PVC — disk immediately fills back up ◦ `UPDATE be_configs SET VALUE="0"`purges it temporarily but it refills after restart 3. Issues we hit today that we now believe are all caused by full disk: a. `CREATE TABLE`hanging indefinitely b. `INSERT`→`automatic create partition failed: Table creation timed out` c. CN nodes going DISCONNECTED during restarts d. `tablet_create_timeout_second`timeouts even at 300s and 600s 4. Questions: a. Is a 100% full DataCache disk the confirmed root cause of all these write failures? b. What's the correct permanent fix — should we`rm -rf /opt/starrocks/cn/storage/datacache/`on each PVC and restart? c. Is there a way to configure StarRocks tonotpersist DataCache across pod restarts? We'd rather start cold than be permanently blocked by full disk.

Rocky

05/31/2026, 2:58 PM

To help you stabilize your cluster, here is the breakdown of the root cause and the permanent fixes for StarRocks v4.0.9 in shared-data mode. 4a. Is 100% disk the confirmed root cause? Yes, absolutely. In StarRocks, even in shared-data mode where the actual data is in S3/Object Storage, the CN/BE nodes require local disk space for critical metadata and operational tasks: * Heartbeats & Metadata: CN nodes must write heartbeat information and local tablet metadata to disk. If the disk is at 100%, these writes fail, causing the node to appear "Disconnected" to the FE. * Table Creation:

CREATE TABLE

and auto-partitioning require a round-trip confirmation from the CNs. If the CN cannot write to its local disk to initialize the internal tracking for those tablets, the RPC will hang or timeout. * Logs & Temp Files: StarRocks generates logs and temporary spill files during inserts. A 100% full disk blocks the process entirely. 4b. What is the correct permanent fix? You should not just

rm -rf

and restart without changing the config, as the cache will eventually fill the disk again. The permanent fix is to set a "Hard Ceiling" that is lower than your EBS volume size. 1. Modify your configuration (
cn.conf
or Helm values): Set the cache to use only 80% of your disk. This leaves 20% (30GB in your case) for logs, core dumps, and metadata.

Copy code

properties
    # For StarRocks 4.0.9
    datacache_disk_size = 80%
    # Ensure this legacy param isn't overriding it
    starlet_star_cache_disk_size_percent = 80

*Note: If both are set, StarRocks takes the larger of the two values. Ensure both are set to 80 or a specific value like 120G.* 2. Perform the one-time cleanup: If the pods are currently stuck, the fastest way to recover is to clear the legacy data once: * Stop the CN pod. _ Delete the contents:

rm -rf <your_storage_root_path>/datacache/_

. * Restart the pod with the new 80% limit. 4c. How to disable DataCache persistence across restarts? By default, StarRocks caches are persistent because they are stored in the

storage_root_path

(which you likely have mapped to a Persistent Volume Claim). If you want a "Cold Start" (Cache Wipe) every time a pod restarts, change your Kubernetes deployment to use

emptyDir

for the datacache directory instead of a PVC: Example Kubernetes snippet:

Copy code

yaml
spec:
  containers:
  - name: starrocks-cn
    volumeMounts:
    - name: cn-datacache
      mountPath: /opt/starrocks/cn/storage/datacache # Adjust to your path
  volumes:
  - name: cn-datacache
    emptyDir: {} # This wipes data whenever the pod is deleted/restarted

Important behavior note for v4.x: StarRocks Data Cache does not delete files to reduce disk usage. It marks blocks as "overwritable" once it hits the

disk_high_level

(default 90%). Because of this,

df -h

will almost always show the disk as "Full" once it reaches your configured limit. This is normal, provided your limit (

datacache_disk_size

) is set lower than the physical disk capacity of the EBS volume. Final Recommendation: 1. Set

datacache_disk_size = 120G

(for your 150G disk). 2. Clear the existing cache directory once. 3. The disk will eventually climb to 120G and stay there; this is expected and safe, as long as the remaining 30G is available for the system. References • administration/management/BE_configuration.md • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md

Stas Grinberg

05/31/2026, 4:07 PM

Hey @Rocky After restarting CN pods today, tablet creation is extremely slow — 1 new partition (~16 tablets) takes 5-6 minutes. Before restarts we loaded 340M rows in 8 minutes.

SHOW COMPUTE NODES

shows all nodes Alive, OK, disk ~15GB/150GB, memory 50%. Is there a known issue with S3 write performance after CN pod restarts in 4.0.9 shared-data? What should we check?

Rocky

05/31/2026, 4:12 PM

The 5-6 minute delay for creating just 16 tablets (one partition) is highly abnormal and typically points to a metadata communication bottleneck between the FE, StarOS (the internal shard manager), and S3, rather than a DataCache issue. Since you are on v4.0.9, you are using the new "Unified Metadata" and "Combined Txn Log" features. Here is what you should check: 1. Known Metadata Stall in v4.0.9 There is a known performance issue in v4.0.x where the FE's planner can stall during "tablet-location lookups" in shared-data mode because it performs individual StarOS RPC calls for every tablet. * The Fix: This was specifically optimized in v4.0.10 (one patch ahead of you) to batch these lookups. * The Symptom: Long "hanging" periods during

CREATE TABLE

or the start of an

INSERT

(the planning phase). 2. Increase Tablet Creation Workers After a pod restart, the system might be busy with background reconciliation tasks. You can speed up the tablet creation thread pool on the CN nodes: * CN Configuration (
cn.conf
):

Copy code

properties
    create_tablet_worker_count = 8  # Increase from default (usually 3)

_Note: This requires a pod restart to take effect if not updated via

UPDATE be_configs

._ 3. Check for StarOS RPC Timeouts In shared-data mode, the FE communicates with a StarOS service to allocate "shards" (tablets). If the pods have new IPs or networking is flaky after the restart, this can cause silent delays. * Check FE Logs (
fe.log
/
fe.warn.log
): Search for

StarOS

create shards

, or

RPC timeout

. * Check CN Logs (
<http://cn.INFO|cn.INFO>
): Search for

create_tablet

and see the timestamps. If the gap between "receive request" and "finish" is long, the S3 write for the tablet's initial metadata file is the bottleneck. 4. S3 / Object Storage Latency Tablet creation in shared-data mode requires a synchronous write of a small metadata file to S3 for every tablet. * If your S3 bucket has a massive number of files (e.g., from old dropped tables that haven't been GC'ed), S3 metadata operations can slow down. * Check: Run a manual

aws s3 cp

or similar from inside a CN pod to the StarRocks bucket to verify there is no unusual latency (should be <100ms). 5. Potential Metadata Bloat (Recycle Bin) If you have been dropping and recreating tables today while fighting the disk issue, your FE's

CatalogRecycleBin

might be full, causing scheduling overhead. * FE Dynamic Config: Try reducing the time items stay in the recycle bin to clear the pressure:

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("catalog_trash_expire_second" = "3600"); -- 1 hour

Summary Recommendation 1. Monitor the FE log during the 5-minute hang. If you see

OlapTableSink

Planner

taking a long time, it is likely the RPC batching issue mentioned in point #1. 2. Upgrade to v4.0.10+ if possible, as it contains several critical fixes for shared-data metadata performance and StarOS stability. 3. Verify S3 connectivity from the new pods to ensure no MTU or IAM role throttling is occurring. References • faq/shared_data_faq.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • deployment/shared_data/feature-support-shared-data.md • using_starrocks/caching/block_cache.md • deployment/shared_data/s3.md • deployment/shared_data/hdfs.md • deployment/shared_data/azure.md

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Rocky

05/31/2026, 4:12 PM

Stas Grinberg

05/31/2026, 8:42 PM

@Rocky We've confirmed the DESC TopN issue with EXPLAIN ANALYZE on a native StarRocks table (shared-data, 4.0.9): Table:

DUPLICATE KEY(event_timestamp)

ORDER BY (event_timestamp ASC)

+ 327M rows, daily RANGE partitions ASC query `ORDER BY event_timestamp ASC LIMIT 50`: • OLAP_SCAN OutputRows:2.95M→ 77ms total ✅ DESC query `ORDER BY event_timestamp DESC LIMIT 50`: • OLAP_SCAN OutputRows:310M→ 1.9s total ❌ Both queries have

WHERE event_timestamp >= DATE_SUB(NOW(), INTERVAL 90 DAY)

. We already have

desc_hint_split_range = 0

in CN config — it reduced overhead but DESC still scans all 310M rows instead of using reverse early termination. Questions: 1. Is there any way to get true reverse scan early termination for DESC TopN in 4.0.9 on a native table sorted ASC? 2. Is the inverted timestamp approach (

event_ts_inv = MAX_INT - unix_timestamp

) the only real solution in 4.0.9? 3. Is this fixed natively in 4.1?

Rocky

05/31/2026, 8:42 PM

The behavior you are seeing is a known characteristic of the LSM-tree storage engine in StarRocks 4.0.9. While StarRocks is extremely fast at forward scans, reverse scans on a large dataset often suffer from "Late Materialization" issues and the overlapping nature of rowsets in a shared-data (Lake) table. 1. Reverse Scan Early Termination in v4.0.9 In v4.0.9, true reverse early termination is difficult to achieve for a native table sorted

ASC

because: * LSM Overlap: To find the "Top 50" largest values, the engine must look at the tail of every rowset. Since rowsets overlap in their key ranges until fully compacted, the engine often has to merge multiple rowset tails, which triggers a much larger scan than a forward scan (where the prefix index and block min/max are optimized). * `desc_hint_split_range`: You correctly identified this parameter. It was introduced to help parallelize DESC queries by splitting the scan range. Setting it to

prevents it from splitting the range into too many small, inefficient sub-scans, but it does not solve the fundamental problem that the engine doesn't "know" it has found the top 50 rows until it has processed a significant portion of the segment metadata. 2. Is the inverted timestamp the only solution? In v4.0.x, there are two primary ways to fix this without changing your query: * The Inverted Column (Manual): As you noted,

event_ts_inv = MAX_INT - unix_timestamp

is the "brute force" way to align physical layout with query order. It works 100% of the time but adds storage overhead and complexity. * Synchronous Materialized View (Recommended): Instead of manual inversion, create a Synchronous MV with the reverse sort order. StarRocks will automatically route the

DESC

query to this MV:

Copy code

sql
    CREATE MATERIALIZED VIEW mv_event_desc AS
    SELECT event_timestamp, ...
    FROM your_table
    ORDER BY event_timestamp DESC; -- The MV will physically sort this DESC

Note: In shared-data mode, MVs are highly efficient and the optimizer is generally excellent at choosing the DESC MV for your query. 3. Is this fixed natively in v4.1? Yes, v4.1 significantly improves this specific scenario through a new feature called Segment Metadata Filtering for Lake tables: * v4.1 Optimization: Version 4.1 introduces more granular segment-level metadata filtering that allows the engine to skip irrelevant segments more aggressively during reverse scans. It also improves the "Short Circuit" logic for

LIMIT

queries in reverse order. * Inverted Index (Beta): v4.1 also introduces Inverted Index support for shared-data mode. While usually for full-text search, an inverted index on a timestamp can provide a fast "lookup" path for the latest values, though a sorted key is still preferred for range queries. Summary Recommendation * If you must stay on v4.0.9: Use a Synchronous Materialized View sorted

DESC

. This is the most "StarRocks-native" way to get sub-100ms DESC performance without the manual complexity of inverted columns. * If you can upgrade to v4.1: The native

DESC

scan performance is notably better due to segment-level pruning improvements, though for a 300M+ row table, a matching physical sort order (via MV) will always be the fastest possible path. References • table_design/sort_key.md • using_starrocks/Materialized_view.md • administration/management/BE_configuration.md (See

desc_hint_split_range

) • deployment/shared_data/shared_data_best_practice.md -practice)

Stas Grinberg

06/01/2026, 7:12 AM

@Rocky Hey Rocky, the v4.1.0 DANGER warning says "BE processes may fail to start in container environments". We run shared-data mode with CN nodes only (enabledBe: false). Does this container image issue affect CN nodes or is it strictly a BE-only problem? Is it safe for us to test v4.1.0?

Rocky

06/01/2026, 7:12 AM

The short answer is No, it is not safe to test v4.1.0 in your environment. Even though you are running in shared-data mode with CN nodes only, this specific issue affects you. Here is the breakdown: 1. Does it affect CN nodes? Yes. In StarRocks, the CN (Compute Node) and the BE (Backend) share the same underlying binary and core engine code (often referred to as the

starrocks_be

process). * The "unstable load order" issue (specifically related to

hash_util

static initialization) causes the process to segfault (SIGSEGV) immediately upon startup, before it even reaches the

main()

function. * Because this happens at the binary/library loading level, it triggers regardless of whether the node is configured as a BE or a CN. 2. Is it safe to test? No. If you are using the official

starrocks/cn-ubuntu:4.1.0

starrocks/be-ubuntu:4.1.0

container images, the nodes will likely fail to start or crash randomly. * GitHub Evidence: This was tracked in Issue #71731, where users reported CN nodes specifically crashing on Kubernetes using the operator. * The Fix: The fix was merged in PR #71825. Recommendation Skip v4.1.0 and use v4.1.1 (or later). As of June 2026, v4.1.1 is the stable patch that resolves this container initialization bug. It is safe to use for testing your shared-data performance improvements (like the reverse scan optimizations we discussed). One critical warning for your v4.1 testing: The release notes for v4.1 also include a Downgrade Warning: Once you upgrade a cluster to v4.1, you cannot downgrade to any v4.0 version earlier than v4.0.6. This is due to internal changes in how StarRocks 4.1 handles tablet splitting and metadata distribution. Since you are currently on v4.0.9, you should be able to move between v4.1 and v4.0.9 if needed, but always take a metadata backup before testing a major version jump.

Stas Grinberg

06/01/2026, 7:13 AM

@Rocky when the 4.1.1 is going to be released?

Rocky

06/01/2026, 7:14 AM

As of today, June 1, 2026, an official release date for v4.1.1 has not been announced, but it is expected to be released imminently (likely in early June). Based on the official documentation and recent release history, here is the current situation: * Status of v4.1.1: It is the next targeted patch release specifically designated to fix the v4.1.0 container initialization crash (Issue #71825) that we discussed. The documentation currently advises all container users to "Wait for v4.1.1" before moving to the 4.1 branch. * Release Cadence: StarRocks typically releases patch versions (e.g., x.x.1, x.x.2) every 2–3 weeks. Since v4.1.0 was released on April 13, 2026, and the v4.0 branch just received its v4.0.10 patch on May 9, 2026, v4.1.1 is slightly behind the usual schedule—likely because of the complexity of the fix for the container crash. * Next Steps: I recommend monitoring the StarRocks GitHub Releases page or the

#release

channel in the StarRocks Slack. Summary for your planning: Since you are currently on v4.0.9, you should not move to v4.1.0. Once v4.1.1 is out (expected within the next week or two), it will be the safe version for you to test both the container stability and the DESC scan optimizations. References • Release Notes for v4.1 (Source 1.6) • StarRocks Versioning and Release Guide (Source 1.10) • StarRocks GitHub Issues - #71825 (Container initialization fix)

Stas Grinberg

06/02/2026, 12:47 PM

@Rocky In StarRocks Operator, when I set
storageClassName
in
starrocksCnSpec.storageSpec
, does
logStorageSize
use the same StorageClass or a separate one? And what's the recommended process to migrate CN StatefulSet PVCs to a new StorageClass without data loss in shared-data mode?

Rocky

06/02/2026, 12:48 PM

In the StarRocks Operator, the storage configuration for Compute Nodes (CN) follows a specific inheritance pattern for StorageClasses, and migration in shared-data mode is uniquely simplified. 1. StorageClass for
logStorageSize
In

starrocksCnSpec.storageSpec

, the

storageClassName

applies to all volumes generated by that specification block. * Shared StorageClass: When you set

storageClassName

, it is used for both the primary data/cache volume (defined by

storageSize

) and the log volume (defined by

logStorageSize

). * Separate PVCs: Even though they share the same StorageClass, the Operator creates two separate PersistentVolumeClaims for each CN pod: *

<cluster>-cn-data-0

(mounted at

/opt/starrocks/cn/storage

for caching) *

<cluster>-cn-log-0

(mounted at

/opt/starrocks/cn/log

for logs) * Recommendation: If you require different performance characteristics for logs (e.g., cheaper HDD storage) vs. cache (e.g., high-speed NVMe), the current Operator does not support separate StorageClasses within a single

storageSpec

. Both will land on the class specified in

storageClassName

. 2. Migrating CN PVCs without Data Loss In shared-data mode, migrating CN nodes to a new StorageClass is significantly easier than in shared-nothing mode because CN nodes are stateless in terms of persistent data. What is at risk? * Logs: Any logs stored on the existing

cn-log

PVCs will be lost if deleted. * Cache: The

cn-data

PVC stores the query cache. If deleted, query performance may temporarily decrease while the CN node re-fetches data from your object storage (S3/MinIO/etc.). * Actual Data: Your table data is stored in object storage (S3), and your metadata is in the FE nodes. Deleting CN PVCs cannot cause data loss. Recommended Migration Process Since you cannot change the

storageClassName

of an existing StatefulSet/PVC, follow these steps to migrate to a new StorageClass: 1. Update the CRD: Edit your

StarRocksCluster

YAML (or Helm

values.yaml

) to set the new

storageClassName

. 2. Graceful Scaling: Scale your CN replicas to

first. This ensures all active queries and tablet metadata are gracefully handled by the FE.

Copy code

bash
    kubectl patch starrockscluster <name> --type='merge' -p '{"spec":{"starRocksCnSpec":{"replicas":0}}}'

3. Delete Old PVCs: Manually delete the old PVCs associated with the CN nodes.

Copy code

bash
    kubectl delete pvc -l "<http://starrocks.com/cluster-name=<cluster-name>,starrocks.com/component=cn|starrocks.com/cluster-name=<cluster-name>,starrocks.com/component=cn>"

4. Restore Replicas: Scale the CN replicas back to your original number.

Copy code

bash
    kubectl patch starrockscluster <name> --type='merge' -p '{"spec":{"starRocksCnSpec":{"replicas":3}}}'

5. Verification: The Operator will see the updated

storageClassName

and create brand-new PVCs using the new StorageClass. Your CN pods will start, reconnect to the FE, and begin rebuilding their local cache from the object storage. Note: If you absolutely must keep your logs, you would need to use a tool like

pv-migrate

to copy data from the old PVC to a new one before scaling back up, but for most users, starting with a fresh log volume and empty cache is the standard path. References • administration/management/logs.md • administration/management/BE_configuration.md • quick_start/helm.md • administration/management/FE_configuration.md

Stas Grinberg

06/02/2026, 1:09 PM

@Rocky _Should

datacache_disk_size

be set to the full usable PVC size (e.g. 3T) and let

disk_high_level/disk_safe_level

handle eviction automatically? Or should

datacache_disk_size

itself be set to ~80% of the PVC (2560G) as a hard cap before eviction kicks in?_ _What is the correct relationship between

datacache_disk_size

disk_high_level

, and

disk_safe_level

Rocky

06/02/2026, 1:10 PM

For your CN nodes in shared-data mode, you should set

datacache_disk_size

as a hard cap at approximately 80–85% of your PVC size rather than the full 3T. Setting it to 100% of the PVC is risky because

disk_high_level

and

disk_safe_level

are reactive safety triggers, while

datacache_disk_size

is the proactive quota for the cache engine. The Recommended Configuration * Target Size: Set

datacache_disk_size

to ~2500G (for a 3T PVC). * Reasoning: You must leave headroom for non-cache data that shares the same PVC, such as: * Process Logs: Standard StarRocks logs and slow query logs. * Core Dumps: If a CN crashes, it will write a core dump to the storage path, which can be tens of GBs. * Metadata/Indexes: Primary Key indexes and local metadata are stored under the

storage_root_path

. Relationship Between Parameters Parameter | Function | Typical Value datacache_disk_size
| The maximum quota allocated to the Block Cache. The cache engine will not attempt to exceed this value. |

80%

2500G

disk_high_level
| A safety threshold based on total filesystem usage. If the entire disk (Cache + Logs + System) hits this %, the system forces cache eviction. |

(90%) disk_safe_level
| The target cleanup point. When eviction is triggered by

disk_high_level

, the system evicts until total disk usage drops to this level. |

(80%) How They Work Together 1. Normal Operation: The Data Cache fills up until it hits

datacache_disk_size

. At that point, it starts its own internal LRU (Least Recently Used) eviction to make room for new data. 2. Safety Trigger: If your logs grow unexpectedly and the total PVC usage hits

disk_high_level

(90%), the

datacache_auto_adjust_enable

mechanism (on by default) will override the cache quota and force-evict data until the disk usage drops back to

disk_safe_level

(80%). 3. The Conflict: If you set

datacache_disk_size

to 100% and

disk_safe_level

to 80%, the system will constantly be in a "tug-of-war"—the cache engine will try to fill the disk to 100%, while the auto-adjuster will constantly delete the cache to pull it back to 80%. Summary Best Practice: Set

datacache_disk_size

to your desired usable cache size (e.g., 80% of PVC). Ensure

disk_high_level

is set slightly higher (e.g., 90%) to act as an emergency brake if other files (like logs) begin to consume your PVC space. _Note: In shared-data clusters, if you don't set

datacache_disk_size

, StarRocks uses the greater of

datacache_disk_size

starlet_star_cache_disk_size_percent

(which defaults to 80)._ References • administration/management/BE_configuration.md • using_starrocks/caching/block_cache.md • data_source/data_cache.md • knowledge/usage/best-practice.md

Stas Grinberg

06/05/2026, 10:05 AM

@Rocky, Do you have a Grafana dashboard JSON for StarRocks already set up, or should I use the official one from GitHub? • "Is Prometheus already scraping the StarRocks FE/CN metrics endpoints?" • "What's the Prometheus data source URL we should use in Coralogix?"

Rocky

06/05/2026, 10:05 AM

I don't have direct access to your private infrastructure or workspace settings, so I can't check the live status of your Prometheus scrape jobs. However, I can provide the official resources and standard configurations you need. 1. Grafana Dashboard JSON You should use the official StarRocks templates. There isn't one "universal" JSON; you should pick the one that matches your architecture: * For General/Shared-nothing: Dashboard-All-Arch.json * For Shared-data (CN) Clusters: * General Dashboard (Overview) * Starlet Dashboard (Internal storage engine details) * For Kubernetes-specific (Helm): StarRocks-Shared_data-for-k8s.json 2. Is Prometheus already scraping metrics? You can verify this yourself by checking the Prometheus UI or your

prometheus.yml

configuration. The standard StarRocks metrics endpoints are: * FE Nodes:

http://<FE_IP>:8030/metrics

* CN/BE Nodes:

http://<CN_IP>:8040/metrics

How to verify: 1. Open your Prometheus UI (usually port 9090). 2. Go to Status -> Targets. 3. Look for a job named

starrocks

or similar. If the targets for ports

and

are "UP", metrics are being collected. 4. If they aren't there, you need to add them to your scrape config:

Copy code

yaml
    scrape_configs:
      • job_name: 'starrocks'
        metrics_path: '/metrics'
        static_configs:
          • targets: ['<FE_IP>:8030']
            labels: {group: fe}
          • targets: ['<CN_IP>:8040']
            labels: {group: cn}

3. Prometheus Data Source URL for Coralogix If you are using Coralogix as your managed Grafana/Prometheus provider, the "Data Source URL" depends on your Coralogix account region. Typically, you don't point Coralogix at StarRocks. Instead, your local Prometheus remote-writes to Coralogix, or you configure a Prometheus Data Source in Grafana using your Coralogix endpoint. * The URL format usually looks like:

https://<coralogix_domain>/prometheus/v1

* Examples by Region: * US1:

<https://coralogix-dashboards.coralogix.com/prometheus/v1>

* EU1:

<https://coralogix-dashboards.coralogix.eu/prometheus/v1>

* Auth: You will need to add a custom HTTP Header in the Grafana data source: * Key:

Authorization

* Value:

Bearer <Your_Coralogix_Alerts_API_Key>

To get your exact URL: Go to the Coralogix UI under Settings -> Integration -> Prometheus to see the specific remote-write or query URL for your account. References • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/metrics-shared-data.md • using_starrocks/caching/block_cache.md References • integrations/other_integrations/datadog_support.md • administration/management/monitoring/Monitor_and_Alert.md

Stas Grinberg

06/08/2026, 12:15 PM

@Rocky Hey, we're setting up proper user access management for StarRocks and need your help on two things: 1. Azure AD SSO for StarRocks We use Azure AD for company authentication and want analysts to connect to StarRocks using their existing Microsoft credentials — no separate StarRocks passwords. • Can we configure StarRocks to authenticate against Azure AD via LDAP or OIDC/OAuth2? • What do you need from us to set it up? (tenant ID, app registration, etc.) • Is there anything already configured in the current StarRocks Helm/operator deployment for SSO? 2. Superset → StarRocks user passthrough Superset already has Azure AD SSO — analysts log in with their Microsoft accounts. We want Superset to pass the logged-in user's identity to StarRocks so queries run as the individual user, not a shared service account. • Does Superset support "Impersonate Logged In User" with the StarRocks SQLAlchemy driver? • If yes, how do we configure it on the database connection? • If not, what's the recommended approach — per-analyst connection credentials in Superset profile?

Rocky

06/08/2026, 12:15 PM

Setting up Azure AD SSO and identity passthrough for StarRocks involves two main parts: configuring the database to accept modern authentication and configuring Superset to utilize StarRocks' native impersonation features. 1. Azure AD SSO for StarRocks StarRocks supports Azure AD (Entra ID) through two primary methods: OAuth 2.0/OIDC (supported from v3.5.0+) or LDAP (using Azure AD Domain Services). Recommended: OAuth 2.0 (OIDC) Starting with StarRocks v3.5.0, you can configure a "Security Integration" to handle OIDC flows directly. * Requirements from Azure: * Application (Client) ID and Client Secret. * Directory (Tenant) ID. * Redirect URI: Usually

https://<fe_ip>:8030/api/oauth2

(for Web UI) or the specific endpoint for your application. * StarRocks Configuration: You must create a security integration and update the FE configuration chain.

Copy code

sql
    CREATE SECURITY INTEGRATION azure_ad_sso
    PROPERTIES (
        "type" = "authentication_oauth2",
        "auth_server_url" = "<https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize>",
        "token_server_url" = "<https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token>",
        "jwks_url" = "<https://login.microsoftonline.com/<tenant_id>/discovery/v2.0/keys>",
        "client_id" = "<client_id>",
        "client_secret" = "<client_secret>",
        "principal_field" = "upn", -- or "sub"/"email" depending on your AD claims
        "required_issuer" = "<https://login.microsoftonline.com/<tenant_id>/v2.0>"
    );

* Helm/Operator Setup: You must set the

authentication_chain

in your FE configurations (via

starrocksFeSpec.config

in the Operator) to include your new integration:

Copy code

yaml
    authentication_chain: "azure_ad_sso,native"

2. Superset → StarRocks Identity Passthrough StarRocks supports user identity passthrough via the IMPERSONATE
privilege and the EXECUTE AS
command. * Does Superset support this? Yes. The

starrocks

SQLAlchemy dialect is compatible with Superset's "Impersonate logged in user" feature. When enabled, Superset will attempt to run

EXECUTE AS "<logged_in_user>" WITH NO REVERT;

before executing the actual query. * How to configure: 1. Grant Permissions: The service account defined in your Superset connection string (e.g.,

superset_svc

) must have the power to impersonate your analysts.

Copy code

sql
        -- Grant the service account permission to act as anyone
        GRANT IMPERSONATE ON USER ALL TO USER 'superset_svc'@'%';

2. Superset Connection Settings: * Go to Data -> Databases -> Edit [Your StarRocks Connection]. * Navigate to the Advanced tab -> Security. * Check the box: "Impersonate logged in user". * Recommendation: Ensure the usernames in StarRocks exactly match the usernames (usually email or UPN) passed by Superset after the Azure AD login. If they don't match, you may need a custom

SQL_QUERY_MUTATOR

in your

superset_config.py

to map identities. Summary Checklist 1. Verify Version: Ensure you are on StarRocks v3.5.0 or later for native OIDC support. 2. App Registration: Create the Azure App Registration and collect the IDs/Secret. 3. Security Integration: Run the

CREATE SECURITY INTEGRATION

SQL command. 4. FE Config: Update

authentication_chain

in your Helm

values.yaml

or Operator spec. 5. Privileges: Grant

IMPERSONATE

to your Superset service account. 6. Superset UI: Toggle the "Impersonate" checkbox in the database settings. References • administration/user_privs/authentication/security_integration.md • administration/user_privs/authentication/ldap_authentication.md

Rocky

06/08/2026, 12:15 PM

• integrations/superset_support.md • administration/user_privs/authorization/privilege_item.md References • administration/user_privs/authentication/ldap_authentication.md • administration/user_privs/authentication/security_integration.md

Stas Grinberg

06/14/2026, 9:17 AM

_Hi @Rocky, we're seeing

fslib_open_cache_hits_total

spike only during heavy workloads (Jun 10-11) but after running a single

SELECT COUNT(*) FROM table

query today, we see nothing in the metric even at Last 1 hour scale._ Questions: 1. _Does`fslib_open_cache_hits_total`reflect every data scan or only specific types of queries? Is there a minimum scan size for it to register?_ 2. _Is this the right metric to track cache hit rate for data reads, or should we use something else like`starrocks_be_block_cache_hit_bytes_total`?_ 3. What's the recommended way to verify the local NVMe cache is being used for a specific query?

Rocky

06/14/2026, 9:17 AM

The behavior you're seeing—spikes during heavy workloads but flatlines for a single `COUNT(*)`—is expected because

fslib_open_cache_hits_total

is a metadata-level metric, not a data-transfer metric. 1. Does
fslib_open_cache_hits_total
reflect every scan? No.

fslib_open_cache_hits_total

tracks the reuse of file handles/descriptors at the internal filesystem library level (which abstracts remote storage like S3 or HDFS). * Why it spikes during heavy workloads: When many concurrent queries access the same sets of files, StarRocks reuses the open file objects in its internal "open file cache" to avoid the overhead of re-authenticating and re-opening a connection to remote storage. * Why a single `COUNT()` shows nothing:* If the table is small or the query is optimized to use metadata (e.g., footers of Parquet/ORC files), it may only open 1 or 2 files. Even if those "opens" were hits, the count is too small to register a visible spike on a Prometheus graph. Furthermore,

COUNT(*)

often skips reading the actual data blocks entirely, focusing only on metadata. 2. Is this the right metric for cache hit rate? No,

fslib_open_cache

is for file-handle overhead. To track the actual data block hit rate on your NVMe disks, you should use the following metrics: * starrocks_be_block_cache_hit_bytes_total
: The amount of data served directly from your local NVMe cache. * starrocks_be_block_cache_miss_bytes_total
: The amount of data that had to be fetched from remote storage (S3/HDFS). * starrocks_be_block_cache_read_bytes_total
: The total data requested from the cache layer. Recommended Hit Rate Formula:

sum(rate(starrocks_be_block_cache_hit_bytes_total[5m])) / sum(rate(starrocks_be_block_cache_read_bytes_total[5m]))

3. Recommended way to verify NVMe cache for a specific query The most reliable way to verify cache usage is the Query Profile. 1. Enable the profile for your session:

Copy code

sql
    SET enable_profile = true;

2. Run your query. 3. Retrieve the profile (via the FE UI at

http://<FE_IP>:8030/query

or using

get_query_profile()

). 4. Search for the ScanNode
section. Look for these specific keys: * DataCacheReadBytes
: Data successfully read from your local NVMe/Memory cache. * DataCacheWriteBytes
: Data that was missed, fetched from remote, and then written to your NVMe cache. * BytesRead
: The total data read (Remote + Local). Verification: If

DataCacheReadBytes

is equal (or very close) to

BytesRead

, the query was served 100% from your local NVMe cache. Summary * fslib_open_cache
: Use this to monitor remote storage connection/handle overhead. * block_cache_hit_bytes
: Use this for overall system hit rate. * Query Profile: Use this to debug why a specific query is slow or to verify cache population. References • data_source/data_cache_observe.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md • data_source/data_cache_troubleshooting.md ) References • data_source/data_cache.md • data_source/data_cache_troubleshooting.md • using_starrocks/caching/block_cache.md • using_starrocks/caching/query_cache.md • data_source/data_cache_observe.md

Stas Grinberg

06/14/2026, 2:40 PM

@Rocky Can you confirm EXECUTE AS works on StarRocks MySQL port 9030 in v4.1.1? Or is there a specific SQLAlchemy connection option in Superset that triggers this?"

Rocky

06/14/2026, 2:41 PM

Yes, I can confirm that EXECUTE AS
is fully supported on the StarRocks MySQL port (9030) in version 4.1.1. In fact, this feature has been available since v2.4, and in v4.1.1 it remains the standard way to handle session-level identity switching for impersonation. 1. How Superset triggers it There is no special SQLAlchemy connection string parameter (like

?impersonate=true

) required in the URI. Instead, Superset handles this at the application level: * Trigger: In the Superset Database Connection settings (Advanced -> Security), check the box "Impersonate logged in user". * Action: When this is enabled, the

starrocks

SQLAlchemy dialect (via the

starrocks

Python driver) detects the user context. Before executing your analyst's query, Superset issues the following SQL command on that specific connection:

Copy code

sql
    EXECUTE AS "analyst_name" WITH NO REVERT;

* Result: All subsequent queries on that session will run with the permissions of

analyst_name

. 2. Requirements for it to work For this handshake to succeed on port 9030, you must ensure: 1. Privileges: The service account you put in the Superset SQLAlchemy URI (e.g.,

superset_svc

) must have the

IMPERSONATE

privilege on the users it intends to impersonate:

Copy code

sql
    -- Grant permission to impersonate a specific analyst
    GRANT IMPERSONATE ON USER 'analyst_name' TO USER 'superset_svc';

    -- Or grant permission to impersonate anyone (common for Superset)
    GRANT IMPERSONATE ON USER ALL TO USER 'superset_svc';

2. User Matching: The username Superset passes must exist in StarRocks. If Superset uses email addresses (from Azure AD) but StarRocks users are short names, you will need to map them or ensure they match exactly. 3. Driver Version: Ensure you are using a recent version of the

starrocks

Python library (

pip install starrocks

). While older versions work, version 1.0.6+ is recommended for compatibility with SQLAlchemy 1.4/2.0 and Superset's impersonation logic. 3. Verification You can verify the identity passthrough is working by running this query in Superset's SQL Lab:

Copy code

sql
SELECT current_user();

If configured correctly, it should return your personal Microsoft/Azure AD username (the one you logged into Superset with), not the

superset_svc

account. References • administration/user_privs/authentication/security_integration.md • sql-reference/sql-statements/account-management/EXECUTE_AS.md • integrations/BI_integrations/Superset.md • administration/user_privs/authorization/privilege_item.md References • integrations/superset_support.md • integrations/smt/starrocks_sqlalchemy.md • integrations/BI_integrations/Superset.md • faq/Others.md • integrations/authenticate_to_aws_resources.md • administration/user_privs/authorization/privilege_item.md

Stas Grinberg

07/02/2026, 10:17 AM

@Rocky Rocky, architecture question: Current setup: Kafka → ingestion service → Iceberg/S3 → StarRocks external catalogs. CN nodes are read-only (queries + cache). We ingest ~24TB/day. Some team members want to simplify by dropping Iceberg and streaming Kafka directly into StarRocks native tables (Routine Load). Concerns: • 24TB/day write load moves onto CN nodes, competing with read queries • No CN read/write separation today • Loses open format — SageMaker/Python can't read native tables directly Questions: 1. At this volume, is native streaming feasible without degrading query performance on the same CNs? 2. Should we separate CN pools for reads vs writes? 3. Is a hybrid (native for hot data, Iceberg for cold/long-term) worth considering?

Rocky

07/02/2026, 10:18 AM

Moving 24TB/day from an external Iceberg/S3 flow to a native StarRocks streaming architecture is a significant shift. At this scale (~280MB/s average, likely 500MB/s+ peak), your concerns regarding resource contention and data openness are valid. 1. Feasibility of Native Streaming (24TB/day) At this volume, native streaming via Routine Load is feasible but requires a distributed approach to avoid degrading query performance. * Ingestion Overhead: 24TB/day translates to heavy CPU usage for data parsing, segment compression, and background compaction. * Performance Optimization: In shared-data (CN-based) architecture, ensure you are on v3.2.3+ and enable

lake_enable_batch_publish_version = true

. This optimization significantly improves transaction commit throughput, which is the typical bottleneck for high-volume streaming. * Small Files: Routine Load can create a "small file problem" if not tuned. You must adjust

max_batch_interval

(increase to 10–20s) and

max_batch_rows

to ensure StarRocks writes larger, more efficient segments to S3. 2. Read/Write Separation with CN Pools In a standard shared-data deployment, all CN nodes are typically treated as a single compute pool. However, to prevent ingestion from "starving" your analysts' queries, you should use Resource Groups: * Logical Isolation: Create a dedicated Resource Group for your Routine Load jobs.

Copy code

sql
    CREATE RESOURCE GROUP ingestion_rg
    TO (user='ingest_user') -- Map your Routine Load user here
    WITH (
        "cpu_weight" = "10", -- Give it lower priority than analyst queries
        "mem_limit" = "30%"   -- Cap memory usage to prevent OOM during heavy spikes
    );

* Physical Isolation (Advanced): While the open-source FE distributes query fragments globally, you can physically isolate workloads by deploying distinct CN node groups (e.g., via K8s node selectors) and using Resource Groups to limit the total concurrency and CPU usage on specific nodes. If you're on a version that supports Compute Clusters/Warehouses, you can direct all ingestion to one cluster and reads to another. 3. Solving the "Open Format" Concern Your team is right that StarRocks native tables aren't "readable" directly by S3 Parquet readers, but they are not closed. * High-Speed Access (Arrow Flight SQL): For SageMaker and Python (Pandas/Polars), use the Arrow Flight SQL protocol (port 9408). It provides a columnar, zero-copy pipeline that is 20x–100x faster than traditional JDBC/MySQL. This is the recommended way for data scientists to pull large native datasets into Python. * Spark Connector: The StarRocks Spark connector allows SageMaker jobs to read StarRocks tables as DataFrames, maintaining high performance through parallel scans. 4. Is a Hybrid Approach Worth It? Yes. A hybrid architecture is often the most cost-effective and performant "sweet spot": * Native (Hot Data): Store the last 7–30 days in native tables. This provides the sub-second performance needed for dashboards and handles the high-velocity Kafka stream efficiently. * Iceberg (Cold Data): Use an Export job or Insert Into (External Catalog) to move older data into Iceberg on S3. * Unified View: Use a View or Materialized View to join the hot (native) and cold (Iceberg) data. Analysts query a single object, and StarRocks handles the cross-catalog join transparently. Recommendation 1. Don't drop Iceberg entirely. It is your safety net for data portability. 2. Pilot Routine Load for your highest-priority "hot" tables first. 3. Implement Resource Groups immediately to cap the ingestion impact. 4. Expose data to SageMaker via Arrow Flight SQL to prove that "native" doesn't mean "inaccessible." References • loading/RoutineLoad.md • unloading/arrow_flight.md

Rocky

07/02/2026, 10:18 AM

• administration/management/resource_management/resource_group.md • data_source/catalog/iceberg/iceberg_catalog.md • knowledge/usage/load/shared_data_batch_ingestion.md References • knowledge/usage/best-practice.md • loading/loading_introduction/loading_considerations.md • loading/loading_introduction/troubleshooting_loading.md

Stas Grinberg

07/04/2026, 5:53 AM

@Rocky, what is the best and efficient way to export 500M records from starrocks native tables on s3 to another s3 with some simple sql transformation

Rocky

07/04/2026, 5:53 AM

For exporting 500 million records with SQL transformations from StarRocks to S3, the most efficient and modern method is INSERT INTO FILES()
, introduced in StarRocks v3.2. This method is superior for large-scale exports because it is fully distributed (leveraging all BE/CN nodes) and supports full SQL expressiveness for your transformations. 1. Recommended Method:
INSERT INTO FILES()
(Best for v3.2+) This approach allows you to treat a destination S3 path as a writable "file sink" directly within a standard SQL

INSERT

statement. Example Syntax:

Copy code

sql
INSERT INTO FILES (
    "path" = "<s3://your-bucket/export-data/>",
    "format" = "parquet", -- Highly recommended for 500M records
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY"
)
SELECT 
    id, 
    UPPER(user_name) as user_name, -- Simple transformation
    price * 0.9 as discounted_price, -- Arithmetic transformation
    DATE_FORMAT(created_at, '%Y-%m-%d') as event_date -- Formatting
FROM starrocks_native_table
WHERE created_at >= '2023-01-01';

Why this is the best way: * Performance: It uses the same distributed execution engine as your queries. Every Compute Node (CN) or Backend (BE) involved in the

SELECT

can write its results in parallel directly to S3. * Transformations: Since it uses a standard

SELECT

clause, you can use joins, aggregations, window functions, and complex UDFs before the data hits S3. * Partitioning: You can even use the

partition_by

property to organize the exported files into a partitioned directory structure (e.g.,

/year=2023/month=01/

). ────────── 2. Alternative:
INSERT INTO
an External Catalog (Iceberg/Hive) If you need the data to be immediately queryable by other engines (like Spark, Trino, or Athena) in a structured way, use an External Catalog. 1. Create an External Catalog (e.g., Iceberg):

Copy code

sql
    CREATE EXTERNAL CATALOG s3_iceberg
    PROPERTIES (
        "type"="iceberg",
        "iceberg.catalog.type"="rest",
        "aws.s3.region"="us-east-1"
    );

2. Insert with Transformation:

Copy code

sql
    INSERT INTO s3_iceberg.db.target_table
    SELECT transformed_col1, transformed_col2 FROM native_table;

* Pros: Data is written in standard Open Table Formats; fully parallel. * Cons: Requires setting up an external catalog/metadata layer (Glue/Hive Metastore). ────────── 3. Comparison with Older Methods * EXPORT
command: This is a background job. While it is distributed, it is primarily designed for full table or partition dumps. Its transformation capabilities are very limited (mostly just column selection, no complex expressions or joins). * `SELECT ... INTO OUTFILE`: This is the traditional MySQL-style export. In StarRocks, it supports transformations but is typically used for smaller result sets. For 500M records,

INSERT INTO FILES()

is significantly more robust and better at handling the high concurrency needed for half a billion rows. Summary Checklist for 500M Records: 1. Format: Use

parquet

orc

. Exporting 500M records to CSV will be much slower and result in much larger files. 2. Timeout: If using

INSERT INTO FILES()

, the session might timeout for very long jobs. Consider increasing the session variable:

SET query_timeout = 36000;

(10 hours). 3. Parallelism: StarRocks will automatically parallelize the write based on the number of tablets in your source table and the number of available BE/CN nodes. References • unloading/unload_using_insert_into_files.md • sql-reference/sql-statements/loading_unloading/unloading/EXPORT.md • data_source/catalog/iceberg/iceberg_catalog.md

Rocky

07/04/2026, 5:53 AM

• loading/s3.md References • unloading/unload_using_insert_into_files.md • unloading/Export.md

Rocky

07/04/2026, 5:54 AM

For exporting 500 million records with SQL transformations from StarRocks to S3, the most efficient and modern method is INSERT INTO FILES()
, introduced in StarRocks v3.2. This method is superior for large-scale exports because it is fully distributed (leveraging all CN/BE nodes) and supports full SQL expressiveness for your transformations. 1. Recommended Method:
INSERT INTO FILES()
This approach allows you to treat a destination S3 path as a writable "file sink" directly within a standard SQL

INSERT

statement. Example Syntax:

Copy code

sql
-- Increase timeout for large 500M record export
SET query_timeout = 36000; 

INSERT INTO FILES (
    "path" = "<s3://your-bucket/export-data/>",
    "format" = "parquet", -- Highly recommended for 500M records
    "compression" = "zstd", -- Good balance of speed and size
    "target_max_file_size" = "1073741824", -- 1GB per file (default)
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY"
)
SELECT 
    id, 
    UPPER(user_name) AS user_name, -- String transformation
    price * 0.9 AS discounted_price, -- Arithmetic
    DATE_FORMAT(created_at, '%Y-%m-%d') AS event_date -- Formatting
FROM starrocks_native_table
WHERE created_at >= '2024-01-01';

Why this is the best way: * Performance: It uses the same distributed execution engine as your queries. Every Compute Node (CN) or Backend (BE) involved in the

SELECT

can write its results in parallel directly to S3. * Transformations: Since it uses a standard

SELECT

clause, you can use joins, aggregations, and complex UDFs before the data hits S3. * Scalability: It handles 500M records easily by splitting the output into multiple files (default 1GB each) to avoid S3 single-object bottlenecks. ────────── 2. Alternative:
EXPORT
(Asynchronous) If you prefer the export to run as a background job (so your SQL client doesn't need to stay connected), use the

EXPORT

command. However, its transformation capabilities are limited compared to

INSERT INTO FILES()

. * Pros: Asynchronous (non-blocking), better for massive full-table dumps. * Cons: Does not support complex SQL transformations in the

SELECT

list (mostly column selection and simple filters). ────────── 3. Comparison with
SELECT ... INTO OUTFILE
The

INTO OUTFILE

syntax is the traditional MySQL-style export. In StarRocks: * It supports transformations. * Limitation: It is generally intended for smaller result sets or single-file exports. For a 500M record dataset,

INSERT INTO FILES()

is more robust as it is designed for high-throughput, multi-file distributed writing. Best Practices for 500M Records: 1. Format: Use

parquet

orc

. Exporting 500M records to CSV will be significantly slower and result in much larger, harder-to-manage files. 2. Timeout: Large exports can exceed the default 5-minute timeout. Always set

SET query_timeout = 36000;

(10 hours) in your session before running the command. 3. Partitioning: If you need the data organized on S3 for other tools (like Athena or Spark), you can use the

partition_by

property in

INSERT INTO FILES()

to create a directory structure like

/year=2024/month=01/

. References • unloading/unload_using_insert_into_files.md • sql-reference/sql-statements/loading_unloading/unloading/EXPORT.md • loading/s3.md • sql-reference/sql-functions/table-functions/files.md References • loading/s3.md • unloading/unload_using_insert_into_files.md

Stas Grinberg

07/04/2026, 5:57 AM

@Rocky can you estimate the timing for this 500M export in same region and 3 NVme nodes of CNs

Rocky

07/04/2026, 5:57 AM

Based on the architecture you've described (3 CN nodes with NVMe in the same region as S3) and a 500M record dataset, here is an estimation for an export using INSERT INTO FILES()
. Estimated Timing: 5–15 Minutes For a typical 500M record table (assuming ~10–15 columns of mixed types), you can expect the process to take roughly 5 to 15 minutes. Breakdown of the Estimate * Throughput per Node: On high-performance CN nodes (NVMe implies modern instances like AWS

c6id

r6id

), StarRocks can typically push 100MB/s to 200MB/s of Parquet-encoded data per node to S3 when in the same region. * Aggregate Throughput: With 3 nodes, you are looking at an aggregate write speed of 300MB/s to 600MB/s. * Data Volume: 500M records usually translate to roughly 50GB–150GB of data in Snappy/Zstd compressed Parquet format (depending on your row width). * Calculation: 100GB / 400MB/s (avg) ≈ 250 seconds (~4 minutes). Adding overhead for query planning, final S3 file commits, and transformation logic brings the realistic window to 5–15 minutes. Factors That Will Influence the Speed 1. Row Width: If your 500M records are very "wide" (e.g., 100+ columns or large JSON/Strings), the data volume increases, and the CPU cost of Parquet encoding becomes the primary bottleneck. 2. Transformation Complexity: "Simple" transformations (arithmetic,

UPPER()

CASE

statements) are handled in-memory and typically won't slow down the export. If the transformations involve heavy JOINS or Window Functions, the

SELECT

portion of the query may become the bottleneck rather than the S3 write. 3. Concurrency & Scaling: Since

INSERT INTO FILES()

is fully distributed, the work is split across your 3 CNs. If you find the speed is insufficient, adding more CN nodes scales this export performance linearly. 4. Target File Size: Using the default

target_max_file_size

(1GB) is optimal. Setting it too small (e.g., 100MB) will increase the number of S3 API calls and potentially slow down the job. Recommendations for Maximum Efficiency * Use Parquet: It is significantly faster than CSV for this volume because StarRocks can encode it in parallel more efficiently. * Set Session Variables: Ensure your session doesn't time out:

Copy code

sql
    SET query_timeout = 3600; -- 1 hour
    SET pipeline_dop = 0;    -- Let StarRocks auto-parallelize based on CPU cores

* Monitoring: You can track the progress in real-time by opening a second session and running:

Copy code

sql
    SELECT * FROM information_schema.loads WHERE TYPE = 'INSERT' ORDER BY CREATE_TIME DESC;

References • unloading/unload_using_insert_into_files.md • sql-reference/sql-statements/loading_unloading/unloading/EXPORT.md • administration/management/resource_management/resource_group.md • loading/s3.md References • unloading/unload_using_insert_into_files.md • unloading/Export.md • loading/s3.md References • knowledge/usage/load/shared_data_batch_ingestion.md • loading/s3.md

Stas Grinberg

07/04/2026, 5:59 AM

@Rocky can we separate this operation from another read compute

Rocky

07/04/2026, 5:59 AM

In StarRocks, especially in the Shared-Data (storage-compute separation) architecture, you can achieve this isolation through two primary methods: Resource Groups (logical isolation) or Multi-Warehouse/Compute Groups (physical isolation). 1. Logical Isolation: Resource Groups (Recommended) This is the most common way to isolate workloads within a single cluster. You can create a dedicated resource group for your export and restrict its CPU and Memory usage so it doesn't "starve" your concurrent read queries. How to set it up: 1. Create a Resource Group:

Copy code

sql
    CREATE RESOURCE GROUP export_group
    WITH (
        "exclusive_cpu_cores" = "10", -- Reserve specific cores (v3.3+)
        "mem_limit" = "30%",          -- Limit to 30% of BE/CN memory
        "concurrency_limit" = "2"     -- Limit how many exports run at once
    );

2. Route your Export: Before running your

INSERT INTO FILES()

statement, set the session to use this group:

Copy code

sql
    SET resource_group = 'export_group';

    INSERT INTO FILES (...)
    SELECT ... FROM table;

_Result: The 500M record export will only consume the resources allocated to

export_group

, keeping your main read compute snappy._ ────────── 2. Physical Isolation: Multi-Warehouse (v3.3+ Shared-Data) If you are running a Shared-Data cluster (CN nodes with data on S3), StarRocks supports a "Multi-Warehouse" architecture. This allows you to have multiple sets of CN nodes (Compute Groups) pointing to the same data on S3. How it works: * Cluster A (Read): Dedicated to dashboards and ad-hoc queries. * Cluster B (ETL/Export): Your 3 NVMe CN nodes dedicated only to large data operations. Syntax to switch: If your admin has set up multiple compute groups (warehouses), you can route your session to the dedicated export nodes:

Copy code

sql
SET warehouse = 'export_warehouse'; -- Directs the query to the specific CN nodes

INSERT INTO FILES (...)
SELECT ... FROM table;

*Note: The "Warehouse" feature is the most robust way to ensure that a massive 500M record export has zero performance impact on your production read cluster, as they are physically separate hardware nodes.* ────────── 3. Tuning for Concurrent Performance Regardless of how you isolate the compute, you should also tune the following to prevent the export from putting too much pressure on the shared I/O: * `pipeline_sink_dop`: Controls the parallelism of the S3 write. For 500M records, you might want to manually cap this if you notice network congestion:

Copy code

sql
    SET pipeline_sink_dop = 8;

* `query_mem_limit`: Even if the resource group has a limit, you should set a per-query safety cap to prevent a single complex transformation from crashing a node:

Copy code

sql
    SET query_mem_limit = 17179869184; -- 16GB

Summary Recommendation If you have 3 dedicated NVMe CN nodes for this, the best way is to place them in a separate Compute Group/Warehouse (if in Shared-Data mode) or use a Resource Group with
exclusive_cpu_cores
to ensure they handle the heavy lifting without impacting your primary query nodes' affecting the rest of the cluster. References • administration/management/resource_management/resource_group.md • unloading/unload_using_insert_into_files.md • introduction/Architecture.md • sql-reference/System_variable.md References • administration/management/resource_management/resource_group.md • unloading/unload_using_insert_into_files.md

Rocky

07/04/2026, 5:59 AM

• sql-reference/System_variable.md • introduction/Architecture.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • quick_start/shared-data.md • deployment/shared_data/feature-support-shared-data.md • faq/resource_isolation_faq.md References • administration/management/resource_management/resource_group.md • sql-reference/System_variable.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md • quick_start/shared-data.md

Stas Grinberg

07/08/2026, 1:56 PM

@Rocky , resource group is linked to users? also what is the limitations for how many users allowed

Rocky

07/08/2026, 1:56 PM

Yes, resource groups are linked to users primarily through Classifiers, though you can also link them manually for a specific session. 1. How Resource Groups Link to Users There are two ways to associate a user with a resource group: * Automatic (Classifiers): You define rules (classifiers) that tell StarRocks: "If this user (or role) runs a query, put it in this resource group."

Copy code

sql
    -- Example: Automatically route user 'bi_user' to the 'analytics_group'
    CREATE RESOURCE GROUP analytics_group
    TO (user='bi_user')
    WITH ("cpu_core_limit" = "10");

_Classifiers can match based on:

user

role

db

(database),

ip

(client IP), or

query_type

(e.g.,

SELECT

INSERT

)._ * Manual (Session Variable): A user can manually "switch" their current session to a specific resource group if they have the necessary permissions.

Copy code

sql
    SET resource_group = 'export_group';

2. Limitations on Number of Users There is no hard architectural limit on the number of users you can create in StarRocks (e.g., it isn't capped at 100 or 500). However, there are practical and configuration-based limits you should know: * System-Wide Users: You can typically have thousands of users. The only real constraint is the memory consumed by metadata on the Frontend (FE) nodes, but user metadata is very small. * Per-User Connection Limits: By default, StarRocks might limit how many simultaneous connections a single user can have to prevent one person from exhausting all FE resources. You can increase this:

Copy code

sql
    -- Check current limit
    SHOW PROPERTY FOR 'root';
    -- Increase limit for a specific user
    ALTER USER 'jack' SET PROPERTIES ("max_user_connections" = "1000");

* Roles per User: By default, a single user can be assigned a maximum of 64 roles (

privilege_max_total_roles_per_user

). * Classifier Matching: While there isn't a strict limit on the number of classifiers, having an extremely high number (e.g., thousands of individual user-to-group rules) can slightly increase the overhead of query planning. It is better to use Roles to group users and then link the Resource Group to the Role. Recommendation for your 500M Export: Instead of creating a new user, the most "efficient" way is to: 1. Use your existing admin/ETL user. 2. Set the resource group for that specific export session using

SET resource_group = 'your_export_group';

. 3. This ensures the export uses the dedicated resources you've allocated without needing to manage a new user identity. References • administration/management/resource_management/resource_group.md • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md References • faq/Others.md • sql-reference/System_limit.md • deployment/environment_configurations.md • administration/management/FE_configuration.md • administration/management/resource_management/Query_management.md • faq/operation_maintenance_faq.md • administration/management/monitoring/alert.md

Stas Grinberg

07/19/2026, 4:10 PM

@Rocky Hey, we're setting up a dedicated

ingester

user in StarRocks (4.1) for Stream Load writes. The user should have load/write access to all current and future tables in the native default catalog. We tried: • `GRANT LOAD_PRIV ON . TO 'ingester'@'%';`→ syntax error • `GRANT LOAD ON ALL TABLES IN ALL DATABASES TO USER 'ingester'@'%';`→ syntax error Questions: 1. What's the correct syntax to grant Stream Load write access to all databases/tables in the native catalog? 2. Does the grant cover future tables/databases automatically, or do we need to re-run it after new tables are created? 3. Should we run this via MySQL client directly against FE:9030, or is there another recommended way?

Rocky

07/19/2026, 4:10 PM

Based on the StarRocks RBAC (Role-Based Access Control) system (introduced in v3.0+), here is the correct way to set up your

ingester

user. The error in your attempts was likely using the keyword

LOAD

(which is not a valid table privilege) and missing the prerequisite

USAGE

privilege on the catalog itself. In StarRocks, loading data requires the INSERT
privilege. 1. Correct Syntax To grant full write access to all current and future tables in the default catalog, run the following sequence:

Copy code

sql
-- 1. Ensure you are in the default catalog context
SET CATALOG default_catalog;

-- 2. Grant USAGE on the catalog so the user can 'see' and access it
GRANT USAGE ON CATALOG default_catalog TO USER 'ingester'@'%';

-- 3. Grant INSERT on all current and future tables/databases
GRANT INSERT ON ALL TABLES IN ALL DATABASES TO USER 'ingester'@'%';

Note: If you also need to delete or update data via loading (e.g., using the Primary Key model), you should add those privileges:
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN ALL DATABASES...
. 2. Does this cover future tables/databases? Yes. In the modern StarRocks RBAC system, granting permissions using the

ALL TABLES IN ALL DATABASES

(or

ALL TABLES IN DATABASE <db>

) syntax is dynamic. • It covers all tables currently in the cluster. • It automatically applies to any future tables created in existing databases. • It automatically applies to any future databases (and their tables) created in the catalog. 3. Recommended way to run You should run these commands via a MySQL client connected to the Frontend (FE) on the query port (default 9030
). • Use a user with high privileges (like

root

admin

). • This is the standard administrative interface for all permission management in StarRocks. Why your previous attempts failed: _ `GRANT LOAD_PRIV ON _.*`:

LOAD_PRIV

was a legacy privilege name from very old versions (v2.x and earlier). In the current RBAC, table-level write access is strictly handled via

INSERT

. * `GRANT LOAD ON ALL TABLES...`: The keyword

LOAD

is not a valid privilege type for the

GRANT

statement in the new system; you must use

INSERT

. References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/User_privilege.md • loading/loading_introduction/loading_concepts.md • loading/StreamLoad.md References • sql-reference/sql-statements/account-management/GRANT.md • loading/loading_introduction/loading_concepts.md • administration/user_privs/authorization/User_privilege.md • loading/StreamLoad.md • loading/RoutineLoad.md References • sql-reference/sql-statements/account-management/GRANT.md • faq/operation_maintenance_faq.md • administration/user_privs/authorization/User_privilege.md • data_source/catalog/hive_catalog.md • administration/user_privs/authorization/privilege_item.md

Stas Grinberg

07/25/2026, 7:08 PM

@Rocky we have Primary Key table — upserts + day partition + ORDER BY timestamp for pagination (shared-data) We’re on StarRocks shared-data. We have a lean analytics table today: -- DUPLICATE KEY — append only, allows duplicates DUPLICATE KEY(

event_timestamp

) PARTITION BY date_trunc('day', event_timestamp) DISTRIBUTED BY RANDOM BUCKETS 16 ORDER BY(

event_timestamp

) For a new span table we need: 1. No duplicates / upserts (Stream Load JSON, conflict resolution on business time) 2. Day partitioningfor prune/retention 3. Sort ordersimilar (filter/scan by timem pagination) Questions: 1. For shared-data + Primary Key (v3.x+), can we use`PRIMARY KEY` +

PARTITION BY date_trunc('day', …)

+ `ORDER BY (…)``together? 2. If we partition on`event_timestamp`, must`event_timestamp`be part of thePRIMARY KEY(along with e.g.

span_id

start_time

)? 3. Recommended PK / partition / sort key for: uniqueness on`(span_id, start_time)`, queries filtered by time, Stream Load upserts with`merge_condition`on`event_timestamp`. 4. Any shared-data limitations vs shared-nothing for PK + decoupled sort key? Thanks.

Rocky

07/25/2026, 7:11 PM

In StarRocks v3.x+ (Shared-Data), you can achieve this design. Here are the specific answers to your questions: 1. Can we use PK + Partition + ORDER BY together? Yes. Since StarRocks v3.0, the Sort Key (

ORDER BY

) has been decoupled from the Primary Key (PK). * Primary Key: Handles uniqueness and upsert logic. * Order By: Determines the physical storage order on disk, which is critical for your "filter/scan by time" and "pagination" performance. * Partition By: Handles data lifecycle (retention) and pruning. 2. Must
event_timestamp
be part of the Primary Key? Yes. In a StarRocks Primary Key table, any column used for Partitioning or Bucketing (Distribution) must be included in the Primary Key definition. * If you partition by

date_trunc('day', event_timestamp)

, then

event_timestamp

must be one of the columns in your

PRIMARY KEY (...)

statement. 3. Recommended Schema Design If your business uniqueness is defined by

(span_id, start_time)

, but you need to partition by time for retention, your schema should look like this:

Copy code

sql
CREATE TABLE span_table (
    span_id VARCHAR(64) NOT NULL,
    start_time DATETIME NOT NULL,
    event_timestamp DATETIME NOT NULL,
    -- ... other columns
)
PRIMARY KEY (span_id, start_time, event_timestamp) -- All must be here
PARTITION BY date_trunc('day', event_timestamp)
DISTRIBUTED BY HASH(span_id) BUCKETS 16
ORDER BY (event_timestamp, span_id) -- Optimized for time-based pagination
PROPERTIES (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE" -- Recommended for Shared-Data v3.3.2+
);

Important Note on Uniqueness: By adding

event_timestamp

to the PK to satisfy the partitioning requirement, you are technically allowing two rows with the same

span_id

and

start_time

if they have different

event_timestamp

values. If

event_timestamp

is always identical to

start_time

, there is no issue. If they differ, and you only want the "latest" version per

span_id

, use the

merge_condition

during Stream Load to ensure only newer

event_timestamp

values overwrite older ones. 4. Shared-Data Limitations vs. Shared-Nothing * Persistent Index: In Shared-Data, the Primary Key index must be persistent. In v3.3.2+, you should set

"persistent_index_type" = "CLOUD_NATIVE"

. This stores the index on object storage (S3/GCS/Azure), allowing your 3 NVMe CN nodes to stay "lean" while still handling large-scale upserts. * Performance: Decoupled sort keys work exactly the same way in Shared-Data. The data is sorted before being written to S3, ensuring that range scans on

event_timestamp

are highly efficient. * Metadata: Shared-Data PK tables are highly optimized for cloud-native environments, but ensure your CN nodes have enough local cache space (the NVMe drives you mentioned) to cache the "hot" parts of the index for sub-second upsert performance. References • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • loading/StreamLoad.md • best_practices/table_clustering.md • faq/operation_maintenance_faq.md References • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md • best_practices/table_clustering.md • faq/Sql_faq.md

Stas Grinberg

07/27/2026, 12:15 PM

@Rocky We have

enable_async_write_back: "false"

in our StarRocks table DDL (Cloud-Native / Shared-Data mode, S3 backend). A colleague flagged that this property is unsupported starting from 3.1.4 and setting it to

"true"

will trigger an error. Two questions: 1. Does`"false"`also trigger an error in newer versions, or is it silently ignored? 2. What StarRocks version introduced this breaking change exactly — is it 3.1.4 or later?

Rocky

07/27/2026, 12:15 PM

The property

enable_async_write_back

was indeed disabled starting from StarRocks version 3.1.4. This change was made because the feature was not fully matured and could lead to data consistency risks in shared-data (cloud-native) environments. 1. Does
"false"
trigger an error or is it ignored? * Setting it to `"true"`: In version 3.1.4 and all subsequent versions (including 3.2.x, 3.3.x, and 4.0), setting this to

true

will trigger a hard error:

Unexpected exception: enable_async_write_back is disabled since version 3.1.4

. * Setting it to `"false"`: This generally does not trigger an error. It is either silently ignored or accepted because it aligns with the only supported behavior (synchronous write-back). Since synchronous writing is now the mandatory default for data safety, the property

"enable_async_write_back" = "false"

is effectively redundant but won't crash your DDL. 2. Which version introduced this change? The change was introduced exactly in StarRocks 3.1.4 (released in November 2023). Recommendations * Remove the property: Since it is deprecated and disabled, it is best practice to remove

"enable_async_write_back" = "false"

from your DDL scripts to avoid confusion and ensure compatibility with future versions where the keyword might be removed from the parser entirely. * Current Alternatives: For managing how data interacts with the local cache and object storage (S3) in shared-data mode, you should now use the following properties: * datacache.enable
: (Default

true

) Controls whether data is cached on local CN/BE disks. * datacache.partition_duration
: Replaces the old

storage_cache_ttl

to control how long data stays "hot" in the local cache. References • release_notes/release-3.1.md • deployment/shared_data/s3.md • deployment/shared_data/azure.md • administration/management/FE_configuration.md References • release_notes/release-3.1.md • administration/management/FE_configuration.md

Stas Grinberg

07/30/2026, 2:59 PM

@Rocky the _statistics_ DB
is exists but empty

Rocky

07/30/2026, 2:59 PM

The

_statistics_

database in StarRocks is a system database used by the Cost-Based Optimizer (CBO) to store metadata about your data (like row counts, min/max values, and cardinality). This information is crucial for generating efficient execution plans. If the database exists but the tables inside it (like

column_statistics

histogram_statistics

) are empty, it usually means the background statistics collection process hasn't successfully run or finished yet. Common Reasons for an Empty
_statistics_
DB 1. New Cluster / No Data: If the cluster was recently created or no data has been loaded into your tables yet, there is nothing for the system to analyze. 2. Background Jobs Haven't Run Yet: By default, StarRocks checks for data updates every 600 seconds (10 minutes). If you just loaded data, the automatic job might still be in the queue. 3. Collection is Disabled: Check your FE configuration. If

enable_statistic_collect

is set to

FALSE

, the background process is turned off. * Check with:

ADMIN SHOW FRONTEND CONFIG LIKE 'enable_statistic_collect';

4. Automatic Collection Windows: There are specific time windows for automatic analysis (defaults are

00:00:00

23:59:59

, covering the whole day). If these were customized, the job might be waiting for the next window. 5. Large Tables: For very large tables, StarRocks might be performing a "Sampled" collection rather than a "Full" one, which can take time to populate. How to Check and Fix 1. Check the Status of Collection Jobs Run this command to see if any background analysis tasks have failed or are currently running:

Copy code

sql
SHOW ANALYZE STATUS;

2. Check Statistics Metadata See which tables the system "knows" about and when they were last updated:

Copy code

sql
SHOW STATS META;

3. Manually Trigger Collection If you need statistics immediately (e.g., for a critical query), you can force a manual collection on a specific table:

Copy code

sql
-- Full collection for a specific table
ANALYZE TABLE your_db.your_table;

-- Or a sampled collection (faster for large tables)
ANALYZE SAMPLE TABLE your_db.your_table;

4. Verify Essential Configurations Ensure these FE configurations are set correctly: * `enable_statistic_collect`: Must be

TRUE

. *

statistic_auto_analyze_start_time

/ `end_time`: Ensure the current time is within this range. Summary of Key Tables in
_statistics_
* column_statistics
: Stores row counts, NDV (number of distinct values), null counts, and min/max. * histogram_statistics
: Stores data distribution for skewed columns. * multi_column_statistics
: Stores joint statistics for multiple columns (v3.5+). References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/data-manipulation/ANALYZE_TABLE.md • administration/management/FE_configuration.md References • using_starrocks/Cost_based_optimizer.md • sql-reference/information_schema/statistics.md

Stas Grinberg

07/30/2026, 7:03 PM

@Rocky we have to stream of data to same primary key table. one real time which is always correct and one stream from scans that might be stopped and replaced. how it works now with datalake? the data is stored from real time in one table and batch collected in other, once we get triggered the batch data is allowed to use we do merge between real time data and batch for one table. how all this could be implemented in starrocks

Rocky

07/30/2026, 7:03 PM

In StarRocks, you can replace the "separate tables + manual merge" process with a single Primary Key (PK) table using the merge_condition
feature. This architecture handles real-time upserts and batch replacements automatically in one place. The Recommended Architecture 1. Single Primary Key Table: Create one table where the

PRIMARY KEY

is your business ID (e.g.,

span_id

). 2. Consistency Logic (
merge_condition
): Use a column (like

update_timestamp

or a

version

number) to decide which record "wins." 3. Real-time Stream: Direct your "always correct" stream (via Flink CDC, Routine Load, etc.) to this table. 4. Batch Stream: Direct your "scans" or batch data to the same table. How to handle the "Batch Replaced" logic When your batch stream is stopped and replaced, you don't need to truncate the table. Instead, you use the versioning logic to ensure the new batch data corrects the old batch data without overwriting more recent real-time updates. Step 1: Define the Table

Copy code

sql
CREATE TABLE span_updates (
    span_id VARCHAR(64) NOT NULL,
    data JSON,
    update_time DATETIME NOT NULL, -- This is your version/merge column
    source_type TINYINT            -- Optional: 1 for Batch, 2 for Real-time
)
PRIMARY KEY (span_id)
DISTRIBUTED BY HASH(span_id);

Step 2: Ingesting Real-time (Always Wins) When loading real-time data, ensure the

update_time

is the current system time or the event time. Use the

merge_condition

property:

Copy code

bash
# Example using Stream Load for Real-time
curl --location-trusted -u root: \
    -H "merge_condition: update_time" \
    -T real_time_data.json \
    http://{FE_HOST}:8030/api/db/span_updates/_stream_load

Step 3: Ingesting Batch (Can be replaced) If you need to re-run a batch because it was "stopped and replaced": 1. Load the new batch with a

update_time

that is greater than the previous failed batch but (crucially) still reflects the actual time of the scan. 2. The Result: * If a row exists from a failed/old batch, the new batch will overwrite it (because its

update_time

is higher). * If a row exists from the real-time stream that arrived after the scan was taken, the batch load will NOT overwrite it (because the real-time

update_time

is higher), preserving the "always correct" data. Implementation with Data Lake If your batch data resides in a Data Lake (Iceberg/Hudi/S3), you can merge it directly into the StarRocks PK table using `INSERT INTO ... SELECT`:

Copy code

sql
-- Enable conditional updates for the insert
INSERT INTO span_updates 
PROPERTIES ("merge_condition" = "update_time")
SELECT span_id, data, scan_timestamp, 1 
FROM iceberge_catalog.db.batch_scans;

Key Advantages for your use case: * No Manual Merge: StarRocks performs the merge at the storage level. You query one table and always get the "winning" record. * Performance: Since it is a Primary Key table, it uses a Delete+Insert strategy internally. This is significantly faster for queries than the "Merge-on-Read" approach used by traditional data lake engines. * Shared-Data Support: This works natively in StarRocks Shared-Data mode, leveraging your S3/Object storage backend for the actual data files while keeping the PK index for fast upserts. What if you need to "Delete" items missing from the batch? If your batch "replacement" means items missing from the new scan should be removed: * Use a two-step load: (1) Mark all rows for that batch as "potentially old" or (2) Load the new batch and then run a

DELETE

where

source_type = 1

and

update_time < [new_batch_timestamp]

. * Because it's a PK table, these deletes are extremely efficient. References • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md

Rocky

07/30/2026, 7:03 PM

• loading/InsertInto.md • loading/Flink-connector-starrocks.md References • faq/Sql_faq.md • loading/Flink-connector-starrocks.md • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md

Stas Grinberg

07/30/2026, 7:07 PM

@Rocky the batch data also flows in small files so it is a big advantage not to handle big cluster for loading. the only problem with that is that it can't be loaded to single primary key table as we didn't get the trigger yet

Rocky

07/30/2026, 7:07 PM

To handle your scenario where batch data is "triggered" and can be "replaced" while merging with real-time data, StarRocks offers a few elegant patterns that don't require managing a separate large compute cluster (like Spark) for the merge. Option 1: The "Versioned Upsert" Pattern (Simplest) This is the most common approach. You use a single Primary Key (PK) table and a

merge_condition

. * The Workflow: 1. Real-time: Streams directly into the PK table with a high

update_time

. 2. Batch (Small Files): The files sit in S3. Your "trigger" simply executes a StarRocks

INSERT INTO ... SELECT FROM FILES()

command. * The "Replacement" Logic: If a batch fails and needs to be replaced, you simply re-run the

INSERT

with the corrected files. If the

update_time

in the new batch is $\geq$ the previous batch, it will overwrite those records. If a real-time update arrived for a record after the scan was taken, its

update_time

will be higher, so the batch load will not overwrite the more recent real-time data. * Trigger Mechanism: Use any orchestrator (Airflow, a simple script, or even a StarRocks Scheduled Job) to run the SQL when the files are ready. Option 2: The "Partition Swap" Pattern (Best for Full Replacements) If your "batch replacement" means "delete everything from the old batch and put in the new batch," using Partitions in a Primary Key table is the cleanest way. 1. Partition by Time/Batch: Partition your PK table by a batch ID or date (e.g.,

PARTITION BY p_date

). 2. The Trigger: When the batch is ready, you load it into a temporary table (using

FILES()

which is very light). 3. The Swap: Execute a

REPLACE PARTITION

command:

Copy code

sql
    ALTER TABLE real_time_table REPLACE PARTITION (p_20231027)
    WITH TABLE temp_batch_table;

* Why it works: This is an atomic metadata operation. The real-time stream can continue writing to other partitions (or even the same one) while this happens. * Note: This "replaces" everything in that partition. If you need to keep real-time data within that same partition, you'd perform a

DELETE

INSERT

instead. Option 3: The "Lakehouse" View (No Ingestion until needed) If you want to avoid loading batch data into StarRocks storage until it's queried: 1. Real-time: Ingest into a StarRocks PK table. 2. Batch: Keep in S3 and define it as an External Table (via Hive/Iceberg catalog or

FILES()

view). 3. The Trigger: Create an Async Materialized View (MV) that joins/unions the two. Set it to

REFRESH MANUAL

. 4. The Result: When your trigger happens, you run

REFRESH MATERIALIZED VIEW my_mv

. StarRocks will compute the merge and store it in its own optimized format for fast querying. Why this fits your "Small Files" & "No Big Cluster" requirement: * StarRocks
FILES()
Function: This is a built-in table function that allows StarRocks BE nodes to pull data directly from S3. It parallelizes the work across your existing StarRocks cluster, so you don't need to spin up Spark/EMR just to "merge" or "load" files. * Memory Efficiency: Unlike traditional engines that might need to "shuffle" the entire dataset to perform a merge, the StarRocks Primary Key engine handles the "merge" at the storage layer via its primary key index (LSM-tree style). Recommendation If your batch data is meant to correct/supplement specific rows, use Option 1. It is the most flexible for mixed-source streams. If your batch data defines a specific time window (e.g., "Yesterday's data"), use Option 2 with partitions. References • table_design/table_types/primary_key_table.md • sql-reference/sql-functions/table-functions/files.md • loading/Load_to_Primary_Key_tables.md

Rocky

07/30/2026, 7:07 PM

• sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • loading/s3.md • loading/minio.md

Stas Grinberg

07/30/2026, 7:21 PM

@Rocky it is more like 2 streams but one of the not approved yet. finally when batch loaded we must to swap or something with master

Rocky

07/30/2026, 7:21 PM

To handle a "two-stream" workflow where one stream (batch) needs approval before merging into the "master" (real-time) table, you can use a Staging-to-Master pattern. Since your batch data comes in small files and you want to avoid a large external compute cluster, StarRocks' built-in

FILES()

table function and Primary Key (PK) tables are the ideal tools. Recommended Workflow 1. The Setup Create two identical tables: * master_table
: The production table receiving the "always correct" real-time stream. * staging_table
: A temporary area for the "unapproved" batch data. Both should be Primary Key tables and include a version column (e.g.,

update_time

) to handle the merge logic.

Copy code

sql
CREATE TABLE master_table (
    id BIGINT NOT NULL,
    data JSON,
    update_time DATETIME NOT NULL
) PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);

-- Create staging with the same schema
CREATE TABLE staging_table LIKE master_table;

2. Load Batch to Staging (No External Cluster) When your "small files" arrive in S3/MinIO, load them into the

staging_table

. This uses your StarRocks BE nodes to process the files directly—no Spark/Flink required for this step.

Copy code

sql
INSERT INTO staging_table
SELECT * FROM FILES(
    "path" = "<s3://my-bucket/unapproved-batch/*.parquet>",
    "format" = "parquet",
    ...
);

3. The "Approval" and "Swap/Merge" Once the batch data is verified/approved, you have two options depending on your goal: Option A: The Atomic Merge (Update specific rows) If the batch should merge with the real-time data (e.g., filling in missing values or correcting history), run an

INSERT INTO ... SELECT

with a

merge_condition

Copy code

sql
-- This moves approved data to master. 
-- Real-time records with a later 'update_time' will NOT be overwritten.
INSERT INTO master_table 
PROPERTIES ("merge_condition" = "update_time")
SELECT * FROM staging_table;

-- Clean up
TRUNCATE TABLE staging_table;

Option B: The Atomic Swap (Replace full partitions) If your batch represents a specific time window (e.g., "Yesterday's data") and should fully replace what is currently in the master for that window, use Temporary Partitions. This is a metadata-only swap and is nearly instantaneous. 1. Load the approved data into a Temporary Partition of the

master_table

. 2. Swap it:

Copy code

sql
ALTER TABLE master_table 
REPLACE PARTITION (p_20231027) 
WITH TEMPORARY PARTITION (tp_approved_batch);

Why this works for you: * Small Files Support: The

FILES()

function is designed to ingest many small files efficiently without needing a heavy loading infrastructure. * Approval Gate: By using a

staging_table

, you can run validation queries (

SELECT count(*)...

) before the data ever touches your production

master_table

. * Merge logic: The

merge_condition

ensures that if a real-time update arrived while the batch was being processed, the real-time update (with the newer timestamp) wins. * No "Big Cluster": StarRocks handles the entire ELT process internally using its own distributed execution engine. Reference * Primary Key Table * FILES() Table Function * Replace Partition References • loading/Load_to_Primary_Key_tables.md • faq/Sql_faq.md • table_design/table_types/unique_key_table.md • table_design/table_types/primary_key_table.md

Stas Grinberg

08/03/2026, 8:54 AM

@Rocky For Stream Load through FE

:8030

, the FE returns

to CN and doesn’t read the body. Our client sends the body immediately (httpx ignores

Expect: 100-continue

), so the FE often resets the connection mid-upload. What’s the supported client pattern — must we wait for 100-continue / not send a body on the FE hop, or should we hit CN

:8040

directly?

Rocky

08/03/2026, 8:55 AM

The behavior you are seeing is expected when the HTTP client does not correctly adhere to the

Expect: 100-continue

protocol. The StarRocks FE is designed to be a lightweight scheduler for Stream Load; it determines which BE/CN should handle the load and immediately issues a

307 Temporary Redirect

. It does not want to receive the data body. If your client sends the body immediately, the FE will close the connection to avoid consuming the data, which triggers a connection reset on the client side. Supported Client Patterns 1. Correct
Expect: 100-continue
Handling (Recommended) This is the officially documented and most efficient pattern. The client flow should be: 1. Request to FE: Send the

PUT

request with headers, including

Expect: 100-continue

. 2. Wait: The client must pause before sending the body. 3. Receive 307: The FE will respond with a

307 Temporary Redirect

and a

Location

header pointing to a BE/CN. 4. Follow Redirect: The client should then initiate a new request to the provided BE/CN URL. 5. Data Upload: On the BE/CN hop, the node will respond with

100 Continue

, at which point the client sends the actual data body. Note on
httpx
: If

httpx

is ignoring the

Expect

header and streaming the body immediately, it is incompatible with the FE-redirect pattern. You may need to use a client like

curl

(which handles this correctly via

--location-trusted

) or manually implement the two-step hop (first get the redirect, then upload to the target). 2. Direct to BE/CN (Alternative) You can bypass the FE redirect by sending your Stream Load requests directly to a BE or CN node (port

by default). * Pros: Avoids the 307 redirect and connection reset issues entirely. * Cons: You lose the FE's built-in load balancing. * Best Practice: If you choose this path, you should place a Load Balancer (like Nginx, HAProxy, or a cloud LB) in front of your BE/CN nodes to distribute the load across the cluster. 3. Stream Load Transaction Interface For more complex programmatic control (especially in Python), consider using the Stream Load Transaction Interface (available since v2.4): 1. `/api/transaction/begin`: FE returns a transaction ID and a target BE. 2. `/api/transaction/load`: Send data directly to that BE. 3. `/api/transaction/commit`: Commit the transaction. This approach separates the "Where do I send data?" logic from the data transfer itself, making it much more robust for clients that struggle with standard HTTP redirects. Summary Recommendation If you cannot make your client wait for the 100-continue/307 response before sending the body, you should hit your BE/CN nodes (or a Load Balancer in front of them) directly at port 8040. This is a common and supported production pattern for high-throughput streaming. References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Stream_Load_transaction_interface.md References • loading/StreamLoad.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • quick_start/helm.md

Stas Grinberg

08/05/2026, 3:56 PM

@Rocky what is the satatements to run for ingester user to permit read from current all catalogs data and future catalogs and tables in catalogs. refresh all external tables in future and now

Rocky

08/05/2026, 3:57 PM

To grant an ingester user permissions that cover all current and future catalogs and tables, you should follow the patterns below. In StarRocks, privileges for databases and tables are scoped within a catalog. While

USAGE ON ALL CATALOGS

covers future catalogs for visibility, you generally must grant table-level permissions (like

SELECT

ALTER

) for each catalog. Option 1: The "Power User" Statement (Recommended for Ingesters) If the ingester is a trusted system user/service and you want a "set and forget" solution for all future catalogs, assign the system-defined

db_admin

role. This role automatically inherits all privileges on any catalog, database, or table created in the future.

Copy code

sql
-- Grant the system role to your user
GRANT db_admin TO USER 'ingester_user'@'%';

────────── Option 2: Granular Role-Based Statements If you prefer to define a custom role with specific "Read + Refresh" permissions, use the following statements. Note that for External Catalogs, you must switch to each catalog once to grant the table-level permissions. 1. Global Catalog Usage (Covers future catalogs) This allows the user to see and "enter" any catalog created in the future.

Copy code

sql
GRANT USAGE ON ALL CATALOGS TO ROLE ingester_role;

2. Read & Refresh for Internal Tables (Covers future internal DBs/Tables) Run this while in the

default_catalog

to cover all current and future internal StarRocks tables.

Copy code

sql
-- ALTER is required to run REFRESH EXTERNAL TABLE
GRANT SELECT, ALTER ON ALL TABLES IN ALL DATABASES TO ROLE ingester_role;

3. Handling External Catalogs (e.g., Hive, Iceberg) Because table privileges are catalog-scoped, for each external catalog you create, you must run a grant once. This will cover all future tables within that specific catalog.

Copy code

sql
-- Switch to the external catalog
SET CATALOG my_hive_catalog;

-- Grant permissions for all current and future tables in this specific catalog
GRANT SELECT, ALTER ON ALL TABLES IN ALL DATABASES TO ROLE ingester_role;

────────── Summary of Requirements * To Read: Requires the

SELECT

privilege on the table. * To Refresh External Tables: Requires the

ALTER

privilege on the table. * To Refresh Materialized Views: Requires the

REFRESH

privilege on the Materialized View. Specific "Refresh" Statement for Materialized Views:

Copy code

sql
GRANT REFRESH ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO ROLE ingester_role;

Pro-Tip for "Future" External Catalogs If you are frequently creating new external catalogs and cannot manually run

GRANT

each time, the only way to automate this within StarRocks' native RBAC is to use the

db_admin

role (Option 1). Otherwise, you would need to include the

GRANT

statements in your catalog creation automation script. References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md • administration/user_privs/authorization/privilege_item.md References • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/user_privs.md

Rocky

08/05/2026, 3:57 PM

• data_source/catalog/hive_catalog.md • sql-reference/sql-statements/account-management/GRANT.md • sql-reference/sys/sys.md

Stas Grinberg

08/05/2026, 4:18 PM

@Rocky looks like we also need SET DEFAULT ROLE db_admin TO 'ingester'@'%';

Rocky

08/05/2026, 4:18 PM

You are absolutely correct. In StarRocks, granting a role with

GRANT

only makes the role available to the user. Without setting it as a default, the user would have to manually execute

SET ROLE db_admin;

at the start of every session to actually use those permissions. For an automated ingester account, you definitely want the role to be active immediately upon login. The Complete Setup To ensure the user has the permissions and they are active by default, you should run:

Copy code

sql
-- 1. Assign the role
GRANT db_admin TO USER 'ingester'@'%';

-- 2. Make it active automatically on login
SET DEFAULT ROLE 'db_admin' TO 'ingester'@'%';

Verification You can verify that the role is correctly set as the default by logging in as the

ingester

user and running:

Copy code

sql
SELECT current_role();

It should return

db_admin

. If you are logged in as an admin and want to check the assignment, you can use:

Copy code

sql
SHOW GRANTS FOR 'ingester'@'%';

-- Or check default roles for all users SELECT * FROM information_schema.applicable_roles; ``` ### Why this is important for Ingesters: Most BI tools and ingestion scripts (like Python's

mysql-connector

httpx

for Stream Load) do not send a

SET ROLE

command after connecting. Without

SET DEFAULT ROLE

, your ingestion jobs would likely fail with "Access Denied" errors despite the role being technically successful

GRANT

Stas Grinberg

08/05/2026, 4:23 PM

@Rocky is there any why to run it efficiently if source contain 40M records before inserting to PK table

Rocky

08/05/2026, 4:24 PM

To ingest 40 million records into a Primary Key (PK) table efficiently, you should focus on minimizing version creation and optimizing the index "Apply" stage. For a bulk load of this size, the default settings for small real-time streams are often inefficient. 1. Optimize Table Design (The "Must-Haves") * Enable Persistent Index: For 40M records, the PK index can consume significant RAM. Enable the persistent index to offload the index from memory to disk.

Copy code

sql
    PROPERTIES ("enable_persistent_index" = "true");

* Use SSDs: PK tables perform a "Delete + Insert" logic. This involves random I/O for index lookups and Delete Vector updates. NVMe SSDs are highly recommended for the BE storage. 2. Ingestion Strategy: Stream Load If your data is in a file (CSV/Parquet/JSON), Stream Load is the most efficient path. * Batch Size: Do not send 40M records in thousands of tiny requests. For 40M records (assuming ~10-20GB of raw data), aim for 1GB to 5GB per batch. This reduces the number of rowsets and the pressure on the compaction engine. * Pre-Sorting: If possible, sort your source data by the Primary Key before loading. This makes the index lookup and "apply" phase much faster because the BE can process the index updates sequentially rather than jumping around the disk/memory. * Parallelism: If you have multiple BEs, split your 40M records into 4-8 files and run them in parallel. StarRocks handles concurrent loads to different tablets well. 3. Tune BE/FE Configurations For a heavy bulk load, adjust these parameters (especially if you see

too many versions

errors): Parameter | Location | Recommendation

streaming_load_max_mb

be.conf

| Increase to

(20GB) if your files are large.

tablet_max_versions

be.conf

| Increase to

(default 1000) to allow more headroom during the bulk load.

transaction_apply_worker_count

be.conf

| Set to your CPU core count to speed up the PK update logic.

stream_load_default_timeout_second

fe.conf

| Increase to

(1 hour) so large batches don't time out. 4. Alternative:
INSERT INTO ... SELECT
If your 40M records are already in a Data Lake (S3/HDFS/Hive) or another StarRocks table, use

INSERT INTO ... SELECT

. * Why? It is often faster than Stream Load for bulk moves because it bypasses the HTTP overhead and uses the internal MPP execution engine. * Memory Tip: For PK tables,

INSERT INTO

can be memory-intensive. Ensure you have enough memory or enable spill-to-disk for the load session:

Copy code

sql
    SET enable_spill = true;
    INSERT INTO target_pk_table SELECT * FROM source_external_table;

Summary of the "Fastest" Path: 1. Sort the 40M records by Primary Key in the source. 2. Split the data into ~5 files (roughly 8M rows each). 3. Stream Load them in parallel to the FE/CN with

partial_update: false

. 4. Monitor the Compaction Score (via

SHOW PROC '/compactions'

) to ensure the cluster is keeping up. References • loading/StreamLoad.md • best_practices/primarykey_table.md • administration/management/BE_configuration.md • table_design/table_types/primary_key_table.md References • knowledge/usage/load/shared_data_batch_ingestion.md • loading/Flink-connector-starrocks.md • faq/loading/Loading_faq.md • faq/loading/Stream_load_faq.md • loading/StreamLoad.md

Rocky

08/05/2026, 4:24 PM

• administration/management/BE_configuration.md

Stas Grinberg

08/05/2026, 5:32 PM

@Rocky if part of the data already in CN cache. is it better performance for inserts

Rocky

08/05/2026, 5:33 PM

Having data in the Data Cache (also known as Block Cache) significantly improves the performance of INSERT INTO ... SELECT
operations, but it primarily speeds up the reading (SELECT) phase rather than the writing phase. Here is a breakdown of how it affects your performance for a 40M record insert into a Primary Key (PK) table: 1. Faster Data Extraction (The "SELECT" part) If you are loading data from an External Catalog (S3, Hive, Iceberg, etc.) or a Cloud-native Table (Shared-data mode): * Without Cache: StarRocks must fetch the 40M records over the network from remote storage. This is often the primary bottleneck due to network latency and object storage throughput limits. * With Cache: If the source data is already in the CN/BE's local disk cache, StarRocks reads it at local NVMe/SSD speeds. This can make the data extraction phase 5x to 10x faster. 2. Impact on the PK "Apply" Phase Primary Key tables use a Delete+Insert strategy. When you insert data, StarRocks must check if the keys already exist to mark old versions as deleted. * The PK Index: This check relies on the Primary Key Index, not the Data Cache. * If you use the Persistent Index, the index is stored on disk. Having this index "warm" in the OS page cache or StarRocks' index cache is what makes the "upsert" logic fast. * Data Cache for Target Table: For shared-data clusters, if the target table's metadata and delete vectors are in the Data Cache, the "Commit/Apply" phase will be faster because the node doesn't have to fetch the existing "Delete Vector" files from remote storage. 3. When Cache Doesn't Help * Stream Load: If you are using

curl

or a connector to push data (Stream Load), the CN cache is irrelevant because the data is coming from the client, not being read from storage. * Local Storage (Shared-Nothing): For internal tables on local BE disks, StarRocks uses the standard Linux Page Cache and its internal Page Cache. The "Data Cache" feature is specifically designed to bridge the gap for remote/external data. Recommendation for 40M Records: If your source is an external table, you can "warm up" the cache before the insert to ensure maximum speed:

Copy code

sql
-- Warm up the source data into the cache
CACHE SELECT * FROM source_catalog.db.source_table;

-- Then run the insert
INSERT INTO target_pk_table SELECT * FROM source_catalog.db.source_table;

INSERT INTO ... SELECT

from an external source, having the data in the CN cache will make the overall command finish much faster. References • data_source/data_cache.md • data_source/block_cache_warmup.md • table_design/table_types/primary_key_table.md • using_starrocks/caching/query_cache.md References • data_source/data_cache.md • data_source/block_cache_warmup.md • table_design/table_types/primary_key_table.md • using_starrocks/caching/query_cache.md • using_starrocks/caching/block_cache.md

Rocky

08/05/2026, 5:33 PM

• data_source/data_cache_troubleshooting.md • best_practices/primarykey_table.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md

2 Views

Open in Slack

Previous Next