List of OLAP databases that support row level updates using primary key for real time streaming inserts/update/delete/upsert
OLAP databases are designed for online analytical processing, which is the process of analyzing large amounts of data to identify trends and patterns. OLAP databases typically store data in a multidimensional format, which means that data is organized into dimensions and measures. For example, a customer data warehouse might have dimensions for customer, product, and order, and measures for sales, quantity, and revenue.
Primary keys are typically used in relational databases to uniquely identify rows in a table. However, OLAP databases do not typically use primary keys because they are not designed for transactional processing. In OLAP databases, data is typically loaded in batches, and it is not necessary to ensure that each row is unique. Additionally, using primary keys can slow down query performance in OLAP databases.
Instead of primary keys, OLAP databases typically use surrogate keys. Surrogate keys are artificial keys that are generated by the database and are not meaningful to the user. Surrogate keys are used to uniquely identify rows in an OLAP database, but they do not have any business meaning.
WITH THAT SAID, StarRocks and others DO SUPPORT PRIMARY KEYS AND SURROGATE KEYS. One the main reasons why databases like StarRocks support primary keys is to support the streaming inserts and updates to the database. This is why we can support mutable and immutable data within the database. It allows us to support use cases like streaming data in real time from transaction processing systems into StarRocks and join multiple streams by performing update operations on individual columns.
Here is a list of known OLAP databases that support primary keys.
- StarRocks: https://docs.starrocks.io/en-us/latest/table_design/table_types/primary_key_table
- ClickHouse: https://clickhouse.com/docs/en/optimize/sparse-primary-indexes#a-table-with-a-primary-key
- SingeStore: https://docs.singlestore.com/cloud/create-a-database/understanding-keys-and-indexes-in-singlestore/
More info at https://github.com/alberttwong/databasecomparison
