Продолжая тему интересных возможностей грядущего релиза PostgreSQL 11, я хотел бы рассказать про новую встроенную функцию websearch_to_tsquery. Соответствующий патч разработали Виктор Дробный и Дмитрий Иванов, с правками от Федора Сигаева. Давайте же разберемся, что реализовано в этом патче.

8-го апреля закончился комитфест 2018-03. Те патчи, которые не закомичены на нем (и на 3 предыдущих комитфестах) уже не попадут в релиз PostgreSQL 11: произошла заморозка функциональности (feature freeze). Время подводить итоги.

Главные новости последнего комитфеста (и версии 11 соответственно):

• увесистый набор патчей для секционирования.
• JIT-компиляции посвящен только один патч, но это шаг в направлении, которое в будущем наверняка будет развиваться интенсивно.
• «покрывающие» индексы (INCLUDE-индексы). Это тема уже активно обсуждается и продолжается в разработках.
• Серия патчей в группе процедурных языков. Они важны в том числе для совместимости со стандартами SQL и миграции с Oracle.
• Интересные, но не столь резонансные патчи.

Начнем в произвольном порядке.

У многих достижений версии PostgreSQL 10 прописка в разделе Секционирование (Partitioning). И это справедливо: очевидно, что при переходе от 9.6 к 10 произошел мощный технологический скачок. В предыдущих версиях секции строили и управляли ими, теми средствами, что уже имелись: механизмом наследования со всеми его ограничениями и неудобствами.

В версии 10 перешли к более специализированным механизмам и более привычному (в том числе для пользователей Oracle, а с этим приходится считаться) синтаксису. Этот скачок при переходе от 10 к версии 11 должен был подкрепиться важными дополнениями, которые должны расширить функциональность и улучшить производительность операций, использующих секционирование. Но из итогов последнего комитфеста (он закончился 8 апреля) видно, что не все задуманное удалось довести до рабочего состояния, а значит не все попадет в версию 11.

К тому же последние пару лет параллельно велись разработки модуля pg_pathman в Postgres Professional. Некоторые важные возможности пересеклись, некоторые остались уникальны для PostgreSQL и pg_pathman (который работает с ванильной версией, то есть PostgreSQL 10 + pg_pathman дает уже вполне впечатляющую сумму функциональности). Об этом будет отдельная статья. Замечания, относящиеся к версии 11 и к pg_pathman для удобства выделены курсивом.

Эта статья представляет собой переработанные и дополненные фрагменты книжки Nouveaulités de PostgreSQL 10. (с) Dalibo, перевод с французского Игоря Лёвшина (оригинал). Примеры из книги проверены, иногда адаптированы и локализованы для большей наглядности.

Релиз PostgreSQL 11 состоится еще не скоро, только в октябре. Но фичфриз уже наступил, а значит мы знаем, какие фичи попали в этот релиз, и можем их потестировать, собрав PostgreSQL из ветки master. Особого внимания заслуживает фича под названием INCLUDE-индексы. Патч изначально написан Анастасией Лубенниковой, а потом допилен Александром Коротковым и Федором Сигаевым. Протолкнуть его в PostgreSQL заняло «всего лишь» что-то около трех лет.

(В статье использованы примеры и пояснения из книги Nouveaulités de PostgreSQL 10. (с) Dalibo, перевод с французского Игоря Лёвшина, редактор Егор Рогов (оригинал). Примеры проверены, иногда изменены для большей наглядности)

Конечно, мы уже ждем не дождемся появления 11-й версии PostgreSQL. Но уже сейчас ясно, что некоторые довольно радикальные улучшения производительности появились уже в версии 10. Определенно есть смысл разобраться сначала с ними.

Производительность "десятки" улучшилась сразу в нескольких направлениях. В этой статье речь пойдет об ускорении за счет:

• распараллеливания сканирования таблиц и индексов,
• более эффективного агрегирования,
• быстрых переходных таблиц,
• ускорения запросов за счет многоколоночной статистики.

Мы начнем с параллелизма.

Мы продолжаем знакомить вас с самыми интересными новостями по PostgreSQL.

Релизы

Вышел PostgreSQL 10.3

В этом релизе закрыта дыра безопасности: неконтролируемый путь поиска объектов в схемах БД в pg_dump и других приложениях. Среди других исправлений: теперь логическая репликация не будет пытаться передавать изменения, если таблицы запрещены для публикации. Также вышли обновленные версии 9.x.

