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

На этот раз мы решили немного изменить формат Постгрессо: теперь никакого информационного равноправия. Об одних релизах и статьях будем рассказывать подробней, о других — в паре строк. Выбор субъективен, конструктивная критика приветствуется.

Релизы

PostgreSQL 13 beta 3

В 3-й бете есть изменения по сравнению с 2-й бетой, смотрите на страничке релиза.

Одновременно с Beta 3 вышли обновления: 12.4, 11.9, 10.14, 9.6.19 и 9.5.23. В них закрыты две обнаруженные бреши в безопасности, связанные с путём поиска (search_path) элементов (таблиц, функций, операторов и так далее) при создании расширений и при логической репликации. Два с лишним года назад была найдена уязвимость CVE-2018-1058, позволяющая использовать особенности работы с переменной search_path (она определяет порядок поиска в схемах при обращении к объектам БД) для запуска злокозненного кода. При неаккуратном использовании этой переменной, враг может перехватить управление над выполнением запросов и затем запустить произвольный SQL-код с правами атакуемого пользователя. Об этом можно прочитать, например, здесь. Эти опасности были объяснены, меры предосторожности перечислены. Теперь оказалось, что мер недостаточно при логической репликации и при создании расширений.

Теперь при логической репликации процессы send и receive будут исполняться с пустой строкой search_path. При создании расширений атакующий, если у него были права создавать новые объекты в расширении, мог запускать опасный код под суперпользователем. Сейчас расширения подправили и создали инструкцию для тех, кто собирается разрабатывать новые расширения.

За beta3 не последует beta4: релизная группа, то есть Джонатан Кац (Johnathan S. Katz), Альваро Эррера (Alvaro Herrera) и Питер Гайген (Peter Peter Geoghegan) пишут 2 сентября, что, проанализировав незакрытые пункты, можно сразу готовить релиз-кандидат 1 (RC1) на 17-е сентября. И, если критических проблем не обнаружится, 24-го сентября уже основную версию PostgreSQL 13. Ну а о том, много ли нового в Чёртовой Дюжине, мы уже писали.

Новые релизы Postgres Pro Enterprise/Standard

Вышли версии Postgres Pro Enterprise 12.4.1, 11.9.1, 10.14.1, 9.6.19.1, Standard 12.4.1, 11.9.1, 10.14.1, 9.6.19.1.

Изменения в Enterprise 12.4.1, например, такие:

• устранена ошибка в оптимизации планировщика, приводившая к неправильной оценке количества строк при включённом параметре enable_compound_index_stats;
• исправлена ошибка в расширении pgpro_scheduler, приводившая к тому, что функция schedule.stop() могла не остановить выполняющиеся задания;
• улучшено расширение rum: при выполнении запросов с весами теперь не требуется перепроверять результаты этих запросов по таблице, так что они выполняются гораздо быстрее;
• исправлены ошибки и в BRIN;
• устранена ошибка, вследствие которой могли теряться результаты при обработке поисковых запросов с использованием оператора отрицания;
• в новой версии multimaster включена функциональность, реализованная в Postgres Pro Enterprise версии 11.8.1 (ранее она оставалась недоступной при обновлении Postgres Pro Enterprise);
• приложение pg_probackup обновлено до версии 2.4.2.

Last but not least (а для многих и самое важное):
12.4.1 и 11.9.1 теперь умеют — благодаря опыту Антона Дорошкевича (ИнфоСофт) и усилиям Андрея Билле (Postgres Professional) — при установке настраивать инстанс на работу с 1С (pg-setup inidb --tune=1C).

mamonsu 2.5.1

mamonsu — агент мониторинга для сбора метрик операционной системы и Postgres, разработанный Postgres Professional. Главное в новой версии — это окончательный переход на python 3 в связи с тем, что в 2020 году уже заканчивается поддержка python 2. Есть и другие обновления. Например, два новых плагина. Первый плагин (он называется точно так же: pg_probackup) позволяет следить за размером каталогов бэкапа, которые хранят WAL и файлы бэкапа, созданные утилитой pg_probackup.

