PostgreSQL 12 Beta 1: тестируем на крупном DWH - CTE больше не материализуются по умолчанию

PostgreSQL Global Development Group выкатила 12 Beta 1 в конце мая, мы подождали несколько недель и в середине июля подняли стенд. Не на синтетике - на копии самой большой клиентской базы из тех, что ведём в рамках DWH и аналитики. База под триста гигабайт, партиционирована по месяцам, несколько сотен миллионов строк в таблице фактов, поверх - BI с дашбордами и регулярные выгрузки через аналитические запросы с CTE.

Тестировали три вещи: улучшения партиционирования, генерируемые столбцы и CTE inline. Самым интересным оказалось третье - в том смысле, что заставило нас серьёзно поработать руками.

Партиционирование: стало лучше, но не радикально

В PostgreSQL 11 мы уже перешли на декларативное партиционирование и выжали из него основное. В 12-й версии авторы доделали несколько важных вещей.

Индексы на партиционированных таблицах. Наконец-то можно создать индекс на родительской таблице - он автоматически появится на всех существующих партициях и на новых при добавлении. В 11-й версии этого не было: приходилось создавать индексы отдельно на каждой секции, и при добавлении новой партиции - не забывать про индекс вручную. Скрипт добавления партиций у нас это учитывал, но это была ручная работа. Теперь достаточно одного CREATE INDEX на родителе.

SELECT с FOR UPDATE на партиционированных таблицах перестал ругаться. В 11-й версии такой запрос в ряде сценариев не работал - обходили через подзапросы. Проверили на стенде - теперь работает нормально.

Производительность прунинга при большом числе секций. У клиента таблица партиционирована помесячно за пять с лишним лет - получается больше 60 секций. На 11-й версии partition pruning на планировании с таким количеством секций давал заметный overhead на построение плана. В 12-й переписали алгоритм - EXPLAIN показывает что прунинг при планировании стал быстрее. Разница в реальном времени выполнения небольшая, но приятная.

Генерируемые столбцы: удобно, но не везде

GENERATED ALWAYS AS - синтетические столбцы, которые вычисляются из других столбцов и хранятся физически. PostgreSQL 12 реализует stored-вариант: значение считается при вставке/обновлении и сохраняется на диск.

У нас в схеме есть несколько столбцов, которые всегда являются детерминированными функциями других: полное имя из частей, категория по диапазону суммы, нормализованный код статуса. Сейчас это либо триггеры, либо логика на стороне ETL. Генерируемые столбцы потенциально упрощают схему.

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

CTE inline: вот тут пришлось поработать

В PostgreSQL 11 и ниже CTE (выражения WITH) всегда материализовались: оптимизатор выполнял подзапрос в CTE один раз, сохранял результат, дальше использовал его как чёрный ящик. Это означало два следствия: предсказуемость (поведение подзапроса изолировано и не зависит от внешнего контекста) и ограничение (планировщик не лезет внутрь CTE с оптимизациями - включая pushdown условий).

Разработчики годами использовали это поведение как "барьер оптимизатора": если нужно было заставить планировщик посчитать подзапрос изолированно - его оборачивали в CTE. Это не баг, это задокументированное поведение, которым осознанно пользовались.

В PostgreSQL 12 дефолт изменился. CTE без побочных эффектов и без рекурсии теперь по умолчанию inlined - то есть планировщик раскрывает их как подзапросы и оптимизирует вместе с остальным запросом. Материализовать явно можно через WITH ... AS MATERIALIZED (...).

На наших запросах это дало несколько сценариев:

Запросы ускорились. Там где CTE делал фильтрацию, которую оптимизатор теперь может протолкнуть вниз - запросы стали быстрее. Часть дашбордных запросов на стенде выполняется заметно шустрее.

Несколько запросов изменили планы не в лучшую сторону. Один запрос с двумя CTE, где первый был промежуточной агрегацией, а второй использовал её результат дважды - на 12-й версии стал медленнее. Раньше агрегация считалась один раз и кешировалась в материализованном CTE. Теперь оптимизатор раскрыл оба CTE, и агрегация стала считаться дважды. EXPLAIN ANALYZE это подтвердил. Фикс тривиальный - добавили MATERIALIZED:

WITH agg AS MATERIALIZED (
    SELECT account_id, SUM(amount) AS total
    FROM transactions
    WHERE status = 'settled'
    GROUP BY account_id
)

Один запрос упал по-другому. Был хитрый CTE, который использовался как барьер специально - чтобы планировщик не тянул условие из внешнего запроса внутрь подзапроса с UNION ALL. Логика там была специфичная: порядок выполнения имел значение из-за side effect в функции. В 12-й версии оптимизатор залез внутрь, и результат стал другим. После MATERIALIZED - снова ожидаемый результат.

Нашли такие случаи ручным просмотром плановых запросов аналитиков - прошлись по десяткам запросов, сравнивая EXPLAIN на 11-й и 12-й версиях. Это заняло день с небольшим. Запросы от инструментов BI - ладно, их меньше и они более контролируемые. Хуже было с запросами из выгрузочных скриптов, которые писались давно и "просто работали".

Итог на этот момент

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

В продакшн 12-ю версию не ставим - это бета, GA ожидается осенью. До этого планируем дотестировать нагрузку под параллельными запросами и посмотреть поведение на операциях добавления партиций. После GA будем принимать решение об апгрейде - с учётом того, что аудит CTE к тому моменту уже сделан.