PostgreSQL 9.4 на тестовом стенде: JSONB быстрее, материализованные представления удобнее

В декабре вышел PostgreSQL 9.4. Мы подождали пару недель, пока не появились первые репорты от людей, которые уже поставили его не на песочницу, и на прошлой неделе обновили тестовый стенд. Рассказываем что нашли.

Два изменения интересуют нас в первую очередь применительно к аналитическим задачам: JSONB и улучшенные материализованные представления. Про репликацию - отдельно, там есть что обсудить.

JSONB: теперь с бинарным хранением

В PostgreSQL 9.3 тип json хранил документ как текст и разбирал его при каждом обращении. Это означало: каждый SELECT с условием по JSON-полю - полный парсинг документа. Индексировать можно было через функциональные индексы, но это требовало явного описания каждого пути.

В 9.4 появился jsonb - тот же JSON, но хранится в разобранном бинарном виде. Документ парсится один раз при вставке. Запросы к полям работают по уже разобранной структуре. GIN-индекс по jsonb строится прямо над содержимым документа - можно индексировать весь столбец без перечисления конкретных ключей.

Мы прогнали несколько запросов на наборе тестовых данных с конфигурационными документами - типичный сценарий, похожий на тот, с которым работали в проекте миграции с MS SQL. Картина такая:

• Точечная выборка по ключу без индекса - jsonb быстрее json примерно в 2-3 раза, что совпадает с тем, что пишут в release notes и что видно у других в интернете.
• Диапазонные условия по числовым значениям внутри документа - разница ещё заметнее, если добавить GIN-индекс: план перестаёт делать seq scan по всей таблице.
• INSERT - здесь jsonb немного медленнее, потому что парсит при записи. Для аналитических нагрузок это обычно несущественно.

Важная оговорка: jsonb не сохраняет порядок ключей и убирает дублирующиеся ключи (оставляет последнее значение). Для большинства задач это нормально, но если приложение по какой-то причине зависит от порядка ключей в JSON - надо проверить отдельно.

Оператор @> для проверки «содержит ли документ вот такую подструктуру» теперь можно использовать с GIN-индексом без танцев с бубном. Для задачи «найди все записи, у которых в конфиге выставлен вот этот флаг» - это прямое решение.

Материализованные представления: наконец CONCURRENT

В 9.3 материализованные представления появились, но с неприятным ограничением: REFRESH MATERIALIZED VIEW блокировал чтение на всё время обновления. Для небольших витрин это терпимо, для чего-то тяжёлого - нет. Мы писали об этом в мае, там же описан костыль через промежуточную схему.

В 9.4 добавили REFRESH MATERIALIZED VIEW CONCURRENTLY. Представление обновляется без блокировки чтений - параллельные SELECT работают со старой версией пока новая строится, потом атомарно переключаются. Единственное условие - на материализованном представлении должен быть уникальный индекс хотя бы по одному столбцу (обычно это первичный ключ или суррогат, который там и так есть).

Это снимает главное ограничение, которое мешало нам рекомендовать материализованные представления для аналитических витрин с регулярным обновлением в рабочее время. Раньше приходилось либо планировать обновление в окно минимальной нагрузки, либо городить схему с отдельными таблицами и атомарным переименованием. Теперь REFRESH CONCURRENTLY по расписанию - и пользователи отчётов не видят блокировок.

Для части задач, которые мы раньше закрывали через ETL-прослойку на SSIS или скриптами с промежуточными таблицами, материализованные представления становятся нормальным решением:

• Агрегированные витрины для регулярных отчётов - обновляются раз в час или чаще, читаются часто и быстро.
• Предвычисленные джойны из нескольких таблиц - когда запрос сложный но данные не меняются каждую минуту.
• Слои трансформации внутри самого PostgreSQL без внешнего оркестратора.

Это не значит что ETL не нужен. Там где данные приходят из внешних источников, где нужна обработка ошибок на уровне строки, где несколько разнородных систем - ETL остаётся правильным выбором. Но для аналитики внутри одной PostgreSQL-базы или внутри одного кластера с репликой - материализованные представления убирают целый слой инфраструктуры.

Логическая репликация: pg_logical как расширение

В 9.4 появилась поддержка logical decoding - механизм, который позволяет читать WAL на уровне строк, а не блоков. На этом строится расширение pg_logical (не входит в стандартную поставку, устанавливается отдельно), которое даёт возможность реплицировать отдельные таблицы или схемы, а не весь кластер целиком.

Мы пока только смотрим на это теоретически: pg_logical на момент выхода 9.4 - не production-ready в смысле полной поддержки всех типов данных и сценариев. Но сам факт что фундамент теперь в ядре - это важно. Физическая репликация, с которой мы работаем, реплицирует весь кластер и не позволяет иметь разные версии PostgreSQL на мастере и реплике. Логическая репликация открывает сценарии обновления мажорной версии без простоя - это интересно.

Что с обновлением на продакшне

Мы обновили тестовый стенд через pg_upgrade, всё прошло без сюрпризов. Для продакшна у клиентов пока осторожничаем: 9.4.0 вышел в декабре, хочется дать ему немного обкататься. Ждём 9.4.1 или 9.4.2.

Планирование обновления продакшна - отдельная тема. Есть два пути: pg_upgrade с коротким простоем (переиндексация на большой базе может занять время), или поднять новый сервер с 9.4 и переключить через репликацию - тогда окно простоя минимальное. Для клиентов, где DWH работает круглосуточно, второй вариант будет правильным.

Всё что касается JSONB, материализованных представлений и хранилищ данных на PostgreSQL - в рамках DWH и аналитики.