MaterializedPostgreSQL в ClickHouse 21.9: аналитическая реплика без ETL

ClickHouse 21.9 вышел в середине сентября, и в нём появилось то, чего клиенты с PostgreSQL-бэкендом просили давно: движок MaterializedPostgreSQL. Идея простая - подключить ClickHouse напрямую к работающей PostgreSQL через logical replication и получить аналитическую реплику в реальном времени без отдельного ETL-пайплайна. Мы взяли это в работу на одном из клиентских стендов. Рассказываем что получилось.

Зачем это вообще нужно

Классическая схема для аналитики поверх транзакционной базы выглядит примерно так: PostgreSQL (OLTP) -> ETL-джоб (Airflow/dbt/самописный скрипт) -> ClickHouse (OLAP). Работает, но у схемы есть цена: ETL надо поддерживать, мониторить, отлаживать при изменениях схемы, и данные в ClickHouse всегда с каким-то лагом - обычно от нескольких минут до часа в зависимости от частоты запуска.

MaterializedPostgreSQL предлагает другой подход: ClickHouse сам подписывается на WAL PostgreSQL через logical replication (тот же механизм, что используется между PostgreSQL-серверами) и реплицирует данные напрямую. Никакого промежуточного слоя, никаких джобов - данные едут сами по мере появления.

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

Как это настраивается

На стороне PostgreSQL требования стандартные для logical replication: wal_level = logical, пользователь с привилегиями репликации,Publication. ClickHouse берёт на себя создание Subscription и начальную синхронизацию.

-- PostgreSQL: создаём publication
CREATE PUBLICATION ch_pub FOR ALL TABLES;

-- PostgreSQL: пользователь с нужными правами
CREATE ROLE ch_replicator REPLICATION LOGIN PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ch_replicator;

Со стороны ClickHouse создаётся база с движком MaterializedPostgreSQL:

CREATE DATABASE pg_replica
ENGINE = MaterializedPostgreSQL('postgres-host:5432', 'source_db', 'ch_replicator', '...')
SETTINGS materialized_postgresql_tables_list = 'orders,products,customers';

После этого ClickHouse начинает начальный снапшот (через COPY на стороне PostgreSQL) и затем переходит в режим потоковой репликации по WAL. На стенде с базой объёмом около 20 ГБ начальная синхронизация заняла порядка 20-30 минут - зависит от железа и сети.

Что реально получается по лагу

Самый важный вопрос для аналитики в реальном времени - какой лаг между изменением в PostgreSQL и появлением данных в ClickHouse.

На нашем стенде при умеренной OLTP-нагрузке (несколько сотен транзакций в секунду) лаг держится в районе нескольких секунд. При пиковой нагрузке - вставках батчами по несколько тысяч строк - лаг вырастал до 10-30 секунд и затем возвращался. Для большинства аналитических задач это абсолютно приемлемо.

Но важно понимать механику: ClickHouse читает WAL через реплика-слот на стороне PostgreSQL. Этот слот держит WAL на диске до тех пор, пока ClickHouse не подтвердит применение. Если ClickHouse недоступен или отстаёт - слот накапливает WAL и PostgreSQL не может его удалить. На занятой базе это может быстро съесть дисковое пространство.

Мониторинг слота - обязателен. Добавили в алерты:

-- PostgreSQL: смотрим размер слота
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots
WHERE active = false OR pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824;

Слот размером больше гигабайта при живом ClickHouse - уже повод смотреть что происходит.

Ограничения, которые реально важны

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

Поддерживаются не все типы данных. ARRAY, JSONB, UUID, NUMERIC - поддерживаются (с нюансами маппинга типов). Кастомные PostgreSQL-типы, hstore, постгресовые ENUM со сложными вариантами - могут потребовать внимания.

Первичный ключ обязателен на каждой реплицируемой таблице. В PostgreSQL это не всегда так - бывают таблицы с UNIQUE-индексом вместо PK или вообще без явного идентификатора строки. Для MaterializedPostgreSQL это проблема.

Один поток репликации - все таблицы из одной базы идут через одно соединение. При большом объёме изменений это может быть узким местом.

Обновление данных в ClickHouse - через ReplacingMergeTree под капотом. Удаление строк реплицируется корректно, но физически данные удаляются с задержкой при мерже. Запросы типа SELECT count(*) могут давать чуть завышенные числа на незамёрженных данных, пока не добавишь FINAL.

Сравнение с ETL-подходом

Если честно - для зрелых продуктовых сценариев ETL через dbt/Airflow пока даёт больше контроля: можно делать трансформации, управлять схемой на стороне ClickHouse независимо от PostgreSQL, легче справляться с изменениями схемы источника. MaterializedPostgreSQL подходит для сценария «мне нужна почти-live копия PostgreSQL-таблиц в ClickHouse для аналитических запросов» без серьёзных трансформаций.

Мы видим его как хороший вариант для небольших и средних баз где схема относительно стабильна и нужен минимальный лаг. На крупных DWH со сложными трансформациями ETL-пайплайн никуда не девается.

Где сейчас

Стенд работает вторую неделю на клиентской базе объёмом около 30 таблиц. Из замеченного: перезапуск ClickHouse корректно возобновляет репликацию с последней подтверждённой позиции WAL - данные не теряются. DDL-изменения пока не прилетали, но мы намеренно не меняем схему источника пока не убедимся в штатной работе.

Параллельно смотрели на ksqlDB как вариант для потоковой аналитики через Kafka между PostgreSQL и ClickHouse - это отдельная история с другими трейдоффами, может расскажем отдельно.

Работы по аналитической инфраструктуре - в рамках DWH и бизнес-аналитики.