@Rocky is it common practice to use a surrogate hash primary key in the pk table if I have multiple fields for it? For instance to minimize pk index size so more gets to live in memory?

Yes, using a surrogate hash key in a Primary Key (PK) table is a recognized optimization pattern in StarRocks, though its necessity has decreased with recent architectural improvements like the Persistent Index. Why use a Surrogate Hash Key? In StarRocks Primary Key tables, the system maintains a Primary Key Index to quickly locate rows for updates (upserts) and deletes. * Memory Usage: By default, if the index is in-memory, the memory cost is calculated as:

Memory = (Primary Key Size + 9 bytes overhead) _ Row Count _ Replicas * 1.5

* Composite Keys: If your primary key consists of multiple

VARCHAR

columns (e.g.,

user_id

,

event_id

,

timestamp

), the "Primary Key Size" can become quite large, leading to massive memory consumption or hitting the

primary_key_limit_size

(default 128 bytes). * Performance: A single

BIGINT

(8 bytes) or

LARGEINT

(16 bytes) is much more efficient to index than a long string or multiple columns. Common Implementation Users typically generate a hash of their composite keys using functions like

xx_hash3_64

(available since v3.2) or `murmur_hash3_32`:

sql
CREATE TABLE my_table (
    pk_hash BIGINT NOT NULL, -- Surrogate hash key
    col1 VARCHAR(64),
    col2 VARCHAR(64),
    ...
) PRIMARY KEY (pk_hash)
DISTRIBUTED BY HASH (pk_hash);

-- Loading data using the hash of original columns
INSERT INTO my_table 
SELECT xx_hash3_64(col1, col2), col1, col2, ... FROM source_data;

Is it still "Common Practice"? While still used for extreme scale, StarRocks has introduced features that make this manual "hashing" trick less necessary for most users: 1. Persistent Index (

enable_persistent_index

=

true

): This is now the recommended way to handle large PK tables. It allows the index to live primarily on disk (SSD) or object storage rather than consuming all your RAM. In version 3.2+, persistent indexes are highly optimized and often eliminate the need for surrogate keys just to save memory. 2. Sort Key Decoupling: Since v3.0, the Primary Key is decoupled from the Sort Key (

ORDER BY

). You can have a simple PK for uniqueness while using multiple columns in your

ORDER BY

clause to optimize query performance. Pros and Cons of the Hash Surrogate Approach Pros | Cons Minimal Memory: Drastically reduces PK index size (especially vs. long VARCHARs). | Collision Risk: While extremely low with

xx_hash3_64

, there is a non-zero mathematical risk of collisions. Faster Upserts: Smaller indexes are faster to search and update during ingestion. | Query Complexity: You must hash your filters in queries or keep the original columns for filtering, which adds storage overhead. Avoids PK Limits: bypasses the 128-byte limit for primary key definitions. | Management: You have to manage the hashing logic during every data load. Recommendation * Use a Persistent Index first: Set

"enable_persistent_index" = "true"

in your table properties. This is the standard "best practice" for managing memory in PK tables. * Use Hashing only if: You have billions of rows and even with a persistent index, the SSD I/O or the small memory cache is becoming a bottleneck, or if your composite PK is extremely wide (near the 128-byte limit). 1 limit). * If you hash: Use

xx_hash3_64

(returns

BIGINT

) for a good balance of performance and low collision risk, or

xx_hash3_128

(returns

LARGEINT

) if you want near-absolute safety from collision safety collisions. References • sql-reference/sql-functions/hash-functions/xx_hash3_64.md • sql-reference/sql-functions/hash-functions/murmur_hash3_32.md • using_starrocks/query_acceleration_with_auto_increment.md

@Rocky would it be correct to say that record updates do not affect compaction score as much as inserts? Like if there is high update throughput, mainly data freshness make take a hit as transaction apply latency slows down and update rowset apply duration slows down

