From RDS to ClickHouse: 100M messages/day at p99 under 10ms

The symptom was always the same: the telemetry dashboard took 30 seconds to answer, and the infrastructure team reported the database as healthy. It was. It was simply never designed for the question we were asking it.

The root cause is the access pattern, not the volume

Telemetry is pure analytical read: low-cardinality aggregations over time windows, almost never a lookup back to a single row. A transactional relational database answers that pattern in the most expensive way possible — B-trees, page splits, buffer-pool contention, and an execution plan that has to touch millions of rows to compute a sum.

Columnar stores invert every one of those decisions. Columns are independent, so an aggregation reads only the columns it needs. Time-based partition pruning becomes almost free. And column compression shrinks the scan by an order of magnitude.

The path, in the order that works

1. Do not migrate the database. Migrate the responsibility: move the telemetry stream to a dedicated write path and let the relational store keep doing what it is good at.
2. Use Databricks ETL to normalize, de-duplicate and pre-aggregate before the load. The fastest insert into ClickHouse is the one that never happened.
3. Choose the ORDER BY key from the most frequent query, not from the entity. For telemetry that is almost always (event_type, occurred_at, device_id).
4. materialize views for the queries that run every five minutes, and leave the rest as ad-hoc queries.

CREATE TABLE telemetry_events (
  occurred_at   DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
  device_id    UInt64          CODEC(ZSTD(1)),
  event_type   LowCardinality(String) CODEC(ZSTD(1)),
  payload      String          CODEC(ZSTD(3)),
  ingested_at  DateTime64(3, 'UTC') DEFAULT now()
) ENGINE = MergeTree
PARTITION BY toYYYYMM(occurred_at)
ORDER BY (event_type, occurred_at, device_id)
TTL toDateTime(occurred_at) + INTERVAL 25 MONTH;

Measured results

• Dashboard query time: 30s → 190ms.
• Load on the relational database: −72%, because it stopped being the analytical path.
• Ingest throughput: 100M messages/day with p99 ingest below 10ms.
• Storage: −88% versus the equivalent relational layout, from columnar compression and native TTL.

When not to migrate

If the query is by primary key, if the volume fits comfortably in memory, and if nobody needs to aggregate millions of rows, the relational store is still the right answer. Serious architecture includes knowing when not to use the tool you just mastered.