ClickHouse materialized views: speed up queries

Speeding up ClickHouse queries with materialized views: when precomputation pays off

Incremental materialized views in ClickHouse compute aggregates as blocks of data are inserted, writing the results into a separate target table so that dashboards and APIs read a small precomputed result instead of rescanning the raw rows on every request. Teams need them when the same aggregation runs repeatedly over a large, append-heavy event, log or telemetry table and the cost of scanning the raw data has become the bottleneck, for example a per-minute error count per service that a dashboard refreshes all day. If the questions change often, or the queries are ad hoc, a raw table with a well-chosen ordering key usually remains the simpler answer. Before committing, list the five or ten queries that drive the expense, confirm that each can be expressed as an aggregate over new rows, and check how often each one runs.

The decision is easier when the alternatives are placed side by side. A projection is a hidden, automatically chosen alternate ordering or pre-aggregation stored with the base table, so it needs no application changes, but it covers fewer query shapes than a dedicated target table. A refreshable materialized view reruns a full query on a schedule, which suits results that cannot be built incrementally, such as joins that depend on later-arriving rows. An incremental view only sees rows inserted after it was created, so existing data must be backfilled separately and confirmed against the source before anyone trusts the numbers. Review the documentation on ClickHouse production operations and test the chosen design on a copy with representative data, since the view's correctness depends on the insert path your pipelines actually use.

The cost of precomputation is paid on every insert, not on every query. Each insert into the source table also runs the view's query and writes into the target, so ingestion throughput, merge work and storage all grow with the number of views and the complexity of their queries, and chained views multiply this effect. Teams should ask who will own the view definitions, how a change to a view is rolled out without losing rows, and how they will detect a view that has stopped processing. Confirm these answers against the ClickHouse version you run, because view behavior and supported syntax have changed across releases, and ask your partner to include a backfill and an insert-load test in the written scope.

How an incremental materialized view writes to its target table

An incremental materialized view in ClickHouse is a trigger on insert, not a stored query that runs on read. Four parts work together: the source table, the SELECT the view runs on each insert block, the target table that holds results, and the merge behavior of the target engine.

The source table receives the raw inserts

The source table is where applications write raw events, logs or metrics. Each insert block sent to it triggers the materialized view, so the source remains the system of record for the full detail. Check which inserts reach the source, since rows written directly to the target or sent through a different table will not update the aggregate. Confirm that every ingestion path, including batch loads and backfills, writes through the source table.

The view SELECT runs per insert block

The SELECT in the view definition runs against each block of rows as it is inserted, not against the whole table. Aggregations are therefore computed on the new block only, and a GROUP BY inside the view produces partial results per block. Keep the SELECT deterministic and cheap, because its cost is added to every insert. Joins against large tables inside this SELECT are a common source of slow ingestion.

The target engine decides how partials combine

The target table's engine determines what happens to partial results. SummingMergeTree adds numeric columns with the same sort key, which suits simple counters and sums. AggregatingMergeTree stores aggregate states, so functions such as uniq or quantiles can be combined correctly across blocks. Choose the engine before the first insert, because changing it later means rebuilding the target.

Aggregate states use -State and -Merge

With AggregatingMergeTree, the view stores intermediate values written with the -State combinator, such as uniqState or sumState. Queries read the final value with the matching -Merge combinator, for example uniqMerge over the state column. Forgetting -Merge at query time returns binary state values rather than numbers, so each dashboard query needs review. Document the pairing for every aggregate column in the target.

Partial rows merge later in the background

ClickHouse does not update the target row when a new block arrives. It writes a new row with the partial result, and background merges later combine rows that share the same sort key. Until a merge runs, a query can see several rows for one group, so queries must still aggregate with GROUP BY and the -Merge functions. Ask how often merges run on your volume before promising freshness to dashboard users.

Which target table engine fits which aggregate

