PostgreSQL 13 GA: мигрируем аналитическую БД с PG 11 - инкрементальная сортировка меняет картину

PostgreSQL 13 вышел в GA 24 сентября. Мы ждали этого момента примерно с июля, когда тестировали Beta 2 на production-слепке и уже видели, что дедупликация B-tree реально работает. Теперь GA, и у нас есть конкретная задача: перевести на PG 13 аналитическую базу в DWH-проекте, которую по разным причинам так и не подняли выше PG 11.

База примерно 800 GB, смешанная нагрузка - исторические аналитические таблицы с партиционированием и несколько десятков отчётных запросов с тяжёлыми ORDER BY и GROUP BY. Клиент давно хотел на свежую версию, мы откладывали, ждали стабильного GA. Дождались.

Почему сразу 13-я, а не через 12-ю

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

Практически это означало, что pg_upgrade нужно было протестировать с дампом production-схемы на dev-стенде, прогнать pg_upgrade --check, убедиться что нет несовместимых объектов. Мы это сделали заранее, ещё пока шла beta. По схеме проблем не нашли - устаревших синтаксических конструкций не было, PostgreSQL 12/13 совместимость нас не укусила.

Процесс: pg_upgrade в offline-режиме

Для базы такого размера у нас было два реалистичных варианта: pg_upgrade --link (hardlink, быстро, но старые файлы данных исчезают) или dump/restore через logical replication. Выбрали pg_upgrade --link с несколькими оговорками:

• Подготовка. За неделю до - полный basebackup на отдельное хранилище. Не потому что не доверяем pg_upgrade, а потому что 800 GB - это не то что хочется восстанавливать из ничего.
• Проверка. pg_upgrade --check в режиме simulation без реального апгрейда. Чисто.
• Окно. Договорились с клиентом на ночное окно с 23:00 до 05:00. Фактически заняло около 2 часов - из них где-то 40 минут сам pg_upgrade, остальное vacuumdb --analyze-in-stages и проверка.
• Старт 13-й. Без инцидентов. Подключения восстановили, прошлись по ключевым запросам.

pg_upgrade --link на 800 GB работает быстро именно потому что физически файлы не копирует - создаёт hardlink'и. Настоящая работа происходит на этапе analyze уже после старта новой версии.

Что изменилось в планах запросов

Основной сюрприз произошёл утром, когда клиент запустил плановые отчёты. Несколько из них, которые мы про себя называли "тяжёлыми", отработали заметно быстрее. Стали смотреть в EXPLAIN ANALYZE.

PostgreSQL 13 добавил инкрементальную сортировку (incremental sort). Суть: если данные уже частично отсортированы по первому ключу (например, планировщик использовал индекс по report_date), сортировка по следующим ключам (report_date, region_id, metric_type) делается не заново на всём наборе, а по группам с одинаковым первым ключом. Это принципиально меняет картину для запросов с составным ORDER BY, где первый ключ покрыт индексом.

На конкретном отчётном запросе - ежедневная выборка с агрегацией по регионам и категориям за скользящий квартал - EXPLAIN ANALYZE показал Incremental Sort вместо Sort. Время выполнения упало примерно в 3 раза относительно того что было на PG 11 на тех же данных. Это не маленькое число.

Честное уточнение: на PG 12 мы эту базу не гоняли, так что сравнение именно с 11-й. Сколько из трёхкратного прироста - за счёт инкрементальной сортировки, сколько за счёт других улучшений планировщика между 11-й и 13-й версиями - точно не скажем. Но Incremental Sort в плане стоит, и именно он объясняет основную часть выигрыша.

B-tree дедупликация: ожидания vs реальность

Когда тестировали бету на другой базе, там был 800 GB другой структуры - OLTP с enum-полями. Там дедупликация давала реальный выигрыш на таблицах с низкой кардинальностью индексов.

На аналитической базе картина другая: большинство тяжёлых индексов здесь либо по дате (высокая кардинальность), либо уникальные суррогатные ключи. REINDEX после апгрейда прогнали, смотрели на pg_relation_size - изменение в пределах нескольких процентов, и то не везде. Для этой конкретной базы дедупликация не является главным выигрышем.

Вывод понятный: B-tree дедупликация - прицельная оптимизация, зависит от профиля данных.

Что сейчас

База работает на PG 13 третьи сутки. Клиент доволен - отчёты, которые раньше уходили пить кофе, теперь возвращаются быстрее. Мониторинг (pg_stat_statements, наши стандартные дашборды) показывает нормальную картину, аномалий нет.

Параллельный VACUUM в 13-й мы пока агрессивно не крутили - сначала понаблюдаем за автовакуумом неделю, потом будем разбираться с тюнингом max_parallel_maintenance_workers под конкретный I/O-профиль. Торопиться некуда.

Остальные клиентские базы на PG 11 в очереди. Для большинства из них сценарий будет похожим.