← назад к разделу

В PostgreSQL таблицы проектируют по сущностям предметной области, а потом добавляют индексы под запросы. В ClickHouse всё наоборот: сначала список аналитических вопросов, под них ORDER BY, движок и предагрегаты. Ошибка в этих решениях не лечится «ещё одним индексом» — только пересозданием таблицы.

Порядок сортировки — главное решение

Когда ClickHouse записывает данные, он сортирует строки внутри каждой части по колонкам из ORDER BY и строит разреженный индекс. При запросе он читает не строки по одной, а блоки по 8 192 строки (гранулы). Если первые колонки ORDER BY совпадают с условием WHERE, движок пропускает ненужные гранулы целиком.

Правило: в начало ORDER BY — колонки с малым числом уникальных значений, по которым фильтруют всегда; в конец — более уникальные и время.

CREATE TABLE order_events (
    event_time   DateTime,
    event_type   LowCardinality(String),
    region       LowCardinality(String),
    customer_id  UInt64,
    order_id     UUID,
    amount       Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, event_type, event_time);

Запрос «по региону за период» использует префикс ключа и читает минимум гранул. Если поставить ORDER BY (event_time, region, event_type), фильтр по типу события перестаёт отсекать гранулы: внутри каждого временного диапазона все типы вперемешку.

Дополнительный бонус: одинаковые значения, стоящие рядом после сортировки, жмутся кодеками лучше. Удачный ключ одновременно ускоряет чтение и уменьшает объём на диске.

Типы данных: где экономят место и время

Каждая колонка в ClickHouse хранится отдельным файлом, и её размер напрямую влияет на скорость сканирования. Поэтому тип — не формальность.

  • LowCardinality(String) — словарное кодирование для колонок с сотнями или тысячами уникальных значений: статусы, типы событий, регионы, валюты. Меньше места, быстрее GROUP BY. При миллионах уникальных значений словарь раздувается и становится вредным.
  • Decimal(18, 2) — для денег. Float64 даёт погрешности при арифметике, как и в любой другой базе.
  • DateTime vs DateTime64(3) — секундная точность против миллисекундной. DateTime64 занимает вдвое больше места; его берут только когда миллисекунды действительно важны.
  • UInt8/16/32/64 — беззнаковые целые вместо bigint на всё. Чем меньше тип — тем быстрее сканирование.
  • Enum8('created' = 1, 'paid' = 2, ...) — компактнее и жёстче LowCardinality, но добавить новое значение — это ALTER. Подходит для закрытых наборов, которые редко меняются.
  • Nullable(T) — создаёт отдельный файл-маску для каждой колонки и запрещает использование в ORDER BY-префиксе. По умолчанию лучше обходиться дефолтами (0, ''); Nullable — только когда «нет значения» семантически отличается от нуля или пустой строки.
  • Array(T), Map(K, V) — легальная денормализация: теги, произвольные атрибуты события. Вместе с функциями arrayJoin, has, mapKeys закрывают большинство случаев «гибкой схемы» без отдельных таблиц.

Агрегатные запросы: идиомы, которых нет в PostgreSQL

ClickHouse предназначен для агрегирования. Несколько встроенных функций делают запросы короче и быстрее по сравнению с обычным SQL:

SELECT
    toStartOfMonth(event_time)            AS month,
    count()                               AS events,
    countIf(event_type = 'order_paid')    AS paid_orders,
    sumIf(amount, event_type = 'order_paid') AS revenue,
    uniq(customer_id)                     AS customers,
    quantile(0.95)(amount)                AS p95_check
FROM order_events
WHERE event_time >= '2026-01-01'
GROUP BY month
ORDER BY month;
  • countIf / sumIf / avgIf — условные агрегаты вместо CASE WHEN внутри агрегата. Читаются лучше и работают быстрее.
  • uniq vs uniqExact — uniq считает приближённо (HyperLogLog, погрешность около 1%) и на порядки дешевле по памяти и CPU. uniqExact даёт точный ответ, но дорого. Для дашбордов почти всегда достаточно uniq.
  • quantile / quantileExact — та же пара для перцентилей.
  • argMax(col, ts) — возвращает значение col из строки с максимальным ts. Удобно для «последнего статуса заказа» без оконных функций и self-join.

Materialized views: предагрегация на лету

В PostgreSQL materialized view — это снимок результата запроса, который обновляется по команде. В ClickHouse всё иначе: materialized view работает как триггер на вставку. Каждый INSERT в исходную таблицу прогоняется через запрос view и дописывается в целевую таблицу прямо во время записи.

Стандартная связка — с AggregatingMergeTree:

CREATE TABLE revenue_by_region_daily (
    day      Date,
    region   LowCardinality(String),
    revenue  AggregateFunction(sum, Decimal(18, 2)),
    orders   AggregateFunction(uniq, UUID)
)
ENGINE = AggregatingMergeTree
ORDER BY (region, day);

CREATE MATERIALIZED VIEW revenue_by_region_daily_mv
TO revenue_by_region_daily AS
SELECT
    toDate(event_time)        AS day,
    region,
    sumState(amount)          AS revenue,
    uniqState(order_id)       AS orders
