ClickHouse: MaterializedView vs Projections — практический гайд

Q: MV обязательно нужен AggregatingMergeTree?

Не всегда. SummingMergeTree хорош для простых sum. ReplacingMergeTree — для dedup events. AggregatingMergeTree — для combinations sum/count/avg/uniq в одной таблице.

Q: Что если изменить ALTER TABLE source — column?

До CH 22: MV ломается, нужно DROP + CREATE. С CH 22+: ALTER TABLE mv MODIFY QUERY позволяет менять SELECT внутри MV без полного пересоздания.

Q: MV для real-time (latency < 1 sec)?

Нет. MV — eventual consistency через background merge. Для real-time agg — Kafka → CH с правильной partition strategy + чтение напрямую source с PREWHERE.

Q: Можно ли JOIN внутри MV-SELECT?

Да, но только с dictionaries или small RAM-резидентными таблицами. JOIN с большой таблицей в каждом INSERT-batch катастрофически медленный.

Q: MATERIALIZED VIEW в PostgreSQL — это то же самое?

Нет, в PG MV — это snapshot данных, обновляется командой REFRESH MATERIALIZED VIEW вручную. ClickHouse MV — trigger на каждый INSERT, автоматически. Концептуально разное. ---

ClickHouse — топ-СУБД для аналитики в РФ. Аналитики Wildberries, Avito, Ozon, Yandex работают с ним ежедневно. Но когда витрина на 5B строк и dashboard грузится 8 секунд — пора учить MaterializedView и Projections.

Эти 2 механизма решают одну задачу — pre-aggregation для быстрого чтения — но работают по-разному. Этот гайд объясняет когда что выбирать.

Зачем материализованные представления

Допустим, у тебя orders на 5 миллиардов строк (~3 TB Compressed). Каждое утро бизнес открывает dashboard «Daily Revenue by Region»:

SELECT
  toDate(created_at) AS day,
  region,
  sum(amount) AS revenue,
  count(*) AS orders
FROM orders
WHERE created_at >= today() - 30
GROUP BY day, region
ORDER BY day DESC;

Без оптимизации: full scan 30 days × ~150M rows = ~4.5B rows прочитано → 6-10 секунд на каждый refresh.

Решение: pre-aggregate данные в отдельной таблице (revenue_daily) с гранулярностью «day × region». 1000 строк за 30 дней vs 4.5B исходных строк.

Но как поддерживать revenue_daily в актуальном состоянии? MV и Projections автоматизируют это.

MaterializedView в ClickHouse: append-only схема

