http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV>, what is the recommended storage cl...
# questions-and-troubleshooting
s
@Rocky, what is the recommended storage class on new Starrocks installation on EKS for BE and FE when the data layer on iceberg tables
r
For a new StarRocks installation on Amazon EKS where the primary data layer resides on Iceberg tables (S3), the storage strategy shifts from "permanent data storage" to "metadata storage" and "high-speed caching." Here are the recommended storage classes and configurations for FE and BE nodes: 1. FE (Frontend) Nodes: Metadata Storage FE nodes store the StarRocks internal catalog, table metadata, and user/permission data. While Iceberg metadata is in S3, StarRocks still maintains its own local state for management. * Recommended Storage Class: 
gp3
(EBS). * Performance Profile: FE metadata operations are not extremely I/O intensive, but they are critical for cluster stability. 
gp3
is ideal because it provides a baseline of 3,000 IOPS and 125 MiB/s throughput, which is more than sufficient for FE nodes. * Size: Usually 100 GB to 200 GB is enough for production metadata. 2. BE (Backend) / CN (Compute) Nodes: Data Cache When querying Iceberg, StarRocks uses a Data Cache (Block Cache) to store "hot" data blocks from S3 on local disks. This is the most critical storage for query performance. * Recommended Storage Class (High Performance): Local NVMe (Instance Store). * Why: For Iceberg/Data Lake analytics, the bottleneck is often the latency of fetching data from S3. Using local NVMe disks on EC2 instances (e.g., 
i3
, 
r5d
, 
m5d
series) provides the lowest latency and highest throughput for the Data Cache. * Implementation: Use the 
local-path-provisioner
or a similar solution to mount the instance's NVMe drive to the BE/CN pod's cache directory. * Recommended Storage Class (Standard/Balanced): 
gp3
(EBS). * Why: If your instance type doesn't have local NVMe or you prefer the ease of EBS management, 
gp3
is the standard choice. * Tuning: For large Iceberg datasets, consider increasing the 
gp3
throughput beyond the default 125 MiB/s to 500+ MiB/s to speed up cache population and reads. * Size: The cache should be sized to fit your "hot" working set. A common starting point is 500 GB to 1 TB per node. 3. Architecture Choice: BE vs. CN For an Iceberg-centric setup, you have two options for the "Backend" layer: * Compute Nodes (CN): Preferred for EKS/Iceberg. CNs are stateless compute nodes designed specifically for shared-data scenarios (like Iceberg on S3). They use local storage only for caching. * Backend Nodes (BE): Can be used similarly to CNs. If you plan to also store some "native" StarRocks tables (Shared-Nothing tables) alongside Iceberg, you must use BEs. Summary Table for EKS Installation Component | Node Type | Recommended Storage Class | Storage Usage FE | Follower/Leader | 
gp3
| Internal Metadata & Logs BE / CN | Compute Node | Local NVMe (Best) or 
gp3
(Standard) | Data Cache (Block Cache) Data Layer | S3 (Iceberg) | N/A | Primary Data Storage Key Tips for Iceberg Setup: 1. Enable Data Cache: Ensure 
datacache.enable = true
(default in v3.2.3+) is set in your configuration to utilize the disks for Iceberg performance. 2. Metadata Caching: Since v3.3.3, StarRocks can also cache Iceberg metadata on FE disks. Ensure your FE 
gp3
volume has enough space (20-50GB) reserved for the path defined by 
iceberg_metadata_cache_disk_path
. 3. IOPS/Throughput: If using 
gp3
for BE/CN caching, tune the throughput to at least 250-500 MiB/s to ensure the cache doesn't become a bottleneck during large scans. References • data_source/data_cache.md • introduction/Architecture.md • deployment/shared_data/feature-support-shared-data.md • using_starrocks/caching/block_cache.md
s
@Rocky, We’re on EKS, Iceberg tables on S3, catalog via iceberg REST (Lakekeeper). We will not use StarRocks native / shared-nothing local tables — only external Iceberg. In that setup, should we deploy a shared-data cluster with CNs only, or is BE + external catalog also first-class and recommended? What breaks or what do we give up if we pick the wrong one?
r
For a new StarRocks installation on Amazon EKS where you are exclusively querying Iceberg tables on S3 via a REST catalog (Lakekeeper), the recommended architecture is a Shared-Data cluster with CNs (Compute Nodes) only. Here is the breakdown of why this is the preferred path and what the trade-offs are. 1. Recommended Choice: Shared-Data Architecture (FE + CN) In this mode (
run_mode = shared_data
), you deploy FEs for management and CNs for compute. * Why it's better for EKS: CNs are designed to be stateless. Since your data is already in S3 (Iceberg), you don't need the local storage persistence that BEs provide. This allows you to use Kubernetes Horizontal Pod Autoscaler (HPA) to scale your compute power up and down based on query load within seconds. * The "Data Cache": Even though CNs are stateless, they use local disk (ideally 
gp3
or instance store NVMe) as a Data Cache. This keeps hot Iceberg data local to the node, giving you performance similar to local tables without the management overhead. * Uniform Management: In 
shared_data
mode, all system metadata and internal tables are stored in a small dedicated S3 bucket. This makes the entire cluster "cloud-native." 2. Alternative: Shared-Nothing (FE + BE) In this mode (
run_mode = shared_nothing
), you deploy BEs and use them to query the External Catalog. * Why people pick it: It is the "classic" deployment. It doesn't require you to configure a dedicated S3 bucket for internal StarRocks state (it stores system metadata on the BE's local disks). * The Problem on EKS: BEs are stateful. Even if they aren't storing "business data," they store system metadata and statistics. If a BE pod is rescheduled, you must ensure its Persistent Volume (PV) follows it. Scaling is slower because the cluster treats BEs as permanent members of the ring. ────────── What do you give up or "break" if you pick the wrong one? Feature | Shared-Data (CN only) | Shared-Nothing (BE only) Elasticity | High. CNs can be added/removed instantly via HPA. | Low. BEs require a formal "Decommission" process. State Management | Stateless. CNs use disks only for transient cache. | Stateful. BEs must maintain persistent storage. Internal Metadata | Stored in S3 (Safe/Durable). | Stored on BE disks (Risky if PVs are lost). Requirements | Must provide a dedicated S3 bucket for internal state. | No extra S3 bucket needed for internal state. Native Tables | Supported (stored in S3). | Supported (stored on BE disks). What happens if you pick the "wrong" one? * If you pick Shared-Nothing (BE): You don't "break" Iceberg queries—they will work perfectly. However, you "give up" the operational ease of Kubernetes. You will struggle with auto-scaling, and your BEs will behave like "heavy" pets rather than "disposable" cattle. * If you pick Shared-Data (CN): You don't "give up" performance. However, you must configure a 
cloud_native_storage_type
and 
aws_s3_path
in your 
fe.conf
. If you forget this, the cluster will not start. Recommendation Summary 1. Use Shared-Data mode (
run_mode = shared_data
). 2. Deploy CNs instead of BEs. 3. Configure a small S3 bucket for the cluster's internal state (this is separate from your Iceberg data). 4. Use 
gp3
or Instance Store for the CN's 
storage_root_path
to act as the Data Cache for your Iceberg reads. This is critical for performance. Note on Lakekeeper: The Iceberg REST catalog is fully supported in both modes. You will define it in StarRocks using:
Copy code
sql
CREATE EXTERNAL CATALOG iceberg_catalog
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "rest",
    "iceberg.catalog.uri" = "<http://lakekeeper-service:8080>",
    "aws.s3.region" = "us-east-1"
);
References • deployment/shared_data/feature-support-shared-data.md
s
@Rocky, StarRocks shared-data mode (FE + CN) on EKS Graviton3, Iceberg on S3 via Lakekeeper REST catalog, no native tables. Fact tables up to 500M rows per customer, partitioned by date, app always queries a 90-day rolling window. Target: sub-3s P95. Is this realistic, and what do we need to configure to get there?
r
Achieving a sub-3s P95 for 500M rows on a 90-day rolling window is highly realistic with StarRocks 3.3+ on Graviton3. In many cases, you can even reach sub-second performance if the "hot" data is fully cached. Since you are using Shared-Data mode (FE + CN) with an external Iceberg catalog, your performance will depend on three pillars: Metadata Caching, Data Caching, and Compute Efficiency. 1. Architecture & Hardware Recommendation * Node Type: Use c7g or r7g instances (Graviton3). StarRocks 3.3+ includes deep optimizations for the ARM NEON instruction set, providing up to a 50% price-performance boost over x86. * CN Storage (Data Cache): Use instances with Local NVMe (Instance Store) (e.g., 
c7gd
or 
r7gd
). This is critical. S3 latency will prevent sub-3s P95 without a local cache. If you must use EBS, use gp3 with at least 500 MiB/s throughput. * FE Storage: Use gp3 for FE. You will be caching Iceberg metadata here, so ensure the volume has 100GB+ of space. 2. Critical Configuration Tuning A. FE: Metadata Cache (The "Planning" Phase) Iceberg queries often slow down during the "planning" phase while reading manifest files from S3. You must move this to the FE's disk/memory.
Copy code
sql
-- Set these in fe.conf
enable_iceberg_metadata_disk_cache = true
iceberg_metadata_cache_disk_capacity = 21474836480  -- 20GB for metadata
background_refresh_metadata_interval_millis = 300000 -- Refresh every 5 min
* Why: This ensures that when your app queries the 90-day window, the FE already knows exactly which S3 files to pick without talking to S3/Lakekeeper during the query. B. CN: Data Cache (The "Execution" Phase) The Block Cache (Data Cache) stores the actual Parquet data blocks on the CN's local NVMe.
Copy code
bash
# Set in cn.conf
datacache_enable = true
# Point to your NVMe mount or gp3 volume
storage_root_path = /mnt/nvme/sdata
* Sizing: 500M rows (fact table) for 90 days is roughly 25-50GB of compressed Parquet data. A 500GB NVMe drive on a CN will easily hold your entire "hot" 90-day window, ensuring nearly all reads are local. C. Query & Session Optimization Enable JIT (Just-In-Time) and Distributed Planning to handle the 500M row scans.
Copy code
sql
-- Global variables (set once)
SET GLOBAL enable_iceberg_metadata_cache = true;
SET GLOBAL plan_mode = 'distributed'; -- Speeds up metadata parsing across CNs
SET GLOBAL jit_level = 1;              -- Enabled by default in 3.3+, but verify
3. Data Modeling for Iceberg To ensure the 90-day window is pruned effectively: * Partitioning: Ensure the table is partitioned by 
date
. StarRocks will perform partition pruning so it only touches the S3 prefixes for those 90 days. * File Size: Aim for Parquet files between 128MB and 256MB. Too many small files will degrade metadata performance; files that are too large will hurt parallel scan efficiency. * Sorting: If your app frequently filters by a specific ID (e.g., 
customer_id
), use the 
SORTED BY
property in Iceberg. StarRocks can use the min/max stats in Parquet footers to skip data within the 90-day partitions. 4. Catalog Integration (Lakekeeper) Your catalog creation should look like this:
Copy code
sql
CREATE EXTERNAL CATALOG lakekeeper
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "rest",
    "iceberg.catalog.uri" = "<http://lakekeeper-rest-url:8080>",
    "aws.s3.region" = "us-east-1",
    "iceberg.catalog.oauth2.credential" = "your_cred", -- if using Polaris/Security
    "iceberg.catalog.vended-credentials-enabled" = "true"
);
What to monitor? If you don't hit <3s, check the Query Profile: 1. WaitTimeForMetadata: If high, your FE metadata cache isn't hitting. 2. DataCacheReadBytes vs. RemoteReadBytes: If 
RemoteReadBytes
is high, your CN local disks are full or the cache is still warming up.
3. ScanTime: If high despite cache hits, consider adding more CNs (Scaling out on EKS is the strength of this architecture). References • release_notes/release-3.3.md • faq/Deploy_faq.md • release_notes/release-2.4.md • using_starrocks/jit.md • using_starrocks/accelerate-cardinality-preserving-joins.md • unloading/Spark_connector.md • using_starrocks/skew_join_v2.md • deployment/environment_configurations.md
s
@Rocky We're on 
kube-starrocks:1.11.4
with StarRocks 
3.5.14
. StarRocks 4.0.x is now out. Should we upgrade for a new deployment on EKS with Iceberg-only workloads, or is 3.5.x still the safer choice? What's the most stable version available in the kube-starrocks Helm chart right now?
r
For a new deployment specifically focused on Iceberg-only workloads on EKS, you should strongly consider starting with StarRocks 4.0.x (ideally 4.0.9+). While the 
kube-starrocks:1.11.4
Helm chart defaults to StarRocks 3.5.x for conservative stability, the 4.0 release is a major architectural milestone that transforms how StarRocks handles external data lakes. 1. Why StarRocks 4.0 is the "Right" choice for Iceberg In 4.0, Iceberg is no longer just an "external format"—it is treated as a native foundation. For your REST catalog + S3 setup, 4.0 provides: * Catalog-Centric Governance (JWT Identity Passthrough): This is a game-changer for REST catalogs (like Lakekeeper or Polaris). 4.0 supports JWT-based session catalogs, meaning user identities flow end-to-end. You no longer need to hardcode storage credentials in StarRocks; the catalog handles authorization and credential vending. * 60% Faster Query Execution: Version 4.0 includes deep optimizations for JOINs, aggregations, and metadata parsing specifically for Iceberg tables, delivering a roughly 60% year-over-year performance boost on TPC-DS benchmarks. * Native Iceberg Writes: If you ever decide to write back to Iceberg from StarRocks (e.g., 
INSERT INTO iceberg_table
), 4.0 introduces a new global shuffle mechanism that avoids "small file explosion" and supports Hidden Partitioning. * Cost Efficiency: Improved metadata caching and file bundling reduce cloud S3 API calls by up to 90%, which significantly lowers AWS costs for high-concurrency workloads. 2. Is 3.5.x "Safer"? * 3.5.14 is the current "Long Term Support" (LTS) candidate. It is rock-solid for production clusters that are migrating from older versions. * However, for a new installation on Graviton3/EKS, 4.0.x is already quite mature (having reached 4.0.9 as of mid-2026). Most of the initial "major version" bugs have been addressed in patch releases. 3. Most Stable Version in Helm The 
kube-starrocks
chart version and the StarRocks engine version are decoupled. * The Chart: 
kube-starrocks:1.11.4
is the most stable and feature-rich chart for EKS right now. It includes important fixes for the StarRocks Operator and improved support for 
ServiceMonitors
. * The Engine: By default, this chart uses 
3.5-latest
. To use 4.0, you simply override the image in your `values.yaml`:
Copy code
yaml
# Example override in values.yaml
frontend:
  image:
    repository: starrocks/fe-ubuntu
    tag: 4.0.9
be: # or cn
  image:
    repository: starrocks/cn-ubuntu  # Use CN for your Iceberg-only setup
    tag: 4.0.9