Второй плагин называется prepared_transaction и дает возможность следить за возрастом самой старой подготовленной транзакции в секундах и количеством всех подготовленных транзакций для двухфазного коммита. Это актуально потому, что в реальной жизни нередко «висящая» подготовленная транзакция не дает работать вакууму.

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

В mamonsu есть плагины, которые появляются при установке (в каталоге mamonsu/plugins). Все они перечислены в примере конфигурационного файла. При установке этот файл автоматически подставляется как конфигурационный файл по умолчанию. Метрики в стандартной установке перечислены в середине файла README.rst. Но у пользователя есть еще и возможность самому написать плагин. Структура каждого питоновского файла, собирающего метрики состоит из определённого набора функций.

В блоге Zabbix выложена статья-расшифровка доклада разработчицы Дарьи Вилковой (Postgres Professional) для Zabbix Meetup Online.

dbForge Studio for PostgreSQL v2.3

Это графический инструментарий для работы с SQL в Postgres под Windows: с редактором кода, разноцветным форматированием его, генератором скриптов и профайлером (окна профайлера можно увидеть на скриншотах). Платный (трайл — 30 дней). Производитель этой студии а также довольно популярной утилиты dbForge Data Compare for PostgreSQL — компания Devart (головной офис в Праге, разработчики в Харькове).

В новой версии революционных изменений нет: например, поддержка PostgreSQL 12 генерация скриптов появились в 2.2, но много удобств добавлено.





Генератор скриптов, который создает соответствующий скрипт в ответ на действия мышью, научился создавать их для: DROP/CREATE TABLE, DROP/CREATE SEQUENCE, DROP/CREATE, CREATE INDEX. Улучшилось форматирование SQL, теперь предложения с CREATE TRIGGER, CREATE INDEX, CREATE SEQUENCE, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW, PROCEDURE\FUNCTION выглядят красиво. Профайлер научился показывать план, не исполняя запрос.


pgagroal 0.8.1

В этой версии пулера улучшена работа с системой мониторинга Prometheus: теперь pgagroal указывает серверы, которые отказали, и показывает ошибки на них. Релизы pgagroal выходят часто. В предыдущей — в версии 0.8.0 — пулер обучился переподключению (failover) и поддержке systemd. В 0.7.0 — в конце мая — появилось удалённое управление.


pg_dumpbinary 2.2

Новая версия программы Жиля Дароля (Gilles Darold), которая сохраняет дамп в бинарном формате и восстанавливает базу командой pg_restorebinary, приблизилась к поведению самой pg_dump: теперь выгружаются таблицы и последовательности расширений, зарегистрированные функцией pg_extension_config_dump. Подробный чейнджлог релиза здесь. Загрузить можно отсюда.

PostGIS 3.0.2

Вместе с этой вышли и версии 2.5.5, 2.4.9 соответствующих стабильных веток. Работают с PostgreSQL 13 beta2 и ниже вплоть до PostgreSQL 9.5 и с GEOS позже версии 3.6. Исправление ошибок, принципиальных изменений не видно.

pg_probackup 2.4.2

В этой версии утилиты появились пакеты SUSE. Кстати, в разделе Статьи есть план целой серии статей о pg_probackup.

Foreign Data Wrapper for SQLite 1.2.1

Toshiba Software Engineering & Technology Center сообщает о новой версии. Она работает с PostgreSQL 9.6, 10, 11 и 12. Улучшено:

• добавлен пушдаун Limit/Sort в SQLite;
• тип SQLite datetime numeric конвертируется в PostgreSQL TimeStamp

FDW поддерживает:
SELECT, INSERT, UPDATE and DELETE на внешних таблицах; транзакции.
Пушдауны:

• WHERE-предложений;
• агрегации;
• ORDER BY;
• LIMIT и OFFSET (*в случае, когда все таблицы, к которым обращаются, внешние);

Детали есть в репозитории с исходниками.

Статьи

PostgreSQL 14: Часть 1 или «июльский разогрев» (Коммитфест 2020-07)

Обстоятельная статья, в ней отражены все новые коммиты, а многие из них проиллюстрированы примерами. Причем некоторые относятся к версии 13. Сейчас появились и будут появляться статьи, посвященные отдельным или нескольким новшествам в PostgreSQL 14, но вряд ли появится текст, касающийся стольких коммитов.

