ℹ️ Skipped - page is already crawled
| Filter | Status | Condition | Details |
|---|---|---|---|
| HTTP status | PASS | download_http_code = 200 | HTTP 200 |
| Age cutoff | PASS | download_stamp > now() - 6 MONTH | 0.2 months ago |
| History drop | PASS | isNull(history_drop_reason) | No drop reason |
| Spam/ban | PASS | fh_dont_index != 1 AND ml_spam_score = 0 | ml_spam_score=0 |
| Canonical | PASS | meta_canonical IS NULL OR = '' OR = src_unparsed | Not set |
| Property | Value |
|---|---|
| URL | https://clickhouse.com/docs/sql-reference/statements/alter/projection |
| Last Crawled | 2026-04-11 13:07:27 (5 days ago) |
| First Indexed | 2025-02-21 03:59:45 (1 year ago) |
| HTTP Status Code | 200 |
| Meta Title | Projections | ClickHouse Docs |
| Meta Description | Documentation for Manipulating Projections |
| Meta Canonical | null |
| Boilerpipe Text | This page discusses what projections are, how you can use them and various options for manipulating projections.
Overview of projections
Projections store data in a format that optimizes query execution, this feature is useful for:
Running queries on a column that is not a part of the primary key
Pre-aggregating columns, it will reduce both computation and IO
You can define one or more projections for a table, and during the query analysis the projection with the least data to scan will be selected by ClickHouse without modifying the query provided by the user.
Disk usage
Projections will create internally a new hidden table, this means that more IO and space on disk will be required.
For example, if the projection has defined a different primary key, all the data from the original table will be duplicated.
You can see more technical details about how projections work internally on this
page
.
Using projections
Example filtering without using primary keys
Creating the table:
CREATE
TABLE
visits_order
(
`
user_id
`
UInt64
,
`
user_name
`
String
,
`
pages_visited
`
Nullable
(
Float64
)
,
`
user_agent
`
String
)
ENGINE
=
MergeTree
(
)
PRIMARY
KEY
user_agent
Using
ALTER TABLE
, we could add the Projection to an existing table:
ALTER
TABLE
visits_order
ADD
PROJECTION user_name_projection
(
SELECT
*
ORDER
BY
user_name
)
ALTER
TABLE
visits_order MATERIALIZE PROJECTION user_name_projection
Inserting the data:
INSERT
INTO
visits_order
SELECT
number
,
'test'
,
1.5
*
(
number
/
2
)
,
'Android'
FROM
numbers
(
1
,
100
)
;
The Projection will allow us to filter by
user_name
fast even if in the original Table
user_name
was not defined as a
PRIMARY_KEY
.
At query time, ClickHouse determines that less data will be processed if the projection is used, as the data is ordered by
user_name
.
SELECT
*
FROM
visits_order
WHERE
user_name
=
'test'
LIMIT
2
To verify that a query is using the projection, we could review the
system.query_log
table. On the
projections
field we have the name of the projection used or empty if none has been used:
SELECT
query
,
projections
FROM
system
.
query_log
WHERE
query_id
=
'<query_id>'
Example pre-aggregation query
Create the table with projection
projection_visits_by_user
:
CREATE
TABLE
visits
(
`
user_id
`
UInt64
,
`
user_name
`
String
,
`
pages_visited
`
Nullable
(
Float64
)
,
`
user_agent
`
String
,
PROJECTION projection_visits_by_user
(
SELECT
user_agent
,
sum
(
pages_visited
)
GROUP
BY
user_id
,
user_agent
)
)
ENGINE
=
MergeTree
(
)
ORDER
BY
user_agent
Insert the data:
INSERT
INTO
visits
SELECT
number
,
'test'
,
1.5
*
(
number
/
2
)
,
'Android'
FROM
numbers
(
1
,
100
)
;
INSERT
INTO
visits
SELECT
number
,
'test'
,
1.
*
(
number
/
2
)
,
'IOS'
FROM
numbers
(
100
,
500
)
;
Execute a first query with
GROUP BY
using the field
user_agent
.
This query will not use the projection defined as the pre-aggregation does not match.
SELECT
user_agent
,
count
(
DISTINCT
user_id
)
FROM
visits
GROUP
BY
user_agent
To make use of the projection you can execute queries that select part of, or all of the pre-aggregation and
GROUP BY
fields:
SELECT
user_agent
FROM
visits
WHERE
user_id
>
50
AND
user_id
<
150
GROUP
BY
user_agent
SELECT
user_agent
,
sum
(
pages_visited
)
FROM
visits
GROUP
BY
user_agent
As previously mentioned, you can review the
system.query_log
table to understand if a projection was used.
The
projections
field shows the name of the projection used.
It will be empty if no projection has been used:
SELECT
query
,
projections
FROM
system
.
query_log
WHERE
query_id
=
'<query_id>'
Creating and using projection indexes
Creating a
projection index
:
CREATE
TABLE
events
(
`
event_time
`
DateTime
,
`
event_id
`
UInt64
,
`
user_id
`
UInt64
,
`
huge_string
`
String
,
PROJECTION order_by_user_id
INDEX
user_id
TYPE
basic
)
ENGINE
=
MergeTree
(
)
ORDER
BY
(
event_id
)
;
Creating a projection with explicit
_part_offset
field
Inserting some sample data:
INSERT
INTO
events
SELECT
*
FROM
generateRandom
(
)
LIMIT
100000
;
The
_part_offset
field preserves its value through merges and mutations, making it valuable for secondary indexing. We can leverage this in queries:
SELECT
count
(
)
FROM
events
WHERE
_part_starting_offset
+
_part_offset
IN
(
SELECT
_part_starting_offset
+
_part_offset
FROM
events
WHERE
user_id
=
42
)
SETTINGS enable_shared_storage_snapshot_in_query
=
1
Manipulating projections
The following operations with
projections
are available:
ADD PROJECTION
Use the statement below to add a projection description to a tables metadata:
ALTER
TABLE
[
db
.
]
name
[
ON
CLUSTER cluster
]
ADD
PROJECTION
[
IF
NOT
EXISTS
]
name
(
SELECT
<
COLUMN
LIST EXPR
>
[
GROUP
BY
]
[
ORDER
BY
]
)
[
WITH
SETTINGS
(
setting_name1
=
setting_value1
,
setting_name2
=
setting_value2
,
.
.
.
)
]
WITH SETTINGS
Clause
WITH SETTINGS
defines
projection-level settings
, which customize how the projection stores data (for example,
index_granularity
or
index_granularity_bytes
).
These correspond directly to
MergeTree table settings
, but apply
only to this projection
.
Example:
ALTER
TABLE
t
ADD
PROJECTION p
(
SELECT
x
ORDER
BY
x
)
WITH
SETTINGS
(
index_granularity
=
4096
,
index_granularity_bytes
=
1048576
)
;
Projection settings override the effective table settings for the projection, subject to validation rules (e.g., invalid or incompatible overrides will be rejected).
DROP PROJECTION
Use the statement below to remove a projection description from a tables metadata and delete projection files from disk.
This is implemented as a
mutation
.
ALTER
TABLE
[
db
.
]
name
[
ON
CLUSTER cluster
]
DROP
PROJECTION
[
IF
EXISTS
]
name
MATERIALIZE PROJECTION
Use the statement below to rebuild the projection
name
in partition
partition_name
.
This is implemented as a
mutation
.
ALTER
TABLE
[
db
.
]
table
[
ON
CLUSTER cluster
]
MATERIALIZE PROJECTION
[
IF
EXISTS
]
name
[
IN
PARTITION
partition_name
]
CLEAR PROJECTION
Use the statement below to delete projection files from disk without removing description.
This is implemented as a
mutation
.
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
engine (including
replicated
variants).
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
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
.
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
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
,
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
"Control Of Projections During Merges" (blog post)
"Projections" (guide)
"Materialized Views versus Projections" |
| Markdown | [Skip to main content](https://clickhouse.com/docs/sql-reference/statements/alter/projection#__docusaurus_skipToContent_fallback)
[](https://clickhouse.com/)
- [Products](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud)
- [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud)
- [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse)
- [Discover more than 100 integrations.](https://clickhouse.com/integrations)
[Discover more than 100 integrations.](https://clickhouse.com/integrations)
- [Use cases](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics)
- [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science)
- [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing)
- [Logs, Events, Traces](https://clickhouse.com/use-cases/observability)
- [All use cases](https://clickhouse.com/use-cases)
[All use cases](https://clickhouse.com/use-cases)
- [Documentation](https://clickhouse.com/docs)
- [Resources](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [User stories](https://clickhouse.com/user-stories)
- [Blog](https://clickhouse.com/blog)
- [Events](https://clickhouse.com/company/events)
- [Learning and certification](https://clickhouse.com/learn)
- [Comparison](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [BigQuery](https://clickhouse.com/comparison/bigquery)
- [PostgreSQL](https://clickhouse.com/comparison/postgresql)
- [Redshift](https://clickhouse.com/comparison/redshift)
- [Rockset](https://clickhouse.com/comparison/rockset)
- [Snowflake](https://clickhouse.com/comparison/snowflake)
- [Video](https://clickhouse.com/videos)
- [Demo](https://clickhouse.com/demos)
- [Pricing](https://clickhouse.com/pricing)
- [Contact](https://clickhouse.com/company/contact?loc=nav)
[46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)
[Search`Ctrl``K`](https://clickhouse.com/docs/search)
[Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=ed1f8d90-8a91-4a77-91fb-3c7a8b48b975&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&utm_ga=GA1.1.659770732.1775912848)
[Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=ed1f8d90-8a91-4a77-91fb-3c7a8b48b975&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&utm_ga=GA1.1.659770732.1775912848)
[Get started](https://clickhouse.com/docs/introduction-clickhouse)
[Cloud](https://clickhouse.com/docs/cloud/overview)
[Manage data](https://clickhouse.com/docs/updating-data)
[Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index)
[Reference](https://clickhouse.com/docs/sql-reference)
[Integrations](https://clickhouse.com/docs/integrations)
[ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview)
[chDB](https://clickhouse.com/docs/chdb)
[About](https://clickhouse.com/docs/about)
[Knowledge Base](https://clickhouse.com/docs/knowledgebase)
[English](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [English](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [日本語](https://clickhouse.com/docs/jp/sql-reference/statements/alter/projection)
- [中文](https://clickhouse.com/docs/zh/sql-reference/statements/alter/projection)
- [Русский](https://clickhouse.com/docs/ru/sql-reference/statements/alter/projection)
- [한국어](https://clickhouse.com/docs/ko/sql-reference/statements/alter/projection)
[Skip to main content](https://clickhouse.com/docs/sql-reference/statements/alter/projection#__docusaurus_skipToContent_fallback)
[](https://clickhouse.com/)
- [Products](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud)
- [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud)
- [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse)
- [Discover more than 100 integrations.](https://clickhouse.com/integrations)
[Discover more than 100 integrations.](https://clickhouse.com/integrations)
- [Use cases](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics)
- [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science)
- [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing)
- [Logs, Events, Traces](https://clickhouse.com/use-cases/observability)
- [All use cases](https://clickhouse.com/use-cases)
[All use cases](https://clickhouse.com/use-cases)
- [Documentation](https://clickhouse.com/docs)
- [Resources](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [User stories](https://clickhouse.com/user-stories)
- [Blog](https://clickhouse.com/blog)
- [Events](https://clickhouse.com/company/events)
- [Learning and certification](https://clickhouse.com/learn)
- [Comparison](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [BigQuery](https://clickhouse.com/comparison/bigquery)
- [PostgreSQL](https://clickhouse.com/comparison/postgresql)
- [Redshift](https://clickhouse.com/comparison/redshift)
- [Rockset](https://clickhouse.com/comparison/rockset)
- [Snowflake](https://clickhouse.com/comparison/snowflake)
- [Video](https://clickhouse.com/videos)
- [Demo](https://clickhouse.com/demos)
- [Pricing](https://clickhouse.com/pricing)
- [Contact](https://clickhouse.com/company/contact?loc=nav)
[46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)
[Search`Ctrl``K`](https://clickhouse.com/docs/search)
[Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=ed1f8d90-8a91-4a77-91fb-3c7a8b48b975&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&utm_ga=GA1.1.659770732.1775912848)
[Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=ed1f8d90-8a91-4a77-91fb-3c7a8b48b975&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&utm_ga=GA1.1.659770732.1775912848)
[Get started](https://clickhouse.com/docs/introduction-clickhouse)
[Cloud](https://clickhouse.com/docs/cloud/overview)
[Manage data](https://clickhouse.com/docs/updating-data)
[Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index)
[Reference](https://clickhouse.com/docs/sql-reference)
[Integrations](https://clickhouse.com/docs/integrations)
[ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview)
[chDB](https://clickhouse.com/docs/chdb)
[About](https://clickhouse.com/docs/about)
[Knowledge Base](https://clickhouse.com/docs/knowledgebase)
[English](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [English](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [日本語](https://clickhouse.com/docs/jp/sql-reference/statements/alter/projection)
- [中文](https://clickhouse.com/docs/zh/sql-reference/statements/alter/projection)
- [Русский](https://clickhouse.com/docs/ru/sql-reference/statements/alter/projection)
- [한국어](https://clickhouse.com/docs/ko/sql-reference/statements/alter/projection)
[Search`Ctrl``K`](https://clickhouse.com/docs/search)
- [Introduction](https://clickhouse.com/docs/sql-reference)
- [Syntax](https://clickhouse.com/docs/sql-reference/syntax)
- [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats)
- [Data types](https://clickhouse.com/docs/sql-reference/data-types)
- [Statements](https://clickhouse.com/docs/sql-reference/statements)
- [SELECT](https://clickhouse.com/docs/sql-reference/statements/select)
- [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into)
- [CREATE](https://clickhouse.com/docs/sql-reference/statements/create)
- [ALTER](https://clickhouse.com/docs/sql-reference/statements/alter)
- [COLUMN](https://clickhouse.com/docs/sql-reference/statements/alter/column)
- [PARTITION](https://clickhouse.com/docs/sql-reference/statements/alter/partition)
- [SETTING](https://clickhouse.com/docs/sql-reference/statements/alter/setting)
- [DELETE](https://clickhouse.com/docs/sql-reference/statements/alter/delete)
- [UPDATE](https://clickhouse.com/docs/sql-reference/statements/alter/update)
- [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/alter/order-by)
- [SAMPLE BY](https://clickhouse.com/docs/sql-reference/statements/alter/sample-by)
- [INDEX](https://clickhouse.com/docs/sql-reference/statements/alter/skipping-index)
- [CONSTRAINT](https://clickhouse.com/docs/sql-reference/statements/alter/constraint)
- [TTL](https://clickhouse.com/docs/sql-reference/statements/alter/ttl)
- [STATISTICS](https://clickhouse.com/docs/sql-reference/statements/alter/statistics)
- [USER](https://clickhouse.com/docs/sql-reference/statements/alter/user)
- [APPLY DELETED MASK](https://clickhouse.com/docs/sql-reference/statements/alter/apply-deleted-mask)
- [QUOTA](https://clickhouse.com/docs/sql-reference/statements/alter/quota)
- [ROLE](https://clickhouse.com/docs/sql-reference/statements/alter/role)
- [APPLY PATCHES](https://clickhouse.com/docs/sql-reference/statements/alter/apply-patches)
- [ROW POLICY](https://clickhouse.com/docs/sql-reference/statements/alter/row-policy)
- [MASKING POLICY](https://clickhouse.com/docs/sql-reference/statements/alter/masking-policy)
- [SETTINGS PROFILE](https://clickhouse.com/docs/sql-reference/statements/alter/settings-profile)
- [PROJECTION](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [VIEW](https://clickhouse.com/docs/sql-reference/statements/alter/view)
- [ALTER TABLE ... MODIFY COMMENT](https://clickhouse.com/docs/sql-reference/statements/alter/comment)
- [ALTER DATABASE ... MODIFY COMMENT](https://clickhouse.com/docs/sql-reference/statements/alter/database-comment)
- [NAMED COLLECTION](https://clickhouse.com/docs/sql-reference/statements/alter/named-collection)
- [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete)
- [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system)
- [SHOW](https://clickhouse.com/docs/sql-reference/statements/show)
- [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant)
- [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain)
- [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke)
- [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update)
- [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach)
- [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table)
- [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table)
- [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach)
- [DROP](https://clickhouse.com/docs/sql-reference/statements/drop)
- [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists)
- [KILL](https://clickhouse.com/docs/sql-reference/statements/kill)
- [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize)
- [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename)
- [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange)
- [SET](https://clickhouse.com/docs/sql-reference/statements/set)
- [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role)
- [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate)
- [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as)
- [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with)
- [USE](https://clickhouse.com/docs/sql-reference/statements/use)
- [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch)
- [MOVE](https://clickhouse.com/docs/sql-reference/statements/move)
- [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant)
- [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop)
- [Operators](https://clickhouse.com/docs/sql-reference/operators)
- [Engines](https://clickhouse.com/docs/engines)
- [Database Engines](https://clickhouse.com/docs/engines/database-engines)
- [Table Engines](https://clickhouse.com/docs/engines/table-engines)
- [Functions](https://clickhouse.com/docs/sql-reference/functions)
- [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions)
- [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions)
- [Table functions](https://clickhouse.com/docs/sql-reference/table-functions)
- [Window functions](https://clickhouse.com/docs/sql-reference/window-functions)
- [Formats](https://clickhouse.com/docs/interfaces/formats)
- [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
- [Introduction](https://clickhouse.com/docs/sql-reference)
- [Statements](https://clickhouse.com/docs/sql-reference/statements)
- [ALTER](https://clickhouse.com/docs/sql-reference/statements/alter)
- PROJECTION
[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/projection.md)
# Projections
This page discusses what projections are, how you can use them and various options for manipulating projections.
## Overview of projections[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#overview "Direct link to Overview of projections")
Projections store data in a format that optimizes query execution, this feature is useful for:
- Running queries on a column that is not a part of the primary key
- Pre-aggregating columns, it will reduce both computation and IO
You can define one or more projections for a table, and during the query analysis the projection with the least data to scan will be selected by ClickHouse without modifying the query provided by the user.
Disk usage
Projections will create internally a new hidden table, this means that more IO and space on disk will be required. For example, if the projection has defined a different primary key, all the data from the original table will be duplicated.
You can see more technical details about how projections work internally on this [page](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes#option-3-projections).
## Using projections[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#examples "Direct link to Using projections")
### Example filtering without using primary keys[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#example-filtering-without-using-primary-keys "Direct link to Example filtering without using primary keys")
Creating the table:
```
CREATE TABLE visits_order
(
`user_id` UInt64,
`user_name` String,
`pages_visited` Nullable(Float64),
`user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent
```
Using `ALTER TABLE`, we could add the Projection to an existing table:
```
ALTER TABLE visits_order ADD PROJECTION user_name_projection (
SELECT *
ORDER BY user_name
)
ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection
```
Inserting the data:
```
INSERT INTO visits_order SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
```
The Projection will allow us to filter by `user_name` fast even if in the original Table `user_name` was not defined as a `PRIMARY_KEY`. At query time, ClickHouse determines that less data will be processed if the projection is used, as the data is ordered by `user_name`.
```
SELECT
*
FROM visits_order
WHERE user_name='test'
LIMIT 2
```
To verify that a query is using the projection, we could review the `system.query_log` table. On the `projections` field we have the name of the projection used or empty if none has been used:
```
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
```
### Example pre-aggregation query[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#example-pre-aggregation-query "Direct link to Example pre-aggregation query")
Create the table with projection `projection_visits_by_user`:
```
CREATE TABLE visits
(
`user_id` UInt64,
`user_name` String,
`pages_visited` Nullable(Float64),
`user_agent` String,
PROJECTION projection_visits_by_user
(
SELECT
user_agent,
sum(pages_visited)
GROUP BY user_id, user_agent
)
)
ENGINE = MergeTree()
ORDER BY user_agent
```
Insert the data:
```
INSERT INTO visits SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
```
```
INSERT INTO visits SELECT
number,
'test',
1. * (number / 2),
'IOS'
FROM numbers(100, 500);
```
Execute a first query with `GROUP BY` using the field `user_agent`. This query will not use the projection defined as the pre-aggregation does not match.
```
SELECT
user_agent,
count(DISTINCT user_id)
FROM visits
GROUP BY user_agent
```
To make use of the projection you can execute queries that select part of, or all of the pre-aggregation and `GROUP BY` fields:
```
SELECT
user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent
```
```
SELECT
user_agent,
sum(pages_visited)
FROM visits
GROUP BY user_agent
```
As previously mentioned, you can review the `system.query_log` table to understand if a projection was used. The `projections` field shows the name of the projection used. It will be empty if no projection has been used:
```
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
```
### Creating and using projection indexes[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#projection-indexes "Direct link to Creating and using projection indexes")
Creating a [projection index](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#projection-index):
```
CREATE TABLE events
(
`event_time` DateTime,
`event_id` UInt64,
`user_id` UInt64,
`huge_string` String,
PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);
```
Creating a projection with explicit `_part_offset` field
Projection indexes can alternatively be created using the following syntax (not recommended):
```
CREATE TABLE events
(
`event_time` DateTime,
`event_id` UInt64,
`user_id` UInt64,
`huge_string` String,
PROJECTION order_by_user_id
(
SELECT
_part_offset
ORDER BY user_id
)
)
ENGINE = MergeTree()
ORDER BY (event_id);
```
Inserting some sample data:
```
INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;
```
The `_part_offset` field preserves its value through merges and mutations, making it valuable for secondary indexing. We can leverage this in queries:
```
SELECT
count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
SELECT _part_starting_offset + _part_offset
FROM events
WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1
```
## Manipulating projections[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#manipulating-projections "Direct link to Manipulating projections")
The following operations with [projections](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#projections) are available:
### ADD PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#add-projection "Direct link to ADD PROJECTION")
Use the statement below to add a projection description to a tables metadata:
```
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]
```
#### `WITH SETTINGS` Clause[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#with-settings "Direct link to with-settings")
`WITH SETTINGS` defines **projection-level settings**, which customize how the projection stores data (for example, `index_granularity` or `index_granularity_bytes`). These correspond directly to **MergeTree table settings**, but apply **only to this projection**.
Example:
```
ALTER TABLE t
ADD PROJECTION p (
SELECT x ORDER BY x
) WITH SETTINGS (
index_granularity = 4096,
index_granularity_bytes = 1048576
);
```
Projection settings override the effective table settings for the projection, subject to validation rules (e.g., invalid or incompatible overrides will be rejected).
### DROP PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#drop-projection "Direct link to DROP PROJECTION")
Use the statement below to remove a projection description from a tables metadata and delete projection files from disk. This is implemented as a [mutation](https://clickhouse.com/docs/sql-reference/statements/alter#mutations).
```
ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name
```
### MATERIALIZE PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/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](https://clickhouse.com/docs/sql-reference/statements/alter#mutations).
```
ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
```
### CLEAR PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/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](https://clickhouse.com/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`](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) engine (including [replicated](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication) variants).
### Controlling projection merge behavior[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#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`](https://clickhouse.com/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`](https://clickhouse.com/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[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#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,
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[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#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)](https://clickhouse.com/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)
[Previous SETTINGS PROFILE](https://clickhouse.com/docs/sql-reference/statements/alter/settings-profile)
[Next VIEW](https://clickhouse.com/docs/sql-reference/statements/alter/view)
- [Overview of projections](https://clickhouse.com/docs/sql-reference/statements/alter/projection#overview)
- [Using projections](https://clickhouse.com/docs/sql-reference/statements/alter/projection#examples)
- [Example filtering without using primary keys](https://clickhouse.com/docs/sql-reference/statements/alter/projection#example-filtering-without-using-primary-keys)
- [Example pre-aggregation query](https://clickhouse.com/docs/sql-reference/statements/alter/projection#example-pre-aggregation-query)
- [Creating and using projection indexes](https://clickhouse.com/docs/sql-reference/statements/alter/projection#projection-indexes)
- [Manipulating projections](https://clickhouse.com/docs/sql-reference/statements/alter/projection#manipulating-projections)
- [ADD PROJECTION](https://clickhouse.com/docs/sql-reference/statements/alter/projection#add-projection)
- [DROP PROJECTION](https://clickhouse.com/docs/sql-reference/statements/alter/projection#drop-projection)
- [MATERIALIZE PROJECTION](https://clickhouse.com/docs/sql-reference/statements/alter/projection#materialize-projection)
- [CLEAR PROJECTION](https://clickhouse.com/docs/sql-reference/statements/alter/projection#clear-projection)
- [Controlling projection merge behavior](https://clickhouse.com/docs/sql-reference/statements/alter/projection#control-projections-merges)
- [Limitations](https://clickhouse.com/docs/sql-reference/statements/alter/projection#limitations)
- [See also](https://clickhouse.com/docs/sql-reference/statements/alter/projection#see-also)
Was this page helpful?
###### Try ClickHouse Cloud for FREE
Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up.
[Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=ed1f8d90-8a91-4a77-91fb-3c7a8b48b975&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Falter%2Fprojection&utm_ga=GA1.1.659770732.1775912848)
© 2016–2026 ClickHouse, Inc.
[Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service)
© 2016–2026 ClickHouse, Inc.
[Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service)
[](https://clickhouse.com/)
EN
- Get started▼
- Cloud▼
- Manage data▼
- Server admin▼
- Reference▼
- Integrations▼
- ClickStack▼
- chDB▼
- About▼
[](https://clickhouse.com/)
EN
main-menu
- Introduction▼
- [Syntax](https://clickhouse.com/docs/sql-reference/syntax)
- [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats)
- Data types▼
- Statements▼
- SELECT▼
- [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into)
- CREATE▼
- ALTER▼
- [COLUMN](https://clickhouse.com/docs/sql-reference/statements/alter/column)
- [PARTITION](https://clickhouse.com/docs/sql-reference/statements/alter/partition)
- [SETTING](https://clickhouse.com/docs/sql-reference/statements/alter/setting)
- [DELETE](https://clickhouse.com/docs/sql-reference/statements/alter/delete)
- [UPDATE](https://clickhouse.com/docs/sql-reference/statements/alter/update)
- [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/alter/order-by)
- [SAMPLE BY](https://clickhouse.com/docs/sql-reference/statements/alter/sample-by)
- [INDEX](https://clickhouse.com/docs/sql-reference/statements/alter/skipping-index)
- [CONSTRAINT](https://clickhouse.com/docs/sql-reference/statements/alter/constraint)
- [TTL](https://clickhouse.com/docs/sql-reference/statements/alter/ttl)
- [STATISTICS](https://clickhouse.com/docs/sql-reference/statements/alter/statistics)
- [USER](https://clickhouse.com/docs/sql-reference/statements/alter/user)
- [APPLY DELETED MASK](https://clickhouse.com/docs/sql-reference/statements/alter/apply-deleted-mask)
- [QUOTA](https://clickhouse.com/docs/sql-reference/statements/alter/quota)
- [ROLE](https://clickhouse.com/docs/sql-reference/statements/alter/role)
- [APPLY PATCHES](https://clickhouse.com/docs/sql-reference/statements/alter/apply-patches)
- [ROW POLICY](https://clickhouse.com/docs/sql-reference/statements/alter/row-policy)
- [MASKING POLICY](https://clickhouse.com/docs/sql-reference/statements/alter/masking-policy)
- [SETTINGS PROFILE](https://clickhouse.com/docs/sql-reference/statements/alter/settings-profile)
- [PROJECTION](https://clickhouse.com/docs/sql-reference/statements/alter/projection)
- [VIEW](https://clickhouse.com/docs/sql-reference/statements/alter/view)
- [ALTER TABLE ... MODIFY COMMENT](https://clickhouse.com/docs/sql-reference/statements/alter/comment)
- [ALTER DATABASE ... MODIFY COMMENT](https://clickhouse.com/docs/sql-reference/statements/alter/database-comment)
- [NAMED COLLECTION](https://clickhouse.com/docs/sql-reference/statements/alter/named-collection)
- [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete)
- [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system)
- [SHOW](https://clickhouse.com/docs/sql-reference/statements/show)
- [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant)
- [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain)
- [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke)
- [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update)
- [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach)
- [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table)
- [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table)
- [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach)
- [DROP](https://clickhouse.com/docs/sql-reference/statements/drop)
- [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists)
- [KILL](https://clickhouse.com/docs/sql-reference/statements/kill)
- [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize)
- [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename)
- [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange)
- [SET](https://clickhouse.com/docs/sql-reference/statements/set)
- [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role)
- [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate)
- [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as)
- [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with)
- [USE](https://clickhouse.com/docs/sql-reference/statements/use)
- [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch)
- [MOVE](https://clickhouse.com/docs/sql-reference/statements/move)
- [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant)
- [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop)
- Operators▼
- Engines▼
- Functions▼
- Formats▼
- [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) |
| Readable Markdown | This page discusses what projections are, how you can use them and various options for manipulating projections.
## Overview of projections[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#overview "Direct link to Overview of projections")
Projections store data in a format that optimizes query execution, this feature is useful for:
- Running queries on a column that is not a part of the primary key
- Pre-aggregating columns, it will reduce both computation and IO
You can define one or more projections for a table, and during the query analysis the projection with the least data to scan will be selected by ClickHouse without modifying the query provided by the user.
Disk usage
Projections will create internally a new hidden table, this means that more IO and space on disk will be required. For example, if the projection has defined a different primary key, all the data from the original table will be duplicated.
You can see more technical details about how projections work internally on this [page](https://clickhouse.com/docs/guides/best-practices/sparse-primary-indexes#option-3-projections).
## Using projections[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#examples "Direct link to Using projections")
### Example filtering without using primary keys[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#example-filtering-without-using-primary-keys "Direct link to Example filtering without using primary keys")
Creating the table:
```
CREATE TABLE visits_order
(
`user_id` UInt64,
`user_name` String,
`pages_visited` Nullable(Float64),
`user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent
```
Using `ALTER TABLE`, we could add the Projection to an existing table:
```
ALTER TABLE visits_order ADD PROJECTION user_name_projection (
SELECT *
ORDER BY user_name
)
ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection
```
Inserting the data:
```
INSERT INTO visits_order SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
```
The Projection will allow us to filter by `user_name` fast even if in the original Table `user_name` was not defined as a `PRIMARY_KEY`. At query time, ClickHouse determines that less data will be processed if the projection is used, as the data is ordered by `user_name`.
```
SELECT
*
FROM visits_order
WHERE user_name='test'
LIMIT 2
```
To verify that a query is using the projection, we could review the `system.query_log` table. On the `projections` field we have the name of the projection used or empty if none has been used:
```
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
```
### Example pre-aggregation query[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#example-pre-aggregation-query "Direct link to Example pre-aggregation query")
Create the table with projection `projection_visits_by_user`:
```
CREATE TABLE visits
(
`user_id` UInt64,
`user_name` String,
`pages_visited` Nullable(Float64),
`user_agent` String,
PROJECTION projection_visits_by_user
(
SELECT
user_agent,
sum(pages_visited)
GROUP BY user_id, user_agent
)
)
ENGINE = MergeTree()
ORDER BY user_agent
```
Insert the data:
```
INSERT INTO visits SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
```
```
INSERT INTO visits SELECT
number,
'test',
1. * (number / 2),
'IOS'
FROM numbers(100, 500);
```
Execute a first query with `GROUP BY` using the field `user_agent`. This query will not use the projection defined as the pre-aggregation does not match.
```
SELECT
user_agent,
count(DISTINCT user_id)
FROM visits
GROUP BY user_agent
```
To make use of the projection you can execute queries that select part of, or all of the pre-aggregation and `GROUP BY` fields:
```
SELECT
user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent
```
```
SELECT
user_agent,
sum(pages_visited)
FROM visits
GROUP BY user_agent
```
As previously mentioned, you can review the `system.query_log` table to understand if a projection was used. The `projections` field shows the name of the projection used. It will be empty if no projection has been used:
```
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
```
### Creating and using projection indexes[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#projection-indexes "Direct link to Creating and using projection indexes")
Creating a [projection index](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#projection-index):
```
CREATE TABLE events
(
`event_time` DateTime,
`event_id` UInt64,
`user_id` UInt64,
`huge_string` String,
PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);
```
Creating a projection with explicit `_part_offset` field
Inserting some sample data:
```
INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;
```
The `_part_offset` field preserves its value through merges and mutations, making it valuable for secondary indexing. We can leverage this in queries:
```
SELECT
count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
SELECT _part_starting_offset + _part_offset
FROM events
WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1
```
## Manipulating projections[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#manipulating-projections "Direct link to Manipulating projections")
The following operations with [projections](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#projections) are available:
### ADD PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#add-projection "Direct link to ADD PROJECTION")
Use the statement below to add a projection description to a tables metadata:
```
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]
```
#### `WITH SETTINGS` Clause[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#with-settings "Direct link to with-settings")
`WITH SETTINGS` defines **projection-level settings**, which customize how the projection stores data (for example, `index_granularity` or `index_granularity_bytes`). These correspond directly to **MergeTree table settings**, but apply **only to this projection**.
Example:
```
ALTER TABLE t
ADD PROJECTION p (
SELECT x ORDER BY x
) WITH SETTINGS (
index_granularity = 4096,
index_granularity_bytes = 1048576
);
```
Projection settings override the effective table settings for the projection, subject to validation rules (e.g., invalid or incompatible overrides will be rejected).
### DROP PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#drop-projection "Direct link to DROP PROJECTION")
Use the statement below to remove a projection description from a tables metadata and delete projection files from disk. This is implemented as a [mutation](https://clickhouse.com/docs/sql-reference/statements/alter#mutations).
```
ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name
```
### MATERIALIZE PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/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](https://clickhouse.com/docs/sql-reference/statements/alter#mutations).
```
ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
```
### CLEAR PROJECTION[](https://clickhouse.com/docs/sql-reference/statements/alter/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](https://clickhouse.com/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`](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) engine (including [replicated](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication) variants).
### Controlling projection merge behavior[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#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`](https://clickhouse.com/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`](https://clickhouse.com/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[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#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,
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[](https://clickhouse.com/docs/sql-reference/statements/alter/projection#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)](https://clickhouse.com/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) |
| Shard | 89 (laksa) |
| Root Hash | 12633450985039531489 |
| Unparsed URL | com,clickhouse!/docs/sql-reference/statements/alter/projection s443 |