Recommendation Summary * Pick 4.0.x if: You want the best performance for Iceberg, need REST catalog security (JWT), or plan to use modern features like 
ASOF JOIN
. Since you are on a new project, starting here avoids a major migration in 6 months. * Pick 3.5.x if: Your organization has strict "wait 6 months after a major release" policies or if you prioritize a battle-tested track record over 60% better performance. Verdict: Go with 4.0.9 (or latest 4.0 patch). It was built specifically to solve the "Iceberg query speed" and "credential management" challenges you've described. References • release_notes/release-3.3.md • release_notes/release-2.4.md • faq/Deploy_faq.md • using_starrocks/jit.md • using_starrocks/accelerate-cardinality-preserving-joins.md • unloading/Spark_connector.md • using_starrocks/skew_join_v2.md • deployment/environment_configurations.md -configurations)
s
@Rocky We're using 
kube-starrocks
Helm chart with CN (shared-data mode). We disabled 
initPassword
and set the root password ourselves via a PostSync Job. CN entrypoint fails with 
Access denied
because it tries to register with FE using root with no password. Should we re-enable 
initPassword.passwordSecret
pointing to our existing Kubernetes secret, and let the operator handle password propagation to CN? Or is there a better pattern?
r
The 
Access denied
error occurs because the StarRocks container entrypoint scripts (and the Operator) use the 
MYSQL_PWD
environment variable to authenticate with the FE when performing node registration (e.g., 
ADD COMPUTE NODE
). Since you set the password via a separate PostSync job, the CN pods have no knowledge of it and attempt to register with an empty password. Recommended Pattern: Use 
initPassword.passwordSecret
 The "correct" and most resilient pattern when using the 
kube-starrocks
chart is to enable 
initPassword.passwordSecret
. Even if you have already set the password manually, you should point the Helm chart to your existing secret. Here is why: 1. Environment Variable Injection: When 
passwordSecret
is provided, the StarRocks Operator automatically injects the 
MYSQL_PWD
environment variable into the FE, BE, and CN pods, sourcing it from your Kubernetes secret. 2. CN Registration: The CN entrypoint script will see 
MYSQL_PWD
and use it to successfully run the 
ALTER SYSTEM ADD COMPUTE NODE
SQL command. 3. Operator Sync: The Operator itself needs this password to manage the cluster (e.g., during scaling or health checks). How to configure it: Update your 
values.yaml
to point to your existing secret:
Copy code
yaml
starrocks:
  initPassword:
    enabled: true
    passwordSecret: "your-existing-secret-name" # The secret must have a key named 'password'
Alternative: Manual Environment Variable Injection If you prefer to keep your custom PostSync logic and avoid the 
initPassword
helper job (which might try to re-run 
SET PASSWORD
), you must manually inject the secret into the CN's environment variables so the entrypoint script can find it. In your 
values.yaml
, add this under the CN spec:
Copy code
yaml
starrocksCnSpec:
  cnEnvVars:
    - name: MYSQL_PWD
      valueFrom:
        secretKeyRef:
          name: your-existing-secret-name
          key: password
Note: You should ideally do this for 
starRocksFeSpec
and 
starRocksBeSpec
as well to ensure all maintenance scripts work. What breaks if you pick the wrong one? * Without 
initPassword
or `MYSQL_PWD`: Your CNs will fail to join the cluster every time a pod is rescheduled or scaled, as they will lack the credentials to register themselves. * PostSync Job vs. Operator: Your PostSync job is "one-and-done." If the root password is ever rotated, the pods will stop being able to register unless the environment variables are updated. The 
initPassword.passwordSecret
pattern is the only one that ensures the Operator stays in sync with the actual state of the database. Verdict: Re-enable 
initPassword.passwordSecret
. It is the native way to handle secret propagation in 
kube-starrocks
and solves the CN registration issue automatically without adding manual boilerplate 
cnEnvVars
boilerplate. References • quick_start/helm.md • loading/Kafka-connector-starrocks.md • administration/management/FE_configuration.md • deployment/post_deployment_setup.md • loading/Flink-connector-starrocks.md • loading/Spark-connector-starrocks.md
s
@Rocky StarRocks 4.0.9, shared-data mode (FE + CN). We registered an Iceberg REST catalog pointing to Lakekeeper with OAuth2 (Azure AD client credentials). 
SHOW CATALOGS
shows the catalog correctly, but 
SHOW DATABASES FROM <catalog>
returns 
ERROR 1064: Failed to load rest catalog
. Our catalog properties: "type" = "iceberg", "iceberg.catalog.type" = "rest", "iceberg.catalog.uri" = "http://lakekeeper-svc:8181/catalog", "iceberg.catalog.security" = "oauth2", "iceberg.catalog.oauth2.credential" = "client_id:client_secret", "iceberg.catalog.oauth2.scope" = "api://xxx/.default", "iceberg.catalog.warehouse" = "warehouse_name", "aws.s3.use_instance_profile" = "true", "aws.s3.region" = "eu-west-1"
r
The error 
ERROR 1064: Failed to load rest catalog
is a generic failure from the StarRocks FE when it cannot initialize the Iceberg REST client. In StarRocks 4.0.x, when using an external OAuth2 provider like Azure AD, there are three common reasons for this failure. 1. Missing 
oauth2.server-uri
(Most Likely) By default, the Iceberg REST specification assumes the REST catalog itself is the identity provider and attempts to fetch tokens from 
{uri}/v1/oauth/tokens
. Since Lakekeeper delegates authentication to Azure AD, StarRocks doesn't know where to get the token. Fix: You must explicitly provide the Azure AD token endpoint.
Copy code
sql
"iceberg.catalog.oauth2.server-uri" = "<https://login.microsoftonline.com/<YOUR_TENANT_ID>/oauth2/v2.0/token>"
2. Property Naming: Dots vs. Hyphens StarRocks 4.0 has standardized on "dot" notation for properties, but the underlying Iceberg library sometimes expects hyphens or specific StarRocks-prefixed versions. For Lakekeeper + StarRocks 4.0, ensure you are using the dot-notated versions correctly. Revised Catalog DDL:
Copy code
sql
CREATE EXTERNAL CATALOG lakekeeper
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "rest",
    "iceberg.catalog.uri" = "<http://lakekeeper-svc:8181/catalog>",
    "iceberg.catalog.security" = "oauth2",
    "iceberg.catalog.oauth2.server-uri" = "<https://login.microsoftonline.com/<tenant>/oauth2/v2.0/token>",
    "iceberg.catalog.oauth2.credential" = "<client_id>:<client_secret>",
    "iceberg.catalog.oauth2.scope" = "<api://xxx/.default>",
    "iceberg.catalog.warehouse" = "warehouse_name",
    "iceberg.catalog.vended-credentials-enabled" = "true", -- Critical for EKS/Graviton
    "aws.s3.region" = "eu-west-1"
);
3. Azure AD Scope Format Azure AD is sensitive to the 
scope
parameter in the 
client_credentials
flow. * Ensure the scope ends with 
/.default
(e.g., `api://your-lakekeeper-app-id/.default`). * In some cases, StarRocks 4.0 might encounter a known issue where it double-encodes the scope. If you still get an error after adding the 
server-uri
, try removing the 
scope
property entirely and see if Lakekeeper/Azure can negotiate a default scope. 4. URI Path Sensitivity Lakekeeper's endpoint can be sensitive to the trailing path. Depending on your Lakekeeper version and ingress: * Try 
<http://lakekeeper-svc:8181>
(no 
/catalog
) * Try 
<http://lakekeeper-svc:8181/api/catalog>
* The StarRocks FE log (
fe.log
) will show a 404 if the path is wrong or a 401/403 if the OAuth2 handshake failed. How to Debug Further If the 
SHOW DATABASES
command still fails, the specific reason is hidden in the FE logs. On your FE pod, run:
Copy code
bash
grep "rest catalog" log/fe.log -A 20
Look for a Java stack trace. If you see: * 
Unauthorized (401)
: The Azure AD token was rejected by Lakekeeper or the 
credential
(ID:Secret) is wrong. * 
Service Unavailable (503)
: FE cannot reach the 
server-uri
(likely a Kubernetes network/egress policy issue). * 
MalformedURLException
: The 
uri
or 
server-uri
format is invalid. Recommendation: Add the 
iceberg.catalog.oauth2.server-uri
first. That is the missing link that prevents StarRocks from performing the initial authentication handshake required to "load" the catalog. References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/datalake_faq.md • quick_start/iceberg.md
s
@Rocky, how many concurrent queries we process for 3 cn nodes and 3 fe nodes
r
For a StarRocks cluster with 3 FE nodes and 3 CN nodes, "concurrency" is split into two layers: Connections (handled by FEs) and Execution Parallelism (handled by CNs). In StarRocks 4.0.x (shared-data mode), here is how the numbers break down: 1. Connection Concurrency (FE Layer) The FEs manage your MySQL protocol connections. * Total Capacity: By default, each FE can handle several thousand connections (configurable via 
qe_max_connection
in 
fe.conf
, default is often 1024 or higher depending on version). * With 3 FEs: If you use a Load Balancer (like the one created by the Helm chart), you can have ~3,000+ concurrent connections open across the cluster. * Per-User Limit: Check 
SHOW PROPERTY FOR 'root';
. The 
max_user_connections
property (default usually 100) often limits a single user more than the FE itself. 2. Execution Concurrency (CN Layer) The CN nodes handle the actual CPU/Memory work. StarRocks uses a Query Queue (V2) to manage this. The "Execution Slots" (how many query tasks can run at once) is calculated by a formula introduced in recent versions: $$\text{Total Slots} = \text{query\_queue\_v2\_concurrency\_level} \times \text{Number of CNs} \times \text{Cores per CN}$$ * Default `query_queue_v2_concurrency_level`: 4 * Example: If your 3 CN nodes have 16 CPU cores each: $$4 \times 3 \times 16 = \mathbf{192 \text{ available execution slots}}$$ What does a "Slot" mean? * Simple Queries (Point lookups/Iceberg metadata): Typically consume 1 slot. In this example, you could run 192 simple queries concurrently. * Complex Queries (Large Joins/Aggregations): StarRocks estimates the "cost" of a query based on row cardinality. A heavy join might consume 10-20 slots, meaning you might only run 10-15 of these heavy queries before they start queuing. 3. High QPS Scenarios (Short Queries) If your Iceberg workload consists of many very small, fast queries: * Users have reported StarRocks clusters achieving 20,000 to 30,000 QPS for extremely light queries. * With 3 FEs, the limit is usually the Leader FE's ability to plan the queries (planning is metadata-intensive). For Iceberg, this is often faster in 4.0 because of the improved metadata cache. Summary Table for 3 FE / 3 CN (16-core nodes) Metric | Expected Limit (Default) | Scaling Factor Max Open Connections | ~3,000 | Add more FEs Simple Concurrent Queries | ~192 | Add more CNs or Increase Cores Heavy Analytical Queries | ~10–20 | Add more CNs or Increase Memory Max QPS (Short Queries) | 10,000+ | FE Planning Speed / Network Recommendation To see your cluster's specific limits based on your hardware, run:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE 'query_queue_v2%';
-- And check per-node stats
SHOW BACKENDS; -- (CNs are listed here in shared-data mode)
If you find queries are queuing despite low CPU usage, you can increase 
query_queue_v2_concurrency_level
to 8 or 16, but monitor BE memory to ensure you don't hit OOM (Out of Memory) errors. References • administration/management/resource_management/query_queues.md • administration/management/resource_management/Query_management.md
s
Hey @Rocky, we deployed StarRocks 4.0.9 on EKS (shared-data mode, FE + CN) and everything works great. For developer access to the FE MySQL port (9030) we're currently using kubectl port-forward which is unstable. Our platform uses Istio VirtualService for HTTP services (like Trino on port 8080) but MySQL protocol is raw TCP. What's the recommended approach to expose StarRocks FE port 9030 internally on EKS for developer/BI tool access? We have Istio and Traefik available.
r
Since you are running on EKS with Istio and Traefik already present, you have three solid ways to expose the MySQL port (9030). Because the MySQL protocol is Raw TCP (non-HTTP), standard Ingress resources won't work, but both Istio and Traefik support TCP routing. Recommendation 1: AWS Internal NLB (Simplest & Most Robust) For MySQL traffic, a Network Load Balancer (NLB) is often better than a proxy layer because it handles long-lived TCP connections more gracefully. This bypasses Istio/Traefik complexity but stays internal to your VPC. Update your FE service (or create a new one) with these annotations:
Copy code
yaml
apiVersion: v1
kind: Service
metadata:
  name: starrocks-fe-internal
  annotations:
    # Use an internal NLB
    <http://service.beta.kubernetes.io/aws-load-balancer-internal|service.beta.kubernetes.io/aws-load-balancer-internal>: "true"
    <http://service.beta.kubernetes.io/aws-load-balancer-type|service.beta.kubernetes.io/aws-load-balancer-type>: "nlb"
    # Ensure cross-zone load balancing for your 3 FEs
    <http://service.beta.kubernetes.io/aws-load-balancer-cross-zone-load-balancing-enabled|service.beta.kubernetes.io/aws-load-balancer-cross-zone-load-balancing-enabled>: "true"
spec:
  type: LoadBalancer
  selector:
    <http://cluster.starrocks.com/cluster|cluster.starrocks.com/cluster>: <your-cluster-name>
    <http://cluster.starrocks.com/component|cluster.starrocks.com/component>: fe
  ports:
  - name: mysql
    port: 9030
    targetPort: 9030
────────── Recommendation 2: Istio Gateway (TCP Mode) Since you already use Istio for Trino, you can reuse your Istio Ingress Gateway. You must define a Gateway that listens on 9030 and a VirtualService that uses 
tcp
instead of 
http
. 1. Update Istio Gateway to listen on 9030: Ensure your Istio Ingress Gateway pod's Service has port 9030 open. Then apply this:
Copy code
yaml
apiVersion: <http://networking.istio.io/v1alpha3|networking.istio.io/v1alpha3>
kind: Gateway
metadata:
  name: starrocks-gateway
spec:
  selector:
    istio: ingressgateway # match your gateway label
  servers:
  - port:
      number: 9030
      name: mysql
      protocol: TCP
    hosts:
    - "*" # Or your internal DNS name
2. Create a TCP VirtualService:
Copy code
yaml
apiVersion: <http://networking.istio.io/v1alpha3|networking.istio.io/v1alpha3>
kind: VirtualService
metadata:
  name: starrocks-fe-tcp
spec:
  hosts:
  - "*"
  gateways:
  - starrocks-gateway
  tcp: # Use 'tcp' for port 9030
  - match:
    - port: 9030
    route:
    - destination:
        host: <your-fe-service-name>.<namespace>.svc.cluster.local
        port:
          number: 9030
────────── Recommendation 3: Traefik IngressRouteTCP If you prefer Traefik, it uses a custom resource called 
IngressRouteTCP
. This is cleaner than Istio's YAML but requires the Traefik entrypoint to be pre-configured for port 9030.
Copy code
yaml
apiVersion: <http://traefik.containo.us/v1alpha1|traefik.containo.us/v1alpha1>
kind: IngressRouteTCP
metadata:
  name: starrocks-mysql
spec:
  entryPoints:
    - mysql # This must be defined in your Traefik static config
  routes:
    - match: HostSNI(`*`) # TCP without TLS doesn't support SNI, so use '*'
      services:
        - name: <your-fe-service-name>
          port: 9030
