Postgres и ClickHouse для DWH: когда что выбрать и внедрить

Какая проблема решается при замене коммерческого DWH

Коммерческое DWH часто становится «налогом на рост». Чем больше данных, пользователей и отчетов, тем быстрее растут лицензии, стоимость расширения и зависимость от конкретного вендора. В итоге изменения в модели данных или новые витрины превращаются в согласования, закупки и ожидание окон обслуживания, а не в работу с бизнесом.

Замена обычно нужна не ради моды на open source, а чтобы вернуть контроль. Компания хочет сама решать, где хранить данные, как масштабироваться, что оптимизировать и за что именно платить: за инфраструктуру и поддержку, а не за каждое ядро или дополнительный модуль.

От новой платформы чаще всего ждут трех вещей: предсказуемой скорости аналитики, прозрачности (понятно, почему запрос быстрый или медленный) и гибкости в развитии витрин. Еще один частый мотив - снижение рисков. Проще менять подрядчиков и не зависеть от того, «закрыта» ли критичная функция лицензией.

Почему часто выбирают связку из двух СУБД, а не одну? Потому что у DWH обычно две разные задачи. Первая - аккуратно собирать, чистить, сверять и выдавать «истину» по справочникам и фактам, где важны транзакционность и ограничения. Вторая - быстро считать тяжелые аналитические запросы по большим объемам, где важны колоночное хранение и эффективные агрегации. Postgres и ClickHouse хорошо закрывают эти роли по отдельности.

Ожидания стоит сразу скорректировать: магии не будет. Если источники «шумные», нет владельцев данных, не согласованы определения метрик, а загрузки идут без контроля качества, то любая связка будет давать спорные цифры и нестабильную производительность. Open source DWH выигрывает, когда есть дисциплина: правила по схемам, версиям витрин, мониторинг загрузок и понятный процесс изменений.

Postgres и ClickHouse: простое разделение ролей

Связка Postgres + ClickHouse дает предсказуемый результат, если заранее договориться о ролях. Тогда вы не пытаетесь заставить одну СУБД делать все сразу и реже ловите сюрпризы по скорости и стоимости хранения.

Postgres обычно становится «входной зоной» и местом, где живут правила. Сюда удобно грузить сырые данные (staging), приводить типы, чистить дубликаты, проверять ключи, вести историю изменений справочников. Он хорош там, где важны транзакции, ограничения и понятная логика обновлений.

ClickHouse берет на себя аналитику: большие сканы, группировки, фильтры по времени, широкие таблицы фактов и быстрые отчеты для BI. Ему проще, когда данные уже подготовлены и разложены так, чтобы читать много строк и меньше «думать» во время запроса.

Типовой поток выглядит так: источники (1С, CRM, биллинг, логи) попадают в staging в Postgres, там проходят нормализацию и контроль качества, затем формируются факты и витрины и загружаются в ClickHouse. BI и аналитики в основном читают ClickHouse, а Postgres остается «пультом управления»: хранит метаданные и помогает объяснить, откуда взялись цифры.

Даже если аналитика в ClickHouse, в Postgres часто логично оставить справочники и их версии (контрагенты, филиалы, товары), метаданные загрузок (что, когда, из какого источника), расписания и статусы задач, правила и результаты контроля качества, а также служебные таблицы для сверок и аудита.

Простой пример: сеть филиалов каждый день присылает продажи. В Postgres вы фиксируете, что файл или пакет получен, проверяете полноту (все ли филиалы отчитались), нормализуете коды товаров. В ClickHouse храните факты продаж в широком виде и считаете отчеты по дням, категориям и регионам за секунды.

Витрины данных: когда Postgres, когда ClickHouse

Витрина данных - не просто таблица «для аналитиков». Это продукт для бизнеса: она отвечает на конкретный набор вопросов, у нее есть владелец, и она обновляется по расписанию, которое всем понятно.

Хорошая витрина обычно включает только то, что нужно для решений, а не «все на всякий случай». Полезно заранее зафиксировать определения полей и метрик (что такое «выручка», «активный клиент», «дата продажи»), гранулярность (по дню, по чеку, по клиенту) и ключи для соединений. Также важно понимать источник данных и режим обновления (инкремент или пересчет) и иметь базовый контроль качества: проверки сумм, пропусков и дублей.

Postgres удобнее, когда витрина живет рядом с операционными данными или требует частых мелких изменений: дописывать строки, обновлять статусы, хранить справочники, поддерживать целостность. Типичный пример - витрина «справочник клиентов + текущий статус договора», где записи могут обновляться много раз в день.

