Я блокчейн разработчик и в проекте у нас базы с сотнями гигабайт данных с децентрализованных бирж. Чтобы строить аналитические отчеты и делать агрегации, такие как вычисления цен, биржевых свечей, объемов торгов, цен на токены, мы используем БД Clickhouse (далее CH). До этого я работал только с Postgres (и давно с MSSQL), и хочу рассказать, как я вкатывался, что удивило – практический опыт и WTFы. Прочитав эту статью вам, возможно, захочется сделать аналитику по своим данным в Clickhouse – возможно, ищете, что полезного освоить на длинных выходных. Итак, поехали!

Зачем вообще Clickhouse?

Добавил этот раздел после вопросов в комментах. CH – это БД, спроектированная для OLAP, т.е. запросов со сложной обработкой данных, такие как агрегации, различные аналитические разрезы по очень большим объемам. Данные хранятся по колонкам (columnar), и эти данные по умолчанию сжаты (ZSTD в CH Cloud), поскольку данные по колонкам однотипные и хорошо сжимаются. Приведу пример конкретной таблицы из моей БД:

SELECT 
    column,
    formatReadableSize(data_compressed_bytes) AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    data_uncompressed_bytes / data_compressed_bytes AS ratio
FROM system.columns
WHERE table = 'liquidity_events_raw' AND database = 'liquidity'
ORDER BY ratio DESC

Compressed – сколько данные занимают на диске, uncompressed – это сколько бы они заняли, если бы лежали по строчкам. В целом, на моей таблице компрессия в 6 раз – таблица на диске занимает 129 Гб, а если бы те же данные вставить в, например, Postgres – заняла бы 786 Гб. Это не только сильно экономит место, но и позволяет быстрее делать агрегаты – ведь читать с диска данных нужно меньше, а I/O – как раз бутылочное горлышко. Более подробно можно почитать тут и тут. Я очень уважаю Постгрес и использую его, но CH и Postgres – БД для разных задач. Так что если нужно быстро делать аналитические запросы, то я выберу CH.

Итак, приступим к практике.

Данные не всегда финальны / "никуда без мержинга"

Типичный use-case кликхауса – мы вставляем данные в таблицу, и потом пишем по ним аналитические запросы. Предположим, нам поступают цены на токены, и мы хотим иметь возможность посмотреть последнюю цену в любой момент времени:

CREATE TABLE latest_price (
    token String,
    price Float64,
    version UInt32
) ENGINE = MergeTree()
ORDER BY token;й

-- в таблицу поступают разные текущие значения 
INSERT INTO latest_price VALUES ('ETH', 3000, 1);
INSERT INTO latest_price VALUES ('ETH', 3100, 2);
INSERT INTO latest_price VALUES ('ETH', 3050, 3);
INSERT INTO latest_price VALUES ('BTC', 100000, 1);

Вот как можно было бы посмотреть последнюю цену. Очень предсказуемо, в Постгресе тоже будет работать:

SELECT token, price, version
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY token ORDER BY version DESC) AS rn
    FROM latest_price
) t WHERE rn = 1;

/* 
Выводит:
        BTC	100000.0	1
        ETH	3050.0      3
*/

Теперь давайте соптимизируем место на диске; говорят, в CH для этого есть ReplacingMergeTree, которая сохраняет только последнюю версию данных (последнюю цену). Для этого, создадим ту же таблицу заново, но с другим ENGINE и вставим те же данные:

CREATE TABLE latest_price (
    token String,
    price Float64,
    version UInt32
) ENGINE = ReplacingMergeTree(version) -- строчки с бОльшим version "остаются"
ORDER BY token;

-- Те же INSERTs

-- И финально SELECT
SELECT * FROM latest_price;

/*
ETH	3000.0	    1
ETH	3100.0   	2
ETH	3050.0	    3
BTC	100000.0	1
*/