На эту же тему, понемножку:
у Хуберта Любашевски (aka depesz) в серии Waiting for PostgreSQL 14:

• Rename wal_keep_segments to wal_keep_size.
• pg_stat_statements: track number of rows processed by some utility commands.
• Improvements for handling large number of connections (серия патчей по оптимизации снэпшотов)..

Пока 3 темы, но продолжение несомненно последует.

Как Lingualeo переехал на PostgreSQL с 23 млн юзеров

Полное название начинается с «В карантин нагрузка выросла в 5 раз, но мы были готовы». Статья главного бэкэнд-разработка компании Lingualeo Олега Правдина вызвала интерес слоёв более широких, чем сисадмины PostgreSQL. На момент Postgresso 24 уже 772 комментария при 44К просмотров. Может потому, что в статье много об административных преобразованиях, поиске исполнителей, может из-за того, что при переходе на новую систему сознательно грохнули некоторое количество (примерно 1%) пользовательских данных. Ну и стартовали холивары на темы NoSQL vs классика и логика внутри базы vs логика в приложениях.

Lingualeo это сервис с 23 миллионов пользователей из России, Турции, Испании и стран Латинской Америки, которые учат с его помощью английский, 100 тыс. одновременных пользователей в пике. У пользователей есть свои собственные словари (они и пострадали частично), Джунгли, курсы. Всё это работало на PHP/MySQL. Теперь всю логику перенесли в базу с хранимками на PL/pgSQL.

В статье не густо технических подробностей (обещают целую серию статей в ближайшем будущем), но их можно найти в комментариях под статьёй. Например, вот так там описано новое решение:

1. фронт дергает ручку «Список покупок»;
2. прокси-сервис [на Go, занимается балансировкой запросов к Мастеру и Слйэвам, плюс обеспечивает взаимодействие с внешними сервисами] получает запрос и дергает соответствующую хранимку в базе (в этой точке возможен гибкий роутинг к слэйвам, например);
3. хранимка формирует ответ в виде json. В ответе есть атрибут с инструкцией для прокси-сервиса: «вызови микросервис sms_sending, вот ему json с параметрами»;
4. прокси-сервис выполняет инструкцию;
5. прокси-сервис отправляет готовый ответ на фронт (п. 4 и 5 могут параллельно выполняться, если независимые).

Нужно:

1. разработать хранимку (PL/pgSQL) на 50-100 строк;
2. время разработки и отладки: 1 — 2 часа;
3. скорость отклика: 1 — 2 мсек (если структура данных правильная);

Прокси-сервис отдаёт готовый JSON на фронт.

Коллеги подсказали мне, что, судя по этим сведениям, в Lingualeo на практике применили подход, теоретически обоснованной в таких статьях как Connecting Galaxies: Bridging the Gap Between Databases and Applications (соавтор статьи, которой, увы, нет в бесплатном доступе, Борис Асенович Новиков, автор учебника Основы технологий баз данных) или в более ранней — Talking To The Database In A Semantically Rich Way. Суть в том, что несоответствие в моделях данных между объектно-ориентированными приложениями и реляционными СУБД может стать роковым для данных. Чтобы его преодолеть, надо обмениваться не отдельными строками отдельных таблиц, а составными объектами (используя, например, JSON, как транспортный формат).

В статье Lingualeo нет ни слова о полнотекстовом поиске, есть только задача:

Чтобы заработала функция перевода слов по клику, текст надо разделить на слова, выражения и словосочетания. Затем — обратиться к словарю и вывести пользователю перевод в новом окошке поверх текста. Бэк передаёт на фронт готовый JSON, в котором текст уже разбит на слова и выражения.

Но в комментарии автор поясняет:

Мы упростили всю систему, исключив rabbit, kafka, elastic search и др. FTS (full-text search) в PG позволяет находить необходимые данные в базе из миллионов документов за 2-3 мсек — для наших задач более чем достаточно…

