Unlock this content
Enter your email to unlock this content for free
Projections vs Matviews
Projections are similar to materialized views conceptually but have many limitations. Use projections when you want to reorder a table (filter by a column not in the sorting key) or for simple aggregations. Use materialized views for joins, complex transformations, multi-table operations, and when you need independent TTL or mutations. Projections are transparent to users but are a "second-class citizen" with limited feature support.
There's another feature of ClickHouse that are kind of similar to materialized views conceptually, but they have a lot of limitations. They are called projections and you usually use them when either you want to reorder a table because you want to filter by a column that it's not in the sorting key or for simple aggregations like the ones we just took a look a few minutes ago.
What are Projections?
Projections are a ClickHouse feature that allow you to reorder tables (create alternative sorting orders for filtering by columns not in the sorting key) or do simple aggregations. They work as per-part mutations: when a new part is created, the projection is applied and generates a new part in the same folder layout. At query time, if projected parts match the query, they are used automatically. Just query the regular table and ClickHouse handles the rest.