ClickHouse Materialized Views: Patterns, Pitfalls, and Refreshable MVs

A production engineer's field guide to ClickHouse materialized views: the four patterns we deploy, the two we refuse to, AggregatingMergeTree mechanics, and how to use Refreshable MVs (CREATE MATERIALIZED VIEW ... REFRESH EVERY) without breaking ingestion.

ClickHouse materialized views look simple from the outside. You write a SELECT, attach it to a source table, and aggregated rows appear in a target table without you doing anything else. Underneath, the semantics differ enough from traditional SQL databases that the same pattern can be a 50x query speedup or a silent data corruption bug, depending on which engine you pick and how you wire the pieces together.

This is a field guide based on what we deploy in production for ClickHouse customers. There are four MV patterns we reach for repeatedly, and two we refuse to ship. Since Refreshable Materialized Views left experimental status in 24.10 (they first appeared in 23.12), the design space has split into incremental and refresh-on-schedule, and the right choice now depends almost entirely on how the source data behaves.

How ClickHouse Materialized Views Actually Work

A ClickHouse materialized view is an AFTER INSERT trigger attached to the leftmost table in its SELECT statement. It fires on every INSERT, processes only the data block that just arrived in memory, and writes the result to a target table as a new part. It does not read the source table from disk during normal operation, and it is not a snapshot you refresh on a schedule (Refreshable MVs, covered below, are a separate object type).

ClickHouse splits large INSERTs into blocks based on max_insert_block_size (default 1,048,576 rows). A 3-million-row INSERT arrives as three sequential blocks, the MV fires three times, and three separate parts land in the target. That chunking is mostly harmless. The real danger runs the other way: high-frequency tiny INSERTs. A client sending 100-row INSERTs fires every attached MV on every one of them, and each INSERT creates a new part in the source plus one per MV target - a fast path to the "Too many parts" error. If you cannot batch on the client side, turn on async_insert = 1 so the server buffers small inserts into larger blocks before the MVs ever fire, or put a buffering layer (Kafka, a Buffer table) in front.

Processing is synchronous: a slow MV SELECT pushes insert latency on the source table up directly, because the INSERT is not acknowledged until every attached MV has written its output. Multiple MVs on the same table run sequentially, but do not rely on the order - older versions ran them alphabetically by view name, while current versions run them in UUID order, which is effectively random. Setting parallel_view_processing = 1 runs them concurrently instead, which helps insert latency but multiplies CPU load during batch inserts - budget for that before enabling it on a loaded cluster.

One non-negotiable rule before any pattern: always use the TO <target_table> syntax. When you omit it, ClickHouse creates a hidden .inner.<uuid> table that gets destroyed with the view - drop the MV and you lose every materialized row. The explicit TO form lets the target persist independently, accept normal ALTER TABLE operations, and (if you want) receive writes from multiple MVs.

The Four Patterns We Deploy in Production

Each pattern below maps to a specific shape of source data and query. Mix them up and you either get wrong numbers or wreck ingestion throughput.

Pattern 1: SummingMergeTree for additive metrics

For pure sums and counts on append-only data, SummingMergeTree is the simplest target. Background merges combine numeric columns sharing the same ORDER BY key. Queries still need GROUP BY with sum() or FINAL, because un-merged parts coexist between merges.

CREATE TABLE daily_sales (
      day Date,
      product_id UInt32,
      revenue Float64,
      order_count UInt64
  ) ENGINE = SummingMergeTree()
  ORDER BY (day, product_id);
  
  CREATE MATERIALIZED VIEW daily_sales_mv TO daily_sales AS
  SELECT toDate(created_at) AS day, product_id,
         sum(price) AS revenue, count() AS order_count
  FROM sales
  GROUP BY day, product_id;
  

The ORDER BY columns of the target must match the GROUP BY columns in the MV SELECT, or background merges will not combine partial rows correctly and you will see duplicates. This is the single most common bug we find in customer MVs.

Note that "additive" covers more than plain sum() and count(). Conditional aggregates like sumIf(price, status = 'paid') and countIf(status = 'refunded') decompose into per-block partial sums that add up correctly across merges, so they belong in SummingMergeTree too. Do not reach for AggregatingMergeTree just because a metric has a filter on it.

Pattern 2: AggregatingMergeTree with State/Merge for non-additive aggregates

The moment you need uniq, quantile, avg, or anything that is not strictly additive, SummingMergeTree is wrong. Block-level processing is the reason: the MV fires per insert block, so a uniqExact(user_id) computed on two blocks produces two row-level cardinalities. Adding them double-counts users that appear in both. The errors compound with every insert.

AggregatingMergeTree stores intermediate aggregation state as binary blobs and merges them with the corresponding *Merge functions. The uniqState function, for example, stores a HyperLogLog sketch; merging two states unions the sketches and produces the correct cardinality.

CREATE TABLE monthly_stats (
      month Date,
      user_id UInt64,
      unique_products AggregateFunction(uniq, UInt32),
      avg_order_value AggregateFunction(avg, Float64)
  ) ENGINE = AggregatingMergeTree()
  ORDER BY (month, user_id);
  
  CREATE MATERIALIZED VIEW monthly_stats_mv TO monthly_stats AS
  SELECT toStartOfMonth(created_at) AS month, user_id,
         uniqState(product_id) AS unique_products,
         avgState(order_value) AS avg_order_value
  FROM orders
  GROUP BY month, user_id;
  
  -- Queries must use the matching *Merge function
  SELECT month, user_id,
         uniqMerge(unique_products) AS unique_products,
         avgMerge(avg_order_value) AS avg_order_value
  FROM monthly_stats GROUP BY month, user_id;
  

For aggregations whose intermediate state is just the value itself - min, max, sum, any, anyLast, groupBitOr - prefer SimpleAggregateFunction over AggregateFunction. It stores the raw value rather than a wrapped state, which means smaller storage, faster reads, and queries that do not need a *Merge call. Only avg, quantile, uniq, and similar non-trivial aggregates require the full AggregateFunction form.

-- Lighter-weight target for aggregates whose state IS the value
  CREATE TABLE session_stats (
      session_id UInt64,
      last_seen SimpleAggregateFunction(max, DateTime),
      page_views SimpleAggregateFunction(sum, UInt64)
  ) ENGINE = AggregatingMergeTree()
  ORDER BY session_id;
  

Pattern 3: Multi-MV fan-in to a single target

Multiple materialized views can write into the same target table. We use this to combine events from two or three source streams (orders, returns, refunds) into one aggregated table without duplicating storage. Each MV reads its own source and emits rows compatible with the target's AggregatingMergeTree ORDER BY. Background merges combine them as if they were inserts from a single pipeline.

We reach for this pattern when the source tables are sharded along different keys and you need a unified rollup. Failure mode to watch for: if one source has dramatically higher insert frequency than the others, it dominates merge activity in the target and can starve smaller MVs of merge attention.

Pattern 4: Refreshable Materialized Views for joins and snapshots

Refreshable Materialized Views (experimental in 23.12, production-ready since 24.10) run a full SELECT over the source data on a schedule and atomically swap the result into the target. The shape:

CREATE MATERIALIZED VIEW customer_360
  REFRESH EVERY 1 HOUR
  ENGINE = ReplacingMergeTree
  ORDER BY customer_id
  AS
  SELECT c.customer_id, c.tier,
         sum(o.amount) AS lifetime_value,
         max(o.created_at) AS last_order_at
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.tier;
  

The query runs end-to-end at each interval and the result replaces the previous version atomically, so readers never see a partial state. Variants worth knowing:

• REFRESH EVERY 1 DAY OFFSET 1 HOUR - schedule a daily refresh that runs one hour after midnight UTC.
• REFRESH EVERY 10 SECOND APPEND TO target_table - append rows instead of swapping the whole target. Useful for snapshot-over-time tables.
• DEPENDS ON other_mv - block this refresh until upstream refreshable MVs finish, building a small DAG.
• ALTER TABLE mv MODIFY REFRESH EVERY 30 SECOND - change cadence without rebuilding. Note it replaces all refresh parameters at once, including any DEPENDS ON clauses.

This is the only pattern that handles JOINs between two frequently-updated tables correctly. Incremental MVs only fire on inserts to the leftmost table, so a refreshed dimension on the right side never propagates. Refreshable MVs give up real-time freshness in exchange for that correctness, and they cost a full re-scan per interval - so the source needs to be small enough, or partition-prunable enough, that the refresh finishes well within the interval.

The Two Patterns We Refuse to Deploy

Anti-pattern 1: Cascading MVs without aggregate states

Chaining materialized views (raw -> mv1 -> mv2) is supported, but the failure mode catches teams repeatedly. Materialized views forward the inserted block, not the merged state of the intermediate target. If mv1 writes into a SummingMergeTree or ReplacingMergeTree, the downstream mv2 sees the pre-merge rows, not the deduplicated or summed final state. The result is a downstream aggregate that double-counts or includes stale versions.

The only safe way to cascade is with AggregatingMergeTree plus state functions all the way down: mv1 emits *State columns, the intermediate target is AggregatingMergeTree, and mv2 reads those state columns and either re-aggregates them with *Merge or carries them forward as states. Most teams who try cascading discover this after a week of mismatched dashboards. We default to flat MVs unless cascading is the only way to express the transformation, and then we set up reconciliation queries that catch divergence early.

Anti-pattern 2: Fan-out MVs that expand row count

The fan-out trap: a GROUP BY that produces more rows than it consumes. We saw a real production case where a 20 GB raw log table generated a 190 GB MV target because the GROUP BY (user_id, attribute_name) exploded each source row into ten attribute rows. That is not a materialized view - that is a 10x storage tax disguised as one.

The check before shipping any MV: compare expected output cardinality against input row count. If the ratio is greater than 1, you are paying for storage twice and querying the raw table is almost always faster. The exception is when the expanded form unlocks a fundamentally different access pattern (e.g. point lookups on attribute_name), in which case a Projection is usually the better tool because it stays inside one table.

Related anti-patterns we treat as red flags during review:

• Ten or more MVs on the same source table. Each adds synchronous insert cost; chains of five or more can cut insert throughput by 80% or more on small batches.
• Using POPULATE. It cannot be combined with the TO syntax and creates a window where rows inserted during the populate are silently dropped.
• MVs over ReplacingMergeTree or CollapsingMergeTree source tables expecting deletes/updates to propagate. They will not - MVs trigger on INSERT only.

Three Rules Before You Ship an MV

Keep chains short and hang them off target tables. When you do chain MVs, each layer multiplies write amplification: every INSERT into the source now produces parts in the source, the intermediate target, and every downstream target, and the whole chain executes synchronously inside the original INSERT. Three layers is where we draw the line; beyond that, insert latency and part counts grow faster than any modeling benefit. Attach each downstream MV to the explicit intermediate TO target table of the previous layer, not to the raw source - stacking several MVs directly on the source table just re-reads the same blocks and re-pays the synchronous cost per view.

If you only need a different sort order, use a Projection. A surprising number of MVs exist only to serve the same rows under a different ORDER BY or primary key - no aggregation, no cross-table logic. That job belongs to projections: they live inside the source table, get maintained by the same merges, stay consistent with the base data automatically, and the optimizer picks them without query changes. Reserve MVs for cases where the output is genuinely a different dataset - aggregated, filtered, or reshaped.

Merges are asynchronous, so naive reads are wrong reads. SummingMergeTree and AggregatingMergeTree collapse rows in background merges, on their own schedule. At any given moment the target table holds unmerged parts, which means a bare SELECT revenue FROM daily_sales WHERE day = today() can return several partial rows per key. Every query against these targets must either aggregate explicitly - GROUP BY with sum() on a SummingMergeTree, GROUP BY with the *Merge wrappers on an AggregatingMergeTree - or use the FINAL modifier and pay its merge-at-read cost. Dashboards that skip this look right in testing (one part, nothing to merge) and drift in production.

Refreshable MVs: Operations and Monitoring

Refreshable MVs are an operational object, not a fire-and-forget pipeline. They can fail, run long, or fall behind, and you need observability for all three.

ClickHouse exposes system.view_refreshes for this. Useful columns:

• status - current state (Scheduled, Running, Disabled).
• last_success_time - when the most recent successful refresh started.
• last_refresh_time - when the most recent attempt finished or started.
• last_success_duration_ms - how long the last good refresh took.
• next_refresh_time - when the next refresh is scheduled.
• exception - error from the last failed refresh.

-- Find MVs that are behind schedule or failing
  SELECT database, view, status,
         last_success_time,
         next_refresh_time,
         last_success_duration_ms,
         exception
  FROM system.view_refreshes
  WHERE next_refresh_time < now() - INTERVAL 5 MINUTE
     OR exception != ''
  ORDER BY next_refresh_time;
  

We wire this into the same alerting that watches system.merges and system.replication_queue. Two specific alerts pay for themselves: refresh duration approaching the refresh interval (the MV is about to start overlapping itself), and any non-empty exception lasting more than two consecutive cycles.

For incremental (non-refreshable) MVs, "lag" is a different question. There is no scheduled refresh. The relevant signals are insert latency on the source (visible in system.query_log), part counts in the target (system.parts), and merge backlog (system.merges). If part count in an MV target keeps climbing, merges are not keeping up - usually because the MV is creating more, smaller parts than the merge thread budget can collapse.

Concern	Incremental MV	Refreshable MV
Trigger	INSERT on leftmost source	Scheduler interval
Freshness	Per-block, real-time	Per refresh interval
Handles JOINs to dimensions	No (only leftmost table)	Yes
Handles updates/deletes in source	No	Yes (re-runs full query)
Insert-time cost	Synchronous, on every INSERT	None
Refresh-time cost	None (continuous)	Full SELECT on schedule
Best for	High-throughput append-only rollups	Joined dashboards, snapshots, dbt-style transforms
Failure visibility	system.merges, part counts	system.view_refreshes

Operational Costs and Migration Discipline

