Materialized Views versus Projections

### When to choose projections[​](#choosing-projections "Direct link to When to choose projections")

You should consider using projections when:

- **Optimizing queries for a single table**: Your primary goal is to speed up queries on a single base table by providing alternative sorting orders, optimizing filters on columns which aren't part of the primary-key, or pre-computing aggregations for a single table.
- You want **query transparency**: you want queries to target the original table without modification, relying on ClickHouse to pick the best data layout for a given query.

### When to avoid projections[​](#avoid-projections "Direct link to When to avoid projections")

You should consider avoiding the use of projections when:

- **Complex data transformation or multi-stage ETL are required**: Projection definitions don't support `JOIN` operations, can't be chained to build multi-step pipelines, and can't handle some SQL features like window functions or complex `CASE` statements. While queries on tables with projections can join freely, the projections themselves aren't suited for complex data transformation.
- **Explicit filtering of materialized data is needed**: Projections don't support `WHERE` clauses in their definition to filter the data that gets materialized into the projection itself.
- **Non-MergeTree table engines are used**: Projections are exclusively available for tables using the `MergeTree` family of engines.
- `FINAL` queries are essential: Projections don't work with `FINAL` queries, which are sometimes used for deduplication.
- You need [parallel replicas](/docs/deployment-guides/parallel-replicas) as they're not supported with projections.

## Summary[​](#summary "Direct link to Summary")

Materialized views and projections are both powerful tools in your toolkit for
optimizing queries and transforming data, and in general, we recommend not to view
using them as an either/or choice. Instead, they can be used in a complementary
manner to get the most out of your queries. As such, the choice between materialized
views and projections in ClickHouse really depends on your specific use case and
access patterns.

As a general rule of thumb, you should consider using materialized views when
you need to aggregate data from one or more source tables into a target table or
perform complex transformations at scale. Materialized views are excellent for shifting
the work of expensive aggregations from query time to insert time. They're a
great choice for daily or monthly rollups, real-time dashboards or data summaries.

On the other hand, you should use projections when you need to optimize queries
which filter on different columns than those which are used in the table's primary
key which determines the physical ordering of the data on disk. They're particularly
useful when it's no longer possible to change the primary key of a table, or when
your access patterns are more diverse than what the primary key can accommodate.

- [Summary of key differences](#key-differences)- [Comparing materialized views and projections](#choose-between)
    - [When to choose materialized views](#choosing-materialized-views)- [When to avoid materialized views](#avoid-materialized-views)- [When to choose projections](#choosing-projections)- [When to avoid projections](#avoid-projections)- [Summary](#summary)

Was this page helpful?