@Rocky what are the best practices for PK tables?

To get the best performance and manage resources effectively with StarRocks Primary Key (PK) tables, follow these best practices for schema design, indexing, and data ingestion. 1. Primary Key Design * Column Data Types: Choose data types that occupy less memory. Prefer

INT

or

BIGINT

over

VARCHAR

. The primary key index is stored in memory (or cached from disk), so a smaller footprint reduces memory pressure. * Minimal Columns: Keep the number of columns in the primary key as small as possible. The primary key's purpose is unique identification, not query acceleration. * Sort Key: Use the

ORDER BY

clause to define a sort key (separate from the primary key) for columns frequently used in query filters. The primary key itself does not automatically speed up queries unless it overlaps with the sort key. 2. Index Persistence (Memory Management) By default, StarRocks uses a Persistent Index (

"enable_persistent_index" = "true"

) to avoid consuming excessive RAM. * Recommended: Keep the persistent index enabled (default). This stores the index on disk (SSD recommended) and only loads a small portion into memory. * Cloud-Native: In shared-data (elastic) clusters, use

"persistent_index_type" = "CLOUD_NATIVE"

to store index data on object storage, which simplifies rebalancing and saves local disk space. * Avoid Full In-Memory: Only use

"enable_persistent_index" = "false"

if your dataset is small or you have abundant RAM, as it can lead to OOM (Out of Memory) issues as the table grows. 3. Partitioning and Bucketing * Partitioning: Use time-based partitioning (e.g.,

PARTITION BY date_trunc('day', dt)

). This allows StarRocks to release the primary key index for older, "cold" partitions that are no longer being updated, significantly reducing memory usage. * Bucketing: * Choose a high-cardinality column for bucketing to avoid data skew. * Aim for a tablet size (bucket size) between 100 MB and 1 GB (compressed) for the best balance between parallelism and management overhead. 4. Data Ingestion & Updates * Batch Size: Avoid frequent, tiny commits (small-file problem). Larger batches (e.g., every 5–10 seconds or several MBs) are more efficient for PK tables because each load triggers index lookups and delete vector generation. * Partial Updates: * Row Mode (Default): Best for real-time, small-batch updates involving many columns. * Column Mode: Best for batch updates involving only a few columns across many rows. Set

"partial_update_mode" = "column"

in your load properties. * Compaction: If you have high-frequency writes, ensure compaction can keep up. Monitor "compaction score" via

SHOW PROC '/transactions/<db>/running'

. You can increase

update_compaction_num_threads_per_disk

in

be.conf

if necessary. 5. Monitoring Monitor the memory consumed by PK indexes using the StarRocks `mem_tracker`:

sql
-- View memory used by Primary Key tables (index, delete vectors, etc.)
http://<be_ip>:<be_http_port>/mem_tracker?type=update

Summary Table: Key Properties Property | Recommended Value | Why?

enable_persistent_index

|

true

| Saves RAM; performance is comparable to in-memory.

persistent_index_type

|

LOCAL

or

CLOUD_NATIVE

|

CLOUD_NATIVE

is preferred for Shared-Data clusters.

partial_update

|

true

| Required for updating specific columns only.

replication_num

|

3

(Default) | Standard for high availability. For more details, see the official StarRocks Primary Key Table Best Practices. References • loading/Load_to_Primary_Key_tables.md • loading/Flink-connector-starrocks.md • table_design/table_types/primary_key_table.md