ClickHouse Materialized Views
ClickHouse Materialized Views: Tips & Tricks
ClickHouse Materialized Views: Tips & Tricks
Tip: Auto-Updating Aggregations
CREATE MATERIALIZED VIEW daily_views
ENGINE = SummingMergeTree()
ORDER BY (date, url)
AS SELECT
toDate(created_at) as date,
url,
count() as views,
uniq(user_id) as visitors
FROM page_views
GROUP BY date, url;
Automatically updates when new data is inserted into page_views.
Gotcha: Materialized Views Don't Query Source Data
They only process new inserts. Existing data in the source table is NOT backfilled unless you use POPULATE.
Tip: POPULATE for Existing Data
CREATE MATERIALIZED VIEW daily_views
POPULATE
ENGINE = SummingMergeTree()
ORDER BY (date, url)
AS SELECT ...;
Processes existing data during creation. But locks the source table.
Gotcha: Multiple Materialized Views
You can have multiple materialized views on the same source table:
CREATE MATERIALIZED VIEW mv_hourly ...;
CREATE MATERIALIZED VIEW mv_daily ...;
CREATE MATERIALIZED VIEW mv_weekly ...;
Each processes inserts independently.
Tip: Query the Materialized View Directly
SELECT * FROM daily_views WHERE date >= today() - 30;
Don't query the source table — the materialized view is pre-aggregated and much faster.
Gotcha: Schema Changes
If you alter the source table, materialized views may break. Update them too.
Tip: Nested Materialized Views
CREATE MATERIALIZED VIEW weekly_summary
ENGINE = SummingMergeTree()
ORDER BY (week)
AS SELECT toStartOfWeek(date) as week, sum(views) as total
FROM daily_views GROUP BY week;
Chain materialized views for multi-level aggregation.
Gotcha: Materialized Views Use Their Own Engine
The view's engine (SummingMergeTree, etc.) is independent of the source table's engine.
Tip: Order of Columns in ORDER BY Matters Massively
ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.
Tip: Use LowCardinality for Enum-Like Strings
Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.
Gotcha: Mutations Are Heavy
ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.
Senior Insight
ClickHouse requires a fundamental shift in thinking for developers coming from OLTP databases. The database is optimized for bulk reads of millions of rows, not single-row lookups. I've seen developers struggle because they try to use ClickHouse like PostgreSQL — frequent small INSERTs, point SELECTs, UPDATEs. These operations work in ClickHouse but perform poorly. The mental model should be: write in batches, read in ranges, and never update. Once you internalize this, ClickHouse becomes beautiful.
Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)