ИТ-инфраструктура — это как водопровод, без неё жизнь уже почти невозможна. И в эти безрадостные дни мы продолжаем выпускать Postgresso.

PostgreSQL 14.3

Вышла версия 14.3 (release notes), и обновлены предыдущие ветки: 13.7, 12.11, 11.16, и 10.21 (объявлено, что ветвь PostgreSQL 10 не будет обновляться с 10-го ноября 2022-го).

В версии много исправлений, отметим два. Александр Лахин из Postgres Professional обратил внимание на лазейку:

В случае, когда привилегированный пользователь работает с объектами другого пользователя, команды REINDEX, CREATE INDEX, REFRESH MATERIALIZED VIEW и CLUSTER отрабатывали недостаточно безопасно. Это же относится и к autovacuum, и pg_amcheck. Они активировали релевантную защиту (заключали процессы в песочницу операций, ограниченных соображениями безопасности — «security restricted operation» sandbox) или слишком поздно, или вообще не активировали её. Если у атакующего были привилегии создавать не временные объекты хотя бы в одной схеме, он мог запустить под суперпользователем зловредные SQL-функции.

Похожая проблема, но с конструкциями DECLARE CURSOR… WITH HOLD и вызовом отложенных триггеров в выражениях индексов и запросов матпредставлений была замечена и ликвидирована ещё в PostgreSQL 12. Новый баг поправлен во всех версиях PostgreSQL от 10 до 14.

Второй исправленный баг требует реиндексации после апгрейда всех GiST-индексов по столбцам, использующим тип данных ltree. Их создаёт расширение ltree для представления меток данных в иерархической древовидной структуре. Оно также предоставляет расширенные средства для поиска в таких деревьях.

О релизе можно прочитать здесь.

Postgres Pro Enterprise 14.2.1

Выпуск основан на PostgreSQL 14.2 и включает все новые возможности, появившиеся в PostgreSQL 14, а также исправления ошибок, вошедшие в корректирующие выпуски PostgreSQL 14.1 и 14.2. По этой же ссылке перечислены значимые отличия этой версии от Postgres Pro Enterprise 13.6.1.

PG, AI и ML

RelationalAI Raises $122M to Redefine How Intelligent Data Apps Are Built

RelationalAI — это система реляционных графов знаний (relational knowledge graph system). Она поддерживает SQL, но имеет ещё и развитые средства, ориентированные на тех, кто делает приложения машинного обучения и ИИ. В компании разработали соответствующий декларативный язык — Rel. Он должен вобрать лучшее из двух языковых миров: реляционное исчисление; принципы языка Datalog (декларативный язык логических вычислений, подмножество Prolog-а), логической архитектуры HiLog-а и Alloy (язык спецификаций, который используют для валидации сложных моделей) — эти три должны быть удобны для специалистов по ИИ. При этом, утверждают создатели, синтаксис интуитивен и очень компактен. Во всяком случае убедить инвесторов разработчикам удалось.

PostgresML

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

Это расширение (pgml), которое задействует PL/Python. Можно установить сразу всё, что нужно для работы в докере, а можно собрать как расширение. Ссылки на этой странице.

CQL Filtering in pg_featureserv

pg_featureserv — это расширение, разработанное Crunchy Data. CQL (Common/Contextual Query Language) — логический язык запросов, напоминающий SQL.

Пример синтаксиса:

continent = 'Europe' AND pop_est <= 5000000

pg_featureserv ориентировано прежде всего на работу через Web с PostGIS. Использует новейший протокол OGC API for Features (OAPIF) RESTful. Написан на Go. Загрузить можно отсюда.

Pl/Perl Recursion

Не имеет прямого отношения к ИИ, но всё же: Лука Феррари (Luca Ferrari) показывает, как построить последовательность Падована средствами Pl/Perl, используя рекурсию. Это не то же, что решать задачу на «обычном» Perl: в Pl/Perl непосредственно недоступны Perl-объекты, работать с ними можно только через SQL-объекты (функции, например). Лука показывает несколько способов построить рекурсию, а потом сравнивает их быстродействие. Быстрее всех работает решение с перловым модулем Sub::Recursive.

emacs и vim

Formatting SQL code with pgFormatter within Emacs

Лука Феррари фанат Emacs и рассказывает, как приспособить pgFormatter Жиля Дароля (Gilles Darold) для работы в любимом редакторе. Можно просто запускать pgFormatter внутри Emacs, но Лука ещё и написал «простой и уродливый кусочек Lisp-кода, загрузив который, можно быстрей и удобней пользоваться pgFormatter».

