ClickHouse VIEW vs materialized view vs projection

"View vs materialized view" is a SQL-engine question. In ClickHouse you have a third option — a projection — and mixing the three is how people ship a "view" that either does nothing for performance or doubles write amplification.

Deep MV vs projection trade-offs live in projections vs materialized views. This page is the vocabulary and the picker.

Three different things people call "views"

Ordinary VIEW: when it's enough

Use a VIEW to stabilize an API, hide columns, or reuse a join you are happy to pay for on every read.

CREATE VIEW analytics.events_public AS
SELECT event_time, user_id, event_type
FROM analytics.events
WHERE event_type != 'debug';

EXPLAIN on a query through the view should look like the same granules as querying the base table. If you created a view because the dashboard was slow, you have not optimized anything — see skip indices or the MV/projection path.

MATERIALIZED VIEW: stored results

A classic MV is not a cache sitting on the view. It is a trigger: each insert into the source also inserts into a destination table (often SummingMergeTree / AggregatingMergeTree).

CREATE TABLE analytics.events_by_day
(
    d Date,
    event_type LowCardinality(String),
    c UInt64
)
ENGINE = SummingMergeTree
ORDER BY (event_type, d);

CREATE MATERIALIZED VIEW analytics.events_by_day_mv
TO analytics.events_by_day
AS
SELECT toDate(event_time) AS d, event_type, count() AS c
FROM analytics.events
GROUP BY d, event_type;

You query events_by_day, not the MV name. JOINs and WHERE in the MV SQL are allowed. Lifecycle is decoupled: you can TTL the rollup harder than the raw log.

Refreshable materialized views (newer ClickHouse) recompute on a schedule instead of piggybacking every insert — useful when the source is not an append-only MergeTree stream.

Projection: transparent acceleration

A projection is another sort / aggregate stored next to the table. You keep querying analytics.events. If the query matches, ClickHouse reads the projection.

No JOIN in the projection. You cannot point a BI tool at a separate rollup table unless you also query that table. Empty projections (defined, never materialized) waste hope, not disk — check system.projection_parts.

Full decision matrix, write amplification, and EXPLAIN proof: projections vs materialized views.

Quick picker

Inventory SQL

SELECT database, name, engine
FROM system.tables
WHERE engine IN ('View', 'MaterializedView')
ORDER BY engine, database, name;

SELECT database, table, name
FROM system.projections
ORDER BY database, table, name;

Related