ClickHouse чаще выигрывает, когда витрина в основном читается: большие выборки, группировки, расчеты по периодам, сравнение сегментов и отчеты «в разрезах». Например, витрина продаж по чекам на сотни миллионов строк, где основная нагрузка - агрегаты по дням, магазинам и категориям.

В связке Postgres и ClickHouse часто работает простое правило: справочники и «текущие состояния» остаются в Postgres, а факты и тяжелая аналитика уходят в ClickHouse.

С витринами легко переборщить. Появляются десятки похожих таблиц без владельца, и никто не знает, какая «правильная». Лучше держать ограниченное число витрин и управлять ими как продуктами: назначать владельца, описывать вопросы, на которые витрина отвечает, документировать определения и источники, а дубли объединять или закрывать. Изменения должны проходить через понятный процесс, а не «по просьбе в чате».

Агрегаты и предрасчеты: где они дают максимальный эффект

Агрегаты - это заранее посчитанные итоги, которые повторяются изо дня в день: выручка по дням, количество операций по неделям, показатели по филиалам или продуктам. В связке Postgres и ClickHouse они часто дают больший выигрыш, чем любые настройки, потому что убирают самые дорогие группировки из пользовательских запросов.

Агрегаты почти обязательны, когда одни и те же отчеты запускают часто, а исходные таблицы быстро растут. Например, руководители каждое утро смотрят продажи по филиалам за вчера, за неделю и с начала месяца. Если каждый раз группировать миллиарды строк, даже быстрый движок будет тратить время и ресурсы, а на пиках начнет «проседать».

Считать на лету разумно, если запрос редкий, данные небольшие или срез постоянно меняется (ad-hoc аналитика). Тогда предрасчет часто превращается в лишнюю витрину, которую нужно поддерживать и объяснять.

Чтобы предрасчеты не стали вечной болью, продумайте инкрементальный пересчет. Пересчитывайте только новый период, а не всю историю. Обычно данные бьют по дате, и каждый день пересчитывается только вчера плюс небольшой «хвост», если возможны поздние корректировки.

Рабочий баланс держится на простых правилах: делайте агрегаты под 3-5 самых популярных отчетов, храните только реально используемую детализацию (день, филиал, продукт), фиксируйте формулы показателей, добавляйте небольшой период пересчета назад, если данные могут исправляться.

Чем больше агрегатов, тем выше скорость и тем выше цена поддержки. Лучше меньше, но понятных и регулярно используемых.

Аналитические запросы: что будет быстро, а что нет

Связка Postgres и ClickHouse хорошо работает, когда вы заранее понимаете характер запросов. Два похожих на вид отчета могут вести себя по-разному: один считается за секунды, другой упирается в диски или сеть.

ClickHouse обычно выигрывает там, где нужно прочитать много строк и быстро посчитать итог. Типичные случаи: фильтры по времени (день, неделя, месяц), топы по продажам или событиям, воронки (шаг 1 -> шаг 2 -> шаг 3), когортный анализ по датам первого действия. Его сильная сторона - большие сканы и агрегации по колонкам.

Postgres чаще предсказуем для точечных выборок и небольших связок. Например, когда вы открываете карточку клиента или устройства и подтягиваете 5-20 связанных записей, или когда нужно аккуратно соединить несколько небольших справочников с фактами, а объем данных умеренный.

Практическое правило выбора движка под запрос:

• ClickHouse: много строк, мало возвращаемых данных, много GROUP BY/COUNT/SUM по периодам.
• Postgres: точечные фильтры по ключам, частые небольшие JOIN, транзакционные проверки и правки.
• С осторожностью: JOIN двух больших таблиц без заранее продуманного ключа и порядка данных.
• С осторожностью: запросы со сложной построчной бизнес-логикой вместо агрегирования.
• Всегда заранее: проверьте, какие поля реально фильтруют BI-пользователи.

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

Чтобы не гадать, соберите реальные запросы до внедрения: выгрузите логи из BI, попросите 10-20 самых важных отчетов, зафиксируйте фильтры (особенно по времени) и ожидаемое время ответа. Затем прогоните их на тестовом объеме и только после этого решайте, что считать в ClickHouse, а что оставить в Postgres.

Требования к дискам, памяти и сети: как считать без гадания

Для связки Postgres и ClickHouse железо лучше считать от задач, а не от «сколько данных сейчас в базе». В DWH объем обычно растет из нескольких слоев: сырье (staging), витрины и агрегаты, плюс ретеншн и бэкапы.

Упрощенная прикидка по объему:

