The best feature ClickHouse offers us is its performance on large datasets. It is consistently fast for analytical queries involving billions of rows. Time range aggregations, group by operations, and joins are simplified, making our work more efficient. We utilize the MergeTree engine for partitioning and indexing, and I appreciate the simplicity of automatic indexing that occurs whenever we create a table by providing the order by clause. We have different engines such as MergeTree, ReplacingMergeTree, and aggregated pre-aggregated daily for time series. These options are easy and flexible, even for our junior engineers. The Materialized Views and rollups are particularly impressive, as we continuously build pre-aggregated tables at daily and hourly levels. The documentation provided by ClickHouse is also quite user-friendly and easy to navigate. Additionally, the columnar architecture allows for strong compression, enabling us to store a significant amount of history without excessive storage costs. ClickHouse has positively impacted our organization. The low cardinality feature stands out as incredibly useful because it provides a smaller storage footprint, faster joins, and quicker group by queries. The TTLs support lifecycle management, allowing us to drop older raw data as discussed. We drive backups from our main primary table after 14 days into a cheaper disk. Projections, similar to lightweight materialized indexes, are also helpful for specific query patterns. Our vectorized execution improves the efficiency of extracting complex aggregated data over billions of rows within a short timeframe. ClickHouse supports JSON and semi-structured data excellently, making it easier to handle complex IoT telemetry data. The documentation, alongside its integrations with Python, Pandas, or AWS SageMaker, facilitates model training. We collect IoT telemetry data from three lakh plus devices, encompassing over 30,000 sites and about one and a half to two lakh dispensers. Our daily data load is initially 100 GB, which compresses down to approximately 10 GB in ClickHouse. We retain data for 14 days, resulting in a very quick latency of less than one second on our UI for machine learning API inferences, thanks to the efficient partitioning provided by ClickHouse. We also provide daily and hourly summary tables for our site teams, aiding our aggregations. The cost reductions due to compression benefit us significantly. We use the TTL function and carry out our data backup efficiently. Integrating with MLflow has simplified data retrieval for model training as ClickHouse provides the data quickly without encountering I/O failures in our pipelines.
