Postgresso №1 за 2022 (38)08.02.2022 11:01

Экстренное сообщение: PGConf.Russia переностся!

Уважаемые коллеги! Вынуждены сообщить, что площадка, на которой мы хотели провести PGConf.Russia 2022, попала под приказ о непроведении массовых мероприятий. В связи с этим конференция переносится на другую площадку и иные даты — конец марта или начало апреля. Мы активно работаем над тем, чтобы PGConf.Russia 2022 состоялся. Более подробная информация появится чуть позже.

Но это хорошая новость для нерасторопных: прием докладов продлевается до 20 февраля.

Принимаются доклады оффлайн длительностью 45 или 22 минуты или мастер-классы длительностью 90 минут на русском и английском языке. Возможно подключение ноутбука докладчиков для живых демонстраций (Live coding).

Регистрироваться здесь.

Основные темы:

PostgreSQL на переднем крае: высокие нагрузки, большие БД, отказоустойчивость;
новое в PostgreSQL и вокруг: развитие PostgreSQL и его экосистемы;
PostgreSQL в реальных системах: архитектура, миграция, эксплуатация;
использование PostgreSQL в платформе 1С;
PostgreSQL в геоинформационных системах (GIS).

Другие конференции

HighLoad++

Должна пройти в Крокус-Экспо 17–18 марта. Вот программа.

pgDay Paris 2022
Опубликована программа pgDay Paris 2022. Восемь докладов, все в один поток. Участвуют Брюс Момджан (Bruce Momjian), Магнус Хагандер (Magnus Hagander) и другие. Со студентов и безработных (со справкой) по 50 юро, с корпоративных посетителей по 150. Регистрация здесь.

PGConf.de 2022

Конференция состоится 13-го мая в Лейпциге в отеле в центре города, как и в 2019-м. Заявки на доклады подавать сюда.

Образование

Вот это событие так событие! PostgreSQL изнутри — в издательстве ДМК Пресс выходит книжка сотрудника отдела образования Postgres Professional Егора Рогова. Сейчас доступна pdf-версия на сайте, а на PGConf.Russia её можно будет увидеть уже в бумажном варианте. А вот страница заказа на сайте издательства.

Postgres: первое знакомство

Вышла новая версия «Малютки». PDF есть на сайте Postgres Professional. В ней:

отражены новинки PostgreSQL версии 14;
добавлена информация по свежевышедшей книге «PostgreSQL изнутри»;
картинки, скриншоты и фотографии, ранее черно-белые, заменены на цветные;
обновлен дизайн обложки, книга оформлена в стиле и цветах конференции.

Книжку будут раздавать на PGConf.Russia 2022

WB TechTalks: «PostgreSQL: между 14.2 и 15devel»

15 февраля в 19:00 в офисе Wildberries пройдет встреча разработчиков PostgreSQL в формате TechTalks. Рассказывать будет Павел Лузанов — руководитель отдела образования в Postgres Professional. Павел в том числе пишет обзоры коммитфестов, ссылки на которые вы регулярно видите в Постгрессо. Он расскажет, что интересного появилось в 14-й версии, ради чего стоит затевать переход на неё, а слушатели будут задавать вопросы. И о 15-й версии тоже: её релизный цикл движется к завершению и многое можно будет обсудить.

Регистрация здесь. Анонсы мероприятий WB Devs в телеграме: t.me/wbdevs

Итоги 2021

Их начали подводить, не дожидаясь Нового Года, но остановиться не могут. И это хорошо: есть весьма содержательные. Например, у Энди Павло (Andy Pavlo, OtterTune):

Databases in 2021: A Year in Review

Разработчики стали выбирать Postgres для создания новых приложений, — говорит Энди Павло. Упоминая совместимость, он раскладывает её на такие составляющие:

Подключились Большие Игроки, — продолжает Энди, — в октябре Google анонсировал совместимость с PostgreSQL в Cloud Spanner. Тогда же Amazon анонсировал Babelfish, конвертирующую запросы SQL Server в запросы к Aurora PostgreSQL.

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

Что до самого Энди Павло, то он поддерживает
Базу данных баз данных.

А не так давно он привлёк внимание нашей общественности, когда писал об автономных (самонастраивающихся) базах:

