Real-Time Analytics Architecture with ClickHouse: A Production Blueprint
Real-time analytics is no longer an exotic requirement — it is the baseline expectation for product analytics, observability, fraud detection, and operational intelligence. In this guide, ChistaDATA engineers walk through a production-grade real-time analytics architecture built on ClickHouse, covering ingestion pipelines, storage layout, query patterns, and high-availability topology.
Why ClickHouse for Real-Time Analytics
ClickHouse is a columnar OLAP database engine purpose-built for analytical workloads that demand sub-second latency over billions of rows. Unlike general-purpose OLTP databases, ClickHouse is optimized around vectorized execution, SIMD-accelerated aggregations, and aggressive compression — characteristics that make it uniquely suited for real-time analytics at scale.
At ChistaDATA, we routinely deploy ClickHouse clusters that ingest millions of rows per second while simultaneously serving interactive dashboards with p99 query latency under 500 ms. Public case studies echo this: Netflix ingests 5 petabytes of logs daily into ClickHouse, serving 10.6M events/second with sub-second queries, and OpenAI runs ClickHouse at petabyte scale for observability.
Three architectural properties make ClickHouse the right engine for real-time analytics:
- Append-optimized MergeTree storage that handles high-throughput streaming inserts without blocking reads.
- Materialized views as incremental transformations that pre-aggregate data at insert time.
- Sparse primary indexes and data skipping indexes that turn wide-column scans into narrow IO.
Reference Architecture: The Five Layers
A production ClickHouse real-time analytics platform typically has five logical layers. We recommend designing each layer independently so that scaling, failure modes, and cost can be reasoned about in isolation.
Layer Purpose Typical Technology
1. Source Event producers (apps, services, IoT) SDKs, OpenTelemetry, CDC
2. Stream Bus Buffering, ordering, replay Kafka, Redpanda, Pulsar
3. Ingest Schema mapping, enrichment, batching Kafka Engine, Vector, clickhouse-kafka-connect
4. Storage & Compute Columnar storage and query execution ClickHouse (ReplicatedMergeTree)
5. Serving Dashboards, APIs, alerting Grafana, Superset, custom APIs
For most workloads we recommend Kafka → ClickHouse Kafka Engine → Materialized View → ReplicatedMergeTree as the default pattern. It provides durability in Kafka, schema flexibility in the materialized view, and durability plus replication in the final table.
Streaming Ingest with the Kafka Engine
The ClickHouse Kafka Engine is a table function that reads messages directly from Kafka topics. When combined with a materialized view, it becomes a first-class streaming ingest pipeline that consumes, parses, and persists events with backpressure handling built in.
-- 1. Kafka consumer table
CREATE TABLE events_kafka
(
event_time DateTime64(3, 'UTC'),
user_id UInt64,
event_type LowCardinality(String),
country LowCardinality(String),
payload String
)
ENGINE = Kafka
SETTINGS
kafka_broker_list = 'kafka-1:9092,kafka-2:9092,kafka-3:9092',
kafka_topic_list = 'events',
kafka_group_name = 'clickhouse-events-consumer',
kafka_format = 'JSONEachRow',
kafka_num_consumers = 4,
kafka_thread_per_consumer = 1,
kafka_max_block_size = 65536;
-- 2. Durable destination table
CREATE TABLE events
(
event_time DateTime64(3, 'UTC'),
user_id UInt64,
event_type LowCardinality(String),
country LowCardinality(String),
payload String,
ingest_time DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (event_type, country, event_time)
TTL toDate(event_time) + INTERVAL 90 DAY;
-- 3. Materialized view that pushes Kafka rows into the MergeTree
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT event_time, user_id, event_type, country, payload, now() AS ingest_time
FROM events_kafka;
Three production tips we apply at ChistaDATA when designing this layer: size kafka_num_consumers to match topic partitions; use LowCardinality(String) for any categorical dimension with under a few thousand distinct values; and never write directly into the Kafka table — always go through a materialized view so that schema evolution is decoupled from the consumer.
Storage Layout: Partitioning, Ordering, and Granularity
The physical layout of data on disk determines 80% of query performance in ClickHouse. The three knobs we tune are the PARTITION BY key, the ORDER BY (sort) key, and the index granularity.
Partitioning
Partitions are independent directories on disk. The right cardinality is usually daily or monthly — fine enough for retention policies and part pruning, but coarse enough to avoid the “too many parts” problem. A common anti-pattern is partitioning by hour on tables with heavy inserts; this frequently produces more than 1000 parts per partition and can trigger the too_many_parts throttle.
Sort Key
Place low-cardinality, high-selectivity columns first in the ORDER BY, then higher-cardinality columns. For real-time analytics, (tenant_id, event_type, event_time) is a frequent pattern — it enables tenant isolation, fast filtered scans, and ordered time-series retrieval in a single sort order.
Data Skipping Indexes
ALTER TABLE events ADD INDEX idx_user_id user_id TYPE bloom_filter(0.01) GRANULARITY 4, ADD INDEX idx_country country TYPE set(100) GRANULARITY 4;
Bloom filters and set indexes let ClickHouse skip entire granules when a filter does not match, turning what would be a full column scan into targeted IO. We consistently see 5–20x reductions in rows read after adding the right skipping indexes.
High Availability and Sharding Topology
For mission-critical real-time analytics, ChistaDATA recommends a ReplicatedMergeTree + Distributed engine topology coordinated by ClickHouse Keeper (or ZooKeeper on legacy deployments).
-- Distributed table that fans queries out to N shards
CREATE TABLE events_distributed AS events
ENGINE = Distributed(
'analytics_cluster', -- cluster name from config.xml
'default', -- database
'events', -- local table on each shard
cityHash64(user_id) -- sharding key
);
- Replication factor of 2 per shard minimum — one replica survives node loss while the second is rebuilding.
- ClickHouse Keeper over ZooKeeper for new deployments. Keeper is written in C++, uses less memory, and recovers faster under load.
- Pin writes to one replica per shard through the load balancer to simplify idempotency reasoning; replication then fans out to other replicas.
- Rack-aware placement so that replicas of the same shard never share a failure domain.
Real-Time Query Patterns That Stay Fast
Real-time analytics queries fall into three shapes: point lookups, rolling aggregations, and ad-hoc slice and dice. Each has a preferred ClickHouse pattern.
-- Rolling 5-minute active users per country
SELECT
toStartOfFiveMinute(event_time) AS bucket,
country,
uniqExact(user_id) AS active_users
FROM events
WHERE event_time >= now() - INTERVAL 1 HOUR
GROUP BY bucket, country
ORDER BY bucket DESC, active_users DESC
SETTINGS max_threads = 16;
Three optimizations we apply by default:
- Use uniqCombined64 or uniqHLL12 for cardinality estimation on very large sets — accuracy within 1% at a fraction of the memory cost of uniqExact.
- Push down filters into the primary key columns so ClickHouse can prune granules before reading.
- Use FINAL sparingly on ReplacingMergeTree tables. Prefer argMax patterns to avoid the merge-on-read penalty.
Observability, Capacity Planning, and Cost Controls
A production real-time analytics platform is as much about operating the system as designing it. We instrument every ChistaDATA-managed ClickHouse cluster with three signal layers:
- System tables — system.query_log, system.part_log, system.merges, and system.replication_queue. These four tables expose 90% of what an on-call engineer needs.
- Built-in Prometheus endpoint via the prometheus handler, scraped every 15 seconds into long-term storage.
- Query budgets — profile-level limits on max_memory_usage, max_execution_time, and max_rows_to_read to prevent runaway queries from destabilizing the cluster.
<profiles>
<dashboard>
<max_memory_usage>10000000000</max_memory_usage>
<max_execution_time>30</max_execution_time>
<max_rows_to_read>5000000000</max_rows_to_read>
<max_result_rows>100000</max_result_rows>
<readonly>1</readonly>
</dashboard>
</profiles>
For cost control, we apply tiered storage via the storage_policy setting — hot data on NVMe, warm data on SSD, cold data on S3 object storage. A 90-day retention policy with S3 tier-down typically cuts storage cost by 70% while preserving query-on-read for historical investigations.
Key Takeaways
- ClickHouse delivers sub-second analytics on billions of rows when the storage layout is designed around the primary query patterns.
- Always ingest from Kafka through a Kafka Engine + materialized view pattern — never let applications write directly to MergeTree.
- Partition daily or monthly; sort by (low-cardinality, high-selectivity) columns first, event_time last.
- Use LowCardinality and data skipping indexes (bloom_filter, set) to reduce rows scanned by 5–20x.
- Run ReplicatedMergeTree with at least 2 replicas per shard and coordinate via ClickHouse Keeper.
- Enforce per-profile memory, time, and row limits to keep dashboards stable under query bursts.
- Tier storage hot → warm → S3 to keep retention long and cost predictable.
How ChistaDATA Can Help
At ChistaDATA Inc., we are 100% focused on ClickHouse. Our engineering team delivers ClickHouse consulting, 24×7 managed services, performance engineering, and mission-critical support for analytics workloads ranging from gigabytes to multi-petabyte clusters. Whether you are building a real-time analytics platform from scratch, migrating from Snowflake, BigQuery, or Redshift, or tuning an existing ClickHouse deployment, we can help accelerate outcomes. Schedule a consultation with our ClickHouse engineering team to discuss architecture, capacity planning, and performance SLAs.
Frequently Asked Questions
How is ClickHouse different from Snowflake or BigQuery for real-time analytics?
ClickHouse is an open-source, self-managed or cloud-managed columnar OLAP database optimized for high-throughput streaming inserts and sub-second query latency. Snowflake and BigQuery are managed data warehouses optimized for batch analytics with higher query latencies (typically 1-10s) and per-query billing. For real-time analytics, ClickHouse generally delivers 10-100x faster query latency and significantly lower cost per query at scale.
What ingest rate can a single ClickHouse node sustain?
A well-tuned ClickHouse node on modern hardware (32 vCPU, NVMe, 128 GB RAM) routinely sustains 500,000 to 2,000,000 rows per second on a single table, depending on row width and compression ratio. Horizontal scaling via sharding allows linear scaling into the tens of millions of rows per second.
Do we need Kafka in front of ClickHouse for real-time analytics?
Not strictly, but we strongly recommend it. Kafka (or Redpanda) provides durability, replay, backpressure, and decoupling between producers and the analytics store. It also smooths out burst traffic that would otherwise create many small parts in ClickHouse and trigger merge pressure.
How do we handle schema evolution in a real-time ClickHouse pipeline?
ClickHouse supports online ALTER operations for adding columns, changing TTL, and adjusting indexes without blocking queries. For evolving event schemas, we recommend a JSON or Map(String, String) column for dynamic fields, with materialized columns promoting frequently-queried keys into strongly-typed columns as the schema stabilizes.
What is the typical p99 query latency we should expect?
For dashboard queries on properly designed tables (good sort key, data skipping indexes, sensible partition), p99 latency is 50-500 ms on cluster sizes from 3 to 50 nodes. Ad-hoc analytical queries scanning tens of billions of rows typically complete in 1-5 seconds. Achieving these numbers requires deliberate schema design, not out-of-the-box defaults.
How does ChistaDATA support production ClickHouse deployments?
ChistaDATA provides 24×7 managed ClickHouse services, performance engineering, architecture review, and migration services from Snowflake, BigQuery, Redshift, and Druid. Engagements range from one-time performance audits to fully managed multi-region clusters with SLA-backed uptime and latency commitments.
You might also like:
- ClickHouse Search: Manticore Full Text Search with Plain Index
- bare-metal.io – Run analytical workloads for one-fourth of the AWS costs
- Understanding Named Collections in ClickHouse
- Why Delta Updates Are Not Recommended in OLAP Databases: A Performance and Efficiency Perspective
- Reduce Query Memory Usage in ClickHouse
