PostgreSQL 14 Beta 3: тестируем row-level filtering в logical replication на партиционированной БД

В феврале, когда мы переводили клиентские DWH на PostgreSQL 13, в разделе про логическую репликацию была честная фраза: «репликацию отдельных подмножеств строк делаем через ETL с явной фильтрацией - в логической репликации row-level фильтрация на уровне публикации не поддерживается, пункт в бэклоге». PostgreSQL 14 Beta 3 вышла на этой неделе, и этот пункт из бэклога, похоже, закрывается.

Рассказываем что именно появилось и как мы это уже смотрим на стенде.

Что такое row-level filtering в публикациях

До 14-й версии публикация (CREATE PUBLICATION) работала по принципу «всё или ничего» на уровне таблицы. Можно было включить в публикацию таблицу целиком или не включать вообще. Если нужна была только часть строк - скажем, только записи с status = 'active' или только за последние N месяцев - это приходилось решать снаружи: триггерами, отдельными представлениями или ETL-слоем.

В PostgreSQL 14 в CREATE PUBLICATION и ALTER PUBLICATION появился параметр WHERE. Теперь можно писать так:

CREATE PUBLICATION sales_active
  FOR TABLE orders
  WHERE (status = 'confirmed' AND region_id = 1);

На подписчике прилетят только строки, удовлетворяющие условию. Если строка меняется так что перестаёт подходить под фильтр - на подписчике придёт DELETE. Если строка появилась или изменилась и начала подходить - INSERT.

Ограничения, о которых важно знать сразу: в WHERE нельзя использовать подзапросы, пользовательские функции и нестабильные выражения (CURRENT_TIMESTAMP, random() и т.п.). Только стабильные выражения по столбцам самой таблицы. Для наших задач это разумно - мы и не хотим нестабильных условий в репликации.

Наш кейс: партиционированная OLTP-таблица

У одного из клиентов схема такая: основная OLTP-база с несколькими крупными таблицами, партиционированными по дате. DWH - отдельный сервер, куда нужно реплицировать только часть данных: определённый регион и только записи в терминальных статусах (completed, cancelled). Промежуточные статусы в DWH не нужны - аналитика работает с завершёнными транзакциями.

Раньше это решалось ETL-джобом, который каждые N минут тянул дельту по updated_at с фильтрацией. Схема рабочая, но с задержкой и с накладными расходами: запрос на источнике, трансформация, вставка на приёмнике. Плюс отдельный мониторинг за тем что джоб живой и не отстаёт.

Логическая репликация из 13-й версии позволяла избавиться от ETL и получить near-realtime, но без фильтрации таблица целиком - а это значит весь мусор промежуточных статусов и все регионы. На DWH нет смысла держать данные регионов, которые там просто не нужны.

Что мы делаем на стенде

Поставили PostgreSQL 14 Beta 3 на отдельный хост, восстановили слепок клиентской базы, поднял реплику-подписчика.

Публикация с фильтром для партиционированной таблицы:

-- на источнике (publisher)
CREATE PUBLICATION dwh_feed
  FOR TABLE orders
  WHERE (region_id = 1 AND status IN ('completed', 'cancelled'));

Важный момент с партиционированием: публикация применяется к партиционированной таблице как целому, фильтр работает корректно на всех партициях автоматически. Не нужно отдельно прописывать публикацию для каждой orders_2021_q1, orders_2021_q2 и так далее - это было бы неудобно.

На стороне подписчика:

-- на подписчике (subscriber)
CREATE SUBSCRIPTION dwh_sub
  CONNECTION 'host=source-host port=5432 dbname=oltp user=replication password=...'
  PUBLICATION dwh_feed;

После подписки прошла начальная синхронизация - только строки, подходящие под фильтр. Объём данных примерно в три раза меньше полной таблицы (что и ожидалось с учётом доли нужных статусов и одного региона из нескольких).

Первые наблюдения

Начальная синхронизация прошла корректно - на подписчике только нужные строки, без лишних регионов и промежуточных статусов. Проверяли через COUNT(*) с теми же условиями на источнике и на приёмнике - сошлось.

Поведение при изменении статуса - тестировали вручную: обновили несколько строк на источнике, переводя их из processing в completed. На подписчике появились INSERT именно для этих строк. Обратное: строки с completed поменяли на processing - на подписчике пришёл DELETE. Семантика работает так как написано в документации.

Партиционирование и фильтр - проблем не увидели. Вставка в актуальную партицию, фильтрация, репликация - всё идёт нормально. Специально тестировали вставки в старые партиции (исторические корректировки данных) - тоже реплицируется правильно.

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

Про SQL/JSON Path - коротко

Вторая заметная вещь в Beta 3 - улучшенный синтаксис SQL/JSON Path. PostgreSQL 14 продолжает работу по приближению к стандарту SQL/JSON: новые методы в path-выражениях, улучшенная обработка ошибок через ? (suppress errors). Для нас это менее горячо - JSON-поля в DWH мы обычно разворачиваем на этапе загрузки, а не на уровне репликации. Но если у вас полудокументная схема с jsonb-полями - стоит посмотреть отдельно.

Где сейчас

Стенд работает третий день, инцидентов нет. Производительность репликационного слота смотрим через pg_stat_replication и pg_replication_slots. Лаг пока в пределах нескольких секунд при умеренной нагрузке на источник.

До GA нагружать это в production не будем - бета есть бета. Но кейс выглядит убедительно: одна строка WHERE в публикации заменяет ETL-джоб с мониторингом и периодическими задержками. Когда выйдет GA, у нас уже будет готовый playbook для этого клиента.

Ссылка на работы по DWH и аналитике.