Formatting SQL code with pgFormatter within Vim

Воодушевлённый примером Луки, Джож Толли (Josh Tolley, End Point Dev) опубликовал заметку, как заставить работать pgFormatter в редакторе Vim. Он тоже написал кусочек кода, не претендующего на универсальность и совершенство, зато использующий некоторые полезные свойства Vim. Комментарии и предложения автор предлагает постить на его гитхаб.

Мониторинг

Point-in-Time PostgreSQL Database and Query Monitoring With pg_stat_statements

В статье Райан Буз (Ryan Booz, Timescale) останавливается на 4 темах:

подготовка к хранению снэпшотов;
получение снэпшотов данных запросами к pg_stat_statements;
сжатие, постоянное агрегирование, политики хранения данных (Data Retention);
улучшение автоматического мониторинга запросов с pg_stat_statements.

Всё это делается в статье с использованием TimescaleDB: в ней данные снэпшотов накапливаются, интервалы сбора снэпшотов устанавливаются её планировщиком (scheduler). Top 100 самых дорогих запросов определяются SQL-запросами к сохраненным снимкам.

Интересно сравнить такой способ мониторинга со специализированным инструментом PWR (pgpro_pwr) / pg_profile. Там анализируются снэпшоты с многих системных таблиц, многоe делается автоматически. Для вывода там используется обычный html. Райан пользуется для визуализации Grafana.

Google Cloud PostgreSQL Under pgwatch2 Monitoring

Каарел Моппел (Kaarel Moppel, Cybertec) рассказывает, как мониторить, когда ничего нельзя — в том числе нет доступа к файловой системе. Каарел показывает, что и в таких экстремальных условиях их утилита pgwatch2 будет работать. Хранить метрики он советует в TimescaleDB. И, конечно, установить расширение pg_stat_statements.

Новейшая версия pgwatch — 1.9.0. На этой странице описаны изменения. В том числе теперь она соответствует новым версиям Go и Grafana.

Understand Your PostgreSQL Workloads Better with pg_stat_monitor

Мэт Йонковит (Matt Yonkovit, Percona) предлагает воспользоваться перконовским инструментом мониторинга pg_stat_monitor, который достиг фазы зрелости general availability (новейшая версия — стабильная 1.0.0).

pg_stat_monitor собирает данные из pg_stat_statements во временнЫе ведёрки (buckets), чтобы смотреть динамику по заданным промежуткам времени. По умолчанию это 10 ведёрок по 60 сек данных каждая. Есть политики хранения данных этих ведёрок. Если нужно хранить данные долго, pg_stat_monitor можно использовать вместе с другим инструментом хранения и/или мониторинга.

Умеет разбивать данные по IP пользователя. Умеет хранить, если потребуется, планы запросов (explain), собирает данные по затратам времени CPU. Опять же, интересно сравнить с PWR.

JSON(B)

Better JSON in Postgres with PostgreSQL 14

Грег Керстинс (Craig Kerstiens, Crunchy Data) рассказывает о том, насколько удобней работать с JSON(B) в 14-й версии по сравнению с предыдущими. Ему нравится новый синтаксис с индексной нотацией (как у массивов, по-английски subscript syntax), который позволяет обойтись без -> и ->>; нравится, как теперь работает GIN-индекс для JSONB.

Но ведь ещё более интересные изменения произошли в PostgreSQL 15. Ссылки на статьи об этом были в нашем прошлом выпуске, здесь же напомним о статьи Олега Бартунова в Живом Журнале —
SQL/JSON in PG15! и о более подробной статье на английском в блоге Postgres Professional: SQL/JSON patches committed to PostgreSQL 15!

Также советуем обратить внимание на статью Олега о TOASTER:

One TOAST fits ALL: JSONB TOASTER

На летний коммитфест ушёл предложенный Pluggable toaster. Это API для TOAST, продолжение идеи AM API для методов доступа. Если воспользоваться таким API, то можно сделать работу с TOAST для некоторых типов данных гораздо более эффективной, так как внутренняя структура такого TOAST предсказуема. Разработчики Postgres Professional реализовали и расширение jsonb_toaster, работающее с этим API (оно будет опубликовано позже), дающее большой выигрыш в производительности.

На эту тему был и доклад Олега Бартунова, Фёдора Сигаева и Никиты Малахова на только что завершившейся Highload++ Foundation.

