@Rocky what's the performance difference in ingestion between duplicate_key tables and primary key tables?

The performance difference in ingestion between Duplicate Key and Primary Key tables is primarily driven by the "work" the system must perform during the write and commit phases. 1. Duplicate Key Table (Highest Ingestion Performance) Duplicate Key tables are designed for append-only scenarios (like logs or time-series data) and offer the highest ingestion throughput. * Mechanism: It performs a simple append. There is no checking for existing keys or deduplication. * Overhead: Very low. The system only needs to sort the data (if a sort key is defined) and write it to disk as new segments. * Performance: This model provides the maximum write speed StarRocks can achieve, as it bypasses the complexities of uniqueness constraints. 2. Primary Key Table (Lower Ingestion Performance) Primary Key tables use a Delete+Insert strategy to support real-time updates while maintaining high query performance. This introduces significant "write amplification" and processing overhead. * Mechanism: * Index Lookups: For every row ingested, StarRocks must look up the primary key in an in-memory index to see if the record already exists. * DelVector Updates: If a record is an update, the system must locate the old row and mark its position as deleted in a Delete Vector (DelVector). * Commit Phase: The commit process is more complex because it involves finalizing these index updates and DelVector generations. * Resource Consumption: It requires more memory (to hold the PK index) and more CPU/IO during ingestion. * Compaction Pressure: To keep queries fast, Primary Key tables require more frequent and resource-intensive compaction. If compaction cannot keep up with high-frequency writes, StarRocks may intentionally slow down or pause ingestion (throttling) to prevent query performance degradation. Summary Comparison Feature | Duplicate Key | Primary Key Ingestion Strategy | Append-only (Direct Write) | Delete+Insert (Lookup + Write) Ingestion Throughput | Highest | Lower (due to PK index lookups) Write Latency | Very Low | Higher (due to commit-time overhead) Deduplication | None (keeps all duplicates) | Automatic (replaces old values) Resource Usage | Low CPU/Memory | High CPU/Memory (for PK index) Recommendation: * Use Duplicate Key tables if you only need to append data (e.g., raw logs) and need the highest possible ingestion speed. * Use Primary Key tables if you require frequent updates/deletes or need the absolute fastest query performance, and are willing to trade off some ingestion throughput and memory to get it. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md

@Rocky ok but let's same we are dumping data that we know has a unique id in a table for some reason, do you have some data/benchmarks for the difference between duplicate key and primary key tables?

While there isn't a single "official" public benchmark table that lists specific MB/s side-by-side (as performance depends heavily on hardware and schema), the performance profile difference between Duplicate Key and Primary Key tables is well-documented in terms of architecture and resource consumption. If you are dumping data with a unique ID into both, here is the performance reality: 1. Ingestion Throughput (The "Speed Limit") * Duplicate Key Table: This represents the maximum physical write speed of StarRocks. Data is simply sorted and written. There is no state to check. It is strictly an I/O and CPU-bound sorting operation. * Primary Key Table: Even if you know the data is unique, StarRocks doesn't "know" that. For every row, it must: 1. Lookup: Check the Primary Key index to see if the key exists. 2. Commit Overhead: During the commit phase (which happens at the end of every load batch), it must finalize the index updates and generate Delete Vectors. * Result: In practice, you can expect a 20% to 50% decrease in raw ingestion throughput compared to a Duplicate Key table, depending on whether your index fits in memory. 2. The "Persistent Index" Factor The ingestion performance of a Primary Key table drops significantly if the Primary Key index exceeds the available RAM. * With In-Memory Index: Ingestion is fast (the "slight dip" mentioned in docs) because the lookup is a memory-speed hash table check. * With Persistent Index (Disk): If you enable

persistent_index