What is a Self-Driving Database Management System?

И это неудивительно: сам он сооснователь компании «Выдронастройка» — OtterTune, где все ключевые посты занимают выдры — см. их страницу НАША КОМАНДА:

A short summary of the Year 2021 in the PostgreSQL ecosystem

Обзор сделан авторами с MigOps, поэтому не без уклона в свои разработки. Что не умаляет актуальности. В этом обзоре есть, например, список расширений, появившихся и анонсированных в прошлом году:

pg_dbms_job: аналог шедулера Oracle DBMS_JOB (анонс MigOps).
orafce_mail: для миграции с Oracle, даёт некоторую совместимость с пакетами UTL_MAIL и DBMS_MAIL.
credcheck: для более строгих парольных политик (анонс MigOps)
pg_dbms_errlog: эмулирует стиль Oracle DBMS_ERRLOG (анонс MigOps)
jdbc_fdw: поддерживает запись в чужие таблицы, а не только чтение.
dynamodb_fdw: FDW для AWS, поддерживает чтение, запись и пуш-даун WHERE-предложений.
pg_validate_ext_upgrade: утилита для проверки валидности апгрейдов расширений.

Есть ещё любопытный список. Число найденных и закрытых дыр в версиях PostgreSQL:
PG 10 — 29
PG 11 — 25
PG 12 — 15
PG 13 — 11
PG 14 — 2
Но у 14 ещё всё впереди.

Postgres in 2021: An Observer’s Year In Review

Это вполне технологичный обзор Лукаса Фиттла (Lukas Fittl, pganalyze). Он начинает с 30-кратного ускорения запросов с конструкцией WHERE… IN.

Интересно, что он, например, обсуждает здесь дилемму 32 vs 64, и как отнесётся к переходу на 64-битные счётчики транзакций VACUUM (спойлер: нормально, исчезновение ему пока не грозит). В дискуссии по поводу патча, предложенного Максимом Орловым (Postgres Professional) Питер Гайген (Peter Geoghegan) высказался примерно так: я думаю, патч должен быть более амбициозным: не приспосабливать то, что есть, а вернуться к самим базовым принципам Postgres. Так что, похоже, попасть в PG 15 патчу не светит — считает Лукас, — может в 16-й.

Ещё он поднимает другие неожиданные темы: использование Rust в Postgres, например.

Who Contributed to PostgreSQL Development in 2020 and 2021?

Роберт Хаас (Robert Haas) регулярно развлекал постгресистов списками самых ярых контрибьторов. Вот и сейчас: 6 списков. Понятно, что Том Лэйн (Tom Lane) лидер всегда и везде. Но мы остановимся на вкладчиках в Postgres из наших краёв. Следите за соревнованием, делайте ставки. В списки вошли:
Никита Глухов, Анастасия Лубенникова, Александр Коротков, Дмитрий Долгов, Андрей Бородин.

FDW: сделать или скачать?

Creating a Postgres Foreign Data Wrapper

Сотрудники компании-разработчика баз данных Dolt (SQL-база с функциональностью, подражающей Git), давеча занимались созданием FDW для своей базы, а теперь решили поделиться опытом. И сделали это вполне основательно и последовательно: рассказывают как создать сначала болванку с заглушками, проверить, загружается ли она, потом наполняют её содержанием, подсказывают, как тестировать и обрабатывать ошибки.

Но если самим делать FDW нет времени и желания, то вот, например, ещё одна серия 1.1.0 FDW от Toshiba Software Engineering & Technology Center из 3 FDW — для DynamoDB, Parquet и JDBC. FDW этой серии умеют работать с PostgreSQL 13 и 14:

DynamoDB FDW 1.1.0

Теперь поддерживает SELECT, INSERT, UPDATE, DELETE; SELECT поддерживает выбор столбцов или объект дерева атрибутов (nested attribute object), используя операторы JSONB -> и ->>; предложения WHERE; пуш-даун функции SIZE в DynamoDB. Исходники на гитхабе.

Parquet S3 FDW 0.2.1

Новая версия Foreign Data Wrapper обеспечивает доступ на чтение к Parquet-файлу на локальной системе или на Amazon S3. Появились новые возможности. Можно, например, создать стороннюю таблицу в PostgreSQL для нескольких файлов, указав каталог, в котором они лежат. Подробности и инструкции по установке на гитхабе.