Версия Postgres Pro Standard 10.3.1 вышла в тот же день, что и PostgreSQL 10.3, так как необходимо было залатать дыру как можно быстрее. Сейчас доступны уже Postgres Pro Standard 10.3.2 и Postgres Pro Enterprise 10.3.2. В них в том числе добавлена поддержка TOAST для атрибутов INCLUDED в индексах-B-деревьях. Серьезно усовершенствована утилита pg_probackup (теперь это версия 2.0.16).

В этой статье мы посмотрим, как в двух системах создавать пользовательские агрегатные и оконные (в терминологии Oracle — аналитические) функции. Несмотря на различия в синтаксисе и в целом в подходе к расширяемости, механизм этих функций очень похож. Но и различия тоже имеются.

Надо признать, что собственные агрегатные и оконные функции встречается довольно редко. Оконные функции вообще по каким-то причинам традиционно относят к разряду «продвинутого» SQL и считают сложными для понимания и освоения. Тут бы разобраться с теми функциями, которые уже имеются в СУБД!

Зачем тогда вообще вникать в этот вопрос? Могу назвать несколько причин:

• Хотя оконные функции объективно сложнее обычных агрегатных, но ничего запредельного в них нет; это абсолютно необходимый инструмент для SQL-разработчика. А создание собственной оконной функции, даже совсем простой, позволяет лучше разобраться с тем, как работают стандартные.
  
• Оконные и агрегатные функции — прекрасный способ совместить процедурную обработку с декларативной логикой. В некоторых ситуациях получается выполнить сложные действия, оставаясь в рамках парадигмы решения задачи одним SQL-запросом.
  
• Да и просто интересная тема, а уж тем более интересно сравнить две системы.
  

Пример, на котором будем тренироваться — подсчет среднего, аналог стандартной функции avg для типа numeric (number в Oracle). Мы напишем такую функцию и посмотрим, как она работает в агрегатном и оконном режимах и может ли она вычисляться несколькими параллельными процессами. А в заключение поглядим на пример из реальной жизни.

В прошлых статьях мы рассмотрели механизм индексирования PostgreSQL и интерфейс методов доступа, а также хеш-индексы, B-деревья, GiST, SP-GiST, GIN, RUM и BRIN. Нам осталось посмотреть на индексы Блума.

Bloom

Общая идея

Классический фильтр Блума — структура данных, позволяющая быстро проверить принадлежность элемента множеству. Фильтр очень компактен, но допускает ложные срабатывания: он имеет право ошибиться и счесть элемент принадлежащим множеству (false positive), но не имеет права сказать, что элемента нет в множестве, если на самом деле он там присутствует (false negative).

Фильтр представляет собой битовый массив (называемый также сигнатурой) длиной m бит, изначально заполненный нулями. Выбираются k различных хеш-функций, которые отображают любой элемент множества в k битов сигнатуры. Чтобы добавить элемент в множество, нужно установить в сигнатуре каждый из этих битов в единицу. Следовательно, если все соответствующие элементу биты установлены в единицу — элемент может присутствовать в множестве; если хотя бы один бит равен нулю — элемент точно отсутствует.

В случае индекса СУБД мы фактически имеем N отдельных фильтров, построенных для каждой индексной строки. Как правило, в индекс включаются несколько полей; значения этих полей и составляют множество элементов для каждой из строк.

Благодаря выбору размера сигнатуры m, можно находить компромисс между объемом индекса и вероятностью ложного срабатывания. Область применения Блум-индекса — большие, достаточно «широкие» таблицы, запросы к которым могут использовать фильтрацию по любым из полей. Этот метод доступа, как и BRIN, можно рассматривать как ускоритель последовательного сканирования: все найденные индексом совпадения необходимо перепроверять по таблице, но есть шанс вовсе не рассматривать значительную часть строк.

Мы продолжаем знакомить вас с самыми интересными новостями по PostgreSQL.

Релизы

Вышел релиз PostgreSQL 10.2
В списке исправлений десятки пунктов. Например: устранение сбоев параллельных процессов при использовании более чем одного узла Gather (Томас Мунро) или Изменение поведения оператора cube ~> int в расширении contrib/cube для обеспечения его совместимости с поиском kNN(Александр Коротков). Одновременно вышли исправления версий 9.x. Напоминаем, что в новой нумерации 10.2 это минорный релиз.
Postgres Pro 10.2.1 Standard
доступен на сайте Postgres Professional Выпуск включает все новые возможности, появившиеся в PostgreSQL 10, а также исправления ошибок, вошедшие в PostgreSQL 10.2. Среди исправлений уже от Postgres Professional:

• Утилита pg_probackup обновлена до версии 2.0.14 (исправлено поведение ptrack и резервного копирования страниц в условиях гонки; исправлено прерывание параллельного резервного копирования ptrack); добавлено новое состояние резервного копирование (ORPHAN), указывающее, что одна из предыдущих резервных копий испорчена.
• Обновлён модуль pg_variables.
• Модуль pg_pathman обновлён до версии 1.4.9. В этой версии исправлена обработка ONLY во всех типах запросов.

Подробности здесь.
2ndQuadrant объявила о выходе версии OmniDB 2.5
Новая версия графического клиента PostgrSQL приблизилась к заявленной цели: стать универсальным клиентом БД. Объявлено, что в новой версии есть базовая поддержка Oracle: можно управлять, соединяться и взаимодействовать с базами Oracle, использовая бóльшую часть функционала, доступного для пользователей PostgreSQL. Появилась новая панель DDL. Релиз 2.5 сопровождался серией статей, например: Oracle with OmniDB ключевого разработчика Вильяма Ивански.

Мы продолжаем знакомить вас с самыми интересными новостями по PostgreSQL. Первый выпуск был почти месяц назад. Наш второй выпуск собрал в полтора раза больше материалов, а к существующим разделам добавился новый – книги о Постгресе и технологиях БД.

Релизы и коммиты

• Cybertec объявил выход pgconfigurator — визуального конфигуратора для редактирования файла postgresql.conf. Можно настраивать:
  • параметры репликации;
  • параметры, связанные с контрольными точками;
  • ввод/вывод, оптимизировать параллельные запросы;
  • конфигурировать память.
• Devart предлагает dbForge Studio for PostgreSQL — графическую среду для работы с базами данных и объектами PostgreSQL.
• Вышла версия JDBC 42.2.0
  Новое:
  • поддержка SCRAM-SHA-256 для PostgreSQL 10 в версии JDBC 4.2 (Java 8+),
  • используется библиотека Ongres SCRAM library;
  • Make SELECT INTO и CREATE TABLE AS теперь возвращает клиенту число строк.
  • поддержка Subject Alternative Names для SSL-соединений
  • поддержка isAutoIncrement в метаданных для столбцов идентификации в PostgreSQL 10;
  • поддержка массивов примитивов;
  • появилась поддержка get/setNetworkTimeout() в соединениях;
  • в Make GSS JAAS теперь логин не обязателен; добавлена опция «jaasLogin»

В прошлых статьях мы рассмотрели механизм индексирования PostgreSQL, интерфейс методов доступа и следующие методы: хеш-индексы, B-деревья, GiST, SP-GiST, GIN и RUM. Тема этой статьи — BRIN-индексы.

BRIN

Общая идея

В отличие от индексов, с которыми мы уже познакомились, идея BRIN не в том, чтобы быстро найти нужные строки, а в том, чтобы избежать просмотра заведомо ненужных. Это всегда неточный индекс: он вообще не содержит TID-ов табличных строк.

Упрощенно говоря, BRIN хорошо работает для тех столбцов, значения в которых коррелируют с их физическим расположением в таблице. Иными словами, если запрос без предложения ORDER BY выдает значения столбца практически в порядке возрастания или убывания (и при этом по столбцу нет индексов).

Метод доступа создавался в рамках европейского проекта по сверхбольшим аналитическим базам данных Axle с прицелом на таблицы размером в единицы и десятки терабайт. Важное свойство BRIN, позволяющее создавать индексы на таких таблицах — небольшой размер и минимальные накладные расходы на поддержание.

Работает это следующим образом. Таблица разбивается на зоны (range) размером в несколько страниц (или блоков, что то же самое) — отсюда и название: Block Range Index, BRIN. Для каждой зоны в индексе сохраняется сводная информация о данных в этой зоне. Как правило, это минимальное и максимальное значения, но бывает и иначе, как мы увидим дальше. Если при выполнении запроса, содержащего условие на столбец, искомые значения не попадают в диапазон, то всю зону можно смело пропускать; если же попадают — все строки во всех блоках зоны придется просмотреть и выбрать среди них подходящие.