… реализовал в хранимке токенизацию текстов с иероглифами (когда идет сплошной список иероглифов, без проблемов, и их надо правильно разбить на слова и выражения, словарь прилагается в таблице). Хранимка с рекурсивным CTE, 50 строк, примерно час ушел на разработку. Скорость обработки в 20 раз быстрее, чем скрипт на питоне. И по размеру кода в 10 раз меньше.
Надеемся на разъяснения в грядущих статьях.

Знакомство с pg_probackup. Первая часть

ЖЖЖЖЖ

Александр Никитин из «БАРС Груп» написал статью об этой утилите Postgres Professional. В первой части он рассказывает о резервном копировании. Дальше будет о восстановлении. Вообще запланировано рассмотреть прежде всего 4 темы:

• создание автономных бэкапов на отдельном сервере
• создание архива WAL-файлов и создание бэкапов в этом режиме
• развёртывание реплики из бэкапа и настройка создания бэкапов с реплики
• различные варианты восстановления;

Путеводитель по резервному копированию баз данных

Владимир Комаров aka hard_sign рассматривает резервное копирование во всех основных СУБД (Oracle, DB2, MS SQL, MySQL, а в эпизодах и MongoDB, Cassandra, Percona Server), в том числе и в PostgreSQL, конечно. В главке об инкрементальном резервном копировании несколько абзацев посвящены pg_probackup.

Эта статья часть мощной серии:
Путеводитель по репликации баз данных
Классификация критичности информационных систем
Распределённые СУБД для энтерпрайза.

Из более ранних есть и остросюжетная: Так что же случилось со Сбербанком?

Why PostgreSQL 13 is a Lucky Release

Джонатан Катц (Johnathan S. Katz, Crunchy Data) справедливо считает, что этот релиз не был решающим прорывом с точки зрения наращивания функциональности, но что это «хорошая штука для всех» — каждый найдёт в нем что-нибудь для себя, из-за чего стоит немедленно проапгрейдиться до PostgreSQL 13.

Прежде всего — считает он — это сокращение размера индексов (дедупликация b-tree). На демонстрационном примерчике выигрыш в три раза по объему и в два раза по производительности. Далее: вакуум работает побыстрее за счет того, что индексы, опять же, во время чистки таблицы обрабатываются параллельно, при этом число параллельных процессов настраивается. Еще Джонатан отмечает как особо важную фичу инкрементальную сортировку. Напоминаем, что в нашей статье о PostgreSQL 13 эти пункты рассмотрены немного подробней (кстати, выигрыш в объеме индекса у нас тоже в 3 раза), но пунктов там гораздо больше, что преимущество и недостаток одновременно — в зависимости от цели читателя.

Avoiding the Pitfalls of BRIN Indexes in Postgres

Джон Порвазник (John Porvaznik, Crunchy Data) для своих примеров генерит табличку с рандомными данными, затем рассматривает структуру индекса BRIN (Block Range INdex), используя расширение pageinspect. На вопрос, заданный себе «когда стоит использовать BRIN?», автор отвечает предложением «когда таблица large insert-only in-order» и дальше останавливается на влиянии каждого звена этой конструкции. Индекс нежный, деликатный. Неожиданное отклонение в каждом из этих звеньев может свести на нет все прелести BRIN, поэтому перед запуском в прод стоит хорошенько промоделировать проект.

How we used Postgres extended statistics to achieve a 3000x speedup

Статья на сайте компании Affinity, известной больше как разработчик инструментов дизайна, рассказывает о том, как их инженеры решили проблему с долгим откликом на их сайте. Проблема была в том, что оптимизатор радикально промазал с оценкой кардинальности — ждал одну запись там, где их тысячи. Автор — Джереж Ралисон (Jared Rulison) — коротко и внятно объясняет важность корреляций при сборе статистики, какие неприятные сюрпризы учёт корреляций может подложить, как оптимизатор может выбрать совсем не оптимальный тип джойна (что и случилось — nested loop вместо hash join). Чтобы вразумить оптимизатор надо проделать некоторые дополнительные действия при сборе статистики.

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

A Crash Course on PostgreSQL for R Users

