http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> What does [42000][1064] Build Exec ...
# questions-and-troubleshooting
m
@Rocky What does [42000][1064] Build Exec OlapScanNode fail, scan info is invalid mean I'm trying to query my db. All queries return this error. It previously worked fine
r
The error 
[42000][1064] Build Exec OlapScanNode fail, scan info is invalid
 typically occurs when the StarRocks Frontend (FE) cannot find a healthy or available replica (tablet) to satisfy your query. Since you mentioned that all queries are failing after working fine previously, it is highly likely a cluster-level issue rather than a problem with a specific table. Here are the most common causes and the steps to fix them: 1. Check if Backend (BE) nodes are down If your BE nodes are offline or disconnected from the FE, the FE cannot build a scan plan. * Action: Run the following command in your MySQL client:
Copy code
sql
    SHOW BACKENDS;
* What to look for: Check the 
Alive
column. If it says 
false
, the BE is down. Restart your BE processes. Also, ensure the 
HeartbeatFlags
and 
SystemDecommissioned
columns don't indicate issues. 2. Check for Unhealthy Tablets If the nodes are up, the data itself might have "unhealthy" replicas (e.g., due to disk failure or a sudden crash). * Action: Check the overall health of the cluster's tablets:
Copy code
sql
    SHOW PROC '/statistic';
* What to look for: Look at the 
UnhealthyTabletNum
column. If it's greater than 0, some data partitions are missing replicas. * Follow-up: To see exactly which tablets are problematic, you can run:
Copy code
sql
    ADMIN SHOW REPLICA STATUS FROM <table_name>;
3. Check for IP/Host Changes If you recently restarted your infrastructure (especially in Kubernetes or cloud environments) and the IP addresses of the BE nodes changed, the FE might still be trying to reach the old IPs. * Action: Verify the 
IP
column in 
SHOW BACKENDS;
matches the actual current IPs of your BE machines. 4. Check FE/BE Logs for "No queryable replica" The error message in the SQL client is often a generic wrapper. The "real" reason is usually logged in the FE leader's 
fe.log
 or 
fe.warn.log
. * Action: Search the FE log for the keywords 
OlapScanNode
or 
no queryable replica
. * Common log message: 
Failed to get scan range, no queryable replica found in tablet XXXX
. 5. Check System Resources (Disk Space) If a BE node's disk is full (usually > 95% by default), StarRocks may mark that node as "available for query" but "not available for new data/compaction," which can sometimes lead to tablet inconsistency or replicas being marked as stale. * Action: Check the 
DiskStat
in 
SHOW BACKENDS;
to ensure disks aren't at 
UsedCapacity
100%. Summary Checklist: 1. Run 
SHOW BACKENDS;
— Are they all 
Alive: true
? 2. Run 
SHOW PROC '/statistic';
— Is 
UnhealthyTabletNum
zero? 3. Restart BEs if they are down. 4. If the cluster is healthy but queries still fail, try running 
DESC <table_name>;
to see if the metadata is still accessible. If you find that 
SHOW BACKENDS
shows all nodes are 
Alive
but you still get this error, please check the FE log specifically for the timestamp when you ran the query; it will provide the specific tablet ID that is causing the "invalid scan info." References • faq/Sql_faq.md • release_notes/release-2.5.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky I have 8 r6in.4xlarge nodes with 4 EBS volumes each - gp3 with 500 throughput 5000 IOPS. 750GB per EBS. Cluster CPU busy utilization mostly below 60% factoring spikes. Compaction score generally stays below 175. Average number of versions per tablet also below 100. However, my p95 transaction publish latency is 10s. My traffic is 50k QPS, each application node performs it's own batching at 500 MB data per batch. I synchronize upserts at the top of every minute. They are ~11k - 12k QPS, so roughly 660k to 720k sent to the cluster every minute. I have 8 BE nodes currently with replication_num to set 1 only. Is my capacity planning correct? I'm not sure if I'm underprovisioning. Because I know if I increase replication to 2, my cluster is not able to handle it. What would your suggested capacity planning be for my situation?
r
The error 
Build Exec OlapScanNode fail, scan info is invalid
 combined with your 10s transaction publish latency indicates that your Frontend (FE) is failing to find a "queryable" replica for your tablets. In StarRocks, a replica is only queryable if its version matches the version expected by the FE's metadata. With your current setup, you are hitting a fundamental architectural bottleneck related to your transaction frequency. 1. The Root Cause: Transaction Frequency (TPS) You mentioned 11k - 12k QPS for upserts. If each of these "queries" is an individual transaction (e.g., individual 
INSERT
or 
Stream Load
calls), you are attempting to commit 11,000+ transactions per second. * The Bottleneck: StarRocks' FE Leader processes 
publish_version
tasks for transactions. This process is largely serial and involves updating the global metadata. * The Limit: A typical StarRocks FE can handle roughly 100 to 500 transaction publishes per second. At 11,000 TPS, the FE's transaction queue is backed up, leading to the 10s p95 latency. * Why Queries Fail: Because the publishing is so delayed, the FE often tries to plan a query against a version that hasn't been fully "published" or "acknowledged" by the BE yet. Since you only have 
replication_num = 1
, there is no other replica to failover to. If that single replica is stuck in a "publishing" state or misses a version heartbeat, the FE marks it as invalid, and the query fails immediately. 2. Capacity Planning Analysis Your hardware (8 nodes of 
r6in.4xlarge
) is actually quite powerful (128 vCPUs total), but it is being misapplied: * Underprovisioned FE, not BE: Your BE nodes (CPU < 60%) are likely fine. The bottleneck is the FE Leader's CPU and Metadata Lock contention. * Replication Risk: Running 
replication_num = 1
in production is extremely dangerous. Any single BE restart or minor disk glitch will cause total data unavailability for those tablets. You cannot handle 
replication_num = 2
right now because doubling the replicas also doubles the work the FE must do to coordinate the transaction publish (it must wait for more ACKs), which your already-overloaded FE cannot handle. * Disk Performance: Your gp3 volumes (500 MB/s, 5000 IOPS) are healthy, but at 11k TPS, the metadata updates (small random writes to the segment files and primary key indexes) are likely saturating the IOPS. 3. Suggested Improvements A. Drastically Reduce Transaction Count (Mandatory) The most critical fix is to batch more aggressively. * Target: Aim for < 100 transactions per second across the whole cluster. * Action: If you are using 500MB batches, your QPS should be very low (e.g., 1 batch every few seconds per node). If you still see 11k QPS, it means your application is likely sending many small requests or your "batching" isn't actually reducing the number of 
Stream Load
/ 
Transaction
calls. * Tool: Use the StarRocks Go/Java Stream Loader which handles micro-batching automatically. B. Optimize FE Configurations If you are on StarRocks v3.2 or higher, you can reduce metadata lock contention:
Copy code
sql
-- Increase transaction lock shards (requires FE restart)
-- Set this in fe.conf
txn_shard_size = 1024
C. Address Compaction Score You mentioned a compaction score of 175. * Warning: StarRocks begins to throttle/delay commits once the compaction score exceeds 100. This is contributing significantly to your 10s latency. * Fix: Increase BE compaction threads to clear the backlog faster.
Copy code
sql
    -- In be.conf
    update_compaction_num_threads_per_disk = 2
    update_compaction_per_tablet_min_interval_seconds = 60
D. Suggested Cluster Strategy 1. Batching: Reduce write TPS to <100 by increasing rows per batch. 2. Replication: Once TPS is down, set 
replication_num = 3
. This is the production standard.
3. FE Resources: Ensure your FE nodes have at least 16-32 cores and are not sharing resources with BEs. Summary: You aren't necessarily underprovisioned on raw hardware; you are "transaction-heavy." By moving from 11,000 small transactions to 100 large transactions, your latency will drop from 10s to <500ms, and the 
OlapScanNode
errors will disappear. References • knowledge/usage/load/shared_data_batch_ingestion.md • administration/management/resource_management/Replica.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • knowledge/usage/best-practice.md
m
@Rocky oh l0_max_mem_usage is per tablet, not node. I have ~1000 tablets per BE node. Is this a concern? I set l0_max_mem_usage to 1073741824
r
Yes, 
l0_max_mem_usage
is per tablet. However, StarRocks has a "safety net" to prevent your BE from crashing, which is why your nodes are likely still running despite the math seemingly adding up to 1TB of memory usage. Here is the breakdown of your situation and why your current configuration is risky: 1. The Memory Math You have 1000 tablets per BE and you set 
l0_max_mem_usage = 1GB
. * Theoretical Demand: 1000 tablets * 1 GB = 1,000 GB (1 TB). * The Global Cap: StarRocks enforces a global limit for Primary Key indexes via the 
update_memory_limit_percent
parameter (default is 60% of the BE's total memory limit). * Your Node: With 128GB RAM, your BE process limit is ~115GB. The Primary Key index cap is ~69GB. The Concern: By setting the per-tablet limit to 1GB, you are essentially telling StarRocks that any single tablet can take up a massive chunk of that 69GB pool. When the 69GB pool fills up, StarRocks will start evicting index pages from memory to disk (if persistent index is enabled) or failing loads. This constant eviction/loading under your 11k-12k QPS load is likely causing massive I/O spikes and CPU contention, contributing to your 10s publish latency. 2. Is 1000 Tablets a Concern? For a node with 128GB of RAM, 1000 tablets is reasonable for general StarRocks use, but it is high for Primary Key tables with high-frequency updates. * Metadata Overhead: Each tablet has its own metadata and version tracking. With 1000 tablets, the FE and BE have to coordinate "Publish Version" tasks for 1000 different entities. * Compaction Pressure: At 11k-12k TPS, you are creating thousands of "rowsets" (small files) every minute across 1000 tablets. Even if your compaction score is 175 (which is actually a bit high—StarRocks prefers <100), the BE is working overtime just to keep up with the metadata of these small files. 3. Why you have 10s Publish Latency Your latency is not a "capacity" problem in terms of RAM/CPU; it is a Transaction Management problem: 1. FE Queueing: The Frontend (FE) Leader must serialized the "Publish Version" for every single transaction. Handling 11,000+ transactions per second is physically impossible for the current FE architecture (which typically caps at 100-500 TPS). 2. Version Exhaustion: With 
replication_num = 1
, you have zero margin for error. If the BE is slightly slow in writing the index for one of those 1000 tablets because of the high 
l0_max_mem_usage
causing memory pressure, the FE will time out. 3. The Result: The FE fails to "build the scan plan" because the only replica you have is "not queryable" (it's still stuck in the previous transaction's version). Suggested Actions: 1. Lower `l0_max_mem_usage`: Change it back to the default 
104857600
(100MB) or 
268435456
(256MB). Letting a single tablet grab 1GB of index memory is unnecessary if you have 1000 tablets; it only starves the other 999 tablets. 2. Aggressive Batching (Crucial): You must reduce your Upsert QPS from 11,000 to under 100. * Instead of 11,000 small requests, send 10-20 large 
Stream Load
requests per second. * This will instantly drop your publish latency from 10s to <200ms. 3. Increase Replication: Once your TPS is under control, you must move to 
replication_num = 3
. A single-replica cluster is not production-ready; a single slow disk or a BE restart will take your entire application down. 4. Monitor Update Memory: Check your BE's memory health:
Copy code
bash
    curl http://<BE_IP>:8040/mem_tracker?type=update
If the "Primary Key Index" is consistently hitting the 60% cap, you need more nodes (horizontal scaling) to spread the tablets out, rather than increasing the per-tablet limit. References • administration/management/BE_configuration.md • sql-reference/System_variable.md
m
@Rocky
Copy code
2026-04-13 21:41:52.407Z ERROR (TableKeeper|132) [TableKeeper.run():76] error happens in Keeper: com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
java.lang.RuntimeException: com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
	at com.starrocks.qe.SimpleExecutor.executeDDL(SimpleExecutor.java:151) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper.createTable(TableKeeper.java:96) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper.run(TableKeeper.java:68) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper$TableKeeperDaemon.runAfterCatalogReady(TableKeeper.java:222) ~[starrocks-fe.jar:?]
	at com.starrocks.common.util.FrontendDaemon.runOneCycle(FrontendDaemon.java:78) ~[starrocks-fe.jar:?]
	at com.starrocks.common.util.Daemon.run(Daemon.java:98) ~[starrocks-fe.jar:?]
Caused by: com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
	at com.starrocks.system.SystemInfoService.checkClusterCapacity(SystemInfoService.java:1309) ~[starrocks-fe.jar:?]
	at com.starrocks.server.LocalMetastore.createTable(LocalMetastore.java:933) ~[starrocks-fe.jar:?]
	at com.starrocks.server.LocalMetastore.createTable(LocalMetastore.java:888) ~[starrocks-fe.jar:?]
	at com.starrocks.server.MetadataMgr.createTable(MetadataMgr.java:303) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.lambda$visitCreateTableStatement$4(DDLStmtExecutor.java:307) ~[starrocks-fe.jar:?]
	at com.starrocks.common.ErrorReport.wrapWithRuntimeException(ErrorReport.java:118) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.visitCreateTableStatement(DDLStmtExecutor.java:306) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.visitCreateTableStatement(DDLStmtExecutor.java:212) ~[starrocks-fe.jar:?]
	at com.starrocks.sql.ast.CreateTableStmt.accept(CreateTableStmt.java:344) ~[starrocks-fe.jar:?]
	at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:107) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor.execute(DDLStmtExecutor.java:197) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.SimpleExecutor.executeDDL(SimpleExecutor.java:146) ~[starrocks-fe.jar:?]
	... 5 more
2026-04-13 21:41:52.407Z ERROR (TableKeeper|132) [SimpleExecutor.executeDDL():150] RepoExecutorexecute DDL error: CREATE TABLE predicate_columns( fe_id STRING NOT NULL,db_id BIGINT NOT NULL,table_id BIGINT NOT NULL,column_id BIGINT NOT NULL,usage STRING NOT NULL,last_used DATETIME NOT NULL,created DATETIME DEFAULT CURRENT_TIMESTAMP) PRIMARY KEY(fe_id, db_id, table_id, column_id) DISTRIBUTED BY HASH(fe_id, db_id, table_id, column_id) BUCKETS 8
PROPERTIES('replication_num'='1')
com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
	at com.starrocks.system.SystemInfoService.checkClusterCapacity(SystemInfoService.java:1309) ~[starrocks-fe.jar:?]
	at com.starrocks.server.LocalMetastore.createTable(LocalMetastore.java:933) ~[starrocks-fe.jar:?]
	at com.starrocks.server.LocalMetastore.createTable(LocalMetastore.java:888) ~[starrocks-fe.jar:?]
	at com.starrocks.server.MetadataMgr.createTable(MetadataMgr.java:303) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.lambda$visitCreateTableStatement$4(DDLStmtExecutor.java:307) ~[starrocks-fe.jar:?]
	at com.starrocks.common.ErrorReport.wrapWithRuntimeException(ErrorReport.java:118) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.visitCreateTableStatement(DDLStmtExecutor.java:306) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.visitCreateTableStatement(DDLStmtExecutor.java:212) ~[starrocks-fe.jar:?]
	at com.starrocks.sql.ast.CreateTableStmt.accept(CreateTableStmt.java:344) ~[starrocks-fe.jar:?]
	at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:107) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor.execute(DDLStmtExecutor.java:197) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.SimpleExecutor.executeDDL(SimpleExecutor.java:146) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper.createTable(TableKeeper.java:96) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper.run(TableKeeper.java:68) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper$TableKeeperDaemon.runAfterCatalogReady(TableKeeper.java:222) ~[starrocks-fe.jar:?]
	at com.starrocks.common.util.FrontendDaemon.runOneCycle(FrontendDaemon.java:78) ~[starrocks-fe.jar:?]
	at com.starrocks.common.util.Daemon.run(Daemon.java:98) ~[starrocks-fe.jar:?]
2026-04-13 21:41:52.408Z ERROR (TableKeeper|132) [TableKeeper.run():76] error happens in Keeper: com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
java.lang.RuntimeException: com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
	at com.starrocks.qe.SimpleExecutor.executeDDL(SimpleExecutor.java:151) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper.createTable(TableKeeper.java:96) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper.run(TableKeeper.java:68) ~[starrocks-fe.jar:?]
	at com.starrocks.scheduler.history.TableKeeper$TableKeeperDaemon.runAfterCatalogReady(TableKeeper.java:222) ~[starrocks-fe.jar:?]
	at com.starrocks.common.util.FrontendDaemon.runOneCycle(FrontendDaemon.java:78) ~[starrocks-fe.jar:?]
	at com.starrocks.common.util.Daemon.run(Daemon.java:98) ~[starrocks-fe.jar:?]
Caused by: com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Cluster has no available capacity.
	at com.starrocks.system.SystemInfoService.checkClusterCapacity(SystemInfoService.java:1309) ~[starrocks-fe.jar:?]
	at com.starrocks.server.LocalMetastore.createTable(LocalMetastore.java:933) ~[starrocks-fe.jar:?]
	at com.starrocks.server.LocalMetastore.createTable(LocalMetastore.java:888) ~[starrocks-fe.jar:?]
	at com.starrocks.server.MetadataMgr.createTable(MetadataMgr.java:303) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.lambda$visitCreateTableStatement$4(DDLStmtExecutor.java:307) ~[starrocks-fe.jar:?]
	at com.starrocks.common.ErrorReport.wrapWithRuntimeException(ErrorReport.java:118) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.visitCreateTableStatement(DDLStmtExecutor.java:306) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor$StmtExecutorVisitor.visitCreateTableStatement(DDLStmtExecutor.java:212) ~[starrocks-fe.jar:?]
	at com.starrocks.sql.ast.CreateTableStmt.accept(CreateTableStmt.java:344) ~[starrocks-fe.jar:?]
	at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:107) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.DDLStmtExecutor.execute(DDLStmtExecutor.java:197) ~[starrocks-fe.jar:?]
	at com.starrocks.qe.SimpleExecutor.executeDDL(SimpleExecutor.java:146) ~[starrocks-fe.jar:?]
	... 5 more
These are in my fe.log. Is it an issue? I only have 1 leader FE node in my cluster
r
The error 
Cluster has no available capacity
 in your 
fe.log
is the root cause of both your DDL failures and your query failures (
Build Exec OlapScanNode fail
). In StarRocks, this specific error is triggered when the Frontend (FE) determines that it cannot allocate any new data (tablets) to the Backend (BE) nodes. Since this is also blocking internal system tables (like 
_statistics_.predicate_columns
via the 
TableKeeper
), your cluster is currently in a "Locked/Read-Only" safety state. Here is why this is happening and how to fix it: 1. Most Likely Cause: Disk "Flood Stage" StarRocks has a safety threshold called the Storage Flood Stage (default is 95%). If the disk usage on your BE nodes exceeds this limit, the FE will refuse to create any new tables or even write new data to existing ones. * Check: Run 
SHOW BACKENDS\G
in your MySQL client. * Look for: The 
DiskStat
and 
Alive
columns. If 
UsedCapacity
is near 95% on all nodes, or if 
Alive
is 
false
, the cluster enters this state. * Solution: Clean up old data, drop unused tables, or increase the size of your EBS volumes. If you must bypass this temporarily to delete data, you can increase the threshold (not recommended for long-term):
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("storage_flood_stage_usage_percent" = "98");
2. Backend (BE) Connectivity / Health If all your BE nodes are marked as 
Alive: false
in 
SHOW BACKENDS
, the FE will report "no available capacity" because there are zero nodes available to host data. * Given your 11k QPS upsert volume, it is very possible the BEs have crashed or are so overwhelmed by "Transaction Publish" tasks that they are failing heartbeats to the FE. * Check BE Logs: Look at 
<http://be.INFO|be.INFO>
or 
be.warn.log
on the nodes for "OOM" (Out of Memory) or "Heartbeat timeout" messages. 3. The 
TableKeeper
and 
predicate_columns
 The specific log you shared shows the FE trying to create an internal system table (
_statistics_.predicate_columns
). This table is used for the Cost-Based Optimizer (CBO) to track which columns are used in filters. * Because your cluster reports "no capacity," the FE can't even initialize its own internal statistics tables. * This confirms the issue is cluster-wide and not specific to your application tables. 4. Impact of 
replication_num = 1
 Because you are running with no redundancy, you have no "Available Capacity" if even a subset of disks are full or a few nodes are down. In a 
replication_num = 3
cluster, the FE can sometimes find space on other nodes; with 
1
, if the specific target node is full or down, the operation fails immediately. Recommended Troubleshooting Steps: 1. Check Backend Status:
Copy code
sql
    SHOW BACKENDS;
If any are 
Alive: false
, restart them. Check the 
ErrMsg
column in that output. 2. Verify Disk Space: Ensure none of your 4 EBS volumes per node are hitting the 95% mark. 3. Address the Transaction Load: As mentioned previously, 11,000 transactions per second is likely killing your FE Leader. This "No Capacity" error can sometimes be a side effect of the FE metadata being so lagged that it cannot accurately calculate cluster state. * Action: Stop your ingestion immediately. * Wait 5-10 minutes for the 
Publish Version
queue to clear. * Check if 
SHOW BACKENDS
returns to a healthy state. 4. Check FE Metadata Disk: Ensure the disk hosting your 
meta_dir
(where the FE lives) has at least 5-10GB of free space. If the FE metadata disk is 100% full, the FE will stop accepting any DDL/DML. Summary: Your cluster thinks it is full or the BEs are dead. Check 
SHOW BACKENDS
first to see if it's a disk space issue (95%+) or a node health issue (
Alive: false
). References • administration/management/logs.md
m
@Rocky Does primary key table support any kind of indexes aside from order keys? Since they are limited to 4 order keys it's impossible to create enough indexes for my use case. Like there is a field called campaign_id. If I want to group by campaign_id for past 24 hours, there is no optimization for query efficiency? Just perform full table scan?
r
Yes, Primary Key tables in StarRocks absolutely support secondary indexes beyond just the order (sort) keys. There is a common misconception about the "4 columns" or "36 bytes" limit. Here is the breakdown of how you can optimize your 
campaign_id
and 
timestamp
queries without doing a full table scan. 1. The "4 Order Key" Misconception In StarRocks, you can include many columns in your 
ORDER BY
clause. However, StarRocks automatically builds a Prefix Index using only the first few columns (up to 36 bytes or roughly 3-5 columns depending on data types). * The Limit: The 36-byte limit is only for the "Prefix Index" (the fastest internal lookup). * The Strategy: You should put the column you use for range filters (like 
timestamp
) first in the 
ORDER BY
clause. This allows StarRocks to quickly skip entire blocks of data that aren't within your "past 24 hours" window. 2. Supported Secondary Indexes for PK Tables You can manually add several types of indexes to your 
campaign_id
column to avoid full table scans: * Bloom Filter Index: * Best for: High cardinality columns (like 
campaign_id
or 
user_id
) used in equality (
=
) or 
IN
filters. * Usage: 
ALTER TABLE your_table SET ("bloom_filter_columns" = "campaign_id");
* Effect: StarRocks will check the Bloom Filter and completely skip data files that definitely don't contain that 
campaign_id
. * Bitmap Index: * Best for: Low-to-medium cardinality columns (e.g., 
status
, 
country
, or 
campaign_type
). * Usage: 
CREATE INDEX index_name ON your_table (column_name) USING BITMAP;
* Effect: Very fast filtering for multi-column combinations. * Inverted Index (Available in newer versions): * Best for: Text search or very complex filtering logic. 3. Automatic Optimization: ZoneMaps Even if you don't create any manual indexes, StarRocks maintains ZoneMaps (Min/Max values) for every single column in every data page (64KB chunks). * If your data is even slightly correlated with your sort order (e.g., campaigns created around the same time are stored near each other), StarRocks will use these Min/Max values to skip pages that don't contain your 
campaign_id
. 4. Suggested Strategy for Your Use Case To optimize 
GROUP BY campaign_id
for the past 24 hours: 1. Sort Key (Order By): Put your time column first.
Copy code
sql
    -- Example Table Definition
    CREATE TABLE ads_data (
        event_id BIGINT,
        timestamp DATETIME,
        campaign_id INT,
        ...
    ) PRIMARY KEY (event_id)
    ORDER BY (timestamp, campaign_id); -- Time first for range filtering
2. Add a Bloom Filter on `campaign_id`:
Copy code
sql
    ALTER TABLE ads_data SET ("bloom_filter_columns" = "campaign_id");
3. Query Execution: When you run `WHERE timestamp > now() - interval 1 day AND campaign_id = 123`: • Step 1: The Prefix Index uses 
timestamp
to jump directly to the data from the last 24 hours. • Step 2: The Bloom Filter or ZoneMaps on 
campaign_id
quickly discards blocks within that 24-hour window that don't belong to campaign 
123
. • Step 3: Only the relevant rows are read into memory. Summary You are not limited to 4 indexes. You can have: 1. One Prefix Index (automatically from the first ~36 bytes of your 
ORDER BY
). 2. ZoneMaps on every single column (automatic). 3. Bloom Filters on as many columns as you need (manual). 4. Bitmap Indexes on as many columns as you need (manual). For your 
campaign_id
grouping, the combination of 
timestamp
as the first sort key and a Bloom Filter on 
campaign_id
will provide near-instant performance. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • faq/Sql_faq.md
m
@Rocky What is the price to pay for bloom filter indexes? It guarantees all campaign_id with 123 is scanned right? Just some false positives like other campaigns not 123 still scanned but that's okay since perform equality filter on scan result. And then for low cardinality like 300-400, better to use bitmap indexes? like network_id
r
The "price to pay" for a Bloom Filter index is very low compared to the performance gains, but there are specific trade-offs to keep in mind regarding storage, ingestion, and query logic. 1. The "Price" of Bloom Filter Indexes * Storage Overhead: Bloom filters are very compact. They typically add ~1% to 3% to the total storage size of the column being indexed. * Ingestion (Load) Performance: Since StarRocks must compute multiple hash values for every row during data ingestion, you will see a slight increase in CPU usage during 
Stream Load
or 
Insert
operations. In a high-load cluster (like your 11k QPS scenario), adding Bloom filters to many columns can add up, but for 1 or 2 critical columns like 
campaign_id
, it is negligible. * Query Memory: Bloom filters are loaded into memory on-demand during queries. You can monitor this via the BE metric 
bloom_filter_index_mem_bytes
. 2. How it Handles Scans (Your Intuition is Correct) You are exactly right about the logic: * False Positives (The "Maybe"): The index might say "Yes" for a page that doesn't actually contain 
campaign_id = 123
. StarRocks will scan that page, find nothing, and move on. * No False Negatives (The Guarantee): If the index says "No," the data is guaranteed not to be there. StarRocks will skip that page entirely. * Result: You will never lose data. The "price" of a false positive is just a tiny bit of wasted I/O, which is still vastly better than a full table scan. ────────── 3. Bitmap Index vs. Bloom Filter for 
network_id
(300-400 Cardinality) For a cardinality of 300-400, a Bitmap index is a bit of a "trap" in StarRocks if used alone. Here is why: * The Page-Level Skipping Problem: StarRocks reads data in 64KB Pages. If your 
network_id
has 400 values and your data is somewhat evenly distributed, every single 64KB page will likely contain at least one row for every 
network_id
. * The Outcome: A Bitmap index will tell the BE exactly which rows to read, but because those rows are scattered across every page, the BE still has to load every single page from disk. You end up loading 100% of the data plus the index, making the query slower than a simple scan. * StarRocks Adaptive Rule: By default, StarRocks won't even use a Bitmap index unless the filter is very selective: * 
Filter Values / Total Cardinality < 1/1000
. * For your case: 
1 / 400 = 0.0025 (or 2.5/1000)
. This is above the limit, so StarRocks will ignore the Bitmap index by default. My Recommendation: 1. For 
campaign_id
(High Cardinality): Use Bloom Filter. It is the gold standard for IDs that appear in many different data files but are rare overall. 2. For 
network_id
(300-400 Cardinality): * If you usually query 
network_id
alongside other filters (like 
campaign_id
), a Bitmap index can help through "Index Intersection." * If you query 
network_id
by itself, don't bother with an index. StarRocks' ZoneMaps (which track Min/Max values of every column automatically) will do a better job with less overhead, especially if the data is naturally sorted by time. Summary Checklist Feature | Bloom Filter | Bitmap Index Best For | High cardinality (IDs) | Low cardinality combinations Price | Low storage (~1-3%), slight load CPU | High storage for high cardinality Filter Types | 
=
, 
IN
| 
=
, 
IN
, 
IS NULL
False Positives | Yes | No Rule of Thumb | Put on IDs used in 
WHERE
| Put on "Tags" or "Status" used in combinations References • table_design/indexes/Bitmap_index.md
m
@Rocky If I add the bitmap index, will it compute for existing values too?
r
Yes, adding a bitmap index (or a bloom filter index) will compute for all existing data. In StarRocks, creating an index on an existing table is an asynchronous schema change operation. It doesn't just apply to new data moving forward; it kicks off a background task to build the index for all historical data already stored in your table. How to manage and track the process: 1. Start the index creation:
Copy code
sql
    CREATE INDEX idx_network_id ON your_table (network_id) USING BITMAP;
2. Monitor the progress: Since this is a background task, the command will return immediately, but the index isn't ready yet. You can check how much of your historical data has been indexed by running:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
* State: Look for 
FINISHED
. While it says 
RUNNING
, the index is still being built for existing rows. * Progress: Shows the percentage of data (tablets) already processed. 3. One at a time: StarRocks allows only one schema change task (like adding an index or adding a column) to run on a specific table at a time. If you want to add multiple bitmap indexes, you should wait for the first one to finish or add them in a single 
ALTER TABLE
statement if supported for that index type. What about Bloom Filters? If you decide to add a Bloom Filter instead (using 
ALTER TABLE your_table SET ("bloom_filter_columns" = "campaign_id")
), it behaves exactly the same way: * It is asynchronous. * It recomputes for all existing data. * You track it using the same 
SHOW ALTER TABLE COLUMN;
command. Performance Impact during creation: While the index is being built: * Queries: You can still query the table, but the new index won't be used until the state is 
FINISHED
. * Ingestion: You can still load new data. StarRocks will handle merging the new data with the index-building process. * Resources: There will be a slight increase in CPU and I/O on your BE nodes while they read the old data to build the index structures. Since you are already at high load, I recommend adding indexes during a slightly quieter period if possible. References • 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/SHOW_ALTER.md • faq/Sql_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/query_tuning/schema_tuning.md • table_design/indexes/Prefix_index_sort_key.md • administration/management/BE_configuration.md • table_design/indexes/Ngram_Bloom_Filter_Index.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_INDEX.md • sql-reference/sql-statements/table_bucket_part_index/DROP_INDEX.md
• best_practices/primarykey_table.md • administration/Configuration.md • administration/Monitor_and_Manage.md • introduction/StarRocks_intro.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/Control_load_speed.md • quick_start/query_from_starrocks.md • table_design/table_types/Unique_table.md • administration/Cluster_administration.md • administration/Data_recovery.md • administration/Hardware_configuration.md • administration/Maintenance.md • administration/Meta_recovery.md • administration/Scale_up_and_down.md • administration/Upgrading.md • benchmarking/SSB_Benchmarking.md • benchmarking/TPC-H_Benchmarking.md • best_practices/query_tuning/query_planning.md • deployment/deploy_manually.md • integrations/loading_tools/Loading_tools.md • loading/InsertInto.md • loading/Load_to_StarRocks.md • loading/RoutineLoad.md • loading/StreamLoad.md • main/Architecture.md • main/Concepts.md • quick_start/Deploy.md • quick_start/Loading.md • sql-reference/sql-statements/data-definition/CREATE_DATABASE.md • sql-reference/sql-statements/data-manipulation/SELECT.md • sql-reference/sql-statements/table_bucket_part_index/TRUNCATE_TABLE.md • table_design/Data_distribution.md • table_design/Data_model.md • table_design/table_types/Aggregate_table.md • table_design/table_types/Duplicate_table.md • using_starrocks/Case_studies.md • using_starrocks/Internal_external_table.md
m
@Rocky Does adding more ORDER KEYS in my primary key table definition help upsert performance? Updating existing rows.
r
No, adding more ORDER BY (sort) keys does not help upsert performance. In fact, it adds a small amount of overhead to the write process. Here is the technical breakdown of why and how it works in StarRocks Primary Key tables: 1. Primary Key vs. Sort Key Decoupling In StarRocks (v3.0+), the Primary Key and the Sort Key (
ORDER BY
) are independent: * Primary Key: Used exclusively to enforce uniqueness and find the "old" row during an upsert (to mark it as deleted). * Sort Key: Used to physically arrange the data on disk within each data file to speed up queries. 2. The Upsert Process (Delete + Insert) When you perform an upsert, StarRocks does two main things: 1. The "Delete" Phase: It looks up the incoming primary key in the Primary Key Index (which is stored in memory). This index tells it exactly where the old row is so it can mark it as deleted. Adding columns to the 
ORDER BY
clause does not change this index or make this lookup faster. 2. The "Insert" Phase: It takes the new data, sorts it according to your 
ORDER BY
keys, and writes it to a new data file. 3. The Performance Cost of More Order Keys Because the "Insert" phase requires sorting the batch of data before writing it to disk: * More Sort Keys = More CPU: Sorting by 10 columns is slightly more CPU-intensive than sorting by 1 column. * Write Latency: While the difference is often negligible for a few extra columns, adding a large number of sort keys will technically slow down the "Write" stage of your upsert. When does it "help"? (The Compaction Factor) There is one indirect way sort keys affect performance: Compaction. * If your sort keys are well-aligned with your ingestion pattern (e.g., sorting by 
timestamp
when data arrives mostly in time order), the background compaction process becomes more efficient because data is already "mostly" in the right place. * Efficient compaction keeps the number of data versions low, which prevents the system from slowing down ingestion due to "too many versions" errors. Recommendation for Your Case Since your goal is to optimize a 
GROUP BY campaign_id
query: * Don't add 
campaign_id
to the Sort Key just for upsert speed. It won't help. * Do add 
campaign_id
to the Sort Key (or use a Bloom Filter) for query speed. * If you are worried about upsert performance, the most important factors are: 1. Memory: Ensure your BE nodes have enough RAM to hold the Primary Key Index. 2. Batch Size: Upserting 10,000 rows in one batch is much faster than 10,000 individual upserts. 3. SSD/NVMe: Primary Key tables are I/O intensive during the commit stage (updating delete vectors). Summary: Adding more order keys is a "buy now, pay later" trade-off. You pay a small price in upsert CPU/latency to get a massive discount on query latency. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md
m
@Rocky How can I track the bloom filter index creation? How can I verify it is done?
r
Since adding a Bloom Filter index is an asynchronous background process, you track it using the same tools as other schema changes (like adding a column or a bitmap index). 1. How to Track Progress Run the following command to see the status of the background job:
Copy code
sql
SHOW ALTER TABLE COLUMN;
What to look for in the output: * State: * `RUNNING`: The index is still being built for your existing data. * `FINISHED`: The process is complete. * `CANCELLED`: The process failed (usually due to a lack of disk space or a manual stop). * Progress: This column shows a percentage (e.g., 
10%
) representing how much of your historical data has been indexed so far. * Msg: If the state is 
CANCELLED
, this will contain the reason why. 2. How to Verify It Is Done Once the status above shows 
FINISHED
, you can verify the index is active in two ways: A. Check Table Properties Run this to see if the column is officially marked as a Bloom Filter column:
Copy code
sql
SHOW CREATE TABLE <table_name>;
Look for 
"bloom_filter_columns" = "campaign_id"
in the 
PROPERTIES
section at the bottom of the output. B. Verify in Query Profile (The "Gold Standard") To be 100% sure the index is actually being used by your queries: 1. Run your query (e.g., 
SELECT ... WHERE campaign_id = 123
). 2. Get the query ID using 
SHOW PROC '/current_queries';
or from your manager UI. 3. View the Query Profile. 4. Look for the 
BloomFilterFilterRows
metric in the 
OLAP_SCAN_NODE
. If this number is greater than 0, it means the index successfully filtered out rows! Pro-Tips: * One at a time: You can only run one 
ALTER TABLE
schema change at a time on a single table. If you try to add a Bloom Filter while a Bitmap index is still building, the second command will be rejected. * No restart needed: The index becomes "active" for queries immediately after the background task hits 
FINISHED
. References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md • table_design/indexes/Ngram_Bloom_Filter_Index.md
m
@Rocky
Copy code
Alter job conflicts with partition creation, for more details please check <https://docs.starrocks.io/docs/faq/Others#how-can-i-prevent-expression-partition-conflicts-caused-by-concurrent-execution-of-loading-tasks-and-partition-creation-tasks>
PRIMARY KEY(
received_hour
, 
auction_id
, 
imp_key
) PARTITION BY date_trunc('hour', received_hour) DISTRIBUTED BY HASH(
auction_id
) ORDER BY(
received
, 
auction_id
) PROPERTIES ( "compression" = "LZ4", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "partition_live_number" = "24", "replicated_storage" = "true", "replication_num" = "1" );
r
This error occurs because you are using Expression Partitioning (
PARTITION BY date_trunc('hour', ...)
), which creates new partitions automatically as data arrives. In StarRocks, creating a partition and performing a schema change (like adding an index) are both metadata operations that require a lock. Loading tasks are given priority. If a load triggers the creation of a new hourly partition while your index is being built, the system will cancel the 
ALTER
job to allow the load to proceed. Since you are partitioning by hour, this conflict is very likely to happen every time a new hour starts. How to Fix: Pre-create Future Partitions The best way to solve this without stopping your data ingestion is to "pre-warm" the partitions for the next several hours. This prevents the loading tasks from needing to create any new partitions while the index job is running. You can use the 
EXPLAIN ANALYZE
trick to trigger the automatic creation of future partitions without actually inserting data:
Copy code
sql
-- This "fake" insert triggers the creation of partitions for the next 12 hours.
-- Replace 'received_hour' with your actual column name if different.
EXPLAIN ANALYZE
INSERT INTO your_table_name (received_hour, auction_id, imp_key)
SELECT 
    DATE_ADD(NOW(), INTERVAL d hour), -- Generate future hours
    'dummy_id', 
    'dummy_key'
FROM table(generate_series(1, 12)) AS g(d);
Steps to complete your index: 1. Check current partitions: Run 
SHOW PARTITIONS FROM your_table_name;
to see the latest partition. 2. Run the script above: This will pre-create the next 12 hours (or 24 hours) of partitions. 3. Start your Index/Bloom Filter again:
Copy code
sql
    CREATE INDEX idx_campaign ON your_table_name (campaign_id) USING BITMAP;
4. Monitor: Check 
SHOW ALTER TABLE COLUMN;
. As long as the index finishes before those 12 hours are up, it will not be interrupted. Why is this happening? * Expression Partitioning: It's "lazy." It only creates a partition when data for that specific time range arrives. * The Conflict: When the first record of a new hour (e.g., 2:00 PM) arrives, the BE asks the FE to create a new partition. The FE sees the 
ALTER
job running and, because StarRocks prioritizes data availability (loading) over index building, it kills the index job. * The Solution: By pre-creating the partitions, you "busy-out" the partition creation logic, leaving the metadata lock free for your 
ALTER
job to finish. Note: If your index is very large and takes more than 12 hours to build, simply increase the 
generate_series
number to 24 or 48. References • faq/Others.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
m
@Rocky So if my partitions are hourly, then I just need to wait awhile and can try re-creating the bloom filter index?
r
No, just waiting and retrying will likely lead to the same error. Because you are using hourly partitions, every time a new hour begins (e.g., at 20000, 30000, etc.), the first row of data that arrives for that hour triggers the creation of a new partition. In StarRocks, partition creation is a metadata operation that takes priority over 
ALTER TABLE
jobs (like building a Bloom Filter). If the index build is still running when that new hour hits, the system will cancel your index job to ensure data loading doesn't stop. Why "Waiting" Won't Solve It If your data volume is large and the Bloom Filter index takes more than 60 minutes to build, it will never finish because it will be killed every hour on the hour. The Solution: Pre-create Future Partitions To give the index job a "clear path" to finish, you need to manually create the partitions for the next several hours (or days) so that the loading process doesn't need to create them. You can use this "trick" to trigger the creation of the next 24 hours of partitions without actually inserting any dummy data:
Copy code
sql
-- This triggers the automatic creation of partitions for the next 24 hours.
-- It won't actually insert data because of the 'EXPLAIN ANALYZE' prefix.
EXPLAIN ANALYZE
INSERT INTO your_table_name (received_hour, auction_id, imp_key)
SELECT 
    DATE_ADD(NOW(), INTERVAL d hour), 
    'dummy_id', 
    'dummy_key'
FROM table(generate_series(1, 24)) AS g(d);
Recommended Workflow: 1. Pre-create Partitions: Run the script above to cover the next 24 hours. 2. Verify Partitions: Run 
SHOW PARTITIONS FROM your_table_name;
to confirm you see the future hours listed. 3. Start the Bloom Filter Job:
Copy code
sql
    ALTER TABLE your_table_name SET ("bloom_filter_columns" = "campaign_id");
4. Monitor Progress: Use 
SHOW ALTER TABLE COLUMN;
. By doing this, you've ensured that for the next 24 hours, no "partition creation" events will occur, giving your Bloom Filter index plenty of time to finish successfully. References • faq/Others.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • faq/Others.md • table_design/data_distribution/expression_partitioning.md
m
@Rocky How can I check if the table is still busy creating new partitions? I want to try creating bloom filter index within an hour
r
You cannot check if the table is "busy" creating a partition in the sense of a long-running process, because partition creation is a near-instant metadata update. The "conflict" occurs the exact millisecond a loading task realizes it needs a new hour's partition and tries to create it while your Bloom Filter job is running. To "clear the way" for your index creation, you need to verify that the partitions for the current hour and the next few hours already exist. 1. How to check if the partitions are already there Run this command to see the latest partitions:
Copy code
sql
SHOW PARTITIONS FROM your_table_name ORDER BY PartitionId DESC LIMIT 5;
What to look for: Check the 
Range
column. It looks like this: 
[types: [DATETIME]; keys: [2023-10-27 14:00:00]; ..keys: [2023-10-27 15:00:00]; )
. * If you see a partition that covers the current time and at least the next hour, you are safe to start the Bloom Filter creation now. _ If the latest partition only goes up to the _current* hour (e.g., it's 14:05 and the latest range ends at 15:00), your index job will be killed the moment a single row of data for 150001 arrives. 2. How to ensure no "busyness" happens for the next hour If you want to be 100% sure the index job isn't interrupted, use the "pre-create" trick mentioned before. This creates the "placeholders" so the loading tasks don't have to do any metadata work:
Copy code
sql
-- This creates partitions for the next 5 hours
EXPLAIN ANALYZE
INSERT INTO your_table_name (received_hour, auction_id, imp_key)
SELECT 
    DATE_ADD(NOW(), INTERVAL d hour), 
    'dummy', 
    'dummy'
FROM table(generate_series(1, 5)) AS g(d);
3. Summary of the "Safe Window" 1. Run the 
SHOW PARTITIONS
command. 2. Identify the latest hour covered by the partitions. 3. If the latest hour is in the future (e.g., it is currently 2:00 PM and you see a partition for 5:00 PM), you have a 3-hour "Safe Window" where no partition conflicts will occur. 4. Start your Bloom Filter job immediately. Why does it fail even if it's "not busy"? In StarRocks, 
ALTER TABLE
(Bloom Filter) and 
CREATE PARTITION
(triggered by loading) both need a "Schema Change Lock" on the table. * If the index is building, it holds the lock. * If a load needs a new partition, it requests the lock. * StarRocks will intentionally kill the index job to give the lock to the load, because it assumes your real-time data ingestion is more important than the background index build. By pre-creating the partitions for the next few hours, you ensure the loader never has to ask for that lock, allowing your Bloom Filter index to finish in peace. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • faq/Others.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • sql-reference/information_schema/partitions.md
m
@Rocky
Copy code
INSERT INTO requests (received, received_hour, auction_id, imp_key)
SELECT
            DATE_ADD(NOW(), INTERVAL 4 hour),
    DATE_ADD(NOW(), INTERVAL 4 hour),
    'dummy',
    'dummy'
FROM table(generate_series(1, 5)) AS g(d);
This will generate 4 hours of partitions in advance? So does that mean if i set max partitions to 24, it will artifically truncate ttl to 20 hours temporarily
r
To answer your question directly: No, pre-creating future partitions will NOT artificially truncate your history (TTL). In StarRocks Expression Partitioning, the 
partition_live_number
uses the current system time as the benchmark, not the timestamp of your latest partition. How the logic works: If you have 
partition_live_number = 24
(hours): 1. The Cutoff: StarRocks calculates the cutoff as 
Current Time - 24 hours
. 2. Retention: Any partition whose time range is newer than that cutoff is kept. 3. Future Partitions: Since future partitions (e.g., 
Now + 4 hours
) are obviously newer than 
Now - 24 hours
, they are kept. 4. Old Partitions: Your oldest partition (at 
Now - 23 hours
) is still newer than the cutoff, so it stays. Result: You will simply have more than 24 partitions total (24 past/present + 4 future = 28 partitions) until the future partitions eventually become "old" relative to the clock. ────────── Important: Fix your SQL The SQL you provided has a small bug. Because you are using a fixed 
INTERVAL 4 hour
instead of the series variable 
d
, it will only create one future partition (the one for 4 hours from now), and it will try to insert 5 identical rows into it. To create a range of future partitions (e.g., the next 5 hours), use the variable 
d
from the `generate_series`:
Copy code
sql
-- Use 'EXPLAIN ANALYZE' so you don't actually insert dummy data, 
-- but still trigger the FE to create the partitions.
EXPLAIN ANALYZE
INSERT INTO requests (received_hour, auction_id, imp_key)
SELECT
    DATE_ADD(NOW(), INTERVAL d hour), -- This uses the 'd' (1, 2, 3, 4, 5)
    'dummy',
    'dummy'
FROM table(generate_series(1, 5)) AS g(d);
Summary of what to do: 1. Run the fixed SQL above to create the next 5-10 hours of partitions. 2. Run your Bloom Filter index command immediately:
Copy code
sql
    ALTER TABLE requests SET ("bloom_filter_columns" = "some_column");
3. Because the partitions for the next several hours already exist, the data loading tasks won't trigger any "Partition Creation" metadata locks, and your index build won't be killed. 4. Verify progress with 
SHOW ALTER TABLE COLUMN;
. References • table_design/data_distribution/expression_partitioning.md • faq/Others.md References • faq/operation_maintenance_faq.md • table_design/data_distribution/dynamic_partitioning.md • table_design/data_distribution/expression_partitioning.md
m
@Rocky
Copy code
[42000][1064] Getting syntax error at line 1, column 123. Detail message: Unexpected input '<EOF>', the most similar input is {'DESC', 'SELECT', 'EXPLAIN', 'DESCRIBE', 'PROPERTIES', '('}.
EXPLAIN ANALYZE INSERT INTO requests (received, received_hour, auction_id, imp_key)
SELECT
            DATE_ADD(NOW(), INTERVAL d hour),
    DATE_ADD(NOW(), INTERVAL d hour),
    'dummy',
    'dummy'
FROM table(generate_series(1, 5)) AS g(d);
r
The syntax error you're seeing usually happens for one of two reasons: the aliasing syntax for the table function or the way 
EXPLAIN ANALYZE
is being parsed by your SQL client. In StarRocks, when using the 
TABLE(generate_series(...))
function, the resulting column is automatically named 
generate_series
. Using the alias 
AS g(d)
can sometimes confuse the parser in older 3.x sub-versions. The Corrected SQL Try this version, which uses the default column name and removes the potentially problematic alias:
Copy code
sql
EXPLAIN ANALYZE 
INSERT INTO requests (received, received_hour, auction_id, imp_key)
SELECT
    DATE_ADD(NOW(), INTERVAL generate_series hour),
    DATE_ADD(NOW(), INTERVAL generate_series hour),
    'dummy',
    'dummy'
FROM TABLE(generate_series(1, 5));
Why this fixes the error: 1. Column Reference: Instead of 
d
, we use the default column name 
generate_series
produced by the function. 2. Aliasing: We removed 
AS g(d)
. StarRocks doesn't always require or support the 
AS alias(col)
syntax for built-in table functions in the same way Postgres does. 3. EOF Error: If you still get an "Unexpected <EOF>" error, ensure you are running the entire block of code at once. Some IDEs (like DataGrip or DBeaver) might try to run only the first line if you don't highlight the whole statement, which would cause an "unexpected end of file" because 
EXPLAIN ANALYZE
is missing its body. A few things to check: * Version Check: Run 
SELECT VERSION();
. 
EXPLAIN ANALYZE
for 
INSERT
and the 
generate_series
function both require StarRocks v3.1 or higher. * Column Names: Make sure the columns 
received
and 
received_hour
in your 
requests
table actually accept 
DATETIME
values. * Privileges: You need 
INSERT
privileges on the table to run 
EXPLAIN ANALYZE INSERT
. What happens next? When you run this successfully: 1. StarRocks will begin the "Insert" process. 2. It will see that data for "Now + 1 hour", "Now + 2 hours", etc., is coming in. 3. It will automatically create those partitions in the metadata. 4. Because it is an 
EXPLAIN ANALYZE
job, it will abort the transaction at the very end. 5. The data is not saved, but the partitions remain created. Once this finishes, you can immediately start your 
ALTER TABLE ... SET ("bloom_filter_columns" = "...")
job. It should now run to completion because the "Partition Creation" events for the next 5 hours have already happened. References • sql-reference/sql-functions/table-functions/generate_series.md • sql-reference/sql-statements/cluster-management/plan_profile/EXPLAIN_ANALYZE.md • table_design/data_distribution/expression_partitioning.md References • sql-reference/sql-functions/table-functions/generate_series.md • sql-reference/sql-statements/cluster-management/plan_profile/EXPLAIN_ANALYZE.md • best_practices/query_tuning/query_profile_text_based_analysis.md -explain-analyze) • faq/operation_maintenance_faq.md • table_design/data_distribution/expression_partitioning.md References • table_design/data_distribution/expression_partitioning.md
m
@Rocky I can run bloom filter generation immediately after?
r
Yes, you can run the Bloom filter generation immediately after your 
EXPLAIN ANALYZE
command finishes. Here is the recommended workflow to ensure it succeeds without being killed by your data loader: 1. Execute the Pre-creation Command Run this to trigger the automatic creation of future partitions. _(Note: I corrected the syntax below to use the default column name 
generate_series
which is more stable across versions)._
Copy code
sql
EXPLAIN ANALYZE 
INSERT INTO requests (received, received_hour, auction_id, imp_key)
SELECT
    DATE_ADD(NOW(), INTERVAL generate_series hour),
    DATE_ADD(NOW(), INTERVAL generate_series hour),
    'dummy',
    'dummy'
FROM TABLE(generate_series(1, 10)); -- Pre-create the next 10 hours
2. Verify the Partitions Exist Before starting the index job, quickly double-check that the partitions were actually created:
Copy code
sql
SHOW PARTITIONS FROM requests ORDER BY PartitionId DESC LIMIT 10;
Look at the 
Range
or 
PartitionKey
column. If you see timestamps for several hours into the future, you are safe. 3. Start the Bloom Filter Job Now, immediately run your index creation:
Copy code
sql
ALTER TABLE requests SET ("bloom_filter_columns" = "auction_id, imp_key");
Why this works immediately: * The Conflict is Gone: The "conflict" happens because the background loader and the Bloom Filter job both need a Schema Change Lock to modify metadata. If the loader realizes it needs to create a new partition, it will request that lock and StarRocks will kill your index job to let the data through. * No more Lock Requests: Since you just pre-created the next 10 hours of partitions, the loader will find the partitions already exist when data arrives. It will simply insert the data without needing the Schema Change Lock. * Persistence: In StarRocks, partitions created during a load (even an aborted/explained one) are permanent metadata changes and do not "roll back." How to monitor the progress: The 
ALTER TABLE
command returns immediately because it is asynchronous. Monitor it with:
Copy code
sql
SHOW ALTER TABLE COLUMN;
Look for the 
State
column. It will move from 
WAITING_TXN
to 
RUNNING
and finally 
FINISHED
. As long as you have enough future partitions, it should stay 
RUNNING
until completion! References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/loading_unloading/INSERT.md
m
@Rocky
Copy code
SHOW ALTER TABLE COLUMN FROM requests;
What would be correct to track it more accurately
r
To track the Bloom filter generation (or any schema change) more accurately than just seeing a "RUNNING" state, you should focus on three specific areas: the Job Status, the Progress Percentage, and the Tablet-level progress. 1. The Standard View (Job Level) Use the 
\G
terminator if you are using a MySQL client to see the output in a vertical list, which is much easier to read:
Copy code
sql
SHOW ALTER TABLE COLUMN WHERE TableName = "requests" ORDER BY CreateTime DESC LIMIT 1 \G
Key columns to watch: * State: * `WAITING_TXN`: Waiting for old data-loading transactions that started before your command to finish. * `RUNNING`: The index is actually being built. * `FINISHED`: Success. * Progress: This shows the percentage of tablets that have been processed (e.g., 
10/100
). If your table has 1,000 tablets, you will see this number climb as each tablet finishes its Bloom filter generation. * Msg: If the job is killed (e.g., by a partition creation conflict), the reason will appear here. ────────── 2. The "Deep Dive" (Tablet Level) If the 
Progress
in the command above isn't moving and you want to see if the work is actually happening under the hood, you can check the status of individual tablets. This is the most accurate way to "see" the engine working.
Copy code
sql
SHOW TABLETS FROM requests WHERE State = "ALTER";
* Empty Result: Either the job hasn't started moving data yet, or it's already finished. * Rows Returned: You will see specific tablets that are currently in the 
ALTER
state. As the job progresses, tablets will cycle through this state. ────────── 3. How to know if it's "stuck" If 
Progress
stays at 
0%
for a long time: 1. Check for Long-Running Transactions: The job cannot move from 
WAITING_TXN
to 
RUNNING
if there is a very old 
INSERT
or 
ROUTINE LOAD
transaction still open.
Copy code
sql
    SHOW PROC '/transactions'; -- Look for 'RUNNING' transactions with a very old 'PrepareTime'