Counts, sums and totals that can be added together without losing meaning are the simplest case for an incremental materialized view. A SummingMergeTree target table keeps one row per sorting key and, during background merges, adds the numeric columns of rows that share that key. Because the arithmetic is simple and additive, the stored table stays small and dashboards can read it directly with a plain aggregation. Merges are asynchronous, so until ClickHouse merges the parts, several rows can exist for the same key. The query must still aggregate, for example with sum() grouped by the sorting key, so duplicate partial rows are combined correctly.

Sorting key choice: columns in the ORDER BY define which rows collapse together, so include every dimension a dashboard groups by and nothing extra.
Non-summed columns: columns outside the key and not summed take an arbitrary value from the merged rows, so avoid storing values that must be exact there.
Averages: store a sum and a count separately and divide at query time, since an average cannot be merged directly.
Merge timing: collapsed totals appear only after merges run, so reads must always re-aggregate.

Before relying on the totals, compare the view output with a direct aggregation over the source table for a sample period.

Distinct counts, unique users and percentiles cannot be merged from final numbers. A uniq result for two partitions cannot be added to give the uniq result for both. The AggregatingMergeTree engine solves this by storing intermediate aggregation states, produced by functions such as uniqState or quantilesState, in columns typed AggregateFunction. The materialized view writes these states at insert time, and the target engine merges them during background merges without finalizing them. Reads must then call the matching -Merge function, such as uniqMerge or quantilesMerge, to produce the final value. Reading the raw state column returns binary data, not a number.

State and merge pairs: each state function needs its matching merge function in every query that reads the table.
Approximation: uniq and quantile functions are approximate, so state the accuracy expectation to dashboard owners.
Storage size: state columns are larger than plain counters, so check the size of the target table after a representative load.
Exact distinct counts: if exact results are required, confirm with the data owner whether the approximate state is acceptable, since exact versions cost more.
Schema changes: changing the state function later requires rebuilding the target table and backfilling.

Verify that the merged results match a direct query over a copy of the source data for the same period.

Not every materialized view aggregates. A view can also copy rows unchanged, or keep only rows that match a filter, into a plain MergeTree table with a different sorting key. This is useful when queries filter on a column that the source table does not order by, or when a team needs a narrower table that holds only one event type. Plain MergeTree does not merge or combine rows, so every insert appears exactly as written. The cost is storage: the copy duplicates the data it keeps, so the view needs a retention plan of its own. Filtering at insert time also means the rule is fixed once the view exists, so changing the filter later means rebuilding the target.

Filter logic: keep the WHERE clause deterministic and simple, because it runs on every inserted block.
Column selection: copy only the columns queries need to limit storage.
Different sorting key: choose the key for the filtered queries, which may differ from the source table.
Retention: set a TTL on the copy independently, since it may need a different lifetime from the source.
Duplicates: a replayed insert produces a duplicate row, so decide whether ReplacingMergeTree is needed instead.

Check row counts against the source for the filter condition before retiring any query that currently reads the original table.

All three engines write partial results and merge them later, so the stored table can differ from the final answer at any given moment. Merges happen in the background, on a schedule ClickHouse controls, and the timing depends on part sizes and load. A newly inserted block may sit as a separate part for some time. This means an aggregate target is always eventually correct, but not necessarily correct on the next query. Query-time merge functions and re-aggregation are what keep results right during that window. Teams that expect instant consistency should measure the lag under their real ingestion rate and tell dashboard users what freshness to expect.

Part count: many small inserts create many parts, which slows merges and reads, so batch inserts where the pipeline allows.
Lag measurement: compare the view output with a direct aggregation during peak ingestion to see the gap.
Forced merges: OPTIMIZE TABLE ... FINAL is expensive on large tables, so do not schedule it as routine maintenance.
FINAL reads: the FINAL modifier merges at read time and adds cost, so use it only where the result requires it.
Engine mix: a single dashboard may need more than one target, so test each one separately.

Confirm the expected freshness with the business owner before publishing a dashboard built on these tables.

Setting up a materialized view on a self-managed cluster

This sequence turns an agreed aggregate into an incremental materialized view that writes to its own target table, and proves the results match raw data before anything reaches production. Each step ends with a check so the view is only deployed once its totals and insert behavior are verified.

