Postgres и PostgreSQL


PostgreSQL 15.1

И, конечно, PostgreSQL 14.6, 13.9, 12.13, 11.18 и 10.23 (у десятки это последний релиз). В этих версиях исправления багов. Здесь мы их описывать не будем. Ещё обновились названия временных поясов на актуальные. О релизе можно прочитать здесь.

Postgres Pro Standard 15.0.1 — они возвращаются

Об этом релизе расскажем подробнее — там важные изменения. И важнейшее из отличий — SQL/JSON: в версию Pro вошли патчи, которые откатили в PostgreSQL 15. А их очень много, и есть существенные. В прошлом Postgresso мы печалились:

Патчи отложили на PostgreSQL 16. Чтобы корректно откатить SQL/JSON, пришлось изменить 60 (!) файлов. Среди того, что откатили:

Но говорили мы это, зная, что эти патчи, над которыми долго работали в Postgres Professional, наверняка войдут в новые релизы Postgres Pro. Так и случилось: те куски (функциональность), что вызывали вопросы у сообщества, дорабатываются, остальное вошло в релиз, не дожидаясь утверждения в сообществе, нередко довольно инерционного. Соответственно, теперь часть доклада PostgreSQL 15: MERGE и другие, где Павел Лузанов рассказывает о SQL/JSON, можно уже слушать не (только) с образовательной целью, а и вполне с практической.

В документации новшества, связанные с SQL/JSON, вошли в раздел 9.16. Функции и операторы JSON. Теперь всё о (SQL) JSON(B) сгруппировано в документации так:

9.16.1. Обработка и создание данных JSON
9.16.2. Язык путей SQL/JSON
9.16.3. Функции и выражения SQL/JSON
9.16.4. JSON_TABLE (это точно last-but-not-least).

Кроме того:
  • добавление модуля tds_fdw (для общения с MS SQL, см. например, статью Интеграция PostgreSQL с MS SQL Server);
  • совместимость с расширением TimescaleDB;
  • поддержка реализации ICU в соответствии с «ванильной»;
  • поддержка процессоров Эльбрус;
  • добавлен модуль rum, обеспечивающий индекс RUM, основанный на GIN (был только в Postgres Pro Enterprise);
  • добавлено расширение pg_wait_sampling — можно следить за происходящим на сервере, в том числе за ожиданиями всех процессов;
  • проапгрейдили mamonsu до 3.5.2, pg_probackup до 2.5.9, pgpro_pwr до 4.1 (добавилась статистика работы JIT).

Полный список обновлений можно увидеть в замечаниях к выпуску.

А вот поддержки Windows в Postgres Pro Standard 15.0.1 уже нет. Об этом предупреждали в Postgres Professional в августе: Postgres Pro будет постепенно снижать степень поддержки ОС Windows в своих продуктах. Немалые усилия, тратившиеся на поддержку Windows-версий плохо сочетаются с тем фактом, что масштабные и нагруженные системы, на которые, в общем, и ориентированы прежде всего версии Postgres Pro, на Windows практически не встретишь. В CNews откликнулись в начале ноября: Самая знаменитая российская СУБД прекращает поддерживать Windows: Она больше не популярна.

Изменения не затронут текущих пользователей более ранних версий. Для них будут выпускаться обновления, поддержка продолжится в полном объеме. Сборки PostgreSQL и PostgreSQL для 1С под ОС Windows будут выходить в штатном режиме.

И ещё: обновлены и сертифицированные версии Postgres Pro.

И ещё важная информация: План разработок Postgres Professional недавно обновлён.

JSON, BSON и даже PJSON


Using PostgreSQL JSON functions to navigate reviews of restaurants in India

Франческо Тизьё (Francesco Tisiot из финской Aiven) считает, что демонстрационные данные с ресторанными обзорами прекрасно подойдут для иллюстрирования работы функций с JSON. Эти данные лежат на гитхабе Kaggle, предлагающей, вообще-то удобное окружение для Python (Jupyter Notebooks). Франческо показывает, как работать с классическими JSON-операторами ->, ->>@>. Откровений в статье нет, но можно освежить воспоминания и навыки, воспользовавшись столь позитивным демо-материалом.