Не будет ошибкой рассматривать BRIN не как индекс в обычном понимании, а как ускоритель последовательного сканирования таблицы. Можно посмотреть на него и как на альтернативу секционированию, если каждую зону считать отдельной «виртуальной» секцией.
Теперь рассмотрим устройство индекса более подробно.

Очень скоро, а именно 5-7 февраля 2018 г. в Москве будет проводиться одна из основных мировых конференций по СУБД PostgreSQL — PGConf.Russia. В этой статье мы анонсируем основные доклады конференции, и расскажем об особенностях её проведения в этом году.

Друзья! Мы решили запустить дайджест свежих новостей, статей, релизов и событий из мира PostgreSQL, который будет выходить раз в две недели. В подборке вы найдете ссылки на наиболее интересные материалы по PostgreSQL, вышедшие за период. Если мы пропустили что-то важное для вас – пишите в комментариях!

Релизы

• Вышел Postgres Pro Standard 10.1.1. В эту версию перенесены все ключевые доработки и новые возможности СУБД Postgres Pro Standard 9.6, исправлены некоторые найденные ошибки. Также вышла сборка PostgreSQL 10.1 под Windows
• Вышла версия PgBouncer 1.8.1. Исправлена ошибка в 1.8: добавлен недостающий файл, теперь PgBouncer без проблем собирается из тарбола.
• Появилась версия драйвера psqlODBC 10.01.0000. Некоторые поправки и усовершенствования по сравнению с версией 10.00.0000. Например, ликвидированы утечки памяти.

Статьи

• В статье Jsonb: few more stories about the performance
  Дмитрий Долгов (Zalando) обнародовал производительность PostgreSQL, MySQL и MongoDB на тестах YCSB. Сравнивалась производительность обработки бинарных JSON-ов (JSONB и BSON). Методика тестирования (в облаке) расписана подробно, есть выводы и рекомендации.
  До этого тема обсуждалась на PGConf.EU 2017 в Варшаве и на других конференциях. Например, в презентации Олега Бартунова по результатам YCSB-тестирования в Postgres Professional (слайд 81 и далее). В этих тестах на выделенных мощных серверах сравнивались только MongoDB и PostgreSQL, а акцент был сделан на высокую нагрузку (тысячи клиентов одновременно).

Мы уже рассмотрели механизм индексирования PostgreSQL, интерфейс методов доступа и все основные методы доступа, как то: хеш-индексы, B-деревья, GiST, SP-GiST и GIN. А в этой части посмотрим на превращение джина в ром.

RUM

Хоть авторы и утверждают, что джин — могущественный дух, но тема напитков все-таки победила: GIN следующего поколения назвали RUM.

Этот метод доступа развивает идею, заложенную в GIN, и позволяет выполнять полнотекстовый поиск еще быстрее. Это единственный метод в этой серии статей, который не входит в стандартную поставку PostgreSQL и является сторонним расширением. Есть несколько вариантов его установки:

• Взять пакет yum или apt из репозитория PGDG. Например, если вы ставили PostgreSQL из пакета postgresql-10, то поставьте еще postgresql-10-rum.
• Самостоятельно собрать и установить из исходных кодов на github (инструкция там же).
• Пользоваться в составе Postgres Pro Enterprise (или хотя бы читать оттуда документацию).

Ограничения GIN

Какие ограничения индекса GIN позволяет преодолеть RUM?

Во-первых, тип данных tsvector, помимо самих лексем, содержит информацию об их позициях внутри документа. В GIN-индексе, как мы видели в прошлый раз, эта информация не сохраняются. Из-за этого операции фразового поиска, появившиеся в версии 9.6, обслуживается GIN-индексом неэффективно и вынуждены обращаться к исходным данным для перепроверки.

Во-вторых, поисковые системы обычно возвращают результаты в порядке релевантности (что бы это ни означало). Для этого можно пользоваться функциями ранжирования ts_rank и ts_rank_cd, но их приходится вычислять для каждой строки результата, что, конечно, медленно.

Метод доступа RUM в первом приближении можно рассматривать как GIN, в который добавлена позиционная информация, и который поддерживает выдачу результата в нужном порядке (аналогично тому, как GiST умеет выдавать ближайших соседей). Пойдем по порядку.

На Higload++ 2017 года в Сколково наша компания Postgres Professional снова провела викторину с традиционной раздачей ништяков, в качестве которых выступили билеты на февральский PgConf.Russia 2018.

