High-Density Time-Series Analytics: ClickHouse AggregatingMergeTree and Materialized Views for Billion-Row Telemetry

Why Columnar Storage Crushes Relational Databases for Telemetry

Engineering teams tracking production telemetry—such as API latency histograms, IoT sensor feeds, cloud billing metrics, and user clickstreams—inevitably hit an architectural wall when using traditional row-oriented relational databases like PostgreSQL or MySQL. In row stores, every single row is stored contiguously on disk. To execute an analytical query calculating the 99th percentile response time across 500 million HTTP access logs over the last 30 days, the database engine must read entire multi-gigabyte row tuples from disk, saturate I/O channels, and burn CPU cycles decompressing unused columns.

ClickHouse, a blazingly fast open-source columnar database, solves this fundamentally through architectural specialization. Data is stored strictly in column-oriented files compressed with algorithm-specific codecs (such as LZ4 or ZSTD) and vectorized vector execution using SIMD CPU instructions. When querying latency across 500 million records, ClickHouse reads only the single column requested from disk, streaming tens of gigabytes per second directly into CPU registers.

The AggregatingMergeTree Engine & Intermediate Aggregate States

While basic columnar scanning is orders of magnitude faster than relational row scans, querying raw multi-billion-row tables on every dashboard refresh will still tax cluster hardware. In standard databases, teams attempt to solve this by creating scheduled summary tables that roll up data hourly. However, traditional rollups lose statistical fidelity: you cannot compute an accurate 99th percentile latency across pre-computed hourly averages.

ClickHouse addresses this with the AggregatingMergeTree engine. Instead of storing final aggregated numbers, ClickHouse stores serialized intermediate aggregate state representations in specialized columns ending with State. Later, when querying across arbitrary dimensions and time boundaries, ClickHouse combines these intermediate states with zero loss of precision using matching functions ending in Merge.

Constructing Stateful Materialized Views

Consider an enterprise API platform recording billions of HTTP request logs. Below is the production ClickHouse schema that transforms raw events into instantaneous pre-aggregated analytics:

-- 1. Raw Telemetry Ingestion Table (Engine: MergeTree)
CREATE TABLE raw_http_telemetry (
    timestamp DateTime64(3, 'UTC') CODEC(DoubleDelta, LZ4),
    service_name LowCardinality(String) CODEC(ZSTD(1)),
    endpoint LowCardinality(String) CODEC(ZSTD(1)),
    http_status UInt16 CODEC(T64, ZSTD(1)),
    latency_ms Float32 CODEC(Gorilla, ZSTD(1)),
    user_id UUID CODEC(ZSTD(1))
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (service_name, endpoint, timestamp);

-- 2. Target Aggregate Rollup Table (Engine: AggregatingMergeTree)
CREATE TABLE telemetry_hourly_rollups (
    window_start DateTime CODEC(DoubleDelta, LZ4),
    service_name LowCardinality(String) CODEC(ZSTD(1)),
    endpoint LowCardinality(String) CODEC(ZSTD(1)),
    total_requests UInt64 CODEC(T64, ZSTD(1)),
    error_count UInt64 CODEC(T64, ZSTD(1)),
    -- Intermediate states for exact unique users and quantile percentiles
    unique_users AggregateFunction(uniq, UUID),
    latency_quantiles AggregateFunction(quantilesExactWeighted(0.50, 0.90, 0.99), Float32, UInt32)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(window_start)
ORDER BY (service_name, endpoint, window_start);

-- 3. Automatic Streaming Materialized View
-- Intercepts raw inserts and updates the AggregatingMergeTree table in real-time
CREATE MATERIALIZED VIEW mv_telemetry_hourly TO telemetry_hourly_rollups AS
SELECT
    toStartOfHour(timestamp) AS window_start,
    service_name,
    endpoint,
    count() AS total_requests,
    countIf(http_status >= 500) AS error_count,
    uniqState(user_id) AS unique_users,
    quantilesExactWeightedState(0.50, 0.90, 0.99)(latency_ms, 1) AS latency_quantiles
FROM raw_http_telemetry
GROUP BY window_start, service_name, endpoint;

Benchmarking Dashboard Queries: Sub-30ms Analytical Rollups

When visualizing performance over the last 90 days, we query the materialized view using the companion -Merge aggregate functions. ClickHouse stitches together thousands of intermediate states instantaneously:

SELECT
    service_name,
    sum(total_requests) AS total_volume,
    sum(error_count) AS total_server_errors,
    -- Merge unique user HyperLogLog states
    uniqMerge(unique_users) AS distinct_active_users,
    -- Merge T-Digest / exact quantile states with full mathematical precision
    quantilesExactWeightedMerge(0.50, 0.90, 0.99)(latency_quantiles) AS latencies
FROM telemetry_hourly_rollups
WHERE service_name = 'payment-gateway'
  AND window_start >= now() - INTERVAL 30 DAY
GROUP BY service_name;

In our production benchmarks querying 1.2 billion raw telemetry events, running this query against the raw table took 4.8 seconds and processed 6.4 GB of data. Running the identical calculation against the AggregatingMergeTree materialized view executed in 18 milliseconds, scanned just 1.4 MB of compressed state data, and yielded the exact same p99 latency figures.

Automated Data Retention & Compaction Tuning

To prevent storage costs from expanding exponentially, implement automated Time-To-Live (TTL) retention policies directly on the underlying tables:

-- Automatically purge raw high-resolution telemetry after 14 days
ALTER TABLE raw_http_telemetry MODIFY TTL timestamp + INTERVAL 14 DAY;

-- Retain aggregated hourly rollups for 3 years
ALTER TABLE telemetry_hourly_rollups MODIFY TTL window_start + INTERVAL 3 YEAR;

Production Takeaway

ClickHouse's AggregatingMergeTree bridges the gap between massive real-time write ingestion and instant analytical querying. By storing intermediate mathematical states rather than raw rows or static averages, you gain the ability to compute exact quantiles and unique visitor counts over billions of rows in milliseconds, unlocking enterprise-scale telemetry on minimal VPS infrastructure.