JDBC FDW 0.1.0

Реализована на C с библиотекой JDK. Теперь тоже поддерживает: SELECT, INSERT, UPDATE, DELETE; пуш-даун выражений WHERE и функций агрегирования. Исходники здесь.

Напоминаем, что в JDBC появились обновления, связанные с закрытием дыр безопасности (это не заплатки, а просто предупреждения):
PostgreSQL JDBC 42.3.2/42.2.25 Security updates

pgpool-II 4.3, иллюстрированное издание

4.3 появился в конце января. В нём немало нового. Пунктом #1 идёт: в Watchdog теперь есть механизм избрания новых членов для поддержания кворума/VIP, в случае удаления Watchdog-узлов. Пункт #10: импорт SQL-парсера. Всего 13 пунктов. Вместо перечисления сошлёмся на статьи создателя pgpool-II Тацуо Ишии (Tatsuo Ishii) в помощь изучающим pgpool-II 4.3:

Playing with PostgreSQL and Pgpool. What’s new in Pgpool-II 4.3? (часть I)

Первая часть появилась почти в новогоднюю ночь — 31-го. В ней демонстрируют, как изменился формат конфигурационного файла, какие поля показывают команды show pool_nodes и pcp_node_info, pcp_proc_info. Теперь можно посмотреть:
backend connection time,
client connection time,
client idle duration,
client disconnection time.

Но самое, может быть, интересное — новую концепцию избрания новых узлов для кворума (membership) — приберегли на 2-ю часть:

Playing with PostgreSQL and Pgpool. What’s new in Pgpool-II 4.3? (часть II)

и снабдили прекрасными картинками.

Но 2-я часть не последняя, будут ещё.

Графовые-реляционные

AgensGraph 2.5

AgensGraph — новое поколение многомодельной графической СУБД. Её разрабатчик — компания Bitnine. AgensGraph — форк PostgreSQL 10, поддерживает ANSI-SQL и openCypher, в ней можно объединить в один запрос SQL и Cypher. О новой версии есть информация здесь. О самой СУБД можно почитать на русском, например, здесь.

AGE (incubating) 0.6.0

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

Депеш ждёт 15-го

Серия Хуберта Любашевского (depesz) Waiting for PostgreSQL 15 неплохо пополнилась в этом году: 5 заметок.

Add HEADER support to COPY text format

Маленькое, но полезное изменение. До этого выводить строку заголовка умела команда COPY CSV, теперь можно и в текстовый файл. При загрузке и CSV, и текста первая строка в любом случае игнорируется.

Waiting for PostgreSQL 15 — Introduce log_destination=jsonlog

Раньше можно было выводить логи только в stderr или сохранять как csvlog. Теперь можно сохранять как JSON-файл.

Add assorted new regexp_xxx SQL functions

Новые 4 функции:
regexp_count(),
regexp_instr(),
regexp_like(),
regexp_substr(),
а в функции regexp_replace() появились новые аргументы. Эти функции повторяют аналогичные функции в Oracle и DB2, но есть некоторые отличия потому, что сама работа с регулярными выражениями в PostgreSQL немного отличается. Кстати, об этом советует помнить Селвакумар Арумигам (Selvakumar Arumugam) в статье How to use regular expression group quantifiers in PostgreSQL.

Allow publishing the tables of schema

Появилась новая опция FOR ALL TABLES IN SCHEMA в Create/Alter Publication. Теперь можно указывать и отдельные таблицы, и все таблицы в одной схеме или в нескольких.

Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner

Раньше не суперюзер по умолчанию мог создавать объекты в схеме public в базе, которой не владел. В 15-й по умолчанию не сможет.

Глубоко ли можно заглянуть под индикатор выполнения?

Query Progress Bar

Брайан по фамилии Likes Postgres предлагает трюки: мониторить происходящее с запросом из параллельной сессии, используя в наблюдаемой сессии последовательности + next_val и RETURNING, а для наблюдающей \watch. Это не всегда работает и не годится для промышленной эксплуатации. Но любопытно.

