Skip to content

Low-latency serving stores

Optional

Databases placed next to the lakehouse to answer queries in milliseconds for many concurrent users, kept in sync with the platform: real-time analytical databases for fast aggregates, and key-value stores for fast lookups.

Before reading

Query performance on lake tables · Processing patterns: batch, micro-batch and streaming

Why it matters

Lakehouse engines read files from object storage. Even well-tuned queries take hundreds of milliseconds to seconds, and engines handle tens of concurrent queries, not thousands. A web page, an in-app dashboard or a fraud check needs answers in milliseconds for every user at once. When the lake cannot meet that, a serving store does, at the cost of another system to run and keep consistent.

How it works

The first question is whether a serving store is needed. Many cases are solved more simply: a pre-aggregated table read through a cache, a small relational database loaded from gold tables, or a longer acceptable delay. A serving store is justified when latency, concurrency or freshness requirements cannot be met otherwise.

Real-time analytical databases, also called real-time OLAP, answer aggregate queries in milliseconds over large, constantly arriving data. Examples are ClickHouse, Apache Pinot, Apache Druid and StarRocks. They store data in their own columnar format on local disks or with local caching, keep indexes and pre-aggregations, ingest directly from Kafka within seconds, and are built for high query concurrency. They suit user-facing analytics: a seller's live dashboard, view counts, operational monitoring.

Key-value and document stores return one record by key in about a millisecond (in-memory) to single-digit milliseconds (durable stores), at very high concurrency. Examples are Redis and Valkey for in-memory data, and DynamoDB, Cassandra or ScyllaDB for durable data at scale. They suit lookups computed by the platform: a customer's recommendations, a product's current stock, features for a machine learning model at prediction time. They do not answer ad hoc questions; every access pattern has to be known in advance and designed into the key.

Relational databases, such as PostgreSQL, fill the middle ground for moderate volumes: indexed lookups, small aggregates and flexible queries.

flowchart LR
  K[Kafka topics] --> RT[(Real-time OLAP:<br/>live counts, seller<br/>dashboards)]
  K --> LAKE[(Lakehouse:<br/>history, modelling)]
  LAKE -->|batch load of<br/>computed results| KV[(Key-value store:<br/>recommendations,<br/>stock by product)]
  RT --> APP[Applications<br/>and data API]
  KV --> APP

Keeping a serving store in sync is the main design problem. There are two feeds. Streaming feeds read the same Kafka topics as the lake, so the store is current within seconds; real-time OLAP databases usually ingest this way. Batch feeds load results computed in the lake, such as daily recommendations, on a schedule; a common technique is to load a new version of the table and switch readers to it in one step, so users never see a half-loaded state.

Whichever feed is used, the lake stays the source of truth. The serving store holds a copy shaped for one access pattern, and can be rebuilt from the lake or from Kafka. Its definitions, such as how a view is counted, must match the lake's, or the live number on the homepage and the number in the monthly report disagree.

Key terms

  • Serving store: a database optimised for fast application queries, fed by the platform.
  • Real-time OLAP: databases that answer aggregate queries in milliseconds on fresh data.
  • Key-value store: a database that returns records by key with very low latency.
  • Concurrency: the number of queries served at the same time.
  • Versioned load: loading a new table version and switching readers to it at once.
  • Source of truth: the system whose data is authoritative when copies disagree.

Example

The shop shows "viewed 1,240 times in the last hour" on each product tile on the homepage and on product pages. The lake can compute this, but every page load would query it.

The clickstream topic feeds a real-time OLAP table that ingests product view events within seconds, with bot traffic already removed by a shared rule (user agents on a bot list, or more than 100 views a minute from one session), defined once in the clickstream contract. Each tile calls the data API, which runs (ClickHouse SQL):

SELECT count(*) AS views
FROM shop.product_views
WHERE product_id = 'p-332'
  AND viewed_at > now() - INTERVAL 1 HOUR;

At peak, 3,000 such queries per second return in under 20 milliseconds. The same events land in the lake through the Iceberg sink, where daily view counts are modelled for reporting. Both use the same rule for what counts as a view, bots excluded, defined in the clickstream contract.

Pitfalls

  • Adding a serving store before trying simpler options. Caching and pre-aggregation often suffice.
  • Different definitions in the store and the lake. Live and reported numbers disagree.
  • No rebuild path. If the store cannot be reloaded from the lake or Kafka, it becomes a second source of truth.
  • Ad hoc queries on key-value stores. They answer only the access patterns their keys were designed for.
  • Ignoring operating cost. Another stateful system needs monitoring, scaling, backups and on-call.

Learn more

  • ClickHouse documentation, introduction: https://clickhouse.com/docs/intro
  • Apache Pinot documentation: https://docs.pinot.apache.org/
  • Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd edition (O'Reilly, 2026), the chapters on derived data and stream processing: https://dataintensive.net/

Data APIs and the backend-for-frontend pattern · Stream processing · Kafka and Kafka Connect · Cost and FinOps