Agree the aggregate and grain
Before any DDL is written, the engineer building the view meets the dashboard owners and writes down the exact metrics, the grouping dimensions (the grain, such as one row per hour per service), the filters and the time zone used for bucketing. The agreed definition is recorded alongside the query the dashboard runs today. This step is done when both teams sign off on a single written definition and on the raw-table query that will serve as the reference answer. Confirm early whether any metric is non-additive, such as a distinct count, because that changes the target column types and the aggregate functions required.
Create the target table
The platform engineer creates the target table on each replica using the engine that matches the aggregate, typically AggregatingMergeTree for states such as sums, counts and quantile states, or SummingMergeTree for simple additive sums. The ORDER BY key is set to the grouping dimensions and time bucket that the dashboards filter on, and the table is created with ON CLUSTER or the equivalent replicated DDL so every replica has the same schema. Done means the table exists on every replica with the same definition, which you can confirm by comparing the output of SHOW CREATE TABLE across nodes.
Create the view with TO target
The engineer creates the materialized view with a TO clause that points at the target table, and the SELECT applies the aggregate functions in their -State or -Merge form as the target engine requires. Using TO keeps the target table as an explicit, separately managed object, which simplifies backfills, schema changes and dropping the view without losing data. The view is created on the staging cluster first. Done means the view exists, it references the correct target, and a small test insert produces the expected rows in the target table.
Test on a copy with representative data
The team loads a copy of recent production data, including the skewed keys, late-arriving events and peak-hour volumes that the real workload produces, into a non-production cluster that runs the same ClickHouse version. Inserts are replayed through the source table so the view is exercised exactly as it will be in production. Done means the replay completes without errors in the server logs, and the team has recorded row counts, insert timings and the size of the target table for comparison later. Confirm the version matches production, since the ClickHouse production FAQ recommends a pre-production environment with representative data and queries.
Reconcile results against a raw-table query
The engineer runs the reference query from step one against the raw table and the same query against the target table, using the matching -Merge functions on the aggregate columns. The totals, counts per group and a sample of individual buckets must match exactly for additive metrics, and any approximate metric is compared within the tolerance the dashboard owners accepted. Mismatches are investigated before moving on, usually caused by time zone bucketing, filters missing from the view, or duplicate inserts. Done means the reconciliation query output is saved and signed off by the dashboard owners.
Measure insert cost before deploying
The team measures how much the view adds to each insert by comparing insert latency and CPU usage on the staging cluster with and without the view attached, using the replayed workload. Insert latency, merge activity and the number of parts created are watched during the replay, since each insert now runs the view's SELECT as well. Done means the team has a documented insert latency baseline and has confirmed that it fits the ingestion window and the agreed service levels. If the cost is too high, the options are a simpler aggregate, a longer bucket, or moving the work to a scheduled refresh.
Deploy and monitor the view
The view is created in production during a planned change window, ideally after the target table has been populated by a backfill of historical data if the dashboards need it. The team then monitors insert latency, error counts in the server logs and the row counts in the target against the source for the first days of operation. Dashboards are switched to the target table only after the reconciliation query passes against live data. Done means the production comparison of totals matches, insert latency stays within the baseline from the staging test, and the change record names the owner of the view.

Backfilling history and chaining views without double counting

An incremental materialized view only processes rows inserted after it is created, so the history already in the source table never reaches the target unless you load it. The backfill is a separate, explicit step: an INSERT INTO target SELECT ... FROM source that uses the same aggregate expressions as the view. Run it one partition at a time in controlled windows, starting with the most recent partitions that dashboards read, and check row counts and aggregate totals for each window against the source before moving on. Keep the backfill off peak hours, because it competes with live ingestion for CPU, memory and merges. Confirm the batch size and window length on a copy of your data first, since the right values depend on your hardware and table layout.

