Alert Evaluations: Incremental Merges in ClickHouse

Highlight, an open-source app monitoring platform, has been utilizing ClickHouse, a columnar database, to manage large datasets and real-time analytics. Recently, they faced performance challenges with their alert system, particularly in processing large time windows for alert evaluations. The traditional method of recalculating alerts every minute for a one-hour window was inefficient, leading to excessive computational overhead. To address this, Highlight implemented incremental merges using ClickHouse's aggregate functions, allowing them to compute and store smaller partial results that could be merged later. This approach significantly improved performance, particularly for simple aggregate functions like Count and Sum. For more complex calculations, such as the median, ClickHouse's -State and -Merge functions were employed, enabling efficient memory usage and faster processing. By calculating intermediate states for new data every minute and merging them with existing states, Highlight achieved a tenfold speed increase in alert evaluations, reducing processing time from 1.24 seconds to 0.11 seconds and memory usage from 7.6 GB to 82 MB. This optimization has enhanced their alert evaluation process, demonstrating the effectiveness of ClickHouse's advanced features in handling real-time data analytics.

- Highlight uses ClickHouse for managing large datasets and real-time analytics.

- Incremental merges improved alert evaluation performance significantly.

- The approach reduced processing time from 1.24 seconds to 0.11 seconds.

- Memory usage decreased from 7.6 GB to 82 MB.

- ClickHouse's -State and -Merge functions are key to efficient data processing.