Часть 5. Проектирование таблиц: ORDER BY, партиции, кодеки, TTL

Никакие настройки сервера не спасут плохую схему. В ClickHouse проектирование таблицы — это одновременно выбор индекса, физического порядка хранения и коэффициента сжатия. Это самое важное решение, и оно принимается один раз.

ORDER BY — сердце таблицы

ORDER BY задаёт порядок, в котором данные лежат на диске, и одновременно строит разрежённый первичный индекс (одна запись на гранулу из 8192 строк по умолчанию).

Практические правила:

1. Начинайте с колонок, по которым фильтруете. Если колонки нет в WHERE — ей нечего делать в начале ключа.

2. Сортируйте колонки по возрастанию кардинальности. Сначала низкокардинальные (тип события, регион), потом высококардинальные (user_id), в конце — время или уникальные идентификаторы. Причина техническая: бинарный поиск эффективно работает только по первой колонке; для последующих применяется обобщённый поиск с исключением, и он тем эффективнее, чем меньше уникальных значений в предшествующих колонках.

3. Это же даёт сжатие. Похожие значения оказываются рядом — разница в размере на диске между удачным и неудачным порядком колонок бывает в десятки раз.

SQL

-- Хорошо: низкая кардинальность → высокая → время
ORDER BY (event_type, tenant_id, user_id, ts)

-- Плохо: уникальный идентификатор первым убивает и индекс, и сжатие
ORDER BY (request_id, event_type, ts)
Нажмите, чтобы развернуть и увидеть больше

4. Не пытайтесь обслужить все сценарии одним ключом. Если у вас принципиально разные паттерны доступа, правильный ответ — не раздувать ORDER BY до десяти колонок, а завести проекции или материализованные представления с другим порядком сортировки.

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

PARTITION BY — это не про производительность фильтрации в первую очередь (за это отвечает ORDER BY), а про управление жизненным циклом данных: удаление, перенос, бэкап целыми кусками.

Стандартный выбор — по месяцу:

SQL

PARTITION BY toYYYYMM(ts)
Нажмите, чтобы развернуть и увидеть больше

Главная ошибка — слишком мелкое партиционирование. PARTITION BY toDate(ts) при трёхлетнем хранении даёт больше тысячи партиций на таблицу, а с учётом реплик и слияний это лишняя нагрузка на метаданные и Keeper. Ориентир: держите число активных партиций в пределах сотен, а не тысяч. По дню партиционируют только если данных очень много и они живут недолго.

Партиция даёт дешёвые операции: DROP PARTITION удаляет данные мгновенно, в отличие от DELETE, который порождает дорогую мутацию.

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

ClickHouse сжимает хорошо, но не обязан исправлять расточительную схему:

LowCardinality(String) для колонок с небольшим числом уникальных значений (статусы, коды стран, типы событий) — словарное кодирование, серьёзная экономия и ускорение фильтров. Ориентир — до нескольких десятков тысяч уникальных значений.
Целые нужного размера: UInt8/UInt16 вместо UInt64 там, где диапазон известен.
DateTime/Date вместо строк с датой. Всегда.
Избегайте Nullable без необходимости: это отдельная колонка-маска, дополнительные чтения и запрет на использование в некоторых оптимизациях. Часто честнее значение по умолчанию.
Для фиксированных наборов — Enum8/Enum16.

Кодеки сжатия

Поверх общего сжатия можно задать кодек под характер данных — особенно выгодно для временных рядов и метрик:

SQL

CREATE TABLE metrics (
    ts      DateTime      CODEC(Delta, ZSTD(1)),
    device  LowCardinality(String),
    value   Float64       CODEC(Gorilla, ZSTD(1)),
    counter UInt64        CODEC(DoubleDelta, ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (device, ts);
Нажмите, чтобы развернуть и увидеть больше

Шпаргалка:

Delta — монотонно растущие значения (время, счётчики);
DoubleDelta — почти равномерно растущие (timestamp с фиксированным шагом, счётчики);
Gorilla — числа с плавающей точкой, меняющиеся понемногу (метрики);
T64 — целые в узком диапазоне;
ZSTD(1..3) — универсальный финальный слой; выше уровень — лучше сжатие, дороже CPU.

Кодеки комбинируются: сначала специализированный, потом общий (CODEC(Delta, ZSTD(1))).

TTL

TTL закрывает две задачи: удаление устаревших данных и перенос их на дешёвый уровень хранения.

SQL

TTL ts + INTERVAL 30 DAY TO VOLUME 'cold',
    ts + INTERVAL 1 YEAR DELETE
Нажмите, чтобы развернуть и увидеть больше

Также TTL умеет агрегировать «на лету» (GROUP BY в TTL) — старые сырые записи схлопываются в агрегаты, объём падает. Полезно для метрик, где детализация нужна только за последнее время.

Не забудьте про system.query_log и другие системные таблицы: без TTL они растут неограниченно. Ограничьте их сразу.

Выбор движка

MergeTree — база для всего.
ReplicatedMergeTree — то же, но с репликацией через Keeper. В проде используйте только его, даже если реплика пока одна: превратить нереплицируемую таблицу в реплицируемую задним числом — отдельная неприятная процедура.
ReplacingMergeTree — дедупликация по ключу сортировки. Важно понимать: схлопывание происходит при слиянии, когда-нибудь, а не сразу. Для гарантированного результата в запросе нужен FINAL (дорого) или агрегация с выбором последней версии.
SummingMergeTree / AggregatingMergeTree — предагрегация при слиянии, для витрин.
Distributed — не хранит данные, а распределяет запросы по шардам.

Реплицируемая таблица создаётся с макросами, которые проставляет оператор:

SQL

CREATE TABLE events ON CLUSTER '{cluster}' (
    ...
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events',
    '{replica}'
)
PARTITION BY toYYYYMM(ts)
ORDER BY (event_type, user_id, ts);
Нажмите, чтобы развернуть и увидеть больше

Про JOIN и мутации — заранее

Два места, где ожидания расходятся с реальностью:

JOIN. Умного планировщика, который сам переставит таблицы, здесь нет. Правая таблица соединения загружается в память — поэтому справа должна быть меньшая, и фильтровать нужно до соединения. Для справочников используйте словари (dictGet) — это быстрее и дешевле по памяти, чем джойн.

UPDATE/DELETE. Это мутации: они асинхронно переписывают целые куски данных. На больших таблицах — дорого и долго. Проектируйте так, чтобы обходиться без них: удаление — через DROP PARTITION, изменение — через версионирование и ReplacingMergeTree. Если мутации всё же нужны регулярно — скорее всего, задача не для ClickHouse.

Дальше: Часть 6. Вставка данных: батчи, async_insert и потоки из Kafka.