The main risk is double counting. If the view is created before the backfill, every row inserted during the backfill is counted twice: once by the view on insert and once by the backfill's SELECT, which reads the source as it stands. The safer order is to fix a cutover point, load history only for partitions that closed before it, and let the view handle everything after it. For partitions that are still receiving inserts, either pause the writers or exclude that partition from the backfill. Aggregating target tables merge rows rather than reject duplicates, so a second load of the same window is not visible in a row count and shows up only as inflated sums. Reconcile totals against the source after each run, and record which partitions have been backfilled so a retry does not repeat them.

Chained views write into a target that is itself the source of another view, so one insert triggers several rounds of work. Each link adds a parse, transform and write for every new block, and the cost compounds with each stage, so keep chains short and check the insert rate with the chain in place. A backfill has the same multiplier: loading the first target can fire downstream views unless the backfill runs in an order that accounts for them, and loading a middle target directly can skip the work its upstream view should have done. Document the dependency order for each chain, load the stages in that order, and test one full chain on a non-production cluster before running it on production data. Confirm with your own tests how much each stage adds to insert latency at your expected volume.

Configuration choices and when each fits

This table compares the target engines, view forms and alternatives you can choose when precomputing aggregates in ClickHouse. Read each row against your query pattern and your insert rate, since the insert cost column is what the view adds to every write. The main takeaway is that the target engine decides how much merge logic your queries must carry, and that a view with a clear target table is easier to test, backfill and retire than a chain of them.

Option	Best fit	Cost to the insert path	What to confirm
SummingMergeTree target	Additive counters and sums where each query needs only totals per key, such as request counts or bytes per host and hour	Each insert block runs the view's SELECT; merges sum rows with matching sort keys, so the work is small per row	That every non-key column is additive, and that queries still wrap sums in GROUP BY because merges are not yet complete
AggregatingMergeTree target	Distinct counts, averages, quantiles and other states that cannot be summed directly, stored as aggregate states and finished at query time	Higher per-insert cost because the view computes aggregate states; state columns are larger than plain numbers	That dashboards read with the matching -Merge combinators, and that the state column types match the view definition exactly
Plain MergeTree target	Pre-filtered or reshaped copies of raw rows, such as a narrower sort order for one query family, where no aggregation happens at insert	Lowest transformation cost, but the table duplicates raw data volume, so storage and merge work grow with the copy	Whether the copy is needed at all, compared with a projection on the source table
View with explicit TO target	Production views where the target table is created and owned separately, so schema, TTL and replication settings are visible and reviewable	Same SELECT cost as the implicit form; the target table's engine, replication and retention drive most of the operational cost	That the target table exists on every replica and that its name and columns are stable across upgrades
View with implicit inner target	Quick experiments and small datasets where ClickHouse creates a hidden inner table for the view	Same insert transformation cost, but the inner table is harder to alter, back up and reason about	Whether the inner table name and layout are recorded in your backup and migration runbooks
Single view over the source	One aggregation feeding one query family, with a single source table and clear ownership	One transformation per insert block; cost grows with the number of views attached to the same source	Which dashboards depend on the target, and whether a source schema change breaks the view
Chained views	Multi-level rollups, such as raw events to hourly to daily, where each level reads a smaller table	Each level adds its own insert work, so a three-level chain multiplies transformation on every write	That each level's grain is a true rollup of the level below, and that an error at one level is visible before the next runs
Projection on the source table	Alternate sort orders or simple pre-aggregations that ClickHouse picks automatically, with no separate table to query	Work is done during merges and inserts on the source table, with extra storage for the projection data	That the optimizer chooses the projection in EXPLAIN output for your real queries; see the sibling topic on projections versus materialized views
Refreshable materialized view	Results that cannot be computed incrementally, such as joins across time windows or full-table recalculation on a schedule	No insert-path cost; the cost moves to scheduled refresh queries that read the source tables	Refresh timing relative to peak hours, and whether readers accept results that are only as current as the last refresh; see the sibling topic on refreshable views

What materialized views add to insert cost and storage

