What Is Real-Time Analytics?
Real-time analytics provides insights on data that is seconds to minutes old — not the hours or days of traditional batch BI. Use cases: live dashboard showing active users on a website, per-minute conversion rate for an A/B test, real-time revenue during a product launch, and anomaly detection on live metrics. The challenge: high ingest volume (billions of events per day) combined with fast, complex analytical queries (GROUP BY, COUNT DISTINCT, percentile aggregations) over large datasets.
The OLAP vs OLTP Distinction
OLTP (Online Transaction Processing): databases like MySQL/PostgreSQL optimized for point reads/writes (SELECT user WHERE id=123), high concurrency, low latency per query, row-oriented storage. OLAP (Online Analytical Processing): databases like ClickHouse/Druid/BigQuery optimized for analytical scans (COUNT(*) WHERE date BETWEEN x AND y GROUP BY country), moderate concurrency, column-oriented storage. The fundamental difference: OLAP stores data by column (all values for “country” are together on disk), enabling 10-100× faster reads for analytical queries that only access a few columns out of hundreds. Row-oriented storage reads all columns even for single-column aggregations.
ClickHouse Architecture
ClickHouse is a column-oriented OLAP database optimized for high-ingest analytics. Key features:
- MergeTree engine: data is written in sorted “parts” (like SSTable files in LSM trees) and merged asynchronously in the background. Parts are sorted by the ORDER BY key — queries with a WHERE clause on this key can skip entire parts (sparse index). This enables 1-10 billion row/second scan speeds on modern hardware.
- Columnar compression: each column is compressed independently using codecs suited to its data type (LZ4 for general data, DoubleDelta for timestamps, Gorilla for time-series floats). 10:1 compression is common — a 10TB dataset compresses to 1TB, reducing I/O by 10×.
- Vectorized execution: queries process data in batches of 1,024 rows using SIMD CPU instructions, achieving 10× throughput over row-by-row processing.
- Materialized views: pre-aggregated tables are maintained automatically as data is inserted. Queries against the materialized view run in milliseconds instead of seconds. Example: a materialized view aggregating (event_type, country, hour, count) is updated automatically on every insert.
Apache Druid for Streaming Analytics
Druid combines streaming ingest (real-time segments updated as data arrives) with OLAP query performance. Architecture: brokers route queries; historical nodes serve immutable deep segments (from S3); real-time nodes ingest from Kafka and serve hot segments. Data flows: Kafka → Druid real-time nodes (segment published after 10 minutes) → historical nodes (for older data). Druid pre-computes rollups at ingest time: raw events are aggregated to (minute, country, event_type, sum/count/distinct) as they arrive — raw event storage is not kept, only pre-aggregated granularity. This reduces storage by 10-100× at the cost of not being able to re-aggregate at finer granularity than the original ingest.
The Lambda Architecture
Lambda architecture handles both high-velocity streaming and historical batch analytics by maintaining two parallel pipelines:
- Batch layer: processes all historical data (Spark on S3/Parquet). Output: accurate batch views. Recomputed periodically (daily/hourly). Slow but correct — handles late-arriving data, corrections.
- Speed layer: processes real-time stream (Flink/Kafka Streams). Output: real-time views (fast, approximate for very recent data). Serves queries with low latency but only covers recent data.
- Serving layer: queries merge results from batch and speed layers. Example: for “active users in last 7 days,” combine accurate batch results (days 2-7) with real-time stream results (last hour).
Kappa architecture simplifies this by using only a streaming pipeline for everything — reprocess historical data by replaying Kafka from the beginning. Simpler operations; requires the stream processor to handle reprocessing at batch scale.
Approximate Algorithms for Scale
Exact COUNT DISTINCT queries on billions of rows are expensive. Approximate algorithms trade a small accuracy loss for massive performance gains:
- HyperLogLog: estimates the count of distinct elements using only 12KB of memory per counter, with ±0.5% error. Used by Redis (PFADD/PFCOUNT), Druid, and BigQuery for approximate DISTINCT counts. Mergeable: HLLs from different time windows or shards can be combined with union.
- Count-Min Sketch: estimates the frequency of individual items in a stream using a small fixed-size matrix. Used for finding heavy hitters (top-K most frequent events). Overestimates never, underestimates bounded by sketch size.
- T-Digest: approximate percentile computation (P50, P95, P99 latency) in a streaming fashion with high accuracy at the tails. More accurate than reservoir sampling for high-percentile queries.
Query Optimization for Dashboards
Analytics dashboards have predictable query patterns: the same queries run every 30 seconds to refresh metrics. Optimizations: (1) Result caching — cache the last query result with TTL = dashboard refresh interval. Dashboard shows slightly stale data (30-60 seconds) but reads never hit the analytical database for cache hits. (2) Pre-aggregation — materialize common GROUP BY combinations at ingest time. “Active users per country per minute” is always queried at the country-minute granularity — pre-aggregate at that level. (3) Scheduled queries — run expensive queries on a schedule (every 5 minutes) and serve from a result store rather than running on demand. (4) Tiered storage — hot data (last 30 days) in ClickHouse on SSD; cold data (older) in Parquet on S3, queried via Presto/Athena when needed.
Interview Checklist
- Ingest pipeline: Kafka → Flink/Spark Streaming → ClickHouse/Druid
- Storage: column-oriented OLAP DB; columnar compression; sparse index on ORDER BY key
- Real-time vs batch: speed layer (Flink) + batch layer (Spark) = Lambda; or Kappa (stream only)
- Approximate analytics: HyperLogLog for distinct counts, T-Digest for percentiles
- Query optimization: materialized views, result caching, pre-aggregation, scheduled queries
- Freshness vs accuracy tradeoff: real-time has seconds latency but may be approximate; batch is exact but hours stale
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “Why is ClickHouse so much faster than PostgreSQL for analytical queries?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “ClickHouse and PostgreSQL are optimized for fundamentally different access patterns. PostgreSQL is row-oriented: all columns for a row are stored together on disk. An analytical query like SELECT COUNT(*), country FROM events GROUP BY country must read ALL columns (id, user_id, event_type, timestamp, country, session_id, …) for every row, even though only the “country” column is needed. ClickHouse is column-oriented: each column is stored separately. The same query reads only the “country” column file u2014 skipping all other columns entirely. For a table with 100 columns and a query using 3 columns, ClickHouse reads 3% of the data vs PostgreSQL’s 100%. Additional optimizations: columnar data with the same value type compresses 10-100u00d7 better than mixed-type rows (LZ4 codec, DoubleDelta for timestamps). Vectorized execution processes 1,024 values at a time using SIMD CPU instructions rather than row-by-row. Sparse indexes allow skipping data ranges that don’t match WHERE clauses without reading them. Combined: ClickHouse can run the same analytical query 100-1000u00d7 faster than PostgreSQL on large datasets.”
}
},
{
“@type”: “Question”,
“name”: “What is the difference between Lambda architecture and Kappa architecture for streaming analytics?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Lambda architecture maintains two parallel data pipelines: a batch layer (Spark on HDFS/S3, accurate but hours-old) and a speed layer (Flink/Kafka Streams, real-time but approximate or incomplete). A serving layer merges results from both for queries. Advantages: handles late-arriving data (batch reprocesses everything correctly), allows corrections (re-run batch with fixed logic), separates real-time and historical processing concerns. Disadvantages: two codebases (one for batch, one for streaming) that must produce identical semantics u2014 complex to maintain, debug, and evolve simultaneously. Kappa architecture uses only a streaming pipeline. Historical reprocessing is done by replaying Kafka from the beginning (keep raw events in Kafka with long retention, or replay from S3). Advantages: single codebase (Flink handles both real-time and backfill), simpler operations. Disadvantages: Kafka replay for years of data is slower than Spark batch (less parallelism than HDFS-based batch), and the stream processor must handle backpressure from bulk replay. Modern recommendation: start with Lambda for mature systems with separate batch and streaming teams; use Kappa when the streaming framework can handle both cases efficiently and you want to minimize operational complexity.”
}
},
{
“@type”: “Question”,
“name”: “How does HyperLogLog estimate distinct counts with only 12KB of memory?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “HyperLogLog (HLL) estimates the number of distinct elements in a stream using a fixed 12KB data structure regardless of the number of elements, with u00b10.5% error. The intuition: hash each element to a random-looking bit string. In a random bit string, the probability of observing k leading zeros is 2^(-k). If we see an element with 10 leading zeros, we’ve probably seen at least 2^10 = 1,024 distinct elements. HLL uses multiple hash functions (or one hash split into multiple registers) to get a robust estimate: maintain 2^b registers (b=14 u2192 16,384 registers). For each element, use the first b bits to select a register and update it with the count of leading zeros in the remaining bits. Estimate = HLL_CONST u00d7 (registers)u00b2 / sum(2^(-register_value)). The harmonic mean across registers reduces variance. Merging HLLs: take element-wise max of register arrays. This enables distributed computation: compute HLL on each partition separately, merge for the global distinct count. Use cases: unique visitors per page (Redis PFADD/PFCOUNT), unique search queries per day, distinct active users across services u2014 all without storing the actual user IDs.”
}
}
]
}