@Rocky what is the difference between primary key table and unique key table in starrocks

The main difference between the Primary Key (PK) table and the Unique Key (UK) table lies in their underlying storage engine architecture, update strategy, and query performance. While both ensure record uniqueness based on a key, they handle data very differently. Here is a breakdown of the key differences: 1. Storage & Update Strategy * Primary Key (PK) Table (Delete+Insert): When you update a row, StarRocks uses a primary key index to find the location of the old row, marks it as deleted in a

DelVector

, and inserts the new row into a new data file. This is a "Delete-and-Insert" strategy. * Unique Key (UK) Table (Merge-on-Read): Data is stored in multiple versions. When you load a row with an existing key, the new version is simply appended. The actual "deduplication" or replacement happens during query time. 2. Query Performance * Primary Key Table: Offers significantly better query performance (typically 3x to 10x faster than Unique Key tables). Because historical duplicates are already marked as deleted during the write stage, the query engine doesn't need to perform complex deduplication logic while reading. It also supports better predicate pushdown and index usage. * Unique Key Table: Since it uses "Merge-on-Read," the system must read multiple versions of data and deduplicate them on the fly during every query, which consumes more CPU and I/O. 3. Memory & Resource Usage * Primary Key Table: Requires a Primary Key Index to track the location of keys. * By default (

enable_persistent_index = true

), this index is persisted to disk, which saves memory but uses some disk I/O. * If kept entirely in memory, it provides the fastest performance but requires more RAM (proportional to the number of unique keys). * Unique Key Table: Does not require a persistent primary key index, making it slightly more "lightweight" in terms of memory overhead during simple append-heavy loads, though at the cost of query speed. 4. Support for Partial Updates * Primary Key Table: Natively supports Partial Updates (updating only specific columns in a row). This is a core feature for CDC (Change Data Capture) scenarios where you might receive updates for only a subset of fields. * Unique Key Table: Primarily designed for full-row replacements. While some partial update workarounds exist, they are not as efficient or native as in the Primary Key model. Summary Comparison Feature | Primary Key Table | Unique Key Table Strategy | Delete+Insert | Merge-on-Read Query Speed | Very Fast (Optimized) | Slower (Online Deduplication) Updates/Deletes | Highly Efficient | Less Efficient Partial Updates | Native & High Performance | Limited Memory Usage | Higher (Needs PK Index) | Lower Best Use Case | Real-time CDC, High-frequency updates, Fast Analytics | Legacy batch loads, simple deduplication Rocky's Recommendation: For almost all modern real-time analytical scenarios in StarRocks, the Primary Key table is the preferred choice due to its superior query performance and robust support for updates and deletes. References • table_design/table_types/primary_key_table.md • table_design/table_types/unique_key_table.md • best_practices/primarykey_table.md