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

Relational databases choke when aggregating billions of time-series telemetry events. Harness ClickHouse AggregatingMergeTree and stateful materialized views to serve sub-30ms analytical rollups on billion-row datasets.

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;

Operational Telemetry, Capacity Sizing & Production Verification

When implementing the patterns behind High-Density Time-Series Analytics: ClickHouse AggregatingMergeTree and Materialized Views for Billion-Row Telemetry in a live production environment, database engineers must validate performance improvements against real buffer-pool and disk I/O telemetry rather than relying on synthetic micro-benchmarks run against cold caches. In PostgreSQL and Redis production clusters, the primary risk during high-concurrency traffic spikes is not raw CPU saturation—it is lock contention, shared-buffer cache eviction churn, and write-ahead log (WAL) flush latency.

Before rolling these configuration or schema changes out to your primary production node, execute a three-step verification protocol on a staging replica populated with production-scale data volumes:

  1. Baseline Buffer & I/O Inspection: Capture before-and-after execution plans using EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) to verify that Buffers: shared read (disk page fetches) drops in favor of shared hit, and inspect pg_stat_io or Redis INFO memory to confirm zero unexpected eviction spikes.
  2. Concurrency & Lock Queue Stress Testing: Simulate 100 to 500 concurrent application sessions through PgBouncer transaction pooling while monitoring pg_stat_activity for Lock or LWLock:BufferMapping wait events.
  3. Automated Rollback & Timeout Guardrails: Always bind DDL and batch maintenance operations to explicit lock_timeout = '2000ms' and statement_timeout thresholds so background administrative tasks never block customer-facing checkout or API transactions.

How do we monitor regressions after deploying this database optimization?

Track mean_exec_time, stddev_exec_time, and shared_blks_read in pg_stat_statements alongside p99 query latency alerts. If you need hands-on assistance tuning your database cluster, explore our Database & High-Throughput Backend Engineering Services or schedule a Database Architecture Review.

Architectural Continuity & Deep Dives

For related production architectures and system implementations, explore these companion guides:

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.

Interactive PostgreSQL Memory & Tuning Calculator

// Real-Time Production Memory Allocator
PostgreSQL 14 / 15 / 16 / 17

Adjust server resources below to calculate optimized postgresql.conf memory thresholds, autovacuum scale factors, and planner cost weights.

16 GB
2 GB 64 GB 256 GB
8 Cores
2 16 64
100
20 200 1,000
generated-postgresql.conf
INI
# Memory Allocations
shared_buffers = 4GB
work_mem = 41MB
maintenance_work_mem = 983MB
effective_cache_size = 12GB

# Concurrency & Background Workers
max_connections = 100
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Autovacuum Tuning (Prevent Bloat)
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000

# Planner Cost Constants (NVME)
random_page_cost = 1.1
effective_io_concurrency = 200
Need hands-on database profiling? Our engineering team analyzes query execution plans, resolves lock trees, and eliminates replication lag.
Book Database Audit (30m)
// Production Systems Architecture • Database Diagnostic Audit

Diagnosing Production PostgreSQL Bloat, Lock Contention, or Replication Lag?

Theoretical tuning only goes so far. Our engineering team conducts hands-on architectural reviews of query execution plans, autovacuum parameters, connection pools, and read-replica lag for high-concurrency systems.

All Insights
Chat on WhatsApp