PostgreSQL 10 в DWH: нативное партиционирование и логическая репликация вместо триггерного зоопарка

В августе мы гоняли PostgreSQL 10 beta на стенде и пришли к выводу: логическая репликация в ядре - это именно то, чего не хватало для нормальных миграций. В октябре вышел GA. Сейчас февраль, и мы наконец сделали то, что откладывали последние полгода: перевели аналитическую базу одного из клиентов с PostgreSQL 9.6 на 10.

Расскажем что было, что не пошло по плану и почему нативное партиционирование оказалось важнее, чем мы думали.

Что за база и зачем трогать

DWH-инсталляция: PostgreSQL 9.6 на выделенном железе, схема с несколькими крупными таблицами фактов - продажи, события, логи транзакций. Суммарно около 800 ГБ живых данных. Партиционирование там было реализовано через старый добрый inheritance: родительская таблица, дочерние по кварталам, триггер BEFORE INSERT который смотрит на дату и делает INSERT в нужную дочернюю. Схема рабочая, но с характером.

Проблем накопилось несколько. Первая - триггер маршрутизации жил в PL/pgSQL и требовал поддержки при добавлении новых партиций: забыл добавить ветку в IF-ELSIF - данные молча падают в дефолтную таблицу или вообще вываливаются с ошибкой. Вторая - pg_dump выгружает и родительскую, и все дочерние таблицы как отдельные объекты, и при восстановлении нужно следить за порядком. Третья - планировщик 9.6 иногда не применял constraint exclusion на запросах с параметрическими условиями, и аналитические запросы неожиданно сканировали все партиции. Последнее в 9.6 чинилось настройкой, но всё равно неприятно.

Итого: живёт, но надо потрогать руками при каждом изменении схемы, и это раздражало команду клиента.

Стратегия миграции: логическая репликация как лифт

Стандартный pg_upgrade для 800 ГБ - это несколько часов даунтайма минимум. Для аналитической базы которую ETL-пайплайн пишет ночью, а днём читает BI-система, несколько часов - уже проблема.

Мы взяли схему с логической репликацией, которую отработали на стенде в августе:

1. Поднять новый кластер PostgreSQL 10 на соседнем железе.
2. Создать на нём целевую схему уже с нативным PARTITION BY.
3. Настроить PUBLICATION на источнике 9.6 и SUBSCRIPTION на 10.
4. Дать репликации догнать источник.
5. Остановить ETL, дождаться нуля отставания, переключить приложения на новый хост.

Ключевой момент: логическая репликация между 9.6 и 10 работает. Источник должен быть 9.4+ с wal_level = logical, приёмник - PostgreSQL 10. У нас 9.6, всё условие выполнено.

На новом кластере таблицы фактов создали сразу с нативным партиционированием:

CREATE TABLE sales (
    sale_id     bigint,
    sale_date   date        NOT NULL,
    amount      numeric,
    ...
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2017_q4
    PARTITION OF sales
    FOR VALUES FROM ('2017-10-01') TO ('2018-01-01');

CREATE TABLE sales_2018_q1
    PARTITION OF sales
    FOR VALUES FROM ('2018-01-01') TO ('2018-04-01');

Никакого триггера. Маршрутизация INSERT-ов - движок сам.

Нюансы которые вылезли

Первый сюрприз - начальная синхронизация. При создании SUBSCRIPTION PostgreSQL делает COPY данных из источника. 800 ГБ по локальной сети заняли около восьми часов. Это надо закладывать в план: логическая репликация не означает «быстро поднял и сразу переключил».

Второй - DDL не реплицируется. Это мы знали с бета-тестов, но в реальном сценарии значит: любое изменение схемы в период синхронизации надо делать вручную на обеих сторонах. Мы договорились с клиентом что в период миграции schema freeze - никаких ALTER TABLE без согласования. Продержались.

Третий - партиционированные таблицы нельзя включить в publication напрямую в PostgreSQL 10. Это неочевидное ограничение: publication на партиционированную таблицу в PostgreSQL 10 не публикует изменения в дочерних партициях. Пришлось либо включать в publication каждую дочернюю партицию отдельно, либо на стороне подписчика принимать данные в непартиционированные staging-таблицы и потом перекладывать. Мы выбрали первый путь: список дочерних таблиц в CREATE PUBLICATION явный, немного процедурно, но работает.

Вот как выглядел CREATE PUBLICATION на источнике:

CREATE PUBLICATION dwh_migration FOR TABLE
    sales_2015_q1, sales_2015_q2, ...,
    sales_2017_q3, sales_2017_q4;

Некрасиво, зато предсказуемо.

Четвёртый - индексы. Subscription создаёт таблицы без индексов, индексы надо создавать на приёмнике отдельно. На 800 ГБ это тоже время - несколько часов на CREATE INDEX CONCURRENTLY по каждому нетривиальному индексу. Всё это делали заранее, до переключения.

Переключение

ETL-пайплайн останавливается в 02:00 и до 06:00 не пишет - окно как раз есть. В 02:15 остановили запись, проверили отставание репликации через pg_stat_subscription, подождали несколько минут пока сошло до нуля. В 02:30 переключили connection string в конфиге BI-системы на новый хост, проверили что дашборды открываются и данные свежие. В 02:45 объявили победу.

Итоговый даунтайм с точки зрения BI - примерно пятнадцать минут в ночное окно. Для нашего случая это отлично.

Что получили на новой схеме

Нативные партиции в PostgreSQL 10 ведут себя заметно приятнее чем inheritance + триггер:

• Добавление новой партиции - один CREATE TABLE ... PARTITION OF. Никакого редактирования триггера.
• Constraint exclusion работает стабильнее на параметрических запросах - планировщик стал корректнее отсекать ненужные партиции.
• EXPLAIN на запросе к партиционированной таблице теперь читается как нормальный SQL, а не как дерево наследования с неочевидными именами.

ETL при этом не заметил ничего: INSERT в родительскую таблицу маршрутизируется автоматически, интерфейс не изменился.

Где стоим

PostgreSQL 10 на DWH/BI-проектах - это теперь наш новый базовый выбор для новых инсталляций. Для существующих - мигрируем по возможности. Логическая репликация как инструмент переключения работает: процедура понятна, управляемые риски, даунтайм в пределах ночного окна.

Ограничение publication на партиционированные таблицы - неудобство, с которым можно жить. Надеемся что в следующих минорных версиях это поправят, но пока обходим через явный список дочерних таблиц.

Следующий кандидат на миграцию - ещё один клиентский PostgreSQL 9.5. Там объём поменьше, зато схема сложнее. Посмотрим.