ClickHouse Observability: High-Performance, Low-Cost Solution for High-Cardinality Data
The State of SQL Observability - Pradeep Chhetri (ClickHouse)
This presentation was given at the ClickHouse meetup in Singapore on July 11, 2024.
The slides can be found at https://clickhou.se/the-state-of-sql-...
The State of SQL-Based Observability
Many successful paradigms in engineering and computer science are the result of two distinct approaches colliding with each other, leading to broader and more powerful applications. In this talk, we’ll look at the parallel backgrounds of two established paradigms: SQL and Observability.
We’ll be tracing back the history of both paradigms. How they managed to avoid each other despite SQL being the lingua franca of data manipulation, and how the industry standardization, fuelled by open-source innovation, has now propelled SQL back into the game as an observability language. We’ll also highlight case studies and benchmark results to provide the necessary elements for the attendee to answer a simple question: is SQL-based observability applicable to my use case? highlighting also the current limitations of this approach and leaving the conclusions for the attendees to draw.
Speaker: Pradeep Chhetri, Software Engineer @ ClickHouse
Pradeep works as a Site Reliability Engineer (SRE) in ClickHouse Cloud. With over a decade of experience, Pradeep has honed his DevOps and product engineering and currently focused on building scalable and reliable infrastructure platforms. Prior to ClickHouse, Pradeep was an SRE at Cloudflare for over 3 year, and served engineering roles at StashAway, NetraDyne, Barricade.io, Indix, and Flipkart. Originally from Nepal, he is currently based in Singapore. Pradeep holds a Bachelor’s degree in Technology, major in Computer Science and Engineering. In his free time, he enjoys watching and playing Chess.
ClickHouse provides a high-performance SQL database for building scalable, cost-effective observability platforms that handle high-cardinality data without pre-aggregation. It is engineered to solve core observability challenges by treating logs, metrics, and traces as a unified data problem, enabling real-time monitoring and querying on petabytes of data.\n\nClickHouse is an effective solution for handling high-cardinality observability data, supporting nearly infinite cardinality without the performance degradation common in other systems. This capability is critical for modern applications where unique identifiers like customer IDs or request traces create massive label sets. Major technology companies have adopted ClickHouse to manage these challenges; for instance, Uber migrated its logging platform from Elasticsearch to ClickHouse in 2021, achieving a 10x data compression ratio and significantly reducing operational costs. Similarly, ClickHouse's internal observability platform successfully compressed 19 petabytes of raw log data by approximately 17% by storing it in an object store, showcasing its storage efficiency for high-volume telemetry. The platform's column-oriented architecture is key to its performance, enabling faster aggregations by reading only the required columns from disk.\n\nFor technology integration, ClickHouse supports modern observability standards and platforms. It is widely used with OpenTelemetry, with companies like eBay deploying multi-tiered OTel collectors to send tracing data to a ClickHouse backend for Kubernetes monitoring. The platform also offers direct integration with Prometheus through a new Time Series Engine that natively supports Prometheus remote read and remote write protocols. This allows Prometheus to offload long-term storage to ClickHouse, which acts as a scalable backend while Prometheus handles querying. To ensure a seamless user experience during migrations from other systems, Uber developed a "Query Bridge" that translates Lucene queries into optimized SQL for ClickHouse.\n\nThe system's architecture is designed for scalability and resilience. As a distributed database, it supports horizontal scaling through sharding and replication where every node is equivalent, eliminating the single point of failure associated with primary-secondary models. ClickHouse also enables a federated query architecture to minimize data egress costs in multi-region cloud deployments by keeping data within its local availability zone while allowing queries to span across regions. For performance tuning, ClickHouse uses a sparse index, which creates one index entry per batch of 8192 rows, resulting in smaller, more efficient indexes compared to traditional databases. Uber's schema design for logs provides a real-world example of performance tuning, utilizing arrays for storing key-value pairs and materialized columns to create secondary indexes on frequently accessed field...