Why your 
port-forward
is unstable Kubernetes 
port-forward
was never intended for persistent DB connections. It uses an SPDY or HTTP/2 stream to tunnel data, which is frequently interrupted by network jitter or pod restarts. Moving to a dedicated Service (NLB or Gateway) will provide: 1. High Availability: It will load balance across your 3 FE nodes automatically. 2. Persistence: Your BI tools (Tableau, PowerBI, etc.) will stay connected. 3. Auditability: You can log source IPs through the LB. Summary Checklist for StarRocks 4.0.x * Security Groups: Ensure your EKS Worker Node Security Group allows inbound traffic on port 9030 from the VPC CIDR or the Load Balancer SG. * FE Proxy: If you eventually plan to use Stream Load via these same developers, consider also exposing port 8030 (HTTP) through the same Istio/Traefik setup, as StarRocks redirects data loads from FE to CN nodes. Recommendation: Go with Option 1 (Internal NLB) for the best performance and least "proxy overhead" for heavy SQL result sets. References
s
Hey @Rocky, we deployed StarRocks 4.0.9 shared-data on EKS with Iceberg REST catalog via Lakekeeper. We're seeing a recurring issue where some external catalogs randomly get into a broken state with "Failed to load table using REST Catalog" error. The only fix is DROP CATALOG + re-register. Nothing changed in the catalog properties — it seems like internal catalog metadata cache gets corrupted after FE restarts or network timeouts. We currently use 
CREATE EXTERNAL CATALOG IF NOT EXISTS
in our init script (runs on every ArgoCD sync).
r
The behavior you're describing—where the Iceberg REST catalog in StarRocks enters a "broken" state that only a 
DROP
and 
CREATE
can fix—is usually caused by the Iceberg REST client object in the FE (Frontend) memory becoming stale or misconfigured without triggering an automatic recovery. Since you are using StarRocks 4.0.9 (likely a developer/nightly build or a typo for 3.3.x/3.4.x) on EKS with ArgoCD, here is the breakdown of why this happens and the best way to handle it. 1. Root Cause: 
CREATE IF NOT EXISTS
vs. Metadata Updates When you use 
CREATE EXTERNAL CATALOG IF NOT EXISTS
in an ArgoCD init script, StarRocks checks if the catalog name exists in its internal metadata. * The Issue: If it exists, StarRocks does nothing. It does not update the properties, nor does it refresh the underlying connection client. * The "Broken" State: If your Lakekeeper instance restarted, had a networking hiccup, or if an OAuth/JWT token expired, the Java REST client inside the StarRocks FE might have a stale connection state or an invalid auth context. StarRocks' metadata cache for the catalog configuration stays "sticky" until the catalog is dropped or the FE is restarted. 2. The Recommended Fix: 
REFRESH EXTERNAL CATALOG
 Instead of the destructive 
DROP
+ 
CREATE
(which can temporarily break active queries), you should use the Refresh command. This command clears the metadata cache and forces StarRocks to re-initialize the catalog client. Add this to your automation or manual troubleshooting steps:
Copy code
sql
REFRESH EXTERNAL CATALOG <catalog_name>;
* What it does: It clears all cached database and table metadata for that catalog and reloads the catalog configuration properties. It is much safer than 
DROP CATALOG
. 3. Tuning the Metadata Cache If the catalog feels "randomly broken" after a period of inactivity, it might be due to the default metadata expiration being too high (24 hours). You can tune these FE dynamic parameters to be more aggressive:
Copy code
sql
-- Decrease the cache expiration (e.g., to 2 hours instead of 24h)
ADMIN SET FRONTEND CONFIG ("iceberg_metadata_memory_cache_expiration_seconds" = "7200");

-- Ensure background refresh is active (helps perceive changes in Lakekeeper)
ADMIN SET FRONTEND CONFIG ("enable_background_refresh_connector_metadata" = "true");
ADMIN SET FRONTEND CONFIG ("background_refresh_metadata_interval_millis" = "300000"); -- 5 mins
4. Credential & Auth Persistence If you are using OAuth2 or JWT with Lakekeeper: * Credentials over Tokens: Ensure you are using 
iceberg.catalog.oauth2.credential
(client secret) rather than a static 
iceberg.catalog.oauth2.token
. If you use a static token, once it expires in Lakekeeper, StarRocks has no way to refresh it except for a catalog recreation. * Vended Credentials: Ensure 
"iceberg.catalog.vended-credentials-enabled" = "true"
(default) is set. This allows Lakekeeper to provide short-lived S3 credentials to StarRocks, preventing long-term credential leakage. 5. Check FE Logs for specific "HTTP 401" or "403" When the catalog "breaks," check the FE leader log (
fe.log
). Look for the stack trace associated with 
Failed to load table
. * If you see Connection Timeout, consider increasing the FE-level HTTP timeout for Iceberg (though usually, the default is sufficient). * If you see Auth failures, it confirms the StarRocks REST client is not refreshing its token against Lakekeeper properly. Summary Strategy for ArgoCD Modify your init/sync script to use an "Upsert" logic if possible, or trigger a refresh: 1. Run your 
CREATE EXTERNAL CATALOG IF NOT EXISTS
. 2. Follow it with 
REFRESH EXTERNAL CATALOG <name>;
to ensure that every time ArgoCD syncs, the FE client is forced to a clean state without dropping the catalog object entirely metadata corruption. References • data_source/catalog/iceberg/iceberg_catalog.md
s
hey @Rocky, We've diagnosed a reproducible issue with our StarRocks 4.0.9 + Lakekeeper (Iceberg REST catalog) setup: Root cause confirmed: OAuth tokens (Azure AD, client credentials flow) have a 1-hour TTL StarRocks caches the token at 
CREATE EXTERNAL CATALOG
time and never refreshes it • After exactly 1 hour, all 72 external catalogs fail simultaneously with 
"Failed to list all databases using REST Catalog"
• StarRocks validates token expiry locally (no Lakekeeper call) how to solve it?
r
This issue occurs because the Apache Iceberg REST Client (used internally by the StarRocks FE) does not always enable automatic token refresh by default, or it lacks the specific endpoint information to do so for an external provider like Azure AD. When the token expires after 1 hour, the cached client object in the FE becomes invalid, leading to the "Failed to list all databases" error across all catalogs sharing those credentials. To solve this in StarRocks 4.0.x, you need to explicitly enable the refresh mechanism and point the catalog directly to the Azure AD token endpoint (bypassing Lakekeeper for the token exchange if necessary). Recommended Solution: Enable OAuth2 Token Refresh Update your catalog registration properties (or your ArgoCD init script) to include the following specific Iceberg REST properties. StarRocks passes these directly to the underlying Iceberg Java client:
Copy code
sql
ALTER EXTERNAL CATALOG <catalog_name> SET PROPERTIES (
    -- 1. Explicitly enable token refresh (Apache Iceberg property)
    "iceberg.catalog.oauth2.token-refresh-enabled" = "true",
    
    -- 2. Direct StarRocks to the Azure AD Token Endpoint
    -- Replace <tenant-id> with your actual Azure Tenant ID
    "iceberg.catalog.oauth2.server-uri" = "<https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token>",
    
    -- 3. Ensure you are using 'credential' (client_id:client_secret) 
    -- and NOT a static 'token'
    "iceberg.catalog.oauth2.credential" = "<client_id>:<client_secret>",
    
    -- 4. Set the required scope for Azure AD (usually the default .default scope)
    "iceberg.catalog.oauth2.scope" = "<https://storage.azure.com/.default>"
);
Why this works: 1. 
token-refresh-enabled
: Forces the internal 
RESTClient
to schedule a refresh task based on the 
expires_in
field returned by Azure AD. 2. 
server-uri
: By default, the REST client tries to get tokens from the 
iceberg.catalog.uri
(Lakekeeper). If Lakekeeper doesn't implement the 
/v1/oauth/tokens
proxy correctly or doesn't support the specific refresh flow for Azure AD, the refresh fails. Providing the direct Azure URL ensures the FE can fetch a new token independently. 3. Local Validation: The "local validation" you observed is the Java client checking the 
expires_at
timestamp. Once it sees the token is expired, if 
token-refresh-enabled
is false, it simply throws an exception instead of attempting a new POST request to the token server. Alternative: Use Azure Workload Identity (EKS Native) Since you are on EKS, a more robust approach is to avoid Client Secrets entirely and use Azure Workload Identity (if you have an OIDC link between EKS and Azure). * Annotate your StarRocks FE/CN Kubernetes Service Account with the Azure Client ID. * The token will be projected into the pod. * StarRocks 4.0 supports reading these tokens from a file:
Copy code
sql
    "azure.adls2.oauth2_use_managed_identity" = "true",
    "azure.adls2.oauth2_tenant_id" = "<tenant_id>",
    "azure.adls2.oauth2_client_id" = "<client_id>"
Summary for your 72 Catalogs If you are managing 72 catalogs via ArgoCD, ensure your script includes the 
iceberg.catalog.oauth2.token-refresh-enabled
 property. Without it, the FE metadata will cache the first token it gets at creation time and never look back until a 
DROP/CREATE
or FE restart clears the memory. Note on Property Names: In some versions of StarRocks, the property might use hyphens instead of dots (e.g., 
iceberg.catalog.oauth2-token-refresh-enabled
). If the dot notation doesn't work, try the hyphenated version. References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_rest_security.md
s
Hi @Rocky Following up on the OAuth token expiry issue with StarRocks 4.0.9 + Lakekeeper (Iceberg REST catalog + Azure AD client credentials). We've done extensive testing and have concrete findings. Confirmed behavior: • OAuth tokens expire after exactly 1 hour — all 72 catalogs fail simultaneously at the 60-minute mark • 
"iceberg.catalog.oauth2.token-refresh-enabled" = "true"
— does not work, tokens still expire after 1 hour 
REFRESH EXTERNAL TABLE
— does not refresh the token, uses existing cached token • Background metadata refresh (
background_refresh_metadata_interval_millis = 300000
) — does not refresh tokens • 
SHOW DATABASES IN catalog
— does not refresh the token • Only DROP + CREATE resets the token — confirmed working but causes ~1 second downtime per catalog Questions: Is 
iceberg.catalog.oauth2.token-refresh-enabled
actually supported in 4.0.9? Is there a correct property name? Is there ANY way to refresh the OAuth token for an existing catalog without DROP + CREATE? 1. Does 
ALTER CATALOG ... SET PROPERTIES (...)
force a token re-fetch? 2. Is this fixed in 4.1? We see the release notes mention deeper Iceberg integration.
r
Sorry, I encountered an error while processing your request: 429 RESOURCE_EXHAUSTED. {'error': {'code': 429, 'message': 'Resource has been exhausted (e.g. check quota).', 'status': 'RESOURCE_EXHAUSTED'}}
s
Hi @Rocky Following up on the OAuth token expiry issue with StarRocks 4.0.9 + Lakekeeper (Iceberg REST catalog + Azure AD client credentials). We've done extensive testing and have concrete findings. Confirmed behavior: • OAuth tokens expire after exactly 1 hour — all 72 catalogs fail simultaneously at the 60-minute mark • 
"iceberg.catalog.oauth2.token-refresh-enabled" = "true"
— does not work, tokens still expire after 1 hour 
REFRESH EXTERNAL TABLE
— does not refresh the token, uses existing cached token • Background metadata refresh (
background_refresh_metadata_interval_millis = 300000
) — does not refresh tokens • 
SHOW DATABASES IN catalog
— does not refresh the token • Only DROP + CREATE resets the token — confirmed working but causes ~1 second downtime per catalog Questions: Is 
iceberg.catalog.oauth2.token-refresh-enabled
actually supported in 4.0.9? Is there a correct property name? Is there ANY way to refresh the OAuth token for an existing catalog without DROP + CREATE? 1. Does 
ALTER CATALOG ... SET PROPERTIES (...)
force a token re-fetch? 2. Is this fixed in 4.1? We see the release notes mention deeper Iceberg integration.
r
It appears you've run into a known limitation in how StarRocks (and the underlying Iceberg Java client) handles OAuth2 token lifecycle management. In version 4.0.x, the "stickiness" of the REST client configuration in the Frontend (FE) memory is the primary hurdle. Based on the latest engineering details for StarRocks 4.0/4.1 and Iceberg REST integration, here are the specific answers to your questions and the recommended path forward. 1. The Correct Property Name (Hyphens vs. Dots) In StarRocks 4.0.9, properties prefixed with 
iceberg.catalog.*
are passed to the Iceberg 
CatalogProperties
. However, the token refresh logic specifically relies on the Apache Iceberg 
RESTSessionCatalog
implementation. There is a known inconsistency where some versions of the StarRocks connector expect hyphens for certain OAuth properties to correctly map them to the Iceberg client factory. Try updating your catalog to use the hyphenated version: * Try this property: 
"iceberg.catalog.oauth2-token-refresh-enabled" = "true"
* And ensure this is set: 
"iceberg.catalog.oauth2-server-uri" = "<https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token>"
If you use dots (
oauth2.token...
), StarRocks might treat it as a custom property and not trigger the internal Iceberg client's 
TokenRefreshManager
thread. 2. Does 
ALTER CATALOG
or 
REFRESH
work for tokens? * 
REFRESH EXTERNAL TABLE
: This only invalidates the metadata (schemas/partitions) for that specific table. It does not re-initialize the Catalog object or its HTTP client, so it will continue using the expired token. * 
REFRESH EXTERNAL CATALOG <name>
: This is more powerful than the table-level refresh, but in 4.0.9, it often only clears the database/table list cache. It may not force the Java 
RESTClient
to dispose of its current auth session. * 
ALTER CATALOG ... SET PROPERTIES
: In StarRocks 4.x, executing an 
ALTER CATALOG
with a change to a property does force the FE to discard the existing Catalog instance and create a new one. * Workaround: You can "tickle" the catalog by altering a dummy property. This will force a token re-fetch without the downtime of a full 
DROP/CREATE
.
Copy code
sql
    ALTER EXTERNAL CATALOG <catalog_name> SET PROPERTIES ("token_refresh_trigger" = "2026-04-30-10-00");
