Data analytics on real-time data has become essential for digital operations and decision-making. As a result, many users have built real-time Data analytics platforms. To analyze different types of business “changes” in real time and respond quickly to business shifts, real-time data updates have become a core requirement for real-time analytics. However, when many users implement real-time data updates, Query performance is often not ideal, significantly reducing the efficiency of business analytics.

Unlike other leading OLAP databases, StarRocks designed and implemented the Primary Key model, which enables better real-time data updates while delivering ultra-fast query capabilities. The Primary Key model has now been refined for more than a year. With a redesigned architecture and implementation, Query performance can be 3–5x higher than other leading OLAP databases when writing large-scale real-time data.

StarRocks is now widely used in core business scenarios by more than 110 large-scale users. Many of them have successfully adopted the Primary Key model to improve real-time analytics capabilities, including Tencent, Airbnb, SF Express, ZhongAn Insurance, and others.

This article introduces the technical internals of the Primary Key model implemented in StarRocks 2.x, including its real-time data update mechanism and the implementation principles behind ultra-fast queries on real-time updated data.

#01 Background

As real-time Data analytics scenarios continue to evolve, demand is also increasing for analytics on data that can be updated in real time. For example:

  • A TP system synchronizes Binlog data to an AP system in real time through CDC. This makes it very convenient to analyze business data “changes” in real time. This is the most common scenario.
  • Traditional OLAP data flows use ETL (Extract-Transform-Load): data is fully processed before being loaded into an AP system. As modern AP systems become more capable, ELT (Extract-Load-Transform) data flows are becoming increasingly popular.

In this approach, Raw data or lightly processed data is first loaded into the AP system. Then, SQL is used inside the AP system to perform various transformations before queries are executed.

This approach has several benefits. Using a unified system reduces development and maintenance costs. Compared with using other compute frameworks for ETL, SQL is easier to use. Finally, within the same AP database system, the transaction mechanism makes it simpler and more convenient to ensure Data consistency.

To support various data processing logic in real-time ELT, database support for real-time updates has also become a strong requirement.

#02 Technical Approaches

AP database systems usually use Columnar storage as the underlying storage engine. A table typically contains multiple files, or Rowsets. Each file is organized in a columnar format, such as Parquet, and is immutable.

Given this organization, several common technical approaches can support updates:

Copy-on-Write

When a batch of updates arrives, the system needs to check whether each record conflicts with the original files, meaning whether there are records with the same Key. For files with conflicts, the system rewrites a new copy that contains the updated data.

With this approach, reads can directly access the latest data files without any merge or other operations, so Query performance is optimal. However, writes are expensive. Therefore, this approach is suitable for T+1 scenarios that do not involve frequent updates, but it is not suitable for real-time update scenarios.

Many Data Lakes use this approach to implement updates, such as Delta Lake and Copy-on-Write tables in Apache Hudi.

Copy-on-Write

Merge-on-Read

When a batch of updates arrives, the data is directly sorted and written into a new file in Columnar storage or Row storage format. Because no deduplication or conflict check is performed during writes, reads must compare Keys and perform Merge to combine multiple data versions, keeping only the latest version to return to the query execution layer.

This approach provides the best write performance and is simple to implement, but read performance is poor. It is not suitable for scenarios with high Query performance requirements. Systems that use this approach include Merge-on-Read tables in Apache Hudi, the Unique Key table in StarRocks, and ClickHouse’s real-time update model.

Merge-on-Read

Delta Store

When a batch of updates arrives, the system first uses the Primary Key index to locate the original file and position of each record, usually an integer row number. The position and the modification are stored as a Delta record in a Delta Store associated with the original file. During queries, the original data and data in the Delta Store must be merged, or the Delta data must be applied.

Because Delta data is organized by row number, the merge process can be performed directly by row number. Compared with Merge-on-Read, which merges by Key, Query performance is much better. Since writes need to query the index, write performance is slightly lower, but read performance is much better. In other words, this approach trades some write performance for read performance. In addition, introducing the Primary Key index and Delta Store increases system complexity. A typical system that uses this approach is Kudu. Some in-memory TP database systems also commonly use this approach.

Delta Store

Delete-and-Insert

When a batch of updates arrives, the system first uses the Primary Key index to locate the original position of each record, marks that record as deleted, and then writes the latest data as a new record into a new file. During reads, outdated old versions are filtered out based on deletion markers, leaving only the latest updated data.

