How do materialized views work in ClickHouse, and how do they speed up real-time analytics?

Think of a news ticker at a stock exchange. Rather than recalculating the market index from all individual stock prices every time someone asks (refresh-based materialized view like PostgreSQL), ClickHouse updates the index incrementally as each trade happens (trigger-based materialized view). The ticker stays current without ever needing to re-read all historical trades.

ClickHouse materialized views are INSERT triggers that execute a SELECT query against each incoming batch of data and write the transformed results to a destination table. When you INSERT 1000 rows into the source table, the materialized view's SELECT query runs against those 1000 rows (not the entire table) and the output is inserted into the target table. This is fundamentally different from PostgreSQL where REFRESH MATERIALIZED VIEW re-executes the full query against all data.

Internally, the materialized view is defined with three components: the source table (implicitly from the SELECT's FROM clause), the transformation query (the SELECT statement with optional GROUP BY for pre-aggregation), and the target table (specified with TO clause or auto-created with ENGINE). For aggregation views, the target table typically uses AggregatingMergeTree engine which stores intermediate aggregation states (like partial counts, sums, and HyperLogLog sketches) that can be merged correctly during queries using the -Merge combinator functions (countMerge, sumMerge, uniqMerge).

At production scale, materialized views enable sub-second dashboard queries on data that arrives at millions of rows per second. A raw events table might have billions of rows, but a materialized view pre-aggregating events by hour and service reduces this to thousands of rows in the target table. Queries hit the small pre-aggregated table instead of scanning billions of rows. Teams commonly build multiple materialized views on the same source table for different dashboard panels, each optimized for specific query patterns.

The non-obvious gotcha is that materialized views only process new inserts, not historical data already in the source table. If you create a materialized view on a table with existing data, the target table starts empty. You must manually backfill with INSERT INTO target_table SELECT ... FROM source_table. Also, if the materialized view query fails (out of memory, schema mismatch), the original INSERT to the source table still succeeds but the view's target table misses that batch, causing data gaps that are difficult to detect without monitoring.