The Challenge
When we started building Claymore, we knew the data requirements would be extreme. Session replay analytics generates massive amounts of data: every mouse movement, every scroll, every click, every network request, every console log — all timestamped and linked to user sessions.
We needed a database that could handle:
Most importantly, we needed real-time queries on raw data. Our AI Analyst feature generates SQL queries on the fly based on natural language questions. Pre-aggregation wasn't an option — users ask questions we can't predict.
What We Evaluated
We spent three months evaluating database options. Here's what we learned:
PostgreSQL + TimescaleDB
Our first attempt. TOAST compression helped with large text fields, but analytical queries on 100M+ rows took 10-30 seconds. Partitioning helped, but operational complexity grew exponentially. Write amplification during VACUUM became a serious issue at scale.
Elasticsearch
Great for text search, painful for everything else. Aggregations were slow, cluster management was a full-time job, and licensing costs at our projected scale were prohibitive. The JVM heap tuning alone consumed weeks.
Snowflake
Excellent for batch analytics, but not designed for sub-second interactive queries. Cold warehouse startup times of 5-15 seconds made it unsuitable for our real-time AI query feature. Cost scaling was also unpredictable.
ClickHouse
The moment we ran our first benchmark, we knew this was different. Queries that took 15 seconds in Postgres completed in 47ms. The same data that consumed 500GB in Postgres compressed to 50GB.
Why ClickHouse Won
| Metric | PostgreSQL | Elasticsearch | ClickHouse |
|---|---|---|---|
| 100M row aggregation | 15-30s | 5-10s | 47ms |
| 1TB storage (same data) | 1TB | 800GB | 100GB |
| Monthly cost (projected) | $8,000 | $15,000 | $1,200 |
| Operational complexity | Medium | High | Low (Cloud) |
The Compression Advantage
Session replay data is highly compressible. User actions repeat patterns, URLs contain common prefixes, timestamps are sequential. ClickHouse's ZSTD codec achieves 10:1 compression on our event data. This isn't just about storage costs — smaller data means faster scans and better cache utilization.
Our ClickHouse-Native Architecture
We didn't just pick ClickHouse — we designed for it. Our architecture leverages ClickHouse-specific features that would be impossible or inefficient on other databases:
-- Example: Our sessions table uses ClickHouse-native features
CREATE TABLE sessions (
sessionId UUID,
projectId LowCardinality(String),
country LowCardinality(String),
browser LowCardinality(String),
cartValue Decimal(10, 2),
createdAt DateTime64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(createdAt)
ORDER BY (projectId, createdAt, sessionId)
TTL createdAt + INTERVAL 12 MONTH TO VOLUME 'cold'
SETTINGS index_granularity = 8192Key architectural decisions that make us ClickHouse-native:
LowCardinality for dimensions: Country, browser, OS, and other categorical fields use dictionary encoding, reducing storage and accelerating GROUP BY.
Materialized Views for real-time aggregations: Dashboard metrics are computed on insert, not on query. Zero latency for common views.
TTL with tiered storage: Hot data on NVMe, older data automatically migrates to S3. Queries remain fast because the engine knows where data lives.
Proper primary key ordering: (projectId, createdAt, sessionId) ensures that tenant queries scan minimal data. This ordering is critical — ClickHouse reads data in primary key order, so placing projectId first means cross-tenant queries never touch your data.
Why AI Requires ClickHouse
Our "Ask, Don't Watch" feature lets users query session data in natural language. The AI generates SQL queries and executes them against ClickHouse in real-time.
This only works because ClickHouse can execute any ad-hoc query on raw data in milliseconds. With pre-aggregated systems, the AI would be limited to predefined dimensions. With slow databases, users would wait 30 seconds for each answer.
Text-to-SQL in Production
User: "Show me sessions from Berlin with checkout errors over $100"
Generated SQL executes in 47ms. The AI can ask follow-up questions, drill down into specific sessions, and link results to video replays. This interactive experience is only possible with sub-second query performance.
We Are Not Database-Agnostic
Some analytics tools advertise "database flexibility" — the ability to run on Postgres, MySQL, or ClickHouse. We made the opposite choice.
Claymore is ClickHouse-native by design. We use features that don't exist in other databases: column codecs, materialized view semantics, merge tree engine behaviors, and ClickHouse-specific SQL functions.
This isn't lock-in — it's optimization. We chose the best tool for the job and built our entire stack around its strengths. The result: 10x better performance and 80% lower costs than database-agnostic alternatives.