В той же статье упоминается GSON — Generic JSON API, с которым можно будет работать и с JSON, и с JSONB.

Распределенные базы и шардинг

Free O'Reilly Report on Distributed SQL Databases

Появилась новая категория баз данных — распределённые базы данных. И это — событие. Во всяком случае, об этом говорят Cockrouch Labs в своём блоге, ссылаясь на экспертов О'Рейли. Их исследование можно скачать бесплатно, но с регистрацией.

How sharding a database can make it faster

Написана статья Тристой Хуан Пан (Trista Juan Pan), которая известна в кругах open source как 2-й контрибьютор Apache ShardingSphere, обладательница наград 2020 China Open-Source Pioneer и 2021 China OSCAR Open Source Pioneer. Статья не техническая, там общие соображения и стратегии.

О производительности и тестировании

Как работает оптимизатор PostgreSQL при большом количестве таблиц в запросе

Павел Толмачёв, наш коллега из отдела образования Postgres Professional написал основательную статью, в которой есть и теоретическая часть с наглядными схемами, и практическая: тестирование. Вот, например, что в статье говорится (болд мой):

Запрос можно написать разными способами — перечислить таблицы через запятую, использовать ключевое слово JOIN, либо использовать смешанный вариант (не рекомендуется). Хотя результат работы таких запросов будет идентичен, но синтаксическая структура запроса будет отличаться (ниже показано как именно она будет отличаться). А раз отличается синтаксическая структура — то и оптимизатор PostgreSQL будет обрабатывать эти запросы по-разному.

Павел сгенерировал группу запросов (в каждом следующем запросе количество таблиц увеличивается на единицу), после их выполнения высчитывает среднее время планирования десяти попыток. Для тестирования используются простейшие таблицы из десяти строк. А график строится вплоть до 100 таблиц. Из статьи читатель узнает, насколько важны параметры

geqo,

geqo_threshold,

и join_collapse_limit.

Павел отсылает интересующихся к учебному курсу QPT. Оптимизация запросов, к серии статей Егора Рогова Запросы в PostgreSQL и к его же книге PostgreSQL изнутри.

Using pgTAP to automate database testing

Джош Толли (Josh Tolley, End Point Dev) рассказывает о тестировании с pgTAP. TAP расшифровывается как Test Anything Protocol, а pgTAP это набор функций для тестирования, которые тестируют, будучи внутри базы. В статье есть примеры, но не меньше рассуждений в жанре зачем? с чего начать?

How we optimized PostgreSQL queries 100x

Вадим Марковцев применяет некоторые приёмы, и эти приёмы укладываются в темы: LEFT JOIN с дополнительными (лишними) условиями; расширенная статистика; нюансы выбора типа данных для первичных ключей; команда CLUSTER.

Интересная деталь: Вадим говорит о том, что ему до некоторых пор не приходилось иметь дело с жёсткими требованиями по производительности, с миллионами записей, о невозможности использовать perf (один из любимых инструментов аналитиков в Postgres Professional) из-за облачных ограничений, и о том, что до конца 2021-го не было pg_hint_plan, который ему, выходит, сильно помог — об этом есть отдельная главка статьи. Напомним, что в Postgres Pro Enterprise расширение pg_hint_plan, разработанное в Японии, входит аж с версии PostgreSQL 9.6. Но в статье, напоминаем, речь об облачном решении.

Ещё некоторые статьи

8 Fascinating Things You Probably Didn't Know PostgreSQL Can Do!

Том Браун (Thom Brown, EDB) предлагает действительно интересные трюки, которые мало кому придут в голову, но имеют практический, а не развлекательный смысл. Например, конструкции с таким странным SELECT могут пригодиться для сравнения таблиц (главка Whole-row References):

SELECT my_table FROM my_table;

Некоторые трюки укорачивают код и упрощают его чтение (главка Comparing a selection of columns); некоторые помогают создать себе подсказки, заодно объясняя, как можно использовать собственные параметры в postgresql.conf (главка Custom Config Parameters); некоторые просто расширяют кругозор — Том показывает, как созданную таблицу можно использовать как новый тип данных (главка Tables are Types). Есть и другие интересные трюки.

Analyzing Historical Flight Data With MobilityDB

Многие помнят выступления по MobilityDB на наших конференциях: MobilityDB: расширение PostgreSQL для управления мобильными данными на PGConf.Russia 2019 и Анализ движения наземного общественного транспорта Москвы: от PostGIS к MobilityDB на PGConf.Russia 2020.