Every incremental materialized view adds work to the insert path, so the number of views and the cost of their SELECT statements set the write budget before any query is faster. ClickHouse runs each view's SELECT on every block of rows inserted into the source table. A view with joins, subqueries or heavy aggregation costs more CPU and memory per block, and that cost lands on the insert itself. Stacking several views on one source table multiplies the effect, because each one repeats the transform. Before adding a view, measure insert latency and CPU on a copy of production data with the same batch sizes your ingestion agents use, and record the baseline so the change can be judged against it.

Each view also has its own target table, and that table stores data for as long as the source does. Aggregated targets are usually smaller than raw data, but a view that groups by high-cardinality keys can approach the size of the source. Target tables that use AggregatingMergeTree hold partial aggregate states rather than final values. Those states are merged in the background, so row counts and disk use can fluctuate between merges. Plan storage for the target as its own table, include it in backup scope and retention rules, and confirm with your team how far merges have progressed before you read exact counts from it. Queries should finish the aggregation with the matching -Merge functions.

Monitor failures on the insert path as well as query results. An incremental materialized view runs inside the insert, so an error in the view's SELECT, a missing column in the target or a type mismatch can make the source insert fail. Your ingestion job then retries or drops data depending on how it was written, and the source table can fall behind without an obvious alert. Watch insert error counts and query logs for exceptions raised by view definitions, and alert on ingestion lag at the source. Test every view change with malformed and late-arriving rows in a pre-production environment, and confirm the rollback steps for a view you may need to drop under load.

Security and operational risks to control

Materialized views add a second layer of tables, grants and logic on top of the source data. The risks below sit at that layer: who can change the pipeline, what happens to data kept on targets, and what breaks when schemas or versions change. Each one is easy to miss at build time and expensive to find in production.

Who can create and write views

An incremental materialized view is a write path: it runs on every insert into its source table and writes to its target. Anyone who can create a view or insert into the target can change what the aggregates contain. Grant CREATE VIEW, INSERT on the target and SELECT on the source as separate rights to named roles, rather than to the default user, which starts with all permissions. Review who holds these grants with the access control documentation.

Target table grants and row policies

Aggregated targets often hold data that is less detailed than the source, but can still expose sensitive dimensions such as customer or account identifiers. Grant SELECT on each target table on its own, so a dashboard role can read rollups without reading raw events. Check whether row policies on the source also apply to the target, because a view writes data that is not filtered by policies applied at query time on the source.

Retention on targets versus sources

A TTL on the source table does not delete rows already aggregated into a target. Targets built with AggregatingMergeTree keep their state until their own TTL or a manual drop removes it. Decide retention for each table separately, write it down, and test it: if a deletion request must remove a person's events, confirm which targets also hold derived values for that person.

Dropping or renaming source columns

A view's SELECT is stored as text and is bound to the source columns it names. Dropping or renaming a column on the source can make inserts fail, or stop the view from populating the target without an obvious error at the source. Before any schema change, list dependent views, change them in the same release, and insert test rows to confirm the target still fills.

Schema changes to target tables

Changing an AggregatingMergeTree target's state columns, or its ORDER BY key, is not a simple in-place edit when the view keeps writing. Plan a new target, backfill it, switch the reads, then retire the old one. Keep the view definition and the target schema in version control so the pair can be reviewed and reproduced in a test environment.

Function behavior across upgrades

A view's aggregation functions run inside the ClickHouse version that performs the insert. Major upgrades can change function behavior, defaults or the analyzer, so an aggregate may return different values after an upgrade. Compare results from a pre-production copy on the target release, and read the changelog before upgrading, since minor backward-incompatible changes can occur.

Questions teams ask before building materialized views

How much effort does a first materialized view take?

The effort depends on how many views the workload needs, how many source tables feed them, and how well the query patterns are understood before the build starts. A single incremental view over one well-defined source table is the smallest unit of work. Effort rises with each additional aggregate, each chained view, and each dashboard query that has to be rewritten to read from the target table. Confirm with any partner which of these items are inside the written scope, because the deliverables and the timeline are agreed in writing before work starts.

Can our team build materialized views without outside help?