Write amplification compounds with scale. Altinity's benchmarks show a single MV cuts insert throughput on small batches by roughly half, five chained MVs by around 80%, and ten by close to 90%. Larger insert batches amortize the overhead - a single MV on million-row batches costs in the 30-40% range. Each INSERT creates 1 + N new parts (one for the source, one per MV target), and ClickHouse will throttle inserts with the "Too many parts" error when active parts in a partition exceed the configured threshold (typically a few hundred).

Schema migrations are operationally expensive, but not every change requires the full teardown. If you only need to change the SELECT logic of an MV created with the TO syntax - a new filter, a fixed expression, an extra column that already exists in the target - ALTER TABLE mv MODIFY QUERY SELECT ... swaps the query in place without interrupting ingestion. It only affects rows inserted after the ALTER, and it does not touch the target table's structure, so anything already materialized stays as it was.

Changing the target table itself - adding or dropping a column the MV writes, switching engines, changing the ORDER BY - still requires the full sequence: drop MV, alter source, alter target, recreate MV, backfill any rows inserted during the gap. Trying to drop a column an MV references fails with CANNOT_DROP_COLUMN.

Backfilling is its own discipline. Skip POPULATE. Create the MV first so the trigger is live for new data, then INSERT historical data into the target in partition-sized chunks:

INSERT INTO daily_sales
  SELECT toDate(created_at) AS day, product_id,
         sum(price) AS revenue, count() AS order_count
  FROM sales
  WHERE toYYYYMM(created_at) = 202604
  GROUP BY day, product_id;
  

For very large tables, stage backfills through a separate target table and use ATTACH PARTITION to swap completed partitions atomically. Failed chunks become a single-partition retry rather than a full restart.

FAQ

What is a ClickHouse materialized view in one sentence? A ClickHouse materialized view is an AFTER INSERT trigger that runs a SELECT on each incoming data block from a source table and writes the transformed rows into a separate target table; it is not a query cache or a scheduled snapshot.

When should I use a refreshable materialized view instead of an incremental one? Use a refreshable MV when you need JOINs against frequently-updated dimension tables, when source data has updates or deletes that must propagate, or when your query is too complex to express as a per-block transformation. Use an incremental MV for high-throughput, append-only rollups where freshness matters in seconds.

Why does my SummingMergeTree MV produce duplicate rows? The target's ORDER BY does not match the MV's GROUP BY. ClickHouse only collapses rows whose ORDER BY tuple is identical, so any divergence leaves both rows in place until query time.

Can I use uniqExact() in a materialized view target column? Not as a plain integer, no. Block-level processing means each insert block computes its own cardinality; storing the integer and summing later double-counts. Use uniqState or uniqExactState into an AggregateFunction column, then uniqMerge at query time.

How do I monitor refreshable MV staleness? Query system.view_refreshes and alert on next_refresh_time < now() - threshold, on non-empty exception, and on last_success_duration_ms approaching the refresh interval. The first signals lateness, the second signals failure, the third predicts an imminent overlap.

Are cascading materialized views safe? Only with AggregatingMergeTree and aggregate state functions through every layer. Chaining over SummingMergeTree, ReplacingMergeTree, or CollapsingMergeTree will silently produce wrong results because MVs forward raw insert blocks rather than merged state.

---

The sweet spot for incremental MVs is high-volume append-only data where aggregation compresses rows by at least an order of magnitude and the read pattern is well-known. Refreshable MVs cover everything that involves joined or mutable data, at the cost of refresh-interval staleness. Mix them deliberately, monitor both with system.view_refreshes and part counts, and the rest of the engineering follows.

Key points:

• Use TO <target_table> always; the implicit inner table dies with the view.
• Match target ORDER BY to MV GROUP BY exactly, or merges produce duplicates.
• Use AggregateFunction + State/Merge for non-additive aggregates; use SimpleAggregateFunction for min/max/sum/any to save space and reads. sumIf/countIf are still additive and fine in SummingMergeTree.
• Refreshable MVs (REFRESH EVERY ...) handle joins and mutable sources at the cost of staleness; monitor via system.view_refreshes.
• Cascading and fan-out MVs are anti-patterns unless you fully understand state forwarding and have measured row-count expansion.
• Backfill manually in partition-sized chunks; never use POPULATE.
• Small frequent INSERTs amplify through every MV; batch on the client or enable async_insert = 1.
• ALTER TABLE mv MODIFY QUERY changes a TO-based MV's SELECT in place (new rows only); structural changes still need the drop/recreate sequence.
• Each MV adds synchronous insert cost; chains of five or more can cut throughput by 80%+ on small batches.

Materialized views reward getting the details right and punish guessing. If you would rather not learn that in production, our ClickHouse consulting team can review an existing design or build the pipeline alongside yours.