Because this approach does not require Merge as in Merge-on-Read and Delta Store, and because filter operators can be pushed down to the Scan layer to directly use different indexes and reduce scan overhead, it has more room to improve Query performance.

Similar to Delta Store, this mode trades some write performance for read performance. It is already used in some industry systems, such as SQL Server’s in-memory Columnar storage. However, Delete-and-Insert requires introducing a Primary Key index and storing and managing deletion markers, which makes it very difficult to implement in large-scale real-time update and query scenarios.

The current StarRocks Primary Key model mainly uses the Delete-and-Insert approach and introduces many new designs to deliver ultra-fast Query performance during large-scale real-time data updates.

Delete-and-Insert

#03 How StarRocks Implements Real-Time Updates

StarRocks added a new Table type: Primary Key. Tables of this type support updating, or Upsert, and deleting, or Delete, one or more rows by primary key.

Operations on StarRocks tables are loaded into the system through load operations. Each load job can be treated as an independent write transaction, and StarRocks guarantees the ACID properties of load transactions. In other words, for a large-scale load transaction spanning multiple Tablets, the load takes effect atomically and maintains isolation between concurrent load transactions.

Overall Transaction Flow

As shown in the following diagram, a load transaction can be divided into two stages: Write and Commit.

StarRocks Load transaction flow diagram
  • In the Write stage, the client distributes the data of the load transaction to the corresponding Tablets according to partitioning and bucketing rules. After each Tablet receives the data, it writes the data into the internal Columnar storage format and forms a Rowset.
  • In the Commit stage, after all data has been successfully written, the FE (Frontend node) initiates the transaction to all participating Tablets and commits it. Each Commit carries a Version number, representing the latest version of the Tablet data.

Internal Tablet Structure

A Tablet is the basic unit of the underlying StarRocks storage engine. Each Tablet can be regarded as a standalone Columnar storage engine that handles reads and writes.

To support real-time updates, the standalone storage engine of the Primary Key model has been redesigned. It is now clearly different from the original storage engines of the other three models: Duplicate, Aggregate, and Unique. As shown in the following diagram, it mainly contains four components:

StarRocks primary index architecture with RocksDB, DelVector and Rowset
  • Meta: Metadata that stores the version history of a Tablet and information about each version, such as which Rowsets it contains. After being serialized as Protobuf, it is stored in RocksDB. For fast access, it is also cached in memory.
  • Rowset: All data in a Tablet is split into multiple Rowsets. Each Rowset is stored as a Columnar storage file. Its internal organization and encoding are similar to Parquet files, but it uses a StarRocks-specific format.
  • Primary Index: The Primary Key index that stores the mapping from primary keys to record locations. Currently, it is fully maintained in memory and is not persisted to disk. It is built on demand during load operations and released after no continuous load has occurred for some time, helping save memory.
  • DelVector: Records rows marked as deleted in each Rowset. For example, if a file has 10,000 rows and 200 rows are marked as deleted, a Bitmap can be used to mark the deleted rows. This data is usually sparse, so RoaringBitmap can store and process it efficiently. It is also stored in RocksDB and cached in memory for fast access.

Metadata Organization

Metadata has a version history. Each Commit or Compaction generates a new version, recording which data is included in that version and what has changed compared with the previous version. The following diagram shows an example of metadata version history.

Example of StarRocks metadata version history

Each Rowset Commit or Compaction Commit generates a new version, and each version records which Rowsets it contains.

As data loading continues, version history accumulates. Older versions are less likely to be used again, so the background Garbage collection (GC) thread periodically deletes old versions. As old versions are deleted, some Rowsets may no longer be referenced by any version and can also be deleted, as shown below:

Garbage collection of metadata

Write Flow

As described earlier, the data of a write transaction is distributed to related Tablets based on partitioning and bucketing information. Each Tablet is responsible for writing part of the data. After a Tablet receives this data, it executes a write flow and eventually adds this data to the dataset it manages. The following diagram shows the stages of the write flow.

Write Flow

