@Rocky Is the cost of unique key tables vs primary key tables that we trade query speed for ingestion throughput if it's purely last write wins per unique key? How does replication work here?

Yes, you have the core trade-off correct: Unique Key tables prioritize raw ingestion throughput (by deferring merging), while Primary Key (PK) tables prioritize query speed (by handling deduplication during ingestion). If your use case is purely Last Write Wins (LWW), here is how the costs and replication break down: 1. Ingestion vs. Query Trade-off Feature | Unique Key Table (Merge-on-Read) | Primary Key Table (Delete+Insert) Ingestion Throughput | Higher. Writing is a simple append of a new version. No index lookup is required during the write phase. | Lower. Each ingestion must perform a primary key index lookup to find and mark old rows as deleted. Query Speed | Slower. The engine must merge all versions of a key at read time to find the "winner," which prevents many optimizations like predicate pushdown. | Faster (3-10x). Since old rows are already marked as deleted in a "Delete Vector," the engine only reads the latest data. Resource Cost | Uses more CPU/IO during queries due to merging. High-frequency writes can cause "too many versions" errors if compaction falls behind. | Uses Memory to store the PK index (can be offloaded to disk via "Persistent Index"). Uses more CPU during ingestion. 2. How Replication Works StarRocks uses a Quorum-based replication mechanism for both table types in a shared-nothing architecture. However, the internal update process differs: * Unique Key Table: When data is loaded, StarRocks replicates the new data "rowset" to a majority of BE (Backend) nodes. Each replica simply stores the new version. The "merging" to find the last write happens only when a user runs a query or during background compaction. * Primary Key Table: * Independent Index Updates: Each replica of a tablet maintains its own Primary Key Index. * The Commit Phase: During ingestion, the data is sent to all replicas. Each BE replica independently performs the index lookup, updates its local index to point to the new row location, and generates a Delete Vector (a bitmap) to mark the old row as deleted. * Consistency: Because the FE (Frontend) ensures that the same sequence of load jobs is committed to all replicas, the local indexes and delete vectors on each BE remain consistent. Recommendation for "Last Write Wins" * Use Primary Key tables if you have a high query volume or need low-latency analytics. It is the modern standard in StarRocks for "upsert" scenarios. * Use Unique Key tables only if your ingestion rate is so extreme that the PK index lookup becomes a bottleneck, and your query volume is low enough that the read-time merge penalty is acceptable. Note: If you are on a version older than 3.0, Primary Key tables had more memory constraints. In newer versions (3.3+), Persistent Indexes allow PK tables to handle massive datasets by storing the index on disk/object storage, significantly reducing the memory "cost" of using PK tables. Sources: • Primary Key table • Unique Key table References • table_design/table_types/unique_key_table.md • table_design/table_types/primary_key_table.md • table_design/table_types/table_types.md