Ещё любопытней, может быть, что эту тему развил не на шутку Николай Самохвалов, написав свою статью, а заодно обнажив истинное имя Брайана Лайкса Постгреса — его, оказывается, зовут Брайан Дэвис. Итак, статья:

Progress bar for Postgres queries — let’s dive deeper

В ней Николай предлагает отделить пастухов от коров, коров от котлет, а тех уже от мух: запросы SELECT, и DDL-запросы требуют разного подхода. Более того, он даёт советы как быть и с мониторингом запросов в интенсивной промышленной эксплуатации — с многотерабайтными базами и миллиардами строк, обрабатываемых в запросе.

Для непромышленного случая предлагается использовать расширение pg_query_state. Это расширение разработано в Postgres Professional и входит в Postgres Pro Standard/Enterprise.

Но не только: подоспело и другое полезное расширение: pg_plan_inspector (Hironobu Suzuki), автора книжки The Internals of PostgreSQL.

А вот ещё одна статья Николая — тоже 2022 года:

EXPLAIN (ANALYZE) needs BUFFERS to improve the Postgres query optimization process

Есть много причин, из-за которых запросы в PostgreSQL сложно оптимизировать. Николай их перечисляет и неожиданно останавливается вот на какой: в команде EXPLAIN по умолчанию отключена опция BUFFERS (сам он считает, что по умолчанию она должна работать). Статья, конечно, не сводится к «включите BUFFERS» и даже к тому, что это и зачем. Логично выяснить, в каких случаях BUFFERS помогает понять то, что без них не ясно. И автор находит такие случаи. Сначала приводит пример с командой CLUSTER. Затем показывает, как может усложнить жизнь долгая транзакция. И даже рекомендует не особенно смотреть на времена исполнения при оптимизации, а сосредоточиться на буферах и пересчитывать их в байты. Ну, а ближе к концу как не упомянуть тонкие клоны?

И ещё крохотный списочек — когда анализ буферов особенно полезен:
при большом распухании (high level of bloat) таблиц/индексов,
очень широких таблицах (с существенными размерами TOAST),
при оценке HOT-обновлений vs. добавление индексов.

Возвращаясь к progres bar: между прочим, в pgBadger (анализаторе производительности Жиля Дароля, Gilles Darold) — наоборот: появилась возможность избавиться от прогрес-бара, оставляя другой вывод нетронутым: pgBadger 11.7

Для этого теперь есть опция --no-progressbar. Ещё там появилась опция --day-report (до этого были только месяцы).

Заклятые друзья: TOAST и JSONB

На хабр-хабе компании Онтико, то есть у Олега Бунина, организатора конференций HighLoad++, появились 3 статьи Олега Бартунова (гендир Postgres Professional), одну из которых (в соавторах разработчик Никита Глухов) мы упоминали в предновогоднем выпуске:
Проклятье TOAST и с каким маслом его ест JSONB

И вот теперь продолжение:

Борьба с TOAST или будущее JSONB в PostgreSQL

Сначала здесь сравнивается эффективность работы с таблицами в двух случаях: когда id вне JSON, и когда внутри. Небо и земля. Однозначный вывод: id внутри JSON делать нечего. Но можно поэкспериментировать дальше — помучить общедоступную Internet Movie Database. В результате были сделаны выводы:

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

Это и было учтено, хранилище TOAST стало более эффективным. И далее: графики неземной красоты и описание изменений в архитектуре (гвоздь программы — Shared TOAST).

Как сделать стрим в Postgres?

Тема в Postgres-сообществе не самая популярная. Но эффективный стрим сделать можно, а статья — ещё одно продолжение темы TOAST с маслом.

Чтобы эффективно стримить, сделали специальный формат данных для TOAST, который состоит из TOAST pointer и inline-буфера. Оператор конкатенации || не делает deTOAST, а просто добавляет данные в этот inline-буфер. Если размер inline данных превышает 2KB, то происходит TOAST, но только изменившихся данных, при этом неизменившиеся чанки используются для всех версий.

Видео на эти темы:

SQL/JSON в PostgreSQL: настоящее и будущее / Олег Бартунов (Postgres Professional)

Json or not Json. Плюсы и минусы использования Json в PostgreSQL / Олег Бартунов, Никита Глухов

SQL/JSON в PostgreSQL: настоящее и будущее / Олег Бартунов (Postgres Professional)

