Materialized views и проекции в ClickHouse: когда что использовать и как не наступить на грабли

ClickHouse быстрый. Настолько быстрый, что первые полгода можно жить без оптимизаций вообще — агрегации по миллиарду строк отрабатывают за секунды. Потом данных становится больше, запросов тоже, дашборд начинает подтормаживать, и вы задумываетесь: «а можно ли считать агрегаты заранее?»

Можно. В ClickHouse для этого есть два механизма: materialized views и проекции (projections). Оба пересчитывают данные на этапе вставки. Оба ускоряют чтение. Но работают по-разному.

Materialized Views: отдельная таблица с агрегатами

Materialized view (MV) в ClickHouse — это не «кешированный SELECT», как в PostgreSQL. Это триггер на вставку: когда данные приходят в source-таблицу, MV выполняет свой SELECT на новом блоке данных и записывает результат в target-таблицу.

-- Source: сырые события
CREATE TABLE events (
    event_date Date,
    user_id UInt64,
    event_type String,
    amount Decimal(18, 2)
) ENGINE = MergeTree()
ORDER BY (event_date, user_id);

-- Target: агрегаты по дням
CREATE TABLE daily_stats (
    event_date Date,
    event_type String,
    total_amount AggregateFunction(sum, Decimal(18, 2)),
    user_count AggregateFunction(uniq, UInt64),
    event_count AggregateFunction(count)
) ENGINE = AggregatingMergeTree()
ORDER BY (event_date, event_type);

-- MV: маршрутизирует вставки из events в daily_stats
CREATE MATERIALIZED VIEW events_daily_mv
TO daily_stats
AS SELECT
    event_date,
    event_type,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS user_count,
    countState() AS event_count
FROM events
GROUP BY event_date, event_type;

Теперь при каждой вставке в events MV автоматически обновит daily_stats. Запрос к агрегатам:

SELECT
    event_date,
    event_type,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(user_count) AS unique_users,
    countMerge(event_count) AS events
FROM daily_stats
GROUP BY event_date, event_type
ORDER BY event_date DESC;

Обратите внимание на State / Merge пары. Это такая вот специфика ClickHouse: при записи в AggregatingMergeTree вы сохраняете промежуточное состояние агрегатной функции (sumState), а при чтении финализируете его (sumMerge).

Когда данные в MV некорректны

MV обрабатывает только новые вставки. Если вы изменяете данные в source-таблице через ALTER TABLE ... UPDATE или DELETE, MV об этом не узнает. Target-таблица рассинхронизируется.

Решения: для редких обновлений — пересоздавать MV с POPULATE. Для частых — использовать ReplacingMergeTree в source-таблице и строить MV поверх дедуплицированных данных. Или использовать refreshable MV.

Refreshable Materialized Views

Альтернатива инкрементальным MV — MV, которые пересчитываются по расписанию:

CREATE MATERIALIZED VIEW hourly_report
REFRESH EVERY 1 HOUR
ENGINE = MergeTree()
ORDER BY (report_hour, category)
AS SELECT
    toStartOfHour(event_date) AS report_hour,
    event_type AS category,
    sum(amount) AS total,
    uniq(user_id) AS users
FROM events
GROUP BY report_hour, category;

Работает как cron: раз в час ClickHouse пересчитывает весь view с нуля.

Проекции

Проекция — это альтернативное представление данных той же таблицы, хранящееся внутри неё. Физически — отдельная копия данных (или агрегатов) с другим ORDER BY. Логически — ClickHouse сам решает, использовать проекцию или нет, в зависимости от запроса.

-- Исходная таблица: ORDER BY (event_date, user_id)
-- Запросы по event_type будут медленными — полный скан

-- Добавляем проекцию
ALTER TABLE events ADD PROJECTION p_by_type (
    SELECT
        event_type,
        event_date,
        sum(amount) AS total_amount,
        count() AS cnt
    GROUP BY event_type, event_date
);

-- Материализуем (построить для существующих данных)
ALTER TABLE events MATERIALIZE PROJECTION p_by_type;

Теперь запрос:

SELECT
    event_type,
    sum(amount),
    count()
FROM events
WHERE event_type = 'purchase'
GROUP BY event_type;

ClickHouse автоматически увидит, что проекция p_by_type покрывает этот запрос, и использует её. Не нужно менять запрос и не нужно указывать другую таблицу.

