How Purl Stores and Searches Logs in ClickHouse

Why ClickHouse for Logs?

When we built Purl, we evaluated every major database for log storage: Elasticsearch, PostgreSQL, TimescaleDB, and ClickHouse. ClickHouse won decisively for three reasons:

1. 1Columnar storage: Log queries typically touch 2-3 columns (timestamp, level, message). ClickHouse only reads the columns you query, skipping the rest.
2. 2Strong compression: message, raw and meta are stored with ZSTD codecs. On our own test corpus that came out at 5.1x — see the Benchmarks section below for the exact figures and the caveat that comes with them.
3. 3Vectorized query execution: Queries process data in batches of 8192 rows using SIMD instructions, achieving throughput that's orders of magnitude faster than row-based databases.

Schema Design

This is the table Purl creates on first boot — copied from the server's own DDL, not a simplified illustration:

CREATE TABLE IF NOT EXISTS logs (
    id UUID DEFAULT generateUUIDv4(),
    timestamp DateTime64(3),
    level LowCardinality(String),
    service LowCardinality(String),
    host LowCardinality(String),
    message String CODEC(ZSTD(3)),
    raw String CODEC(ZSTD(3)),
    meta String CODEC(ZSTD(3)),
    trace_id String DEFAULT '' CODEC(ZSTD(3)),
    request_id String DEFAULT '' CODEC(ZSTD(3)),
    span_id String DEFAULT '' CODEC(ZSTD(3)),
    parent_span_id String DEFAULT '' CODEC(ZSTD(3)),
    INDEX idx_level level TYPE set(100) GRANULARITY 4,
    INDEX idx_service service TYPE set(1000) GRANULARITY 4,
    INDEX idx_message message TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 4,
    INDEX idx_trace_id trace_id TYPE bloom_filter(0.01) GRANULARITY 4,
    INDEX idx_request_id request_id TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (service, level, timestamp)
TTL toDateTime(timestamp) + INTERVAL 30 DAY
SETTINGS index_granularity = 8192

The TTL interval is not hardcoded — it comes from your PURL_RETENTION_DAYS setting and is baked into the table at creation time. And when Purl is configured against a ClickHouse cluster, every engine in the schema is swapped for its replicated counterpart automatically: this table becomes ReplicatedMergeTree, the pattern table becomes ReplicatedReplacingMergeTree, and the stats views become ReplicatedSummingMergeTree. Single-node and cluster deployments run the same schema code.

Key design decisions:

• LowCardinality for level, service, host — these have few unique values, so dictionary encoding saves a large fraction of their space
• ZSTD(3) codecs on message, raw and meta — the three columns that hold nearly all the bytes, and the reason the compression ratio in the Benchmarks section looks the way it does
• Partitioning by day — enables instant partition drops for retention cleanup instead of expensive DELETE queries
• ORDER BY (service, level, timestamp) — the sort key matches the filters people actually use, so the primary index does most of the pruning
• Skip indexes — set indexes on level and service, a tokenbf_v1 token bloom filter on message, and bloom filters on trace_id and request_id, so a needle-in-haystack trace lookup does not read every granule
• TTL — automatic data expiration, no cron jobs needed
• Separate message and raw — message is the parsed line for search, raw keeps the original payload so nothing is lost on ingest

Query Optimization

Materialized Views for Patterns

Pattern detection is pre-aggregated at write time. Purl creates a materialized view named logs_patterns_mv which writes into a separate log_patterns table rather than keeping its own state:

CREATE TABLE IF NOT EXISTS log_patterns (
    pattern_hash UInt64,
    pattern String,
    sample_message String,
    service LowCardinality(String),
    level LowCardinality(String),
    first_seen DateTime64(3),
    last_seen DateTime64(3),
    occurrence_count UInt64
)
ENGINE = ReplacingMergeTree(last_seen)
ORDER BY (pattern_hash, service, level)
TTL toDateTime(first_seen) + INTERVAL 30 DAY

The view itself normalises each incoming message before hashing it — UUIDs, IP addresses, timestamps, bare numbers and long hex strings are each replaced with a placeholder, so user 4821 logged in and user 9137 logged in collapse to the same pattern:

CREATE MATERIALIZED VIEW IF NOT EXISTS logs_patterns_mv TO log_patterns AS
SELECT
    -- normalise(message) stands in for a nested chain of replaceRegexpAll
    -- calls substituting <UUID>, <IP>, <DATETIME>, <NUM> and <HEX>
    cityHash64(normalise(message)) AS pattern_hash,
    normalise(message)             AS pattern,
    any(message)                   AS sample_message,
    service,
    level,
    min(timestamp) AS first_seen,
    max(timestamp) AS last_seen,
    count()        AS occurrence_count
FROM logs
GROUP BY pattern_hash, pattern, service, level

The real view spells that normalisation out as a chain of nested replaceRegexpAll calls; it is shown collapsed here for readability. ReplacingMergeTree(last_seen) keeps one row per pattern, preferring the most recently seen version. The "Patterns" tab in the dashboard reads log_patterns, never the raw log table.

Two more materialized views, logs_level_stats and logs_service_stats, roll counts up by day into SummingMergeTree tables. They are what makes the dashboard's level and service breakdowns cheap regardless of how much data sits behind them.

What we don't use: projections

An earlier version of this post claimed the dashboard's time-series charts were served by a logs_by_time projection. They are not, and never were — no projection exists on the logs table. The volume chart runs an ordinary GROUP BY toStartOfMinute(timestamp) (or hour, or day, depending on the selected range) with WITH FILL to pad empty buckets, straight against the logs table.

It is fast enough without one because the daily partition key plus the sort key already cut the scan down to the selected window. A projection is a reasonable future optimisation if that stops holding at larger volumes, but we would rather document the query we actually run than the one that sounds more sophisticated.

Benchmarks

Every number below was measured on the stack this project ships — the unmodified docker-compose.yml, whose defaults cap the Purl container at 1 CPU / 1GB RAM and ClickHouse at 2 CPU / 4GB. Host was an Apple M4 Pro (12 cores, 24GB) running Docker Desktop, ClickHouse 25.11. The test corpus was 10,111,001 log lines spread across a 24-hour window. These are deliberately small-box numbers: a real server has more cores to give, and we would rather publish what we actually ran than what we hope you would get.

Ingest

Measured through POST /api/logs — the same HTTP endpoint Vector and the Purl agent use — at 100,000 NDJSON lines per run, three runs per configuration. The range is the spread across those three runs:

• 1,000 lines per batch, 2 concurrent senders: 28,200–28,900 lines/second
• 1,000 lines per batch, 1 sender: 21,700–22,200 lines/second
• 100 lines per batch, 4 concurrent senders: 18,000–18,600 lines/second
• 10,000 lines per batch, 4 concurrent senders: 16,000–20,800 lines/second

Batches of roughly 1,000 lines with two senders is the sweet spot. Counter-intuitively, pushing the maximum 10,000-line batch is slower: the per-line validation pass inside the single-core Perl process becomes the bottleneck long before ClickHouse does. If you are tuning a shipper, tune the batch size before you add senders.

Search

Measured end-to-end through GET /api/logs, which issues both a result query and a count query per request. Thirty requests per repeat, three repeats, with the response cache defeated on every single request so nothing is served from memory. Ranges show the spread across the three repeats:

• 24h window, no filter (9.05M matches): p50 113–115ms, p95 115–122ms
• 1h window, no filter (390k matches): p50 117–118ms, p95 119ms
• level:ERROR over 24h (841k matches): p50 114–115ms, p95 116–117ms
• KQL level:ERROR AND service:billing (105k matches): p50 100–102ms, p95 106–107ms
• KQL wildcard service:api-* (1.13M matches): p50 104–105ms, p95 109–110ms
• Substring search, rare term (826 matches): p50 223–230ms, p95 249–282ms
• Substring search, common term (1.73M matches): p50 455–474ms, p95 500–536ms

The shape of that list matters more than any single figure. Filters that hit an index — time, level, service — land flat at roughly 110ms whether they match 390,000 rows or 9 million, because the partition key and the skip indexes keep the scan small. Unindexed substring search across the message column costs two to four times more, and it scales with how many rows match, not how many exist. Searching for something common is the expensive case, which is the opposite of most people's intuition.

Of each of those numbers, 60–90ms is ClickHouse and the rest is the Purl process serialising JSON on its one allotted core. Give the container more CPU and that flat 110ms floor comes down with it.

Storage

The 10.1M-line corpus occupied 289 MiB on disk against 1.44 GiB uncompressed — 5.1x compression, roughly 30 bytes per line. Generated logs repeat themselves more than production traffic does, so treat that ratio as a ceiling rather than a promise.

What we did not measure

We have not run Elasticsearch, Loki or any other engine on this hardware, so this post has no comparison table. A benchmark of someone else's system that we never actually ran would be marketing, not data — and an untuned competitor is trivially easy to beat on a slide. Run your own workload against your own logs instead; the whole stack is one docker compose up away.

Operational Simplicity

The biggest win isn't performance — it's operations. ClickHouse requires almost zero maintenance:

• No shard management
• No rebalancing
• No JVM tuning
• Automatic TTL-based cleanup
• Single binary deployment

This is why Purl can offer production-grade log management starting at just docker compose up.