ClickHouse real time analytics

Real-time analytics is not a faster version of batch reporting. It is a different architecture where data arrives continuously, queries run against the live stream, and results reflect what happened seconds ago rather than what happened yesterday. ClickHouse® was designed for this workload from the ground up: columnar storage for fast scans, vectorized execution for fast aggregation, append-optimized ingestion for continuous writes, and materialized views for pre-computation on arrival.

This post covers the schema patterns, ingestion paths, query techniques, and pre-aggregation strategies that make ClickHouse the right engine for real-time analytics.

What real-time analytics requires

Batch analytics refreshes on a schedule: hourly ETL jobs, nightly data warehouse loads, morning report generation. The data is always stale by at least one refresh interval. Real-time analytics inverts this: data is queryable within seconds of arrival, and dashboards reflect current state without cache invalidation or pre-computation jobs.

The technical requirements:

• Ingestion latency under seconds. Events must be writable and queryable without waiting for batch loads.
• Query latency under 100ms. Dashboard filters, API endpoints, and alerting queries must respond fast enough for interactive use.
• Continuous write throughput. The system must accept a steady stream of events without write amplification degrading read performance.
• Flexible aggregation. Ad-hoc queries across dimensions (time, user, product, region) without pre-defining every possible grouping.

Row-oriented databases fail on all four requirements at scale. ClickHouse addresses each through architectural decisions made at the storage and execution layer.

Event schema for real-time analytics

The foundation is an append-only event table with a sort key aligned to query patterns:

CREATE TABLE events
(
    event_time      DateTime64(3),
    event_type      LowCardinality(String),
    user_id         String,
    session_id      String,
    product_id      LowCardinality(String),
    region          LowCardinality(String),
    revenue         Nullable(Float64),
    properties      String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time, user_id);

event_type first in the sort key means queries filtering by event type skip the majority of data granules. event_time second enables efficient time-range filtering. LowCardinality on dimensions with under 10,000 distinct values reduces storage and accelerates GROUP BY.

For implementing real-time analytics, getting the sort key right is the single most impactful schema decision. A misaligned sort key means every query scans the full table regardless of filters.

Streaming ingestion paths

ClickHouse accepts data through multiple ingestion channels, each suited to different source types:

Direct INSERT via HTTP. The HTTP interface accepts JSONEachRow, CSV, Parquet, and other formats. Batch inserts of 1,000-10,000 rows perform best. The Events API in Tinybird wraps this with authentication, rate limiting, and automatic schema detection.

Kafka engine. ClickHouse reads directly from Kafka topics without an intermediate consumer application:

CREATE TABLE kafka_events
(
    event_time  DateTime64(3),
    event_type  LowCardinality(String),
    user_id     String,
    revenue     Nullable(Float64)
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list  = 'analytics.events',
    kafka_group_name  = 'clickhouse_consumer',
    kafka_format      = 'JSONEachRow';

Materialized view routing. A materialized view reads from the Kafka engine table and inserts into the target MergeTree table, filtering and transforming as data arrives.

For enterprise-grade real-time analytics, the ingestion path must handle backpressure, schema evolution, and at-least-once delivery without duplicate events polluting aggregations. ClickHouse's ReplacingMergeTree deduplicates on a version column, making at-least-once ingestion safe.

Real-time aggregation queries

ClickHouse's aggregation functions run over billions of rows in milliseconds when the sort key aligns with query filters:

-- Live dashboard: events and revenue by type, last hour
SELECT
    event_type,
    toStartOfMinute(event_time)     AS minute,
    count()                         AS events,
    uniq(user_id)                   AS unique_users,
    round(sum(revenue), 2)          AS revenue
FROM events
WHERE event_time >= now() - INTERVAL 1 HOUR
GROUP BY event_type, minute
ORDER BY minute DESC, events DESC;

Conditional aggregation with countIf, sumIf, and uniqIf avoids subqueries:

SELECT
    toStartOfHour(event_time)                   AS hour,
    countIf(event_type = 'purchase')            AS purchases,
    countIf(event_type = 'page_view')             AS page_views,
    round(sumIf(revenue, event_type = 'purchase'), 2) AS revenue,
    uniq(user_id)                               AS unique_users
FROM events
WHERE event_time >= today()
GROUP BY hour
ORDER BY hour;

For analytics at scale, these queries run in under 100ms on tables with hundreds of millions of rows when the sort key matches the filter columns.

Pre-aggregation with materialized views

Dashboard queries that run every few seconds should read from pre-aggregated tables, not scan raw events on every request:

CREATE TABLE hourly_metrics
(
    hour            DateTime,
    event_type      LowCardinality(String),
    region          LowCardinality(String),
    event_count     UInt64,
    user_count      AggregateFunction(uniq, String),
    total_revenue   Float64
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (event_type, region, hour);

CREATE MATERIALIZED VIEW hourly_metrics_mv TO hourly_metrics AS
SELECT
    toStartOfHour(event_time)   AS hour,
    event_type,
    region,
    count()                     AS event_count,
    uniqState(user_id)          AS user_count,
    sum(revenue)                AS total_revenue
FROM events
GROUP BY hour, event_type, region;

A dashboard query reading from hourly_metrics touches thousands of rows instead of hundreds of millions. The trade is write amplification: every insert into events triggers the materialized view computation. For real-time analytics, this trade is almost always worth making.

Sampling for exploratory queries

When exploring large event tables, ClickHouse's SAMPLE clause reads a fraction of data for statistically representative results:

SELECT
    event_type,
    count() * 100 AS estimated_count,
    uniq(user_id) * 100 AS estimated_users
FROM events SAMPLE 0.01
WHERE event_time >= today() - 7
GROUP BY event_type
ORDER BY estimated_count DESC;

SAMPLE 0.01 reads 1% of granules and extrapolates. Useful for ad-hoc exploration before running expensive exact queries on the full dataset.

TTL for monitoring data lifecycle

Real-time analytics generates continuous data that grows without bound. ClickHouse TTL rules manage retention automatically:

ALTER TABLE events MODIFY TTL
    event_time + INTERVAL 7 DAY TO VOLUME 'hot',
    event_time + INTERVAL 90 DAY TO VOLUME 'warm',
    event_time + INTERVAL 1 YEAR DELETE;

Hot data on NVMe serves live dashboards. Warm data on standard SSD serves historical queries. Data older than 1 year deletes automatically without manual partition drops.

Latency comparison with batch alternatives

The difference between real-time and batch analytics is not incremental. It is architectural.

For building real-time apps, the sub-100ms query latency that ClickHouse delivers is what makes user-facing analytics, live dashboards, and API-served metrics possible. Batch alternatives cannot serve interactive use cases regardless of how much hardware you add.

Tinybird for real-time analytics

Building real-time analytics on ClickHouse requires managing cluster infrastructure, schema migrations, Kafka consumer groups, materialized view backfills, and monitoring query performance. Tinybird is managed ClickHouse that handles the infrastructure so teams focus on the analytical SQL.

Every pattern in this post maps directly to Tinybird: the events table becomes a datasource, the materialized view becomes a Pipe with TYPE MATERIALIZED, and the dashboard query becomes an endpoint Pipe published as a parameterized HTTP API. Real-time streaming data architectures describe the ingestion patterns that Tinybird's Events API and Kafka connector implement without custom consumer code.

For query optimization, how to make SQL database faster principles apply directly to ClickHouse: sort key alignment, LowCardinality dimensions, PREWHERE filters, and pre-aggregation via materialized views.

Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Tinybird is SOC 2 Type II certified. The pipeline from event source to live analytics API takes hours to configure rather than weeks to build.

Parameterized SQL endpoints accept date range, event type, and dimension filters from your frontend or BI tool. Each filter change triggers a fresh query against live data, not a cache lookup against yesterday's pre-computation. Your product team sees metrics update as events arrive. Your engineering team stops maintaining ETL schedules and cache invalidation logic that exist only because the underlying database was too slow for interactive queries.

When real-time becomes the default

Teams that adopt real-time analytics stop compensating for stale data. Product managers stop asking "when was this dashboard last refreshed?" Engineers stop building caching layers that exist only because the underlying database is too slow. Alerting systems query live data instead of waiting for the next batch job.

ClickHouse makes real-time the default case, not an edge case requiring special infrastructure. The ingestion, aggregation, and serving patterns in this post are the standard way to build analytics that keep up with your data.