Странно, вроде предполагается, что мы получим только последние версии (две строчки). Вот мы и "открыли" концепцию нефинальных данных (non-final data). Пока таблица состоит из 4х строчек, но когда-нибудь (непонятно когда), она оптимизируется до 2х (на практике, это через полчаса должно произойти). Можно воспользоваться FINAL, чтобы посмотреть, как это должно выглядеть:

SELECT * FROM latest_price FINAL;

/*
ETH	3050.0	    3
BTC	100000.0	1
*/

FINAL не оказывает влияния на данные на диске, а мержит данные (берет последнюю версию цены) в RAM. Чтобы выполнить мержинг на диске раньше времени, можно написать OPTIMIZE TABLE latest_price FINAL;

Что значит мержинг? Можно почитать, но двух словах – для быстроты вставки на каждый insert batch создается свой part, физическая частичка данных на диске, которые потом смерживаются (соединяются) бекграунд процессом, тем самым экономя место на диске. Можно посмотреть parts таблицы запросом:

SELECT name, rows
FROM system.parts
WHERE (`table` = 'latest_price') AND active
ORDER BY name ASC;

-- до OPTIMIZE TABLE
/*
name         rows
  all_1_1_1	 1
  all_2_2_1	 1
  all_3_3_1	 1
  all_4_4_1	 1
*/

-- после OPTIMIZE TABLE
/*
  name         rows
  all_1_4_2	   2
*/

Это был первый WTF – я привык к тому, что в Постгресе (OLTP) мы всегда видим только финальные данные. В случае CH (OLAP), нефинальность данных обеспечивает более высокую скорость вставки, перенося косты мержинга на бекграунд процесс. Поэтому CH не используется для трекинга состояния пользователей, статусы покупок и документов – только для аналитики по большим данным.

Подробнее про parts, про движок MergeTree, про Partitions (с помощью которых можно включать/выключать части данных).

Никуда без materialized views (MVs)

MV нужны, чтобы предвычислить данные на этапе вставки для более быстрых селектов в дальнейшем. Элементарный пример – MV читает все цены токенов, но сохраняет в target таблицу только записи выше 5000:

-- Target-таблица
CREATE TABLE expensive_tokens (
    token String,
    price Float64,
    version UInt32
) ENGINE = MergeTree() ORDER BY token;

-- MV: только цены > 5000
CREATE MATERIALIZED VIEW expensive_tokens_mv TO expensive_tokens AS
SELECT * FROM latest_price WHERE price > 5000;

-- делаем вставки

SELECT * FROM expensive_tokens;
/*
выводит
BTC	100000.0	1
*/

Более сложный случай – комбинация MV + AggregatingMergeTree. На основе таблицы latest_price выше мы хотим предвычислять и сохранять в другую таблицу последнюю цену токена:

-- создадим таблицу latest_price как раньше

-- destination таблица (куда пишется результат агрегирования)
CREATE TABLE token_stats (
    token String,
    latest_price AggregateFunction(argMax, Float64, UInt32)
) ENGINE = AggregatingMergeTree()
ORDER BY token;

-- MV, который читает из исходной (source) таблицы latest_price
CREATE MATERIALIZED VIEW token_stats_mv TO token_stats AS
SELECT
    token,
    -- след. стейт - цена для максимальной версии (последняя цена)
    argMaxState(price, version) AS latest_price
FROM latest_price
GROUP BY token;

-- теперь можно выполнить наши INSERTы

-- Посмотрим результат
SELECT 
    token,
    argMaxMerge(latest_price) AS price
FROM token_stats
GROUP BY token;

/*
  BTC	100000.0
  ETH	3050.0
*/

Когда я увидел примеры MV, вроде в целом стало понятно. Но сразу стали появляться вопросы:

