14.5 Databases & Columnar Storage Engine

Storage Engine Architecture

Distributed tracing telemetry has unique database access patterns:

1. Append-Only Ingestion: Spans are written once in batches; individual updates and in-place mutations never occur.
2. Column-Selective Aggregations: Queries almost always filter by service_name, time_range, and duration, ignoring 90% of other attributes.
3. Time-Bound TTL: Telemetry expires after 14-30 days.

Table Schema Definition

CREATE TABLE spans (
    tenant_id LowCardinality(String),
    service_name LowCardinality(String),
    span_name LowCardinality(String),
    trace_id String,
    span_id String,
    parent_span_id String,
    start_time DateTime64(6, 'UTC'),
    duration_us UInt64,
    status_code UInt8,
    error_message String,
    attributes Map(String, String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(start_time)
ORDER BY (tenant_id, service_name, status_code, start_time, trace_id, span_id)
TTL start_time + INTERVAL 30 DAY;

---

Comparison: ClickHouse vs Alternatives

Database	Fit for Tracing	Compression	Aggregation Speed	Why Rejected
ClickHouse	Optimal	6x - 10x	Sub-second over billions	Chosen storage engine
PostgreSQL	Poor	1.3x	Degrades $>10M$ rows	Row storage causes disk IO bottleneck on bulk inserts.
Elasticsearch	Moderate	1.5x	Fast search, high RAM	Inverted index overhead consumes 3x more disk/RAM.
MongoDB	Poor	1.2x	Slow aggregation	Unindexed JSON fields require full document scans.
Cassandra	Fair	2x	High write, weak query	Ad-hoc multi-dimensional trace queries are unsupported.