Using Materialized Views in ClickHouse (vs. Postgres)

Highlight.io, an open-source observability solution, adopted ClickHouse to manage and query large volumes of observability data, particularly for complex analytical tasks. ClickHouse, an OLAP database, is more suited for aggregate data analysis compared to PostgreSQL, which excels in OLTP tasks and single-record lookups. Materialized Views (MVs) in both databases help optimize query performance by storing transformed data. Highlight.io uses MVs in ClickHouse to efficiently handle various query patterns, such as aggregating traces and optimizing row lookups by different attributes, which is crucial for their tracing product's functionality. An example involves creating MVs to differentiate numeric and non-numeric trace attributes for quick searches, as well as optimizing trace searches by TraceId to support their UI needs, despite the additional resource consumption involved in maintaining these views.