$ lexprog.com

// notes from an old coder -- php, databases, and the occasional rant

[October 31, 2026] ClickHouse

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/)

────────────────────────────────────────────────────────
<-- back to posts