ClickHouse 20.9: пробуем MaterializedPostgreSQL - PostgreSQL в ClickHouse через logical replication

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

Контекст: в ряде DWH-проектов у нас уже есть PostgreSQL как операционная база, и клиенты периодически хотят гонять аналитику прямо по ней. Кончается это, как правило, одинаково - тяжёлые GROUP BY со сканом десятков миллионов строк начинают мешать OLTP-нагрузке, все друг друга блокируют, и дальше начинается разговор про читающие реплики или отдельный аналитический контур. MaterializedPostgreSQL - один из ответов на этот вопрос, и нам было интересно посмотреть, насколько рабочий.

Как это устроено

Механика стандартная для logical replication: в PostgreSQL нужен wal_level = logical, движок создаёт replication slot, получает initial snapshot таблицы и дальше применяет WAL-события через replication protocol. ClickHouse хранит данные в своём формате, на запросы отвечает своим движком.

Настройка на стороне PostgreSQL минимальная:

-- postgresql.conf
wal_level = logical

-- Права для пользователя репликации
GRANT SELECT ON TABLE orders TO clickhouse_replication;
ALTER ROLE clickhouse_replication REPLICATION;

Со стороны ClickHouse:

CREATE DATABASE pg_replica
ENGINE = MaterializedPostgreSQL('pg-host:5432', 'prod_db', 'clickhouse_replication', 'secret')
SETTINGS materialized_postgresql_tables_list = 'orders';

После этого ClickHouse делает начальный снапшот и начинает слушать слот. Таблица появляется в pg_replica.orders и в неё непрерывно льются изменения из PostgreSQL.

Что получилось на стенде

Стенд: PostgreSQL 13 с таблицей orders на ~30 миллионах строк, имитация операционной нагрузки - INSERT/UPDATE через pgbench с умеренной интенсивностью. Смотрели на latency репликации и на разницу во времени аналитических запросов.

Latency репликации оказалась около секунды при умеренной нагрузке - INSERT на PostgreSQL появляется в ClickHouse примерно через 0.8-1.2 секунды. Это не zero-latency и для операционных задач не подходит, но для аналитики, где запросы смотрят на данные с задержкой хотя бы нескольких минут, это абсолютно приемлемо.

Аналитические запросы - вот где стало интереснее. Типичный запрос: агрегация по статусам заказов с группировкой по дате и категории товара, без особо хитрых фильтров, просто полный скан с агрегацией. На PostgreSQL 13 такой запрос занимал несколько секунд даже на относительно свежей статистике. На ClickHouse тот же запрос - меньше 200 миллисекунд. Разница больше чем в 20 раз. Это ожидаемо с учётом колоночного хранения и векторного выполнения ClickHouse, но видеть это на реальных данных всё равно приятно.

Ограничения, которые надо иметь в виду

Движок новый, и это чувствуется. Несколько вещей, которые мы нашли за время тестирования:

• PRIMARY KEY обязателен. Без первичного ключа в PostgreSQL таблица реплицироваться не будет. Это разумно - ClickHouse использует PK для применения UPDATE и DELETE, иначе он просто не знает, какую строку менять. Но если в схеме PostgreSQL есть таблицы с составным ключом через ограничение, а не через PRIMARY KEY, придётся смотреть внимательнее.
• DDL не реплицируется. Изменения схемы в PostgreSQL не передаются в ClickHouse автоматически. ALTER TABLE на PostgreSQL - и нужно пересоздавать базу в ClickHouse или как минимум пересоздавать таблицу. Для стабильных схем это не проблема, для активно развивающихся - уже вопрос.
• Replication slot остаётся. Если ClickHouse упал и не вычитал слот, WAL в PostgreSQL не будет очищаться. На занятой базе это потенциально означает рост pg_wal и проблемы с дисковым пространством. Нужно мониторить pg_replication_slots и лаг консьюмера.
• Экспериментальный статус. В релизных нотах 20.9 движок помечен как experimental. Это не запрет на использование, но сигнал что на production без тщательного тестирования лезть не стоит.

Где это имеет смысл

По итогам стенда - история рабочая для конкретного сценария: PostgreSQL как операционная база, нужны аналитические запросы по свежим данным без нагрузки на prod. MaterializedPostgreSQL даёт это практически без операционных затрат на настройку ETL - не нужно писать ETL-пайплайн, поддерживать расписание, думать об инкрементальной загрузке. Репликация работает сама.

Для схем с частыми DDL-изменениями или без первичных ключей - пока нужны обходные пути. Для аналитических задач по стабильной схеме - стоит смотреть серьёзно.

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