ClickHouse (реплика из PostgreSQL) → Superset

OLAP-ускоритель для тяжёлой аналитики (дата публикации: 19 июня 2025 г.)

1. Почему именно эта связка

OLTP не справляется c BI-нагрузкой. На больших порталах Bitrix24 десятки миллионов сделок и звонков быстро превращают «обычный» PostgreSQL в узкое горлышко: джойны по тысячам строк, оконные функции и свёртки за годовой период легко съедают CPU и блокируют рабочие транзакции. Решение — вынести «тяжёлые» аналитические запросы в ClickHouse 25.5: колоночное хранение, минимальные индексы, распараллеленный SIMD-сканер и компрессия LZ4 снижают latency в 50–100 раз для типичной витрины. (clickhouse.com)

Near-real-time без двойной ETL. Движок MaterializedPostgreSQL в ClickHouse создаёт таблицу-реплику из Postgres, делает первоначальный дамп и дальше применяет изменения по логическому слоту WAL — ни Kafka, ни Debezium, ни отдельный CDC-кластер. Таким образом «оперативные» CRUD остаются в Postgres, а аналитику мы снимаем с ClickHouse почти без задержек. (clickhouse.com)

2. Компоненты и роли

3. Подготовка окружения

3.1 Что ставим

Памятка: движок MaterializedPostgreSQL пока experimental — включаем флажок allow_experimental_materialized_postgresql_table = 1 в /etc/clickhouse-server/config.xml. (clickhouse.com)

3.2 Мини-docker-compose (DEV)

version: "3.9"
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: demo
      POSTGRES_PASSWORD: demo_pwd
      POSTGRES_DB: crm
    ports: ["5432:5432"]

  clickhouse:
    image: clickhouse/clickhouse-server:25.5
    volumes:
      - ./ch_config.xml:/etc/clickhouse-server/config.d/custom.xml:ro
    ports: ["8123:8123","9000:9000"]

  superset:
    image: apache/superset:5.1.0rc0
    env_file: superset.env
    depends_on: [clickhouse, postgres]
    ports: ["8088:8088"]

docker compose up -d — через минуту все сервисы готовы.

3.3 Права и настройки Postgres

-- В Postgres (crm)
CREATE ROLE ch_replica WITH REPLICATION LOGIN PASSWORD 'replica_pwd';
ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM SET max_replication_slots = 10;
SELECT pg_create_logical_replication_slot('ch_slot', 'pgoutput');

Restart — и база готова поставлять WAL-дельты.

4. Настройка потоков данных

4.1 Реплика в ClickHouse

SET allow_experimental_materialized_postgresql_table = 1;

CREATE DATABASE b24_pg ENGINE = MaterializedPostgreSQL
SETTINGS
  host = 'postgres',
  port = 5432,
  user = 'ch_replica',
  password = 'replica_pwd',
  dbname = 'crm',
  PostgreSQL_replication_slot_name = 'ch_slot';

ClickHouse создаст служебные таблицы materialized_postgresql_queue и запустит поток WAL-записей.

Проверяем очередь:

SELECT table_name, last_sync_timestamp
FROM system.materialized_postgresql_tables
WHERE database_name = 'b24_pg';

4.2 Индекс-ускорение

ALTER TABLE b24_pg.crm_deal
ADD PROJECTION p_by_stage
  (SELECT stage_id, amount, updated_at)
  ORDER BY (stage_id, updated_at);

Такая проекция режет скан при запросах «pipeline по stage за период» в 5–6 раз.

4.3 Дополнительный слой dbt (опция)

profiles.yml:

clickhouse_olap:
  target: prod
  outputs:
    prod:
      type: clickhouse
      schema: mart
      host: clickhouse
      port: 9000
      user: default
      password: ""

models/mart_sales/fct_deal_funnel.sql

{{ config(materialized='view') }}