2. Check BE Logs: If the state is 
RUNNING
but no tablets are moving, the Backend (BE) might be resource-constrained. Pro-Tip for Large Tables If you have a massive table, the Bloom filter generation can take a while because StarRocks has to read the data and write the index for every single existing file. * Don't panic if it's slow: It is a background process designed not to impact query performance too heavily. * The "Point of No Return": Once it hits 
RUNNING
and you see the 
Progress
(e.g., 
1/500
) start to increment, your pre-creation of partitions has done its job—it won't be killed by the metadata lock anymore. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • faq/Sql_faq.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLETS.md References • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • faq/Sql_faq.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md
m
@Rocky Alter job conflicts with partition creation, for more details please check https://docs.starrocks.io/docs/faq/Others#how-can-i-prevent-expression-partition-conflicts-caused-by-concurrent-execution-of-loading-tasks-and-partition-creation-tasks Still get this error message after pre-creating partitions
r
If you have already pre-created future partitions but are still seeing the "Alter job conflicts with partition creation" error, it means a background load job is still triggering a "partition creation" event for a timestamp that was not covered by your 
EXPLAIN ANALYZE
command. Here is why this happens and how to fix it: 1. The "Late Data" Problem The most common cause is late-arriving data. * Your 
EXPLAIN ANALYZE
created partitions for the future (e.g., 
NOW() + 1 hour
). * However, if a Stream Load or Routine Load contains data from the past (e.g., 
NOW() - 2 hours
) and that partition doesn't exist yet, StarRocks will try to create it. * In StarRocks, any partition creation (past or future) takes a metadata lock that kills the 
ALTER TABLE
job immediately. Fix: Identify if you have gaps in your current or past partitions:
Copy code
sql
-- Check if there are gaps in the last 24 hours
SHOW PARTITIONS FROM requests ORDER BY PartitionKey DESC LIMIT 50;
If you see any missing hours in your recent history, pre-create them as well:
Copy code
sql
EXPLAIN ANALYZE 
INSERT INTO requests (received, received_hour, auction_id, imp_key)
SELECT 
    DATE_SUB(NOW(), INTERVAL generate_series hour), -- CREATE PAST HOURS
    DATE_SUB(NOW(), INTERVAL generate_series hour),
    'dummy', 'dummy'
FROM TABLE(generate_series(0, 24)); -- Pre-create the last 24 hours just in case
2. The "Current Hour" Gap If you ran 
generate_series(1, 5)
, you created partitions for $+1$ to $+5$ hours. You might have missed the current hour (the "0" hour). Fix: Always include 
0
in your series:
Copy code
sql
FROM TABLE(generate_series(0, 10))
3. Identify the "Offending" Partition To know exactly which partition is killing your job, you can check the FE (Front End) leader log (
fe.log
). Look for the error message; it will usually mention the 
PartitionName
it was trying to create when the conflict occurred. This tells you exactly what timestamp you missed. 4. The "Pause and Pulse" Strategy (Recommended) If your data stream is very messy (lots of late data), the pre-creation method might be like playing "Whac-A-Mole." Instead, use this sequence: 1. Pause the Loads: If you are using Routine Load, pause it for 2 minutes.
Copy code
sql
    PAUSE ROUTINE LOAD FOR your_job_name;
2. Pre-create a wide range: Create partitions for the last 24 hours AND the next 24 hours. 3. Start the Alter:
Copy code
sql
    ALTER TABLE requests SET ("bloom_filter_columns" = "auction_id, imp_key");
4. Resume the Loads:
Copy code
sql
    RESUME ROUTINE LOAD FOR your_job_name;
Once the Alter job moves from 
WAITING_TXN
to 
RUNNING
(check with 
SHOW ALTER TABLE COLUMN
), it has successfully "captured" the lock. As long as the partitions for incoming data already exist, the loads will proceed without killing the index build. Summary Checklist • ☐ Did you include the current hour (
0
) in 
generate_series
? • ☐ Is there late-arriving data (e.g., data from yesterday) triggering old partition creation? • ☐ Are you using 
EXPLAIN ANALYZE
? (Remember: standard 
EXPLAIN
does not trigger partition creation). References • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • sql-reference/sql-statements/loading_unloading/ROUTINE_LOAD.md References • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/expression_partitioning.md
m
@Rocky
Copy code
3947802,p2026042923,3,2026-04-29 18:16:48,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 23:00:00]; ..types: [DATETIME]; keys: [2026-04-30 00:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,0B,0B,false,0,3,418695937037172738,TXN_NORMAL,true
3943838,p2026042922,4,2026-04-29 18:16:48,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 22:00:00]; ..types: [DATETIME]; keys: [2026-04-29 23:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,0B,0B,false,0,4,418695642972422144,TXN_NORMAL,true
3947572,p2026042921,3,2026-04-29 18:16:48,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 21:00:00]; ..types: [DATETIME]; keys: [2026-04-29 22:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,0B,0B,false,0,3,418695937037172737,TXN_NORMAL,true
3947342,p2026042920,3,2026-04-29 18:16:48,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 20:00:00]; ..types: [DATETIME]; keys: [2026-04-29 21:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,0B,0B,false,0,3,418695937037172736,TXN_NORMAL,true
3947112,p2026042919,3,2026-04-29 18:16:48,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 19:00:00]; ..types: [DATETIME]; keys: [2026-04-29 20:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,0B,0B,false,0,3,418695937035075584,TXN_NORMAL,true
3901621,p2026042918,323,2026-04-29 18:23:02,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 18:00:00]; ..types: [DATETIME]; keys: [2026-04-29 19:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,95.3GB,95.3GB,false,55249040,323,418693893102501888,TXN_NORMAL,true
3804875,p2026042917,1170,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 17:00:00]; ..types: [DATETIME]; keys: [2026-04-29 18:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,294.1GB,294.1GB,false,178716346,1170,418686346410852352,TXN_NORMAL,true
3740043,p2026042916,1734,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 16:00:00]; ..types: [DATETIME]; keys: [2026-04-29 17:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,303.7GB,303.7GB,false,182228701,1734,418678794417602560,TXN_NORMAL,true
3666876,p2026042915,2009,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 15:00:00]; ..types: [DATETIME]; keys: [2026-04-29 16:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,297.5GB,297.5GB,false,179055503,2009,418671244689276928,TXN_NORMAL,true
3599282,p2026042914,2157,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 14:00:00]; ..types: [DATETIME]; keys: [2026-04-29 15:00:00]; ),auction_id,114,1,HDD,9999-12-31 15:59:59,,315.4GB,315.4GB,false,190296378,2157,418663693708951552,TXN_NORMAL,true
3531173,p2026042913,2265,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 13:00:00]; ..types: [DATETIME]; keys: [2026-04-29 14:00:00]; ),auction_id,109,1,HDD,9999-12-31 15:59:59,,301.8GB,301.8GB,false,180969381,2265,418656142648934400,TXN_NORMAL,true
3472262,p2026042912,2332,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 12:00:00]; ..types: [DATETIME]; keys: [2026-04-29 13:00:00]; ),auction_id,109,1,HDD,9999-12-31 15:59:59,,314.3GB,314.3GB,false,187130692,2332,418648596403978240,TXN_NORMAL,true
3426697,p2026042911,2371,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 11:00:00]; ..types: [DATETIME]; keys: [2026-04-29 12:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,282.3GB,282.3GB,false,169443015,2371,418641046226862080,TXN_NORMAL,true
3393780,p2026042910,2359,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 10:00:00]; ..types: [DATETIME]; keys: [2026-04-29 11:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,250.6GB,250.6GB,false,147999441,2359,418633499631681536,TXN_NORMAL,true
3360687,p2026042909,2211,2026-04-29 18:17:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 09:00:00]; ..types: [DATETIME]; keys: [2026-04-29 10:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,194.9GB,194.9GB,false,118923420,2211,418625945526599680,TXN_NORMAL,true
3327567,p2026042908,2166,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 08:00:00]; ..types: [DATETIME]; keys: [2026-04-29 09:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,180.6GB,180.6GB,false,105114720,2166,418618395508867072,TXN_NORMAL,true
3292017,p2026042907,2299,2026-04-29 18:19:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 07:00:00]; ..types: [DATETIME]; keys: [2026-04-29 08:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,168.3GB,168.3GB,false,103438944,2299,418610848928366592,TXN_NORMAL,true
3244155,p2026042906,2448,2026-04-29 18:21:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 06:00:00]; ..types: [DATETIME]; keys: [2026-04-29 07:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,189.4GB,189.4GB,false,114319473,2448,418603297346158592,TXN_NORMAL,true
3189226,p2026042905,2606,2026-04-29 18:19:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 05:00:00]; ..types: [DATETIME]; keys: [2026-04-29 06:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,223.5GB,223.5GB,false,133887727,2606,418595748200841216,TXN_NORMAL,true
3121484,p2026042904,2798,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 04:00:00]; ..types: [DATETIME]; keys: [2026-04-29 05:00:00]; ),auction_id,100,1,HDD,9999-12-31 15:59:59,,258.8GB,258.8GB,false,154311885,2798,418588200223637504,TXN_NORMAL,true
3031689,p2026042903,2723,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 03:00:00]; ..types: [DATETIME]; keys: [2026-04-29 04:00:00]; ),auction_id,122,1,HDD,9999-12-31 15:59:59,,249.1GB,249.1GB,false,154782374,2723,418580650916839424,TXN_NORMAL,true
2926906,p2026042902,2968,2026-04-29 18:21:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 02:00:00]; ..types: [DATETIME]; keys: [2026-04-29 03:00:00]; ),auction_id,122,1,HDD,9999-12-31 15:59:59,,288.3GB,288.3GB,false,185181005,2968,418573098409787392,TXN_NORMAL,true
2829232,p2026042901,3076,2026-04-29 18:21:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 01:00:00]; ..types: [DATETIME]; keys: [2026-04-29 02:00:00]; ),auction_id,116,1,HDD,9999-12-31 15:59:59,,334.9GB,334.9GB,false,210810642,3076,418565548345917440,TXN_NORMAL,true
2733370,p2026042900,3040,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-29 00:00:00]; ..types: [DATETIME]; keys: [2026-04-29 01:00:00]; ),auction_id,116,1,HDD,9999-12-31 15:59:59,,322.5GB,322.5GB,false,203197270,3040,418557998795849728,TXN_NORMAL,true
2626640,p2026042823,3081,2026-04-29 18:22:10,0,NORMAL,received_hour,[types: [DATETIME]; keys: [2026-04-28 23:00:00]; ..types: [DATETIME]; keys: [2026-04-29 00:00:00]; ),auction_id,116,1,HDD,9999-12-31 15:59:59,,329.6GB,329.6GB,false,205114020,3081,418550449459691520,TXN_NORMAL,true
Don't see any missing partitions. PArtition live number only 24.
r
The reason you are still seeing the "Alter job conflicts" error—despite pre-creating future partitions—is likely due to one of two things: 1. The "24-Hour TTL" Trap: You mentioned your 
partition_live_number
is set to 24. This means StarRocks will only allow 24 partitions to exist. If you pre-create 5 hours of the future, StarRocks will automatically drop the 5 oldest hours of the past to stay at the limit. If a data load arrives for even one of those recently dropped hours, it will trigger a "Partition Creation" event to recreate it, which kills your Alter job. 2. Late-Arriving Data: In ad-tech (based on your 
auction_id
and 
imp_key
columns), data is often delayed. If a single row arrives with a timestamp older than your earliest partition (
p2026042823
), it triggers a partition creation. The Solution: The "Buffer Expansion" Strategy To fix this, you need to temporarily widen the allowed number of partitions so that the system doesn't have to create or drop anything while the Indexing job is running. Step 1: Temporarily increase the TTL limit Increase the 
partition_live_number
so you can hold more than 24 hours of data. This prevents the "Whac-A-Mole" effect where creating a future partition deletes a past one.
Copy code
sql
ALTER TABLE requests SET ("partition_live_number" = "120"); -- Set to 5 days temporarily
Step 2: Pre-create a "Wide Buffer" (Future AND Past) Cover a wide range to ensure NO incoming data (late or future) triggers a creation. Based on your current time (
18:23
), let's cover 24 hours back and 24 hours forward:
Copy code
sql
EXPLAIN ANALYZE 
INSERT INTO requests (received, received_hour, auction_id, imp_key)
SELECT 
    DATE_ADD(NOW(), INTERVAL generate_series-24 hour), -- -24 to +24
    DATE_ADD(NOW(), INTERVAL generate_series-24 hour),
    'dummy', 'dummy'