The detailed process is as follows:

  • Incoming data batches accumulate in MemTable. When MemTable is full, it needs to Flush to disk.
  • Flush includes three operations: Sort, Merge, and Split. Sort first sorts the data by primary key. Merge combines multiple records with the same primary key and keeps only the latest version. Split separates Upsert and Delete operations in this batch and writes them into different files.
  • After all data for the load has been sent and flushed, a new Rowset containing multiple files is generated.
  • The FE initiates the Commit flow and executes Commit for the Tablet. The BE (Backend node) includes three steps:
    • Look up and update the Primary Index, record all updated records, and mark them as deleted.
    • Generate DelVector based on the records marked as deleted.
    • Generate a new version of Meta and write it to RocksDB together with DelVector.

Concurrent Control for Write Transactions

StarRocks supports concurrent load. Multiple load transactions may conflict, so conflicts must be resolved through concurrent control. Each load transaction is divided into two stages: Write and Commit.

  • Write: The stage that writes data and generates Rowset files. It can be executed in parallel and accounts for most of the transaction time.
  • Commit: After the FE initiates Commit, operations such as looking up the Primary Key index, generating DelVector, and generating metadata must be serialized. However, this stage only accounts for a small part of the overall load, so serialized execution has limited impact on load performance.
Concurrent Control for StarRocks Write Transactions

Loads follow the Last Write Wins principle. For conflicts between records with the same Key in concurrent load transactions, the record with the larger version overwrites the record with the smaller version. There are generally two ways to handle write-write conflicts in concurrent transactions:

  • One is Pessimistic locking. Its disadvantages include locking overhead, the impracticality of row-level locks for very large write transactions, and the impact of locking on concurrent write performance.
  • The other is Optimistic locking. It does not check conflicts during writes, but checks them during Commit. If a conflict occurs, the transaction is aborted. However, loads in AP systems are usually very large transactions, so aborting and reloading is too costly and therefore not ideal.

StarRocks does not use either of these two classic approaches. Its transaction conflict handling is unique. The Commit stage is executed serially, and DelVector is used to resolve conflicts, avoiding the overhead and performance impact of locking.

After a write conflict occurs, no Rollback is required. The system only needs to mark the older conflicting record as deleted. This is especially suitable for scenarios where Data loading tasks can be treated as pure-write large transactions.

Primary Key Index

The main task in the Commit stage is to look up and update the Primary Key index, which accounts for more than 90% of the entire Commit process. Commit speed mainly depends on the performance of the Primary Key index. Therefore, the Primary Key index is currently implemented using an in-memory HashMap. Like the compute layer, it uses the high-performance HashMap library phmap. The HashMap key is a Binary string encoded from multiple columns, and the value is a 64-bit integer composed of (rowset_id, rowid). This allows the system to locate which row in which Rowset corresponds to a given primary key.

Compared with general-purpose KV stores such as RocksDB, the in-memory HashMap has a significant performance advantage. On modern CPUs, each operation usually takes only 20 ns to 200 ns, meaning a single core can reach 5M to 50M ops/s. This ensures that large batch load transactions can complete Commit quickly. Its biggest disadvantage is memory consumption. Optimizations have been made for fixed-length and variable-length primary keys to reduce memory usage. In the future, HashMap memory usage will continue to be optimized, and a Persistent index based on SSD/PMEM will be explored.

The Primary Key index is currently loaded on demand. When writes occur, the system reads primary key columns to build the index. After a long period without writes, the index is released to save memory. This is highly suitable for scenarios with hot and cold data patterns: a record is frequently updated for a period after creation, then gradually becomes cold and is no longer updated. If data is partitioned by day, only recent partitions have active loads, so only the Primary Key indexes of Tablets in recent days are loaded. This keeps overall memory usage manageable.

On-demand primary index loading

Typical scenarios include various order scenarios, such as e-commerce orders, ride-hailing or shared-bike orders, as well as application or device Sessions.

Compaction

Continuous real-time Data loading creates many small Rowset files. In addition, updates and deletes mark a large number of records in Rowsets as deleted. Both issues affect read performance for queries and waste storage space.

Similar to storage engines such as LSM, StarRocks also uses background Compaction to optimize data organization, but its specific mechanism is very different from LSM:

  • First, when selecting Rowsets for Compaction, the strategy is based on Cost, prioritizing small files and files with many deleted records.
  • Because duplicate data has already been removed during writes, there are no duplicate records in storage, so Compaction does not need to handle duplicate record merging.
  • Because Delete is natively supported, Rowset files do not contain Delete or RangeDelete Tombstone markers, making processing simpler.
  • However, because the Primary Key index is introduced, Compaction changes the actual position of records. The system must additionally maintain consistency of the Primary Key index, which increases complexity.