3. Improvements in StarRocks 4.1 Yes, StarRocks 4.1 (and late 4.0.x patches) includes a significant refactor of the Iceberg Connector (
IcebergCatalog
class). * Vended Credentials & Session Token Passthrough: 4.1 introduces better support for Azure Workload Identity. Since you are on EKS, the recommended long-term fix is to stop using client secrets in the catalog properties entirely. * Identity Passthrough: If your users are also authenticated via JWT/Azure AD, StarRocks 4.1 can pass the user's own token to the REST catalog (Lakekeeper), letting Lakekeeper handle the refresh/validation lifecycle. Recommended Immediate Fix (Azure AD Specific) Since Azure AD tokens are being validated locally and failing at the 1-hour mark, the Iceberg client thinks it doesn't have a valid "Refresh Token" to get a new "Access Token." Update your 72 catalogs with the following set of properties. This configuration is known to be more stable for Azure REST flows:
Copy code
sql
ALTER EXTERNAL CATALOG <name> SET PROPERTIES (
    "iceberg.catalog.security" = "oauth2",
    "iceberg.catalog.oauth2-token-refresh-enabled" = "true",
    "iceberg.catalog.oauth2-server-uri" = "<https://login.microsoftonline.com/<tenant>/oauth2/v2.0/token>",
    "iceberg.catalog.credential" = "client_id:client_secret",
    "iceberg.catalog.oauth2.scope" = "<https://storage.azure.com/.default>"
);
Why this helps: 1. Direct URI: It bypasses Lakekeeper for the token exchange, ensuring the client talks directly to Azure. 2. Hyphenated Property: It ensures the StarRocks 
IcebergClientFactory
correctly identifies the refresh-enabled flag.
3. Scope: Explicitly defining the scope prevents Azure from returning a token that might lack certain metadata permissions which often leads to the 
"Failed to list all databases"
error. Summary Strategy for ArgoCD To avoid the ~1s downtime of 
DROP
, modify your ArgoCD sync logic to: 1. Run 
CREATE EXTERNAL CATALOG IF NOT EXISTS
. 2. If the catalog already exists, run the 
ALTER EXTERNAL CATALOG ... SET PROPERTIES
command. In StarRocks 4.x, this acts as a "soft reset" that re-authenticates the session without deleting the metadata objects. References • data_source/catalog/maxcompute_catalog.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md • data_source/catalog/hive_catalog.md • data_source/catalog/hudi_catalog.md • administration/management/FE_configuration.md • sql-reference/sql-statements/Catalog/CREATE_EXTERNAL_CATALOG.md
s
Hey @Rocky, we've been debugging the StarRocks Iceberg REST catalog token expiry issue. Here's what we found and want your input: Problem: All 73 external Iceberg catalogs fail after exactly 1 hour with 
Failed to list all databases using REST Catalog
. Root cause confirmed: Azure AD OAuth2 access tokens expire after 1h, and StarRocks doesn't auto-refresh them. What we tested: • 
iceberg.catalog.oauth2.token-refresh-enabled
— doesn't exist, had no effect • Various hyphenated property names — broke the initial connection • 
ALTER CATALOG org_xxx SET ("iceberg.catalog.oauth2.credential" = "client_id:secret")
— this works! Catalog recovers immediately after running it on a failed catalog Questions: Is there a native StarRocks mechanism to auto-refresh OAuth2 tokens for REST catalogs, without external intervention? (We're on v4.0.9) Is upgrading to v4.1 expected to fix this natively? Is the 
ALTER CATALOG
approach (running every 55 min via CronJob) a reasonable production solution, or is there a better pattern you'd recommend? 1. Are there any known risks with running 
ALTER CATALOG
frequently on active catalogs (active queries, metadata cache invalidation)?
r
This is a known challenge in the StarRocks 4.0.x series because the Frontend (FE) metadata layer treats the Iceberg REST Catalog object as "sticky." While the underlying Apache Iceberg Java client can refresh tokens, it must be explicitly configured to do so via properties that StarRocks passes through. Here is the breakdown of your questions and the recommended production path. 1. Is there a native mechanism in v4.0.9? The native mechanism exists in the underlying Iceberg library, but StarRocks 4.0.9 has strict property mapping. The reason your hyphenated tests likely "broke" the connection is that the Iceberg REST client expects a specific mix of properties. In StarRocks 4.0.9, try this specific combination of properties (notably using 
oauth2-server-uri
with a hyphen, as this is often required to trigger the Iceberg 
RESTClient
refresh thread):
Copy code
sql
ALTER EXTERNAL CATALOG <catalog_name> SET PROPERTIES (
    "iceberg.catalog.oauth2-server-uri" = "<https://login.microsoftonline.com/<tenant>/oauth2/v2.0/token>",
    "iceberg.catalog.oauth2-token-refresh-enabled" = "true",
    "iceberg.catalog.oauth2.credential" = "client_id:client_secret",
    "iceberg.catalog.oauth2.scope" = "<https://storage.azure.com/.default>"
);
Note: If 
iceberg.catalog.oauth2.credential
(dots) is used, ensure the refresh property uses a hyphen (
oauth2-token-refresh-enabled
). StarRocks sometimes fails to pass dotted custom properties to the internal Iceberg 
TokenRefreshManager
. 2. Is this fixed in v4.1? Yes, v4.1 introduces significant improvements. * Deep Iceberg Integration: Version 4.1.x updates the internal Iceberg SDK and refactors the 
IcebergCatalog
class to handle OAuth2 flows more gracefully, especially for REST endpoints like Polaris or Lakekeeper. * JWT Passthrough: Version 4.1 allows for JWT-based identity passthrough. If your StarRocks users authenticate via Azure AD, StarRocks can pass the user’s own token to the REST catalog. This offloads the token lifecycle to the client/IdP session rather than a static catalog credential. * Managed Identity: v4.1 has more robust support for Azure Workload Identity (EKS OIDC), which removes the need for client secrets and manual token refreshes entirely. 3. Is the 
ALTER CATALOG
CronJob a reasonable solution? Actually, your discovery that 
ALTER CATALOG ... SET ("iceberg.catalog.oauth2.credential" = "...")
works is the most reliable workaround for 4.0.9. Why it works: In StarRocks, an 
ALTER CATALOG
operation that modifies a property forces the FE to discard the existing Catalog object and its cached HTTP/REST client. The next query triggers a fresh initialization, which performs a new OAuth2 
client_credentials
exchange. Risks of the 55-minute CronJob: * Metadata Cache Invalidation: The primary risk is that 
ALTER CATALOG
clears the FE's cached list of databases and tables for that catalog. The first query after the cron job will experience a "cold start" latency (likely 500ms–2s) as it re-fetches the metadata from Lakekeeper. * Active Queries: Queries already in the execution phase (on the BEs/CNs) are unaffected because they already have their data splits and file paths. * Active Planning: Queries in the planning phase exactly during the 
ALTER
might fail with a "Catalog not found" or "Metadata changed" error, but this window is extremely small (milliseconds). Recommended Pattern If you are sticking with v4.0.9 for now, the CronJob approach is a common and safe pattern used by many large-scale StarRocks users. To minimize impact: 1. Stagger the refreshes: Don't refresh all 73 catalogs at the exact same second. Jitter the refreshes over a 5-minute window to avoid a spike in FE CPU and Lakekeeper requests. 2. Tickle the Catalog: You don't even need to update the secret. You can update a dummy property to force the re-init: ```sql
ALTER EXTERNAL CATALOG <name> SET PROPERTIES ("refresh-timestamp" = "2024-05-20-10-00"); ``` Final Recommendation: Plan an upgrade to v4.1.x (specifically 4.1.1+) when possible. It moves away from "static secret" management toward native Azure identity integration, which is the intended architectural fix for this. References • data_source/catalog/iceberg/iceberg_rest_security.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md • data_source/catalog/hive_catalog.md • data_source/catalog/maxcompute_catalog.md • sql-reference/sql-statements/Catalog/CREATE_EXTERNAL_CATALOG.md • administration/management/FE_configuration.md • data_source/feature-support-data-lake-analytics.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md References • data_source/catalog/iceberg/iceberg_rest_security.md • data_source/catalog/iceberg/iceberg_catalog.md
s
@Rocky is there any local connection between lakekeeper and starrocks when they both on same EKS cluster?
r
When deploying both StarRocks and Lakekeeper (or any Iceberg REST catalog) on the same Amazon EKS cluster, you gain several "local" advantages—though they are primarily architectural and network-based rather than a proprietary protocol. Here is how you can leverage their co-location to solve your token expiry and connectivity issues: 1. Internal Kubernetes Networking (The "Local" Pipe) Since both are in the same cluster, you should bypass the public internet and Azure AD endpoints for the initial handshake whenever possible. * Service DNS: Use the internal Kubernetes Service address for your catalog URI: 
"iceberg.catalog.uri" = "<http://lakekeeper.namespace.svc.cluster.local:8181>"
* Benefits: This reduces latency (sub-millisecond), eliminates NAT gateway/Egress costs, and keeps your metadata traffic entirely within your VPC. 2. Offloading Storage Auth via "Vended Credentials" Since you are on StarRocks 4.0.9, you should enable Vended Credentials. This is the single biggest benefit of the REST architecture. * The Problem: Currently, StarRocks is likely trying to manage Azure AD tokens for both the Catalog and the underlying Parquet files in ADLS Gen2. * The Solution: Let Lakekeeper handle the Azure storage secrets. StarRocks only needs a token to talk to Lakekeeper. Lakekeeper then "vends" (hands over) short-lived, temporary SAS tokens to StarRocks for each data read. * Catalog Property:
Copy code
sql
    "iceberg.catalog.vended-credentials-enabled" = "true"
_Note: With this enabled, you can remove all 
azure.adls2._
properties from your StarRocks catalog definition. StarRocks will simply use whatever token Lakekeeper gives it.* 3. Native Kubernetes Identity (IRSA/OIDC) Instead of hardcoding an Azure AD client secret in 73 catalogs (which is what's expiring after 1 hour), you can leverage the EKS OIDC Provider. * Lakekeeper Side: Lakekeeper supports native Kubernetes authentication (
auth.k8s.enabled: true
in its Helm chart). It can validate the Service Account token of the StarRocks FE pod. * StarRocks Side: While 4.0.9 primarily uses 
client_credentials
, it supports JWT Identity Passthrough. If you authenticate to StarRocks using a JWT, StarRocks 4.0+ can forward that identity to Lakekeeper. * The "Local" Advantage: In a K8s-native setup, you can configure Lakekeeper to trust the Kubernetes OIDC issuer. This allows StarRocks to communicate with Lakekeeper using its pod identity, avoiding the "1-hour Azure Secret" loop entirely. 4. Summary of Recommended "Local" Configuration To stabilize your 73 catalogs on 4.0.9 without the 1-hour failure, use this pattern which combines internal networking and vended credentials:
Copy code
sql
CREATE EXTERNAL CATALOG lakekeeper_local
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "rest",
    -- Use local K8s DNS
    "iceberg.catalog.uri" = "<http://lakekeeper-service.lakekeeper-ns:8181>",
    -- Enable vended credentials to stop StarRocks from managing Azure secrets
    "iceberg.catalog.vended-credentials-enabled" = "true",
    -- Security settings
    "iceberg.catalog.security" = "oauth2",
    "iceberg.catalog.oauth2-token-refresh-enabled" = "true",
    "iceberg.catalog.oauth2.credential" = "client_id:client_secret"
);
Why this helps your specific situation: 1. Vended Credentials means StarRocks only does one OAuth handshake (with Lakekeeper). It no longer needs to fetch/refresh tokens for the thousands of files in Azure Storage. 2. Internal URI ensures that even if there's a transient network issue with Azure, the FE-to-Catalog metadata heartbeats stay local to EKS. 3. Upgrade Path: In StarRocks 4.1, the integration goes deeper, allowing StarRocks to use the EKS Pod Identity (IRSA) to authenticate to the REST catalog natively, which completely eliminates the need for the 
client_id:secret
string and the 1-hour refresh headache. References
s
@Rocky, do you have a graph search capability?
r
StarRocks is not a native Graph Database (like Neo4j), but it provides several powerful mechanisms for Graph-like search and processing, depending on what "graph search" means for your use case: 1. Recursive SQL (Hierarchies & Traversals) If your graph search involves traversing relationships (e.g., "Find all subordinates of Manager X" or "Find all connected devices"), StarRocks added Recursive Common Table Expressions (Recursive CTEs) in version 4.1. * Capability: Allows 
WITH RECURSIVE
queries to traverse tree and graph structures. * Limitation: You are currently on v4.0.9, so you would need to upgrade to v4.1 to use this natively. In 4.0.9, these searches must be done via multiple manual 
JOIN
operations. 2. Vector Search (Similarity & GNNs) If your graph search involves finding "similar entities" (common in Graph Neural Networks or Knowledge Graphs), StarRocks has a high-performance Vector Index. * Capability: Supports 
HNSW
and 
IVFFLAT
indexes for fast similarity searches using embeddings. * Use Case: You can store graph node embeddings in StarRocks and perform sub-second "Top-K" neighbor searches across millions of nodes. 3. Integrated Graph Engines (Cypher/Gremlin) For users who need a true Graph Query Language (like Cypher) while keeping their data in StarRocks, there are third-party integrations: * PuppyGraph: A graph query engine that can treat StarRocks tables as a graph. It allows you to run Cypher queries directly against your StarRocks data without moving the data out. 4. Text-Based Search If your "search" is about finding nodes by attributes: * Inverted Indexes: StarRocks supports full-text inverted indexes for high-speed keyword searching within node properties. * N-gram Bloom Filters: Useful for fuzzy matching of names or IDs within a graph-like dataset. Summary for your Current Setup (v4.0.9): * Recursive Search: Not natively available (arrives in v4.1). * Vector Similarity Search: Fully supported. * Graph Engine: Requires an external integration like PuppyGraph. Recommendation: If you need to perform deep path traversals natively in SQL, the upgrade to v4.1 is highly recommended as it introduces the 
WITH RECURSIVE
feature specifically for these scenarios. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md
s
@Rocky, is the materialized view, created from query on external iceberg table, must be built on default starrocks catalog? i cant create it in same external catalog where the original query come from?
r
Yes, you are correct—in StarRocks, an asynchronous Materialized View (MV) must be created within the internal catalog (
default_catalog
), even if the query it is based on pulls data from an external Iceberg catalog. Here is why this is required and how the architecture works: 1. The Internal Catalog Requirement The StarRocks 
default_catalog
is the only catalog that supports native StarRocks storage management. When you create an asynchronous MV, StarRocks does more than just save a query; it creates an actual physical table to store the pre-calculated results. * Metadata Management: The FE (Frontend) needs full control over the MV's refresh state, versioning, and partition-level metadata. External catalogs like Iceberg (managed via REST/Lakekeeper) are treated as "read-only" or "managed by an external authority" for the purposes of StarRocks' internal MV acceleration logic. * Storage Control: StarRocks stores MV data in its high-performance storage format (on its own BEs or in a managed S3/Blob storage bucket for shared-data mode). It cannot currently "own" the storage and lifecycle of an object inside a third-party managed Iceberg catalog. 2. How to set it up correctly You simply reference the external catalog in the 
AS SELECT
portion of your statement, but you do not prefix the MV name with the external catalog name. Correct Pattern:
Copy code
sql
-- Ensure you are in a database within the default_catalog
USE default_catalog.my_warehouse_db;

CREATE MATERIALIZED VIEW iceberg_acceleration_mv
PARTITION BY (dt)
DISTRIBUTED BY HASH(id)
REFRESH ASYNCHRONOUS EVERY (INTERVAL 1 HOUR)
AS 
SELECT id, data, category, ts, date_trunc('day', ts) as dt
FROM `azure_iceberg_catalog`.`production_db`.`raw_logs` -- Reference external here
WHERE ts > '2024-01-01';
3. What if you need the data back in the Iceberg Catalog? If your goal is to have the result of a StarRocks computation available as a physical Iceberg table for other tools (like Spark or Presto) to read from the REST catalog, you have two options: * Logical Views (v3.5+): You can create an Iceberg View in the external catalog. This stores the SQL definition in Lakekeeper, but it doesn't cache data (it is not "materialized"). * INSERT INTO (Unified Catalog): You can manually or via a CronJob run an 
INSERT INTO ... SELECT
or 
INSERT OVERWRITE
from StarRocks into a target table in your Iceberg catalog. This moves the data into the external catalog, but it won't benefit from StarRocks' automatic "Transparent Query Rewrite" that MVs provide. Summary * Acceleration MVs: Must live in 
default_catalog
. They act as a local "cache" for your 73 external Iceberg catalogs. * External Catalogs: Are used as sources. * Query Rewrite: Even though the MV is in 
default_catalog
, StarRocks is smart enough to see a query hitting 
azure_iceberg_catalog.db.table
and automatically redirect it to the MV in 
default_catalog
to save costs and time. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • table_design/table_design.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • data_source/catalog/iceberg/DDL.md
s
@Rocky, so the multi catalogs per customer is broken when inserting all in same default storage
r
I understand your concern—if you have 73 separate catalogs (one per customer) to maintain strict isolation, forcing all accelerated data (Materialized Views) into a single 
default_catalog
feels like it breaks that multi-tenant boundary. Here is the technical reality of how to handle this in StarRocks 4.0.9 without compromising your architecture: 1. The "Default Catalog" is just the Metadata Home Even though the MV must be registered in the 
default_catalog
, you can (and should) maintain isolation by creating a shadow database structure. * Logical Isolation: Create one database in the 
default_catalog
for every external catalog (e.g., 
db_customer_1_cache
, 
db_customer_2_cache
). * RBAC Isolation: Use StarRocks' Role-Based Access Control (RBAC) to ensure that the user/service account for "Customer A" only has permissions for 
customer_a_iceberg_catalog
and 
default_catalog.db_customer_a_cache
. They will never see or access other customers' cached data. 2. Can you store the MV data back in the Customer's Iceberg? As of StarRocks 4.0.x, Materialized Views cannot be stored directly inside an external REST catalog (like Lakekeeper/Iceberg) if you want the "Transparent Query Rewrite" feature. * The Reason: For StarRocks to automatically swap a slow Iceberg query for a fast MV query, it needs to manage the MV's refresh state and metadata itself. Most external REST catalogs don't allow StarRocks to "own" the full lifecycle and internal metadata required for these advanced optimizations. * The Result: The MV data is stored on your StarRocks BE nodes (local disks or S3). This is actually beneficial because StarRocks storage is significantly faster for query acceleration than reading from Iceberg/S3. 3. Alternative: Unified Storage (Shared-Data Mode) If you are worried about "all data in one place" from a storage compliance perspective: * In StarRocks Shared-Data mode (common in EKS), StarRocks stores its data in S3/Azure Blob. * While all MVs are logically in 
default_catalog
, the data is still just objects in a bucket. If you need storage-level isolation (separate keys/buckets per customer), you would currently need separate StarRocks clusters or wait for future "multi-storage-volume" support. 4. How to prevent the "Broken" feel To maintain your multi-tenant design, use this naming and permission pattern:
Copy code
sql
-- 1. External Catalog (Customer 1)
-- Linked to Lakekeeper namespace 'cust1'
CREATE EXTERNAL CATALOG cust1_iceberg ...; 