• Сырье: средний размер строки x строк в день x дней хранения.
• Витрины: часто 20-80% от сырья (зависит от нормализации и дублей).
• Агрегаты: нередко 5-30%, но при предрасчетах «на все случаи» может стать больше витрин.
• Служебное: индексы и WAL в Postgres, части и слияния (merge) в ClickHouse (часто плюс 20-50%).
• Рост: минимум +30% и отдельно место под бэкапы.

Диск: объем важен, но скорость часто важнее. Критичные места - WAL и индексы в Postgres, а также фоновые слияния и вставки в ClickHouse. Если диск медленный, вы увидите «пилу» по задержкам: загрузка вроде идет, но запросы становятся медленнее в моменты слияний. В таких узких местах обычно выигрывают быстрые SSD/NVMe и раздельные диски под разные роли.

Сеть ломает ожидания, когда начинаются массовые загрузки, репликация, распределенные запросы и бэкапы. Частая ошибка - считать только «сколько ГБ в день», забывая про пики: ночные догрузки, пересчет витрин и перенос данных между Postgres и ClickHouse. Если сеть узкая, время загрузки растягивается, и окно ночных расчетов начинает «съедать» рабочий день.

Память и CPU тоже могут стать лимитом. В ClickHouse группировки, сортировки и JOIN иногда резко поднимают потребление памяти. В Postgres важны кэш, work_mem и параллелизм. CPU упирается на сложных агрегациях и при высокой конкуренции пользователей.

Практика простая: закладывайте запас и меряйте на пилоте. Проверьте на реальных запросах скорость загрузки и пересчета витрин, задержки запросов на фоне (merge, vacuum), запас сети на репликацию и бэкапы, а также деградацию при росте данных хотя бы в 3-5 раз.

Если пилот делаете на серверах «боевого» класса, результаты будут ближе к реальности, чем тест на случайной виртуалке. Например, для on-prem сценария это могут быть стойки уровня S200 Series от GSE.kz.

Данные и пайплайны: что продумать до первой загрузки

Большинство проблем в DWH начинается не с выбора Postgres или ClickHouse, а с того, как вы договорились о данных. Если до первой загрузки не зафиксировать базовые правила, дальше придется разбирать расхождения в отчетах и бесконечно переделывать витрины.

Сначала определите каркас схемы. Нужны стабильные ключи (не зависящие от названия, статуса или филиала), единые справочники и понятные правила по времени. Например, что такое «день»: по часовому поясу источника, по времени головного офиса или по UTC? Даже показатель «выручка за вчера» может меняться, если разные системы режут сутки по-разному.

Что зафиксировать заранее

• Единый идентификатор сущностей: клиент, договор, точка продаж, устройство.
• Общие справочники (организации, филиалы, статусы) и кто их владелец.
• Соглашения по времени: часовой пояс, календарь, «бизнес-день», задержки закрытия.
• Границу ответственности: что считается в источнике, что в ETL/ELT, что в витрине.
• Правила изменений: как обрабатываются исправления задним числом.

Дальше решите режим загрузки: batch или near real-time. «Почти онлайн» часто хотят по привычке, но реально он нужен только для ограниченного набора витрин (мониторинг очередей, fraud-сигналы, оперативные KPI). Для финансовой и управленческой отчетности обычно достаточно пакетной загрузки раз в час или раз в день. Так проще контролировать качество и стабильность.

Контроль качества должен быть встроен в пайплайн, а не делаться вручную после жалоб. Минимальный набор: дубликаты по ключам, пропуски обязательных полей, задержки поступления, сверки итогов с источниками (хотя бы по суммам и количествам).

Логика расчетов и наблюдаемость

Версионируйте правила расчета показателей: кто поменял формулу, когда, для каких дат она действует. Полезная практика - хранить код трансформаций и миграции схемы вместе и выпускать изменения как релиз.

Наблюдаемость нужна с первого дня: время загрузки, объемы, число ошибок, доля опоздавших данных, время выполнения ключевых запросов. Тогда сбои видны сразу, а не в момент, когда руководителю срочно нужен отчет.

Пошаговая схема внедрения связки Postgres + ClickHouse

Связку Postgres и ClickHouse лучше внедрять не как «большую миграцию за раз», а как серию коротких, проверяемых шагов. Так вы рано видите, где узкие места (диск, сеть, модель данных) и не строите систему «на глаз».

Начните с конкретики: какие отчеты и метрики должны работать в первый день и какая задержка приемлема (5 минут, час, сутки). Зафиксируйте не только список отчетов, но и типичные фильтры: по филиалу, по менеджеру, по периоду, по продукту. Именно они определяют, где нужны агрегаты и как партиционировать данные.