How FerretDB stores BSON in JSONB

Зато вот эта статья — скорее экзотика. В прошлом выпуске мы писали о FerretDB. В этой статье очень любопытные подробности. Чи Фуджи (Chi Fujii) пишет о способе, каким FerretDB решают проблему совместимости монговского BSON и постгресового JSONB (PostgreSQL — движок FerretDB, понимающей протокол MongoDB). BSON существенно отличается от JSONB. Например, BSON содержит длину документа и в нём важен порядок полей. Поэтому BSON сначала сериализуется в некий PJSON, изобретённый FerretDB, а тот уже дальше сохраняется с использованием типа JSONB. Для сохранения порядка полей используется массив. Основатель компании, Алексей Полажченко (Alexey Palazhchenko) в комментариях поясняет, что есть трудности с дублированием имён полей и с построением индексов, но идеи есть, работа идёт.

PG-Пятилетка


Postgres v15 — a billion transactions later

Только что мы рассказывали, как Каарел Моппел (Kaarel Moppel, Cybertec) решил количественно сравнить производительность PostgreSQL версий 10 и 15 на небольшом наборе запросов, используя pgbench.

Тогда 15-й победил по сумме, хотя и не слишком убедительно: 5 years in PostgreSQL major versions performance — anything surprising?

Ответ отрицательный. Теперь Каарел запустил pgbench, чтобы посмотреть, как будет вести себя десятка и пятнашка, выполняя миллиард транзакций. Тест серьёзный: на его облачных инстансах эти транзакции крутились около 10 дней.

Ошеломляющих результатов опять нет. Обе версии работают неплохо. Наиболее серьёзные отличия проявились в поглощении дискового пространства: PG 15 определённо лучше для диска. Поэтому, если хранилище медленное, если приходится сильно вылезать из памяти, то надо апгрейдить 10 на 15 как можно скорей. Эти достижения связаны с дедупликацией и восходящим удалением (bottom-up deletion) в индексах.

Логическая репликация: параллельное чтение Капилы и Лузанова


Очередной раз обращаем внимание на обзоры коммитфестов Павла Лузанова. Если кто не знает, то напомним: это обзоры-статьи, там не просто перечисляются и кратко описываются вошедшие патчи, а приводятся демонстрационные примеры — куски кода, которые можно исполнить у себя.

В статье Амита Капилы (Amit Kapila) Logical Replication Improvements in PostgreSQL-15 новшества собраны вместе. Амит известный человек в сообществе, а к разработке логической репликации сам приложил руку.

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

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

Не уверен, что Амит читал обзоры, но в любом случае можно говорить о взаимодействии — пусть в восприятии читателя. Оба, например, пишут о фильтрации строк, приводят примеры комманд, но Амит даёт ещё и важный комментарий: это может помочь при разбрасывании данных по разным узлам кластера для увеличения производительности. Да ещё и перечисляет 7 особенностей такой фильтрации, даёт ссылку на доку (вот версия .ru) и на статью своего коллеги из Fujitsu Питера Смита, (Peter Smith) целиком посвященную этой фиче.

А вот о Функциях для просмотра содержимого каталогов логической репликации можно узнать из Часть 3 или Коммитфест 2021-11, а в статью Амита они не попали.

При этом есть и случаи, когда краткий (по определению) обзор даёт больше деталей/примеров. Вот: в PostgreSQL 15: Часть 4 или Коммитфест 2022-01 о Логической репликации без суперпользователя. Здесь и понятней, и есть примеры, которые можно воспроизвести, чтобы лучше понять и запомнить.

Ещё один интересный случай уж сравните сами: Разрешение конфликтов.

Ну и сводная табличка-список:
Павел пояснил, почему в его обзоре этого пункта нет (даю в сокращении): в 14-й версии добавили полезную возможность передавать изменения на подписчик не по окончании транзакции, а прямо по ходу её выполнения. Это полезно для логической репликации транзакций изменяющих большие объемы данных. Но не всё было гладко. В 15-й просто исправили кривизну реализации 14-й.

PostgreSQL 16: Часть 2 или Коммитфест 2022-09

