Unlock this content
Enter your email to unlock this content for free
Tinybird is not affiliated with, associated with, or sponsored by ClickHouse, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc.
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.