Всем привет, меня зовут Сергей Прощаев. В этой статье расскажу про подводные камни индексации при переезде с классических реляционных баз в мир колоночных аналитических СУБД на примере PostgreSQL и ClickHouse.
Я Tech Lead и руководитель направления Java/Kotlin разработки в FinTech & E‑commerce, а ещё преподаю на курсах по разработке и архитектуре в OTUS. За последние пару лет я видел несколько проектов, где команда решила «ускорить аналитику», перетащив часть данных из PostgreSQL в ClickHouse. И почти всегда одно и то же: переносят схему один‑в‑один, создают привычные B‑tree индексы… и получают не ускорение, а головную боль.
Сегодня разберём пять конкретных ошибок при работе с индексами в ClickHouse, которые возникают из‑за привычек, унаследованных от PostgreSQL. И главное — покажу, как их исправить, опираясь на лучшие практики команд.
Представьте ситуацию
Вы — инженер данных или бэкенд‑разработчик. У вас есть боевой PostgreSQL, который отлично справляется с транзакциями. Но отчёт по продажам за последние три месяца выполняется 40 секунд, а дашборд руководителя грузится вечность. Логичное решение: вынести аналитическую нагрузку в ClickHouse — колоночную СУБД, оптимизированную для агрегаций и больших объёмов чтения.
Вы создаёте таблицу в ClickHouse, почти как в PostgreSQL. Добавляете индексы… и тут начинаются проблемы. Запросы не ускоряются, вставки тормозят, а некоторые индексы вообще игнорируются. Знакомо?
Я сам через это проходил. В одном проекте мы переносили логи платежей за два года. Сделали «правильный» первичный ключ как в PG — по id транзакции. И получили, что каждый SELECT COUNT(*) сканировал почти все гранулы. Пришлось перепроектировать, и разница была ощутима в разы.
Так что давайте сразу по кочкам.
Ошибка №1. Создавать в ClickHouse обычный B‑tree индекс
Симптом: Вы пишете CREATE INDEX idx_user ON clicks(user_id). ClickHouse выполняет команду, но в плане запроса видите Index type: none или Skip: 0. Запрос по user_id всё равно сканирует всю таблицу.
Почему возникает: В PostgreSQL B‑tree — это сбалансированное дерево, где каждый лист указывает на конкретную версию строки (ctid). ClickHouse же хранит данные колонками и не использует row‑level B‑tree индексы.
Вместо адресации отдельных строк ClickHouse хранит sparse primary index (разрежённый первичный индекс), который позволяет быстро найти диапазоны гранул (granules) с помощью бинарного поиска по sparse index marks, но после этого данные внутри гранулы всё равно сканируются.
Команда CREATE INDEX в ClickHouse создаёт не B‑tree, а так называемый data skipping index (индекс пропуска данных). Он помогает отсекать целые гранулы, но не даёт точечного доступа к строке.
К чему приводит: Инженеры тратят время на создание бесполезных индексов, получают ложное чувство защищённости. В итоге запросы всё равно медленные, а размер таблицы растёт из‑за дополнительных файлов.
Как исправить (best practice): Главный инструмент в ClickHouse — правильно спроектированный sort key (ORDER BY), поверх которого строится sparse primary index. Вторичные индексы бывают типов: minmax, set, bloom_filter, ngrambf_v1.
Пример правильного подхода:
-- ClickHouse: таблица с составным ключом сортировки CREATE TABLE events ( event_time DateTime, user_id UInt64, action String ) ENGINE = MergeTree ORDER BY (user_id, event_time) PRIMARY KEY (user_id, event_time);
Теперь запрос WHERE user_id = 123 будет эффективно отсекать гранулы, потому что данные физически сгруппированы по user_id.
Ошибка №2. Использовать первичный ключ для уникальности, а не для сортировки
Симптом: Вы объявляете PRIMARY KEY (id), потому что привыкли, что в PG первичный ключ — это уникальный идентификатор строки. В ClickHouse это работает, но запросы по диапазонам дат медленные.
Почему возникает: В PostgreSQL первичный ключ — это уникальный B‑tree индекс плюс ограничение. В ClickHouse первичный ключ не уникален и не гарантирует уникальность. На самом деле в движке MergeTree sparse primary index строится по выражениям PRIMARY KEY, которые обычно являются префиксом ORDER BY. Выражение PRIMARY KEY задаёт, какие колонки попадут в sparse index, а ORDER BY определяет физический порядок строк на диске.
Если ключ высококардинальный (например, id), то значения плохо группируются в гранулы — возникает poor locality и bad clustering. ClickHouse хуже отсекает диапазоны данных, и эффективность индекса падает.
К чему приводит: Данные не группируются логически. Запрос с фильтром по дате всё равно читает почти всю таблицу. При этом вставки работают, но эффективность по памяти и скорости низкая.
Как исправить (best practice): Выбирайте ORDER BY и PRIMARY KEY исходя из типовых запросов. В начало ORDER BY обычно ставят поля, которые:
чаще всего участвуют в фильтрации с равенством (
=илиIN),хорошо группируют данные (обеспечивают locality),
позволяют эффективно отсекать диапазоны гранул.
Низкая кардинальность сама по себе не является обязательным требованием — например, event_time часто хорошо работает в time-series сценариях, особенно если запросы фильтруются по временным диапазонам (хотя стоит помнить о возможной write amplification при монотонных вставках).
Если нужно добавить уникальный идентификатор, поставьте его в конец составного ключа. Для строковых измерений с ограниченным числом значений часто полезен тип LowCardinality, который снижает memory footprint и ускоряет обработку. Пример:
CREATE TABLE sales ( sale_date Date, product_id UInt64, amount Decimal(10,2), region LowCardinality(String) ) ENGINE = MergeTree PARTITION BY toYYYYMM(sale_date) ORDER BY (region, sale_date, product_id) -- сначала регион, дата, потом продукт PRIMARY KEY (region, sale_date); -- более короткий ключ для sparse индекса
Ошибка №3. Игнорировать порядок полей в ORDER BY
Симптом: Вы задали ORDER BY (product_id, sale_date), а потом часто ищете WHERE sale_date BETWEEN ... AND ... без указания product_id. Запрос всё равно медленный.
Почему возникает: ClickHouse использует разрежённый первичный индекс как указатель на гранулы. Наибольший эффект для pruning (отсечения гранул) обычно дают поля, стоящие левее в ORDER BY, особенно если они участвуют в фильтрации по равенству.
К чему приводит: Инженеры удивляются, почему индекс вроде бы есть, а план запроса показывает granules: 1000 / 1000. Потому что данные не отсортированы по полю фильтрации в пределах каждой гранулы.
Как исправить (best practice): Проанализируйте, какие поля чаще всего участвуют в WHERE с равенством (= или IN). Поставьте их самыми левыми в ORDER BY. Затем добавьте поля с диапазонами (>, <, BETWEEN). Поля с высокой кардинальностью, но хорошей кластеризацией, тоже могут стоять слева.
Реальный кейс: в проекте логирования мы долго мучились с запросами по event_time. Поменяли ORDER BY (user_id, event_time) на ORDER BY (event_time, user_id) — и скорость выборки за последний час выросла в 10 раз.
Ошибка №4. Не использовать партиционирование или использовать его как в PostgreSQL
Симптом: Вы создали партиции по дням (PARTITION BY toYYYYMMDD(event_date)), но запросы за месяц всё равно медленные. Или наоборот, сделали партицию по году, но таблица огромная.
Почему возникает: В PostgreSQL партиции — это физические дочерние таблицы, и их может быть не очень много (обычно сотни). В ClickHouse партиции — это отдельные директории на диске. Технически ClickHouse способен работать с тысячами партиций, но большое количество партиций и частей (parts) быстро создаёт operational overhead: растёт metadata, ухудшается merge, могут быть проблемы с памятью.
К чему приводит: Слишком мелкие партиции (например, по часам) порождают тысячи частей и убивают производительность вставок. Слишком крупные (по году) не дают удобно удалять старые данные.
Как исправить (best practice): Партиционирование в ClickHouse — это в первую очередь средство для управления данными (удалять старые партиции, переносить в S3), а не для ускорения запросов. Для ускорения используйте ORDER BY. Но если данные очень большие (терабайты), можно разбить по месяцам или неделям, сохраняя разумный баланс: слишком крупные партиции ухудшают lifecycle management, слишком мелкие — создают excessive amount of parts.
Пример для данных с retention 90 дней:
CREATE TABLE events ( event_date Date, user_id UInt64, payload String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) -- по месяцам (12 партиций в год) ORDER BY (event_date, user_id); -- Старые партиции удаляем: ALTER TABLE events DROP PARTITION '202501'
Ошибка №5. Неправильно выбирать между skip-индексами и материализованными представлениями
Симптом: Вы добавили bloom_filter индекс на поле url для быстрого поиска по точному совпадению. Но запрос всё равно читает много гранул.
Почему возникает: Skip-индексы в ClickHouse работают только на уровне гранул. По умолчанию размер гранулы ориентирован примерно на 8192 строки, но при adaptive index granularity фактический размер может отличаться. Если фильтр не очень селективный, индекс может сказать «пропустить эту гранулу? нет» — и всё равно прочитает её. Для высокоселективных фильтров (редкие значения) skip-индекс эффективен. Для частых — нет.
К чему приводит: Инженеры надеются, что bloom_filter заменит B‑tree, и разочаровываются. Либо создают материализованные представления (MV) там, где достаточно было просто правильно отсортировать данные.
Как исправить (best practice): Разделите задачи. Если вам нужно часто искать редкие значения (например, лог ошибки по коду) — используйте bloom_filter с явным указанием вероятности ложноположительного срабатывания. Например:
ALTER TABLE events ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 3;
0.01 — допустимый false positive rate (1%). GRANULARITY 3 означает, что один блум-фильтр строится на 3 гранулы.
Если нужно агрегировать по частому значению — лучше подойдёт ORDER BY с этим полем в начале.
Если же запрос сложный, и вам нужны предрасчитанные агрегаты — используйте материализованные представления (Materialized View). Но осторожно: MV в ClickHouse имеют сложную operational семантику:
они работают как
INSERT-триггерный пайплайн;нет автоматического обновления при изменении исходных данных (no automatic refresh);
при добавлении данных в исходную таблицу MV обновляется атомарно, но backfill (добавление исторических данных) требует ручного вмешательства;
возможны дубликаты при повторной вставке, нет exactly‑once семантики «из коробки».
Поэтому MV в ClickHouse — мощный, но требующий аккуратного проектирования инструмент. Они не являются аналогом материализованных представлений в PostgreSQL или Oracle.
Правильный пример для агрегации с uniq (количество уникальных пользователей). Важно: для неаддитивных агрегаций нужно использовать AggregatingMergeTree и комбинаторы -State / -Merge:
-- Создаём MV с агрегатными состояниями CREATE MATERIALIZED VIEW daily_stats ENGINE = AggregatingMergeTree ORDER BY event_date AS SELECT event_date, countState() AS total_events, uniqState(user_id) AS unique_users FROM events GROUP BY event_date; -- Запрос к MV: сначала делаем SELECT с *Merge SELECT event_date, countMerge(total_events) AS total_events, uniqMerge(unique_users) AS unique_users FROM daily_stats GROUP BY event_date;
Это гарантирует корректное слияние частичных состояний. Использовать SummingMergeTree с uniq() — ошибка, которая приведёт к неверным результатам.
Структурные различия
Чтобы закрепить понимание, посмотрите на схему ниже. Она показывает, чем отличаются хранение и индексация в PostgreSQL и ClickHouse (рис. 2).
Схема показывает принципиальную разницу в индексации между PostgreSQL и ClickHouse. В PostgreSQL B‑tree индекс указывает на конкретную строку (heap tuple) — это позволяет точечно находить одну запись. В ClickHouse sparse primary index указывает не на строку, а на целую гранулу данных (блок ~8192 строк), после чего все строки внутри гранулы сканируются. Следовательно, в ClickHouse нет смысла искать «одну запись» — эффективно работают только диапазонные запросы и агрегации по группам.
Вывод: B‑tree в PostgreSQL позволяет точечно найти строку. В ClickHouse sparse primary index помогает пропустить целые гранулы, но внутри гранулы данные сканируются. Поэтому так важны порядок сортировки и селективность фильтров.
Теперь — как принять решение, какой тип индекса использовать в ClickHouse (рис. 3).
Схема помогает принять решение: нужен ли дополнительный индекс (data skipping index) в ClickHouse или достаточно правильно настроить ORDER BY. Если поле фильтрации стоит в начале ORDER BY — дополнительный индекс не требуется. Если нет — смотрим на кардинальность: для редких значений подойдёт bloom_filter или set, для частых — лучше использовать материализованное представление или агрегацию «на лету» без индекса.
Вывод: сначала пытайтесь решить фильтрацию через ORDER BY. Если не получается — для редких значений ставьте bloom_filter, для частых пересматривайте модель данных или используйте MV с AggregatingMergeTree.
Что в итоге и при чём тут открытый урок?
Я специально не стал углубляться в тонкую настройку каждого параметра — это тема отдельного разговора. Главный вывод: не тащите привычки PostgreSQL в ClickHouse. Вместо B‑tree — проектируйте sort key (ORDER BY) и используйте sparse primary index. Вместо уникальных первичных ключей — группируйте данные логически. Вместо надежды на универсальный индекс — комбинируйте партиции, skip-индексы и материализованные представления, помня об их ограничениях.
Эти ошибки я сам совершал в начале пути. И каждый раз меня выручало не количество индексов, а понимание, как ClickHouse физически хранит и читает данные.
Чтобы понять, насколько вы уже готовы работать с ClickHouse на уровне проектирования, индексов, партиционирования и оптимизации запросов, можно пройти бесплатное вступительное тестирование. Оно поможет оценить текущий уровень и увидеть, какие темы стоит подтянуть перед более глубоким изучением ClickHouse.
Перед тестом можно присмотреться к открытым урокам. Они бесплатные, проходят в живом формате: преподаватели-практики покажут подходы на конкретных примерах, объяснят, где PostgreSQL-логика ломается в ClickHouse, и ответят на вопросы.
2 июня в 20:00 — «Ты — индекс в Postgres, Я — индекс в ClickHouse. Мы — разные» Разберём различия в механизмах индексации PostgreSQL и ClickHouse и покажет, как они влияют на проектирование схем и производительность запросов.
18 июня в 20:00 — «Интеграция ClickHouse и dbt»
Расскажем, как использовать dbt в ETL/ELT-процессах вместе с ClickHouse: от базовых принципов до практической интеграции.
А чтобы не пропускать новые разборы, открытые вебинары и материалы по разработке, аналитике и инфраструктуре, подписывайтесь на канал OTUS в MAX.