obsessiondb/clickhouse-skills
Overview
This skill provides 10+ proven patterns for ClickHouse Materialized Views (MVs) to build real-time aggregation, transformation, and query-optimization pipelines. It highlights which MergeTree engines to use—SummingMergeTree, AggregatingMergeTree, ReplacingMergeTree—and warns about common pitfalls like missing backfills and wrong ORDER BY choices. Practical examples and troubleshooting guidance help you design safe, performant MVs.
How this skill works
Materialized Views run the MV SELECT on each INSERT batch into the source table and write results into a target table; they do not read historical data. Use -State functions in MVs to store intermediate aggregate states and -Merge functions at query time to complete aggregation for AggregatingMergeTree. Choose SummingMergeTree for additive counters and AggregatingMergeTree for non-additive aggregates like uniq or quantiles.
When to use it
- Create real-time counters and pre-aggregations to speed heavy queries
- Parse or enrich incoming raw data for downstream consumers
- Maintain latest-per-key state (last seen, last action) with ReplacingMergeTree
- Build multi-level aggregation (hourly → daily) to reduce query cost
- When queries are slow and data can be incrementally aggregated at insert time
Best practices
- Treat MVs as INSERT-triggered processing, not caches of query results
- Pick the correct engine: SummingMergeTree for sums/counts, AggregatingMergeTree for uniq/quantiles, ReplacingMergeTree for last-value
- Match target ORDER BY to the MV GROUP BY for proper merges and space efficiency
- Backfill historical data manually when adding an MV; MVs do not process existing rows
- Avoid many MVs on a single source table to prevent insert latency; consider async or consolidated pipelines
Example use cases
- Daily event counters using SummingMergeTree for fast dashboard metrics
- Accurate unique user and p95 latency using AggregatingMergeTree with -State/-Merge
- Parse JSON logs into structured tables on insert for simpler downstream queries
- Track latest user state with ReplacingMergeTree and argMax patterns
- Multi-level aggregation: hourly AggregatingMergeTree feeding daily AggregatingMergeTree for rollups
FAQ
No. Materialized Views only run on new INSERT batches. Backfill historical data by inserting aggregated results into the target table manually.
When should I use AggregatingMergeTree vs SummingMergeTree?
Use SummingMergeTree for pure additive metrics (sums, counters). Use AggregatingMergeTree when you need non-additive aggregates like uniq(), quantile(), or when accurate merging of state is required.
2 skills
This skill helps you design and manage ClickHouse materialized views for real-time aggregation and data transformation, optimizing queries and avoiding common
This skill helps you design ClickHouse schemas with optimized ORDER BY, partitioning, and data types to achieve sub-second queries and strong compression.