Самое интересное по PG 16 из первого, июльского, коммитфеста можно прочитать в предыдущем — июльском обзоре, а в этом обзоре — о сентябрьском коммитфесте:
Я бы обратил внимание на двунаправленную логическую репликацию: Павел разбирает её подробно. Это действительно событие. Можно сказать, что это уже встроенный в PostgreSQL «мультимастер» — теперь можно не только читать с разных нод, но и писать на разные ноды кластера. Насколько при этом можно расслабиться, не опасаясь неконсистентности? Эффективность, надёжность и работоспособность такого решения ещё предстоит, конечно, исследовать.

Английская версия есть на сайте Postgres Professional: PostgreSQL 16: part 2 or CommitFest 2022-09.

Нечёткий поиск


Как мы писали в Postgresso #6 (43), в статье Fuzzy Name Matching in Postgres Пол Рэмзи, сгенерив 50 тыс. имён при помощи Fake Name Generator, исследует и рассказывает о soundex и расстояниях Левенштейна. А вот о таких вещах как триграммы и расширение pg_trgm почему-то не говорит.

Зато в статье Ищем имена с опечатками в PostgreSQL автор, Денис Смирнов aka darthunix (о себе он говорит: пишу распределенный SQL для Тарантула на Rust) выбрал именно это расширение:

fuzzystrmatch умеет считать расстояние Левенштейна между словами и три фонетических алгоритма: Soundex, Metaphone и Double Metaphone. Подводными камнями является, во-первых, то, что функция Левенштейна в данном модуле не позволяет создать индекс для произвольного поискового запроса. Во-вторых, все фонетические алгоритмы реализованы для латиницы.
В связи с этим, решение задачи я начал искать там, где светлее, а именно с модуля
pg_trgm.

Для поиска опечаток он установил (создал) pg_trgm, табличку с 300 000 имён, GIN-индекс и начал искать себя: «смерно дени анато». Но результат 133 мс. на ноутбуке с ssd из Шэньчжэня его не удовлетворил, поэтому он обратился к комбинации нечёткого поиска с полнотектовым. И воспользовался индексом rum. Напоминаем, что он теперь есть в Postgres Pro Standard. Денис же его собирал из исходников.

Далее в дело пошёл алгоритм Metaphone из статьи Фонетические алгоритмы, который он реализовал на PL/Python (plpython3u). После ещё некоторых комбинаций с индексами и алгоритмами запрос с «Смернов дини онатольев» отрабатывал на порядок быстрей.

PGroonga 2.4.1

PGroonga — расширение для быстрого текстового поиска. Его сила в том, что FTS будет работать эффективно для всех (утверждают создатели) языков (pg_trgm не поддерживает, например, иероглифы).

Вообще-то Groonga это поисковый движок и СУБД с колоночным хранением данных. Groonga развитый проект — там есть ещё Rroonga, Mroonga и Nroonga. Создатели публикуют бенчмарки Groonga vs. textsearch и pg_trgm, показывающие быструю работу расширения.

В новой версии появилась поддержка PostgreSQL 15 (а 10 больше не поддерживается);
появился оператор &@~ для работы с типом jsonb.

Ещё статьи


Reducing replication lag with IO concurrency in Postgres 15

Томас Манро (Thomas Munro, Citus Data) предлагает установить PostgreSQL 15, запустить pgbench со scale=1000 и 32 клиентами, запустить поточную репликацию, установить maintenance_io_concurrency=0, отключить full_page_writes и
посмотреть, как будет расти отставание реплики. После этого сказать
ALTER SYSTEM SET maintenance_io_concurrency = 10;
SELECT pg_reload_conf();

и наблюдать отставание. Ситуация улучшилась, но прекрасной её не назовёшь. А вот при maintenance_io_concurrency = 32 другое дело: отставание полностью рассосалось.

maintenance_io_concurrency = 0 соответствует архитектуре PostgreSQL 14. Интересные вещи. Но дальше ещё интересней: Томас рассказывает о пагубном историческом наследии UNIX по части асинхронного ввода-вывода: полстолетия назад разработчики UNIX сделали выбор в пользу того, чтобы скрыть асинхронную природу ввода-вывода. Другие ОС того времени — Multics и VAX/VMS — могли при некоторых настройках предоставлять асинхронность пользовательским программам. Но UNIX всегда усыплял процессы до тех пор, пока данные будут готовы к использованию, и обойти эту модель работы с данными было нельзя.