и есть ещё спринт-доклад Appendable bytea TOAST на PGConf.Online 2021

Ещё статьи

How to use regular expression group quantifiers in PostgreSQL

Название этой небольшой статьи лукавит: в ней нет развёрнутых рекомендаций по использованию регулярных выражений в PostgreSQL. Там описан всего один частный случай из практики автора, работающего с базами медицинских карт HL7. Но это случай любопытный. Он сначала использует регулярное выражение в grep, а затем пытается использовать его в запросе PodtgreSQL с regexp_matches и не получает желаемого результата. Пишет функцию на PL/Perl и использует его там — всё работает. А потом показывает, как подправить выражение, чтобы оно давало результат как в grep и Perl.

Automatic Partition Creation In PostgreSQL

В статье Лоренца Альбе (Laurenz Albe, Cybertec) рассматриваются два основных сценария, когда автоматическое создание секций (партиций) может понадобиться:

в случае, когда секции нарезаются по временнЫм критериям (time-triggered partitioning — например, закончился месяц, начался новый) и
в случае, когда строка не попадает ни в одну из уже созданных секций (on-demand partitioning).

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

Delete column statistics in PostgreSQL or YugabyteDB

Фрэнк Пашо (Franck Pachot) показывает, как убить статистику, чтобы она больше не влияла на выбор плана. Она видна в представлении pg_stats, а вычищать её нужно из системной таблицы pg_statistic. Возможно, цель его — напомнить, что в YugabyteDB просто взять и удалить статистику нельзя, надо сначала сказать set yb_non_ddl_txn_for_sys_tables_allowed=on;

Как заметил коллега Лузанов, в ванильном PostgreSQL взять и удалить тоже нельзя: только если
allow_system_table_mods включён. Он (говорит документация)
разрешает модификации структуры системных таблиц, а также некоторые другие потенциально опасные операции с системными таблицами. Изменить этот параметр могут только суперпользователи. Если данный параметр отключён, эти действия не разрешены даже суперпользователям.

Accelerating VACUUM in Postgres

Алексей Козлов из Data Egret показывает, какие настройки делают VACUUM (autovacuum) более агрессивным. Например, увеличить максимальное количество процессов автовакуума autovacuum_max_workers, которые по умолчанию работают втроём, до 10 (кстати, в недавней нашей статье у клиента их было аж 50, и все работали).

Но ещё дюжина параметров может повлиять на скорость вакуумирования. И в отличие от autovacuum_max_workers, их можно поменять без рестарта сервиса.

Ещё релизы

OraDump-to-PostgreSQL v5.1

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

улучшена миграция пространственных данных (SDO_GEOMETRY);
появилась поддержка SSL-соединений с PostgreSQL-сервером;
верифицированная поддержка PostgreSQL на Azure.

pgAdmin 4 v6.4

В новой версии добавлена поддержка импорта/экспорта описаний серверов и их групп. Они сбрасываются в JSON-файл и их можно развернуть на другой системе (или на той же). Раньше это можно было сделать только скриптом setup.py из командной строки, а теперь можно через Import/Export в Servers Wizard.

На этой странице бинарники для Windows и macOS, пакеты Python Wheel, Docker, RPM, DEB, тарболы исходников.

pgagroal 1.4.0

В новой версии этого высокопроизводительного пулера:

появилась поддержка для TLS-коммуникаций между pgagroal и PostgreSQL;
появилась настройка log_mode;
Tracker: события для сокетов;
Flush: опциональный аргумент базы данных

Подробности на гитхабе. Вот исходники, RPM для Fedora 34/35, RHEL 8.x, CentOS 8.x и SEL 12, OpenSUSE 15

pgsodium 2.0.0

Авторы сообщают, что в pgsodium 2.0.0 (расширение PostgreSQL, использующее криптографическую библиотеку libsodium) появилось много нового:

поддержка шифрования XChaCha20-SIV;
поддержка верификации идентичности SignCryption Sign & Encrypt;
поддержка на уровне ключа id HMACSHA 512/256, generichash, и shorthash;
поддержка низкоуровневого стриминга XChaCha20;
in-memory-ключ теперь защищён функцией sodium_malloc.

Подробности здесь.