Materialized Views versus Projections

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/data-modeling/projections/2_materialized-views-versus-projections.md)

> A common question from users is when they should use materialized views versus
> projections. In this article we will explore the key differences between the two and why you
> may want to pick one over the other in certain scenarios.

## Summary of key differences[​](#key-differences "Direct link to Summary of key differences")

The table below summarizes the key differences between materialized views and projections for various aspects of consideration.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Aspect Materialized views Projections|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Data storage and location Store their results in a **separate, explicit target table**, acting as insert triggers, on insert to a source table. Projections create optimized data layouts that are physically **stored alongside the main table data** and are invisible to the user.| Update mechanism Operate **synchronously** on `INSERT` to the source table (for incremental materialized views). Note: they can also be **scheduled** using refreshable materialized views. **Asynchronous** updates in the background upon `INSERT` to the main table.| Query interaction Working with Materialized Views requires querying the **target table directly**, meaning that you need to be aware of the existence of materialized views when writing queries. Projections are **automatically selected** by ClickHouse's query optimizer, and are transparent in the sense that the user doesn't have to modify their queries to the table with the projection in order to utilise it. From version 25.6 it is also possible to filter by more than one projection.| Handling `UPDATE` / `DELETE` **Don't automatically react** to `UPDATE` or `DELETE` operations on the source table as materialized views have no knowledge of the source table, acting only as insert triggers *to* a source table. This can lead to potential data staleness between source and target tables and requires workarounds or periodic full refresh. (via refreshable materialized view). By default, are **incompatible with `DELETED` rows** (especially lightweight deletes). `lightweight_mutation_projection_mode` (v24.7+) can enable compatibility.| `JOIN` support Yes. Refreshable materialized views can be used for complex denormalization. Incremental materialized views only trigger on left-most table inserts. No. `JOIN` operations aren't supported within projection definitions for filtering the materialized data. However, queries that join tables with projections work normally—projections optimize individual table access.| `WHERE` clause in definition Yes. `WHERE` clauses can be included to filter data before materialization. No. `WHERE` clauses aren't supported within projection definitions for filtering the materialized data.| Chaining capabilities Yes, the target table of one materialized view can be the source for another materialized view, enabling multi-stage pipelines. No. Projections can't be chained.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Applicable table engines Can be used with various source table engines, but target tables are usually of the `MergeTree` family. **Only available** for `MergeTree` family table engines.| Failure handling Failure during data insertion means that data is lost in the target table, leading to potential inconsistency. Failures are handled **silently** in the background. Queries can seamlessly mix materialized and unmaterialized parts.| Operational overhead Requires explicit target table creation and often manual backfilling. Managing consistency with `UPDATE`/`DELETE` increases complexity. Projections are automatically maintained and kept-in-sync and generally have a lower operational burden.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `FINAL` query compatibility Generally compatible, but often require `GROUP BY` on the target table. **Don't work** with `FINAL` queries.| Lazy materialization Yes. Monitor for projection compatibility issues when using materialization features. You may need to set `query_plan_optimize_lazy_materialization = false`| Parallel replicas Yes. No.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [`optimize_read_in_order`](/docs/operations/settings/settings#optimize_read_in_order) Yes. Yes.|  |  |  | | --- | --- | --- | | Lightweight updates and deletes Yes. No. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## Comparing materialized views and projections[​](#choose-between "Direct link to Comparing materialized views and projections")

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

You should consider using materialized views when:

- Working with **real-time ETL & multi-stage data pipelines:** You need to perform complex transformations, aggregations, or to route data as it arrives, potentially across multiple stages by chaining views.
- You require **complex denormalization**: You need to pre-join data from several sources (tables, subqueries or dictionaries) into a single, query-optimized table, especially if periodic full refreshes with the use of refreshable materialized views are acceptable.
- You want **explicit schema control**: You require a separate, distinct target table with its own schema and engine for the pre-computed results, offering greater flexibility for data modelling.
- You want to **filter at ingestion**: You need to filter data *before* it's materialized, reducing the volume of data written to the target table.

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

You should consider avoiding use of materialized views when:

- **Source data is frequently updated or deleted**: Without additional strategies for handling consistency between the source and target tables, incremental materialized views could become stale and inconsistent.
- **Simplicity and automatic optimization are preferred**: If you want to avoid managing separate target tables.

### 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: