Real-Time Ad Telemetry: ClickHouse OLAP Architecture for 500k QPS

Modern programmatic advertising exchanges, Supply-Side Platforms (SSPs), and Demand-Side Platforms (DSPs) process immense streaming event volumes. A tier-one ad exchange routinely evaluates between 500,000 and 1,000,000 bid requests per second (QPS), generating billions of telemetry events daily across auction bids, win notifications, impressions, video quartile beacons, and post-click conversions. Storing and querying this high-cardinality data stream—featuring complex multidimensional tuples of publisher IDs, placement IDs, device types, geo-locations, and DSP buyer seats—exhausts traditional relational databases and general-purpose time-series engines. Engineering high-throughput ad telemetry demands the high-performance analytical capabilities of ClickHouse columnar storage.

1. Why Columnar Storage Dominates High-QPS Ad Exchanges

In programmatic ad exchanges, analytical queries rarely require complete row reconstruction. A reporting dashboard calculating publisher eCPM across hourly intervals only needs three specific columns: event_timestamp, publisher_id, and clearing_price_micros. In a row-oriented database, the storage engine must read the entire multi-kilobyte row off disk—including user-agent strings, IP addresses, and creative JSON payloads—saturating I/O bandwidth.

ClickHouse stores each column in independent physical files on NVMe storage. When executing analytical queries, the engine reads exclusively the necessary columns from disk. Furthermore, because identical data types sit contiguously in memory, ClickHouse applies domain-specific compression algorithms (LZ4, ZSTD, Gorilla for timestamps, and DoubleDelta for sequential integers), yielding massive compression ratios that reduce Petabyte-scale operational storage overhead.

Sparse primary indexing is fundamental to this performance profile. Unlike B-tree indexes that store an index entry for every single row, ClickHouse stores one index mark per index granularity block (typically 8,192 rows). The entire sparse primary index resides permanently in RAM, allowing the query engine to eliminate non-matching data granules in microseconds before touching physical NVMe disks.

2. Ingestion Pipeline: Kafka Engine & Materialized Views

Direct HTTP or TCP inserts from hundreds of distributed bidding nodes into ClickHouse create high-frequency micro-batches that degrade LSM-tree compaction. Instead, ad exchange backends deploy an asynchronous event streaming pipeline backed by Apache Kafka:

-- ClickHouse Kafka Engine & Materialized View Pipeline
-- 1. Ephemeral Kafka Engine Table
CREATE TABLE default.kafka_ad_impressions_stream (
    event_time DateTime64(3, 'UTC'),
    auction_id UUID,
    publisher_id LowCardinality(String),
    dsp_seat_id LowCardinality(String),
    country_code LowCardinality(FixedString(2)),
    device_type LowCardinality(String),
    clearing_price_cpm Float64,
    latency_ms UInt16
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka1.internal:9092,kafka2.internal:9092',
         kafka_topic_list = 'ad_impressions_stream',
         kafka_group_name = 'ch_impressions_consumer',
         kafka_format = 'JSONEachRow',
         kafka_num_consumers = 8;

-- 2. Authoritative Columnar Destination Table
CREATE TABLE default.ad_impressions_raw (
    event_date Date,
    event_time DateTime64(3, 'UTC'),
    auction_id UUID,
    publisher_id LowCardinality(String),
    dsp_seat_id LowCardinality(String),
    country_code LowCardinality(FixedString(2)),
    device_type LowCardinality(String),
    clearing_price_cpm Float64,
    latency_ms UInt16
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
PRIMARY KEY (publisher_id, event_date)
ORDER BY (publisher_id, event_date, dsp_seat_id, country_code, event_time)
SETTINGS index_granularity = 8192;

-- 3. Streaming Materialized View Pipeline
CREATE MATERIALIZED VIEW default.mv_ad_impressions_consumer TO default.ad_impressions_raw AS
SELECT
    toDate(event_time) AS event_date,
    event_time,
    auction_id,
    publisher_id,
    dsp_seat_id,
    country_code,
    device_type,
    clearing_price_cpm,
    latency_ms
FROM default.kafka_ad_impressions_stream;

The Kafka Engine automatically polls messages from broker partitions in batches of 100,000 events, piping them through the Materialized View into the destination MergeTree table with zero intermediate application-tier consumer code required.

3. Optimizing High-Cardinality Dimensions with LowCardinality Types

In programmatic telemetry, dimensions such as publisher_id (5,000 unique keys), country_code (250 keys), and device_type (5 keys) repeat billions of times. Storing these dimensions as raw strings bloats disk indices and memory structures.

ClickHouse provides the LowCardinality data type modifier. LowCardinality converts strings into dictionary-encoded integer keys under the hood. When aggregating or filtering by publisher or country, ClickHouse performs operations over compact 8-bit or 16-bit numeric arrays, accelerating analytical GROUP BY queries by 5× to 10× while preserving human-readable string semantics in SQL queries.

4. Comparative Matrix: Big Data OLAP Engines for Ad Telemetry

5. AggregatingMergeTree: Pre-Aggregated Rollups on Ingestion

While raw event logs are necessary for real-time fraud forensics and billing reconciliation, 95% of reporting dashboard queries request aggregated metrics (total revenue, eCPM, win rate, average latency) grouped by hour or day.

ClickHouse provides the AggregatingMergeTree engine. Using aggregate state combiners like sumState(), countState(), and uniqHLL12State() (HyperLogLog for unique device estimation), ClickHouse pre-aggregates incoming events in the background during partition merges. Dashboard queries hitting pre-aggregated materialized views return in under 5 milliseconds across multi-billion-event datasets.

6. High-Availability Cluster Topologies & Zero-Downtime Sharding

To eliminate single points of failure, enterprise ad exchanges deploy ClickHouse in distributed, sharded topologies managed via ClickHouse Keeper or Apache ZooKeeper. The cluster is structured into symmetric replica pairs across independent availability zones.

Distributed tables (ENGINE = Distributed) route analytical queries concurrently across all shards, merging results on the initiating coordinator node. Ingestion pipelines write directly to the underlying local ReplicatedMergeTree tables on each shard rather than routing writes through the distributed coordinator table. This architectural separation prevents coordinator network bottlenecks during 500,000 QPS surge events. If an individual node undergoes kernel upgrades or hardware maintenance, its replica seamlessly handles query traffic with zero client-side errors, guaranteeing 99.999% SLA uptime for mission-critical programmatic billing infrastructure.

7. Telemetry Retention Lifecycle & Cold Tier S3 Storage

Retaining Petabytes of raw ad telemetry on high-speed NVMe flash indefinitely introduces excessive infrastructure costs. ClickHouse tiered storage policies automate data lifecycles seamlessly across hot, warm, and cold volume definitions.

Hot data (0 to 7 days old) resides on local NVMe arrays for sub-second forensic analysis. Cold data (8 to 90 days old) is automatically migrated in the background to Amazon S3 or MinIO object storage without altering table schemas or SQL query interfaces. Query planners execute disk-aware partition pruning: if an analyst executes a 30-day historical trend query, ClickHouse fetches compressed columnar chunks directly from S3 using byte-range HTTP requests, caching hot indexes locally in RAM. After 90 days, automated partition drop queries purge expired raw logs, preserving long-term aggregates while keeping operational infrastructure costs firmly contained.