SELECT
  toStartOfMonth(updated_at) AS month,
  stage_id,
  count() AS deals_cnt,
  sum(amount) AS pipeline
FROM b24_pg.crm_deal
GROUP BY month, stage_id
ORDER BY month DESC;

Запуск: dbt run --target prod.

Лайфхак: ClickHouse v25.5 поддерживает window-view с POPULATE, можно строить непрерывный агрегиат без dbt.

5. Трансформации и моделирование

5.1 TTL-менеджмент

ALTER TABLE b24_pg.crm_activity
  MODIFY TTL updated_at + INTERVAL 18 MONTH DELETE;

Сырые логи автоматом чистятся, не занимая диск.

6. Визуализация в Superset

6.1 Подключаем обе БД

1. Settings → Database Connections → +

   • Postgres DSN: postgresql://bi_user@postgres:5432/crm
   • ClickHouse DSN: clickhousedb://default@clickhouse:9000/b24_pg

2. Dataset → +

   • «crm_deal» (Postgres) — для детальных карточек.
   • «mart.fct_deal_funnel» (ClickHouse) — для тяжёлой сводки.

6.2 Переключение на лету

В Superset 5.1 появились Database Bindings: в настройки чарта добавляем «Database override» = ClickHouse → граф строится из OLAP-движка; меняем — и получаем оперативный срез из Postgres.

6.3 Time-series пример

Time Series Bar

• Datasource: mart.fct_deal_funnel (CK)
• Time: month
• Metrics: pipeline
• Series: stage_id

Refresh = 15 s — практически live.

7. Мониторинг и автоматизация

Авто-алёрт: если WAL-lag > 64 MB → NiFi/Slack «реплика отстаёт > 60 с».

8. Типичные ошибки и лайфхаки

1. «Replication slot” не создаётся — убедитесь, что wal_level=logical и пользователь в Postgres имеет REPLICATION.
2. CH говорит «unsupported type jsonb» — предварительно приводите поле к text или используйте PostgreSQLJSONEachRow формат.
3. Superset рушит JOIN в CK — указывайте /*+ distributed_product_mode='global' */ в SQL Lab, иначе по умолчанию local.
4. Запросы всё равно идут медленно — добавьте PRIMARY KEY (hash(cityHash64(id))) в таблице реплики — ключ не уникальный, но фокусирует скан.
5. Синхронизация схемы — MaterializedPostgreSQL не ловит ALTER TABLE … DROP COLUMN; ведите DDL через миграции Flyway и запускайте SYNC DATABASE b24_pg после апдейта.

9. Заключение

ClickHouse ↔ PostgreSQL c Superset-переключателем — быстрый способ отделить транзакции от аналитики, не меняя привычной схемы данных.

Плюсы

• Быстрее: 50× ускорение тяжёлых отчётов, пока Postgres остаётся «мастером».
• Просто: один флаг и движок MaterializedPostgreSQL, без Kafka.
• Гибко: Superset за секунду меняет источник; разработчики проверяют деталь, а менеджеры — агрегат.

Минусы

• Движок ещё experimental, не храните «золото» только в ClickHouse.
• DDL на Postgres требует ручного SYNC в ClickHouse.
• Реплика даёт нагрузку на WAL: \~1 MB/10 k UPDATE — рассчитывайте диск.

Когда масштабировать?

• 5 TB raw → разносите реплику по-шардово (Cluster AllReplicated).

• 10 k QPS аналитики → Materialized Views + pro-jections.

• Мультирегион → ClickHouse Keeper + Postgres logical replication в each region.

Полезные ссылки

• Materialized PostgreSQL engine
• ClickHouse Changelog 25.5
• Postgres → ClickHouse CDC tips (clickhouse.com)
• Superset 5.x Release Process

Пусть Postgres отвечает за точность, ClickHouse — за скорость, а Superset — за красоту ваших графиков. Данные работают, кофе остывает медленнее, чем выполняется запрос.