PostgreSQL 9.6: апгрейд production с 9.4 и первые наблюдения по parallel seq scan

29 сентября PostgreSQL 9.6 должен выйти финальным релизом. Мы работаем с RC уже несколько недель - на одном из DWH-проектов решили не ждать GA и поставить RC в production. Не от нетерпения, а потому что конкретная фича нужна была уже сейчас, и RC для этого оказался достаточно стабильным.

Фича - parallel query. Точнее, для нас - parallel seq scan.

Откуда задача

На проекте есть несколько аналитических запросов по таблицам от нескольких сотен миллионов строк. Запросы жирные: агрегации, фильтрация по нескольким колонкам без подходящего индекса, иногда джойны. Раньше на PostgreSQL 9.4 такой запрос просто шёл последовательно - один поток, один CPU, столько времени сколько нужно. Время исполнения считалось минутами.

Вариантов было несколько: индексы (не всегда подходят при такой аналитике), партиционирование (большая работа, откладывали), переезд на columnar-хранилище (ещё большая работа). Parallel query казался самым дешёвым входным билетом - хотя бы посмотреть что даёт, не трогая приложение.

Как делали апгрейд

PostgreSQL 9.6 RC поставили по стандартной схеме: pg_upgrade с 9.4 на 9.6. Перед этим - полный дамп, проверка на staging с идентичными данными. Апгрейд на staging прошёл чисто, потом повторили на production в окно обслуживания.

pg_upgrade между 9.4 и 9.6 прошёл без сюрпризов. Каталог данных конвертировался быстро, приложение поднялось, соединения установились. Это хорошая новость - иногда между мажорными версиями pg_upgrade выдаёт предупреждения про несовместимые расширения или изменившиеся типы данных. Здесь обошлось.

Что с parallel query

После апгрейда мы не трогали конфиг - хотели посмотреть на дефолтное поведение. По умолчанию в 9.6 включён max_parallel_workers_per_gather = 0, то есть параллелизм выключен.

Дальше начали аккуратно увеличивать. Параметры которые крутили:

• max_parallel_workers_per_gather - сколько воркеров может запустить один gather-узел плана. Ставили 2, потом 4.
• min_parallel_relation_size - минимальный размер таблицы для параллельного сканирования. По умолчанию 8 МБ, это разумно.
• parallel_setup_cost и parallel_tuple_cost - планировщик взвешивает стоимость запуска воркеров против выигрыша. На дефолтах для наших таблиц планировщик сам выбирал parallel seq scan без подсказок.

Что получили: тяжёлые запросы с max_parallel_workers_per_gather = 4 стали выполняться заметно быстрее - грубо говоря, в несколько раз, хотя конкретные цифры зависят от запроса и железа. Важнее другое: ни строчки в коде приложения не менялось. EXPLAIN ANALYZE показывает Gather над Parallel Seq Scan - планировщик сам принял решение.

EXPLAIN ANALYZE
SELECT region, sum(amount)
FROM orders
WHERE created_at >= '2016-01-01'
GROUP BY region;

-- фрагмент плана на 9.6 с max_parallel_workers_per_gather=4:
-- Gather  (cost=... rows=... width=...)
--   Workers Planned: 4
--   Workers Launched: 4
--   ->  Partial HashAggregate
--         ->  Parallel Seq Scan on orders

На 9.4 тот же запрос давал обычный Seq Scan в один поток.

Что ещё заметили в 9.6

Parallel query - главное для нас, но релиз принёс и другое.

synchronous_commit = local и per-transaction настройка. Теперь synchronous_commit можно выставить на уровне транзакции. Для DWH это интересно: тяжёлые ETL-загрузки можно гнать с synchronous_commit = off для конкретных сессий, не меняя глобальный параметр. Пока не включали, но держим в уме.

Full-text search. Улучшения в phrase search - теперь можно искать точные фразы через <-> оператор в to_tsquery. У нас есть один проект где это было бы полезно, но это отдельная история.

Parallel aggregation. Агрегации тоже умеют работать параллельно - Partial HashAggregate в плане выше как раз это. Воркеры считают частичные агрегаты, Gather собирает финальный результат. Для COUNT, SUM, AVG работает хорошо.

Где осторожность

RC есть RC. На критических данных мы держим полные резервные копии в любом случае, но с RC это особенно важно. Параллельные воркеры потребляют дополнительную память - work_mem умножается на количество воркеров, это надо считать. На машине с 64 ГБ RAM и work_mem = 256 МБ при четырёх воркерах один тяжёлый запрос может взять гигабайт просто на сортировки.

У планировщика с parallel query есть и слабые стороны: не все виды соединений и операций ещё умеют параллелиться в 9.6. Если запрос включает несколько вложенных подзапросов или специфичные агрегаты, параллелизм может не включиться. EXPLAIN в помощь - всегда смотрим план перед тем как делать выводы.

Где сейчас

Обновление работает в production уже три недели. Аналитические запросы выполняются быстрее, жалоб нет. GA-релиз 29 сентября - тогда переведём и оставшиеся инсталляции, которые сидят на staging.

Для DWH и аналитических проектов parallel query - это реальный аргумент в пользу апгрейда. Не нужно переписывать схему под columnar-хранилище и не нужно тратить время на оптимизацию каждого запроса вручную. Для части задач достаточно обновить Postgres и выставить max_parallel_workers_per_gather.