ClickHouse или PostgreSQL: 100 млн событий меняют выбор базы

Для отчётов и аналитических панелей оставляйте PostgreSQL, пока запросы укладываются в целевую задержку и не мешают транзакциям. Если после настройки широкие сканирования и одновременные запросы регулярно нарушают эти цели, добавление ClickHouse становится обоснованным. Архитектурные рекомендации ClickHouse предлагают сохранять PostgreSQL системой транзакционной записи, измерять пределы его аналитической нагрузки и переносить тяжёлые отчёты поэтапно.

В материале Aunimeda описан клиентский отчёт по конверсионной воронке за 30 дней на более чем 100 млн событий: после переноса аналитики время сократилось с 47 до 0,8 секунды; отдельно приведён стендовый тест таблицы из 100 млн строк, в котором запрос воронки за 7 дней занял 47,1 секунды в PostgreSQL и 0,82 секунды в ClickHouse. У клиентского отчёта и стендового запроса разные временные окна. Их результаты показывают возможный выигрыш на конкретной нагрузке, а не числовую границу, после которой любой проект должен менять базу.

Как объём связан с задержкой

Количество строк важно вместе с долей данных, которую читает запрос. Отчёт с узким диапазоном дат или выборкой по клиенту может быстро работать и на большой таблице, если план использует подходящий индекс. Запрос, который при каждом открытии панели группирует значительную часть истории по меняющимся измерениям, создаёт совсем другую нагрузку. Для него размер прочитанного массива часто важнее общего размера базы.

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

Что может исправить PostgreSQL

Сначала разберите запросы, из-за которых панель не укладывается в цель. Документация PostgreSQL по EXPLAIN объясняет, что EXPLAIN ANALYZE показывает фактическое выполнение узлов плана, а сведения о буферах помогают оценить чтение данных. Так можно отличить лишнее чтение из-за плана или индекса от запроса, которому действительно требуется большая часть истории. Время EXPLAIN ANALYZE не включает передачу строк клиенту, поэтому задержку всей панели нужно измерять отдельно.

Если фильтры повторяются и выбирают небольшую часть таблицы, помогают подходящие индексы. Если набор показателей стабилен, часть работы можно перенести в предварительно рассчитанные сводки. У обоих решений есть цена: индексы занимают место и добавляют работу при записи, а сводки нужно обновлять с требуемой частотой. Читающая реплика может убрать отчёты с основного узла, но сохраняет строчное хранение PostgreSQL и собственное отставание от него.

Для отчётов, ограниченных временем, подходит секционирование по дате. Руководство PostgreSQL по секционированию описывает исключение секций по условиям запроса и предупреждает, что чрезмерное число секций увеличивает затраты на планирование и память. Если панель всякий раз охватывает всю историю, разделение таблицы по месяцам само по себе не сократит объём нужных ей данных. Проверять эффект стоит на рабочем диапазоне дат и с реальными фильтрами.

Когда ClickHouse оправдывает отдельный слой

ClickHouse полезен там, где отчётам приходится читать множество событий и агрегировать их по нескольким измерениям, особенно при одновременной работе панелей. Он хранит значения столбцов раздельно и может читать только нужные запросу столбцы. Физический порядок данных и модель таблицы также влияют на то, сколько информации потребуется обработать. Поэтому сравнение имеет смысл проводить на тех запросах, которые уже исчерпали разумные способы ускорения в PostgreSQL.

Проверять нужно не только время одного запуска. Сопоставьте задержку при обычной и пиковой параллельности, потребление ресурсов, скорость обновления панели и влияние на транзакционную запись. Если PostgreSQL после настройки выполняет эти требования, второе хранилище добавит работу без необходимого выигрыша. Если широкие сканирования продолжают нарушать цель, а ClickHouse на той же аналитической задаче её достигает, разделение нагрузок получает измеримое основание.

Цена переноса данных и свежести

Отдельная аналитическая база требует первоначальной загрузки истории и доставки последующих изменений. Для постоянно обновляемой копии обычно используют захват изменений из журнала PostgreSQL. Конвейеру нужны наблюдение, восстановление после сбоев и место для данных в обеих базах. Если потребитель остановится, отставание копии вырастет; удержание записей журнала может увеличить расход места на стороне PostgreSQL.

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

Стоимость решения включает не только серверы двух баз. В расчёт входят хранение копии, первоначальная загрузка, передача изменений, контроль задержки, исправление расхождений и сопровождение аналитической модели. Чем строже требование к свежести и чем чаще меняются исходные записи, тем больше работы требует этот слой. Для отчётов, которые допускают задержку и много читают, такая цена может окупаться; для небольших выборок с жёсткой привязкой к текущему состоянию транзакций она обычно менее оправданна.

Правило выбора для панелей

Оставляйте PostgreSQL, если запросы ограничены предсказуемыми фильтрами, параллельная нагрузка невелика и панель вместе с транзакционным приложением выполняет свои требования к задержке. Когда цель нарушена, сначала устраните лишнее чтение и оцените индексы, секционирование, сводки или реплику. После каждого изменения сравнивайте результат при той параллельности, ради которой панель создавалась.

Добавляйте ClickHouse, когда широкие аналитические запросы после этих мер всё ещё перегружают PostgreSQL либо реплика не справляется с нужной скоростью и свежестью. При этом PostgreSQL остаётся местом транзакционной записи, а в ClickHouse попадают данные для отчётов, которым действительно нужен отдельный слой чтения. Граница выбора проходит там, где измеренное улучшение панелей оправдывает перенос изменений, двойное хранение и постоянную проверку корректности.

Читайте также:

• Контент-план для TikTok: поиск и аналитика теперь меняют темы после публикации
• Google запросила выпуск до 64 млн комаров — решения EPA пока нет
• Опрос в Одноклассниках: четыре настройки меняют смысл результата