Поэтому, хоть в PostgreSQL 15 и добавили параллелизма в ввод-вывод, истинно асинхронным он не стал. Вот в будущем — может быть. Одним словом, очень советую эту статью интересующимся жанром «под капотом».

6 устаревших команд Linux и инструменты, которые пришли им на замену

Андрей Колесников, руководитель инженерного отдела в компании МойОфис публикует перевод статьи Хосе Висенте Нуньеса (Jose Vicente Nunez) 6 deprecated Linux commands and the tools you should be using instead, что на сайте RedHat.

В этой шестёрке:
  • fgrep и egrep vs. grep с флагами;
  • nslookup vs. dig (а в комментариях советуют drill);
  • ifconfig, netstat и route vs. ip.

Такая тема, задевающая за живое новичков и ветеранов, собрала, конечно, немало (больше сорока) комментариев.

Emacs(client) as editor in psql

Лука Феррари (Luca Ferrari) написал статью для элиты/секты, работающей в Emacs. EmacsClient даёт много интересных возможностей при работе с psql. Например, можно использовать внешние редакторы (скажем, перловый pgFormatter) для форматирования запросов, не выходя из редактора Emacs.

Listening to Postgres: How LISTEN and NOTIFY Syntax Promote High Availability at the Application Layer

Шон Томас (Shaun Thomas, EDB, тот самый Томас, у которого PG-пятнецы (PG Phrydais), но не PSQL-пятнецы (PSQL Phrydeys) — см. об этом в прошлом номере) пишет на редкую тему: мало того, что о приложениях, ещё и об использовании механизма NOTIFY / LISTEN. К тому же разбираются и FOR UPDATE SKIP LOCKED. Но всё это интересные, мощные, но непростые средства, связанные с тонкостями работы транзакций, поэтому мы советуем внимательно почитать документацию, если тема сильно заинтересовала.

NULL, изменчивый и (почти) непредсказуемый


NULL-значения в PostgreSQL: правила и исключения

Алексей Борщев преобразовал свой доклад на PgConf.Russia 2022 в статью. На мой вкус это был один из самых увлекательных докладов (может, самый). Пересказывать не буду: читайте, смотрите, если можете (слайды доклада доступны всем, а видео тем, у кого есть личный кабинет на конференции).

А 4 ноября Чарли Батиста (Charly Batista, Percona) опубликовал статью: PostgreSQL: Are All NULLs the Same?

В Перконе наверняка читают русскоязычные новости по PostgreSQL, поэтому, можно сказать, что замечательная статья Алексея порождает «круги на воде». Но, может, это совпадение, и тема просто «носится в воздухе». В любом случае это хорошо: тема интересная.


Рисунок Лоренца Альбе

Avoiding “OR” For Better Query Performance

Лоренц Альбе (Laurenz Albe, Cybertec) в этой статье рассказывает, как переписать запрос с OR, используя UNION, в результате чего он будет выглядеть так себе, зато исполняться существенно быстрее.

Примеры не примитивные: при индексировании, например, используется класс операторов text_pattern_ops. Из статьи мы узнаём (если не знали), что бывают хорошие OR, а бывают и плохие: если, например, OR в выражении CASE в SELECT, то это хороший OR; а вот если OR в фильтрах WHERE, как чаще всего и бывает, то быть беде. Скорее всего план не будет оптимальным.

Можно переписать с заменой OR на IN, это поможет. А вот неожиданное решение: индексирование при помощи триграмного индекса из расширения pg_trgm. Он тоже помогает. Далее оказывается, что кроме плохого OR есть и жуткий OR (условие сразу для 2 таблиц). И вот в этом случае Лоренц переписывает запрос с OR как 2 запроса, объединенных UNION.

Статья произвела впечатление, и читатели попросили продолжения на тему: а почему же оптимизатор сам не перепишет запрос?

Rewrite OR To Union In PostgreSQL Queries

