ClickHouse projections versus materialized views: which one fits your queries
Use a projection when the same table must serve several query shapes and you want ClickHouse to choose the right structure per query; use a materialized view when you need a separate target table that other teams query directly or that holds a precomputed aggregate. A team whose dashboards or log searches are slow on a self-managed cluster usually hits this choice once the base table's ordering key already fits its most common filter and a second access pattern still scans too much data. A projection behaves like an additional hidden table stored with each data part. It can carry a different primary index, so filters on a different column can skip data, or a precomputed aggregation, so grouped queries read fewer rows. ClickHouse decides at query time whether to use it, so application code does not change.
The choice depends on three things: query shape, insert cost and who needs to see the data. Projections are attached to the table and maintained as part of it, so they suit the case where the base table keeps its name and its consumers, and the extra work is a second copy of the data per part. A materialized view writes into a target table at insert time, which gives you a table you can name, grant access to, retain on its own schedule or aggregate with a different engine. Both add write cost, and both need checking on a copy of production data before you commit. Confirm the projection behavior, its limits and how it is created or materialized in the ClickHouse projections documentation against the server version you run.
Before you build either, write down the five or six queries that matter most, record the rows read for each on a representative copy, and note who queries which result. Test one projection and one view against that list, measure insert throughput and disk use on the same hardware, and confirm with EXPLAIN that the planner uses what you built. If a result must be shared, retained separately or refreshed on a schedule, a view is usually the better fit. If the only goal is faster reads of the same data in another order, a projection keeps the model simpler. Ask your team to confirm which of these needs apply before the design review.
How projections and materialized views differ in practice
A projection acts like an additional hidden table attached to a base table. It can carry a different primary index, which gives the data a second sort order, or a precomputed aggregation. The projection's data is stored alongside the base table's parts, so it lives inside the same table definition and is maintained by ClickHouse itself rather than by a separate object you query by name. Queries against the base table do not need to name the projection. ClickHouse decides at query time whether a projection can answer part or all of a query more cheaply, and uses it when it can.
- Where the data lives: inside the base table's storage, as hidden structures tied to each part, not as a separate table you manage.
- How queries reach it: applications keep querying the base table; the planner chooses the projection automatically.
- How ClickHouse selects it: the choice is made per query by the optimizer, so the same table can serve different access patterns without rewritten SQL.
- What the insert path does: each insert writes the projection data as part of the same insert, so write cost grows with every projection added.
- What to watch: a projection you expect to be used may be skipped, so verify the choice with EXPLAIN on representative queries.
Confirm version-specific projection behavior, including supported expressions and how projections are materialized for existing parts, in the ClickHouse projections documentation before you rely on it.
An incremental materialized view is a trigger-like object. When rows are inserted into a source table, the view's SELECT runs on the new block and writes its output into a separate target table. The target is a regular table with its own engine, ordering and retention, so it is queried by name. Applications therefore need to know which table to read, and the design of the target table, such as an aggregating engine, is a deliberate part of the build rather than something the optimizer creates for you.
- Where the data lives: in a separate target table that you define, name, secure and retain independently of the source.
- How queries reach it: queries and dashboards must target the view's target table directly.
- How ClickHouse selects it: there is no automatic selection; the query author chooses the table.
- What the insert path does: the view's query runs during each insert into the source, so the transformation cost is paid at write time.
- What to watch: rows already in the source before the view was created are not transformed unless you backfill them.
Confirm current materialized view behavior, including how inserts propagate and how to backfill, in the ClickHouse documentation for your target version.
The most visible difference is how a query ends up using the extra structure. With a projection, the decision is invisible to the application, which is useful when many dashboards and ad hoc queries share one table and you want them to benefit without changes. With a materialized view, the decision is explicit, which is useful when the team wants a fixed, documented read path and clear ownership of the summary table.
- Transparency: projections change the plan for queries on the base table; materialized views change which table a query should name.
- Control: a materialized view gives you a stable schema for a known set of reports, while a projection serves whatever filters and sorts the optimizer finds useful.
- Coverage: a projection that cannot cover a query falls back to the base table, which can hide a slow path; a missing target table fails loudly, which is easier to notice.
- Testing: check each important query with EXPLAIN against a copy of production data to see whether the projection is chosen.
- Governance: access policies on target tables are set separately from the base table, which matters for row-level restrictions.
Before relying on either option for a regulated dataset, confirm how access controls apply to hidden or target structures in the ClickHouse access control documentation.
Both structures add write work, but the cost and the failure modes differ. Projections are tied to the base table's parts, so their lifecycle follows merges, mutations and partition operations on that table. Materialized views are separate tables with their own storage, merges and retention, which means they can be dropped, rebuilt or retained on a different schedule. Operators need to plan for both, especially when a view or projection must be changed on a large table.
- Write amplification: every projection and every materialized view adds work to each insert, so measure ingestion throughput before and after adding one.
- Backfill: adding a materialized view does not transform historical rows automatically; plan a backfill job for existing data.
- Schema changes: changing a projection means rebuilding it for existing parts, which can be slow on large tables; confirm the approach for your version.
- Backups: the target tables of a materialized view are regular tables and must be included in BACKUP scope; confirm projection coverage in your backup procedure.
- Restore tests: a restore that includes the source but not the target can leave dashboards silently incomplete, so test both.
Review the ClickHouse backup documentation and the mutation and projection notes for your version before scheduling any structural change.
Which option fits which query pattern
This table compares projections, incremental materialized views, refreshable materialized views, and simpler first steps across query shape, insert cost, visibility, backfill and ownership. Read each row against your own query mix, and use the final row to decide whether a derived structure is needed at all.
| Decision factor | Projection | Incremental or refreshable materialized view |
|---|---|---|
| Query shape it serves | Same base table read with a different sort order or a precomputed aggregate, chosen by the optimizer when it matches the query | Any precomputed result shape you define, including joins and aggregates the optimizer would never derive on its own |
| Insert overhead | Written with each insert into the base table, adding work to every insert | Incremental views run on each insert into the source table, so their cost depends on the view query and target engine |
| Visibility of derived data | Hidden from applications: queries still target the base table and ClickHouse decides whether to use the projection | Visible as a separate table that queries and dashboards must name explicitly |
| Backfill approach | Materialize the projection for existing parts as a planned operation, then confirm it covers historical data | Incremental views need a planned backfill of historical source rows; refreshable views rebuild their target on a schedule |
| Operational ownership | Owned by the table definition; changes are made on the base table and checked against query plans | Owned as a separate object with its own dependencies, refresh schedule or insert path, and monitoring |
| When neither is needed | Start with an ordering key that matches the most common filters, then test with EXPLAIN on representative queries | Start with the same ordering key work, and add a skip index only after measuring rows read on the filtered column |
How to test a projection before production
This sequence moves a candidate projection from a slow-query finding to a documented decision, so you know it speeds up the right queries, that ClickHouse actually uses it, and what it costs on writes and merges before production changes.
Capture the slow queries that justify a projection
Start from production evidence rather than a guess. Query system.query_log for the slow queries against the table, grouping by normalized query text and recording read rows, read bytes, memory and duration. Your data platform team or DBA runs this, and the output is a short list of queries with their filter columns, sort orders and aggregations. You know this step is done when each candidate query is tied to a measured cost, and when you have decided which queries a projection must serve and which should be fixed by a schema or query change instead.
Copy a representative table to pre-production
Create a pre-production cluster and copy the target table with enough real data and parts to reproduce the plan you saw in production. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, so copy the table definition, the ordering key and a representative time range, and keep the query set from the previous step. Confirm that row counts, partition layout and the number of parts are close to production before you trust any timing from this environment.
Add the candidate projection without touching production
On the copy, add the candidate projection with ALTER TABLE ... ADD PROJECTION, using either a different ordering for filters the base key does not serve or a precomputed aggregation for repeated GROUP BY queries. Keep one candidate per test run so its effect is isolated. The change applies to new parts immediately, so the check here is that the statement succeeds and that the projection appears in system.projections for the table.
Materialize the projection on existing parts
Existing parts do not contain the projection until you build it, so run ALTER TABLE ... MATERIALIZE PROJECTION for the partitions in scope. Watch system.mutations until the mutation completes and no failed parts remain. Record how long the materialization ran and how much disk it added. You know this step is done when the mutation shows as finished and the projection's size in system.projection_parts is reported for the sampled partitions.
Confirm with EXPLAIN that ClickHouse uses it
Run each query from the first step with EXPLAIN indexes = 1 and check that the plan names the projection and that read rows fall relative to the base table. Also check that unrelated queries still use the base table. A projection that is built but never chosen adds write cost without benefit, so if the plan does not change, return to the query design or the projection definition before continuing. Compare the rows read figures against the production baseline.
Measure insert and merge impact
Replay a representative insert workload against the copy, both with and without the projection, using the same batch sizes and concurrency as production. Compare insert latency, CPU, and the time parts take to merge in system.parts and system.merges. Projections are written on every insert and merged with the table, so this measurement is the cost side of the decision. You know this step is done when you have a written comparison that your operations team accepts for the expected write rate.
Document the rollback and the decision
Write down how to remove the projection with ALTER TABLE ... DROP PROJECTION, including whether the drop is safe during ingestion, which dashboards or queries depend on the projection, and the query plans that will change back. Test the rollback on the copy and confirm the base table answers the same queries correctly. Record the query gains, the disk and write costs, the migration plan for production, and whether a materialized view would be the better choice for the workload. Your change review is complete when this record is approved.
Trade-offs that change the decision
Projections and materialized views both trade storage and write work for faster reads, but they fail in different ways. The cards below cover the costs and checks that tend to decide which one fits a given query pattern.
Storage duplication
A projection stores a second copy of the rows in a different sort order or in precomputed aggregate form, so the table's disk footprint grows with every projection added. A materialized view also writes its own target table, which holds its own copy of the data. Before choosing either, measure the compressed size of the base table and estimate the added size for each candidate. Ask the team to confirm how the projection's size is reported in system tables on a copy of production data.
Insert and merge cost
Every insert into a table with a projection also writes the projection's data, and background merges rewrite both. A materialized view runs its query on each inserted block and writes the result to a target table, so its cost depends on the view's query and on how many views are chained. Test write throughput with the real insert batch sizes before committing, because projection and view costs both appear on the ingest path.
Hidden from direct queries
A projection is not a separate table that applications can name in queries. ClickHouse considers it automatically when a query matches, so analysts query the base table and get the benefit without a new object to learn. The trade-off is that users may not know which path ran. Plan documentation and dashboard owners around the base table name, and confirm which queries are expected to use the projection.
Planner choice needs verification
ClickHouse decides at query time whether to read a projection, and a query that looks like it should use one may not. Filters, column expressions and aggregate shapes all affect the decision. Use EXPLAIN on representative queries and compare rows read against the base table. A materialized view has no such planner decision: applications query the target table by name, which makes the expected path easier to confirm in code review.
Schema change coordination across replicas
Adding or dropping a projection or a materialized view changes the schema that every replica of a ReplicatedMergeTree table must agree on. Projections on existing data also need a materialization step to cover parts written before the change. Plan the change in a maintenance window or a staged rollout, check replication queues during the change, and confirm the materialization behavior for your ClickHouse version in the release notes before running it in production.
Operational load of extra target tables
A materialized view adds at least two objects to manage: the view itself and its target table, with a separate engine, TTL and retention policy. Backfills, view chains and changes to the source query all need owners and runbooks. Projections add fewer named objects but still need monitoring of their size and merge behavior. Ask who will own each derived object, how it is backed up, and how a failed backfill is detected and retried.
How replicas and backups treat derived data
Replication and backups protect different things, and neither one protects a projection or materialized view by design. ReplicatedMergeTree tables, coordinated by ClickHouse Keeper, keep each replica's data in step with its peers, and a recovered replica automatically syncs missing data from a healthy replica. A projection is stored with its base table's parts, so it follows that replication path. A materialized view is different: it is a trigger that writes into a separate target table, and that target table is replicated only if the team creates it as a replicated table. Before go-live, confirm the engine and replication status of every table involved, including the target tables of each view.
Replication alone does not protect against human error or corruption. A bad DROP, a faulty ALTER or a corrupted part is copied to every replica, so the damage spreads. The ClickHouse backup documentation covers this gap with BACKUP and RESTORE, which support full and incremental backups of tables, partitions or databases. Plan the backup scope so it includes the base tables and every target table the team relies on for queries. A projection comes along with its table, but a target table that is missing from the backup scope will be unrecoverable from the backup, and it may take a long backfill from source data to rebuild it. Check whether the source data is still available for replay, because that is the fallback when a target table cannot be restored.
A backup that has never been restored is not evidence that recovery works. The backup documentation warns that an untested backup will probably not restore properly when needed, and recommends regular restore tests on a spare cluster. Each test should restore the base tables and the target tables together, then compare row counts and key aggregates against production, and time how long the restore and any backfill take. Record the result, because that timing is the recovery time the business will actually get. Ask the team that owns the views to sign off on the restored results before the test is considered complete.
Security and operational considerations
Projections and materialized views both change what ClickHouse stores and how queries run, so they inherit the same access, upgrade and retention obligations as base tables. The cards below cover the controls that keep derived data safe to run in production.
Restrict the default user
A fresh ClickHouse installation starts with a default user that holds all permissions. Before any projection or materialized view is created, disable or restrict that account and create named users with roles. Check that application accounts can read the derived objects they need without being able to alter table definitions. Confirm the exact steps against the access control documentation for your version, since defaults and config layout can differ between releases.
Grant least-privilege on derived tables
A materialized view writes to a target table, and a projection is stored inside its parent table, so a reader of the derived data may see rows the base table would hide. Apply row policies and grants to the target table and to any aggregated output, not only to the raw source. Test each role with a query against the derived object, because a grant on the source alone does not cover it.
Control who changes table definitions
Adding or dropping a projection or a materialized view rewrites or backfills data and can change how every query on the table runs. Limit ALTER and CREATE rights to a small set of administrators, and route definition changes through reviewed SQL files kept in version control. Record who changed which object, since a silent projection change can shift query plans without any application change.
Track upgrade changelogs
Stable ClickHouse releases ship about monthly, and minor releases can include backward-incompatible changes. Projection and materialized view behavior is part of that surface, so read the changelog for each target version before upgrading and test the derived objects on a copy with representative data. Keep the list of supported versions in view, since the security policy names which lines still receive security updates.
Watch merges and insert latency
Every insert into a table with projections or materialized views also writes the derived data, and background merges must process each copy. Monitor merge backlog, the number of active parts, and insert latency after each new derived object goes live. A rising part count on a target table often shows that the derived write path is slower than the source insert rate, which is an early sign to rethink the design.
Align derived data with retention and erasure
A projection expires with its parent rows, but a materialized view target table keeps its own data and its own TTL. If a source row is deleted for erasure or retention, the aggregated copy may still hold the information. Confirm that TTL and deletion rules are set on every target table, and test an erasure request end to end, including the derived objects.
Questions teams ask before choosing projections or materialized views
Can our own team build projections or materialized views without a partner?
Yes, if your engineers already run self-managed ClickHouse and know MergeTree tables well. Projections are defined on an existing table with SQL, and materialized views are created with SQL as well, so the syntax is not the hard part. The effort lies in choosing which query shapes deserve a precomputed structure, measuring the insert cost each one adds, and proving the result on representative data before production. Teams new to ClickHouse usually gain more from an outside review of the design than from writing the DDL themselves. Ask whether your team has time to run a full test cycle and an ongoing review of query patterns.
What makes the effort larger or smaller for a projection or view project?
The main drivers are the number of distinct query shapes you need to serve, the size of the schema, the insert rate, and any compliance obligations that touch the data. Each additional query shape means another candidate projection or view to evaluate, and each one adds write work during inserts. A high insert rate makes the cost of extra structures visible sooner, so the design needs more measurement. Compliance needs add review steps for retention, access and audit evidence, especially when a view stores derived copies of personal or regulated data. Ask any provider to break the estimate down by these drivers rather than quoting a single figure.
How long does a pre-production test take for these changes?
The length depends mainly on how much data you can copy into the test environment and how many query shapes you need to verify. Small datasets can be tested quickly, while large event or telemetry tables need time for loading, for backfilling any materialized view, and for comparing query plans and row counts. The test has to run on data that resembles production, because a projection chosen on a small sample can behave differently at scale. Ask your team or a partner to identify the largest tables and the slowest queries first, since those set the critical path. We do not publish a fixed duration; the written scope sets it.
What does our team need to provide for this work?
The work needs three inputs from your side: representative data, query logs, and a spare cluster for testing. Representative data means a copy or sample that keeps the cardinality, skew and time range of production, ideally with any sensitive fields masked under your approved process. Query logs, such as the entries in system.query_log, show which filters, groupings and time windows matter most, so they drive the design more than assumptions do. A spare cluster lets you apply a projection or view, measure its effect, and test a restore without touching production. Confirm access rights, change windows and the owner who will sign off on the result before the work starts.
When should we choose a projection instead of a materialized view?
Choose a projection when you want ClickHouse to use an alternate ordering or a precomputed aggregate for the same source table, without changing how applications query it. A projection behaves like an additional hidden table that the query planner can select automatically at query time. Choose a materialized view when the precomputed result must feed a separate target table, be queried directly by name, or be shaped for a dashboard or downstream system. Views also suit transformations that projections cannot express. Both trade extra storage and insert work for faster reads, so the decision should follow the query log evidence.
What should we check before the change reaches production?
Check that the chosen structure is used by the queries you measured, that results match the original queries, and that insert throughput stays within your requirements. Use EXPLAIN to confirm a projection is selected, and compare row counts and aggregates between the old and new paths. Measure merge and storage growth, because extra structures consume disk space and background work. Confirm the rollback path, which is dropping the projection or view and rebuilding if needed, and make sure backups still cover the source tables. Document the decision so future schema changes do not silently duplicate what already exists.
What does an outside partner deliver for projections and materialized views?
A partner should deliver a written inventory of the query shapes and tables in scope, a recommendation for each candidate projection or view with its trade-offs, and the DDL to apply in your environment. A typical scope also covers a test plan on representative data, verification evidence, a rollback procedure, and a short handover that explains maintenance, such as what to do when source columns change. DeployOpen plans, implements and documents this work inside your own infrastructure, and most engagements are fixed-scope with deliverables agreed in writing before work starts. Ongoing query reviews are available as a retainer. Ask for the deliverable list and acceptance criteria in the quote.
Does the work cover compliance evidence for the changed tables?
DeployOpen can prepare systems, configuration and evidence for compliance assessments, including documentation of how derived tables are created, retained and access-controlled. It does not issue audit reports, attestations or certifications, which independent auditors and certification bodies provide. If a materialized view copies regulated data, the scope should cover where that copy lives, who can query it, how it is erased or expired, and how it is included in backups. Confirm with your compliance lead which controls and evidence your assessor expects before the design is fixed.
How DeployOpen scopes and delivers this work
DeployOpen plans and implements self-hosted ClickHouse inside your own infrastructure, whether that is your cloud accounts, data centers or restricted networks. For projections and materialized views, the work starts with a written scope that names the tables, the query patterns each change must serve, the target table engines, and the ingestion paths that will carry the extra write cost. The scope also records who owns the decision on each object, so a projection or view added for one dashboard does not quietly become a permanent cost on every insert and merge. Deliverables and timelines are agreed in writing before work begins, and a quote follows from that scope rather than from a list price.
Because a projection or a view changes how data is written and read, DeployOpen tests the change on a representative copy of your tables before anything reaches production. That copy should hold realistic volumes and the real query set taken from system.query_log, so the team can compare before and after plans with EXPLAIN, check rows read, and measure insert throughput and merge behavior. Your team should confirm which ClickHouse version it runs in production and read the changelog for that version before the test, since minor backward-incompatible changes can occur. Test results, the rollback steps and the monitoring that will watch the new object are written up as part of the handover.
Many teams find that projection and view decisions need revisiting as query patterns change, so DeployOpen also offers ongoing support as a retainer. Support can cover reviewing new dashboards for candidate projections or views, checking insert latency after schema changes, and planning upgrades so the behavior of these objects is verified on each release. Ask any partner, including DeployOpen, to state in the quote which of these activities are one-time and which recur, and who is responsible for each. DeployOpen prepares systems, controls and evidence for compliance work, but independent auditors and certification bodies issue any audit report or certification.