Проекция только для пересортировки

Проекции работают и без GROUP BY — просто как альтернативный порядок сортировки:

ALTER TABLE events ADD PROJECTION p_by_user (
    SELECT * ORDER BY user_id, event_date
);
ALTER TABLE events MATERIALIZE PROJECTION p_by_user;

Теперь WHERE user_id = 42 будет использовать проекцию вместо полного скана. По сути такой вот аналог вторичного индекса в row-based БД, только хранится полная копия данных.

Когда что выбирать

Проекции — когда вам нужен другой ORDER BY для одной таблицы. Сценарий: основная таблица упорядочена по (timestamp, user_id), но 30% запросов фильтруют по country_code. Добаляем проекцию с ORDER BY (country_code, timestamp) и эти запросы ускорятся на порядок.

MV — когда нужна агрегация из нескольких источников, сложные трансформации или отдельная таблица. ETL-пайплайн, где данные из raw_events агрегируются в hourly_metrics, которые потом агрегируются в daily_metrics — цепочка MV. Проекции так не умеют.

MV — когда нужна фильтрация на вставке. Хотите материализовать только event_type = 'purchase'? MV поддерживает WHERE. Проекции — нет.

Проекции — когда нужна прозрачность для BI-инструментов. Superset, Grafana, Metabase шлют запросы к одной таблице. Проекции ускоряют эти запросы без переконфигурации BI — инструмент даже не знает, что проекция существует.

SummingMergeTree для простых счётчиков

Если ваши агрегаты — простые суммы и каунты, можно использовать SummingMergeTree вместо AggregatingMergeTree. Проще:

CREATE TABLE daily_revenue (
    event_date Date,
    category String,
    revenue Decimal(18, 2),
    order_count UInt64
) ENGINE = SummingMergeTree()
ORDER BY (event_date, category);

CREATE MATERIALIZED VIEW revenue_mv
TO daily_revenue
AS SELECT
    event_date,
    event_type AS category,
    sum(amount) AS revenue,
    count() AS order_count
FROM events
GROUP BY event_date, event_type;

-- Запрос: просто SELECT, без *Merge
SELECT event_date, category, sum(revenue), sum(order_count)
FROM daily_revenue
GROUP BY event_date, category;

sum() в финальном запросе нужен, потому что SummingMergeTree склеивает строки с одинаковым ORDER BY при merge-операциях, но merge происходит асинхронно, могут быть неслитые куски. sum() гарантирует корректный результат.

dictGet вместо JOIN

ClickHouse не любит JOIN на больших таблицах. Если вам нужно обогатить факты справочными данными (название категории, регион пользователя), используем словари:

CREATE DICTIONARY category_dict (
    id UInt64,
    name String,
    department String
) PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'categories' DB 'default'))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 600);

-- В MV или в запросе
SELECT
    event_date,
    dictGet('category_dict', 'name', category_id) AS category_name,
    sum(amount)
FROM events
GROUP BY event_date, category_name;

Словарь живёт в памяти, обновляется по расписанию, lookup — O(1). В MV можно денормализовать данные через dictGet прямо при вставке, избавившись от JOIN-ов в аналитических запросах.

Вообще, лучше начинать без оптимизаций. ClickHous сам по себе достаточно быстр для большинства задач на голых MergeTree-таблицах с правильным ORDER BY. Когда конкретный запрос начинает тормозить добавляем проекцию для этого запроса (если он бьёт по одной таблице с другим порядком) или MV (если нужна агрегация или трансформация).

Если ClickHouse уже работает в боевой эксплуатации и вы устали чинить запросы по одному, полезно разложить всё по полкам. На курсе «ClickHouse для инженеров и архитекторов БД» разбирают архитектуру, MergeTree, проекции и материализованные представления, резервное копирование и практики ускорения запросов. Готовы к серьезному обучению? Пройдите вступительный тест.

Для знакомства с форматом обучения и экспертами приходите на бесплатные уроки:

• 2 марта 19:00. «Стратегии обработки и хранения полуструктурированных данных в Clickhouse: Schema-on-Read, Schema-on-Write, Native Json». Записаться

• 17 марта 20:00. «Как перейти на ClickHouse, когда PostgreSQL уже не хватает?». Записаться

• 25 марта 20:00. «РазвИИтие на основе ClickHouse: базы данных для искусственного интеллекта». Записаться