PostgreSQL 9.6 в production: три месяца с parallel query и новой репликацией

В сентябре мы писали про апгрейд DWH с 9.4 на 9.6 RC и первые наблюдения за parallel seq scan. Прошло три месяца, 9.6 уже GA с сентября, и накопилось достаточно чтобы написать по-честному - без розовых очков первых недель.

Спойлер: работает. Но с нюансами, которые на RC не были видны.

Что изменилось за три месяца

ETL-цикл на проекте - это ночная загрузка из нескольких источников в аналитические таблицы, агрегации, построение витрин. До 9.6 он занимал стабильно около 4 часов. После апгрейда и настройки параллелизма - около 1,5 часов. Запросы не менялись. Схема не менялась. Только версия PostgreSQL и параметры конфига.

Конкретно поменяли:

• max_parallel_workers_per_gather = 4 - на машине с 16 ядрами это оставляет запас для OLTP-нагрузки в дневное время.
• max_worker_processes = 12 - суммарный пул воркеров для всего сервера.
• work_mem = 128MB - пришлось уменьшить с 256 MB: при 4 воркерах на запрос это 512 MB только на сортировки одного запроса, а ETL-шаги иногда идут параллельно.

Планировщик сам выбирает parallel plan для подходящих запросов - большинство агрегаций по жирным таблицам попали под параллелизм без единой подсказки.

Что реально параллелится, а что нет

После трёх месяцев EXPLAIN-ов сложилась более чёткая картина.

Параллелится хорошо: seq scan с фильтрацией, hash join на больших таблицах, partial HashAggregate (COUNT, SUM, AVG). Это основной объём ETL-запросов - они и дали выигрыш.

Параллелится хуже или не параллелится: запросы с подзапросами через LATERAL, операции с LIMIT, window functions. Несколько запросов с ROW_NUMBER() OVER (PARTITION BY ...) остались однопоточными - планировщик не нашёл как их распараллелить. Пришлось переписывать вручную, разбивая на шаги.

Неожиданный момент: несколько запросов с DISTINCT перестали использовать параллельный план после того как выросли таблицы. Оказалось, планировщик переоценивал стоимость gather-узла для DISTINCT-агрегаций при большом числе уникальных значений. Помогло уменьшить parallel_tuple_cost с дефолтного 0.1 до 0.05 - планировщик стал агрессивнее выбирать параллельные планы.

Репликация: что изменилось для нас

В 9.6 существенно поменяли streaming replication. Главное для нас - synchronous_commit теперь настраивается per-transaction и появился remote_apply как вариант для synchronous_standby_names.

На одном из проектов у нас hot standby для аналитических запросов (чтобы не грузить primary). С remote_apply мы теперь знаем что реплика реально применила транзакцию, а не просто получила WAL. Для аналитических витрин, где читают сразу после записи ETL - это важно. Раньше периодически ловили ситуацию где аналитик уже смотрит на реплике, а свежие данные ещё не применены.

Включили на одном проекте, работает три недели - задержка репликации выросла незначительно (single-digit миллисекунды на нашем железе), зато пропали жалобы на «данные не обновились».

Мониторинг параллельных запросов

Один практический момент: pg_stat_activity теперь показывает параллельные воркеры отдельными строками - у них нет client_addr и application_name содержит parallel worker. Наш Zabbix-шаблон поначалу не понимал почему число активных соединений выросло - это воркеры, а не новые клиентские сессии.

Подправили мониторинг: считаем клиентские сессии и параллельные воркеры раздельно (по наличию client_addr). Заодно добавили алерт если воркер-процессов больше max_worker_processes * 0.8 - означает что пул воркеров близок к исчерпанию и новые параллельные планы деградируют до последовательных.

В Grafana смотрим через pg_stat_activity - динамику воркеров в ETL-окне. Картинка стала читаемее чем просто «количество запросов».

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

work_mem - главная переменная для настройки. При параллелизме умножать надо не только на максимальное число воркеров, но и на число одновременных тяжёлых запросов. На OLAP-нагрузке это легко проморгать и получить OOM.

Апгрейд через pg_upgrade с 9.4 прошёл чисто, но между 9.5 и 9.6 один проект поймал предупреждение про изменившееся поведение jsonb в edge-кейсах. Не критично, но pg_upgrade --check перед production - обязательно.

Где сейчас

Три инсталляции на 9.6 GA. ETL-процессы быстрее, аналитики не жалуются на время ожидания отчётов, мониторинг настроен. Четвёртую инсталляцию переводим в декабре - там схема сложнее и хотим сначала отработать pg_upgrade на staging с реальным объёмом данных.

Для проектов с аналитической нагрузкой и ETL апгрейд до 9.6 сейчас выглядит как один из самых дешёвых способов ускорить тяжёлые запросы. Не бесплатный - нужно откалибровать work_mem и разобраться с мониторингом воркеров. Но соотношение усилий к результату хорошее.