В этой статье Лоренц показывает, что всё непросто, что есть немало случаев, когда оптимизатор не имеет права переписывать с UNION, так как результат запроса будет разный. UNION ALL тоже не всегда спасает. Проблема в том, что слишком сложно автоматически распознать случаи, когда переписывать можно, а когда — нет.

Можно ещё почитать и вот такую коротенькую заметку: PostgreSQL do different equality predicates make a difference?

В ней Фриц Хоогланд (Frits Hoogland) задаёт себе вопрос: как замена '=' на 'IN' в запросе влияет на план. И приходит к выводу: да никак.

Explain That Parameterized Statement In PostgreSQL!

Опять Лоренц Альбе. На этот раз он даже придумал анекдот, вот он в моём вольном переводе:
— Чем отличается детектив от консультанта по базам данных?
— Детективу нужна улика, чтобы придумать план, а консультанту нужен план, чтобы найти улику.



Кроме анекдота в статье немало полезного: как план параметризованных запросов можно использовать дальше для запросов с конкретными аргументами, как управлять выбором планов, играя параметром plan_cache_mode со значениями force_custom_plan / force_generic_plan, как получить общий (generic) план при помощи PREPARE, как работает EXPLAIN с ANALIZE и без, как использовать псевдотип unknown, чтобы добиться от планировщика нужных действий. Мало того: всё это он объединил в полезном расширении, которое назвал generic_plan.

Железо


Сравнение процессора Байкал-S и HiSilicon Kunpeng 920

Автор, о котором известно только то, что он разработчик IoT, провёл 9 разных тестов и опубликовал табличку с результатами. Заодно, конечно, рассказал об архитектуре Байкала-S и Kunpeng 920. Хотя оба и на ARM64, отличаются они изрядно — и ядрами (Cortex-A75 / Cortex-A72), и общей топологией. ОС тоже отличались, но по-другому было нельзя: работать приходилось по ssh. Выводов и обобщений нет, только тесты. Есть интересные вопросы/ответы в комментариях, которых почти полсотни.

Образование


Опубликована локализованная документация к PostgreSQL 15.0 — перевод сделан в компании Postgres Professional.

Как (и зачем) контрибьютить в Postgres


Такой вопрос то и дело возникает в постгресовых рассылках. Александр Алексеев писал об этом в блоге Timescale How (and why) to become a PostgreSQL contributor.

Недавно некто Матеус Алкантара спросил об этом в рассылке и получил сразу ответ от Александра. Он посоветовал для начала ревьюить чужой код: во-первых, это благое дело — именно такой деятельности чаще всего недостаёт сообществу; во-вторых, вы поймёте, какими разработками занято сообщество, какие патчи имеют шанс пройти; поймёте детали реализации частей системы.

И дал ссылки — на свою статью тоже, но для начала на книжку Database System Concepts 7th Edition Абраама Зильбершаца (Abraham Silberschatz, Yale University), Хенри Корта (Henry F. Korth, Lehigh University) и С. Сударшана (S. Sudarshan, Indian Institute of Technology, Bombay). И ещё дал ссылку на видео курсов CMU (то есть Университета Карнеги Меллона). Мы, кстати, тоже рекомендовали эту книгу и эти курсы в прошлом выпуске, где был очень большой образовательный блок.