FROM TABLE(generate_series(0, 48));
Step 3: Run the Bloom Filter job Now start the job. Since the system now has a wide range of partitions and is not forced to drop old ones, no "Partition Creation" events should occur.
Copy code
sql
ALTER TABLE requests SET ("bloom_filter_columns" = "auction_id, imp_key");
Step 4: Monitor and Revert Once the job is finished (check with 
SHOW ALTER TABLE COLUMN
), revert your TTL:
Copy code
sql
-- Wait until State = 'FINISHED'
ALTER TABLE requests SET ("partition_live_number" = "24");
Why this is happening (Technical Detail) In StarRocks, Expression Partitioning uses a metadata lock. If a 
ROUTINE LOAD
or 
STREAM LOAD
task realizes it needs to create a partition for a specific timestamp (because that partition doesn't exist), it requests a "Schema Change Lock." StarRocks is designed to prioritize data ingestion, so it will instantly abort any running 
ALTER TABLE
job to give that lock to the loader. By temporarily increasing your 
partition_live_number
and pre-filling the gaps, you ensure the loader "finds" every partition it needs, so it never asks for the lock. How to verify the specific conflict If it fails again, run this to see exactly which partition name caused the death:
Copy code
sql
SHOW ALTER TABLE COLUMN FROM requests ORDER BY CreateTime DESC LIMIT 1 \G
Look at the 
Msg
 column. It will often say something like: 
...conflict with partition creation [p2026042820]
. This tells you exactly which hour you forgot to pre-create! References • table_design/data_distribution/expression_partitioning.md • faq/Others.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md References • faq/Others.md
m
@Rocky How can I check storage needs of my bloom filter or bitmap indexes?
r
To check the storage needs or current usage of Bloom filter and bitmap indexes in StarRocks, there isn't a single "one-line" SQL command (like 
SHOW INDEX SIZE
). However, you can determine this using a combination of Query Profiles, Estimation Formulas, and Monitoring Metrics. 1. Check Actual Disk Usage (via Query Profile) The most accurate way to see how much space your indexes are taking on disk is to look at a Query Profile for a query that uses those indexes. 1. Enable the profile:
Copy code
sql
    SET enable_profile = true;
2. Run a query that filters on the indexed column (e.g., 
SELECT count(*) FROM table WHERE col = 'value';
). 3. View the profile (using 
SHOW PROFILE CURRENT;
or through the FE web UI at 
http://<FE_IP>:8030/query
). 4. Look for these metrics under the `OLAP_SCAN_NODE`: * 
CompressedBytesRead
: This is the total data read from disk. * 
BitmapIndexFilter
/ 
BloomFilterFilterRows
: These tell you if the index was used. * Detailed Breakdown: In the physical profile, StarRocks often distinguishes between data pages and index pages. For example, if 
CompressedBytesRead
is 100MB and the data read for that column is 40MB, the remaining 60MB is the index overhead. 2. Check Memory Usage (Monitoring) Bloom filters are often cached in memory on the Backend (BE) nodes to speed up queries. You can monitor the memory footprint of these indexes: * Metric Name: 
bloom_filter_index_mem_bytes
* Access: You can view this via the BE metrics endpoint: 
http://<BE_IP>:<BE_HTTP_PORT>/metrics
. * SQL: You can also check general memory usage with 
SHOW PROC '/backends';
. 3. Estimating Storage Needs (Before Creating) If you are planning to add an index and want to estimate the storage overhead: For Bloom Filter Index: By default, StarRocks uses roughly 10 bits per unique value per page. * Formula: 
(Number of Rows / 1024) * 10 bits
(roughly). * Rule of Thumb: A Bloom filter usually adds 1% to 5% overhead to the total size of the indexed column. * Note: If you decrease the 
bloom_filter_fpp
(False Positive Probability) during table creation, the storage overhead will increase significantly. For Bitmap Index: Bitmap storage depends heavily on cardinality (number of distinct values). * Low Cardinality: Extremely small (only a few bits per row). * High Cardinality: Can become quite large because StarRocks builds a dictionary and a separate bitmap for every unique value. * Reference: In StarRocks' own performance tests, a bitmap index on a column with 600,000 distinct values added roughly 1 byte per row of storage overhead. 4. Summary Table of Commands Goal | Method Check if index is used | 
EXPLAIN
or Query Profile (
BloomFilterFilterRows
) Check disk space overhead | Enable Query Profile and check 
CompressedBytesRead
vs 
DataRead
. Check memory footprint | BE Metrics: 
bloom_filter_index_mem_bytes
Check index definition | 
SHOW CREATE TABLE <table_name>;
or 
SHOW INDEX FROM <table_name>;
Rocky's Tip: Avoid creating Bloom filters on columns that are already part of your Short Key (Prefix Index). The Prefix Index is already stored in memory and is much faster than a Bloom filter; adding a Bloom filter on top of it just wastes disk space without adding performance. References • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
m
@Rocky I tested a big query taking ~20 million records -------------------------------------------------------------+ | Summary | | QueryId: 019ddac1-fa26-7f91-8ca5-97b32e76cd21 | | Version: 4.0.7-b75f536 | | State: Finished | | TotalTime: 11s489ms | | ExecutionTime: 11s440ms [Scan: 3s336ms (29.16%), Network: 49.476ms (0.43%), ResultDeliverTime: 7s776ms (67.98%), ScheduleTime: 11s284ms (98.64%)] | | CollectProfileTime: 0 | | FrontendProfileMergeTime: 2.035ms | | QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 337.317 GB | | Top Most Time-consuming Nodes: | | 1. RESULT_SINK: 3s510ms (47.76%) | | 2. OLAP_SCAN (id=0) : 3s368ms (45.83%) | | 3. EXCHANGE (id=2) : 469.985ms (6.39%) | | 4. PROJECT (id=1) : 1.259ms (0.02%) This is part of the explain analyze query result. Does this mean I need to allocate 337GB of memory across my 8 BE nodes? They only have 128 GiB per node, so average of 42.7 GB per node memory just for a single query?
r
No, you do not need to allocate 337 GB of physical RAM to run this query. There is a common misunderstanding of the "Allocated" metric in StarRocks profiles. Here is the breakdown of what those numbers actually mean and how to interpret your results: 1. "Allocated" vs. "Peak" Memory * QueryAllocatedMemoryUsage (337 GB): This is a cumulative value. It represents every single byte the query ever requested from the system during its 11-second run. If StarRocks allocates a 100 MB chunk, processes it, frees it, and then allocates a new 100 MB chunk, the "Allocated" memory will show 200 MB, even though only 100 MB was ever used at one time. * QueryPeakMemoryUsage: This is the "high-water mark" (the maximum amount of RAM used at any single point in time). This is the number that actually determines if you will hit an OOM (Out of Memory) error. In your profile, it shows 
?
(likely because of the specific version or profile collection level), but it is guaranteed to be significantly lower than 337 GB. 2. Why is "Allocated" so high for 20M records? 337 GB for 20 million records works out to roughly 17 KB per record. If your table has many columns or long `VARCHAR`/`JSON` fields, this is normal. As data flows through the pipeline (Scan → Project → Exchange → Sink), StarRocks copies or transforms these blocks of data. Since the "Allocated" metric is cumulative, every time the data moves to a new operator, it adds to that total. 3. The Real Bottleneck: 
ResultDeliverTime
(7.7s) Your profile shows that 68% of the query time is spent in 
ResultDeliverTime
. * This means the 8 BE nodes finished processing the data quickly, but they had to sit and wait for 7.7 seconds for the Frontend (FE) and your SQL client to receive all 20 million rows. * Fetching 20 million rows to a client is usually discouraged in OLAP. If you are doing this to export data, consider using 
SELECT INTO OUTFILE
 which is much more memory-efficient and bypasses the FE. 4. How to find the real memory usage per node To see how much RAM your BE nodes are actually using during the query, look further down in your profile for the Fragment or Instance sections: * Look for 
InstancePeakMemoryUsage
. * Look for 
PeakMemoryUsage
 under the 
OLAP_SCAN_NODE
or 
RESULT_SINK
. Summary & Recommendations: 1. Don't panic about the 337 GB: Your 128 GiB nodes are likely only using a few GBs of "Peak" memory for this query. 2. Check Result Set Size: If you don't actually need 20 million rows in your application, add a 
LIMIT
or more filters. Shipping 20M rows over the network is what's driving the "Allocated" memory and the 11-second delay. 3. Monitor via `SHOW PROC`: While the query is running, you can run 
SHOW PROC '/backends';
in another window to see the 
MemUsed
column. This shows the real-time physical memory usage of the nodes. References • best_practices/query_tuning/query_profile_operator_metrics.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/sql-statements/loading_unloading/SELECT_INTO_OUTFILE.md References • best_practices/query_tuning/query_profile_operator_metrics.md • loading/loading_introduction/troubleshooting_loading.md
m
@Rocky So would paginations alleviate memory pressure? Like query 1 million records by 1million instead of all
r
Yes, pagination will absolutely alleviate memory pressure, particularly on the Frontend (FE) node and your network buffers, but how you paginate makes a massive difference in StarRocks. 1. Why Pagination Helps When you run a 
SELECT
for 20 million rows: 1. BE Level: Each of your 8 BE nodes scans a portion of the data and holds it in memory buffers. 2. FE Level: The FE node acts as the "collector." It must receive, potentially sort, and serialize all 20 million rows before sending them to your SQL client. 3. Client Level: Your client (Python, Java, Go, etc.) must hold those 20 million rows in its own memory. By querying 1 million rows at a time, you ensure that the FE only has to handle 1/20th of the data at any given moment, and your client doesn't need 40+ GB of RAM just to hold the result set. ────────── 2. The "Right" way vs. the "Wrong" way In a distributed database like StarRocks, there are two ways to paginate. One is fast, the other gets slower and more memory-intensive as you go. The "Wrong" Way: 
LIMIT
and 
OFFSET
Copy code
sql
-- Query 1 (First million)
SELECT * FROM requests ORDER BY id LIMIT 1000000 OFFSET 0;

-- Query 20 (The last million)
SELECT * FROM requests ORDER BY id LIMIT 1000000 OFFSET 19000000;
* The Problem: To get the last 1 million rows, StarRocks must still scan and sort all 20 million rows, then throw away the first 19 million. This is called "Deep Pagination" and it will eventually become just as slow/memory-intensive as the original query. The "Right" Way: Keyset Pagination (The Seek Method) Instead of skipping rows by number, skip them by their unique ID or timestamp.
Copy code
sql
-- Query 1 (First million)
SELECT * FROM requests ORDER BY id LIMIT 1000000;
-- Note the last ID received: 1045932

