http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> How can you introspect the permissi...
# questions-and-troubleshooting
s
@Rocky How can you introspect the permission, roles and grants to determine the default role assigned to a native user ?
r
s
@Rocky I can 
SET GLOBAL activate_all_roles_on_login = ON;
but if I want to clear/reset that variable to is default value, how can I do that "reset to default".
r
To introspect permissions and determine the default role assigned to a native user, as well as manage the 
activate_all_roles_on_login
variable, follow these steps: 1. Introspecting Default Roles To determine which roles are set as "default" for a specific user, the most effective method is to query the 
information_schema.applicable_roles
view. This view contains an 
IS_DEFAULT
column that explicitly identifies default roles. Query Example:
Copy code
sql
SELECT ROLE_NAME 
FROM information_schema.applicable_roles 
WHERE USER = 'your_username' 
  AND IS_DEFAULT = 'YES';
Alternatively, you can use `SHOW GRANTS`: * 
SHOW GRANTS FOR 'user_identity';
will list the roles granted to the user, though it may not distinguish the "default" status as clearly as the 
information_schema
query. 2. Resetting 
activate_all_roles_on_login
 In StarRocks, there is no dedicated "RESET" command for system variables. To "reset" a global variable to its default value, you must manually set it back to that value using the 
SET GLOBAL
command. The default value for 
activate_all_roles_on_login
is 
false
. How to reset it:
Copy code
sql
SET GLOBAL activate_all_roles_on_login = FALSE;
Note on Behavior: * When `TRUE`: All roles assigned to a user are automatically activated upon login, ignoring any specific default roles set. * When 
FALSE
(Default): Only the roles explicitly set via 
SET DEFAULT ROLE
(or the 
public
role if none are set) are activated upon login. 3. Managing Default Roles If you have disabled the "activate all" global variable and want to manage specific default roles for a user, use the following commands: * Set specific default roles: 
SET DEFAULT ROLE <role_name> TO <user_identity>;
* Set all roles as default: 
SET DEFAULT ROLE ALL TO <user_identity>;
* Clear/Reset default roles (to none): 
SET DEFAULT ROLE NONE TO <user_identity>;
(The 
public
role will still be active). References • sql-reference/information_schema/applicable_roles.md • sql-reference/information_schema/column_privileges.md • sql-reference/information_schema/columns.md • sql-reference/sys/grants_to_roles.md
s
@Rocky Look at this example. I set a new session timeout property on this new user. How can I remove a user property ? I want to go back to how was before with only the 3 variable set:
Copy code
StarRocks> create user sr_test identified by 'test';
Query OK, 0 rows affected (0.037 sec)

StarRocks> show properties for 'sr_test';
+----------------------+-----------------+
| Key                  | Value           |
+----------------------+-----------------+
| max_user_connections | 1024            |
| catalog              | default_catalog |
| database             |                 |
+----------------------+-----------------+
3 rows in set (0.033 sec)

StarRocks> alter user 'sr_test' set properties ('session.query_timeout' = '12345');
Query OK, 0 rows affected (0.033 sec)

StarRocks> show properties for 'sr_test';
+-----------------------+-----------------+
| Key                   | Value           |
+-----------------------+-----------------+
| max_user_connections  | 1024            |
| catalog               | default_catalog |
| database              |                 |
| session.query_timeout | 12345           |
+-----------------------+-----------------+
4 rows in set (0.027 sec)
r
To remove a user property and revert to the default state, you set the property value to an empty string (
''
). In StarRocks (starting from version 3.3.3), the 
ALTER USER ... SET PROPERTIES
command uses an empty string to "clear" or "unset" a custom property. How to remove the property: Run the following command for your user:
Copy code
sql
ALTER USER 'sr_test' SET PROPERTIES ('session.query_timeout' = '');
Verification: After running the command above, check the properties again:
Copy code
sql
SHOW PROPERTIES FOR 'sr_test';
The 
session.query_timeout
entry will be removed from the list, returning you to the original three variables:
Copy code
text
+----------------------+-----------------+
| Key                  | Value           |
+----------------------+-----------------+
| max_user_connections | 1024            |
| catalog              | default_catalog |
| database             |                 |
+----------------------+-----------------+
Important Notes: * Username only: When using 
SET PROPERTIES
, you must use the 
username
(e.g., 
'sr_test'
) rather than the full 
user_identity
(e.g., 
'sr_test'@'%'
). * Version requirement: This granular property management is supported from v3.3.3 onwards. * Variable Inheritance: Once cleared, the user will inherit the value from the Global session variables or the system defaults. References • sql-reference/sql-statements/account-management/ALTER_USER.md • administration/user_privs/authentication/native_authentication.md References • sql-reference/sql-statements/account-management/ALTER_USER.md • administration/user_privs/authentication/native_authentication.md
s
Copy code
StarRocks> alter user 'sr_test' set properties ('session.query_timeout' = '');
Query OK, 0 rows affected (0.033 sec)