The Compaction flow is similar to the normal write flow:

  1. Select input Rowsets for Compaction based on the selection strategy.
  2. Merge Rowsets and generate a new Rowset.
  3. Commit this Rowset, update the Primary Key index, generate DelVector, and atomically replace the input Rowsets in metadata.
StarRocks compaction workflow of rowsets

During Compaction, concurrent loads may also be running and may conflict with Compaction. To detect and resolve conflicts, the system records the IDs of the original Rowsets used in Compaction. When updating the Primary Key index during Commit, it checks whether the Rowset ID corresponding to each row has changed. If it has changed, that row has already been updated, so the system ignores the update for that row and marks the corresponding row in the Rowset as deleted.

Read Flow

Compared with other designs and implementations, the Primary Key table has major advantages in read performance. Combined across multiple factors, Query performance can improve by more than 3–5x. The main reasons are:

  • Removing Merge operations: Because historical duplicate records are already marked as deleted during writes, reads no longer need a Merge process to perform deduplication and find the latest version of data. For many queries, the Merge process is the bottleneck of the entire query flow. Removing Merge can greatly improve performance. In addition, after Merge is removed, the entire execution tree can use vectorized computation, which also significantly improves Query performance.
  • Predicate pushdown: In many other systems, predicate pushdown cannot go below the Merge operation. After removing Merge, predicate pushdown can go directly to the lower-level Rowset Scan operation. This allows secondary indexes in Rowset files, such as zonemap, bitmap, and bloom, to take effect and accelerate queries by several times.
  • Parallel scanning: When multiple Rowset files exist, Scan operations on multiple files can run in parallel.

The following diagram compares a query using the Merge-on-Read mode, such as the Unique Key table in StarRocks and ClickHouse’s real-time update model, with the Delete+Insert mode, which is the Primary Key model in StarRocks.

Merge-on-read vs del flag query plan comparison

With the Primary Key mode, the overhead of Merge is removed, I/O for reading order_id is avoided, the state==2 predicate can use Bitmap index acceleration, and the amount of data transmitted across the entire operator tree is much smaller.

A simulated order table scenario was used to compare performance between the Unique Key table and the Primary Key table. The table contained 10 million orders per day, and each order was updated multiple times. Data was loaded continuously for 20 days. When queries ran while real-time loads were ongoing, simple queries achieved more than 10x performance improvement in the best case. When loads were paused for a period and queries ran independently, performance improved by around 3x.

Primary key vs Unique key benchmark comparison bar chart

#04 Future Work

Currently supported operation types include Upsert and Delete. They can cover the most common scenarios, including CDC synchronization from TP systems to StarRocks and regular batch load deduplication. As application scenarios become more complex, new requirements are emerging for StarRocks. The following are features planned for significant future investment.

Partial Update

Partial update support is needed. A typical scenario is that several upstream modules write to a Denormalized table or Flat table in real time, and each module updates only part of the columns in the table. The table then automatically completes the Join operation.

Currently, there are two ways to implement this Join operation. One is to use a T+1 approach and complete the batch process externally with compute frameworks such as Hive or Apache Spark, but this cannot achieve real-time processing. The other is to perform real-time Join in stream processing systems such as Apache Flink before loading data into the AP system. This has more limitations and is very complex to implement.

Conditional Update

Conditional update allows data to be updated only when the loaded data meets certain conditions. A typical scenario is when upstream data arrives out of order and a timestamp is needed to determine whether an update should be applied.

Read-Write Update

Partial update and Conditional update are essentially read-write transactions. Compared with pure-write transactions such as Upsert and Delete, they are more complex to implement. After conflicts occur, a simple Last Write Wins strategy no longer applies and further processing is required. However, compared with general read-write transactions, there are still certain characteristics that can be used to optimize conflict detection and resolution.

ELT Updates

ELT (Extract-Load-Transform) is becoming increasingly popular. Raw data is first loaded into the AP system, and then various SQL statements are used inside the system for further processing. This not only accelerates queries and reduces development and O&M complexity, but also makes it possible to manage data dependencies within a single system.

Complex ELT data flows often involve batch updates to data. These are usually large-scale read-write transactions over batch data. Traditional concurrent control mechanisms in TP systems cannot effectively address the challenges created by large transactions, so innovative solutions are needed.