FROM order_events
WHERE event_type = 'order_paid'
GROUP BY day, region;

Чтение — через -Merge-функции:

SELECT day, region, sumMerge(revenue) AS revenue, uniqMerge(orders) AS orders
FROM revenue_by_region_daily
GROUP BY day, region;

Почему sumState/uniqState, а не просто sum/uniq? Они хранят промежуточное состояние агрегата, а не готовое число. Это значит, что предагрегат по дням корректно доагрегируется в месяцы и годы. Для uniq это принципиально: сложить два числа HyperLogLog нельзя — нужны исходные структуры. Дашборд читает таблицу в тысячи строк вместо сырых миллиардов.

Два важных ограничения. Во-первых, MV срабатывает только на новые вставки — данные, вставленные до создания view, в агрегат не попадут. Засыпку делают отдельным INSERT ... SELECT. Во-вторых, ошибка в каскаде view может завалить INSERT в исходную таблицу. Каскады глубже одного-двух уровней быстро становятся неотлаживаемыми.

ReplacingMergeTree: как хранить «текущее состояние»

ClickHouse — append-only система. Чтобы обновить строку, нужно вставить новую версию. ReplacingMergeTree при слиянии частей оставляет только строку с максимальным значением указанного поля:

CREATE TABLE orders_latest (
    order_id    UUID,
    status      LowCardinality(String),
    amount      Decimal(18, 2),
    updated_at  DateTime64(3)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

Каждое изменение заказа — это новая вставка с той же order_id. При слиянии останется строка с максимальным updated_at. Проблема в том, что слияние случается «когда-нибудь», и до его завершения дубликаты видны в запросах. Честное чтение — одно из двух:

-- Короткий вариант: сливает версии прямо во время чтения
SELECT * FROM orders_latest FINAL WHERE status = 'paid';

-- Альтернатива: предсказуемая стоимость на больших таблицах
SELECT order_id, argMax(status, updated_at) AS status, argMax(amount, updated_at) AS amount
FROM orders_latest
GROUP BY order_id;

FINAL удобен, но на больших таблицах дорого обходится. Вариант с argMax многословнее, но предсказуемее по нагрузке. В обоих случаях это сознательная плата за «обновляемость» в append-only мире.

JOIN и словари

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

Как обходятся:

  • Денормализация при записи — главный приём. Название категории, регион, тариф кладут прямо в строку события на этапе пайплайна. Дополнительное место на диске дешевле, чем дорогие JOIN-ы при чтении.
  • Словари (dictionary) — справочники, которые ClickHouse сам подгружает из PostgreSQL, файла или HTTP-источника и обновляет по расписанию. Обращение — через функцию dictGet('regions_dict', 'name', region_id) без JOIN вовсе.
  • Если JOIN неизбежен — маленькая таблица справа, фильтры применяются до JOIN, а не после.

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

ОшибкаЧто происходитКак правильно
SELECT * на широкой таблицеЧитаются все колонки — смысл колоночного хранения пропадаетПеречислять только нужные колонки
Точечный запрос по order_id, которого нет в ORDER BYПолный скан при каждом вызовеТочечные чтения — в PostgreSQL; в ClickHouse — отдельная таблица с нужным ключом
Вставка по одной строке из приложенияОшибка TOO_MANY_PARTS, вставка встаётГрупповые вставки / async insert (подробнее)
Nullable на всех колонках «на всякий случай»Лишний файл на каждую колонку, всё медленнееДефолты; Nullable — только по семантической необходимости
PARTITION BY toDate(...) на годах данныхТысячи партиций, деградация вставки и чтенияМесяц (toYYYYMM) как значение по умолчанию
Частые ALTER ... UPDATE/DELETEОчередь мутаций, нагрузка на дискReplacingMergeTree с версиями строк
uniqExact/quantileExact в каждом дашбордеЛишние память и CPU ради точности, которую никто не заметитuniq/quantile
Каскад materialized views в пять уровнейСложно диагностировать сбои вставкиОдин-два уровня; остальное — пересчётом по расписанию

Коротко

  • Схема проектируется от запросов: сначала список аналитических вопросов, потом ORDER BY.
  • В ORDER BY — сначала колонки с малой кардинальностью (регион, тип), в конец — время.
  • LowCardinality(String) экономит место и ускоряет GROUP BY для колонок с сотнями уникальных значений.
  • Nullable создаёт лишние файлы — по умолчанию используй дефолты.
  • countIf/sumIf/argMax — основные идиомы агрегатных запросов в ClickHouse.
  • Materialized view — это триггер на вставку, а не снимок; срабатывает только на новые данные.
  • sumState/uniqState хранят промежуточное состояние — предагрегат корректно доагрегируется дальше.
  • ReplacingMergeTree откладывает дедупликацию до слияния; честное чтение — через FINAL или argMax.
  • JOIN грузит правую таблицу в память; альтернативы — денормализация при записи и словари.

Что почитать дальше

  • Fundamentals — как работают части и разреженный индекс под капотом.
  • Интеграция с сервисом — как наполнять эти таблицы из Java/Spring.
  • Эксплуатация — TTL, репликация, мониторинг слияний и мутаций.