-- 2. Internal Cache Database (Customer 1)
-- This lives in StarRocks storage but is isolated by RBAC
CREATE DATABASE default_catalog.cust1_cache;

-- 3. Create the MV in the cache DB
CREATE MATERIALIZED VIEW default_catalog.cust1_cache.active_users_mv
REFRESH ASYNCHRONOUS EVERY (INTERVAL 1 HOUR)
AS 
SELECT ... FROM cust1_iceberg.prod_db.users;

-- 4. Grant Permissions
GRANT SELECT ON DATABASE default_catalog.cust1_cache TO ROLE 'customer_1_role';
GRANT USAGE ON CATALOG cust1_iceberg TO ROLE 'customer_1_role';
Summary Your multi-catalog architecture isn't "broken," but your acceleration layer (the MVs) must be managed centrally by the StarRocks engine to function. By using a 1:1 mapping between external catalogs and internal "cache" databases combined with strict RBAC, you maintain the same level of tenant isolation while gaining the performance of local StarRocks storage. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • administration/privilege_item.md • using_starrocks/async_mv/Materialized_view.md
s
@Rocky, what are our options to set the physical storage of default catalog? is there any option to set different bucketa per Database?
r
Yes, you can absolutely set different physical storage (buckets) per Database within the 
default_catalog
using a feature called Storage Volumes (introduced in StarRocks 3.1+). This is the recommended way to maintain physical data isolation in a multi-tenant environment while still using StarRocks' internal Materialized Views for acceleration. How it Works In Shared-Data (Cloud-Native) mode, StarRocks allows you to define multiple "Storage Volumes," each pointing to a different S3 bucket (or a specific path in a bucket) with its own credentials. You can then assign these volumes to specific databases. 1. Create a Storage Volume for each customer Each volume can have its own bucket and its own IAM/Access Key credentials.
Copy code
sql
-- Customer A Storage Volume
CREATE STORAGE VOLUME customer_a_volume
TYPE = S3
LOCATIONS = ("<s3://customer-a-private-bucket/starrocks_data/>")
PROPERTIES
(
    "enabled" = "true",
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "AKIA...",
    "aws.s3.secret_key" = "SECRET..."
);

-- Customer B Storage Volume
CREATE STORAGE VOLUME customer_b_volume
TYPE = S3
LOCATIONS = ("<s3://customer-b-private-bucket/starrocks_data/>")
PROPERTIES
(
    "enabled" = "true",
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "AKIA...",
    "aws.s3.secret_key" = "SECRET..."
);
2. Assign the Volume to a Database When you create the database in the 
default_catalog
for that customer's Materialized Views/Tables, you link it to their specific volume.
Copy code
sql
-- Any table or MV created in this DB will physically live in Customer A's bucket
CREATE DATABASE cust1_cache 
PROPERTIES ("storage_volume" = "customer_a_volume");

-- Any table or MV created in this DB will physically live in Customer B's bucket
CREATE DATABASE cust2_cache 
PROPERTIES ("storage_volume" = "customer_b_volume");
Key Benefits for your Architecture: * Physical Isolation: Data for Customer A's MVs never touches the same S3 bucket as Customer B's. * Inheritance: You don't have to specify the storage for every Materialized View. Any MV created inside 
cust1_cache
automatically inherits the 
customer_a_volume
setting. * Granular Control: If you have a specific MV that is exceptionally large, you can even override the storage volume at the individual Table/MV level. * Billing/Cleanup: Since the data is in the customer's own bucket, you can easily track storage costs per tenant or delete a customer's data entirely by wiping their specific bucket path. Important Note: This feature requires your StarRocks cluster to be running in Shared-Data mode (where StarRocks stores its primary data in object storage rather than local BE disks). In the older Shared-Nothing mode, all data is stored on local disks managed by the BEs, and you cannot map databases to different S3 buckets for their primary storage. References • deployment/shared_data/feature-support-shared-data.md • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/s3.md • deployment/shared_data/azure.md References • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md • deployment/shared_data/azure.md
s
@Rocky, is it possible to connect via superset to starrocks connection of admin user but somehow to restrict the superset user from doing somthing in the starrocks like only read and not write
r
It is not possible to restrict an 
admin
user's own permissions within StarRocks once you have connected as that user. By definition, the 
admin
account has all privileges. If Superset connects using the 
admin
credentials, that connection can technically perform any action (DROP, DELETE, etc.) regardless of Superset's internal settings. However, you have two primary options to achieve your goal: Option 1: Dedicated Restricted User (Best Practice) The standard and most secure way is to create a dedicated user for Superset and grant it only the necessary read-only permissions. This ensures that even if someone manages to run raw SQL via Superset's SQL Lab, they are physically blocked by StarRocks. 1. Create a "Read Only" Role and User:
Copy code
sql
-- Create a role with read-only access
CREATE ROLE superset_read_only;

-- Grant usage on catalogs and select on data
GRANT USAGE ON ALL CATALOGS TO ROLE superset_read_only;
GRANT SELECT ON ALL TABLES IN ALL DATABASES TO ROLE superset_read_only;
GRANT SELECT ON ALL VIEWS IN ALL DATABASES TO ROLE superset_read_only;
GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO ROLE superset_read_only;

-- Create the user and assign the role
CREATE USER 'superset_user'@'%' IDENTIFIED BY 'your_password';
GRANT 'superset_read_only' TO USER 'superset_user'@'%';
SET DEFAULT ROLE 'superset_read_only' FOR 'superset_user'@'%';
2. Update Superset Connection: Change your SQLAlchemy URI in Superset to use 
superset_user
instead of 
admin
. 
<starrocks://superset_user:your_password@<host>:<port>/<database>>
────────── Option 2: User Impersonation (Advanced) If you want to keep the connection authenticated as a powerful user (like 
admin
) but have StarRocks automatically switch permissions based on the specific person logged into Superset, you can use User Impersonation. StarRocks supports the 
EXECUTE AS
command. When you enable impersonation in Superset, the 
admin
user "acts as" the logged-in Superset user for the duration of the query. 1. Grant Impersonation Rights to Admin: The 
admin
user must be allowed to impersonate other users:
Copy code
sql
-- Replace 'target_user' with specific users or use ALL
GRANT IMPERSONATE ON USER 'target_user'@'%' TO USER 'admin'@'%';
2. Enable in Superset: • In Superset, go to Settings -> Database Connections. • Edit your StarRocks connection. • In the Advanced -> Security section, check the box "Impersonate the logged-in user". How it works: When "User A" logs into Superset and runs a query, Superset sends a pre-query: 
EXECUTE AS "User A" WITH NO REVERT;
. StarRocks then checks if "User A" has 
SELECT
permissions. If they don't, the query fails, even though the underlying connection was made by 
admin
. Summary Recommendation • Use Option 1 if you want a simple, "blanket" read-only restriction for everyone using Superset. • Use Option 2 if you need different people in Superset to have different levels of access (e.g., User A can see Finance data, User B cannot) while using a single database connection. References • administration/user_privs/authorization/User_privilege.md • integrations/BI_integrations/Superset.md • sql-reference/sql-statements/account-management/EXECUTE_AS.md • faq/Others.md References • administration/user_privs/authorization/User_privilege.md • faq/Others.md • integrations/superset_support.md
s
@Rocky option b looks fantastic, is that means i will need to create users in starrocks similar to how they in superset right? also is that means that all catalogs created by admin seen to any user before he made this kind of query.
r
To answer your questions about Option B (User Impersonation): 1. Do you need to create users in StarRocks for every Superset user? Yes. The 
EXECUTE AS <username>
command requires that the target 
<username>
already exists in StarRocks. StarRocks does not automatically "create" a user just because Superset tries to impersonate them. * The Workflow: You would create a StarRocks user for each customer or team (e.g., 
cust1_user
, 
cust2_user
). * The Permissions: You grant each of these users only the specific permissions they need (e.g., 
GRANT USAGE ON CATALOG cust1_iceberg TO USER cust1_user
). * The Admin's Role: You grant the 
admin
user the right to impersonate them: 
GRANT IMPERSONATE ON USER cust1_user TO USER admin;
. 2. Can users see catalogs created by Admin before the impersonation? No, they won't see them. When you enable "Impersonate logged-in user" in Superset, the very first thing Superset does after opening a connection is send the 
EXECUTE AS
command. * Metadata Isolation: Once 
EXECUTE AS cust1_user
is executed, the session context switches entirely. If 
cust1_user
does not have 
USAGE
permission on "Customer B's Catalog," that catalog will not appear in 
SHOW CATALOGS
or in the Superset dropdowns for that session. * Security: Even though the physical connection was established using the 
admin
password, the session effectively "becomes" the restricted user. It cannot "see" or "revert" back to the admin's view. Important Technical Details: * Syncing Names: Superset identifies the "logged-in user" by their Superset username. Therefore, the StarRocks username must match the Superset username exactly. * Metadata Explorer: One small caveat: In Superset, the "Database Sync" (where Superset scans for new tables in the background) sometimes uses the main connection credentials (
admin
) without impersonation to build its internal search index. To prevent this, you should ensure that the user IDs you want to isolate are also managed within Superset's own internal RBAC, or use the "Dedicated Restricted User" (Option A) if you want to be 100% sure the metadata sync itself is also limited. Summary of the "Option B" setup: 1. Superset: Use the 
admin
credentials in the connection string. 2. Superset Settings: Check "Impersonate the logged-in user." 3. StarRocks: * Create 
user_a
and 
user_b
. * 
GRANT USAGE ON CATALOG catalog_a TO user_a;
(and nothing for catalog_b). * 
GRANT IMPERSONATE ON USER user_a TO admin;
* 
GRANT IMPERSONATE ON USER user_b TO admin;
This setup is the most powerful way to maintain your multi-tenant architecture while using a single, centralized Superset connection. References • sql-reference/sql-statements/account-management/EXECUTE_AS.md • administration/user_privs/authorization/User_privilege.md • integrations/BI_integrations/Superset.md • sql-reference/sql-statements/account-management/GRANT.md • data_source/catalog/iceberg/iceberg_rest_security.md • administration/user_privs/authorization/privilege_item.md References • faq/Others.md • data_source/catalog/iceberg/iceberg_rest_security.md • integrations/BI_integrations/Superset.md
s
@Rocky what is the best approach for like pagination in query where every next page in app should query the next ordered rows
r
For pagination in StarRocks, especially with large datasets, the Keyset Pagination (also known as the "Seek Method") is the most efficient and recommended approach. While the traditional 
LIMIT offset, count
is easy to write, it becomes significantly slower as the page number increases because the database must scan and discard all previous rows to reach the requested offset. 1. The Best Approach: Keyset Pagination (Seek Method) Instead of telling the database how many rows to skip, you tell it the exact point where the last page ended using a unique, ordered column (like a Primary Key or a unique ID). Example (Single Column): If you are sorting by `id`:
Copy code
sql
-- Page 1
SELECT * FROM orders 
ORDER BY id ASC 
LIMIT 20;

-- Page 2 (Assume the last ID of Page 1 was 500)
SELECT * FROM orders 
WHERE id > 500 
ORDER BY id ASC 
LIMIT 20;
Why it's better: * Performance: StarRocks can use its Short-key Index and Zone-map to jump directly to the data, rather than scanning thousands of rows to "skip" them. * Consistency: If a row is deleted or inserted while the user is browsing, the pages won't "shift" or show duplicate rows. ────────── 2. Handling Multi-Column Sorting If you need to sort by a non-unique column (like 
created_at
), you must append a unique column (like 
id
) to ensure the order is stable and pagination works correctly. Example (Multi-Column using Tuple Comparison): StarRocks (v3.1+) supports row/tuple comparison, which makes this very clean:
Copy code
sql
-- Page 2 (Assume the last row had created_at = '2023-10-01' and id = 123)
SELECT * FROM orders 
WHERE (created_at, id) > ('2023-10-01', 123) 
ORDER BY created_at ASC, id ASC 
LIMIT 20;
_Note: If you are on an older version, you would write this as: 
WHERE created_at > '...' OR (created_at = '...' AND id > ...)
._ ────────── 3. Summary of Pagination Strategies Method | SQL Example | Performance | Use Case Offset | 
LIMIT 1000, 20
| Poor (O(N)) | Small datasets or jumping to specific deep pages (e.g., Page 500). Seek (Keyset) | 
WHERE id > last_id
| Excellent (O(log N)) | High-performance apps, infinite scroll, "Next Page" flows. Best Practices for StarRocks Performance: 1. Use Sort Keys: Ensure the columns you use in 
ORDER BY
are part of the table's 
ORDER BY
(Sort Key) definition. This allows StarRocks to read data in the correct physical order directly from disk. 2. Pagination before JOIN: If you are joining multiple tables, perform the pagination in a subquery on the primary table first, then join the results. This avoids doing expensive joins on rows that will eventually be discarded.
Copy code
sql
    SELECT *
    FROM (SELECT id FROM orders WHERE id > 500 ORDER BY id LIMIT 20) t1
    JOIN order_details t2 ON t1.id = t2.order_id;