That is a very insightful observation. You are largely correct, but there is a nuance in how "compaction score" and "apply latency" interact in the StarRocks Primary Key (PK) model. 1. Compaction Score: Inserts vs. Updates It is slightly more accurate to say that high-frequency writes (small batches) affect the compaction score, regardless of whether they are inserts or updates. * Rowset Versions: In StarRocks, every transaction (load job) creates a new rowset version. The compaction score is primarily a function of the number of these rowset versions. * The Difference: If you perform 1,000 small inserts or 1,000 small updates, both will result in 1,000 rowsets and a high compaction score (leading to ingestion slowdowns or "too many versions" errors). * Compaction Workload: Where updates and inserts differ is in the work the compaction engine performs. * Inserts: Compaction merges small files into larger ones. The data volume grows. * Updates: Compaction merges files and removes the old, stale versions of rows (garbage collection). Because PK compaction is "out-of-place" and uses Delete Vectors, it doesn't need to do a costly k-way merge sort like the Unique Key model. This makes the compaction task itself very efficient for updates, even if the score (version count) remains high. 2. The Bottleneck: "Apply" Latency and Data Freshness You hit the nail on the head regarding Apply Latency. This is unique to the Primary Key model and is the primary victim of high update throughput. * The "Apply" Phase: In a PK table, the commit stage includes an "Apply" phase. This is where the system: 1. Searches the Primary Key Index to see if the incoming keys already exist. 2. If it's an Update, it identifies the old row's location (Rowset ID + Segment ID + Row ID) and generates/updates a Delete Vector (DelVector). 3. Updates the index with the new row's location. * Why Updates are Slower: For a pure Insert of a new key, the system just adds the key to the index. For an Update, the system must find the old location and modify a DelVector. This extra lookup and metadata modification makes the "Update Rowset Apply Duration" significantly longer than a simple insert. * Impact on Freshness: Since this "Apply" happens during the commit/publish phase of a transaction, a slow Apply duration directly increases the time between "data sent" and "data visible to queries." This is why data freshness takes a hit. Summary of the Trade-offs Metric | Impact of Inserts | Impact of Updates Compaction Score | High (based on frequency/versions) | High (based on frequency/versions) Compaction Resource | Moderate (merging data) | Lower (discarding deleted rows) Apply Latency | Low (mostly index writes) | High (index lookups + DelVector creation) Primary Bottleneck | Disk I/O / Ingestion Rate | CPU/Memory (Index) & Apply Latency Pro-Tips for High Update Throughput: * Monitor Apply Latency: Check the metric

update_rowset_apply_duration

in your BE metrics. If this is high, your "Apply" phase is the bottleneck. * Batching is King: To lower the compaction score and reduce the number of "Apply" cycles, increase your load batch size (e.g., in Flink or Routine Load). * Persistent Index: If your PK index is too large for RAM, use

"enable_persistent_index" = "true"

. This offloads the index to disk, preventing "Apply" latency from spiking due to memory pressure on the memory-resident index. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • administration/management/monitoring/metrics.md

@Rocky is batching necessary still with merge commit enables? Seems to be the case as my compaction score lowered when I increased batch size from 50mb to 500 mb

Yes, manual batching is still necessary and highly recommended, even with "Merge Commit" enabled. While Merge Commit (introduced in v3.4.0) is a fantastic safety net, it does not fully replace the performance benefits of larger manual batches. Your observation that increasing your batch size from 50MB to 500MB lowered your compaction score is exactly what the system expects. Here is why manual batching still wins: 1. Merge Commit is a "Safety Net," Not a "Replacement" Merge Commit is designed for scenarios where you cannot control the client (e.g., thousands of independent devices or microservices hitting Stream Load simultaneously). It groups those tiny concurrent requests (KBs to few MBs) into a single transaction to prevent the "Too many versions" error. However, if you can control the batching (like in a Flink job or a custom loader), manual batching is superior because: * FE Overhead: Even with Merge Commit, the Leader FE still has to handle the overhead of receiving and processing every individual HTTP request before merging them. A single 500MB request is much "cheaper" for the FE than ten 50MB requests. * Data Density: A 500MB batch allows the BE to build larger, better-compressed segments. This leads to more efficient I/O during both queries and compaction. 2. Why your Compaction Score decreased The Compaction Score is primarily a count of "unmerged rowset versions." * At 50MB batches: You are creating 10x more versions for the same volume of data. The compaction engine has to work 10x harder to keep up, often leading to a higher "backlog" (score). * At 500MB batches: You are effectively doing "pre-compaction" at the source. The system receives data that is already well-organized into a single version. The compaction engine has significantly less work to do, so the score stays low. 3. The "Apply" Phase Bottleneck (Primary Key Tables) In Primary Key tables, every transaction must go through the Apply phase (checking the PK index and updating Delete Vectors). * With 50MB batches: The system must run the Apply logic 10 times. * With 500MB batches: It runs once. Since the Apply phase is often the bottleneck for data freshness in PK tables, the 500MB batch will actually result in lower overall latency and better "freshness" because the system isn't bogged down by constant metadata updates. Summary Recommendation * Use Merge Commit if: You have high-concurrency small writes (many threads) that you can't easily aggregate. It prevents the cluster from crashing due to version exhaustion. * Use Manual Batching (100MB - 1GB) if: You want the best possible performance, lowest CPU/IO overhead, and the lowest compaction scores. * The "Sweet Spot": For most StarRocks users, a batch size of 100MB to 500MB (or a 5-10 second interval) is the ideal balance between data freshness and system stability. Rocky's Tip: If you are using the StarRocks Flink Connector, you can use

sink.buffer-flush.size

to control the manual batch size and keep

enable_merge_commit

as a backup for peak traffic spikes. References • loading/Flink-connector-starrocks.md • loading/StreamLoad.md • knowledge/usage/load/shared_data_batch_ingestion.md