Дальше разложите данные по источникам: откуда берутся, как часто обновляются, что критично (например, финансовые операции), где возможны «поздние» события. Это сразу задает правила загрузки, дедупликации и контроля качества.

Рабочая последовательность обычно такая:

1. Зафиксировать список приоритетных отчетов и критерии успеха: время ответа, свежесть, точность.
2. Описать источники и обновление: расписание, объемы, какие поля могут меняться задним числом.
3. Выбрать слои и владельцев: staging для сырья, core для согласованных сущностей, marts для витрин, и назначить ответственных.
4. Сделать пилот на одном домене (например, продажи): собрать витрину в Postgres, тяжелые срезы и группировки вынести в ClickHouse, замерить узкие места.
5. Переносить витрины по очереди и выключать старые куски DWH только после параллельной проверки цифр.

На пилоте измеряйте три вещи: скорость загрузки, скорость типовых запросов и «стоимость ошибок» (сколько времени уходит на пересчет, если пришли исправления). Если отчет по сети филиалов часто режут по датам и городам, проверьте партиции по времени и ключи распределения заранее. Иначе ClickHouse может быть быстрым на демо и медленным в реальности.

Финальный шаг - план переключения: период параллельного расчета, сверка ключевых метрик и понятный «откат», если в проде всплывет расхождение.

Типовые ошибки внедрения и как их избежать

Самая частая ошибка при переходе на Postgres и ClickHouse - сложить все данные в одну огромную таблицу и дальше «пусть аналитики разбираются». В итоге нет понятных витрин, нет ответственных, и любой новый отчет превращается в ручную работу и споры о том, какие цифры правильные.

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

Частые причины поломок отчетов

Отдельная боль - время и часовые пояса. Если в одной системе время хранится в локальном поясе, в другой в UTC, а в витрине оно «как пришло», отчеты по дням начнут прыгать. Например, филиалы закрывают день в 20:00 по местному времени, а в аналитике часть продаж уезжает на следующий день.

Не менее опасна непродуманная стратегия обновлений. Postgres нормально чувствует себя с точечными обновлениями и справочниками, а в ClickHouse частые апдейты строк обычно дорогие и могут тормозить запросы. Лучше проектировать загрузку так, чтобы в ClickHouse преобладали вставки и переигровка партиций, а исправления шли через пересчет кусками, а не через бесконечные мелкие правки.

Как избежать проблем на практике

Перед запуском договоритесь о базовых правилах: у каждой витрины есть владелец и описание метрик, время хранится единообразно (часто UTC) и отдельно выделено локальное время, есть понятный план обновлений (что пересчитываем целиком, что инкрементально, где допустима задержка), настроены мониторинг загрузок и качества (свежесть, контроль сумм, аномалии), а также есть «золотой» отчет, который сравнивается с эталоном после каждого изменения.

Если внедряете DWH в организации с филиалами, начните с одной витрины (например, продажи по дням и точкам), доведите ее до стабильности и только потом масштабируйте подход на остальные домены. В проектах системной интеграции (в том числе у GSE.kz) именно дисциплина витрин, времени и контроля качества чаще дает больший эффект, чем попытки «ускорить» все сразу.

Короткий чеклист перед запуском в прод

Перед запуском важнее всего договориться о смыслах и границах системы. Даже сильная связка Postgres и ClickHouse начнет «сыпаться», если разные команды по-разному считают одну и ту же метрику или не понимают, где искать нужные данные.

Начните с отчетов. Составьте короткий список ключевых дашбордов и выгрузок, которые реально используют: финансовые итоги, продажи, остатки, SLA, нагрузка по филиалам. Для каждого пункта зафиксируйте определения метрик (например, «выручка» - по оплате или по отгрузке; «активный клиент» - за 30 дней или за календарный месяц) и владельца, который подтверждает изменения.

Дальше проверьте архитектуру слоев: где хранится «сырье», где витрины, где агрегаты. Должно быть понятно, какой источник является единственным «истинным» для каждого поля и где именно его можно использовать. Частая ошибка - когда витрина в Postgres внезапно начинает подтягивать данные из разных мест «как быстрее», и через месяц никто не может воспроизвести цифры.

Отдельно зафиксируйте ретеншн и правила хранения по слоям. Обычно сырье хранят дольше для переигровки загрузок, витрины - по бизнес-потребности, агрегаты - ровно настолько, насколько они ускоряют запросы. Если не определить сроки заранее, диски закончатся неожиданно, а «чистки» начнут ломать отчеты.