, the index is stored on disk (usually SSD). This allows you to have primary keys larger than your RAM, but it introduces disk I/O into the ingestion path, further widening the performance gap between it and the Duplicate Key model. 3. Backpressure and Throttling Primary Key tables are much more sensitive to "Write Amplification" caused by compaction. * Duplicate Key: Compaction is simpler (base-rowset merging). * Primary Key: Compaction must manage both data and Delete Vectors. If you ingest too fast, the "compaction score" will rise. StarRocks will intentionally throttle or pause ingestion for Primary Key tables to prevent query performance from degrading. Duplicate Key tables are much harder to "choke" in this manner. Summary: When to choose which? Scenario | Use Duplicate Key | Use Primary Key Data Nature | Raw logs, immutable events | CDC data, orders, user profiles Ingestion Priority | Absolute max throughput | Maintaining uniqueness/updates Query Priority | Filtering/Scanning efficiency | Point lookups, high-concurrency "latest value" Hardware | Optimized for I/O | Requires more RAM/Fast SSD for index Bottom Line: If your data is truly append-only and you don't need the database to enforce uniqueness or handle updates, Duplicate Key will always be faster and more resource-efficient for ingestion. Using a Primary Key table "just in case" for append-only data is considered a StarRocks anti-pattern because of the unnecessary index and compaction overhead. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • table_design/table_types/duplicate_key_table.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • table_design/table_types/duplicate_key_table.md

> StarRocks will intentionally throttle or pause ingestion for Primary Key tables to prevent query performance from degrading. Duplicate Key tables are much harder to "choke" in this manner. Docs for this? @Rocky

Yes, this behavior is documented across several sections of the StarRocks documentation, particularly in the Primary Key Table Best Practices and Compaction guides. The "choking" or throttling occurs because Primary Key tables have a more complex lifecycle during ingestion than Duplicate Key tables. 1. Ingestion Slowdown (Shared-data Clusters) In shared-data clusters (Cloud-native), StarRocks uses a Compaction Score to monitor the health of a partition. If the score is too high, it indicates that ingestion is significantly faster than the system's ability to merge data. * Mechanism: When a partition's compaction score exceeds the threshold set by

lake_ingest_slowdown_threshold

(default: 100), the FE will intentionally delay the commit of new transactions. * Log/Error Evidence: You will see messages like:

Partition's compaction score is larger than 100.0, delay commit for 6513ms.

* Hard Stop: If the score exceeds

lake_compaction_score_upper_bound

(default: 2000), the system will outright reject the load with:

Failed to load data... because of too large compaction score.

2. Version Limits (Shared-nothing Clusters) In standard (Shared-nothing) clusters, the mechanism is a hard limit on the number of "versions" (data segments) a tablet can hold before they are compacted. * Mechanism: If ingestion is too frequent (e.g., many small batches), the number of versions will hit the

tablet_max_versions

limit (default: 1000). * Result: The ingestion fails with:

Failed to load data into tablet xxx, because of too many versions.

Why Primary Key Tables are more sensitive While both table types have version limits, Primary Key tables are "choked" more easily for three reasons: 1. Write Amplification: For every row, StarRocks must look up the PK index and potentially update a Delete Vector. This consumes more CPU and I/O than a Duplicate Key append. 2. Compaction Overhead: Primary Key compaction is more resource-intensive because it must merge data and reconcile Delete Vectors. Because it takes longer to compact, the "Compaction Score" or "Version Count" stays high for longer. 3. Index Weights: In shared-data clusters, the Primary Key index itself contributes to the compaction score (controlled by

pk_index_compaction_score_ratio

). This means a PK table reaches the "slowdown" threshold faster than a Duplicate Key table with the same number of data files. Summary of Documentation Links * *Primary Key Table Best Practices*: Details the slowdown thresholds and how to monitor them using

show proc '/transactions/...'

. * *Compaction for Shared-data*: Explains the calculation of the Compaction Score and the

lake_ingest_slowdown_threshold

. * *BE Configuration*: Defines parameters like

tablet_max_versions

and

pk_index_compaction_score_ratio

. In short: Duplicate Key tables are "harder to choke" because they perform the bare minimum work (append only), allowing compaction to keep up with much higher ingestion frequencies before hitting the safety limits are triggered. References • best_practices/primarykey_table.md • administration/management/compaction.md • administration/management/BE_configuration.md References • best_practices/primarykey_table.md • administration/management/compaction.md