Real-Time Analytics at Scale: ClickHouse vs Apache Druid Deep Dive - Data Engineering Hub

← Back to Home Real-Time Analytics at Scale: ClickHouse vs Apache Druid Deep Dive Table of Contents Introduction CDC Approaches Debezium Kafka Connect Exactly-Once Schema Evolution Monitoring Operational Best Practices Comparison Recommendations Conclusion Real-Time OLAP: The Requirements Modern applications need sub-second analytical queries on streaming data: user-facing dashboards, anomaly detection, ad-tech bidding, and observability. ClickHouse: The Analytical Powerhouse Columnar DBMS designed for analytical queries with vectorized execution. Architecture MergeTree Engine Family: - MergeTree (basic) - ReplacingMergeTree (deduplication) - SummingMergeTree (pre-aggregation) - AggregatingMergeTree (materialized views) - CollapsingMergeTree (state updates) - VersionedCollapsingMergeTree (versioned updates) - GraphiteMergeTree (Graphite metrics) Data Ingestion Streaming Ingestion -- Kafka Engine Table (native consumption) CREATE TABLE events_kafka ( event_id String, user_id UInt64, event_type String, properties Map(String, String), event_time DateTime64(3) ) ENGINE = Kafka() SETTINGS kafka_broker_list = 'broker1:9092,broker2:9092', kafka_topic_list = 'events', kafka_group_name = 'clickhouse_consumer', kafka_format = 'JSONEachRow', kafka_num_consumers = 4; -- Materialized view for MergeTree storage CREATE MATERIALIZED VIEW events_mv ENGINE = ReplacingMergeTree(event_time) PARTITION BY toYYYYMM(event_time) ORDER BY (user_id, event_type, event_time) AS SELECT * FROM events_kafka; Batch Ingestion (High Throughput) -- Native format (fastest) clickhouse-client --query "INSERT INTO events FORMAT Native" < events.bin -- Or via HTTP curl -X POST 'http://localhost:8123/?query=INSERT+INTO+events+FORMAT+CSV' --data-binary @events.csv.gz -H "Content-Encoding: gzip" Query Performance Optimization Primary Key Design -- Good: high cardinality first for filtering ORDER BY (tenant_id, event_time, event_type) -- Bad: low cardinality first ORDER BY (event_type, tenant_id, event_time) Projections for Common Queries ALTER TABLE events ADD PROJECTION by_user ( SELECT user_id, event_type, count() as cnt GROUP BY user_id, event_type ORDER BY user_id ); Materialized Views for Pre-aggregation CREATE MATERIALIZED VIEW hourly_metrics ENGINE = SummingMergeTree() PARTITION BY toYYYYMM(hour) ORDER BY (tenant_id, event_type, hour) AS SELECT tenant_id, event_type, toStartOfHour(event_time) as hour, count() as event_count, uniqExact(user_id) as unique_users FROM events GROUP BY tenant_id, event_type, hour; Scaling ClickHouse Sharding -- Distributed table across shards CREATE TABLE events_distributed AS events ENGINE = Distributed(cluster, db, events, rand()); Replication -- ReplicatedMergeTree for HA ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}') PARTITION BY toYYYYMM(event_time) ORDER BY (tenant_id, event_time) Apache Druid: The Streaming Analytics Database Designed for sub-second queries on streaming and batch data with native time-series optimization. Architecture Druid Cluster: - Coordinator (segment management) - Overlord (ingestion supervision) - Broker (query routing) - Router (optional, unified endpoint) - Historical (cold data) - MiddleManager (ingestion workers) - Peons (task runners) - Real-time (streaming ingestion) Data Ingestion Native Kafka Indexing Service { "type": "kafka", "spec": { "dataSchema": { "dataSource": "events", "timestampSpec": { "column": "event_time", "format": "iso" }, "dimensionsSpec": { "dimensions": [ "user_id", "event_type", "tenant_id", { "type": "json", "name": "properties" } ] }, "metricsSpec": [ { "type": "count", "name": "event_count" }, { "type": "hyperUnique", "name": "unique_users", "fieldName": "user_id" } ], "granularitySpec": { "type": "uniform", "segmentGranularity": "HOUR", "queryGranularity": "MINUTE" } }, "ioConfig": { "topic": "events", "consumerProperties": { "bootstrap.servers": "broker:9092", "group.id": "druid_events" }, "taskCount": 4, "replicas": 1, "taskDuration": "PT1H" }, "tuningConfig": { "type": "kafka", "maxRowsInMemory": 1000000, "maxBytesInMemory": 100000000, "maxPendingPersists": 5 } } } Batch Ingestion (Native Parallel) { "type": "index_parallel", "spec": { "dataSchema": { ... }, "ioConfig": { "type": "index_parallel", "inputSource": { "type": "s3", "paths": ["s3://bucket/events/2026/01/01/"] }, "inputFormat": { "type": "json" } }, "tuningConfig": { "type": "index_parallel", "maxRowsPerSegment": 5000000, "maxTotalRows": 100000000, "numBackgroundThreads": 8 } } } Query Performance Segment Granularity HOUR: Best for high-volume real-time (smaller segments, faster queries) DAY: Better for batch-heavy workloads Rollup for Pre-aggregation "metricsSpec": [ { "type": "count", "name": "event_count" }, { "type": "longSum", "name": "total_value", "fieldName": "value" }, { "type": "hyperUnique", "name": "unique_users", "fieldName": "user_id" }, { "type": "thetaSketch", "name": "user_sketch", "fieldName": "user_id", "size": 16384 } ] Approximate Algorithms HyperLogLog: Unique counts (hyperUnique) Theta Sketches: Set operations (union, intersection) Quantiles/DDSketches: Percentiles Bloom Filters: Existence checks Scaling Druid Historical: Scale horizontally for storage/query throughput MiddleManager: Scale for ingestion parallelism Broker: Scale for query concurrency Deep Storage: S3/GCS/HDFS for segment persistence ClickHouse vs Druid: Detailed Comparison Aspect ClickHouse Apache Druid Architecture Shared-nothing, vectorized Disaggregated (compute/storage) Storage Format Columnar (MergeTree) Columnar (segments + bitmaps) Compression Excellent (LZ4, ZSTD, Delta) Good (LZ4, dictionary, bitmap) Join Support Full SQL joins Limited (lookup joins, broadcast) Subqueries Full support Limited Window Functions Full support Limited Time-Series Optimizations Good (MergeTree) Native (segment granularity, rollup) Approximate Algorithms Good (HLL, quantiles) Excellent (HLL, Theta, DDSketch) Real-Time Ingestion Latency 1-5 seconds (Kafka engine) seconds (native indexing) Exactly-Once Ingestion Kafka offsets + idempotent writes Transactional indexing (Kafka) Schema Changes ALTER TABLE (instant metadata) Re-ingestion or schema evolution Backfill/Reprocessing INSERT + OPTIMIZE Re-indexing tasks Query Language ClickHouse SQL (extended) Druid SQL (subset) + native JSON JOIN Performance Excellent (hash join, merge join) Broadcast joins only High Cardinality Dimensions Good Challenging (dictionary encoding limits) Multi-Tenancy Row-level security, quotas Limited (namespace isolation) Operational Complexity Moderate (single binary) High (many components) Cloud Offerings ClickHouse Cloud, Altinity Cloud Imply Cloud, self-managed Typical Performance Profiles ClickHouse Strengths Complex analytical queries with JOINs Ad-hoc exploration with high cardinality dimensions Wide tables (hundreds of columns) Vectorized execution for aggregations Materialized views for pre-computation Druid Strengths Time-series dashboards with fixed query patterns High-throughput streaming ingestion Approximate distinct counts / set operations Sub-second queries on billions of rows (with rollup) Native Kafka integration with exactly-once Hybrid Architectures Many organizations use both: Streaming Data -> Kafka |-> Druid (real-time dashboards, fixed queries, approx algorithms) +-> ClickHouse (ad-hoc analysis, complex joins, ML feature store) When to Choose What Requirement Choose Complex SQL, JOINs, subqueries, window functions ClickHouse Sub-second fixed-pattern dashboards on streaming data Druid High cardinality dimensions (user_id, session_id) ClickHouse Approximate distinct counts, set operations Druid Simpler operations (single binary, fewer configs) ClickHouse Native exactly-once Kafka ingestion Druid Schema evolution without re-ingestion ClickHouse Cost optimization via aggressive rollup Druid ML feature store / wide tables ClickHouse Geo-spatial queries ClickHouse (native) Operational Best Practices ClickHouse Monitor merge mutations and parts count Use TTL for automatic data expiration Configure appropriate merge_tree settings Enable query logging for analysis Druid Monitor segment loading/unloading on Historicals Tune middleManager heap for ingestion tasks Configure segment granularity for your query patterns Use compaction tasks for segment optimization Conclusion ClickHouse excels as a general-purpose analytical database with full SQL support. Druid excels as a specialized real-time analytics platform with best-in-class approximate algorithms and native streaming ingestion. The choice depends on query complexity, team expertise, and operational capacity. Related Articles Batch Processing at Scale Streaming Data Pipelines Modern Data Warehousing Data Quality and Testing Data Observability Data Orchestration