@Rocky Hi, looking for advice on modeling row-level access control in StarRocks. Setup: CRM objects (contacts, deals, tasks) in Primary Key tables, up to ~500 users and a few dozen teams per account, millions of rows per object. Each row stores: - owner_id INT (nullable) - access_users BITMAP – user ids from all user-type attributes on the record - access_teams BITMAP – team ids from team-type attributes Queries filter with predicates like: owner_id = :uid OR owner_id IS NULL OR bitmap_contains(access_users, :uid) OR bitmap_has_any(access_teams, bitmap_from_string(‘1,4,9’)) combined with normal list-view filters, sorting and pagination. Questions: 1. Is BITMAP a good fit as a value column in a PK table for this, or would ARRAY<INT> with array_contains perform better at this size (sets are tiny, max 500 bits)? 2. Can bitmap_contains / bitmap_has_any use any index or predicate pushdown, or will they always scan? Any way to help zone-map or bloom filters here? 3. Best way to do partial updates of a single bitmap column when one record changes, and to bulk-recompute the column for all rows of one object type. 4. Any known pitfalls: NULL vs empty bitmap, bitmap_from_string cost per query, or a better pattern people use for per-row ACLs in StarRocks?

Modeling row-level access control (RLAC) in StarRocks using the

BITMAP

data type is a common and efficient pattern, especially for complex many-to-many permission structures. 1. BITMAP vs ARRAY<INT> For your scale (max 500 bits per row, millions of rows),

BITMAP

is generally the better fit, but

ARRAY<INT>

is a viable alternative if you need more flexibility. * BITMAP (Roaring Bitmap): * Pros: Extremely compact and specifically optimized for set operations (

contains

,

union

,

intersect

). Bitwise operations are faster than array iteration in the vectorized engine. * Cons: Only supports non-negative

BIGINT

values. Opaque binary format makes it harder to debug via standard SQL (requires

bitmap_to_array

). * ARRAY<INT>: * Pros: Supports any integer type (including negative if needed). Easier to inspect. * Cons: Higher storage overhead for sparse IDs.

array_contains

is fast but generally slightly slower than bitwise checks once data is loaded. Recommendation: Use

BITMAP

. StarRocks is heavily optimized for Roaring Bitmaps because they are also used internally for its global dictionary and delete vectors. 2. Indexing and Predicate Pushdown A critical distinction:

BITMAP

columns do not benefit from standard secondary indexes (Bloom Filter, ZoneMap) for internal bit-checks. * ZoneMaps: These store min/max values of the entire column. For a

BITMAP

or

ARRAY

column, the "min/max" is based on the serialized binary blob, which is useless for pruning based on a specific user ID. * Bloom Filters: These index the entire value. A Bloom filter would only help if you were searching for an exact match of the entire set of permissions (e.g.,

access_users = :some_bitmap

), not for "contains." * Primary Key Sort Order: In a Primary Key table, the data is sorted by the PK. If your queries always include an

account_id

or

tenant_id

, make sure that column is the first part of your Primary Key. This allows the storage engine to prune entire data blocks before the

BITMAP

filter even runs. 3. Partial Updates and Bulk Recomputation Since you are using a Primary Key table, you have two efficient ways to handle this: * Single Record Change (Partial Update): Use Column Mode partial updates. If you are using Stream Load or Flink, set

partial_update = true

. This allows you to update only the

access_users

column for a specific PK without reading or rewriting the rest of the row. * Bulk Recomputation: The most efficient way to recompute a column for millions of rows is an

INSERT INTO ... SELECT

or

UPDATE

statement:

sql
    UPDATE crm_objects
    SET access_users = (SELECT ... FROM acl_source WHERE ...)
    WHERE account_id = :aid;

For very large-scale recomputations, a common pattern is to compute the new mapping in a temporary table and use a

JOIN

update. 4. Pitfalls and Patterns * Cost of `bitmap_from_string`: Avoid calling this function inside your query's

WHERE

clause for every row. _ _Bad:*

WHERE bitmap_has_any(access_teams, bitmap_from_string('1,2,3'))

_ _Better:* Pass the filter as a constant bitmap object if your client library supports it, or pre-calculate the filter bitmap in a CTE:

sql
        WITH filter AS (SELECT bitmap_from_string('1,2,3') as bm)
        SELECT * FROM crm_objects, filter
        WHERE bitmap_has_any(access_teams, <http://filter.bm|filter.bm>);

* NULL vs Empty Bitmap: *

bitmap_contains(NULL, 1)

returns

NULL

(falsy in

WHERE

). * Initialize columns with

bitmap_empty()

to avoid NULL handling logic in every query. * Standard Pattern for ACLs: Many high-concurrency implementations in StarRocks use the following query structure to maximize the Short-Circuit effect: ```sql SELECT * FROM crm_objects WHERE account_id = :aid -- Primary prune (Sort Key) AND ( owner_id = :uid -- Fast scalar check