-- 1. Создаём target-таблицу для агрегатов
CREATE TABLE revenue_daily (
  day Date,
  region String,
  revenue_state AggregateFunction(sum, Decimal(18, 2)),
  orders_state AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (day, region);

-- 2. Создаём MV, который автоматом пишет в target
CREATE MATERIALIZED VIEW revenue_daily_mv TO revenue_daily AS
SELECT
  toDate(created_at) AS day,
  region,
  sumState(amount) AS revenue_state,
  countState() AS orders_state
FROM orders
GROUP BY day, region;

-- 3. Читаем из target с финализацией агрегатов
SELECT
  day,
  region,
  sumMerge(revenue_state) AS revenue,
  countMerge(orders_state) AS orders
FROM revenue_daily
WHERE day >= today() - 30
GROUP BY day, region
ORDER BY day DESC;

Что важно понять:

MV — это trigger на INSERT в source table. Каждый new batch в orders → автоматически пишется агрегат в revenue_daily.
AggregatingMergeTree хранит partial states (sumState, countState), не финальные значения. Это позволяет дальше merge при чтении.
MV — append-only. Не обновляет existing rows, только инсертит. ClickHouse merge engine склеивает дубликаты в background через merge parts.

Шаг 1-5: построить MV для daily revenue rollup

Шаг 1. Подготовить source table

CREATE TABLE orders (
  order_id UInt64,
  user_id UInt64,
  region String,
  amount Decimal(18, 2),
  status String,
  created_at DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (region, created_at, order_id)
SETTINGS index_granularity = 8192;

Шаг 2. Target-таблица с AggregatingMergeTree

CREATE TABLE revenue_daily (
  day Date,
  region String,
  revenue AggregateFunction(sum, Decimal(18, 2)),
  orders AggregateFunction(count, UInt64),
  unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, region);

Шаг 3. Materialized View

CREATE MATERIALIZED VIEW revenue_daily_mv TO revenue_daily AS
SELECT
  toDate(created_at) AS day,
  region,
  sumState(amount) AS revenue,
  countState() AS orders,
  uniqState(user_id) AS unique_users
FROM orders
WHERE status = 'completed'
GROUP BY day, region;

Шаг 4. Backfill исторических данных

MV срабатывает только на новые INSERT. Для существующих данных:

INSERT INTO revenue_daily
SELECT
  toDate(created_at) AS day,
  region,
  sumState(amount) AS revenue,
  countState() AS orders,
  uniqState(user_id) AS unique_users
FROM orders
WHERE status = 'completed'
  AND created_at < '2024-12-01'  -- до сегодня
GROUP BY day, region;

Шаг 5. Чтение dashboards

SELECT
  day,
  region,
  sumMerge(revenue) AS revenue,
  countMerge(orders) AS orders,
  uniqMerge(unique_users) AS unique_users
FROM revenue_daily
WHERE day BETWEEN today() - 30 AND today()
GROUP BY day, region
ORDER BY day DESC, region;

Время чтения: было 6-10 секунд → стало 20-50 миллисекунд.

Projections (CH 21.3+): что это и чем отличаются от MV

Projections — это встроенный в таблицу альтернативный sort order + pre-aggregation. Не отдельная таблица.

ALTER TABLE orders ADD PROJECTION revenue_by_day_region (
  SELECT
    toDate(created_at) AS day,
    region,
    sum(amount) AS revenue,
    count() AS orders
  GROUP BY day, region
);

-- Materialize для существующих данных
ALTER TABLE orders MATERIALIZE PROJECTION revenue_by_day_region;

Теперь любой запрос, попадающий под структуру projection, автоматически перенаправится:

SELECT toDate(created_at) AS day, region, sum(amount), count()
FROM orders
WHERE created_at >= today() - 30
GROUP BY day, region;
-- ClickHouse сам выбирает projection вместо main data

EXPLAIN покажет:

PROJECTION: revenue_by_day_region

Шаг 1-4: создать Projection на orders

Шаг 1. Добавить projection через ALTER

ALTER TABLE orders ADD PROJECTION region_revenue (
  SELECT
    toDate(created_at) AS day,
    region,
    sum(amount) AS revenue,
    count() AS orders,
    uniq(user_id) AS unique_users
  GROUP BY day, region
);

Шаг 2. Materialize для existing data

ALTER TABLE orders MATERIALIZE PROJECTION region_revenue;

Это процесс на часы для terabyte-таблиц. Запускается в background.

Шаг 3. Verify что projection используется

EXPLAIN actions=1
SELECT toDate(created_at), region, sum(amount)
FROM orders
WHERE created_at >= today() - 30
GROUP BY 1, 2;

-- Ищи в output: ReadFromMergeTree (Projection: region_revenue)

Шаг 4. Drop projection если не нужна

ALTER TABLE orders DROP PROJECTION region_revenue;

MV vs Projections: когда что выбирать

Критерий	MaterializedView	Projections
Отдельная таблица?	Да (target)	Нет (внутри source)
Backfill при ALTER source?	Нет (только новые INSERT)	Да (auto при materialize)
Можно JOIN внутри?	Да	Нет
Можно WHERE с subquery?	Да	Ограниченно
Видна планировщиком автоматом?	Нет (надо явно читать из target)	Да (transparent rerouting)
Storage overhead	Отдельная	Включена в source partitions
Когда оптимально	Сложная агрегация с JOIN, real-time append-only events	Простой rollup по уже существующим колонкам

Rule of thumb:

MV — когда нужны сложные трансформации (JOIN, custom logic), real-time event-stream pipelines, кросс-таблицы агрегаты.
Projection — когда нужен alternative sort/aggregate того же source data, прозрачно для query planner.

Подводные камни

Eventual consistency

MV пишет в target асинхронно в background merge thread. Между INSERT в source и появлением в MV — до нескольких секунд.

Для real-time queries (latest 1 minute) — читай напрямую source. Для исторических — из MV.

ALTER source ломает MV

ALTER TABLE orders ADD COLUMN discount Decimal(18, 2);
-- MV revenue_daily_mv продолжает работать со старой схемой
-- НО: новый INSERT с discount не пишется в MV (silently ignored)

Решение: DROP MV + CREATE заново с новой схемой. Или используй ALTER TABLE ... MODIFY QUERY (с CH 22+).

Нет CASCADE DELETE

DELETE из source НЕ удаляет из MV. Если в source row был «исправлен» (insert/update), MV сохранит обе версии, и SELECT увидит дубль.

Решение: ALTER TABLE revenue_daily DELETE WHERE day = '2024-03-15' + re-backfill.

Projections блокируют OPTIMIZE TABLE FINAL

При большом числе projections OPTIMIZE FINAL может зависнуть на 30+ минут (нужно перестроить все projections). На production используй partition-level OPTIMIZE.

Projection не работает с подзапросами/JOIN

Любой query с JOIN или subquery — projection пропускается, fallback на main data. Это часто молчаливая регрессия dashboard производительности.

FAQ

MV обязательно нужен AggregatingMergeTree?

Не всегда. SummingMergeTree хорош для простых sum. ReplacingMergeTree — для dedup events. AggregatingMergeTree — для combinations sum/count/avg/uniq в одной таблице.

Что если изменить ALTER TABLE source — column?

До CH 22: MV ломается, нужно DROP + CREATE. С CH 22+: ALTER TABLE mv MODIFY QUERY позволяет менять SELECT внутри MV без полного пересоздания.

MV для real-time (latency < 1 sec)?

Нет. MV — eventual consistency через background merge. Для real-time agg — Kafka → CH с правильной partition strategy + чтение напрямую source с PREWHERE.

Можно ли JOIN внутри MV-SELECT?