В этой статье разбираются вопросы викторины.

Мы уже познакомились с механизмом индексирования PostgreSQL и с интерфейсом методов доступа, и рассмотрели хеш-индексы, B-деревья, индексы GiST и SP-GiST. А в этой части займемся индексом GIN.

GIN

— Джин?.. Джин — это, кажется, такой американский спиртной напиток?..
— Не напиток я, о пытливый отрок! — снова вспылил старичок, снова спохватился и снова взял себя в руки. — Не напиток я, а могущественный и неустрашимый дух, и нет в мире такого волшебства, которое было бы мне не по силам.

Лазарь Лагин, «Старик Хоттабыч».

Gin stands for Generalized Inverted Index and should be considered as a genie, not a drink.

README

Общая идея

GIN расшифровывается как Generalized Inverted Index — это так называемый обратный индекс. Он работает с типами данных, значения которых не являются атомарными, а состоят из элементов. При этом индексируются не сами значения, а отдельные элементы; каждый элемент ссылается на те значения, в которых он встречается.

Хорошая аналогия для этого метода — алфавитный указатель в конце книги, где для каждого термина приведен список страниц, где этот термин упоминается. Как и указатель в книге, индексный метод должен обеспечивать быстрый поиск проиндексированных элементов. Для этого они хранятся в виде уже знакомого нам B-дерева (для него используется другая, более простая, реализация, но в данном случае это несущественно). К каждому элементу привязан упорядоченный набор ссылок на строки таблицы, содержащие значения с этим элементом. Упорядоченность не принципиальна для выборки данных (порядок сортировки TID-ов не несет в себе особого смысла), но важна с точки зрения внутреннего устройства индекса.

1. Кластер multimaster

Расширение multimaster и его поддержка в ядре, которые есть только в версии Postgres Pro Enterprise, дают возможность строить кластеры серверов высокой доступности (High Availability). После каждой транзакции гарантируется глобальная целостность (целостность данных в масштабах кластера), т.е. на каждом его узле данные будут идентичны. При этом легко можно добиться, чтобы производительность по чтению масштабировалась линейно с ростом количества узлов.

Мы уже рассмотрели механизм индексирования PostgreSQL, интерфейс методов доступа и три метода: хеш-индекс, B-дерево и GiST. В этой части речь пойдет о SP-GiST.

SP-GiST

Вначале немного о названии. Слово «GiST» намекает на определенную схожесть с одноименным методом. Схожесть действительно есть: и тот, и другой — generalized search trees, обобщенные деревья поиска, предоставляющие каркас для построения разных методов доступа.

«SP» расшифровывается как space partitioning, разбиение пространства. В роли пространства часто выступает именно то, что мы и привыкли называть пространством — например, двумерная плоскость. Но, как мы увидим, имеется в виду любое пространство поиска, по сути произвольная область значений.

SP-GiST подходит для структур, в которых пространство рекурсивно разбивается на непересекающиеся области. В этот класс входят деревья квадрантов (quadtree), k-мерные деревья (k-D tree), префиксные деревья (trie).

Планировщик заданий (scheduler) не во все времена считался обязательным инструментом в мире баз данных. Все зависело от назначения и происхождения СУБД. Классические коммерческие СУБД (Oracle, DB2, MS SQL) представить себе без планировщика решительно невозможно. С другой стороны, трудно вообразить потенциального пользователя MongoDB, который откажется от выбора этой модной NoSQL-СУБД из-за отсутствия планировщика. (Кстати, термин «планировщик заданий» в русском контексте СУБД употребляют, чтобы отличить его от планировщика запросов — query planner, мы же для краткости будем звать его здесь планировщиком).

PostgreSQL, будучи Open Source и впитав традиции сообщества с образом жизни DIY («сделай сам»), в наше время регулярно претендует на место как минимум заместителя коммерческой СУБД. Из этого автоматически следует, что PostgreSQL просто обязана иметь планировщик, и что этот планировщик должен быть удобен для администратора базы и для пользователя.

Хорошей традицией на постгресовых конференциях стало устраивать викторины с розыгрышем билетов на следующие конференции. Наша компания Postgres Professional на недавнем PgDay’17 разыгрывала билеты на PgConf.Russia 2018, которая пройдет в феврале 2018 года в Москве. В этой статье представлен обещанный разбор вопросов викторины.