-- Query 2 (Next million)
SELECT * FROM requests WHERE id > 1045932 ORDER BY id LIMIT 1000000;
* The Benefit: StarRocks uses the Primary Key or Short Key index to jump directly to ID 
1045932
. It never touches the first 19 million rows. This keeps memory usage low and execution time consistent for every page. ────────── 3. Better Alternatives for "20 Million Record" Exports If your goal isn't "user-facing pagination" but actually "exporting the data to another system," pagination is actually the slow way. Instead, use one of these: 1. 
SELECT ... INTO OUTFILE
: This tells the BE nodes to write the results directly to a file system (like S3, HDFS, or a shared NFS drive). This completely bypasses the Frontend node's memory and the network bottleneck of shipping rows to your SQL client. 2. 
EXPORT
command: An asynchronous background job that exports data to storage. It is the most memory-efficient way to move millions of rows because StarRocks manages the memory internally and retries if there are issues. Summary Recommendation: * If you are building an App/UI: Use Keyset Pagination (
WHERE id > ... LIMIT 1000000
). * If you are Exporting Data: Use 
SELECT INTO OUTFILE
 to avoid memory pressure on your FE and client entirely. References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • unloading/Export.md • unloading/unload_using_insert_into_files.md • administration/management/resource_management/spill_to_disk.md References • unloading/Export.md • loading/loading_introduction/feature-support-loading-and-unloading.md
m
Copy code
SELECT campaign_id, bid_floor, bid_price, network_id, deal_ids,
         cleaned_domain_key, bid_price_prered, predicted_score,
         experimental_group_type, campaign_pacing
  FROM requests
  WHERE received BETWEEN '2026-04-29 14:00:00' AND '2026-04-29 18:00:00'
    AND has_bid = 1
    AND campaign_id IN (2873537,2711716,3215039,2757926,2711715)
@Rocky How can I paginate this query the right way then?
r
To paginate your query the "right way" (the Seek Method) in StarRocks, you need to follow three steps: add a unique sort key, skip the 
OFFSET
clause, and use a "cursor" in your 
WHERE
clause. Step 1: Add a Unique Sort Key Your current query doesn't have an 
ORDER BY
. For pagination to be consistent, you must sort by a combination of columns that are unique. Since multiple requests can have the exact same 
received
timestamp, you should sort by 
received
plus a unique ID (e.g., 
request_id
). Step 2: The First Page Query Simply add the 
ORDER BY
and your desired page size (e.g., 1 million).
Copy code
sql
SELECT campaign_id, bid_floor, bid_price, network_id, deal_ids,
       cleaned_domain_key, bid_price_prered, predicted_score,
       experimental_group_type, campaign_pacing,
       -- Include these to use as your "cursor" for the next page
       received, request_id 
FROM requests
WHERE received BETWEEN '2026-04-29 14:00:00' AND '2026-04-29 18:00:00'
  AND has_bid = 1
  AND campaign_id IN (2873537,2711716,3215039,2757926,2711715)
ORDER BY received ASC, request_id ASC
LIMIT 1000000;
Note: From this result, record the 
received
and 
request_id
of the last row (e.g., 
2026-04-29 14:30:05
and 
ID: 987654
). ────────── Step 3: The Subsequent Pages (The "Seek") Instead of using 
OFFSET 1000000
, you tell StarRocks to start searching exactly where the last page ended. Option A: Using Tuple Comparison (Cleaner) StarRocks supports tuple comparison, which is the most readable way to do this:
Copy code
sql
SELECT ...
FROM requests
WHERE received BETWEEN '2026-04-29 14:00:00' AND '2026-04-29 18:00:00'
  AND has_bid = 1
  AND campaign_id IN (2873537,2711716,3215039,2757926,2711715)
  -- This is the "Seek" logic:
  AND (received, request_id) > ('2026-04-29 14:30:05', 987654)
ORDER BY received ASC, request_id ASC
LIMIT 1000000;
Option B: Standard Expanded Logic (Most Compatible) If you prefer standard boolean logic, the filter looks like this:
Copy code
sql
  AND (
    received > '2026-04-29 14:30:05' 
    OR (received = '2026-04-29 14:30:05' AND request_id > 987654)
  )
Why this is better for your 337GB Memory "Problem": 1. No "Deep Paging" Penalty: If you used 
OFFSET 19000000
, StarRocks would have to find all 20 million rows, sort them, and discard the first 19 million. With the Seek Method, it uses the index to jump straight to the 19 millionth row. 2. Constant Memory Usage: Every page (1st or 20th) will use the exact same amount of memory because the engine only ever processes 1 million rows at a time. 3. Client Stability: Your application/script only receives 1 million rows at a time, preventing your local machine from crashing or timing out. Rocky's Performance Tip: For this to be lightning-fast, ensure your table's Sort Key (defined in 
ORDER BY
during 
CREATE TABLE
) starts with 
received
. This allows StarRocks to use its Prefix Index to instantly skip millions of rows. 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 • table_design/indexes/vector_index.md • using_starrocks/caching/query_cache.md
m
@Rocky My sort key is order by (received, auction_id) while primary key is received_hour, auction_id, and imp_key. How does this chnage my queyr
r
This schema is actually ideal for the "Seek Method" (keyset pagination) because your Sort Key (
received
, 
auction_id
) is exactly what StarRocks uses to build its physical Prefix Index. Here is how this specific setup changes and optimizes your query: 1. Performance: The Prefix Index Advantage In StarRocks, the 
ORDER BY
you define at the table level (the Sort Key) determines how data is physically laid out on disk. * Because your Sort Key starts with 
received
, StarRocks can use its Prefix Index to skip directly to the exact millisecond in your 
WHERE
clause. * Because 
auction_id
is the second part of that key, the "Seek" (jumping to the last record of the previous page) will also be near-instant. 2. Uniqueness: Sort Key vs. Primary Key For pagination to work correctly, your 
ORDER BY
must be unique. * Primary Key: 
(received_hour, auction_id, imp_key)
ensures every row is unique. * Sort Key: 
(received, auction_id)
is what controls performance. Crucial Check: Is 
(received, auction_id)
unique? In most ad-tech schemas, an 
auction_id
is unique. If one 
auction_id
can have multiple impressions (
imp_key
), then 
(received, auction_id)
is not unique. If it's not unique, pagination might skip or repeat rows. Recommendation: Use your Sort Key for performance, but add the rest of the Primary Key as a "tie-breaker" to ensure 100% accuracy. ────────── The Optimized Pagination Query Page 1:
Copy code
sql
SELECT campaign_id, auction_id, received, imp_key, ... 
FROM requests
WHERE received BETWEEN '2026-04-29 14:00:00' AND '2026-04-29 18:00:00'
  AND has_bid = 1
  AND campaign_id IN (2873537, 2711716, 3215039)
-- Match your Sort Key exactly for max speed, add imp_key for uniqueness
ORDER BY received ASC, auction_id ASC, imp_key ASC
LIMIT 1000000;
Page 2 (The Seek): Assume the last row of Page 1 had: * `received`: 
'2026-04-29 14:30:05'
* `auction_id`: 
'AUC-999888'
* `imp_key`: 
'IMP-123'
Copy code
sql
SELECT ...
FROM requests
WHERE received BETWEEN '2026-04-29 14:00:00' AND '2026-04-29 18:00:00'
  AND has_bid = 1
  AND campaign_id IN (2873537, 2711716, 3215039)
  -- The "Seek" logic using your PK/Sort Key combo
  AND (received, auction_id, imp_key) > ('2026-04-29 14:30:05', 'AUC-999888', 'IMP-123')
ORDER BY received ASC, auction_id ASC, imp_key ASC
LIMIT 1000000;
Why this is better for your memory: 1. Prefix Index Pruning: Since the query starts with 
received
(the first column of your Sort Key), StarRocks uses the Prefix Index to avoid scanning any data outside your 4-hour window. 2. Zero-Offset Penalty: Because you aren't using 
OFFSET
, StarRocks never has to "fetch and discard" rows. It uses the index to jump straight to the first row of the next page. 3. Minimal FE Memory: By fetching only 1 million rows at a time, the Frontend node only needs to buffer a small fraction of the 337GB total "Allocated" memory you saw in the profile. Rocky's Tip: Since you have a Primary Key table, StarRocks is already optimized for high-concurrency updates. Using this pagination method ensures your reads are just as efficient as your writes. References • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md • best_practices/table_clustering.md
m
@Rocky Where do i find storage occupied by bloom filter index
r
In StarRocks, finding the exact storage (disk) occupied by a Bloom Filter index is not as straightforward as a single SQL command like 
SELECT index_size
. This is because Bloom Filter indexes are stored inside the segment files alongside the column data. Here are the three ways to find or estimate this storage: 1. The Metric (Memory Consumption) While this doesn't show disk size directly, it shows how much memory is consumed when these indexes are loaded into the cache on the BE (Backend) nodes. * Metric Name: 
bloom_filter_index_mem_bytes
* Where to find: On any BE node, you can visit the metrics page at 
http://<BE_IP>:<BE_HTTP_PORT>/metrics
(default port is 
8040
). Search for "bloom_filter" to see current memory usage. 2. The Manual Way (Table Comparison) Because Bloom Filters are built during data ingestion or through an 
ALTER TABLE
process, you can check the storage jump: 1. Run 
SHOW DATA;
to see the current size of your table. 2. Add the Bloom Filter index: 
ALTER TABLE <name> SET ("bloom_filter_columns" = "column_id");
3. Wait for the task to finish (
SHOW ALTER TABLE COLUMN;
). 4. Run 
SHOW DATA;
again. The difference is the storage cost. _ _General Expectation:* Bloom filters in StarRocks are highly space-efficient, typically adding less than 1% to the total storage of the column being indexed. 3. The Deep Dive (CLI meta_tool) If you have SSH access to your Backend (BE) nodes, you can use the 
meta_tool
to inspect a segment file directly. This will show the exact breakdown of data, indexes, and footers. 1. Find a tablet's path: 
SHOW TABLET <tablet_id>;
2. Go to that directory on the BE. 3. Run the meta tool (located in the BE 
bin/
directory):
Copy code
bash
    ./bin/meta_tool --operation=show_segment_footer --file=/path/to/segment_file
This output will include a section for 
index_page
, showing the byte size of the Bloom Filter index within that segment. Summary of Index Storage Visibility Index Type | Visibility Bitmap Index | 
SHOW INDEX FROM table;
(Shows presence, not always size) Inverted Index | Can be seen in 
information_schema.be_segments
or 
SHOW DATA
(as part of data). Bloom Filter | Built into segment files. Visible via BE metrics or 
meta_tool
. Rocky's Tip: If you are worried about storage pressure, remember that StarRocks compresses these indexes. A Bloom Filter on a high-cardinality column is almost always worth the tiny storage trade-off for the massive speed boost in 
WHERE column = x
queries. References • table_design/indexes/Bloomfilter_index.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_INDEX.md • knowledge/usage/best-practice.md References • knowledge/usage/best-practice.md • table_design/indexes/Bloomfilter_index.md • administration/management/monitoring/metrics.md • best_practices/query_tuning/schema_tuning.md • table_design/indexes/Bitmap_index.md
8 Views
Open in Slack
PreviousNext
Powered by