В понедельник, 13 ноября приглашаем вас в офис Райффайзенбанка, где пройдет очередной PostgreSQL MeetUp. Мероприятие организовано совместно с компанией Postgres Professional.

Мы долго думали, как назвать наше событие, и решили дать ему романтичное название «Осенний Postgres», хотя, скорее всего, осени будет мало и, может, выпадет снег.

Мы уже познакомились с механизмом индексирования 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-ов не несет в себе особого смысла), но важна с точки зрения внутреннего устройства индекса.

Администраторы баз данных и разработчики часто сталкиваются с ситуациями, когда необходимо данные из разных баз сравнивать и синхронизировать, либо просто перенести их в другую рабочую базу. В этом случае очень важно выбрать правильный инструмент, который поможет справиться с этой задачей быстро и эффективно. Для PostgreSQL на рынке существует несколько готовых инструментов, которые позволяют находить различия и выполнять синхронизацию данных. В этой статье проведем небольшой обзор особенностей этих инструментов, а именно продукты таких компаний как Devart, SQL Maestro Group, Navicat и Altova.

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

В ходе работы DLP-система ежедневно перехватывает огромные массивы информации – это и письма сотрудников, и информация о действиях пользователей на рабочих станциях, и сведения о хранящихся в сети организации файловых ресурсах, и оповещения о несанкционированном выводе данных за пределы организации. Но полезной эта информация будет только в случае, если в DLP реализован качественный механизм поиска по всему массиву перехваченных коммуникаций. С тех пор, как в 2000 году увидела свет первая версия нашего DLP-решения, мы несколько раз меняли механизм поиска по архиву. Сегодня мы хотим рассказать о том, какие технологии мы использовали, какие видели в них преимущества и недостатки, и почему мы от них в итоге отказывались. Возможно, кому-то наш опыт окажется полезен.

Иногда возникает желание мониторить локальный GIT сервер на предмет кто (ФИО из LDAP), какой проект и откуда(ip-адрес) клонит или пушит.

Изучив документацию, стало ясно, что такого функционала из коробки нет, точнее есть, но в платной версии GitLab. Под катом мой опыт реализации мониторинга.

Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).

Постараюсь охватить множество тем и приемов, которые помогут при работе с данными, стараясь не углубляться в подробное описание того или иного функционала. Я любил подобные статьи, когда обучался самостоятельно. Пришло время отдать должное бесплатному интернет самообразованию и написать собственную статью.

Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin'e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.

Поехали!

Во время миграции из Oracle в PostgreSQL с помощью ora2pg встал вопрос с несоответствием типов данных между разными базами. По умолчанию не все колонки конвертируется правильно, а отсутствие в Oracle Boolean и вовсе порождает неоднозначность – часть колонок нужно перенести как числа, часть как логические значения. В тоже время hibernate знает все о типах данных и может создать эталонную схему.

Итоговый процесс переноса выглядел следующим образом: создание структуры таблиц через ora2pg, исправление структуры по эталонной схеме, перенос данных, конвертация blob и Boolean, добавление отсутствующих в PostgreSQL функций (nvl, nvl2, regexp_substr), создания оставшейся структуры — индексов, view и прочего.

Под катом накопившиеся за время конвертации sql скрипты для полуавтоматической миграции.

Неоднократно доводилось слышать мнение, что из всех заметающих кривых. именно кривая Гильберта наиболее перспективна для пространственной индексации. Мотивируется это тем, что она не содержит разрывов и потому в некотором смысле “хорошо устроена”. Так ли это на самом деле и при чем здесь пространственная индексация, разберёмся под катом.

Вышел релиз PostgreSQL 10. Мы долго ждали появления новых фич. Мои коллеги ещё весной тестировали новую версию и отправляли баг-репорты: раз, два. А я был очень рад обновлению и подготовил перевод Release Notes на русский язык. Давайте обсуждать.

На самом деле прошло уже два дня, но статью на Хабр никто до сих пор не написал, так что придется мне устранять это упущение, что и делаю с удовольствием.

Итак, что же нового в этой версии PostgreSQL?

Во-первых, изменилось само версионирование. До "десятки" мы наблюдали множество минорных версий 9.x, которые выходили примерно раз в год и при этом вносили серьезные, далеко не минорные изменения. Поэтому с версии 10 было принято решение сделать нумерацию 10, 11, 12 и т.д. Кстати, MySQL, похоже пошел по тому же пути, прыгнул с 5.7 на 8.0

Ладно, это всё мелочи, перейдем к существу вопроса

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

Вместо предисловия

Статья будет интересна тем, кто хоть раз задумывался о вопросе наката изменений (патча) на реляционную БД. Статья не будет интересна тем, кто уже освоил и использует Liquibase. Главной целью данной статьи является указание ссылки на репозиторий с примером использования. В качестве примера я выбрал накат sample-схемы HR на БД Oracle (список всех поддерживаемых БД) — любой желающий может скачать себе репозиторий и поиграться в домашних условиях. Желание продемонстрировать пример вызвано обсуждением этого вопроса на ресурсе sql.ru.

Пару слов для начала

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

1. Apache JMeter – инструмент для нагрузочного тестирования, который способен проводить тесты для JDBC-соединений, FTP, LDAP, SOAP, JMS, POP3, IMAP, HTTP и TCP из коробки и еще множество других протоколов и решений, используя различные плагины.
2. Yandex.Tank – это облачный инструмент для нагрузочного тестирования, использует различные генераторы нагрузки, в том числе и JMeter.
3. Yandex.OverLoad – сервис для удобного мониторинга и анализа серверов под нагрузкой.

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).

Под катом будет небольшая заметка о применении пространственного индекса
на основе zcurve для индексации точечных данных, расположенных на сфере. А так же bencmark-и для PostgreSQL и сравнение с таким же (но совсем другим)
индексом на R-дереве.

Вы наверняка знаете, что в Postgres есть материализованные представления (materialized views) и обертки сторонних данных (foreign data wrappers, FDW). Материализованные представления позволяют материализовывать запросы и обновлять их по требованию. Обертки сторонних данных предоставляют функциональность загрузки данных из внешних источников, таких как, например, NoSQL-хранилища или другие серверы Postgres.

Вероятно, что вариант использования материализованных представлений совместно с обертками сторонних данных вы еще не рассматривали. Материализованные представления ускоряют доступ к данным: результаты запросов сохраняются и отпадает необходимость выполнять их еще раз. Доступ к сторонним данным через FDW может быть довольно медленным, поскольку они находятся в других системах. Объединив эти функции, можно в итоге получить быстрый доступ к сторонним данным.

Александр Коротков, Дмитрий Иванов (Postgres Professional)

Ведущий: Тяжелая артиллерия в лице Александра и Дмитрия будет рассказывать про важную фичу Postgres. И не то, чтобы фичу, а проблему, с которой сталкиваются люди, работающие с Postgres – это то, как правильно секционировать или партиционировать, как вам более удобно произносить, таблицы. И Александр с Дмитрием уже довольно длительное время трудятся над расширением, которое позволяет это делать гибко, хорошо, удобно и быстро.

Александр Коротков: Как правильно уже сказали, наш доклад будет посвящен расширению pg_pathman, которое реализует продвинутое секционирование в Postgres. Основную часть доклада будет рассказывать мой коллега Дмитрий Иванов, который сейчас очень активно включился в работу над расширением pg_pathman, а я буду время от времени что-то добавлять.