PostgreSQL 12 в production: партиционирование без заплаток, B-tree индексы на больших DWH

PostgreSQL 12 GA вышел в октябре 2019-го. Мы не торопились: несколько недель наблюдали за тем, как сообщество тестирует релиз на реальных нагрузках, читали release notes внимательно, прогнали на dev-стенде. К январю решили: пора. В очереди два клиентских DWH на обслуживании, которые давно просятся на апгрейд - там и проверим.

Что нас интересовало в первую очередь

Главным кандидатом на проверку был партиционинг. С PostgreSQL 10 мы декларативное партиционирование встречали с осторожностью - многое приходилось обходить: внешние ключи к партиционированным таблицам не работали, уникальные индексы требовали включения ключа партиционирования, ручные CHECK-констрейнты были нужны для pruning. PostgreSQL 11 улучшил картину, но список оговорок всё ещё был длиннее, чем хотелось бы.

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

Первый DWH: партиционирование по дате

У первого клиента схема классическая: таблица фактов партиционирована по месяцам через RANGE, три года истории, регулярно добавляем новые партиции, BI-инструмент ходит с датами в параметрах. После миграции на PostgreSQL 11 в ноябре 2018-го partition pruning работал на этапе выполнения - это уже было хорошо. Смотрели что изменится в 12-й.

Первое, что увидели в EXPLAIN ANALYZE: планировщик в PostgreSQL 12 заметно лучше работает с множеством партиций в плане. Раньше на 40+ партициях EXPLAIN сам по себе был медленноватым - планировщик строил план с полным перебором секций даже до того как отсекал ненужные. В 12-й это стало быстрее за счёт переработанного внутреннего представления планов с партиционированием.

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

Добавление внешних ключей, ссылающихся на партиционированную таблицу, - в PostgreSQL 12 наконец заработало нормально. Раньше это было невозможно в принципе, приходилось держать в ETL-логике то, что хотелось оформить через FK. Теперь несколько ограничений добавили по-честному.

Второй DWH: B-tree индексы

Второй клиент - таблица пошире по колонкам и поменьше по истории, зато с большим числом индексов на колонках со слабой кардинальностью: статусы, категории, флаги.

В PostgreSQL 12 B-tree получил несколько внутренних улучшений: сниженный WAL-трафик при вставках, ускоренное сканирование по диапазону на больших индексах, лучшая обработка NULL при сортировке. Не революция, но на тяжёлых таблицах заметно.

Что получилось на практике:

• Размер индексов после REINDEX - без значимых изменений по сравнению с PostgreSQL 11. Геометрия хранения B-tree принципиально не поменялась.
• Время построения индекса на больших таблицах - здесь прироста не заметили, по времени примерно то же.
• Производительность запросов по проиндексированным полям с фильтрацией по статусу - незначительный прирост, в пределах погрешности. Снижение WAL-нагрузки при интенсивной записи - более ощутимый практический эффект на нагруженных кластерах.

JIT в 12-й версии стал включаться немного умнее - изменили эвристику порога по умолчанию. На нашей аналитической нагрузке разница с PostgreSQL 11 небольшая, JIT уже был настроен и работал. Оставили настройки как были, без изменений.

Миграция: как проходило

Оба апгрейда через pg_upgrade --link. Алгоритм отработанный ещё на переходе с 10 на 11: basebackup заранее, схема через pg_dump, остановка, апгрейд, vacuumdb --analyze-in-stages, старт, наблюдение.

Один нюанс с pg_upgrade в этот раз: он сообщил об устаревших записях в pg_proc для функций, созданных с синтаксисом, которого в PostgreSQL 12 нет. Ничего критичного, но на обоих кластерах нашлось по паре таких функций из старых ETL-скриптов. Поправили до миграции.

После старта PostgreSQL 12 - оба кластера без инцидентов. Планировщик на некоторых запросах выбрал другой план по сравнению с 11-й - это ожидаемо после ANALYZE, потому что статистика собирается заново. На одном из запросов план стал хуже: планировщик выбрал hash join там, где раньше использовал merge join по индексу. Разобрались через SET enable_hashjoin = off для конкретного запроса, зафиксировали через pg_hint_plan, пока смотрим.

Где сейчас

Оба кластера на PostgreSQL 12 около двух недель. Субъективно - работает стабильнее чем 11-я на момент GA. Партиционирование ведёт себя предсказуемо, никаких обходных путей для стандартных сценариев больше не нужно. B-tree дедупликация дала реальный выигрыш по размеру индексов на "плохих" колонках - это позволяет немного сократить аппетиты кластера к дисковому пространству.

Остальные клиентские DWH на 11-й пока трогать не будем - сначала понаблюдаем ещё месяц на этих двух. Если не преподнесут сюрпризов, будем двигаться дальше.