Real-Time OLAP and Analytical Databases

Parent: data analysis · researched 2026-05-30T22:38:58.644Z· 25 sources · 11 concepts · skill da-28-realtime-olap-databases

Real-time OLAP databases are the query and storage engines that answer

Overview

Real-time OLAP databases are the query and storage engines that answer [source]
analytical questions (aggregations, group-bys, filters, top-N, time-series [source]
rollups) over large, continuously updating datasets with **sub-second [source]
latency and high concurrency**. They sit between stream processing (which [source]
transforms events in flight - da-14) and the BI/semantic layer (da-18), serving [source]
as the low-latency serving layer for dashboards, monitoring, and [source]
user/customer-facing analytics. [source]
What makes an engine "real-time OLAP" rather than a cloud data warehouse: [source]
Fresh data: rows are queryable seconds (often sub-second) after they land, [source]
via streaming ingestion - not after a nightly batch load. [source]
High concurrency / QPS: built to serve thousands of concurrent queries [source]
(a per-user dashboard feature), not a handful of internal analysts. ClickHouse [source]
reports 1,000+ concurrent queries per node; Snowflake defaults to ~8 queries [source]
per warehouse, Redshift caps ~50 concurrent across queues [source]
(ClickHouse, 2025). [source]
Tight latency SLAs: tens of milliseconds, achieved with columnar storage, [source]
vectorized execution, and pre-aggregation/indexing. [source]
This skill covers the engines and the query layer. It is the OLAP-database [source]
node of the data-analytics curriculum (da-1 onward); it does not cover stream [source]
processing (da-14), pipeline orchestration (da-13), or the metrics layer (da-18). [source]

1. Columnar storage

Data is stored by column, not by row. Analytical queries touch few columns [source]
but many rows, so columnar layout reads only the needed columns, drastically [source]
cutting I/O, and stores like-typed values together so they compress far better [source]
(delta, dictionary, RLE, LZ4/ZSTD). This is the foundational OLAP advantage over [source]
row stores (SQLFlash, 2025; [source]
Airbyte, 2024). [source]
Compression both saves storage and increases effective scan throughput. [source]

2. Vectorized execution