Союз R и PostgreSQL — нечастая тема. В нехитрой статье и примерах используется демонстрационная база Полётов Нью-Йоркских аэропортов (во flights14 > 12 млн записей). Попробуйте демобазу наших аэропортов — она побогаче семантически. Расширение plr Джо Конвея (Joe Conway), позволяющее хранить и исполнять пользовательские R-функции в базе, не используется. Автор обходится обычными соединениями при помощи RPostgres. Используется библиотеки Tidyverse, dplyr и другие. Есть полезные ссылки.

Building a recommendation engine inside Postgres with Python and Pandas

Крейг Кирстинс (Craig Kirstiens) из Crunchy Data решил построить движок рекомендательного сервиса прямо внутри PostgreSQL, то есть используя хранимые функции на plpython3u.
Он взял простенький пример движка на Python с демоданными, загрузил данные в Postgres. Там, где в Python был тип DataFrame, Крейг использует массивы Postgres.


Data systems that learn to be better

Эдам Коннер-Саймонс (Adam Conner-Simons) из Computer Science and Artificial Intelligence Laboratory (CSAIL, лаборатория внутри MIT) пишет о проектах со зловещими именами: Цунами (Tsunami) и Bao (BAndit Optimizer).

Цунами основан на теоретической статье The Case for Learned Index Structures (по ссылке только аннотация), написанной в 2017-м профессором MIT Тимом Краска (Tim Kraska) с соавторами и соратниками из Google. Статья тогда наделала шуму в Postgres-сообществе. Там говорилось: «идея в том, что модель может выучить порядок сортировки или структуру словарных ключей (structure of lookup keys) и, исходя из этой информации, определять оптимальную позицию записи в индексе или вообще ее необходимость.»

«Приближается революция?» — спрашивал Николай Самохвалов. Мало кто верил, что обучающиеся индексы действительно заменят B-деревья, хэш-индексы и Bloom-фильтры. В качестве «трезвого взгляда» Олег Бартунов, например, приводил исследование, где ИИ работал не лучше интерполяции сплайнами:
The Case for B-Tree Index Structures Томаса Ноймана (Thomas Neumann). Никто из разработчиков PostgreSQL, во всяком случае, не реализовал работоспособные обучающиеся индексы.

Краска, однако, представил проект Tsunami в стенах CSAIL. Он утверждает, что на тестах удаётся достичь выигрыша в скорости исполнения запросов на порядок, а данные можно организовать в наборы обученных индексов, сократив суммарный объем на два порядка (примерно как он и предсказывал в той статье).

Кроме того, с коллективном другого состава под руководством Райана Маркуса (Ryan Marcus) он участвует в проекте Boa (аннотация), где, как утверждается, оптимизатор, полностью интегрированный в PostgreSQL, учится меньше часа на собственных ошибках, после чего составляет план так, что бьёт по производительности опенсорсные и коммерческие СУБД.

А цель CSAIL — объединить эти два проекта в один, который будет работать в существующих облачных инфраструктурах, таких как амазоновский Redshift. Скептики не ведутся: «любопытно, но я что-то не знаю ни одного, кто бы над этим работал» — пишет Брюс Момджан (Bruce Momjian). Но Дмитрий Долгов видел сообщения, что о планах реализовать Bao как опенсорсный проект, хотя никакого взаимодействия с Postgres-сообществом пока не замечено.

Образование

Вышла новая английская версия «Малютки» — PostgreSQL: The First Experience

В этом издании примеры на PostgreSQL 12. Загрузить PDF можно бесплатно отсюда. А русская версия здесь.

Облака

Postgres Pro на Azure, Mail.ru и Яндексе

Виртуальные машины, с вышедшими в конце августа новыми минорными версиями Postgres Pro, появились в облаке Microsoft Azure. Там есть виртуальные машины Postgres Pro Enterprise и Postgres Pro Standard версий 9.6.19.1, 10.14.1, 11.9.1 и 12.4.1 (виртуальные машины Postgres Pro Enterprise версий 10.14.1, 11.9.1 и 12.4.1 в двух вариантах — с ОС CentOS и ОС Ubuntu).

