UPSERT и не только. Что ждать от PostgreSQL 9.5?

(Вольный перевод с англ. https://www.compose.io/articles/coming-in-postgresql-9-5/)

С появлением только что вышедшей версией PostgreSQL 9.5-alpha (бета-версии ожидаются в августе) стало ясно, что входит в список новых возможностей свежей версии данной СУБД. Помимо шума вокруг аналога UPSERT (см. https://www.compose.io/articles/postgresqls-future-is-looking-upsert/) появляется много замечательных возможностей, и сейчас самое время кратко взглянуть на них.

 

Безопасность строкового уровня

Одна из самых интересных новинок, особенно для тех, кто использует базы данных с множеством пользователей, работающих с одними и теми же данными, — это Система безопасности уровня строк (Row level security, RLS). Она позволяет описывать правила для таблицы, которые определят, сможет ли данный пользователь видеть конкретную строку таблицы или нет. Такие правила привязываются к таблице, и срабатывают только тогда, когда явным образом активированы — это делается с помощью команды ALTER TABLE tablename ENABLE ROW LEVEL SECURITY. По сути своей, каждое подобное правило — это выражение, которое должно быть истинным для того, чтобы строка стала видимой.

В качестве примера можно привести ситуацию, когда некоторая таблица имеет колонку owner («владелец») – в данном случае выражение (owner == current_user) позволяюет сделать так, чтобы строки таблицы могли быть видны только их «хозяевам». (Если штуки, подобные currrent_user, для вас в новинку, советуем посмотреть на системно-информационные функции в PostgreSQL, System Information Functions, предоставляющие доступ к очень интересной информации о сессиях и рабоче среде.) Суперпользователь, конечно же, сможет увидеть все строки таблицы — дело в том, что у всех суперпользователей по умолчанию выставлена опция BYPASSRLS, означающая, что RLS-проверки осуществляться не будут.

Данная возможность может очень пригодиться тем, кто хочет разделить потоки данных между пользователями, изолировать их. Подробнее об этом можно прочитать на англ. в блоге DSHL.

 

BRIN-индексы

Аббревиатура «BRIN» означает «Block Range Index», или индекс блоковых зон. Если у вас есть очень большие таблицы и столбцы, по своей природе связанные с положением строки в таблице, как, например, временная метка в некотором логе, то BRIN-индексы предоставляют вам возможность индексировать такие столбцы, не тратясь на создание и поддержку B-деревьев (структур, используемых в стандартных индексах).

Работу BRIN-индекса можно представить так: данные разбиваются на зоны, после чего в индексе сохраняется информация о страницах, попадающих в данные диапазоны, плюс служебная информация. (Таблицы в PostgreSQL состоят из страниц, каждая из которых может содержать одну или более строк.) Служебная информация для блока включает минимальное и максимальное значения по данному столбцу для тех строк, которые содержатся в страницах, попавших в данную «зону».

Когда SQL-запрос использует BRIN-индекс, первый запрос к индексу возвращает страницы, которые потенциально содержат строки, соответствующие запросу. Для очень больших таблиц это позволяет быстро отбросить большую часть заведомо не подходящих страниц. Следующим шагом СУБД просматривает полученные страницы, последовательно проверяя строки и отбирая подходящие.

Полезн или нет BRIN-индексы? Ответ на этот вопрос целиком зависит от некоторого баланса компромиссов. Стандартные индексы (B-деревья) быстры при поиске строк, но занимают довольно много дискового пространства, и накладные расходы на их обслуживание существенны. Структуры BRIN медленнее в поиске, чем B-деревья, но явно компактнее и просты в обслуживании — конечно же, важно понимать, что не все столбцы могут быть проиндексированы с использованием данны структур. Подробнее про BRIN-индексы можно почитать на англ. здесь и здесь.

В качестве иллюстрации, пример из блога DSHL:

- создание таблиц размером 650MB:
  * btree: 626.859 ms
   * brin: 208.754 ms
(ускорение в 3 раза)

- обновление 30% значений:
  * btree: 8398.461 ms
  * brin: 1398.711 ms
(ускорение в 4 раза)

- бонус:
  * размер btree-индекса: 28MB
  * размер brin-индекса: 64kB

- пример поиска: $ select count(*) from table where id between 600000::int8 and 650000::int8;
  * btree between: 9.574 ms
  * brin between: 21.090 ms

 

JSONB

JSONB – новый и очень популярный тип данных, появившийся в 9.4. Обладая развитой поддержкой для создания индексов, этот тип, в то же время, в версии 9.4 имел существенный недостаток: любая модификация значения JSONB влечёт за собой извлечение документа целиком и полное его переопределение. В 9.5 появилась возможность использования ряда модификаторов JSONB. Так, существует специальный оператор ||, который позволяет конкатенировать два JSONB-значения. Новый оператор - обладает двумя режимами — он может удалять поле верхнего уровня, если дана строка, или же элемент массива, если дано число. Оператор - работает только с ключами верхнего уровня, в то время как оператор # будет, если задать некоторый путь «внутрь» документа, удалять вложенные ключи и их значения.

Появляются три новые функции для JSONB — jsonb_set() для обновления значения, находящегося по некоторому заданному пути внутри документа, json_strip_nulls() для удаления ключей, у которых значения NULL и, наконец, «красивый вывод» (pretty print) JSONB-значений jsonb_pretty().

Всё это улучшает возможности манипуляций с JSON-документами в PostgreSQL и безусловно «облегчит» библиотеки скриптов для работы с JSON. Подробности на англ. см, например, здесь.

 

Ускоренная сортировка

Пара улучшений в PostgreSQL 9.5 касаются сортировки. Улучшения эти начались с январского коммита, который расширил возможности сортировки за счёт фреймворка для работы с сокращёнными ключами. Первое применение этому нашлось в работе с текстовыми полями — используются первые 8 символов текстового поля, для которых создаётся blob-значние с применением strxfrm(), компресора для сортируемых строк. Далее это блоб значение используется для сортировки текста, причём в случае коллизии используется традиционное сравнение. Код используется алгоритм HyperLogLog для обнаружения наихудших случаев (ситуаций, когда все 8 первых символов текстовых значений одинаковы) и при обнаружении их принимает соответствующие меры. Фреймворк далее был применён для получения сокращённых ключей в сортировке чисел.

Согласно различным бенчмаркам, в обобщённом случае, это изменение может ускорить индексацию в 3 раза. Это очень заметный шаг в улучшении производительности СУБД, особенно учитывая, насколько важна сортировка в работе ядра системы.

 

…и не только!

Это лишь некоторые улучшения, появившиеся в PostgreSQL 9.5. Есть ещё много новинок. Скачивайте и тестируйте!