Hey folks, My team and I have been working on a <a href="https://signoz.io/blog/query-performance-improvement/" target="_blank" rel="noopener nofollow">performance optimization</a> and wanted to share the results. We managed to cut log-query scanning from nearly all data blocks down to less than 1% by reorganizing how logs are stored in ClickHouse.
Instead of relying on bloom-filter skip indexes, we generate a deterministic “resource fingerprint” (a hash of cluster + namespace + pod, etc.) for every log source and now sort the table by this fingerprint in the ORDER BY clause of the primary key. This packs logs from the same pod/service contiguously, letting ClickHouse’s sparse primary-key index skip over irrelevant data blocks entirely.
The result: a filter on a single namespace now reads just 222 out of 26,135 blocks (0.85%), slashing I/O and latency.
Next up, we’re tackling GROUP BY performance. We’re currently working on using ClickHouse’s new native JSON column type, which should let us eliminate an expensive data materialization step and improve performance drastically.
This approach worked well for us, but I’m want to hear from others. Is sorting on a high-cardinality fingerprint like this a common pattern, or is there a more efficient way to achieve this data locality that we might have missed?

By data engineers, for data engineers
python, sql, docker and much more

The Data Engineer

A development team optimized ClickHouse log query performance by replacing bloom filter skip indexes with a deterministic resource fingerprint approach. They sort their primary key on a hash of cluster, namespace, and pod information, which groups logs from the same source together. This change reduced block scanning from nearly 100% to just 0.85% (222 out of 26,135 blocks) for single namespace queries, significantly improving I/O and latency. The team is now exploring ClickHouse's native JSON column type to further optimize GROUP BY operations.

We stopped relying on bloom filters and now sort our ClickHouse primary key on a resource fingerprint. It cut our log query scans to 0.85% of blocks