Кроме этого, Postgres Pro Standard версий 11 и 12 предлагается в облаке Microsoft Azure в виде образов Docker-контейнеров — и в составе виртуальной машины, и в виде самостоятельного контейнера. Немало: 13 приложений на фоне примерно 60, имеющих отношение к Postgres. В том числе, например, PostgreSQL Hosting: Fully Managed DBaaS on Azure.

В Яндекс.Облаке стали доступны виртуальные машины с Postgres Pro Enterprise 11.9.1 и 12.4.1. В их составе pg_probackup, CFS, multimaster и прочие Enterprise-возможности, а также установленные и настроенные сервер Zabbix и агент mamonsu. Руководство по созданию и использованию Postgres Pro Enterprise в Яндекс.Облако здесь. Незадолго до этого Postgres Pro в виде DBaaS появилась в облаке Mail.Ru Cloud Solutions — пока только с Postgres Pro Standard 11.

У Яндекса есть и своя PostgreSQL — Yandex Managed Service for PostgreSQL (кластеры с версиями 10, 11 и 12, а также PostgreSQL 10 для 1C. О производительности 1С в Яндекс.Облаке есть ролик в разделе Вебинары). . Есть довольно внушительный список расширений. Вообще, сравнивать облачные предложения занятие полезное и интересное, но не для этого новостного обзора — слишком много вариантов. Плюс облачники обычно не стремятся сразу выставить на общее обозрение адекватную для сравнения техническую информацию.

Announcing pgBackRest for Azure: Fast, Reliable Postgres Backups

Крейг Керстинс (Craig Kerstiens) рассказывает о pgBackRest, который теперь может работать в облаках Azure.

DB-Engines Ranking — Trend Popularity

Это рейтинг облачных СУБД по некоторому набору критериев. PostgreSQL примостился за Oracle, MySQL и Microsoft SQL Server. Но если глянуть кривые популярности, то видно, что эти трое стоят на месте (и даже чуть заваливаются), а наш красавец упрямо карабкается вверх (но MongoDB цепляется за пятки).

Вебинары и митапы

#RuPostgre

Ровно в начале учебного года — 1 сентября — Николай Самохвалов с Ильей Космодемянским начали с Интро новый сезон RuPostgres-вторников. В ближайших стримах главный фокус будет на «разработческих» темах: великий и ужасный SQL, сложные запросы, JSON, оптимизация производительности, отладка, ORM, GraphQL и т.д. и т.п. Но и «админские» темы постараются не забывать. Документ с инфо, куда можно вписывать пожелания, здесь.

Вебинар 1С на Postgres в облаке

Yandex.Cloud выложили ролик (около 30 мин, начинается почему-то на 9:35): Марат Мустафин, руководитель Центра разработки компании мудрых советов — WiseAdvice (основной партнер Yandex.Cloud по 1С) — рассказывает о нагрузочном тестировании (22:00), о требованиях к оборудованию, настройках PostgreSQL (в том числе отключение синхронного коммита) на сетевых и локальных SSD, влияющих на производительность, зависимости выбранного размера дисков на скорость работы, надежность и масштабируемость приложений.

Разворачивалось всё и тестировалось на кластере под Windows в яндексовском Managed Service for PostgreSQL, куда входит и их пулер Odyssey. Версия PostgreSQL 10-я (ведутся работы по переходу на 11-ю). Тесты: 1C:ERP тест-центр и синтетический Тест Гилёва. Тестирование вызывает много вопросов. Про Тест Гилёва Марат так и говорит: результаты слишком неоднозначные, и вообще это лишь начало всестороннего тестирования.

Вебинары 2ndQuadrant

JSON & ARRAY – Contemporary PostgreSQL Data Types

Состоялся 2-го сентября. Ведущий — Борис… — нет, не знаю, как произнести его фамилию: Boriss Mejias.

New Features in PostgreSQL 13

Ожидается 16-го сентября, в 19:00. рассказывать будет Питер Айзентраут (Peter Eisentraut)

Конференции

pgDay Israel 2020

Должен состояться уже 10-го сентября в Тель-Авиве.

---

Предыдущие выпуски:
#23, #22, #21, #20, #19, #18, #17, #16, #15, #14, #13, #12, #11 (спец), #10, #9, #8, #7, #6, #5, #4, #3, #2, #1