@Rocky Is there a way via resource groups or any other built-in StarRocks mechanisms to ensure people never perform a

select *

query? It can be hard to enforce via client side in a big organization. Like big query restriction.

Yes, StarRocks provides two main built-in mechanisms to address this: one that restricts the syntax itself and another that restricts the impact of such queries. 1. SQL Blacklist (Syntax Restriction) If you want to strictly forbid the

SELECT *

syntax globally, you can use the SQL Blacklist feature. This mechanism uses regular expressions to match and reject queries before they are executed. * Step 1: Enable the blacklist feature (it is disabled by default):

sql
    ADMIN SET FRONTEND CONFIG ("enable_sql_blacklist" = "true");

* *Step 2: Add a regex pattern to block `SELECT `: You can use a case-insensitive regex to catch various spacing and casing of the

SELECT *

pattern.

sql
    -- Blocks "SELECT _ FROM ...", "select _ from ...", etc.
    ADD SQLBLACKLIST "(?i)SELECT\\s+\\_\\s+FROM._";

_Note: You must escape the

_

with

\\

in the regex string.* * Management: *

SHOW SQLBLACKLIST;

— View existing rules. *

DELETE SQLBLACKLIST <index_number>;

— Remove a rule. Limitation: The SQL Blacklist is currently a cluster-wide setting. It applies to all users and cannot be easily scoped to specific resource groups via the blacklist command itself. ────────── 2. Resource Groups (Impact Restriction) If your goal is similar to Google BigQuery (where you want to prevent users from scanning massive amounts of data accidentally), Resource Groups are the recommended approach. Instead of blocking the syntax, you block queries that exceed a certain data volume or resource threshold. When you create or alter a Resource Group, you can set "Big Query" limits:

sql
ALTER RESOURCE GROUP your_group_name WITH (
    'big_query_scan_rows_limit' = '1000000', -- Max 1M rows scanned
    'big_query_mem_limit' = '2147483648',   -- Max 2GB memory
    'big_query_cpu_second_limit' = '100'     -- Max 100s CPU time
);

Why this is often better than a syntax block: * Granularity: You can apply these limits to specific users or roles using Classifiers. For example, your Data Scientists might be allowed to scan more, while BI Dashboard users are restricted. * Safety: A

SELECT _

on a 10-row reference table is harmless; a

SELECT _

on a 10-billion-row fact table is the problem.

big_query_scan_rows_limit

only kills the latter. Summary of Options Mechanism | Restricts By | Granularity | Best For SQL Blacklist | Syntax (Regex) | Global | Absolute enforcement of SQL coding standards. Resource Groups | Runtime Usage | Per User/Role | Preventing "expensive" queries (like BQ). igQuery limits). | ). | ). | Pro-tip: In many large organizations, a combination is used: the SQL Blacklist is used to block known dangerous patterns (like

CROSS JOIN

without a limit), while Resource Groups are used to govern the data scanning behavior of

SELECT *

queries. References • administration/management/resource_management/Blacklist.md • administration/management/monitor_manage_big_queries.md • sql-reference/sql-statements/cluster-management/sql_blacklist/ADD_SQLBLACKLIST.md • administration/management/resource_management/resource_group.md

@Rocky How will query queues help me? If I define resource groups, high contention queries will just take longer, same idea with queues? Wall clock time to get result is the same if no contention but queries sit in queue?

Great question! It’s a common misconception that queues are just a way to "delay the inevitable." In reality, query queues provide Admission Control, which is the difference between a system that is "busy" and a system that is "crashing." Here is how query queues help you beyond what simple resource contention does: 1. Throughput vs. Latency (The "Thrashing" Problem) If you have no queue and just let 100 high-concurrency

SELECT *

queries run at once: * Contention: Every query competes for the same CPU cycles and memory. The OS/StarRocks spends massive amounts of time on context switching and managing memory pressure. * Wall Clock Time: Instead of 10 queries finishing in 1 minute and the next 10 in the following minute, all 100 queries try to run at once and might take 20 minutes to finish—or worse, they might all fail due to Out of Memory (OOM) errors. With Queues: StarRocks ensures only an "optimal" number of queries run. By keeping the system at 80-90% utilization instead of 110%, the queries that are running finish much faster. In many cases, the total "wall clock time" (Wait Time + Execution Time) is actually lower with a queue because you avoid the performance degradation of an overloaded system. 2. Predictability & Stability Without a queue, your big queries act like "bulls in a china shop." They can: * Starve Small Queries: A single massive

SELECT *

can grab all the memory, causing a fast 50ms dashboard query to time out. * Fail Randomly: Under heavy contention, queries don't just "take longer"—they often hit the

mem_limit

and die. Queues provide a "Waiting Room": Instead of a query failing because the system is currently busy, it sits in a queue (

IsPending = true

) and starts the moment resources are free. This is much better for user experience than an error message. 3. Graceful Rejection (The "Safety Valve") Queues allow you to set a

query_queue_max_queued_queries

limit. * If the queue gets too long (e.g., 200 queries waiting), StarRocks will immediately reject the 201st query. * This prevents a "death spiral" where a backup of queries continues to grow until the FE or BE nodes become unresponsive. 4. Dynamic Concurrency (v3.1.4+) In newer versions, StarRocks doesn't just make queries wait; it can dynamically adjust. If the system is getting crowded, the query queue can automatically lower the

pipeline_dop

(parallelism) of incoming queries. * This means instead of a query taking 16 cores and making everyone else wait, it might be told to run on 4 cores. It takes slightly longer to run, but it allows more queries to progress simultaneously without hitting the queue. Comparison Summary Feature | Unmanaged Contention | With Resource Groups + Queues System Behavior | Thrashing / OOM risks | High utilization / Stable Wall Clock Time | Unpredictable (can be much higher) | Predictable (Wait + Fast Execution) Small Queries | Get crushed by "Big Queries" | Protected via Resource Group isolation Failure Mode | Queries fail mid-execution (wasted work) | Queries are rejected early or wait to start Rocky's Recommendation: Use Resource Groups to isolate your "Big Query" users, and enable Query Queues to handle the bursts. This ensures that even if someone ignores your advice and runs a

SELECT *

, they only slow themselves down (or wait in their own group's queue) without bringing down the entire cluster for everyone else. Key Variables to check: *

enable_query_queue_select = true

*

query_queue_concurrency_limit

*

query_queue_mem_used_pct_limit

References • administration/management/resource_management/query_queues.md • knowledge/usage/best-practice.md • administration/management/monitor_manage_big_queries.md