Yes, a team that already runs ClickHouse and understands MergeTree engines can build a first view, especially when the aggregate is simple. The harder parts are choosing the target engine, such as AggregatingMergeTree with aggregate state functions, writing the correct merge queries, and proving that results match the source. Teams that have not operated a replicated cluster often underestimate the work of testing inserts under load. An outside partner is most useful for the design review, the backfill plan and the verification suite, which are the steps where mistakes are expensive to undo.

How do we plan the timing of a backfill?

A materialized view only processes rows inserted after it is created, so existing history must be loaded separately. The plan has to cover the size of the historical data, the insert rate during the load, and whether the backfill competes with dashboards and ingestion for CPU, memory and disk. Teams usually run the backfill partition by partition, check each batch against the source, and keep the live view attached so new inserts are not lost. Ask for the sequence, the rollback point and the window in which heavy load is allowed.

What does the work need from our team?

Three inputs drive most of the early work. The first is representative data samples, including edge cases such as late-arriving events, nulls and duplicates. The second is the list of dashboard and API queries the view must serve, with their filters, groupings and expected freshness. The third is a pre-production cluster with enough capacity to hold a realistic copy of the data, because the ClickHouse production guidance recommends testing with representative data and queries before changes reach production. Also plan for a named owner who can approve the schema and the cutover.

When should we choose projections or refreshable views instead?

Choose a projection when the goal is a different sort order or a simple pre-aggregation that ClickHouse should select automatically for matching queries on the same table. Choose a refreshable materialized view when the query cannot be expressed incrementally, such as one that needs a full recompute, a complex join across tables that change independently, or a result that can tolerate a scheduled refresh delay. Incremental materialized views fit best when each insert produces a predictable aggregate and freshness must follow inserts closely. The right choice depends on your query list and freshness requirement, so test each candidate against the same workload.

How do we verify that view totals match the source?

Verification compares aggregates from the view against the same aggregates computed directly from the source table, over the same time ranges and keys. The check should run after the backfill, after a period of live inserts, and after any replica recovery, since replication keeps replicas consistent but does not catch logic errors in the view. Include counts of late or duplicate events, because an insert that is retried can change totals if the design does not account for it. Ask for the reconciliation queries, the tolerance for differences and the schedule on which they will run in production.

What does a materialized view add to insert cost?

Every insert into the source table also triggers the view's query and writes to the target table, so insert time and background merge work increase. The cost grows with the number of views, the complexity of each query and the volume of inserts. Aggregate state columns can also take more space than plain values. Ask for a measurement of insert latency and merge load on the pre-production cluster, using a load pattern that matches your agents or pipelines, before deciding how many views to run in production.

How DeployOpen scopes and delivers ClickHouse materialized views

DeployOpen treats incremental materialized views as a data model change with insert-time cost, not a quick query fix. We start from the dashboards and queries that need to be faster, then design the target tables, backfill, test and document each view inside the ClickHouse cluster you run.

How we scope

Inventory the slow dashboard and API queries, their filters, groupings and freshness needs before choosing any view design.
Agree in writing which source tables, target engines, backfill windows and query paths the materialized view work covers.
Estimate insert-time overhead on your real ingestion rate and confirm it with your team before any build starts.

How we build

Design target tables, such as AggregatingMergeTree, with aggregate state columns that match the queries they must serve.
Build and chain views in a pre-production environment loaded with representative data, then compare results with the source queries.
Run backfills in controlled batches and verify row counts and aggregate totals against the source tables.

How we hand over

Deliver view definitions, backfill runbooks and query rewrites as version-controlled files with a clear owner for each view.
Document the insert cost, merge behavior and the steps to drop, recreate or rebuild a view safely.
Walk your engineers through testing a change to a view on a copy before it reaches production.

How we support

Ongoing support, upgrades and continuous testing are available as a retainer after handover.
Plan review of each ClickHouse upgrade against materialized view behavior, using the changelog before moving versions.
Confirm with your team how backups and restore tests cover the target tables, since replication alone does not protect against human error.

Speeding up ClickHouse queries with materialized views