1. В чем разница между MV+AggregatingMergeTree vs ReplacingMergeTree (см. предыдущий пункт)?

   Ответ: MV сдвигает "тяжелую" обработку на INSERT, облегчив SELECT. В случае ReplacingMergeTree "тяжелая" обработка приходится на этап мерджа (либо через FINAL в памяти либо OPTIMIZE TABLE на диске). В случае MV – на этапе вставки, что предпочтительнее для больших таблиц. Также, MV+AggregatingMergeTree хранит только конкретные стейты, а ReplacingMergeTree – все строчки, что требует больше места на диске.

   Также, FINAL нельзя использовать в других MV, поэтому тут только MV+AggregatingMergeTree. Лично мне больше нравится MV + AggregatingMergeTree (тут невозможно забыть про финальность), но для простоты и если данные просто селектами дергать, то можно и ReplacingMergeTree (главное не забыть FINAL). ReplacingMergeTree – если вся строчка нужна.

2. Когда срабатывает MV и что такое "работает на вставке" – это же просто селект. Из чего конкретно делается селект? Можно ли посмотреть всю source таблицу?

   То, что в MV в запросе выше latest_price – это не вся таблица, а только новые строчки, которые вставляются в рамках текущего батча. Всю таблицу напрямую посмотреть не получится, но можно сделать так:

   – Создаем еще одну MV на latest_price (например, latest_price_dup), которая будет просто дублировать latest_price.

   – В token_stats_mv делаем так

   WITH all_table AS (
     SELECT * FROM latest_price     -- новые строчки
     UNION ALL
     SELECT * FROM latest_price_dup -- старые строчки
   ),
   SELECT * FROM all_table  -- вся исходная таблица целиком

3. Можно ли создать MV, если исходная таблица уже заполнена данными?

   Можно, для этого используется модификатор POPULATE при создании MV. Но тогда не получится указать destination таблицу. А иногда бывает нужно. Тогда можно сделать копию исходной таблицы, создать на нее MV, сделать вставку из исходной таблицы в копию, MV отработает, потом удалить копию и пересоздать MV на оригинальную таблицу – теперь и данные в MV есть, и все готово к работе. В целом, POPULATE менее гибкие, поэтому я в итоге перестал их юзать.

Подробнее про MV почитать тут.

Производительность JOIN

Выглядят они так же как и в Postgres:

CREATE TABLE liquidity_events (
    timestamp DateTime,
    token String,
    amount UInt32
) ENGINE = MergeTree()
ORDER BY token;

CREATE TABLE token_prices (
    token String,
    price Float64
) ENGINE = MergeTree()
ORDER BY token;

-- заполняем таблицы данными

-- извлекаем все события, джойним цены и выводим объем.
SELECT le.timestamp, le.token, tp.price*le.amount AS volume
FROM liquidity_events le
  JOIN token_prices tp ON tp.token = le.token
WHERE le.timestamp >= '2025-04-30 00:00:00'

Кажется, что все просто, но на практике CH загружает в память ВСЮ правую таблицу (то есть token_prices), несмотря на то, что у нее есть индекс по token, т.е. запрос будет очень медленным! Когда я впервые столкнулся, то не мог понять, почему так медленно.

Решение – либо менять местами таблицы, либо по возможности максимально фильтровать правую таблицу ДО джойна (можно вынести в CTE для читаемости), например:

SELECT le.timestamp, le.token, tp.price*le.amount AS volume
FROM liquidity_events le
  JOIN (
        SELECT * FROM token_prices
        -- какое-то условие для уменьшения размера таблицы
        -- например тут, если нужны данные только по трехбуквенным токенам
        -- (искусственный пример для простоты)
        WHERE length(token) = 3
      ) tp ON tp.token = le.token
WHERE le.timestamp >= '2025-04-30 00:00:00'
  AND length(le.token) = 3 -- тут тоже сразу фильтруем левую таблицу

JOIN с неточным совпадением (ASOF JOIN)

Иногда между таблицами нет точного совпадения. Например, нам нужно найти объем в $ операции ликвидности, для этого нужно для каждой liquidity_events найти цену токена в момент события либо последнюю ближайшую (последний обмен). Тогда используется ASOF JOIN:

CREATE TABLE liquidity_events (
    timestamp DateTime,
    token String,
    amount UInt32
) ENGINE = MergeTree()
ORDER BY token;