На этот раз работу с MobilityDB демонстрирует Флориан Надлер (Florian Nadler, Cybertec) в блоге своей компании. MobiblityDB — это расширение, оно требует сначала установить расширение PostGIS. Для демонстрации Флориан пользуется открытыми данными OpenSky-Network. В анализируемой таблице около 52 млн. полётов.

Образование

В рамках обновления курсов для администраторов на 13-ю версию выпустили курсы DBA1, DBA3.
Свежий DBA1 прочитали и записали на видео в Элисте 19-21.04. Сейчас идет монтаж видео.

Некоторые релизы

pg_ivm 1.0

Расширение pg_ivm расшифровывается как Incremental View Maintenance. Его создателю, Юго Нагата (Yugo Nagata), не удалось продвинуть соответствующий патч ядра в версию PostgreSQL 15. Чтобы не ждать долго, он решил реализовать IVM как расширение. Здесь объясняется, зачем нужно IVM, и как оно работает.

pgAdmin4 6.9

Новое: добавлен статус бар для Query Tool; появились опции Ignore owner и Ignore whitespace. О других новшествах и исправленных багах — на той же странице.

bun

ORM для PostgreSQL, MySQL, MSSQL, и SQLite на Go. Код находится на гитхабе uptrace, где можно найти средства трассировки, разработанные при поддержке OpenTelemetry и ClickHouse. Последняя версия bun — 1.1.5. В ней нет существенных изменений.

bun работает с PostgreSQL, MySQL (и MariaDB), MSSQL и SQLite. Умеет делать массовые INSERT, UPDATE (используя CTE) и DELETE. Есть средства миграции, тоже как Go-функции.

На странице в гитхабе есть ссылки на примеры, дискуссии и прочее.

pg_back 2.1.0

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

В новой версии:

добавился режим quiet (задаётся -q/--quiet), приоритетный по отношению к режиму verbose;
вычисляются контрольные суммы глобальных и ACL-файлов;
сбрасываются hba_file и ident_file;
шифрование и дешифровка полученных файлов;
выгрузка файлов на AWS S3, Google Cloud Storage, Azure Blob Storage или удалённый хост по SFTP;
системный таймер для Debian в пакетах, гарантированных goreleaser;
проверка синтаксиса конфигурационного файла.

Apache AGE(incubating) 1.0.0

Apache AGE(incubating) — расширение PostgreSQL (AGE = A Graph Extension), которое добавляет функциональность графической базы данных. В AGE единое хранилище для реляционных данных и данных графической модели, и запросы к ним можно делать как на ANSI SQL, так и на графовом языке запросов openCypher.

В релизе 1.0.0 появились, например, функции для загрузки графов из CSV-файлов; поддержка MERGE (не путать с MERGE, который только что попал в PostgreSQL); поддержка UNWIND.

Видеобеседы

Postgres.tv Open Talks

Это уже традиционные четверги с англоязычными гостями, которые проводят Николай Самохвалов и Илья Космодемьянский.

Open Talk #003. Do you vacuum everyday? — Ханну Кросинг (Hannu Krosing);
Open Talk #002. Past, Present, and Future of Logical Replication — Амит Капила (Amit Kapila);
Open Talk #001. Postgres in a Microservices World — Брюс Момджан (Bruce Momjian).

Конференции

PGConf.Russia 2022

Только что закончился приём заявок на доклады. Сама конференция состоится 20–21-го июня.

Highload++ Foundation/2022

Состоялась 13-14-го мая в Крокус-Экспо, Москва. Много Яндекса, много Go, много AI/ML. Про СУБД, кажется, меньше, чем обычно. Все тезисы докладов здесь.

Мы упоминали о докладе про TOASTER. Вот ещё некоторые интересные темы:

Подсистема I/O в PostgreSQL: архитектура, проблемы, обходные пути — Артем Сергиенко, Wrike;
Потоковая обработка BigData для МТС [в том числе с PostgreSQL] — Евгений Ненахов, МТС Digital.

PGDay Israel 2022

Состоится 20-го октября. Заявки принимаются.

Percona Live 2022 состоится 16-18-го мая в Остине, Техас.

Среди докладов, например:
Managing PostgreSQL With Ansible — Андреас Шербаум (Andreas Scherbaum, Adjust GmbH).

PGConf.EU 2022 состоится 25-28-го октября в Берлине.

До встречи через месяц, коллеги.

Постгрессо №4 (41)