The StarRocks Primary Key model is a table model designed for analytical workloads that require frequent data updates and deletes. It uses primary keys to identify records, helping maintain an up-to-date view of business data for real-time analytics.

  1. Primary index
    A primary index maps primary keys to row locations. When an update or delete arrives, the index helps locate the existing record without scanning the entire table. Persistent index options can reduce reliance on memory, depending on the deployment and configuration.
  2. Delete-and-Insert mechanism
    For updates, StarRocks writes the new record and marks the previous record as deleted. Queries exclude deleted records rather than merging multiple record versions at query time. Compaction subsequently reclaims obsolete data.
  3. Flexible update capabilities
    Partial updates allow ingestion workflows to supply selected columns instead of complete rows. Conditional updates help control which incoming records replace existing data, supporting business rules and handling out-of-order updates.
  4. Primary keys and sort keys
    Primary keys identify records for updates and deletes, while sort keys organize stored data to improve query efficiency. These roles are distinct: record identity and query access patterns may require different key designs.
  5. Typical use cases
    The model supports CDC-based synchronization from transactional databases, frequently updated customer or player profiles, order status analytics, and logistics tracking. It is particularly useful when analytical queries need to reflect changes to existing records rather than only newly appended events.

Effective implementation requires appropriate primary key design, index configuration, and update strategies. Query performance and data freshness depend on the workload, ingestion pipeline, resource allocation, and deployment architecture.

FAQ

How does the StarRocks Primary Key model handle updates?

It uses a primary index to locate the existing record, marks that record as deleted, and writes the updated record. Queries read the current data without merging record versions at query time.

Can selected columns be updated without replacing the entire row?

Yes. Partial updates allow supported ingestion workflows to provide the primary key and selected columns. Configuration and behavior depend on the ingestion method and supported capabilities.

When should the Primary Key model be used?

Use it when records change frequently and analytics must reflect their current state. Common examples include CDC-fed data warehouses, user profiles, order records, and shipment status tracking.