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

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

—

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

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

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

—

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

—

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

### 3.1 Что ставим

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

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

«`yaml
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

«`sql
— В 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

«`sql
SET allow_experimental_materialized_postgresql_table = 1;

Читай также: NiFi → Elasticsearch ↔ PostgreSQL → Power BI

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-записей.

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

«`sql
SELECT table_name, last_sync_timestamp
FROM system.materialized_postgresql_tables
WHERE database_name = ‘b24_pg’;
«`

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

«`sql
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`:

«`yaml
clickhouse_olap:
target: prod
outputs:
prod:
type: clickhouse
schema: mart
host: clickhouse
port: 9000
user: default
password: «»
«`

`models/mart_sales/fct_deal_funnel.sql`

«`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. Трансформации и моделирование

| Зона | Хранилище | Что лежит |
| ———— | ——————- | ———————————— |
| **Hot OLTP** | `PostgreSQL` | live CRUD, ≤ 7 дней |
| **Replica** | `ClickHouse.b24_pg` | полная копия, задержка < 2 c | | **Mart** | `ClickHouse.mart_*` | агрегаты, проекции, MV (Window View) | ### 5.1 TTL-менеджмент ```sql 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** (PG) | `pg_replication_slots.restart_lsn` vs `pg_current_wal_lsn()` | > 64 MB |
| **CH replication queue** | `system.materialized_postgresql_tables.queue_size` | > 100 000 |
| **CPU CK** | `system.metrics` → `Query/Insert` | > 80 % |
| **Disk** | `system.asynchronous_metrics` | 75 % |
| **Superset /health** | Prometheus exporter | latency > 500 мс |

Читай также: Kafka + Debezium → PostgreSQL → Superset

*Авто-алёрт:* если 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](/https://clickhouse.com/docs/engines/database-engines/materialized-postgresql)
* [ClickHouse Changelog 25.5](/https://clickhouse.com/docs/whats-new/changelog)
* [Postgres → ClickHouse CDC tips](/https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips-v2) ([clickhouse.com][6])
* [Superset 5.x Release Process](/https://github.com/apache/superset/discussions/27769)

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

[1]: https://clickhouse.com/docs/whats-new/changelog?utm_source=chatgpt.com «2025 Changelog | ClickHouse Docs»
[2]: https://clickhouse.com/docs/engines/table-engines/integrations/materialized-postgresql?utm_source=chatgpt.com «MaterializedPostgreSQL | ClickHouse Docs»
[3]: https://github.com/ClickHouse/ClickHouse/releases?utm_source=chatgpt.com «Releases · ClickHouse/ClickHouse — GitHub»
[4]: https://github.com/apache/superset/discussions/27769?utm_source=chatgpt.com «Release Process changes · apache superset · Discussion #27769»
[5]: https://clickhouse.com/docs/engines/database-engines/materialized-postgresql?utm_source=chatgpt.com «MaterializedPostgreSQL | ClickHouse Docs»
[6]: https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips-v2?utm_source=chatgpt.com «Postgres to ClickHouse: Data Modeling Tips V2»

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

Похожие записи: