ClickHouse MergeTree на собеседовании Data Engineer

Зачем спрашивают на собесе DE

ClickHouse — самый популярный OLAP-движок в РФ-DE. На собесе обязательно: «как работает MergeTree», «зачем ORDER BY», «когда ReplacingMergeTree». Senior — нюансы projections, marks, granularity, MV vs projections.

Главная боль без понимания — DE создал таблицу с ORDER BY (id), удивляется почему агрегационные запросы по event_date тормозят.

Как устроен MergeTree

MergeTree — основной движок в ClickHouse. По дизайну подобен LSM-tree.

Схема:

1. INSERT создаёт part — отдельную папку с упорядоченным куском данных.
2. Background процесс merge объединяет parts в более крупные.
3. Чтение пробегает по всем частям, объединяя в результат.

Ключевые свойства:

• Колоночное хранение — каждая колонка в отдельном файле, сжатие, чтение только нужных колонок.
• Sorted by ORDER BY — внутри part данные отсортированы.
• Sparse index — primary index хранит каждый N-й (index_granularity, дефолт 8192) ключ. Не для каждой строки, как в Postgres.
• Marks — указатели в файлах данных по индексу.

CREATE TABLE events (
  event_date DATE,
  user_id UInt64,
  event_type String,
  amount Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
SETTINGS index_granularity = 8192;

ORDER BY и primary key

ORDER BY — физический порядок данных внутри part. Главный ключ оптимизации запросов.

Правило: ставь в ORDER BY колонки, по которым чаще всего фильтруешь и группируешь, в порядке убывания селективности.

ORDER BY (event_date, user_id, event_type)

Запрос WHERE event_date = '2026-05-01' AND user_id = 42 использует индекс эффективно. Запрос WHERE event_type = 'click' (без префикса) — full scan.

Primary key. По умолчанию = ORDER BY. Можно сократить:

ORDER BY (event_date, user_id, event_type)
PRIMARY KEY (event_date, user_id)

Primary key — то, что попадает в sparse index. Уменьшение primary key → меньше памяти на index, но запросы по полным ORDER BY ещё работают (через locality).

Ограничения: ORDER BY — обязателен (кроме движков без сортировки), уникальности он не даёт.

Партиционирование

Partition — физическое разбиение таблицы на куски по выражению PARTITION BY.

PARTITION BY toYYYYMM(event_date)

→ для каждого месяца — отдельная папка /202605/, /202606/, ...

Зачем:

• Drop partition мгновенно (ALTER TABLE ... DROP PARTITION).
• Pruning — при WHERE event_date = ... Spark читает только релевантные partitions.
• Optimize / merge на уровне partition.

Эмпирика: партиция должна быть «среднего размера» (десятки ГБ). Слишком много мелких — каталог раздут, запросы медленнее.

Не путай с ORDER BY:

• PARTITION BY = физическое разделение (отдельные директории).
• ORDER BY = сортировка внутри партиции.

Обычно: PARTITION BY toYYYYMM(date) + ORDER BY (date, user_id).

Семейство MergeTree-движков

MergeTree — базовый.

ReplacingMergeTree(version_col). При merge сохраняет строку с максимальным version_col для каждого ORDER BY-ключа. Используется для дедупликации (CDC, upsert).

ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

Дедупликация eventual — происходит на merge. Чтение через FINAL — форс дедупликации (медленнее).

SummingMergeTree(metric_cols). При merge суммирует metric_cols для одинакового ORDER BY-ключа.

ENGINE = SummingMergeTree(clicks, impressions)
ORDER BY (campaign_id, ad_id);

Удобно для агрегационных таблиц.

AggregatingMergeTree. Хранит промежуточные состояния агрегатов (AggregateFunction(sum, ...)). Финальная агрегация на чтение через -Merge. Для сложных метрик (uniq, quantile).

CollapsingMergeTree(sign). Удаление через парные строки +1/-1. Сложно, специальный случай.

VersionedCollapsingMergeTree. То же + version, для concurrent updates.

GraphiteMergeTree, etc. Специальные случаи.

В современной разработке чаще: MergeTree (faktы) + ReplacingMergeTree (dim/CDC) + SummingMergeTree (preaggregated) + AggregatingMergeTree (advanced metrics).

Replicated и distributed

ReplicatedMergeTree. Поверх MergeTree — репликация через ZooKeeper / Keeper.

ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')

Каждая INSERT записывается в Keeper, реплики стягивают.

Distributed engine. Виртуальная таблица, ссылается на shards. INSERT шардирует, SELECT параллелит.

CREATE TABLE events_distributed AS events
ENGINE = Distributed('cluster', 'db', 'events', user_id);

user_id — sharding key.

Стандартная схема в проде:

• На каждом узле — ReplicatedMergeTree локальная таблица.
• Поверх — Distributed для запросов на cluster.
• INSERT в Distributed → шардируется автоматически.

Когда не стоит использовать ClickHouse

OLTP сценарий. Частые UPDATE / DELETE (даже через ALTER) дороги. CH для append-only / log-style данных.

Транзакции. Полноценных ACID нет. Лёгкие atomic INSERT в одну партицию — есть. Multi-table транзакции — нет.

Real-time индивидуальное чтение. CH оптимизирован под аналитические запросы по миллионам/миллиардам строк. Один SELECT на одну строку — overhead. Postgres / KV-store лучше.

Частые JOIN с большими таблицами. CH JOIN не блестит. Идиома — денормализация (всё в одной wide table) или JOIN с маленькой dim через GLOBAL.

Высокий QPS на single row. Тысячи QPS на single rows — Cassandra / Redis / Postgres лучше.

Частые ошибки

ORDER BY (id) для event-таблицы. Запросы по event_date идут full scan — катастрофа. Ставь ORDER BY (event_date, user_id).

Index granularity 8192 для маленьких таблиц. Если 100k строк — granularity 8192 даёт 12 marks, индекс почти бесполезен. Уменьшай для маленьких таблиц.

Слишком частые INSERT'ы. Каждый INSERT = новый part. > 100 partsв минуту → too many parts error. Батчируй (10k+ строк за раз).

ALTER TABLE ... UPDATE на огромной таблице. Это mutation — переписывает все parts. Долго и дорого. Используй ReplacingMergeTree для upsert-семантики.

Confused FINAL semantics. SELECT ... FINAL принудительно дедуплицирует на чтение — в 2-10× медленнее. На агрегатах — не нужно (argMax(value, version) решает).

Без partitioning. Без partition нельзя drop старых данных без полного перезаписания таблицы.

Партиционирование по слишком мелкому ключу. PARTITION BY toDate(date) на 5-летней истории = 1825 partitions, каталог раздувается.

Distributed без weight. Шарды разной мощности — данные распределяются равномерно, перегружая слабый.

Связанные темы

• Greenplum на собесе DE
• Партиционирование в ClickHouse
• Star schema vs Snowflake
• DWH ClickHouse на собесе DE
• Подготовка к собесу Data Engineer

FAQ

Materialized view в CH — как работает?

MV — триггер на INSERT в источник, который записывает в целевую таблицу. Не «view as query» как в Postgres, а реальная таблица. Дешёвая агрегация в реальном времени.

Projection vs MV?

Projection (CH 21+) — встроенная альтернатива MV. Прозрачна для запросов: оптимизатор сам выбирает projection при подходящем фильтре. MV — отдельная таблица, нужен явный SELECT.

Что такое sparse index?

Не индекс на каждую строку, а на каждые N строк (granularity). Хранит min/max ORDER BY ключа на granule. На лету решает, какие granules читать.

Granularity 8192 — почему такое значение?

Эмпирический баланс между размером индекса в памяти и точностью pruning. Можно менять — для аналитических с длинными scan'ами 16384, для частых точечных — 1024.

CH удалять строки умеет?

Через ALTER TABLE ... DELETE WHERE (mutation, дорого) или специальные движки (CollapsingMergeTree). На практике — append-only с soft delete (deleted флаг) часто проще.

Это официальная информация?

Нет. Статья основана на документации ClickHouse 23.x и материалах Altinity / Yandex.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.