Проверьте качество данных и наблюдаемость загрузок: есть ли проверки на полноту, дубликаты и «прыжки» метрик, есть ли алерты на опоздание загрузок и резкое падение или рост объема, есть ли простой способ понять, какая таблица и какой шаг сломался.

И последнее - пилот с измерениями. На ограниченном, но реальном наборе данных замерьте рост диска по слоям, сетевой трафик между компонентами, время типовых запросов и время пересчета агрегатов. Если результаты сильно хуже ожидаемого, это почти всегда сигнал, что нужно менять гранулярность витрин, схему партиционирования или стратегию предрасчетов, а не «добавлять железо наугад».

Пример сценария: отчетность для сети филиалов

Представьте сеть из 30-100 филиалов. Каждый день руководству нужны одни и те же цифры: выручка, количество чеков, средний чек, топ товаров, динамика по неделям. Источники обычно разные: кассы, CRM, склад, справочник филиалов и номенклатуры. Данные приходят с задержками, иногда задним числом меняются возвраты и статусы.

В связке Postgres и ClickHouse удобно разделить роли. Postgres держит staging (куда грузятся сырые выгрузки) и справочники: филиалы, категории, менеджеры, правила группировки. Там проще контролировать качество, фиксировать версии справочников и делать понятные проверки. ClickHouse хранит готовые отчетные витрины, где важна скорость чтения и агрегаций.

Практичный набор агрегатов для такого кейса - дневные продажи по филиалу и категории, а отдельно по товару для топов. Тогда отчеты вроде «вчера vs прошлый год» или «топ-20 категорий по региону» читаются из небольших таблиц, а не пересчитывают миллионы строк чеков.

Обновления лучше строить так, чтобы не пересчитывать все заново. Частый подход: ежедневная загрузка новых данных и пересчет только последних N дней (например, 7-14), потому что именно там чаще всего прилетают корректировки и возвраты. Остальная история считается стабильной и не трогается.

На пилоте важно проверить не только «самый быстрый график», а повторяемость результата: время 5-10 типовых запросов (по филиалу, региону, категории, топы), длительность ежедневной загрузки и пересчета последних N дней, поведение при одновременных запросах от нескольких руководителей, скорость «догоняния», если загрузка задержалась, и то, как вы увидите ошибку до того, как отчет уйдет в рассылку.

Если инфраструктура планируется on-prem, заранее прикиньте, где будет стоять ClickHouse (ему важны быстрые диски и стабильная сеть), и кто будет отвечать за поддержку 24/7. В Казахстане такие проекты часто делают на своих серверах и с локальным сервисом - здесь может пригодиться опыт системного интегратора вроде GSE.kz.

Следующие шаги: пилот, инфраструктура и поддержка

Чтобы связка Postgres + ClickHouse не превратилась в «еще один недоделанный DWH», начните с короткого пилота на реальных отчетах, которые сегодня болят: какие таблицы читают, какие фильтры используют, где ждут минутами.

Выберите 1-2 домена для пилота (например, продажи и остатки или финансы и платежи) и возьмите срез данных хотя бы за 3-6 месяцев. Так быстрее станет понятно, какие витрины лучше держать в Postgres, а какие тяжелые агрегации и длинные аналитические запросы стоит отдать ClickHouse.

Сразу договоритесь о минимальных SLA, иначе «быстро» у всех разное. Достаточно двух цифр: задержка обновления данных (например, каждые 15 минут или раз в день) и время ответа ключевых отчетов (например, до 5 секунд на типовой фильтр).

Дальше - инфраструктура и эксплуатация. Планируйте емкость не «на сейчас», а с запасом на 12-24 месяца: рост данных, больше пользователей, новые витрины. Для ClickHouse часто упираются в диски (скорость и объем) и сеть между узлами, для Postgres - в IOPS на запись и надежные бэкапы.

Практичный план работ на 2-6 недель:

• Собрать 20-30 реальных запросов и список полей, которые должны быть в витринах.
• Зафиксировать SLA по задержке обновления и времени ответа отчетов.
• Прикинуть рост данных на 12-24 месяца и заложить резерв по дискам и памяти.
• Назначить ответственных: кто владеет витринами, кто следит за качеством данных, кто выдает доступы.
• Настроить базовую эксплуатацию: мониторинг, бэкапы, тест восстановления, расписание регламентов.

Если внутри команды не хватает рук на интеграцию и дежурство, имеет смысл подключить партнера. Например, GSE.kz как производитель и системный интегратор может помочь с подбором серверов, построением инфраструктуры и организацией круглосуточной поддержки.