3. Avoid 
LIMIT
without `ORDER BY`: In a distributed system, the return order is not guaranteed unless you explicitly define an 
ORDER BY
clause. References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • best_practices/table_clustering.md References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/json-functions/json-operators.md • knowledge/usage/best-practice.md • sql-reference/sql-functions/struct-functions/row.md • release_notes/release-2.5.md
s
@Rocky, our use cases is probably scan some activity records sorted by timestamp
r
For activity records, where you likely have high volume and frequent queries for "recent events," the Seek Method (Keyset Pagination) is the gold standard. Since activity records often have identical timestamps (e.g., multiple logs in the same millisecond), you must use a secondary unique column (like a record ID) to ensure no data is skipped or duplicated during pagination. 1. Recommended Table Design For activity records, use the Duplicate Key table model. It is optimized for append-only log data. Ensure your 
ORDER BY
(Sort Key) starts with your timestamp to make the scan extremely fast.
Copy code
sql
CREATE TABLE activity_logs (
    event_time DATETIME NOT NULL,
    user_id INT,
    event_type VARCHAR(50),
    request_id BIGINT -- A unique ID for every record
) 
ENGINE=OLAP
DUPLICATE KEY(event_time, user_id)
-- Ensure event_time is the first sort key for fast range scans
ORDER BY (event_time, request_id) 
DISTRIBUTED BY HASH(user_id);
2. The Pagination Query (Seek Method) In your application, instead of tracking a "Page Number," you track the Timestamp and Unique ID of the very last row on the current page. Page 1:
Copy code
sql
SELECT event_time, user_id, event_type, request_id
FROM activity_logs
ORDER BY event_time DESC, request_id DESC
LIMIT 50;
Page 2 (and beyond): Assuming the last row of Page 1 had 
event_time = '2023-10-27 10:00:00'
and `request_id = 998877`:
Copy code
sql
SELECT event_time, user_id, event_type, request_id
FROM activity_logs
WHERE (event_time, request_id) < ('2023-10-27 10:00:00', 998877)
ORDER BY event_time DESC, request_id DESC
LIMIT 50;
Why this is the best approach for activity records: * Performance: StarRocks uses its Sort Key index to jump directly to that specific timestamp and ID. It doesn't matter if you are on page 2 or page 2,000; the query time remains constant (milliseconds). * No Duplicate/Missing Records: If you only filtered by 
event_time < '...'
, and there were 5 records with that exact same timestamp, you might skip some or show them twice. Adding the 
request_id
to the comparison ensures a perfect "cut" between pages. * Tuple Comparison: StarRocks supports 
(col1, col2) < (val1, val2)
, which simplifies the SQL significantly compared to writing complex 
OR
logic. Summary Checklist 1. Don't use 
OFFSET
: 
LIMIT 10000, 50
will get slower as users scroll deeper. 2. Use a Tie-breaker: Always include a unique column (ID) in your 
ORDER BY
and 
WHERE
clause to handle duplicate timestamps. 3. Align with Sort Key: Ensure your table's 
ORDER BY
clause matches your query's 
ORDER BY
clause for maximum I/O efficiency. References • knowledge/usage/best-practice.md • table_design/table_types/duplicate_key_table.md • best_practices/table_clustering.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md References • table_design/table_types/duplicate_key_table.md • table_design/table_types/unique_key_table.md • table_design/table_types/table_types.md • table_design/table_types/table_capabilities.md
s
@Rocky what if the table contains many dynamic filters within range of partition days and we always need to show in sub second count distinct on one of the sub activities for uncrowned fikter
r
To achieve sub-second 
COUNT(DISTINCT)
with many dynamic filters on activity records, the standard SQL approach (
COUNT(DISTINCT id)
) will eventually fail as data grows. The best approach in StarRocks for this specific scenario is Bitmap Aggregation (using Roaring Bitmaps). This allows you to perform exact deduplication across millions of rows in milliseconds, even with ad-hoc filters. 1. The Strategy: Pre-Aggregate into Bitmaps Instead of scanning every raw row, you create an Aggregate Table (or a Synchronous Materialized View) that stores "pre-computed" sets of unique IDs (bitmaps) grouped by your common filter dimensions. Example Table Design: If you need to filter by 
activity_type
, 
platform
, and 
country
, your aggregate table would look like this:
Copy code
sql
CREATE TABLE activity_stats_daily (
    event_date DATE NOT NULL,
    activity_type VARCHAR(50),
    platform VARCHAR(20),
    country VARCHAR(20),
    -- This column stores the set of unique user IDs as a bitmap
    user_id_bitmap BITMAP BITMAP_UNION 
) 
ENGINE=OLAP
AGGREGATE KEY(event_date, activity_type, platform, country)
PARTITION BY RANGE(event_date) (...)
DISTRIBUTED BY HASH(activity_type);
2. How it works with Dynamic Filters When you run a query with filters, StarRocks doesn't look at individual users. It finds the pre-aggregated bitmaps for the matching rows and performs a highly optimized Bitwise OR (
BITMAP_UNION
) to get the final count. The Query:
Copy code
sql
SELECT BITMAP_UNION_COUNT(user_id_bitmap)
FROM activity_stats_daily
WHERE event_date >= '2023-10-01' AND event_date <= '2023-10-07'
  AND platform = 'iOS'       -- Dynamic Filter 1
  AND country = 'US';        -- Dynamic Filter 2
* Performance: Even if there are 100 million raw events, StarRocks might only need to merge a few hundred bitmaps, which is a sub-second operation. 3. Handling String IDs (Global Dictionary) Bitmaps require Integer IDs. If your sub-activity ID (like a 
user_uuid
) is a string: 1. Use a Global Dictionary: Use StarRocks' 
AUTO_INCREMENT
feature or a separate mapping table to map each string UUID to a unique 
BIGINT
. 2. Load with `to_bitmap()`: During the data ingestion (Stream Load or Routine Load), use the mapping to convert the string to the integer ID before storing it in the 
BITMAP
column. 4. What if the filter is "Un-indexed" or unpredictable? If you have filters that change constantly and aren't in your 
AGGREGATE KEY
, you have two choices: * Option A (Exact): Keep the most common 3-5 filters in the 
AGGREGATE KEY
. For other "wildcard" filters, use Bitmap Indexes on the raw table. This speeds up the filtering of rows before the count happens. * Option B (Approximate): If 99% accuracy is enough, use the HyperLogLog (HLL) type instead of 
BITMAP
. It is even faster and uses less storage, but it provides an estimate rather than a 100% exact count. Summary Checklist for Sub-Second Performance: 1. Use Aggregate Tables: Move from raw records to bitmap-based aggregation. 2. Partition Pruning: Ensure your query always includes a 
date
range filter that matches your table's partitions. 3. Sort Key: Place the most frequently used "dynamic filters" at the beginning of your 
AGGREGATE KEY
or 
ORDER BY
clause. 4. Bitmaps over Strings: Never do 
COUNT(DISTINCT string_col)
on large datasets; always map to Bitmaps or use HLL. References • using_starrocks/distinct_values/Using_bitmap.md • using_starrocks/query_acceleration_with_auto_increment.md • table_design/table_types/aggregate_key_table.md • using_starrocks/distinct_values/Using_HLL.md
• sql-reference/sql-functions/aggregate-functions/bitmap_union_count.md References • using_starrocks/distinct_values/Using_bitmap.md • using_starrocks/query_acceleration_with_auto_increment.md • table_design/table_types/aggregate_key_table.md • sql-reference/sql-functions/aggregate-functions/bitmap_union_count.md • using_starrocks/distinct_values/Using_HLL.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • knowledge/usage/best-practice.md • administration/management/Management_of_statistics.md • administration/management/Cost_based_optimizer.md • table_design/indexes/Bitmap_index.md • table_design/indexes/Bloomfilter_index.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/duplicate_key_table.md • best_practices/table_clustering.md • knowledge/usage/performance-tuning.md • faq/Others.md • integrations/BI_integrations/Superset.md • integrations/superset_support.md • sql-reference/sql-statements/account-management/EXECUTE_AS.md • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/ranger_plugin.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/json-functions/json-operators.md • sql-reference/sql-functions/struct-functions/row.md • release_notes/release-2.5.md
s
@Rocky , i getting error to create matterialyzed view on top of iceberg external table with partition column changed over time. is they any way to ignore that? it is created only from very beginning of iceberg table while we query the data from latest 90 days for mv
r
Creating a partitioned Materialized View (MV) on an Iceberg table with partition evolution (where the partition column or strategy changed over time) is currently a known limitation in StarRocks. When the analyzer detects multiple partition specs in the Iceberg metadata, it throws the error: 
Do not support create materialized view when base iceberg table has partition evolution
. Since you only need data from the latest 90 days, here is the best approach to bypass this and achieve your goal: 1. The Workaround: Non-Partitioned MV (Full Refresh) The "partition evolution" error specifically blocks partitioned MVs because StarRocks cannot safely map the evolved partitions 1:1 for incremental refreshes. If your 90-day dataset is not excessively large (e.g., under a few hundred million rows), the most reliable way is to create the MV without a 
PARTITION BY
clause. This tells StarRocks to treat it as a single unit: * How it helps: Standard queries on Iceberg do support partition evolution. By removing 
PARTITION BY
from the MV definition, you bypass the partition-mapping check. * SQL Example:
Copy code
sql
    CREATE MATERIALIZED VIEW activity_90d_mv
    REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
    AS
    SELECT *
    FROM iceberg_catalog.db.activity_logs
    -- Filter here to limit the scope and ignore historical "evolved" data
    WHERE event_time >= CURRENT_DATE() - INTERVAL 90 DAY;
2. Strategy for "Ignoring" History (Partition TTL) If you are on StarRocks v3.1.5 or later, you should use the 
partition_ttl
property. While this doesn't fix the "evolution" error for the initial creation of a partitioned MV, it is the correct way to manage a 90-day rolling window for internal tables or successful MVs. * Property: 
"partition_ttl" = "90 DAY"
* Behavior: StarRocks will automatically drop partitions older than 90 days during each refresh. 3. The "Permanent" Fix: Rewrite Iceberg Data If you absolutely must have a partitioned MV (to enable partition-level incremental refreshes), you must unify the partition spec on the Iceberg side. * Action: Use an external engine (like Spark or Flink) to run an Iceberg 
rewriteDataFiles
job. * Result: This rewrites historical files into the current partition spec. Once the table has only one active partition spec, the StarRocks error will disappear, and you can create your 
PARTITION BY
MV. 4. Alternative: Regular View If the MV remains problematic due to the Iceberg metadata, consider using a Regular View with a strict 90-day filter. StarRocks' Data Lake House engine (v3.x+) is highly optimized for Iceberg. With features like Global Dictionary (v3.5+) and I/O Caching, a regular view might provide the sub-second performance you need without the maintenance overhead of an MV. Summary of Recommendations: Requirement | Recommended Action Bypass Creation Error | Remove 
PARTITION BY
from the MV definition (use Full Refresh). Limit Data Volume | Use a 
WHERE
clause for the last 90 days in the MV SQL. Enable Partitioned MV | Rewrite historical Iceberg data to the current partition spec. Version Check | Ensure you are on v3.2.3+ for better Iceberg Partition Transform support. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • release_notes/release-3.3.md References
s
@Rocky, do starrocks has primer key table sync from external iceberg catalog every x minutes?
r
Yes, StarRocks provides two main ways to sync data from an external Iceberg catalog to a StarRocks table at regular intervals. 1. The Managed Way: Asynchronous Materialized View (Recommended) This is the most "native" way to sync an Iceberg table to StarRocks. An Asynchronous Materialized View (Async MV) acts as a local, high-performance copy of your data lake data. * How it works: StarRocks handles the scheduling and the data movement. Since v3.1.4, StarRocks can detect partition-level changes in Iceberg (V1 tables), so it only refreshes the modified partitions rather than the whole table. * Sync Logic:
Copy code
sql
    CREATE MATERIALIZED VIEW iceberg_sync_mv
    PARTITION BY event_date -- Optional: Align with Iceberg partitions
    DISTRIBUTED BY HASH(id)
    REFRESH ASYNC EVERY (INTERVAL 5 MINUTE) -- Your "X minutes" sync
    AS
    SELECT id, name, event_date
    FROM iceberg_catalog.db.activity_logs;
* Note: By default, Async MVs use the Duplicate Key table model internally. If you specifically need the Primary Key table model (for real-time upserts and deletes), see Method 2. 2. The Explicit Way: Job Scheduler + Primary Key Table If you specifically need the target table to be a Primary Key table (e.g., to handle manual deletes or joins that require PK performance), you can use the StarRocks Job Scheduler (available since v3.2). * How it works: You create a standard Primary Key table and then schedule a "Job" to pull data from Iceberg using 
INSERT INTO
. * Upsert Behavior: On a Primary Key table, 
INSERT INTO
automatically behaves as an UPSERT. If a record with the same ID exists, it is updated; otherwise, it is inserted. * Sync Logic:
Copy code
sql
    -- 1. Create your Primary Key table
    CREATE TABLE starrocks_pk_table (
        id BIGINT,
        name VARCHAR(100),
        update_time DATETIME
    ) PRIMARY KEY (id)
    DISTRIBUTED BY HASH(id);

    -- 2. Create a Job to sync every X minutes
    CREATE JOB sync_iceberg_job
    SCHEDULE EVERY (INTERVAL 10 MINUTE)
    COMMENT 'Sync from Iceberg to PK table'
    DO
      INSERT INTO starrocks_pk_table
      SELECT id, name, update_time
      FROM iceberg_catalog.db.activity_logs
      WHERE update_time > now() - interval 15 minute; -- Incremental logic