Instead of processing one row at a time through a tuple-at-a-time interpreter, [source]
the engine processes batches (vectors) of column values in tight loops, [source]
exploiting CPU SIMD instructions, cache locality, and amortized [source]
virtual-function/branch overhead. This yields multi-x to order-of-magnitude [source]
speedups and is paired with columnar storage in every modern engine [source]
(SQLFlash, 2025; [source]
Cockroach Labs). [source]
Even hybrid OLTP/OLAP systems (Google Spanner's columnar engine, OceanBase 4.3) [source]
adopt columnar + vectorized execution for analytics, up to ~200x faster on live [source]
data (InfoQ, 2025). [source]

3. Real-time vs batch analytics (Lambda / Kappa)

Batch: data loaded and computed periodically; high latency, high accuracy, [source]
cheap recompute (classic warehouse / BI pattern). [source]
Real-time: data queryable seconds after arrival; low latency, continuous [source]
Lambda keeps two paths: a batch layer for correctness and a speed layer [source]
for low latency, at the cost of dual code and reconciliation. [source]
Kappa collapses everything into a single streaming path over an immutable [source]
log (e.g., Kafka), replaying when recompute is needed; simpler but harder for [source]
large historical batch jobs [source]
(bix-tech, 2025; [source]
Materialize). [source]
Most 2025 teams run a hybrid: streaming for operational decisions, batch for [source]
trusted reporting and model training [source]
(makitsol, 2025). [source]

4. Streaming ingestion & upserts

Real-time OLAP engines ingest directly from Kafka, Pulsar, and Kinesis. [source]
Apache Pinot transforms bytes from Kafka into queryable segments with sub-second [source]
visibility from write to query as the default; end-to-end latency under 5s is [source]
(StarTree, 2025; [source]
Confluent). [source]
Append-only is the simplest model (events never change). Pinot was [source]
append-only until 2022. [source]
Upserts let the same key be ingested many times but return only the latest [source]
value at query time. Pinot supports full upsert (new row replaces the old [source]
entirely) and partial upsert (only specified columns update) [source]
(Pinot deep dive, 2026). [source]
StarRocks uses a primary-key table model (cloud-native PK index in [source]
shared-data) for upserts and CDC-style mutable data [source]
(StarRocks, 2025). [source]

5. Materialized views & pre-aggregation

Precompute aggregates so dashboard queries hit ready answers instead of scanning [source]
Pinot star-tree index: precomputes selected aggregation paths during [source]
segment generation; returns aggregation/group-by over billions of rows in [source]
milliseconds when the query shape matches configured dimensions/metrics, with [source]
no separate MV maintenance. Benchmarks show ~126x throughput over an inverted [source]
index (27 to 3,494 QPS on 4 vCPU) [source]
(Pinot docs; [source]
StarTree, 2023). [source]
StarRocks / Doris asynchronous materialized views: precompute joins and [source]
aggregations; the optimizer automatically rewrites base-table queries to [source]
use the MV (transparent query rewrite). Can be built over external lake [source]
catalogs (Iceberg/Hive/Hudi/Paimon) [source]
(StarRocks docs; [source]
Doris docs). [source]
ClickHouse materialized views / projections: MVs are insert-time triggers [source]
that populate a target table (often with AggregatingMergeTree); projections [source]
store an alternate sorted/aggregated copy inside the part. [source]

6. Indexing & the ClickHouse MergeTree model

ClickHouse's MergeTree family writes each INSERT as an immutable **data [source]
part** (one file per column + index), merged in the background. [source]
Sparse primary index: one mark per granule (default 8,192 rows), stored [source]
in primary.idx; it does not enforce uniqueness, it lets the engine skip [source]
granules that cannot match a filter [source]
(ClickHouse docs). [source]
Data-skipping indexes (minmax, set(N), bloom_filter, ngrambf_v1, [source]
tokenbf_v1) summarize non-key columns so granules can be skipped on [source]
secondary predicates; tune GRANULARITY and materialize after adding to [source]
existing data (oneuptime, 2026). [source]
Druid instead indexes within each segment (300-700 MB target) including [source]
inverted (bitmap) indexes for fast filtering [source]
(Druid docs). [source]

7. Star-schema-on-OLAP & denormalization

Two camps: (a) flatten/denormalize into one wide table for fastest scans [source]
(historically favored by Druid/ClickHouse, which are weaker at large joins); [source]
(b) keep the star/snowflake schema and join at query time. StarRocks [source]
explicitly preserves star/snowflake schemas and does real-time pre-processing at [source]
load, with a strong distributed join engine, so you avoid the maintenance burden [source]
of giant denormalized tables [source]
(StarRocks features; [source]
jusdb, 2026). [source]
Rule of thumb: denormalize when joins dominate latency and the engine joins [source]
poorly; keep the star schema when the engine joins well and dimensions change. [source]

8. Storage-compute separation / shared-data / tiered storage

Modern engines decouple cheap durable object storage (S3/GCS/Azure Blob) [source]
from elastic compute, mirroring cloud-DW architecture but for real-time [source]
workloads. StarRocks 3.0+ shared-data mode replaces storage-bearing backends [source]
with compute nodes (CN) that cache hot data and read cold data from S3, [source]
giving elastic scaling; 4.0 cut object-store API costs and reached 15-30s data [source]
freshness in this mode [source]
(StarRocks architecture; [source]
Medium/Ding, 2025). [source]
Tiered storage (hot local SSD to warm/cold object store) is now standard across [source]
ClickHouse, Druid, Pinot, and StarRocks. [source]

9. High-QPS user-facing / customer-facing analytics

"User-facing analytics" embeds analytics in the product, exposed to end [source]
users, so every user gets personalized metrics, producing **hundreds of thousands [source]
of QPS** rather than a few analyst sessions [source]
(Pinot). This is the workload real-time OLAP engines [source]
exist for and where cloud DWs fail on concurrency/cost. Pinot has served [source]
20,000+ QPS at sub-second p99 with 99.99% availability via star-tree [source]
pre-aggregation; ClickHouse powers a customer-facing feature and a BI dashboard [source]
from one service (StarTree; [source]
ClickHouse, 2025). [source]

Tools / Frameworks

Benchmarks (treat as directional, vendor-published): StarRocks reports [source]
ClickHouse ~2.2x and Druid ~8.9x slower on 13 SSB flat-table queries; Pinot [source]
reported 2-4x faster than Druid on some queries [source]
(StarRocks; [source]
StarTree). [source]

Methodology — choosing & designing

Classify the workload. Internal batch BI / ad-hoc to cloud DW (Snowflake/ [source]
BigQuery/Redshift) or DuckDB for single-node. User-facing / sub-second / high [source]
QPS / streaming-fresh to a real-time OLAP engine. [source]
Match engine to query shape. [source]
Massive single-table aggregations, want simplicity: ClickHouse. [source]
Per-user dashboards, very high QPS, fixed query shapes: Pinot (star-tree). [source]
Interactive time-series exploration with high concurrency: Druid. [source]
Real-time analytics needing joins on a star schema, lakehouse: StarRocks/Doris. [source]
Embedded/local, no server, Parquet on disk: DuckDB. [source]
Decide the freshness path (Kappa-style streaming vs hybrid Lambda) and the [source]
mutability model (append-only vs full/partial upsert). [source]
Model the schema: denormalize for join-weak engines; keep star schema for [source]
StarRocks/Doris with strong join engines. [source]
Pre-aggregate intentionally: star-tree (Pinot) or async MVs (StarRocks/ [source]
Doris) or MV+projections (ClickHouse) for the known dashboard query shapes. [source]
Tune indexing: sort key / primary index ordered by your most selective [source]
filter; add data-skipping/bitmap indexes for secondary predicates. [source]
Right-size storage: separate storage/compute (shared-data) and tier hot to cold [source]
to control cost at scale. [source]

Practical Patterns

Kafka to real-time OLAP serving layer: stream events to Kafka, ingest into [source]
Pinot/Druid/ClickHouse for sub-5s freshness, serve the product dashboard [source]
directly from the engine. [source]
CDC upserts: stream Postgres/MySQL changes (Debezium/ClickPipes) into a [source]
PK/upsert table so the OLAP store mirrors mutable source state. [source]
Query-shape-driven pre-aggregation: enumerate the dimensions/metrics your [source]
dashboards actually use, then build a matching star-tree or async MV; do not [source]
pre-aggregate everything. [source]
Two-tier serving: cloud DW for deep batch/historical + real-time OLAP engine [source]
for the hot, user-facing layer; sync via Iceberg or scheduled exports. [source]
Sort by the dominant filter: order the table by the column(s) most queries [source]
filter/range on (e.g., (tenant_id, timestamp)) so the sparse index skips the [source]

Anti-Patterns

Using a cloud DW for user-facing analytics: concurrency caps (Snowflake ~8, [source]
Redshift ~50) and per-query cost make per-user dashboards slow and expensive [source]
(ClickHouse, 2025). [source]
Real-time OLAP for OLTP: these engines are not for point updates/deletes, [source]
transactions, or single-row lookups by a service of record. [source]
Pre-aggregating for query shapes you do not run: star-trees and MVs cost [source]
storage and ingestion CPU; build them for real query shapes only. [source]
Over-indexing ClickHouse: too many data-skipping indexes slow inserts and [source]
rarely help; they also do not help with negations [source]
(oneuptime, 2026). [source]
Expecting DuckDB to be a streaming/high-concurrency server: it is [source]
in-process, single-node, batch/interactive, not a serving engine [source]
(Kestra, 2026). [source]
Many tiny inserts into MergeTree: floods the engine with small parts; batch [source]
inserts (or use async inserts) so merges keep up. [source]
Treating vendor benchmarks as neutral: SSB/flat-table benchmarks favor the [source]
publisher; validate on your own query shapes and data. [source]

Troubleshooting

Slow aggregation / group-by: confirm pre-aggregation matches the query [source]
shape (star-tree dims/metrics, MV grouping keys); check vectorized path is used. [source]
High latency under concurrency: check QPS vs node count; add star-tree/MV; [source]
scale compute (shared-data CNs) horizontally. [source]
Stale data: inspect ingestion lag (Kafka consumer lag, segment commit/ [source]
handoff in Pinot/Druid, publish batching/freshness in StarRocks shared-data). [source]
Query scans too much data: primary/sort key not aligned to the dominant [source]
filter, or missing data-skipping/bitmap index; reorder sort key. [source]
Insert pressure / "too many parts" (ClickHouse): inserts too small/frequent; [source]
batch them; let background merges catch up. [source]
Upsert results look wrong: verify full vs partial upsert semantics and that [source]
the upsert primary key and partitioning are configured correctly. [source]
Costs spiking on object store (shared-data): API call volume; enable batch [source]
publish / caching (StarRocks 4.0 addressed this) and size the local hot cache. [source]

References

ClickHouse - How the 5 major cloud data warehouses compare on cost-performance (2025): https://clickhouse.com/blog/cloud-data-warehouses-cost-performance-comparison [source]
ClickHouse - ClickHouse vs Snowflake for Real-Time Analytics (2025): https://clickhouse.com/blog/clickhouse-vs-snowflake-for-real-time-analytics-comparison-migration-guide [source]
ClickHouse docs - A practical introduction to primary indexes (sparse index/granules): https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes [source]
oneuptime - Data Skipping with Sparse Indexes in ClickHouse (2026): https://oneuptime.com/blog/post/2026-03-31-clickhouse-data-skipping-sparse-indexes/view [source]
oneuptime - Avoid Over-Indexing in ClickHouse (2026): https://oneuptime.com/blog/post/2026-03-31-clickhouse-avoid-over-indexing/view [source]
StarTree - Inside the flight path of real-time ingestion in Apache Pinot (2025): https://startree.ai/resources/inside-the-flight-path-of-real-time-ingestion-in-apache-pinot/ [source]
StarTree - A Tale of Three Real-Time OLAP Databases (Pinot/Druid/ClickHouse): https://startree.ai/resources/a-tale-of-three-real-time-olap-databases/ [source]
StarTree - Star-Tree Index Part 2: High Concurrency (2023): https://startree.ai/resources/star-tree-indexes-in-apache-pinot-part-2-understanding-the-impact-during-high-concurrency/ [source]
Apache Pinot docs - Star-Tree Index: https://docs.pinot.apache.org/build-with-pinot/indexing/star-tree-index [source]
pdpspectra - Apache Pinot Deep Dive 2026: User-Facing Analytics, Upserts (2026): https://pdpspectra.com/blog/apache-pinot-realtime-olap-2026/ [source]
Confluent - Real-Time Analytics with Apache Kafka and Pinot: https://www.confluent.io/blog/real-time-analytics-with-kafka-and-pinot/ [source]
StarRocks - Benchmark: StarRocks vs ClickHouse, Druid, Trino: https://www.starrocks.io/blog/benchmark-test [source]
StarRocks docs - Query rewrite with materialized views: https://docs.starrocks.io/docs/using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views/ [source]
StarRocks docs - Architecture (shared-data / compute nodes): https://docs.starrocks.io/docs/introduction/Architecture/ [source]
jusdb - StarRocks Database (2026): Architecture & Real-Time Analytics Guide (2026): https://www.jusdb.com/blog/starrocks-explained-the-complete-guide-to-real-time-analytics [source]
Apache Doris docs - Overview of Asynchronous Materialized Views: https://doris.apache.org/docs/query-acceleration/materialized-view/async-materialized-view/overview/ [source]
Apache Druid docs - Segments: https://druid.apache.org/docs/latest/design/segments/ [source]
Apache Druid docs - Ingestion: https://druid.apache.org/docs/latest/ingestion/index.html [source]
SQLFlash - OLAP Database Architecture: Columnar Storage & Vectorized Execution (2025): https://sqlflash.ai/article/20250722_olap-database-architecture/ [source]
InfoQ - Google Spanner Unifies OLTP and OLAP with Columnar Engine (2025): https://www.infoq.com/news/2025/09/google-spanner-oltp-olap-unify/ [source]
bix-tech - Kappa vs. Lambda vs. Batch: https://bix-tech.com/kappa-vs-lambda-vs-batch-choosing-the-right-data-architecture-for-your-business/ [source]
Materialize - When Is Kappa Architecture Most Effective?: https://materialize.com/blog/when-is-kappa-architecture-most-effective/ [source]
makitsol - Real-Time Analytics vs Batch Processing (2025): https://makitsol.com/real-time-analytics-vs-batch-processing-in-us-eu/ [source]
Kestra - Embedded Databases in 2026: DuckDB, SQLite, Polars, chDB (2026): https://kestra.io/blogs/embedded-databases [source]
Tinybird - OLAP databases: what's new and what's best in 2026 (2026): https://www.tinybird.co/blog/best-database-for-olap [source]
Estuary - Top 10 Real-Time OLAP Databases in 2026 (2026): https://estuary.dev/blog/real-time-olap-databases/ [source]
pracdata - State of Open Source Real-Time OLAP Systems 2025 (2025): https://www.pracdata.io/p/state-of-open-source-read-time-olap-2025 [source]

Children

Columnar storage (frontier)
Vectorized (SIMD) execution (frontier)
Real-time vs batch analytics (Lambda/Kappa) (frontier)
Streaming ingestion and upserts (Kafka/Pulsar/Kinesis) (frontier)
Materialized views and pre-aggregation (frontier)
ClickHouse MergeTree indexing (sparse primary index, data-skipping) (frontier)
Star-schema-on-OLAP and denormalization (frontier)
Storage-compute separation / shared-data / tiered storage (frontier)
High-QPS user-facing analytics (frontier)
OLAP engines vs cloud data warehouses (frontier)
Embedded OLAP (DuckDB) (frontier)

Frontier under this node: ClickHouse MergeTree indexing (sparse primary index, data-skipping), Columnar storage, Embedded OLAP (DuckDB), High-QPS user-facing analytics, Materialized views and pre-aggregation, OLAP engines vs cloud data warehouses, Real-time vs batch analytics (Lambda/Kappa), Star-schema-on-OLAP and denormalization, Storage-compute separation / shared-data / tiered storage, Streaming ingestion and upserts (Kafka/Pulsar/Kinesis), Vectorized (SIMD) execution

← the whole tree · 3D view· how to read this page