StarRocks> show properties for 'sr_test';
+----------------------+-----------------+
| Key                  | Value           |
+----------------------+-----------------+
| max_user_connections | 1024            |
| catalog              | default_catalog |
| database             |                 |
+----------------------+-----------------+
3 rows in set (0.027 sec)
that works
@Rocky I'm writing a user and role manager application. Is there a way in StarRocks to create some sort of shared mutual exclusion lock safely that makes it impossible for 2 processes to get hold of the same lock ? For example, with PostgreSQL I would use 
pg_advisory_lock()
; anything similar in StarRocks?
r
StarRocks does not currently have a built-in equivalent to PostgreSQL's 
pg_advisory_lock()
or MySQL's 
GET_LOCK()
. It is primarily an OLAP engine designed for high-concurrency analytics rather than application-level distributed coordination. Here is a breakdown of why this is the case and how you might handle it: 1. No Advisory Lock API StarRocks does not provide session-scoped advisory locks that automatically release upon disconnection. While it uses internal locks (visible via 
sys.fe_locks
), these are reserved for metadata operations (like 
ALTER TABLE
) and cannot be invoked by user applications for custom mutual exclusion. 2. Transaction Behavior Limitations While StarRocks supports SQL transactions (from v3.5/v4.0 onwards), its locking model is not designed for row-level mutual exclusion: * No 
SELECT ... FOR UPDATE
: This common pattern for locking rows in OLTP databases is not supported. * Write Conflict Handling: In StarRocks transactions, if two sessions attempt to update or delete the same data simultaneously, they do not block each other in a traditional "lock wait" state. Instead, they both proceed, and the visibility of changes is determined by the order of the 
COMMIT
statements. 3. Recommended Workarounds If you are building a manager application that requires safe mutual exclusion, the best practices are: * External Distributed Lock Manager (Recommended): Use a tool specifically built for this purpose, such as Redis (Redlock) or ZooKeeper. This is the standard approach for managing coordination in distributed systems where the underlying database is an OLAP engine. * Primary Key Table "Lock" (Simulated): If you must use StarRocks, you can create a Primary Key table to act as a lock registry.
Copy code
sql
    CREATE TABLE application_locks (
        lock_name VARCHAR(64) NOT NULL,
        owner_id VARCHAR(64),
        expires_at DATETIME
    ) PRIMARY KEY (lock_name)
    DISTRIBUTED BY HASH(lock_name);
