An Intro to ClickHouse Projections

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

ClickHouse projections are one of the most interesting query-optimization features in the engine because they let you maintain alternate physical representations of the same table. That can mean a different sort order, pre-aggregation, or a faster path for certain filters, all while keeping the projection tied closely to the base table.

They are powerful, but they are also easy to misunderstand. Teams often ask whether projections are just materialized views with a new name. They are not.

This guide covers how to think about them in practice.

In this post, we’ll cover:

• What projections are
• Why they help
• How they differ from materialized views
• A simple example
• When not to use them

What projections are

ClickHouse describes projections as hidden, automatically maintained data representations attached to a table. They can reorder data or precompute derived structures so the query planner has a faster path available at read time.

The key idea is that a projection lives with the base table rather than as a separate user-managed object you query directly.

That makes projections especially useful when:

• your main table’s ORDER BY is correct for the primary workload
• but a secondary workload would benefit from a different layout

This is a common ClickHouse problem. You want one physical sort order, but users filter on multiple different dimensions.

Why projections matter

ClickHouse performance is heavily influenced by physical layout. A well-chosen ORDER BY can improve performance dramatically, but there is still a limit: one base sort order cannot be perfect for every access pattern.

Projections help close that gap.

Recent ClickHouse releases made projections even more interesting by improving how they behave like secondary indexes for pruning. That means the planner can often use them more effectively than older mental models suggest.

A simple example

Say your events table is sorted by time because most queries are time-windowed:

CREATE TABLE events
(
  event_date Date,
  event_time DateTime,
  region LowCardinality(String),
  user_id UInt64,
  url String,
  PROJECTION region_proj
  (
    SELECT *
    ORDER BY region, event_date
  )
)
ENGINE = MergeTree
ORDER BY (event_date, event_time, user_id);

The base table stays optimized for time-oriented queries. The projection gives ClickHouse another path for region-oriented filters.

That is the core value proposition: keep a sensible primary layout while creating a faster route for an important secondary pattern.

Projections vs materialized views

This is the main conceptual comparison.

Use projections when you want:

• an optimization attached to a base table
• alternate sort orders
• hidden maintenance tied directly to the same object
• query planner transparency

Use materialized views when you want:

• a separately queryable derived table
• more explicit control over downstream storage
• a modeling artifact that stands on its own

The short version:

• projections are a performance structure
• materialized views are a data product structure

Sometimes they overlap. But the intent is different.

What projections are especially good at

Projections shine when:

• a table serves multiple common filter patterns
• the base sort key is correct for one major workload
• creating a second full table would be clumsy or harder to maintain

Good examples:

• time-sorted data that is also often filtered by region, tenant, or user segment
• tables where a subset of analytical queries repeatedly suffer because the main ordering key does not help enough
• repeated aggregations that can be precomputed in a projection design

What projections do not fix

Projections are not a substitute for good primary design.

If the base table’s ORDER BY is poor for the dominant workload, fix that first. Projections are a secondary optimization, not a bandage for a bad first decision.

They also do not remove the need to understand workload shape. If query patterns are messy and inconsistent, projections can become an optimization guessing game instead of a deliberate design choice.

Common mistakes

Adding projections before understanding the main table layout

Always start with the base table. If the core physical design is wrong, projections may just hide the problem temporarily.

Treating them like general-purpose indexes

They can behave index-like in important ways, but they are still physical data structures with storage and maintenance implications. This is not the same as sprinkling B-tree indexes over an OLTP table.

Creating too many

Every projection has cost. If you add one for every possible query dimension, you turn a clean design into maintenance debt.

Forgetting workload drift

A projection that helps today may stop mattering six months later. Revisit them as query patterns change.

A practical decision rule

Create a projection when all of these are true:

1. The base table’s main sort order is already justified.
2. A secondary query pattern matters enough to optimize.
3. The benefit is repeatable, not hypothetical.

If you are not sure, benchmark first.

Projections and query optimization strategy

The best mental model is that ClickHouse optimization is layered:

• choose good data types
• choose a strong ORDER BY
• choose a sensible partitioning scheme
• then use projections where a second layout is worth maintaining

That order matters. Projections are powerful because they come after you already understand the table, not before.

Frequently asked questions

What is a projection in ClickHouse? A projection is a secondary physical layout of a table’s rows, stored inside the same MergeTree parts as the base table and maintained automatically as you insert. It is intended to make a different sort order or pre-aggregation available to queries that do not match the base table’s primary sort. Unlike a separate materialized view, a projection lives on the same data, so reads against it can never be stale relative to the base table.

Are ClickHouse projections the same as materialized views? No. A materialized view is a separate table populated by a trigger on inserts — it has its own storage, its own MergeTree parameters, and you query it by name. A projection is part of the parent table’s storage, has no separate name to query, and is selected automatically by the optimizer when it can satisfy the query. The practical difference: projections are atomic with the base table, materialized views are not.

When should I add a projection to a ClickHouse table? Add a projection when the base table’s primary ORDER BY is correct for the dominant access pattern but a secondary pattern still does too much work. The four-way test is: the base sort is justified, the secondary pattern is meaningful (real query volume, not “someone might want this”), the benefit is measurable on representative data, and the write overhead is acceptable. If any of those is false, projections are not the right tool.

Do projections slow down inserts in ClickHouse? Yes — each projection materializes the rows in its own sort order at insert time, so inserts cost roughly proportionally more in write CPU and disk. The penalty is usually small for one or two well-chosen projections and visible for four or five. Measure on a representative workload before adding more than two.

Do I need to query the projection by name? No, and you cannot. The ClickHouse optimizer picks the projection automatically when it determines the projection’s sort or aggregation can satisfy the query more cheaply than the base table. You can verify this happened by inspecting the EXPLAIN output or system.query_log for the projections field.

What is the difference between a normal projection and an aggregating projection? A normal projection stores rows in a different ORDER BY. An aggregating projection (using SELECT key, sum(x), max(y) ... GROUP BY key) stores pre-aggregated state for the columns you specify, similar to an AggregatingMergeTree. Use a normal projection when queries filter or sort differently; use an aggregating projection when queries roll up to a coarser grain and you want to avoid scanning the base rows.

Can a projection have a different primary key than the base table? Yes — that is the whole point. The projection declares its own ORDER BY, which becomes its sort key, and queries that filter or aggregate on that key can use the projection in place of the base. The base table’s primary key is unaffected.

How do I drop or rebuild a ClickHouse projection? Use ALTER TABLE <table> DROP PROJECTION <name> to remove a projection from the schema, and ALTER TABLE <table> MATERIALIZE PROJECTION <name> to backfill it across all existing parts after adding it to a populated table. Without the MATERIALIZE step, the projection only covers data inserted after the ALTER ADD PROJECTION ran.

Wrapping up

ClickHouse projections are valuable because they let you optimize secondary access patterns without giving up the base table design that makes your primary workload fast. They are not just materialized views with different branding, and they are not a replacement for thoughtful schema design.

Use them when:

• the base sort order is right
• an important secondary pattern still needs help
• the benefit is measurable

If you are still working through the fundamentals, start with our guide to ClickHouse PARTITION BY, ORDER BY, and PRIMARY KEY. For a cheaper alternative when the secondary pattern only needs pruning rather than re-sorting, see ClickHouse TTL and data skipping indexes in practice. And if you want a day-to-day interface for experimenting with layouts and queries, see our guide to the best ClickHouse GUI tools.