PostgreSQL 11 GA: обновили первый продакшн DWH - partition pruning сам, JIT ускорил аналитику
Обновили production DWH с PostgreSQL 10 до 11. Partition pruning по дате стал автоматическим, JIT-компиляция дала 20-30% на аналитических запросах без ручной настройки.
PostgreSQL 11 GA - декларативное партиционирование с runtime pruning, JIT-компиляция через LLVM и параллелизм DDL-операций
18 октября PostgreSQL Global Development Group выпустила PostgreSQL 11 GA. Мы её ждали: в августе тестировали бету, нашли конкретный прирост на параметрическом partition pruning и решили, что сразу после GA мигрируем первый клиентский DWH. Окно на обслуживание договорились заранее, pg_upgrade прогнали в выходные. Вот что получилось.
Контекст: зачем вообще торопиться
Проект по DWH и аналитике у этого клиента стандартный: таблица фактов партиционирована по месяцам, три года истории, несколько десятков миллионов строк. BI-инструмент передаёт даты через параметры, не константы - и это именно тот случай, где PostgreSQL 10 не справлялся с partition pruning на этапе выполнения. Планировщик не знал значение $1 заранее, включал в план все 36 партиций, сканировал их. На запросе за квартал это было заметно.
В PostgreSQL 11 pruning перенесли на этап выполнения - Append узел получает параметр в runtime и отсекает лишние партиции до чтения. Мы это проверили на бета-стенде ещё в августе, разница была очевидна. GA означало: теперь можно на продакшн.
Миграция: pg_upgrade без сюрпризов
Использовали pg_upgrade --link - жёсткие ссылки вместо копирования файлов данных. На 200 гигабайтах это принципиально: upgrade с копированием занял бы часы, с --link - минуты. Понятно, что после --link откат уже не тривиален (файлы данных общие), но мы взяли pg_dump схемы и свежий basebackup заранее.
Процедура стандартная: остановить PostgreSQL 10, запустить pg_upgrade, проверить, запустить PostgreSQL 11, прогнать vacuumdb --analyze-in-stages - чтобы не ждать пока автовакуум соберёт статистику. На наш объём от остановки сервиса до запуска PostgreSQL 11 ушло около 20 минут.
Одна неожиданность: pg_upgrade честно предупредил о нескольких функциях, которые использовали устаревший синтаксис параметров агрегатов. В PostgreSQL 11 он всё ещё работает с warning, но в следующих версиях может отвалиться. Зафиксировали в задачи.
Partition pruning: constraint_exclusion в мусор
Раньше нам приходилось держать constraint_exclusion = partition и следить, чтобы у каждой партиции был CHECK-констрейнт на диапазон дат - только так планировщик понимал что можно исключать секции. В PostgreSQL 11 это не нужно: оптимизатор читает определение партиционирования из системного каталога и сам знает какие секции за какой период отвечают.
На практике - убрали constraint_exclusion = partition из конфига (переключили на off) и удалили ручные CHECK-констрейнты с партиций. EXPLAIN ANALYZE на типовом квартальном запросе теперь показывает never executed у 33 из 36 партиций - сканируются только нужные три.
Разница в реальном времени выполнения - примерно вдвое относительно PostgreSQL 10 на той же нагрузке. Не синтетика: те самые BI-запросы, которые раньше шли заметно дольше.
JIT: включили, настроили порог
JIT в PostgreSQL 11 включён по умолчанию (jit = on). На нашей нагрузке результат неоднородный.
Аналитические запросы с тяжёлыми выражениями - CASE WHEN, вычисляемые метрики, функции над большими наборами строк - дали 20-30% прироста. Это ощутимо, особенно на запросах, которые аналитики гоняют в интерактивном режиме.
Короткие OLTP-запросы и агрегации по маленьким срезам - JIT здесь добавляет overhead на компиляцию, который никакого выигрыша не даёт. jit_above_cost по умолчанию стоит 100000, что достаточно высоко - короткие запросы под порог не попадают. Пока оставили дефолт, наблюдаем.
DDL в параллельном режиме - ещё одна фича PostgreSQL 11, которую сразу заметили. CREATE INDEX теперь использует несколько воркеров через max_parallel_maintenance_workers. На нашей железке с 8 ядрами создание индекса на большой таблице стало заметно быстрее. Параметр по умолчанию 2, мы подняли до 4 - время построения упало.
Что ещё изменилось в поведении
Несколько вещей, которые обнаружились уже после миграции в ходе наблюдения:
pg_partitioned_table в системном каталоге. Появился в 11-й версии. Обновили утилиты обслуживания партиций - теперь читаем метаданные оттуда вместо pg_constraint. Код стал чище.
DEFAULT PARTITION. На одном из источников данных изредка прилетают события за периоды вне текущей схемы партиционирования. В PostgreSQL 10 такая вставка падала с ошибкой, ETL нужно было ловить и обрабатывать. Добавили DEFAULT PARTITION - теперь такие строки туда уходят, ETL об этом не знает, а мы добавили алерт на заполнение дефолтной партиции выше порога.
ALTER TABLE ... ATTACH PARTITION без длинной блокировки. Ежемесячно добавляем новую партицию. В PostgreSQL 10 ATTACH PARTITION держал ACCESS EXCLUSIVE lock на всё время проверки констрейнтов - на большой таблице это было заметно для подключённых сессий. В PostgreSQL 11 можно сначала добавить CHECK (NOT VALID), потом VALIDATE CONSTRAINT отдельной транзакцией с более мягкой блокировкой. Переписали скрипт добавления партиций.
Что пока не трогали
JIT с jit_inline_above_cost и jit_optimize_above_cost - оставили дефолты. Хотим сначала собрать статистику на реальной нагрузке недели три-четыре, потом смотреть что крутить. Включать JIT для всего подряд без понимания профиля нагрузки - не лучшая идея.
Логическая репликация между 10 и 11 нам не понадобилась - мигрировали через pg_upgrade. Но клиент с сервисом без окна на даунтайм у нас в очереди на миграцию; там будем использовать именно логическую репликацию.
Итог пока такой
PostgreSQL 11 на продакшне неделю, инцидентов не было. Partition pruning работает как ожидали - BI-запросы по дате ускорились без каких-либо изменений в SQL или схеме. JIT дал реальный прирост на тяжёлых аналитических запросах, хотя и не везде. constraint_exclusion и ручные CHECK-констрейнты можно выбросить - это ручная работа которую PostgreSQL 11 берёт на себя автоматически.