Но Александр оказался не единственным советчиком. Роберт Трит (Robert Treat, который ведёт общеобразовательный zillablog, работая в Instaclustr, подхватил предложение Александра: да, разбор чужого патча отличный способ завести знакомства в среде разработчиков — может очень пригодиться, когда коллеги-ревьюеры начнут разносить твой собственный патч.

Он посоветовал статью Learning PostgreSQL Internals в блоге Пола Рэмзи (Paul Ramsey — тоже есть в упомянутом образовательном блоке — но отчего же не повторить полезные для самообразования ссылки). А в заключение сказал: почему бы не глянуть в постгрес-вики, если уже не.

Ну а мы опять напоминаем о книжке нашего коллеги Егора рогова: PostgreSQL изнутри. Английская версия здесь.

Обновленный курс DBA2 по версии PostgreSQL 13 выложен на сайт Postgres Professional.

В новом курсе:
  • учтены нововведения версий PostgreSQL 11, 12 и 13;
  • вместо сборки из исходных кодов используется установка из пакета;
  • материал многих тем переработан, чтобы демонстрации чередовались с теорией.

Неожиданный ликбез


— от нескольких известных людей. Грозит перерасти в приятный флешмоб по поводу этакого словарика терминов/жаргона. Вот, например: Postgres Databases and Schemas. На самом деле тема там чуть шире: Крейг Керстинс (Craig Kerstiens, Crunchy Data) говорит о кластерах, базах данных и схемах. Оговаривая, что в 90% случаев приложение работает с кластером (в постгресовом смысле, а не в серверном) с одной базой данных и одной схемой, он в двух словах рассказывает, как развести ресурсы по пользователям и по задачам.

Postgres Insider Terminology

Пол Рэмзи (Paul Ramsey, Crunchy Data) пишет о Tuple/Record/Row, Array (ещё и о странных массивах), Relation (ещё и о relkind и pg_class), Target List and Restrictions, Schema (ещё и о pg_namespace), Pages (и о Blocks) и TOAST. Пишет предельно просто, но ухитряется сказать о некоторых вовсе не очевидных вещах.


Конференции и митапы


Infostart Event 2022 Saint Petersburg

Конференция 1С прошла в СПБ 6-8 октября 2022-го. Как можно увидеть в расписании, было 5 потоков. Можно обратить внимание на такие, скажем, доклады:
Тонкости эксплуатации PostgreSQL (Антон Дорошкевич, ИнфоСофт) — у него было аж 3 доклада + круглый стол. Был среди них такой:
Детский сад, штаны на лямках;
Новое в 14-й и 15-й версиях Postgres (Иван Панченко, Postgres Professional);
Мигрируем с MS SQL на Postgres (Владимир Крючков, Осмокод) и его же
Опыт оптимизации 1С на PostgreSQL, в котором есть про тормозные списки, оптимизацию RLS, декомпозицию и про много другое;
Внутренняя жизнь ваших запросов PostgreSQL. Как и зачем «подглядывать» в подробности (Екатерина Соколова, Postgres Professional) — как? при помощи представления pg_stat_progress_*, модуля pg_query_state и других средств.

PGConf.Cибирь 2022

Прошла в Новосибирске 24-го октября. Видеозаписи докладов скоро появятся на сайте мероприятия (слайды уже есть), а пока можно ознакомиться с итогами в публикации Интерфакса.

А за день до этого была PGStart.Новосибирск — встреча для студентов Новосибирска с основателями компании Postgres Professional Иваном Панченко и Фёдором Сигаевым.

Это турне Postgres Professional закончилось на PGMeetup.Барнаул. Там выступили Пётр Петров, Иван Панченко и Павел Лузанов.

Highload++ 2022

Начнётся совсем скоро — 24-25 ноября в Крокус Экспо. Докладов и потоков очень много. Вот примеры докладов в этом году:
Репликация между SQL- и NoSQL-базами данных: туда и обратноАлександр Горякин (Tarantool, VK) расскажет о там, как безболезненно переносить данные между реляционными и NoSQL базами.
Балансировка нагрузки в мульти-эксабайтном стораджеВадим Зотеев (Яндекс) — в мультиэкзабайтном, Карл!
Аномальные случаи высокой нагрузки в PostgreSQL, и как мы с ними справилисьМихаил Жилин
(Postgres Professional) обещает рассказать о том, почему index scan / index only scan могут тормозить при адекватном плане запроса; что за странные ожидания LWLock'а SubtransControlLock или ClientRead видны в pg_stat_activity и о многом другом.

Prague PostgreSQL Developer Day 2023

Должна пройти в Праге 31-го января — 1-го февраля 2023 в Праге. Присылать заявки сюда.

PGConf India, 2023

Организатор — India PostgreSQL User Group. Должна пройти в Бангалоре 22-24 февраля 2023.

SCaLe 20x

Это не постгресовая конференция, а опенсорсная, самая большая в Северной Америке. Пройдёт 9-12 марта 2023 в Пасадене (около Лос Анджелеса). регистрация.

На этом пока всё.

Комментарии (1)


  1. Kwisatz
    23.11.2022 06:51

    Поясните пожалуйста смысл " совместимость с расширением TimescaleDB; "

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