Why We Built LogPulse on ClickHouse

When we started building LogPulse, the first architectural decision we had to make was also the most consequential: where do the logs go? The answer to that question would dictate our cost structure, query performance, multi-tenancy model, and ultimately whether we could deliver a product that competes on both price and speed. After months of prototyping, benchmarking, and running production workloads against multiple backends, we chose ClickHouse. This post explains why.

The Problem with Elasticsearch

Elasticsearch is the default choice for log analytics, and for good reason. It pioneered full-text search over semi-structured data and built an ecosystem that millions of engineers depend on. But when you operate at scale -- ingesting tens of gigabytes per day across hundreds of tenants -- its limitations become painfully clear.

The JVM heap pressure alone is a constant source of operational toil. Inverted indices consume enormous amounts of memory. Shard management becomes a full-time job. And the storage footprint is staggering: a typical Elasticsearch deployment stores logs at roughly 1.2-1.5x the raw data size after indexing. For a multi-tenant SaaS product where storage costs flow directly to the margin line, that ratio is a dealbreaker.

We needed something that could handle the same query patterns -- keyword search, field filtering, time-range scans, aggregations -- but with fundamentally better economics.

Why ClickHouse

ClickHouse is a columnar database originally built at Yandex for web analytics. Its core strengths align almost perfectly with log analytics workloads: it excels at scanning large volumes of time-series data, compresses aggressively, and executes analytical queries with remarkable efficiency.

The compression ratios alone justified the switch. Where Elasticsearch inflates data, ClickHouse compresses it. We consistently see 10-50x compression ratios on real log data depending on the cardinality of the fields. A dataset that would consume 500GB in Elasticsearch fits in 10-50GB in ClickHouse. That is not a rounding error -- it is a fundamental shift in unit economics.

We use the @clickhouse/client library (v0.2.10) for all server-side interactions. The client supports streaming inserts, query cancellation, and session management, which are essential features for a multi-tenant platform where a single runaway query cannot be allowed to affect other users.

Schema Design for Multi-Tenancy

Our primary log table lives in the shared_tenants database and uses the MergeTree engine, which is the foundational table engine in ClickHouse. The table is called shared_tenants.logs and its design reflects two competing priorities: query performance for individual tenants and storage efficiency across all tenants.

The partition key is (tenant_id, toYYYYMMDD(timestamp)). This means every tenant-day combination produces a separate set of data parts on disk. When a user queries their logs, ClickHouse prunes all partitions belonging to other tenants before the query even begins executing. This is not a filter applied after a scan -- it is a structural guarantee that tenant data is physically isolated at the storage layer.

The ORDER BY clause is (tenant_id, namespace, timestamp, log_id). This ordering is deliberate. Most queries filter by tenant and namespace first, then scan a time range. By placing these columns at the front of the sort key, ClickHouse can skip entire granules of data that do not match the query predicates. The log_id at the end ensures deterministic ordering for deduplication.

-- Partition: physical data isolation per tenant per day
PARTITION BY (tenant_id, toYYYYMMDD(timestamp))

-- Order: optimized for the most common query pattern
ORDER BY (tenant_id, namespace, timestamp, log_id)

The Column Set

The logs table carries a wide set of columns designed to cover Kubernetes-native logging, structured events, and free-form text. The core columns include tenant_id, timestamp (DateTime64(3) for millisecond precision), log_id, event, level, index, host, source, sourcetype, cluster, namespace, pod, container, and node.

For extensibility, we use three Map(String, String) columns: attributes, parsed_fields, and labels. The Map type in ClickHouse lets us store arbitrary key-value pairs without schema migrations, which is critical when every customer has different log formats. We also store Kubernetes annotations in a dedicated annotations Map column.

On top of these, we define materialized columns -- level_extracted, status_code, and duration_ms -- that are computed at insert time from the raw event data. These columns exist purely for query performance. Instead of parsing a JSON body at query time to extract an HTTP status code, ClickHouse materializes the value once during ingestion and stores it as a native column. Queries that filter on status_code or aggregate by duration_ms skip the parsing step entirely.

Full-Text Search with Token Bloom Filters

One of the most common objections to ClickHouse for log analytics is the lack of inverted indices for full-text search. Elasticsearch has had this for over a decade. ClickHouse takes a different approach: token bloom filters.

A token bloom filter is a probabilistic index that can quickly determine whether a data granule does NOT contain a given token. It cannot confirm that a token IS present (false positives are possible), but it can definitively rule out granules that lack the token. For log search, this is exactly the right tradeoff. Most search queries match a tiny fraction of the data. The bloom filter eliminates 95-99% of granules from the scan, and ClickHouse only reads the remaining candidates.

The result is full-text search performance that approaches Elasticsearch for typical queries, with a fraction of the storage and memory overhead.

Real-Time Metrics with SummingMergeTree

Beyond raw log search, LogPulse provides real-time dashboards with metrics like log volume, error rates, and latency distributions. Running these aggregations over the raw logs table on every dashboard load would be prohibitively expensive.

Instead, we maintain a separate SummingMergeTree table that pre-aggregates metrics into 5-minute buckets. As logs are ingested, we simultaneously write summary rows that increment counters for each tenant, namespace, and log level. ClickHouse automatically merges these rows in the background, collapsing partial sums into final aggregates. Dashboard queries hit this table instead of the raw logs, returning results in single-digit milliseconds.

Query Safety

In a multi-tenant system, you cannot trust user-generated queries. A poorly written aggregation over an unindexed field could consume the entire cluster. We enforce several hard limits: a 30-second execution timeout, a 5GB memory cap per query, and a mandatory tenant_id filter injected by the query compiler. All user-facing queries use parameterized inputs to prevent SQL injection. We also enforce namespace-level RBAC, so users can only query namespaces their role permits.

The default TTL is set to 30 days. Data older than the retention window is automatically dropped by ClickHouse at the partition level, which means no expensive DELETE operations and no tombstone management.

Results

After running ClickHouse in production for several months, the numbers speak for themselves. P95 search latency is under 200ms for typical queries across datasets with billions of rows. Storage costs are a fraction of what they would be with Elasticsearch. And the operational surface area is dramatically smaller -- no JVM tuning, no shard rebalancing, no segment merging.

ClickHouse is not without its tradeoffs. It is less mature for full-text search than Elasticsearch, its ecosystem is smaller, and some query patterns (particularly high-cardinality GROUP BY with low selectivity) require careful schema design. But for log analytics at scale, the combination of columnar compression, partition pruning, materialized columns, and bloom filter indices makes it the right foundation for LogPulse.