PostgreSQL 12 beta: тестируем декларативное партиционирование на 500 ГБ

У нас уже несколько месяцев живёт задача: аналитическая база на PostgreSQL 11, таблица событий около 500 ГБ, и партиционирование по месяцам, которое работает - но с нюансами. Нюансы в том, что partition pruning при планировании вёл себя непредсказуемо: на части запросов планировщик всё-таки лез во все секции, хотя по условию должен был читать две. Добавили enable_partition_pruning = on, поставили правильные индексы - в целом работает, но осадок остался.

Когда PostgreSQL core team объявила о начале бета-фазы PostgreSQL 12 с упором на улучшенное партиционирование и генерируемые столбцы, мы решили поднять тестовый стенд и проверить на реальных данных. Не на синтетическом бенчмарке, а на дампе той самой таблицы событий.

Что делали в PostgreSQL 11 и где болело

Декларативное партиционирование появилось в PostgreSQL 10, в 11-й версии его заметно улучшили - добавили поддержку партиций в UPDATE/DELETE, индексы на родительской таблице стали автоматически создаваться на дочерних. Но был список случаев, когда планировщик принимал не самые разумные решения.

Конкретно наш кейс: таблица events разбита по RANGE на месячные партиции, 18 секций за полтора года. Запрос с фильтром вида WHERE event_time >= '2019-01-01' AND event_time < '2019-03-01' - планировщик должен читать две партиции, но при определённых условиях (вложенный подзапрос, JOIN с небольшой таблицей) он строил план с обходом всех 18 секций. С EXPLAIN ANALYZE видно - Seq Scan на партициях, которых в условии нет.

Обходили через constraint_exclusion = on - это старый механизм, появившийся до декларативного партиционирования и работающий через проверку CHECK-ограничений. Грязный костыль, но помогал.

Стенд PostgreSQL 12 beta

Развернули на отдельных машинах с идентичными параметрами: те же ресурсы, тот же postgresql.conf с параметрами работы, те же данные. Из важного: PostgreSQL 12 убрал constraint_exclusion как основной механизм для партиционированных таблиц - теперь там отдельный, переработанный partition pruning на уровне планировщика, и enable_partition_pruning включён по умолчанию.

Заливали дамп таблицы в партиционированную схему обеих версий. В 12-й пересоздавали схему уже без CHECK-ограничений на дочерних таблицах - они там больше не нужны для pruning.

Что изменилось по-настоящему

Partition pruning при планировании. Это главное. В PostgreSQL 12 pruning происходит на этапе планирования для большинства условий - не только при WHERE event_time = ..., но и при подзапросах и JOIN-ах, где значение диапазона можно вычислить статически. Запросы, которые в 11-й версии стабильно обходили все секции при наличии JOIN, в 12-й стали корректно выбирать нужные. EXPLAIN показывает только те партиции, которые входят в диапазон запроса.

Пример EXPLAIN на запросе за два месяца:

EXPLAIN SELECT count(*), event_type
FROM events
WHERE event_time >= '2019-01-01' AND event_time < '2019-03-01'
GROUP BY event_type;

На PostgreSQL 11 без костылей: план обходил 18 партиций. На PostgreSQL 12 beta: план содержит только 2 партиции - events_2019_01 и events_2019_02. Всё остальное вырезается на этапе планирования, ещё до выполнения.

Runtime pruning. Отдельная история - pruning при выполнении, когда значение фильтра становится известно только в runtime (например, через параметр или подзапрос). В 12-й это тоже работает. Не все случаи, но заметно больше чем раньше.

Генерируемые столбцы. Это отдельная фича, к партиционированию напрямую не относится, но нам пригодилась. Декларируешь столбец как GENERATED ALWAYS AS (выражение) STORED - PostgreSQL сам его вычисляет и хранит при вставке. В нашем случае использовали для нормализации временной метки до начала месяца - раньше это делал триггер или приложение.

ALTER TABLE events
ADD COLUMN event_month date
GENERATED ALWAYS AS (date_trunc('month', event_time)::date) STORED;

Партиционирование по этому столбцу работает, partition key может ссылаться на генерируемый столбец. Немного чище чем держать дополнительное поле и заботиться о его заполнении в каждом INSERT.

Что не проверяли и что насторожило

Это beta, и несколько вещей мы намеренно не тестировали в боевых условиях. Во-первых, hash partitioning - для нашей задачи он не нужен, но в 12-й версии его тоже улучшили. Во-вторых, производительность при очень большом количестве партиций (50+) - по документации там изменили внутреннее представление в памяти, должно стать лучше, но у нас 18 секций и разницы мы бы просто не увидели.

Насторожила одна вещь: в beta мы поймали один странный план на сложном запросе с тремя JOIN-ами - планировщик выбрал nested loop там, где в 11-й был hash join, и запрос оказался медленнее. Возможно, это проблема конкретного бета-билда и статистики, возможно - регрессия. Дополнительно посмотрим на финальных релиз-кандидатах.

Промежуточный итог

Для нашей конкретной боли - непредсказуемый partition pruning в 11-й версии - PostgreSQL 12 beta уже сейчас выглядит как решение без костылей. Декларативное партиционирование на 500 ГБ с 18 секциями ведёт себя так, как должно вести: нужные секции читаются, лишние пропускаются, план выглядит разумно.

На продакшн это, очевидно, не идёт - beta есть beta. Но тестовый стенд убедил, что при финальном релизе обновление на 12-ю версию для аналитической базы имеет смысл планировать. Проекты по DWH и аналитике, где живут аналогичные задачи с партиционированием, ставим в список на оценку миграции.