To "acquire" the lock, you would attempt an 
INSERT
(not an 
UPSERT
). Because the Primary Key table enforces uniqueness, only one process can successfully insert a specific 
lock_name
. However, you would need to manually handle clean-up if a process crashes without deleting the row. In summary: For a production-grade user/role manager application requiring safe mutual exclusion, you should use an external coordinator like Redis rather than attempting to force StarRocks into a locking role it was not designed for. References • loading/SQL_transaction.md • sql-reference/sys/fe_locks.md • table_design/table_types/primary_key_table.md References • loading/SQL_transaction.md • knowledge/usage/best-practice.md
s
@Rocky Are concurrent 
INSERT
on primary table really concurrent safe ? Only one of the conflicting INSERT can ever succeed ? Is this guaranteed ?
r
No, StarRocks is not concurrent-safe for implementing a mutual exclusion lock using standard 
INSERT
statements. While the Primary Key table enforces uniqueness, it does so using UPSERT semantics rather than "Insert or Fail" semantics. Here is why this fails for locking: 1. UPSERT Behavior (Last Writer Wins) In StarRocks, the Primary Key table is designed for high-frequency updates. If two processes attempt to 
INSERT
the same key simultaneously: * Both processes will receive a 
Query OK
(Success) message from the server. * The database will internally handle the conflict by having the "later" version (based on the transaction commit order) overwrite the "earlier" version. * Neither process will receive a "Duplicate Key" error, making it impossible for a process to know if it actually "acquired" the lock or simply "stole" it from someone else. 2. Transactional Isolation Even with StarRocks' multi-statement transactions (introduced in v3.4/v3.5), the database does not support 
SELECT ... FOR UPDATE
or Serializable isolation levels. If you try a "check-then-insert" pattern:
Copy code
sql
-- This is NOT safe for locking in StarRocks
BEGIN;
SELECT count(*) FROM locks WHERE lock_id = 'my_lock'; -- Returns 0 for both processes
INSERT INTO locks (lock_id, owner) VALUES ('my_lock', 'process_A');
COMMIT;
In Snapshot Isolation (the typical model for OLAP engines), both concurrent processes will see 0 rows in their snapshot, both will perform the 
INSERT
, and both will 
COMMIT
successfully. The final state of the table will just show the last one to commit. 3. Is there a "Guaranteed" way? There is currently no built-in guarantee or configuration (like a 
STRICT_INSERT
mode) that would cause an 
INSERT
to fail on a duplicate key in a Primary Key table. Recommendation For a manager application, you should use an external system that provides linearizability and atomic "Compare-and-Swap" (CAS) or "Insert-if-not-exists" operations: * Redis: Use 
SET lock_key value NX PX 30000
(NX = Only set if not exists). * Etcd / ZooKeeper: Use their native distributed lock recipes. * PostgreSQL: Continue using 
pg_advisory_lock()
. StarRocks is optimized for "writing data as fast as possible" rather than "coordinating distributed application state coordination between distributed application instances." References • table_design/table_types/primary_key_table.md • loading/SQL_transaction.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • table_design/table_types/duplicate_key_table.md
s
@Rocky But if after INSERT you select the lock and compare the lock owner_id (globally unique id's) against yours and it matches, would this be safe if table is distributed on the lock_name ?
r
No, the "Insert then Select" pattern is not safely concurrent in StarRocks for a mutual exclusion lock. Even if the table is distributed on the 
lock_name
(ensuring the specific key is handled by a specific bucket/tablet), it is not safe due to how StarRocks handles Primary Key conflicts and visibility. Why it is not safe: 1. UPSERT vs. INSERT: StarRocks Primary Key tables do not have an "Insert or Fail" mode. If two processes (A and B) attempt to insert the same 
lock_name
, both will receive a 
Query OK
. StarRocks treats this as an *UPSERT*—it simply replaces the existing value with the newest one. 2. The "Dual Winner" Race Condition: * Process A inserts 
(lock1, ID_A)
. The transaction is committed. * Process B inserts 
(lock1, ID_B)
. The transaction is also committed (overwriting ID_A). * Process A executes its 
SELECT
. If B's commit has not yet finished its "Publish" phase or if A's snapshot was taken slightly earlier, A will see 
ID_A
and believe it won. * Process B executes its 
SELECT
. It will see 
ID_B
and believe it won. * Result: Both processes believe they hold the same lock simultaneously. 3. No Locking Reads: StarRocks does not support 
SELECT ... FOR UPDATE
or 
SERIALIZABLE
isolation. In PostgreSQL, 
pg_advisory_lock
or a PK-based insert-fail pattern relies on the database engine blocking other sessions at the row level until the transaction is resolved. StarRocks is designed for high-throughput analytics and avoids these types of heavy-weight row locks. Recommendation For a user/role manager application, you should handle mutual exclusion at the application layer or using a system designed for distributed coordination: * Redis (Recommended): Use the 
SET lock_key unique_id NX PX 30000
command. The 
NX
flag ensures the key is only set if it does not already exist, providing an atomic "Insert-if-not-exists." * ZooKeeper / etcd: These are purpose-built for distributed locking and handle session timeouts/crashes automatically. * Relational DB: If your application already uses a relational database like PostgreSQL for metadata, use its 
pg_advisory_lock
or a standard table with a Unique Key (where the duplicate 
INSERT
will actually throw an error). Summary: Do not use StarRocks for distributed locking. Its storage engine is optimized for "Last Writer Wins" and will not provide the strict atomicity required for a safe mutual exclusion. References • table_design/table_types/primary_key_table.md • loading/SQL_transaction.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • table_design/table_types/primary_key_table.md • table_design/table_types/duplicate_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/table_types.md
s
Got it makes sense
@Rocky Can you mitigate this to some extend by adding a sleep between the INSERT and SELECT so that any inconsistent visibility has time to propagates around ? I know this is now "correct" but with a long enough multi seconds delay that could be "good enough" ?
r
While adding a delay (a "sleep") between the 
INSERT
and the 
SELECT
makes a race condition less likely, it still does not provide a safe or guaranteed mutual exclusion lock in StarRocks. It is "probabilistic" rather than "deterministic," which is generally unacceptable for application-level state management. Here is why this approach remains unsafe: 1. The fundamental "Insert" problem In a traditional database (PostgreSQL/MySQL), an 
INSERT
into a Unique Key column fails if the key already exists. This failure is the signal to the second process that it lost the lock. In StarRocks, the 
INSERT
into a Primary Key table always succeeds (it's an UPSERT). Because both processes receive a "Success" message immediately, they both believe they have successfully written their claim to the lock. 2. Snapshot Isolation and the "Double Winner" StarRocks uses Snapshot Isolation. Even with a delay, you can run into this scenario: 1. Process A inserts 
(lock_1, owner_A)
and commits. 2. Process B inserts 
(lock_1, owner_B)
and commits. 3. Process A sleeps for 5 seconds. 4. Process B sleeps for 5 seconds. 5. Process A starts a 
SELECT
. Depending on the exact timing of the "Version Publish" phase in the StarRocks FE (Front End), Process A might still see its own version (
owner_A
) because Process B’s version hasn't been fully finalized/published globally yet. 6. Process B starts a 
SELECT
and sees 
owner_B
. 7. Result: Both processes verify themselves as the "owner" and proceed to run the protected code simultaneously. 3. The "Last Writer Wins" Risk If Process B overwrites Process A's record, Process A might see that it is no longer the owner and back off (which is good). However, if there is any network jitter or a long Garbage Collection (GC) pause in the StarRocks FE during the "Publish" phase, the time it takes for a write to become visible to all subsequent 
SELECT
queries is variable. There is no "Wait for Sync" command that guarantees you are reading the absolute latest committed state across the whole cluster. 4. Lack of Session Awareness Even if the delay worked, StarRocks has no concept of "Session Liveness" for data rows. * In PostgreSQL 
pg_advisory_lock
, if the process crashes, the lock is automatically released. * In your StarRocks "table lock" model, if your application process crashes after the 
INSERT
but before the work is done, the lock row stays in the table forever. You would have to write a complex "reaper" process to clean up expired locks based on timestamps, which introduces its own set of race conditions. Summary Using StarRocks for locking with a 
SLEEP
is effectively "Hope-Based Locking." * For a hobby project: It might work 99.9% of the time. * For a production User/Role Manager: It is dangerous. A single race condition could lead to corrupted permissions or duplicate identity records that are very hard to clean up later. The Correct Path: If you cannot use Redis or ZooKeeper, and you have a relational database (like PostgreSQL, MySQL, or SQL Server) anywhere in your stack, use that for the lock. If you only have StarRocks, it is better to design your application to be idempotent (so that running the same operation twice is harmless) rather than trying to build a lock on an engine that doesn't support it. References • table_design/table_types/primary_key_table.md • loading/SQL_transaction.md • sql-reference/sql-statements/loading_unloading/INSERT.md
s
@Rocky It's a bit hard because not all USER/ROLE DDL operations have 
IF NOT EXISTS
. i'll check for a global lock or a way to ensure we cannot have more than 2 such process active at a time
r
You are correct that the lack of 
IF NOT EXISTS
for some DDL operations (like 
CREATE ROLE
in some versions or complex 
GRANT
sequences) makes concurrent automation difficult. Since StarRocks does not provide a native 
GET_LOCK()
function or 
SERIALIZABLE
transactions for data tables, you have two main options to coordinate your User/Role manager. 1. The "DDL Lock" Trick (Internal to StarRocks) Unlike the Primary Key table (which uses "Last Writer Wins" UPSERT semantics), StarRocks Metadata (DDL) is managed by the Leader FE and is strictly consistent. If you try to create a role that already exists, the Leader FE will reject it with an error. You can use a "dummy" Role as a global mutex: 1. Acquire Lock: Run 
CREATE ROLE execution_lock;
* Success: You have the lock. * Failure (Error: 
Role 'execution_lock' already exists
): Another process is running. Wait and retry. 2. Execute: Run your 
CREATE USER
, 
CREATE ROLE
, and 
GRANT
statements. 3. Release Lock: Run 
DROP ROLE execution_lock;
Warning: If your process crashes during step 2, the "lock" remains. You would need a timeout mechanism (e.g., check the creation time of the role and force-drop it if it's older than 10 minutes). 2. Verify 
IF NOT EXISTS
Availability Check your StarRocks version, as support has been expanding: * 
CREATE USER [IF NOT EXISTS]
: Available since v3.3. * 
CREATE ROLE
: Does not currently support 
IF NOT EXISTS
(as of v3.3/3.4). * 
GRANT
: This operation is idempotent by nature. If you grant a role to a user who already has it, StarRocks will simply return 
OK
without error. 3. External Coordination (Recommended) If you are running these processes from a container or a script, the most robust way is to use an external coordinator: * Kubernetes: Use a 
Job
(which ensures only X completions) or a 
Singleton
pod. * Database-based Lock: If you have a side-metadata DB (like a small MySQL/Postgres instance), use 
GET_LOCK()
or a unique constraint there. * Distributed Lock: Use Redis (Redlock) or Etcd. Summary of Idempotency for User/Role Management: Operation | Supports 
IF NOT EXISTS
 | Behavior on Duplicate 
CREATE USER
| Yes (v3.3+) | Fails if not using 
IF NOT EXISTS
CREATE ROLE
| No | Fails (Use this as your lock signal) 
GRANT
| N/A | Succeeds (Idempotent) 
DROP USER
| Yes | Fails if not using 
IF EXISTS
Rocky's Advice: Use the 
CREATE ROLE
failure as your "lock." It is handled by the FE's internal state machine and is globally consistent across the cluster, unlike the Primary Key data tables we discussed earlier. References • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/sql-statements/account-management/CREATE_ROLE.md • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/GRANT.md References • sql-reference/sql-statements/account-management/CREATE_ROLE.md • sql-reference/sql-statements/account-management/CREATE_USER.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authentication/native_authentication.md
s
@Rocky For regular read-only application user, is the 
USAGE
grant on the 
default_catalog
needed ? For example if I give SELECT permission on a table living inside a database inside the default_catalog, will the user be able to select data on that table if it is missing USAGE permission on the default catalog?
r
Yes, a 
USAGE
grant on the 
default_catalog
is required for a read-only user in StarRocks (specifically in versions 3.0 and later with the new RBAC system). Even if you grant 
SELECT
on a specific table or database, the user will likely encounter a "Access denied" or "No permission" error when trying to query it if they do not have 
USAGE
on the parent catalog. Why it is needed: 1. Hierarchical Privilege Model: StarRocks follows a 
Catalog -> Database -> Table
hierarchy. To access any "child" object, the user must have the 
USAGE
privilege on the "parent" object to "enter" that namespace. 2. Official Best Practices: The StarRocks documentation for creating a 
read_only
role explicitly includes the 
USAGE
grant as the first step:
Copy code
sql
    -- Create a role.
    CREATE ROLE read_only;

    -- Step 1: Grant USAGE on the catalog (required to 'see' and 'use' the catalog)
    GRANT USAGE ON ALL CATALOGS TO ROLE read_only;
    -- Alternatively, for just the default catalog:
    -- GRANT USAGE ON CATALOG default_catalog TO ROLE read_only;

    -- Step 2: Grant SELECT on the actual data
    GRANT SELECT ON ALL TABLES IN ALL DATABASES TO ROLE read_only;
3. Context Switching: Commands like 
USE <db_name>
or 
SET CATALOG default_catalog
will fail without the 
USAGE
privilege. While the MySQL client connects to the 
default_catalog
by default, the authorization engine still checks if the user has the right to "use" that catalog when they execute a query. Exceptions and Edge Cases: * The 
public
role: By default, some clusters may have 
USAGE
on the 
default_catalog
granted to the 
public
role. Since every user belongs to 
public
, it might appear to work without an explicit grant. However, for a strictly managed environment where you have revoked default permissions, you must grant it explicitly. * Legacy Versions (Pre-3.0): In older versions, the catalog concept didn't exist in the same way, and permissions were often database-level. In 3.x, the catalog is a first-class object in the security model. Rocky's recommendation: Always include 
GRANT USAGE ON CATALOG default_catalog TO ROLE <role_name>;
in your user setup scripts to avoid "No permission" errors during metadata discovery or query execution. References • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/Catalog/SET_CATALOG.md
s
@Rocky I noticed that creating a new users without any explicit properties set still returns a few properties in SHOW PROPERTIES. Is there a way to avoid those ? It confuses my reconciliation logic managing properties:
Copy code
StarRocks> create user sr_user_a identified by 'a';
Query OK, 0 rows affected (0.029 sec)

StarRocks> show properties for 'sr_user_a';
+----------------------+-----------------+
| Key                  | Value           |
+----------------------+-----------------+
| max_user_connections | 1024            |
| catalog              | default_catalog |
| database             |                 |
+----------------------+-----------------+
3 rows in set (0.037 sec)
r
No, there is currently no way to avoid or hide these properties. In StarRocks, 
max_user_connections
, 
catalog
, and 
database
are built-in, mandatory system properties for every user. Even if you do not specify them during 
CREATE USER
, the system populates them with default values: * 
max_user_connections
: Defaults to 
1024
. * 
catalog
: Defaults to 
default_catalog
. * 
database
: Defaults to an empty string. Why you cannot remove them: These are not just "metadata tags"; they are functional configuration points for the FE (Front End). For example, 
max_user_connections
is checked at the moment of login to prevent connection exhaustion, and `catalog`/`database` define the initial session context. Because they must always have a value for the user to function, StarRocks displays the active effective value in 
SHOW PROPERTY
. Recommendation for Reconciliation Logic: To make your automation robust against these "phantom" properties, you should adjust your logic to ignore specific keys or treat default values as "empty/null". 1. Filter the output: When parsing the result of 
SHOW PROPERTY
, explicitly ignore the three keys: 
max_user_connections
, 
catalog
, and 
database
unless they deviate from the system defaults. 2. Define a "Null State": In your reconciliation tool, treat the following as the "base state" for a user:
Copy code
json
    {
      "max_user_connections": "1024",
      "catalog": "default_catalog",
      "database": ""
    }
If the 
SHOW PROPERTY
output matches exactly this, your logic should consider the user to have "no custom properties." Note on v3.3.3+ If you are using StarRocks v3.3.3 or higher, you can also set session variables at the user level (e.g., 
SET PROPERTIES ("session.query_timeout" = "300")
). These will also appear in 
SHOW PROPERTY
prefixed with 
session.
. Your reconciliation logic should be prepared to handle these as well. References • sql-reference/sql-statements/account-management/SHOW_PROPERTY.md • sql-reference/sql-statements/account-management/ALTER_USER.md • sql-reference/sql-statements/account-management/CREATE_USER.md References • sql-reference/sql-statements/account-management/SHOW_PROPERTY.md • administration/user_privs/authentication/native_authentication.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • sql-reference/sql-statements/account-management/ALTER_USER.md • sql-reference/sql-statements/account-management/CREATE_USER.md
s
@Rocky The actual values of 1024 and default_catalog, could they ever be different ? Where does that "default" effective value comes from. I worry about defaults changing accross version and throwing off the code.
r
The values you see are hardcoded defaults within the StarRocks FE (Front End) source code. Because they are part of the system's core logic for session management, they are automatically assigned to every user identity upon creation. 1. Where do these defaults come from? * 
max_user_connections
(1024): This is the default per-user limit for concurrent connections. _ _Note on changes:* While this specific user property has been 1024 for a long time, the cluster-wide global limit (
qe_max_connection
) was recently increased from 1024 to 4096 (in v3.1.12+ and v3.2.7+). It is possible the per-user default could be bumped in a future major release to align with higher-scale hardware. * 
catalog
(
default_catalog
): This is the identifier for StarRocks' internal storage engine. In StarRocks 2.3+, the internal catalog is hardcoded as 
default_catalog
. This name is fixed and cannot be changed, though a user can be configured to land in a different (external) catalog by default. * 
database
(empty): Every user defaults to no specific database context until they run a 
USE
command or have one explicitly set. 2. Can they be different? Yes, they can be different in two scenarios: 1. Manual Override: If someone runs 
ALTER USER 'user' SET PROPERTIES ("max_user_connections" = "500")
, the value changes for that specific user. 2. Version Upgrades: As mentioned with 
qe_max_connection
, StarRocks maintainers occasionally update default thresholds to reflect modern production requirements. 3. How to make your reconciliation logic "Future-Proof" Since these defaults can change across versions, hardcoding 
1024
or 
default_catalog
in your code is a brittle strategy. Instead, use one of these two patterns: Pattern A: The "Dummy User" Baseline (Recommended) When your reconciliation service starts, create a temporary "baseline" user, run 
SHOW PROPERTY
, and store those values as the "System Default" map. 1. 
CREATE USER reconciliation_baseline_tmp;
2. 
SHOW PROPERTY FOR reconciliation_baseline_tmp;
(Store these keys/values). 3. 
DROP USER reconciliation_baseline_tmp;
4. In your logic, only mark a property as "managed" or "changed" if it differs from this baseline. Pattern B: Explicitly Ignore System Keys If you only care about custom properties (like those added in v3.3+ for session variables), you can explicitly ignore the "Big Three":
Copy code
python
# Pseudo-code
def get_custom_properties(user):
    all_props = run_sql(f"SHOW PROPERTY FOR '{user}'")
    system_keys = {'max_user_connections', 'catalog', 'database'}
    return {k: v for k, v in all_props if k not in system_keys}
Rocky's Summary: These defaults are part of the StarRocks "system state." Because the StarRocks project evolves, the safest way to reconcile is to treat the values returned by a freshly created user as the "zero state" rather than assuming specific strings or static numbers. References • administration/management/FE_configuration.md (See 
qe_max_connection
) • sql-reference/sql-statements/account-management/SHOW_PROPERTY.md • data_source/catalog/default_catalog.md References • data_source/catalog/default_catalog.md • sql-reference/sql-functions/utility-functions/catalog.md • sql-reference/System_variable.md • sql-reference/sql-statements/account-management/SHOW_PROPERTY.md
s
@Rocky I'm running a test database with 3 tables with replication factor of 3, daily partitionning and distributed by 8 hash buckets. Is it fair to say I should have 3 tables * replication 3 * 7 days * 8 buckets == 504 tablets ? I see 648 total tablets (216 for each table with some having datasize 0). Why is this different?
r
Your math is correct ($Tables \times Replicas \times Partitions \times Buckets = Total\ Tablets$), but the discrepancy (648 vs 504) indicates that each of your tables actually has 9 partitions, not 7. Based on your observation of 216 tablets per table ($216 \div (3\ replicas \times 8\ buckets) = 9$): Why you have 9 partitions instead of 7: If you are using Dynamic Partitioning, StarRocks manages the partition lifecycle based on your properties. You likely have a configuration similar to this: * Historical/Current Partitions: You might have 7 partitions for the "past" and "today." * Future Partitions (
end
property): By default or through configuration, StarRocks often creates partitions for the future to ensure there is a place for incoming data. If 
dynamic_partition.end
is set to 
2
, it creates partitions for "today + 1" and "today + 2." This results in $7 + 2 = 9$ partitions. The partitions with datasize 0 are almost certainly these future partitions (or historical ones where no data has been loaded yet). How to verify: You can see the exact list of partitions and their creation times by running:
Copy code
sql
SHOW PARTITIONS FROM <table_name>;
Count the rows in that output; you will likely see 9 rows per table. Summary of the calculation for your setup: * Tablets per Partition: $8 \text{ buckets} \times 3 \text{ replicas} = 24 \text{ tablets}$ * Tablets per Table: $24 \text{ tablets/partition} \times 9 \text{ partitions} = 216 \text{ tablets}$ * Total for 3 Tables: $216 \times 3 = 648 \text{ tablets}$ How to adjust it: If you want to reduce the tablet count to exactly 504, you would need to adjust your 
dynamic_partition
properties (specifically 
start
and 
end
) so that the total number of partitions equals 7. However, having a few empty future partitions is considered a best practice in StarRocks to prevent load failures when the date rolls over. References • table_design/data_distribution/dynamic_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • table_design/data_distribution/Data_distribution.md References • table_design/data_distribution/dynamic_partitioning.md
s
@Rocky There are 8 partitions because my test dataset of 7 days lands midday (not at 00:00 UTC partition boundary).
r
If you see 648 total tablets for 3 tables with 8 buckets and a replication factor of 3, the math confirms that you have exactly 9 partitions per table: $$\frac{648 \text{ tablets}}{3 \text{ tables} \times 3 \text{ replicas} \times 8 \text{ buckets}} = \mathbf{9 \text{ partitions}}$$ Even though your data span covers 8 calendar days, you are seeing a 9th partition for one of the following reasons: 1. The "Future" Partition (
dynamic_partition.end
) If you are using Dynamic Partitioning, StarRocks is designed to pre-create partitions for the future so that data loading doesn't fail when the clock strikes midnight. * The property 
dynamic_partition.end
(which is required when enabling this feature) specifies how many future partitions to keep. * If 
dynamic_partition.end
is set to 
1
, StarRocks will always ensure there is a partition for "tomorrow." * Result: 8 days of data + 1 future partition = 9 partitions. 2. The "Today" vs "Tomorrow" Boundary If your test "7-day" window starts at midday on Day 1 and ends at midday on Day 8, that accounts for 8 partitions. However, if the current system time on your StarRocks cluster is already on "Day 8" (or Day 9), the dynamic partitioning daemon may have already triggered the creation of the next day's partition. 3. Explicit Creation If you created the table with a syntax like 
START ("2023-01-01") END ("2023-01-08") EVERY (INTERVAL 1 DAY)
, check if the range is inclusive or exclusive. In StarRocks, 
VALUES LESS THAN ("2023-01-08")
excludes the 8th. To cover 8 full days of data, you often end up with a 9th boundary. How to confirm: Run the following command to see the exact names and ranges of the partitions:
Copy code
sql
SHOW PARTITIONS FROM <table_name>;
You will see 9 rows. Look at the 
PartitionName
and the 
LessValue
. One of them will likely have a date in the future (relative to your data) and a 
DataSize
of 
0B
. Summary Your logic is correct, but the system is behaving as intended by maintaining a "buffer" partition. This prevents your ingestion pipeline from crashing the moment the current day ends. References • table_design/data_distribution/dynamic_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • table_design/data_distribution/Data_distribution.md References • table_design/data_distribution/dynamic_partitioning.md
s
@Rocky 648.0 / 8.0 / 3.0 / 3.0 == 9.0 - This means there would be 9 partition but SHOW PARTITION says 8, not 9.
r
The math you are using is correct: Tablets = Tables × Partitions × Buckets × Replication Factor. For your setup of 3 tables, 8 partitions (daily for 7 days + 1 extra for the midday boundary), 8 buckets, and 3 replicas, you should indeed have 576 tablets ($3 \times 8 \times 8 \times 3$). The fact that you see 648 total tablets (exactly 216 per table) means that StarRocks is actually managing 9 partitions worth of tablets for each table ($216 / 3 \text{ replicas} / 8 \text{ buckets} = 9$). Since 
SHOW PARTITIONS
only lists 8, the "missing" partition is likely one of the following: 1. Temporary Partitions (The most likely cause) If you have used 
INSERT OVERWRITE
or manually ran 
ADD TEMPORARY PARTITION
, StarRocks creates "Temporary Partitions." These have their own tablets but do not show up in a standard 
SHOW PARTITIONS
command. * Why they are there: 
INSERT OVERWRITE
creates a temporary partition to load data into before atomically swapping it with the real one. If a load is currently running, failed, or was recently completed, the tablets for that temporary partition might still exist. * How to check: Run 
SHOW TEMPORARY PARTITIONS FROM <table_name>;
. * Tablet Count: 1 temporary partition $\times$ 8 buckets $\times$ 3 replicas = 24 tablets. * Total: 192 (formal) + 24 (temporary) = 216 tablets. 2. The Recycle Bin (Trash) StarRocks does not immediately delete tablets when a partition is dropped or overwritten. It moves them to a "Recycle Bin" (controlled by the FE config 
catalog_trash_expire_second
, default is 24 hours). * If you recently performed an 
INSERT OVERWRITE
on one partition, the old partition's 24 tablets are still in the trash, while the new partition is visible in 
SHOW PARTITIONS
. * This would account for exactly 216 tablets per table until the trash is emptied. 3. Materialized Views or Rollups In StarRocks, Materialized Views (and internal Rollup indexes) are separate storage objects. * If you created a single-partition Materialized View for each table with the same 8-bucket distribution, it would add exactly 24 tablets ($1 \times 8 \times 3$) to the total count associated with that table identity. * How to check: Run 
DESC <table_name> ALL;
to see if there are any Rollup indexes or MVs. Summary Checklist To find exactly where those extra 24 tablets per table are coming from, run these commands: 1. 
SHOW TEMPORARY PARTITIONS FROM <table_name>;
 (Check for overwrite remnants). 2. 
SHOW PROC '/cluster_balance/tablet_info';
 (This provides a global view of all tablets, including those in the trash or belonging to internal processes). 3. Check for MVs: 
SELECT * FROM information_schema.materialized_views WHERE table_name = '<your_table>';
The "datasize 0" you observed is typical for either a newly created Temporary Partition or a Materialized View that hasn't finished its first refresh. References • table_design/data_distribution/dynamic_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • administration/management/FE_configuration.md (Search for 
catalog_trash_expire_second
) References • knowledge/usage/best-practice.md • table_design/data_distribution/Data_distribution.md • table_design/StarRocks_table_design.md • faq/Deploy_faq.md • faq/shared_data_faq.md
s
@Rocky Is this table design efficient for a multi tenant table on account_key. The same interaction_id can appear in 2 different conversation_id. All SELECT queries will always filter on a single account_key and a start_time range expression. Could the primary key be listed in a different order ? conversation_id and interaction_id are very high cardinality (millions per day accross all accounts). Accounts on the order of ~ 100000.
Copy code
CREATE TABLE interactions (
    account_key        BIGINT       NOT NULL,
    conversation_id    VARCHAR(36)  NOT NULL,
    interaction_id     VARCHAR(36)  NOT NULL,
    start_time         DATETIME     NOT NULL,
    organization_id    VARCHAR(36)  NOT NULL,
    participant_type   VARCHAR      NOT NULL,
    user_key           BIGINT,
    user_id            VARCHAR(36),
    channel_type       VARCHAR      NOT NULL,
    is_originator      BOOLEAN      NOT NULL,
    end_time           DATETIME     NOT NULL,
    outcome            VARCHAR,
    direction          VARCHAR      NOT NULL
)
PRIMARY KEY (account_key, conversation_id, interaction_id, start_time)
PARTITION BY date_trunc('day', start_time)
DISTRIBUTED BY HASH(account_key)
ORDER BY (account_key, start_time)
PROPERTIES (
    "partition_live_number" = "14"
);
r
Your current table design is very efficient for the workload you described. Because you are using the Primary Key model, StarRocks allows you to decouple the uniqueness constraint (
PRIMARY KEY
) from the physical storage order (
ORDER BY
), which you have utilized correctly. Here is a breakdown of why this design works and a few optimizations to consider: 1. The "Order By" is the most critical part In StarRocks, the 
ORDER BY
clause determines the Sort Key. This key is used to build the Prefix Index (the first 36 bytes of the row) and to sort data within each tablet. * Your Design: 
ORDER BY (account_key, start_time)
* Why it's efficient: Since your queries always filter on a single 
account_key
and a 
start_time
range, StarRocks can use the prefix index to skip over irrelevant data blocks entirely. * Order of Sort Key: Putting 
account_key
first is correct because it is used for an equality filter (
=
), followed by 
start_time
for the range filter (
BETWEEN
or 
>
). 2. Primary Key Column Order The order of columns in the 
PRIMARY KEY (...)
statement primarily affects the Primary Index (used for upsert/delete lookups). * Constraint: In the Primary Key model, all columns used in 
PARTITION BY
and 
DISTRIBUTED BY
must be part of the Primary Key. You have satisfied this by including 
start_time
and 
account_key
. * Order recommendation: It is generally best to put columns that are frequently updated or have high locality together. However, your current order 
(account_key, conversation_id, interaction_id, start_time)
is standard and shouldn't cause performance issues. * Memory Tip: The Primary Index is stored in memory (roughly 30-40 bytes per row). With millions of interactions per day and a 14-day retention, ensure your BE nodes have enough RAM to hold this index (e.g., 100M rows $\approx$ 4GB RAM). 3. Distribution by 
account_key
 * Efficiency: 
DISTRIBUTED BY HASH(account_key)
is excellent for multi-tenancy. It ensures that all data for a specific account resides in the same set of tablets (buckets). When you query 
WHERE account_key = 123
, StarRocks only hits the specific buckets for that account, significantly reducing I/O. * Skew Check: Since you have 100,000 accounts, the data should be well-distributed across your 8 buckets. If you have "whale" accounts (one account having 50%+ of all data), you might see some performance variance on those specific buckets, but for 100k accounts, this is rarely an issue. 4. Can the PK be in a different order? Yes, it could be, but it likely won't change your query performance because your 
ORDER BY
is already optimized. * If you moved 
start_time
earlier in the 
PRIMARY KEY
, it might slightly improve the locality of the Primary Index during ingestion (if data arrives in time-order), but it's a marginal gain. * Uniqueness: If 
interaction_id
is unique only within a conversation, then 
(conversation_id, interaction_id)
must remain in the PK. If 
interaction_id
were globally unique, you could potentially remove 
conversation_id
from the PK to save index memory, but only if your business logic allows it. Suggested Minor Tweaks: * Bucket Count: You mentioned 8 buckets in your previous question. If you are seeing "millions per day" per table, and you keep 14 days, check the size of your tablets. StarRocks performs best when tablets are between 100MB and 1GB. If your partitions are very large (e.g., >20GB per day), you should increase the bucket count from 8 to something higher (like 16 or 32). * Prefix Index Limit: The prefix index is 36 bytes. * 
account_key
(BIGINT) = 8 bytes. * 
start_time
(DATETIME) = 8 bytes. * Total = 16 bytes used. You have 20 bytes left for the next column in the 
ORDER BY
if you ever decide to add one. Summary
Your design is a textbook example of efficient multi-tenant table design in StarRocks. The combination of Account-based Distribution and Account + Time-based Sorting will provide sub-second latency for the query patterns you've described. References • table_design/table_types/primary_key_table.md • best_practices/table_clustering.md • table_design/StarRocks_table_design.md • knowledge/usage/best-practice.md References • knowledge/usage/best-practice.md • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • best_practices/table_clustering.md
s
@Rocky Tell me more about this concept of prefix index of 36 bytes you mentionned
r
In StarRocks, the Prefix Index is a lightweight, in-memory "map" used to skip over irrelevant data blocks during a query. It is one of the primary reasons StarRocks can achieve sub-second response times on massive datasets. Here is a breakdown of how the 36-byte rule and the indexing logic work: 1. How it is Created When data is written to a table: * Sorting: StarRocks sorts the data based on your 
ORDER BY
columns (or Key columns if no 
ORDER BY
is specified). * Blocking: It divides these sorted rows into logical blocks of 1,024 rows each. * Indexing: It takes the values of the first row in every block and stores them in the Prefix Index. 2. The 36-Byte & 3-Column Limits To keep the index small enough to fit entirely in the BE (Backend) node's RAM, StarRocks imposes three strict limits on what goes into a single index entry: 1. Total Length: An entry cannot exceed 36 bytes. 2. Column Count: An entry cannot include more than 3 columns. 3. The "VARCHAR" Rule: If a 
VARCHAR
or 
STRING
column is encountered, the index entry stops after that column, even if you haven't reached 36 bytes or 3 columns yet. 3. Applying this to your Table In your table design: 
ORDER BY (account_key, start_time)
* 
account_key
(BIGINT): Takes 8 bytes. * 
start_time
(DATETIME): Takes 8 bytes. * Total: 16 bytes. Since 16 is less than 36, and you only have 2 columns, your entire sort key fits perfectly into the Prefix Index. When you run 
WHERE account_key = 123 AND start_time > '2023-01-01'
, StarRocks performs a binary search on this 16-byte index in RAM to find exactly which 1,024-row blocks might contain your data. It then only reads those specific blocks from the disk. 4. What happens if you exceed the limit? If you had a very long Sort Key, such as 
ORDER BY (string_id, account_key, start_time)
where 
string_id
is a 40-character VARCHAR: 1. StarRocks would take the first 36 bytes of 
string_id
. 2. It would stop there. 3. 
account_key
and 
start_time
would not be in the Prefix Index. In that scenario, a query filtering on 
account_key
would be much slower because the index couldn't help narrow down the search—it would only know about the 
string_id
. Summary of Benefits * Sparse Index: Because it only stores 1 entry per 1,024 rows, it is 1,000x smaller than the actual data. * In-Memory: It is always loaded in RAM for lightning-fast lookups. * Binary Search: It turns a massive table scan into a fast $O(\log N)$ search. References • table_design/indexes/Prefix_index_sort_key.md • knowledge/usage/best-practice.md • table_design/StarRocks_table_design.md References • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md
s
@Rocky based on this BIGINT + DATETIME order by clause, can you estimate the FE memory requirements so that this fits entirely in RAM for say 300 million rows over a table partitionned daily and distributed over say 11 hash buckets ?
r
To accurately estimate memory, we need to distinguish between Frontend (FE) metadata and Backend (BE) indexing. In StarRocks, the Prefix Index (which uses your 
ORDER BY
clause) is stored on the BE nodes, not the FE. The FE primarily stores metadata about where those tablets are located. 1. FE Memory Requirements (Metadata) For the FE, memory usage is driven by the number of tablets, not the number of rows. * Tablet Count: 14 partitions × 11 buckets × 3 replicas = 462 tablets. * Memory Estimate: StarRocks official guidelines recommend a minimum of 16GB RAM for any cluster with fewer than 1 million tablets. Since you only have 462 tablets, your FE memory usage for this table will be negligible (measured in megabytes). 2. BE Memory: Prefix Index (Sort Key) The Prefix Index is a sparse index (1 entry per 1,024 rows) that lives in the BE cache. * Rows: 300,000,000 * Entries: $300,000,000 / 1,024 \approx 293,000$ entries. * Size per entry: Your 
(BIGINT, DATETIME)
sort key uses 16 bytes. * Total RAM: $293,000 \times 16 \text{ bytes} \approx \mathbf{4.7 \text{ MB}}$ per replica. * Verdict: This is extremely lightweight and will easily fit in the BE's RAM. 3. BE Memory: Primary Index (The real factor) Since you are using a Primary Key table, the Primary Index (mapping PKs to row locations) is the largest memory consumer. For 300M rows with an 88-byte PK (
BIGINT
+ 
VARCHAR(36)
+ 
VARCHAR(36)
+ 
DATETIME
): * Option A: Persistent Index (Default, Recommended) * StarRocks stores the bulk of the index on disk (SSD) and only keeps a small portion in RAM. * Memory Usage: Typically variable but very low compared to the row count. It won't exceed your BE 
update_memory_limit_percent
(usually 15-20% of BE RAM). * Option B: Full In-Memory Index (
enable_persistent_index
= 
false
) * Formula: 
(PK_size + 9) × row_count × 1.5
* Calculation: $(88 + 9) \times 300,000,000 \times 1.5 \approx \mathbf{43.6 \text{ GB}}$ cluster-wide (per replica). * Note: We strongly recommend Persistent Index for 300M+ rows to avoid massive RAM requirements. Summary Checklist 1. FE Nodes: 16GB RAM is standard and more than enough for your metadata scale. 2. BE Nodes: Ensure you have SSDs and leave 
enable_persistent_index = true
(default). This will keep the memory footprint for those 300M rows very manageable, even with high-cardinality VARCHARs in your PK. 3. Bucket Sizing: 300M rows / 14 days $\approx$ 21M rows per day. Distributed over 11 buckets $\approx$ 2M rows per tablet. This is a healthy size; your tablets will likely be a few hundred MBs each, which is optimal. References • table_design/indexes/Prefix_index_sort_key.md • faq/Others.md (See "How to set the memory size of an FE") • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md References • faq/Others.md • faq/operation_maintenance_faq.md • knowledge/trouble_shooting/fe_memory_problems.md • deployment/plan_cluster.md • administration/management/FE_configuration.md • introduction/Architecture.md
Open in Slack
PreviousNext
Powered by