Which one should you choose? Feature | Asynchronous MV | Job Scheduler + PK Table Effort | Low (Managed by StarRocks) | Medium (Manual SQL & Logic) Table Model | Duplicate Key (managed) | Primary Key (explicit) Upsert Support | Managed Overwrite | Native PK Upsert Change Detection | Automatic (since v3.1.4) | Manual (via 
WHERE
clause) Requirement | Data acceleration/caching | High-frequency UPSERTs/Deletes Important FE Configurations To ensure StarRocks detects the latest metadata from Iceberg for these syncs, you may need to adjust these Frontend (FE) configurations: * `enable_background_refresh_connector_metadata`: Set to 
true
(default in 3.0+) to poll Iceberg metadata. * `background_refresh_metadata_interval_millis`: Default is 
600000
(10 minutes). Set this to a lower value if your sync interval (X minutes) is very short. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/cluster-management/job_scheduler/CREATE_JOB.md • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md References • release_notes/release-3.3.md
s
@Rocky but option 1 also creates physical table right?
r
Yes, Option 1 (Asynchronous Materialized View) also creates a physical table inside StarRocks. When you create an Asynchronous MV, StarRocks allocates actual storage for the pre-computed results. It is not just a "logical" shortcut; it is a managed physical table that lives in your StarRocks cluster. Here is the breakdown of how "Physical" these options are and why it matters: 1. Asynchronous MV (Managed Physical Table) * Storage: It creates a physical table (usually using the Duplicate Key model internally). You can see the storage it consumes by running 
SHOW TABLE STATUS
. * Maintenance: StarRocks manages the data lifecycle. When the sync interval "X" hits, StarRocks runs a background task to refresh the data. * Performance: Queries hit the local StarRocks disks instead of the external Iceberg storage, which is why it provides sub-second performance. * Constraint: You cannot choose the table model (like Primary Key) for an Async MV. It handles updates by overwriting changed partitions or the entire table. 2. Job + Primary Key Table (Explicit Physical Table) * Storage: You explicitly create a table using the 
PRIMARY KEY
engine. This is also a physical table. * Maintenance: You are responsible for the sync logic (the 
INSERT INTO ... SELECT
statement). * The "Upsert" Advantage: Unlike the MV, a Primary Key table supports row-level updates. If your Iceberg table has small changes across many partitions, the 
INSERT INTO
will "upsert" only the specific records based on the Primary Key. * When to use this instead of MV: Use this if your Iceberg source has frequent row-level updates/deletes and you want to keep a mirrored "latest state" in StarRocks with the highest possible query performance (Primary Key tables are 3-10x faster than other models for many lookups). Summary Comparison Feature | Async MV (Option 1) | Job + PK Table (Option 2) Physical Storage? | Yes | Yes Managed by SR? | Fully Managed | Manually Scheduled Table Engine | Managed (Duplicate/Unique) | Primary Key Sync Method | Partition-level overwrite | Row-level Upsert Custom Indexes? | Limited | Full support (Bitmap, Bloom, etc.) Conclusion: Both create physical tables. If you simply need a fast "cache" of the last 90 days and your Iceberg data is mostly append-only or partition-based, the Async MV is easier. If you need to handle real-time row updates or specific primary key lookups, use the Job + Primary Key table. References • release_notes/release-3.3.md
s
@Rocky for option 1. is it sync all data avery x minutes or taking some intervals or chunks
r
For Option 1 (Asynchronous Materialized View), the sync behavior depends on how you define the MV. It does not necessarily sync all data every time. Here is how the sync/refresh works: 1. Incremental Refresh (by Partition) If your Materialized View is partitioned (using 
PARTITION BY
), StarRocks performs an incremental refresh. * How it works: StarRocks (since v3.1.4) can detect which partitions in your Iceberg table have changed. * Sync Logic: Instead of refreshing the entire table, it only refreshes the specific partitions that have new or updated data. * Limitation: This currently requires the base Iceberg table to be a V1 table for automatic change detection. For V2 tables, it often defaults to a full refresh unless configured otherwise. 2. Batching/Chunks (
partition_refresh_number
) If you have a massive amount of data to sync (e.g., 1,000 partitions), StarRocks can process them in "chunks" (batches) rather than all at once to avoid overloading the cluster. * Property: 
"partition_refresh_number" = "n"
* Behavior: If 
n=10
, StarRocks will refresh 10 partitions at a time until the MV is up to date. This makes the sync process much more stable. 3. Limited Window (Intervals) Since you only care about the latest 90 days, you can prevent StarRocks from ever looking at older "chunks" of data: * 
WHERE
Clause: Define your MV with 
WHERE date >= CURRENT_DATE() - INTERVAL 90 DAY
. This ensures StarRocks only syncs the relevant interval. * `partition_ttl`: You can set 
"partition_ttl" = "90 DAY"
. StarRocks will then automatically delete local partitions older than 90 days, keeping the "physical table" footprint small. 4. Full Refresh (Default for Non-Partitioned MVs) If you create the MV without a 
PARTITION BY
clause (which I recommended to bypass your "partition evolution" error), StarRocks will perform a Full Refresh every X minutes. * Is it slow? It depends on the volume. If your 90-day window is small (e.g., a few GBs), a full refresh every 30 minutes is usually very fast because StarRocks uses its high-speed vectorized execution engine to pull the data. Summary of Sync Behavior: MV Type | Change Detection | Sync Volume | Chunking Support Partitioned MV | Yes (Partition-level) | Only changed partitions | Yes (via 
partition_refresh_number
) Non-Partitioned MV | No | Full 90-day window | No (Single task) Recommendation for your case: Because of your Iceberg partition evolution error, you likely have to use a Non-Partitioned MV. To keep this sync efficient: 1. Use a 
WHERE
clause to strictly limit the sync to the last 90 days. 2. Set the refresh interval (
EVERY X MINUTE
) to a value that balances data freshness with system load. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
s
@Rocky Use case: Fast paginated list on large table We have a StarRocks v4.0 shared-data cluster. We need to implement Activity page pagination over ~314M rows with sub-second response. Query pattern: -- Page 1 SELECT id, event_timestamp, thread_id, type, service, main_actor.display_name FROM sanitized_steps_pk WHERE event_timestamp >= DATE_SUB(NOW(), INTERVAL 90 DAY) AND source_pipeline = 'auditEvents' ORDER BY event_timestamp DESC, id ASC LIMIT 50; -- Page 2+ (keyset pagination) SELECT id, event_timestamp, thread_id, type, service, main_actor.display_name FROM sanitized_steps_pk WHERE event_timestamp >= DATE_SUB(NOW(), INTERVAL 90 DAY) AND source_pipeline = 'auditEvents' AND (event_timestamp < '<last_ts>' OR (event_timestamp = '<last_ts>' AND id > '<last_id>')) ORDER BY event_timestamp DESC, id ASC LIMIT 50; Table definition: PRIMARY KEY(id, event_timestamp) PARTITION BY date_trunc('day', event_timestamp) DISTRIBUTED BY HASH(id) BUCKETS 16 ORDER BY (event_timestamp, id) Problem: EXPLAIN shows 
partitions=89/89
and 
cardinality=314M
— the sort key 
ORDER BY (event_timestamp, id)
is not being used for TopN optimization. Query is very slow. Questions: 1. Does PRIMARY KEY table in v4.0 support TopN sort key optimization for`ORDER BY col DESC LIMIT N`? 2. Would DUPLICATE KEY with`DUPLICATE KEY(event_timestamp, id)`perform better for this read pattern? 3. What is the recommended table type and sort key design for fast keyset pagination on 300M+ rows?
r
Implementing sub-second pagination on 300M+ rows in StarRocks requires the Sort Key to perfectly align with your query pattern to trigger TopN Scan Optimization. Here are the answers to your specific questions and the recommended design: 1. Does PRIMARY KEY in v4.0 support TopN sort key optimization? Yes, but with a major caveat regarding Mixed Directions. StarRocks (v3.0+) Primary Key tables decouple the Primary Key from the Sort Key (defined via 
ORDER BY
in the table definition). Data is physically stored sorted by these columns. However, TopN optimization (stopping the scan early once the limit is reached) works best when the query's 
ORDER BY
matches the physical storage order. While StarRocks can perform a Reverse Scan (e.g., query 
ORDER BY col DESC
on a 
col ASC
sort key), it cannot easily optimize mixed directions like 
ORDER BY event_timestamp DESC, id ASC
. In your case, to satisfy 
id ASC
while reading 
event_timestamp
in reverse, the engine must perform a full sort of all rows matching the 
WHERE
clause (314M rows), which is why your query is slow. 2. Would DUPLICATE KEY perform better? Yes, for log-style read patterns. If your "Activity page" data is essentially append-only logs (no updates/deletes), the Duplicate Key model is strictly more efficient. It has less metadata overhead (no delete bitmaps or primary key indexes) and its TopN optimizations are the most mature. If you do need updates, the Primary Key table is the right choice, but you must fix the sort order mismatch. 3. Recommended Table Design & Keyset Pagination To achieve sub-second response on 300M+ rows, follow these steps: Step A: Align the Sort Key for Filtering Place your equality filter 
source_pipeline
at the very beginning of the sort key to enable Prefix Index pruning. * Proposed Sort Key: 
ORDER BY (source_pipeline, event_timestamp, id)
Step B: Use Uniform Sort Directions Change your pagination logic to use the same direction for both columns. This allows the storage engine to read the data in a single reverse-pass. * Query: 
ORDER BY event_timestamp DESC, id DESC
* Keyset logic: 
(event_timestamp < '<last_ts>' OR (event_timestamp = '<last_ts>' AND id < '<last_id>'))
Step C: Optimized Table Definition (Example)
Copy code
sql
CREATE TABLE sanitized_steps_pk (
    id BIGINT,
    event_timestamp DATETIME,
    source_pipeline STRING,
    ...
) PRIMARY KEY(id, event_timestamp) -- Ensure your PK includes partitioning columns
PARTITION BY date_trunc('day', event_timestamp)
DISTRIBUTED BY HASH(id) BUCKETS 16
ORDER BY (source_pipeline, event_timestamp, id); -- PHYSICAL SORT KEY
Why your 
EXPLAIN
showed 
partitions=89/89
 This is actually expected. Since you are filtering for the last 90 days (
DATE_SUB(NOW(), INTERVAL 90 DAY)
) on a table partitioned by day, StarRocks correctly identified that it needs to look into ~90 partitions. The slowness isn't the number of partitions; it's the fact that it's scanning every row inside those partitions instead of using the sort key to stop early. Summary Checklist for Sub-Second Pagination: 1. Check mixed directions: Ensure your 
ORDER BY
is either all 
ASC
or all 
DESC
. 2. Sort Key Prefix: Ensure selective equality filters (like 
source_pipeline
) are the first columns in your 
ORDER BY (...)
table definition. 3. TopN Filter: Verify that 
enable_topn_runtime_filter = true
(default in v4.0) by running 
SHOW VARIABLES LIKE '%topn%';
. 4. Row Constructor: For keyset pagination, use the row constructor syntax for better readability and performance: 
WHERE (event_timestamp, id) < ('<last_ts>', '<last_id>')
. References • knowledge/usage/best-practice.md • best_practices/table_clustering.md
s
@Rocky For 300M rows with DESC ORDER BY, is there any way to achieve sub-second TopN without storing a separate DESC-sorted copy of the data?"
r
To achieve sub-second TopN on 300M rows with a 
DESC
order without storing a separate copy, you need to understand how StarRocks handles Reverse Scans and where the "Mixed Direction" bottleneck occurs. 1. Does StarRocks support 
DESC
in the physical sort key? No. In StarRocks, the 
ORDER BY
clause in a 
CREATE TABLE
statement (the physical sort key) only defines which columns to sort by. It does not allow you to specify 
DESC
. All physical sort keys are stored in Ascending (ASC) order. 2. Can you achieve sub-second 
DESC
without a second copy? Yes, but only if all sort directions are consistent. StarRocks has a Reverse Scan optimization. If your table is physically sorted by 
(event_timestamp, id)
in ASC order, and you query:
Copy code
sql
ORDER BY event_timestamp DESC, id DESC LIMIT 50
The storage engine can read the data backwards from the end of the 90-day range. Because the query's sort order is exactly the reverse of the physical order, it can trigger TopN Scan Optimization, stopping the scan immediately after finding the first 50 rows. This is extremely fast and works on 300M+ rows in milliseconds. 3. Why your query is slow (The Mixed Direction Problem) The bottleneck in your current query is the Mixed Direction: 
ORDER BY event_timestamp DESC, id ASC
. * The data is physically stored as: 
[Time: 10:01, ID: 1], [Time: 10:01, ID: 2], [Time: 10:02, ID: 3]
* To satisfy 
Time DESC
, the engine reads backwards. _ But to satisfy 
ID ASC
_within* the same timestamp, it cannot simply read backwards. It must scan all rows for that timestamp (or the whole 90-day range if timestamps are unique) and perform a CPU-heavy sort to re-order the IDs. * Result: The 
Explain
shows a full scan of 89 partitions because the engine cannot "early exit" until it has seen all candidates to ensure the 
id ASC
requirement is met. 4. Recommended Solutions (No Table Copy) Option A: Unified Direction (Best Performance) The most common "pro-tip" for high-performance pagination is to force all sort columns to use the same direction. Most users don't care if the secondary ID sort is ASC or DESC as long as the primary Timestamp is DESC and the pagination is stable. * New Query: 
ORDER BY event_timestamp DESC, id DESC LIMIT 50
* Keyset Logic: 
WHERE (event_timestamp, id) < (last_ts, last_id)
Option B: Inverted Generated Column If you must have 
id ASC
while timestamps are 
DESC
, you can create a "Generated Column" that stores the numeric inverse of your timestamp.
Copy code
sql
ALTER TABLE sanitized_steps_pk ADD COLUMN event_timestamp_inv BIGINT 
AS (9999999999999 - unix_timestamp(event_timestamp));

