Как мы сократили объем данных в 10 раз, не повредив пользовательскому опыту, или переезд Postgres → ClickHouse

По умолчанию ClickHouse использует для сжатия алгоритм zstd

Это в ClickHouse Cloud. В обычном LZ4 по умолчанию. В Yandex Cloud не в курсе. zstd в среднем экономит ~30% диска по сравнению с lz4, но больше кушает проц.

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

Тут странное написано. Партиции не для этого, а для манипуляции данными (чтобы, например, по TTL дропать целиком месяц и т.п.). Если вы сделаете одну партицию, а на первое место в order by добавите ключ, по которому были партиции, то запросы станут быстрее. Вы этого, скорее всего, не заметите - заметно только если партиций очень много. Файлы в память не загружаются. Наоборот на каждый файл открывается буфер 2МБ, соответственно больше файлов - больше памяти. Но опять же это заметно только если очень много партиций (например, у вас партиции по дням, данные за много лет, много колонок, и вы хотите вставить/прочитать данные, в которых есть все даты). Не то чтобы я рекомендовал вам что-то менять в таблице, просто этот абзац вводит в заблуждение.

date_created Date32,
hour_created Int16,

Мелочь, но наверное, вам хватит Date и Int8.

allow_nullable_key = 1

лучше уберите это. Однажды может очень больно выстрелить в ногу. Это хотели выпилить вовсе, но почему-то не выпилили.

После перехода на ClickHouse запрос стал выполняться 20-25 минут.

Попробуйте в конец запроса дописать settings optimize_aggregation_in_order = 1;. *В clickhouse есть countIf().

Для оркестрации этого процесса используется Temporal

Как использовался Temporal, как писали для него workflows, как интегрировали с бэкендом? Ни слова в статье. Temporal сам по себе довольно монстроузен, было бы интересно почитать подробнее как и для чего вы его использовали.

Пытались поступить аналогично, но не получилось. В итоге имеем данные как в CH, так и в PostgreSQL, который в некоторых случаях обращается в CH через pg_clickhouse через FDW. Объемы примерно такие же. Если самая крупная таблица не намного больше терабайта, то все в сумме - порядка 7ТБ.

Если очень упрощённо, то помешали две проблемы.

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

Во-вторых, много запросов, которые CH выполняет неэффективно. Если на данных из статьи, это будет, например, тоже упрощенно, вычислить медианное время между последовательными операциями одного user_id по date_created+hour_created в пределах одно и того же channel_id и в группировке по block_kind_id. Если не упрощённо, то добавляются фильтрации, например, по логнормальной квартильной функции от разницы между date_created+hour_created между последовательными операциями в указанном выше разрезе. В таких случаях, возможности PostgreSQL по индексации и ручному параллелизму через dblink на несколько десятков ядер позволяют выполнять на нем такие запросы заметно быстрее, чем на CH.

P.S. Занимаемые объёмы на СХД в нашем случае не важны, так как экономический эффект на порядки превышает затраты на хранение данных. А вот производительность играет решающую роль.

CDC — новые изменения переносятся из исходной СУБД в целевую (может работать неограниченно долго).

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

Тоже была похожая проблема:
Одна команда внедряла фичу по генерации квартальных и полугодовых отчётов вещания плееров и увидели что фоновые джобы для крупных клиентов отлетали по таймауту в 1 час. Поняли что постгря не вывозит запрос для данных за квартал и тем более за полгода ("удивительно", не правда ли?).
Задачу на поиск решения делегировали в мою core команду. В итоге решили вытаскивать домен истории вещания из монолита в отдельный сервис.
Перекос нагрузки в сторону записи - ~3k записей в секунду. Чтение очень редкое - ~5 чтения в час. Решение нужно было довольно быстрое и с минимальными телодвижениями. Выбрали как раз "репликацию" из Postgres (партиционированный по дням) в ClikckHouse через PeerDB.

Итог:
На самых больших клиентах полный флоу генерации отчёта (запрос данных + постобработка + генерация xlsx/csv + сохранение в S3) выполняется ~3.5 минуты.
Нагрузочные тесты на хайлоад кластере показали что такой вариант имеет запас на рост ~x2.5 по нагрузке.
Сейчас этого хватает с головой и даёт время на выбор решения получше. Хотим уйти от Postgres + PeerDB + ClickHouse к ClickHouse + надёжный буфер, так как запись в ClickHouse хорошо работает с большими батчами в минимум 10к+ строк.