CREATE TABLE token_prices_ext (
    token String,
    timestamp DateTime,
    price Float64
) ENGINE = MergeTree()
ORDER BY (token, timestamp);

-- заполняем таблицы данными

-- извлекаем все события, джойним цены и выводим объем.
SELECT le.timestamp, le.token, tp.price*le.amount AS volume
FROM liquidity_events le
   ASOF JOIN token_prices_ext tp ON tp.token = le.token 
                        AND tp.timestamp <= le.timestamp
WHERE le.timestamp >= '2025-04-30 00:00:00'

Для него один из ON условий должен обязательно совпадать (в данном случае token), а другой быть больше-равно или меньше-равно.

Как показано в предыдущем примере, CH загрузит token_prices_ext в память целиком (что очень долго), поэтому нужно ограничивать правую часть джойна. На практике это можно сделать эвристикой. Например, token_prices_ext содержит данные до декабря 2025, а в таблице liquidity_events будут события только до 1 июня (то есть мы можем исключить данные в token_prices_ext июнь-декабрь), поэтому можно ограничить даты справа.

Также, если предположить, что для всех событий ликвидности есть цена не далее чем 1 день назад, то можно ограничить даты token_prices_ext еще и слева:

...
ASOF JOIN (
    SELECT * FROM token_prices_ext
    WHERE 
      timestamp <= '2025-06-01 00:00:00' -- ограничение справа
      AND timestamp >= '2025-04-29 00:00:00' -- ограничение по дате слева
) tp ON ...

Пользуясь такими эвристиками, можно здорово ускорить запросы.

Мелочи, про которых захотелось рассказать

Все мы привыкли к CTE. Так вот, в CH они не инлайнятся, то есть будут выполнены каждый раз при использовании, в отличие от Постгреса. На это дело уже два года как висит PR. Так что, будьте внимательны.

Из очень удобного – можно в отличие от Постгреса делать так:

SELECT a*b AS computed
FROM ...
WHERE computed > 0   -- алиас можно сразу использовать в условиях

Инструментарий

Как установить CH. На Mac поставил без проблем через brew (только потом не мог найти папку с данными). Для запросов рекомендую DBeaver, только обновите драйвер до последней версии (сейчас 0.9.5), иначе очень вероятны странные ошибки.

Также классная штука – clickhouse-client. Это консольный клиент, с помощью которого можно работать даже по SSH.

# запрос для MacOS
/opt/homebrew/bin/clickhouse client  --host myhost --port 9000 --user myuser --password 'my_pass'

Самое приятное в clickhouse-client – то, что при выполнении длинного запроса, нажав пробел, можно увидеть детальный прогресс, метрики запроса, используемую память и т.д. Наверняка это можно вытащить через системные таблицы, но иметь это в UI очень удобно.

Для отладки медленных запросов, если добавить перед запросом "EXPLAIN indexes=1", то можно получить план выполнения. Ниже изображен пример плохого запроса – были просканированы все parts и гранулы, что говорит о том, что индексы не задействованы вообще.

Иногда бывает удобно просить LLM объяснить план.

Конечно, правильные индексы – в CH называются ordering/sorting keys – это основное, на что надо смотреть при дизайне таблицы. Есть доп фишка – projections – когда к готовой таблице можно добавить как бы еще один sorting key, при этом таблица на диске задублируется (тк. порядок данных уже будет другой) – про это можно почитать в блоге CH.

Заключение

В рамках статьи невозможно охватить много материала – Clickhouse поистину бесконечен. Я надеюсь, что статья позволит вкатиться в аналитику больших данных быстрее, чем это сделал я. Если будут комментарии, багфиксы или ПРы – буду благодарен. Всех с наступающим!

Минутка PR. Веду тг‑канал Web3 разработчик. Пишу небольшие заметки о задачах по блокчейну/крипте (Ethereum), которые решаю. Буду рад видеть среди подписчиков!