First off, let’s talk about the duplicated key table. This is StarRocks’ default table and one of the most popular ones among users. Picture this: you’ve got a table creation statement on your right. See the DUPLICATE KEY keyword? After this, you can declare your sorting columns. It’s a great idea to choose columns that are frequently used in your filter conditions.
Next up, we have the Aggregation table. This one is ideal when you have business scenarios involving aggregated indicator columns. Imagine you’ve created a data table using this table. What happens is that your indicator columns get neatly aggregated based on your definitions.
Finally, let’s talk about the Primary Key table. This table shines when you’re dealing with real-time or frequent updates. The Primary Key table ensures that for any given primary key, there’s just one record, eliminating the need for Merge operations. This greatly improves query performance at the cost of a slight dip in write performance and memory usage.