-- Change physical sort key to use the inverse
ALTER TABLE sanitized_steps_pk ORDER BY (source_pipeline, event_timestamp_inv, id);
Now, sorting 
event_timestamp_inv ASC
is physically equivalent to 
event_timestamp DESC
. Since both 
event_timestamp_inv
and 
id
are now being sorted 
ASC
, the storage engine can perform a standard forward scan with early exit. Summary * Reverse Scan allows sub-second 
DESC
queries on 
ASC
physical keys. * Mixed Directions (
DESC
, 
ASC
) break this optimization and force a large-scale sort. * Solution: Change your pagination to 
DESC, DESC
or use a generated column to flip the direction of one value physically. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • best_practices/table_clustering.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/Sql_faq.md • table_design/table_types/primary_key_table.md
s
@Rocky _"We have an Iceberg table partitioned by day on 
event_timestamp
. We always query 
ORDER BY event_timestamp DESC, id DESC LIMIT 50
for pagination. The data is append-only, no updates._ Two questions: _1. For a native DUPLICATE KEY table with sort key 
(source_pipeline, event_timestamp, id)
— is there any way to achieve sub-second TopN for DESC ordering without storing a separate copy?_ 2. Can Iceberg table files be written/sorted in a specific order that helps StarRocks' external scan perform faster TopN for DESC queries?
r
To achieve sub-second TopN pagination on 300M+ rows, here are the detailed answers for your native and external table scenarios: 1. Native DUPLICATE KEY Table (Reverse Scan) Yes, you can achieve sub-second performance without a separate DESC-sorted copy. StarRocks supports Reverse Scan optimization. Even though data is physically stored in ascending order based on your 
ORDER BY (source_pipeline, event_timestamp, id)
clause, the storage engine can read these sorted blocks backwards. * How it works: When you query 
WHERE source_pipeline = 'auditEvents' ORDER BY event_timestamp DESC, id DESC LIMIT 50
, StarRocks first uses the Prefix Index to jump to the end of the range for that 
source_pipeline
. It then reads the data segments in reverse. * The "Early Exit" (TopN): Because the query's sort order (
DESC, DESC
) is the exact opposite of the physical sort order (
ASC, ASC
), the scan can stop the moment it collects 50 rows. This "Short Circuit" is what delivers sub-second latency on large datasets. * Key Requirement: Ensure your 
ORDER BY
in the query is consistent (e.g., both columns are 
DESC
). As discussed before, a mixed direction like 
event_timestamp DESC, id ASC
would break this optimization and force a slow, full-range sort. 2. Iceberg Table Optimization Yes, sorting Iceberg files helps, but with some architectural limits. StarRocks v4.0 supports the 
ORDER BY
clause in Iceberg DDL and can leverage sorted Iceberg data to accelerate scans: * File Pruning via Stats: If your Iceberg files are sorted by 
event_timestamp
, StarRocks uses the min/max statistics in the Iceberg manifests and Parquet footers to prune files. For a 
DESC
query, the optimizer will prioritize reading files where the 
max
value of 
event_timestamp
is highest. * TopN Pushdown: StarRocks can push down the 
LIMIT
to the scan level for external tables. This means it will stop reading further Iceberg files once it has collected 50 rows from the latest partitions. * Parquet Page Skipping: Within a single Parquet file, if the data is sorted, StarRocks' vectorized Parquet reader can use page-level min/max indexes to skip over millions of rows that don't match your criteria or are outside the current TopN range. The catch: Unlike native StarRocks tables, Iceberg tables have higher metadata overhead (opening many small S3/HDFS files). To make Iceberg sub-second for 300M rows: 1. Sort the Iceberg data: Ensure your ingestion pipeline (Spark/Flink) writes files globally sorted by 
(event_timestamp, id)
. 2. Enable Data Cache: Use StarRocks' Data Cache (
SET enable_scan_datacache = true
) so that the Parquet footers and frequently accessed "latest" pages are stored on local NVMe/SSD. 3. Use StarRocks v4.0+: This version includes the most advanced TopN pushdown logic for data lake catalogs. Summary Comparison Feature | Native Table | Iceberg Table DESC Performance | Ultra Fast (Reverse Scan) | Fast (Min/Max skipping) Early Exit | Stops at the block/page level | Stops at the file/page level Latency Goal | ~10-100ms | ~200-800ms (with cache) Recommendation | Best for high-concurrency UI | Best for ad-hoc or large batch Pro-Tip: For the native table, verify the optimization is working by running 
EXPLAIN ANALYZE <query>
. Look for the 
ShortKeyFilterRows
or 
PushDownTopN
indicators in the Scan node. References • table_design/table_types/duplicate_key_table.md • best_practices/table_clustering.md • sql-reference/System_variable.md • data_source/catalog/iceberg/DDL.md • release_notes/release-4.0.md References
s
@Rocky _We tested DESC, DESC on our DUPLICATE KEY table with sort key 
(source_pipeline, event_timestamp, id)
and got 2.3s — same as Iceberg. The EXPLAIN plan shows 
TOP-N
with DESC but no early exit. Here's the exact EXPLAIN:_ (paste the EXPLAIN output) The ASC, ASC query runs in 0.46s with a 
TOP-N
node and early exits correctly. Why is the reverse scan NOT firing for DESC, DESC? Is there a session variable or table property we need to enable for reverse scan optimization in v4.0 shared-data?"
r
The reason your 
DESC, DESC
query is significantly slower than the 
ASC, ASC
query—and why it isn't triggering a sub-second "early exit"—is likely due to a combination of Prefix Index truncation and how the storage engine handles Reverse Scans. 1. The 36-Byte Prefix Index Truncation In StarRocks, the Prefix Index (sparse index) is used to jump to specific data blocks. It has two critical limitations: * It is limited to the first 36 bytes of the sort key. * Once a 
VARCHAR
, 
STRING
, or 
CHAR
column appears in the sort key, it is usually the last column included in that index entry, and StarRocks often stops including subsequent columns (especially if it reaches the 36-byte limit). Your Sort Key: 
ORDER BY (source_pipeline, event_timestamp, id)
If 
source_pipeline
is a `VARCHAR`: 1. StarRocks uses the first 36 bytes of the pipeline name for the index. 2. Because a 
VARCHAR
appeared first, the 
event_timestamp
and 
id
are likely excluded from the Prefix Index entirely. 2. Why ASC is fast but DESC is slow * For `ASC, ASC`: The engine uses the Prefix Index to jump to the beginning of the range for 
source_pipeline = 'auditEvents'
. Since it's a forward scan, it simply starts reading from that point and stops as soon as it hits 50 rows. Even without the timestamp in the index, the "start of the pipeline" is enough to trigger an early exit. * For `DESC, DESC`: To perform a "Reverse Scan" with early exit, the engine must jump to the end of the 90-day range within that pipeline. Because 
event_timestamp
is not in your Prefix Index, the engine cannot seek the end of the range. It is forced to scan a much larger portion of the data (likely the entire 90-day block for that pipeline) to find the "last" 50 rows. 3. Recommended Fixes Fix A: Move the Timestamp to the Front If your queries always involve a time range, placing the 
event_timestamp
first ensures it is included in the Prefix Index.
Copy code
sql
-- New Table Sort Key
ORDER BY (event_timestamp, source_pipeline, id)
_Note: This makes filtering by 
source_pipeline
slightly less efficient for point lookups, but it makes time-based DESC pagination significantly faster._ Fix B: Use a Fixed-Length Type for the Pipeline (If possible) If 
source_pipeline
is a low-cardinality category, consider using an 
INT
(mapping names to IDs) or a very short 
CHAR(N)
. This allows the 
event_timestamp
to fit within the 36-byte prefix index limit. Fix C: Verify with Query Profile Run your 
DESC, DESC
query and check the Query Profile. Look for these indicators in the `OLAP_SCAN_NODE`: * 
ShortKeyFilterRows
: If this value is 0 or very low, it confirms the Prefix Index is not being used to prune the scan range. * 
PushDownTopN
: Ensure this is present. Is there a Session Variable? In v4.0, ensure 
enable_topn_runtime_filter
is 
true
(default). There is also a hint-related variable mentioned in recent versions:
Copy code
sql
-- This can sometimes help the optimizer decide to split ranges for DESC TopN
SET desc_hint_split_range = 4096;
However, the root cause is almost certainly the Prefix Index's inability to "seek" the end of your time range because of the leading 
VARCHAR
column. References • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md • sql-reference/System_variable.md • best_practices/table_clustering.md References • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md
• sql-reference/System_variable.md • best_practices/table_clustering.md • knowledge/usage/best-practice.md • release_notes/release-4.0.md • table_design/table_types/duplicate_key_table.md • deployment/shared_data/feature-support-shared-data.md • faq/shared_data_faq.md • administration/management/resource_management/Replica.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • best_practices/query_tuning/query_hint.md • using_starrocks/accelerate-cardinality-preserving-joins.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/Sql_faq.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/array-functions/array_top_n.md • data_source/catalog/iceberg/DDL.md • sql-reference/sql-functions/utility-functions/encode_sort_key.md • release_notes/release-3.3.md • sql-reference/sql-functions/window-functions/WINDOW.md • sql-reference/sql-statements/data-manipulation/SELECT.md • administration/Management.md • using_starrocks/Materialized_view.md • sql-reference/sql-functions/Scalar_functions.md • data_source/Catalog.md • table_design/Data_model.md • introduction/StarRocks_intro.md • quick_start/Quickstart.md • sql-reference/sql-functions/Aggregate_functions.md • administration/Monitor_and_Manage.md • data_source/External_table.md • loading/Loading_intro.md • faq/Faq.md
• sql-reference/sql-functions/Window_functions.md • using_starrocks/Query_acceleration.md • sql-reference/Sql_reference.md • table_design/Table_design.md • benchmarking/SSB_Benchmarking.md • best_practices/Best_practices.md • release_notes/Release_notes.md • deployment/Deployment_recommendation.md • using_starrocks/Caching.md • integrations/Integrations.md • data_source/Data_lake.md • unloading/Unload_overview.md • sql-reference/sql-statements/Sql_statements.md • administration/Maintenance.md • sql-reference/how_to_query/How_to_query.md • administration/Cluster_administration.md • getting_started/Introduction.md • faq/Load_faq.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md • sql-reference/sql-statements/account-management/SET_VARIABLE.md • loading/Spark_load.md • loading/RoutineLoad.md • loading/StreamLoad.md • sql-reference/sql-statements/data-manipulation/EXPLAIN.md • sql-reference/sql-statements/data-definition/ALTER_TABLE.md • loading/Etl_load.md • sql-reference/sql-statements/data-manipulation/INSERT.md • loading/BrokerLoad.md • loading/InsertInto.md • data_source/catalog/iceberg_catalog.md • data_source/catalog/hive_catalog.md • data_source/catalog/hudi_catalog.md • data_source/catalog/paimon_catalog.md • data_source/catalog/deltalake_catalog.md • data_source/catalog/jdbc_catalog.md • loading/Flink_connector_starrocks.md • data_source/catalog/unified_catalog.md • data_source/catalog/catalog_intro.md
• loading/StarRocks_connector_for_apache_spark.md • data_source/catalog/multi_catalog_overview.md • loading/StarRocks_connector_for_apache_kafka.md • data_source/catalog/elasticsearch_catalog.md • table_design/table_types/aggregate_table.md • table_design/table_types/unique_key_table.md • sql-reference/sql-functions/bitmap-functions/bitmap_hash64.md • sql-reference/sql-functions/aggregate-functions/bool_or.md • sql-reference/sql-functions/string-functions/strpos.md • sql-reference/sql-functions/date-time-functions/to_datetime.md • sql-reference/sql-functions/string-functions/regexp_count.md • sql-reference/sql-functions/string-functions/tokenize.md • sql-reference/sql-functions/utility-functions/format_bytes.md • sql-reference/sql-functions/utility-functions/column_size.md • sql-reference/sql-statements/data-definition/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/data-definition/ALTER_MATERIALIZED_VIEW.md • using_starrocks/caching/query_cache.md • data_source/data_cache.md • sql-reference/sql-functions/array-functions/array_agg.md • table_design/indexes/Bitmap_index.md • table_design/indexes/Bloom_filter_index.md • sql-reference/sql-functions/string-functions/str_to_date.md • sql-reference/sql-functions/aggregate-functions/count.md • sql-reference/sql-functions/aggregate-functions/sum.md • sql-reference/sql-functions/aggregate-functions/avg.md • administration/management/resource_management/Vacuum.md • administration/management/resource_management/Compaction.md • loading/Loading_overview.md
The reason your 
DESC, DESC
query is significantly slower than 
ASC, ASC
is that while StarRocks v4.0 is capable of "Reverse Scans," this optimization often struggles when the sort key prefix contains a constant equality filter (like your 
source_pipeline = 'auditEvents'
) in certain engine versions or configurations. In your 
ASC, ASC
query, the engine uses the prefix index to jump to the start of the 
auditEvents
range and reads forward. Since it hits the 50-row limit almost immediately, it exits. In the 
DESC, DESC
case, if the Reverse Scan logic does not trigger correctly, the engine defaults to scanning the entire 90-day range forward, sorting it all in memory, and then picking the last 50 rows. To achieve sub-second latency for 
DESC, DESC
, please try the following steps: 1. Verify "TopN Runtime Filter" and "Split Range" StarRocks uses a "Reverse Scan Range" optimization for TopN. There is a specific internal parameter 
desc_hint_split_range
(introduced/improved in v4.0.x/v3.5.x) that controls how the engine splits and reverses scan ranges for descending queries. Ensure these variables are enabled in your session:
Copy code
sql
-- Ensure TopN runtime filters are active (usually default true)
SET enable_topn_runtime_filter = true;

-- This forces the optimizer to consider reversing the scan ranges for DESC queries
-- If this is set too low or to 0, reverse scan might be disabled. 
-- Try setting it to a large value or leaving it at default.
-- Note: This is sometimes a FE config, but check if your version supports it as a session variable.
2. Check the Sort Key order (The "Prefix" Trap) Your current sort key is 
(source_pipeline, event_timestamp, id)
. While StarRocks can technically reverse-scan a suffix of a sort key when the prefix is a constant, it is much more efficient if the Timestamp is the very first column, especially for time-partitioned tables. Recommendation: If the 
source_pipeline
filter is always present but the 
event_timestamp
filter is what primarily limits the data volume, consider moving the timestamp to the front of the Sort Key. * New Sort Key: 
ORDER BY (event_timestamp, source_pipeline, id)
* This allows the engine to use the Prefix Index to jump directly to the end of the timestamp range and read backward across all pipelines, or use the Partition Pruning more effectively. 3. The "Inverted Column" Workaround (Guaranteed Sub-Second) If you cannot get the engine to reverse-scan the 300M rows reliably, the most robust "production-grade" solution used by high-scale StarRocks users is to avoid DESC entirely by using an inverted column. 1. Add a Generated Column that stores a "Negative" or "Inverted" version of your timestamp:
Copy code
sql
    ALTER TABLE sanitized_steps_pk ADD COLUMN event_ts_inv BIGINT
    AS (9999999999 - unix_timestamp(event_timestamp));
2. Update your Sort Key to include this column:
Copy code
sql
    ALTER TABLE sanitized_steps_pk ORDER BY (source_pipeline, event_ts_inv, id);
3. Change your query to 
ORDER BY event_ts_inv ASC, id ASC
. * Since the query is now 
ASC, ASC
, it will use the same optimization that gave you 0.46s latency, but it will return the latest records first. Summary of why Reverse Scan might be failing: * Shared-Data Metadata: In shared-data mode, the FE must explicitly order the scan ranges (rowsets in S3) in reverse order for the BE to pick them up correctly. If the FE doesn't do this, the BEs will start reading the oldest rowsets first. * Segment Pruning: TopN optimization relies on the Scan node knowing it can stop. If the 
EXPLAIN
shows a 
TOP-N
node but the Scan node is still returning millions of rows (check 
ScanRows
in the Profile), then the TopN Pushdown is not reaching the storage layer.
Immediate Fix: Try setting 
SET enable_tablet_internal_parallel = true;
and verify if the 
ScanRows
in your query profile decreases when running the 
DESC
version. If the DESC` query. If it still scans 314M rows, the Inverted Column approach is your best path to sub-second UI performance. References • sql-reference/System_variable.md • best_practices/query_tuning/query_hint.md
s
@Rocky _We identified the root cause of our slow DESC pagination. Our PRIMARY KEY table has 
persistent_index_type = CLOUD_NATIVE
which stores the index in S3, adding overhead to every scan and preventing the reverse scan early exit._ _We want to switch to a DUPLICATE KEY table with sort key 
(event_timestamp, id)
for the Activity page pagination use case. The data is append-only — synced from Iceberg, no updates or deletes needed._ Questions: 1. Is DUPLICATE KEY the right choice for append-only data with fast DESC pagination in shared-data mode? 2. Will the reverse scan TopN optimization work correctly on a DUPLICATE KEY table in shared-data v4.0? 3. Any other properties we should set for optimal DESC scan performance?
r
Yes, switching to a DUPLICATE KEY table is the recommended path for your use case. In StarRocks v4.0 (Shared-data mode), this architecture provides the most efficient "short-circuit" path for large-scale pagination on append-only data. Here are the answers to your specific questions: 1. Is DUPLICATE KEY the right choice? Yes. The Duplicate Key table is the optimal model for append-only logs or activity streams. * No Index Overhead: Unlike the Primary Key model, it does not maintain a persistent index (Cloud Native or otherwise) to check for row uniqueness. This eliminates the "Persistent Index" I/O bottleneck you experienced. * Pure Sorting: It relies entirely on the 
ORDER BY
sort key for data organization. For 300M rows, this means data is physically clustered on S3 in a way that allows for extremely efficient range pruning and Top-N seeks. 2. Does Reverse Scan work in v4.0 Shared-data? Yes, it is a built-in optimization. In StarRocks v4.0, when you have a sort key defined as 
(event_timestamp, id)
and you query 
ORDER BY event_timestamp DESC, id DESC LIMIT 50
, the engine uses a Reverse Scan Range optimization. * The Mechanism: Instead of scanning from the beginning of the table, the FE (Frontend) identifies the "end" of the relevant data ranges and instructs the BE/CN nodes to read the segments in reverse order. * Early Exit: Once the scan nodes collect the required 50 rows, they immediately stop reading further data from S3. This "TopN Pushdown" is what allows for sub-second responses even on hundreds of millions of rows. 3. Optimal Properties for DESC Performance To ensure you hit sub-second latencies consistently in v4.0 Shared-data mode, verify or set the following: * 
desc_hint_split_range
(Session Variable): Ensure this is set to its default (usually 
100
or higher). This variable controls how StarRocks splits and reverses scan ranges for 
DESC
queries. If set to 
0
, the reverse scan optimization is disabled.
Copy code
sql
    SET desc_hint_split_range = 100;
* Enable TopN Runtime Filter: This allows the TopN limit to be pushed all the way down to the scan layer.
Copy code
sql
    SET enable_topn_runtime_filter = true;
* Data Cache (Crucial for S3): In shared-data mode, ensure your CN nodes have sufficient local SSD space and that 
enable_scan_datacache
is true. This ensures that the Parquet footers and the "latest" data pages (which you are likely querying via DESC) are stored locally for millisecond access. * Random Bucketing: For Duplicate Key tables in v4.0, use Random Bucketing (
DISTRIBUTED BY RANDOM
). This prevents "data skew" that can happen with Hash bucketing, ensuring the 50 rows are distributed evenly across your compute nodes during the final merge. Recommended Table DDL
Copy code
sql
CREATE TABLE activity_logs (
    event_timestamp DATETIME NOT NULL,
    id BIGINT NOT NULL,
    source_pipeline VARCHAR(64),
    payload JSON
) 
DUPLICATE KEY(event_timestamp, id) -- Historical syntax
ORDER BY (event_timestamp, id)     -- Modern v3.3+ sort key definition
PARTITION BY RANGE(event_timestamp) (...)
DISTRIBUTED BY RANDOM BUCKETS 16;
Note on "Mixed" Orders: If your UI ever requires 
ORDER BY event_timestamp DESC, id ASC
, the reverse scan will not fire because the directions are mixed. For that specific case, you would still need the "Inverted Column" workaround mentioned previously. As described previously. References • knowledge/admin_and_operation/shared_data_compaction.md
Open in Slack
PreviousNext
Powered by