Projections

### MATERIALIZE PROJECTION[​](#materialize-projection "Direct link to MATERIALIZE PROJECTION")

Use the statement below to rebuild the projection `name` in partition `partition_name`.
This is implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

```
ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
```

### CLEAR PROJECTION[​](#clear-projection "Direct link to CLEAR PROJECTION")

Use the statement below to delete projection files from disk without removing description.
This is implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

```
ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
```

The commands `ADD`, `DROP` and `CLEAR` are lightweight in the sense that they only change metadata or remove files.
Additionally, they are replicated, and sync projection metadata via ClickHouse Keeper or ZooKeeper.

Note

Projection manipulation is supported only for tables with [`*MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) engine (including [replicated](/docs/engines/table-engines/mergetree-family/replication) variants).

### Controlling projection merge behavior[​](#control-projections-merges "Direct link to Controlling projection merge behavior")

When you execute a query, ClickHouse chooses between reading from the original table or one of its projections.
The decision to read from the original table or one of its projections is made individually per every table part.
ClickHouse generally aims to read as little data as possible and employs a couple of tricks to identify the best part to read from, for example, sampling the primary key of a part.
In some cases, source table parts have no corresponding projection parts.
This can happen, for example, because creating a projection for a table in SQL is “lazy” by default - it only affects newly inserted data but keeps existing parts unaltered.

As one of the projections already contains the pre-computed aggregate values, ClickHouse tries to read from the corresponding projection parts to avoid aggregating at query runtime again. If a specific part lacks the corresponding projection part, query execution falls back to the original part.

But what happens if the rows in the original table change in a non-trivial way by non-trivial data part background merges?
For example, assume the table is stored using the `ReplacingMergeTree` table engine.
If the same row is detected in multiple input parts during merge, only the most recent row version (from the most recently inserted part) will be kept, while all older versions will be discarded.

Similarly, if the table is stored using the `AggregatingMergeTree` table engine, the merge operation may fold the same rows in the input parts (based on the primary key values) into a single row to update partial aggregation states.

Before ClickHouse v24.8, projection parts either silently got out of sync with the main data, or certain operations like updates and deletes could not be run at all as the database automatically threw an exception if the table had projections.

Since v24.8, a new table-level setting [`deduplicate_merge_projection_mode`](/docs/operations/settings/merge-tree-settings#deduplicate_merge_projection_mode) controls the behavior if the aforementioned non-trivial background merge operations occur in parts of the original table.

Delete mutations are another example of part merge operations that drop rows in the parts of the original table. Since v24.7, we also have a setting to control the behavior w.r.t. delete mutations triggered by lightweight deletes: [`lightweight_mutation_projection_mode`](/docs/operations/settings/merge-tree-settings#deduplicate_merge_projection_mode).

Below are the possible values for both `deduplicate_merge_projection_mode` and `lightweight_mutation_projection_mode`:

- `throw` (default): An exception is thrown, preventing projection parts from going out of sync.
- `drop`: Affected projection table parts are dropped. Queries will fall back to the original table part for affected projection parts.
- `rebuild`: The affected projection part is rebuilt to stay consistent with data in the original table part.

## Limitations[​](#limitations "Direct link to Limitations")

It is not possible to use an `ALIAS` column in a projection's `ORDER BY` clause. For example:

```
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 ALIAS a + 1,
--highlight-next-line
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;
-- Fails with UNKNOWN_IDENTIFIER
```

`ALIAS` columns are not physically stored and are computed on-the-fly at query time, so they are unavailable during the projection part write path when the sorting expression is evaluated.

Instead, use `MATERIALIZED` columns or inline the expression directly:

```
-- using MATERIALIZED column
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 MATERIALIZED a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;

-- using an inline expression
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;
```

## See also[​](#see-also "Direct link to See also")

- ["Control Of Projections During Merges" (blog post)](https://clickhouse.com/blog/clickhouse-release-24-08#control-of-projections-during-merges)
- ["Projections" (guide)](/docs/data-modeling/projections#using-projections-to-speed-up-UK-price-paid)
- ["Materialized Views versus Projections"](https://clickhouse.com/docs/managing-data/materialized-views-versus-projections)

- [Overview of projections](#overview)- [Using projections](#examples)
    - [Example filtering without using primary keys](#example-filtering-without-using-primary-keys)- [Example pre-aggregation query](#example-pre-aggregation-query)- [Creating and using projection indexes](#projection-indexes)- [Manipulating projections](#manipulating-projections)
      - [ADD PROJECTION](#add-projection)- [DROP PROJECTION](#drop-projection)- [MATERIALIZE PROJECTION](#materialize-projection)- [CLEAR PROJECTION](#clear-projection)- [Controlling projection merge behavior](#control-projections-merges)- [Limitations](#limitations)- [See also](#see-also)

Was this page helpful?