Performance impact of materialized views in ClickHouse®

The performance impact of materialized views (MVs) in ClickHouse is investigated by Stefan Kaeser, Senior Solutions Architect at DoubleCloud. MVs are insert triggers that move data from a source table to a target table when new data is inserted into the source table. They can be used for aggregation and transformation of data. The impact of MVs on write performance is tested using Null and MergeTree engines, with batch sizes of 100 rows and one million rows. Results show that adding a single MV to a table reduces insert queries by approximately half but does not affect the total amount of rows written per second. Chaining multiple views slows down insert performance nearly linearly, while having multiple target tables at once has similar results. The use of filters in materialized views can reduce the impact on insert speed for MergeTree tables when dealing with large amounts of data. Overall, MVs are a powerful tool for implementing pipelines in ClickHouse, but their impact on performance should be considered when designing systems with many views.