Postgresso #6 (43)22.07.2022 07:01

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

PostgreSQL 15 Beta 2

В Beta 2 есть все возможности общедоступной (generally available) версии PostgreSQL, но некоторые детали реализации могут поменяться за время Beta-фазы. Отличия от Beta 1 перечислены на странице релиза — это 14 непринципиальных изменений и исправлений.

Об отличиях PostgreSQL 15 (не Beta 2, а «готовой» версии) от PostgreSQL 14 можно почитать здесь, а загрузить Beta 2 можно отсюда. До версии GA остаётся ещё выпустить версию RC (release candidate).
Citus 11 for Postgres goes fully open source, with query from any node

Объявлено, что отныне Citus (11-я версия) открыт полностью. В 11.0 можно запускать распределённые запросы с любого узла кластера, схема и метаданные автоматически синхронизуются. Пишущие транзакции всегда реплицируются по 2PC (с 2-фазным коммитом). Архитектура недемократичная: есть узел-координатор и несколько рабочих узлов. Здесь есть некоторая информация о новой версии и об отличиях 11.0 от предыдущих реализаций, но эта информация относится ещё к Beta-версии. Из текста нынешнего анонса следует, что синхронизируются и последовательности, и функции, и схемы, и другие объекты базы данных, которые не синхронизировались в 11.0 beta.

Introducing PostgreSQL interface for Spanner: availability at scale with the interface you know

Интерфейс был анонсирован ещё в октябре прошлого года, мы об этом относительно подробно уже писали. Позиционируется как spanner-with-granular-instance-sizing — гранулированный Spanner, платишь за те его размеры, какие тебе нужны. Теперь можно и попробовать.

Конференции


PGConf.Russia 2022

Выложены видео докладов на прошедшей 20–21-го июня конференции. Некоторые доклады мы представляли в прошлом Постгрессо, теперь их (и другие) можно увидеть.

А сейчас список лучших докладов по итогам общего голосования.

Серебряный доклад известного популяризатора астрономии, Владимира Сурдина эффектно завершал конференцию:
На поверхности Венеры адская жара — там плавятся олово и свинец. Роботы-первопроходцы погибали, исследуя Венеру. Какие тут могут быть разговоры о жизни? Оказывается — могут! Неожиданно оказалось, что объём пространства, пригодного для жизни, на Венере больше, чем на Земле. Возможно, и признаки жизни уже обнаружены. Станет ли когда-нибудь Венера второй Землёй?

Дальше в нашем обзоре встретится ещё немало ссылок на доклады PGConf.Russia предыдущих лет.

Postgres.FM и Postgres.TV, четверги и вторники


Slow queries and slow transactions

5-го июля Николай Самохвалов (Postgres.ai) и Майкл Кристофайдис (Michael Christofides, основатель pgMustard) представили свой аудио-проект Postgres FM (дополняющий Postgres TV Николая и Ильи Космодемьянского) и начинают они с обсуждения медленных запросов и транзакций: не только как ускорить их, а и о том, когда это вообще нужно, а когда можно и потерпеть.

Через неделю — 14-го июля — Николай и Майкл обсуждали Managed services vs. DIY. На странице этого обсуждения огромное число ссылок на обсуждаемые технологии и компании.

Майкл рассказал о проекте и своей любви к подкастам и на сайте pgMustard. Он говорит, что дуэт с Николаем ему интересен в том числе потому, что Николай ближе к проблемам DBA, к высоконагруженным системам, соответствующим сервисам и инструментам, а сам Майкл ближе к разработчикам и нуждам стартапов.

Встречи Postgres.TV происходят по четвергам. Видео-версия выкладывается на Postgres.TV, а аудио-версия — параллельно на Postgres.FM. Но Postgres.TV этим не исчерпывается: например, последнее видео с участием Яна Каррманаса (Нидерланды, EDB) — PostgresTV guests: Jan Karremans. Postgres and Kubernetes, не имеет аудио-версии на Postgres.FM.

До этого Николай в соавторстве с Ильёй Космодемьянским запомнились русскоязычными Постгрес-вторниками (#ruPostgres). Последние видео из этой серии было выложено 5 месяцев назад — с Егором Роговым: Внутри PostgreSQL. Англоязычные четверги начинались параллельно.

Образование


Egor Rogov. PostgreSQL 14 Internals

Книга Егора Рогова (здесь о её русском оригинале) сейчас переводится на английский. Пока переведена только 1-я часть. Она доступна. Переводом занимается Людмила Мантрова. Остальные части появятся в скором времени.

PostgreSQL 13. Оптимизация запросов

Курс QPT-13 выложен на сайте postgrespro.ru. Курс двухдневный. Учебные материалы выложены, ссылки здесь.

DBA1 в Элисте

Выложены видео обновлённого до PostgreSQL 13 курса DBA1, прочитанного в Элисте сотрудниками отдела образования Postgres Professional в этом году. Курс длился 3 дня, сейчас выложены 2 из них, 3-й день выложат скоро.

Миграции и «чужие» данные


Перенос данных с Oracle на PostgreSQL: основные этапы, несовпадающие типы и форматы данных

Статья Петра Петрова (Postgres professional), открывает серию из 4 статей о миграции. Очень рекомендуем, Пётр пишет основательно, используя свой немалый опыт. В первой статье есть, в том числе, большой набор ссылок на доклады по миграции, прозвучавшие на PGConf.Russia разных лет. Вот полдюжины из этого списка:

Handle empty strings when migrating from Oracle to PostgreSQL

Это одна из важных тем при обсуждении миграции Oracle → Postgres: пустые строки и NULL. Авторы — Шашиканта Паттаньяк и Дипак Махто (Sashikanta Pattanayak и Deepak Mahto) — не только констатируют это несоответствие, но и напоминают о существовании некоторых не слишком часто встречающихся возможностей PostgreSQL. Например, о выражении NULLIF (его авторы почему-то называет функцией — бог им судья, а тема важная).

Показано, как имитировать оракловую NVL постгресовой COALESCE, как вместо оракловой DECODE использовать постгресовые конструкции с CASE. Но можно использовать и расширение Orafce, предназначенный именно для миграции, где есть свои NVL и Decode.

Статья не то чтобы глубока и основательна, зато есть полезные ссылки, например:
Migration Playbook. Oracle Database 19c To Amazon Aurora with PostgreSQL Compatibility (12.4) — PDF 400-страничного пособия по миграции в тех случаях, когда автоматическая миграция с AWS Schema Conversion Tool не поможет.

Кстати, на тему NULL был интереснейший доклад Алексей Борщева (Postgres Professional) на PGConf.Russia 2022: NULLs в Postgres. Много интересных примеров логичного, но контринтуитивного поведения этих самых NULL. В Postgres NULL не равен NULL, например. Но (в PostgreSQL 15) можно настроить так, что будет равен.

Есть статья по поводу изменений (улучшений) в работе с NULL в PostgreSQL 15:
Postgres 15 improves UNIQUE and NULL

Райан Лэмберт (Ryan Lambert, владелец и CEO RustProof Labs) скачал и исследует PostgreSQL 15 Beta 2. Его заинтересовало изменение UNIQUE null treatment option, давно обсуждавшееся почти год:
разрешить уникальным ограничениям и индексам считать NULL одним и тем же значением. Раньше NULL всегда индексировались как разные (различимые) величины, теперь можно это изменить, создавая UNIQUE-ограничения и уникальные индексы с предложением NULLS NOT DISTINCT. Райан проверяет, показывает как это работает в новейшей версии PostgreSQL.

Handling Trailing Zeros With Numeric Datatype in PostgreSQL

Автор, Джагадиш Пануганти (Jagadeesh Panuganti, MigOps) рассматривает тип NUMBER (Oracle) vs. NUMERIC (PostgreSQL), их поведение и предлагает способы обойти проблемы с ненужными нулями в хвосте числа. Проблема (не такая уж драматическая, кажется) решается по-разному в PostgreSQL до 12-й версии включительно и начиная с 13-й, где появляется функция trim_scale().

Oracle vs PostgreSQL — Transaction control statements

Коротенькая, но любопытная статья. Ахил Банаппагари (Akhil Reddy Banappagari, MigOps) обещает, что дочитав статью, вы обретёте ясность по поводу разного поведения PL/SQL и PL/pgSQL. Начинает с BEGIN (и END). Проверяет, что делают в Oracle и PostgreSQL COMMIT и ROLLBACK, смотрят на (совсем разную) работу блоков исключений.

Changing Tires at 100mph: A Guide to Zero Downtime Migrations

Статья Кирана Рао (Kiran Rao, независимый канадский разработчик в Сан-Франциско) начинается со спойлера: как шаг за шагом проводить миграцию? А вот так.

  1. Создайте пустую таблицу.
  2. Пишите и в старую, и в новую.
  3. По кусочкам копируйте данные из старой в новую.
  4. Проверяйте консистентность.
  5. Переключите читающие запросы на новую.
  6. Не пишите в старую.
  7. Сотрите старую.


Но читать дальше имеет смысл: там рассказывается о таких вещах, как расширение uuid-ossp, интересная ссылка на безопасные/опасные массовые операции, много кусочков кода.

How to Join MySQL and Postgres in a Live Materialized View

Бобби Илиев (Bobby Iliev, Materialize) пишет: когда ты работаешь над проектом, в котором полно микросервисов, ты, скорее всего, используешь много разных баз данных [в смысле СУБД]. Обычно, когда происходит JOIN двух разных СУБД, нужно создавать новый микросервис, в котором это будет происходить, но это усложняет систему.

Для упрощения и создана — с участием Бобби — база Materialize. Это потоковая база, написанная на Rust, которая работает с результатами SQL-запроса (с материализованным представлением) в памяти, при этом отслеживая меняющиеся данные. Эта статья — урок, где используется Materialize для JOIN данных из MySQL и Postgres в материализованном представлении реального времени (live materialized view). В этом случае можно делать запрос непосредственно к этому представлению и, таким образом, получать результаты сразу из двух СУБД в реальном времени, используя стандартный SQL.

OnlineMigrations

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

Авторы (команда fatkodima) сравнивает online_migrations с strong_migrations, разработанной GitLab: online_migrations это суперсет strong_migrations, с многими фичами и некоторыми API. Главные отличия в том, что strong_migrations даёт рекомендации в текстовом виде, а вы уж там дальше как-нибудь сами, в то время, как online_migrations помогает генерить код. Помогает в:

  • переименовании таблиц/столбцов;
  • изменении типа столбцов (в том числе в замене PRIMARY/FOREIGN KEYS с integer на bigint);
  • добавлении столбцов с дефолтными значениями;
  • дозаполнении недостающих данных (backfilling data);
  • добавлении различных типов ограничений.


В online_migrations есть среда для запуска миграции данных для очень больших таблиц, используя background migrations тех же авторов.

node-pg-migrate 6.2.2

Средство миграции для PostgreSQL (но можно использовать и для совместимых с ним, например CockroachDB). Построено на Node.js. Начинал проект Тэо Эфраим (Theo Ephraim), теперь поддерживается чешской компанией Salsita Software. В версии 6.2.2 есть обратно-несовместимые изменения. История проекта довольно долгая, поэтому информацию о совместимости многочисленных версий лучше отслеживать.

PGSpider

PGSpider (на самом деле pgspider_ext) это расширение для создания кластерных движков, работающих с распределёнными данными. pgspider_ext соединяется с некоторым количеством источников данных через Foreign Data Wrapper (FDW). Ориентирован на работу с Big Data и IoT.

PGSpider работает с декларативным секционированием (партицированием). Даже если у таблицы с исходными данными нет ключа секционирования, PGSpider создаст его сам. Умеет объединять данные из разных источников с похожими схемами в одну виртуальную таблицу.

Через FDW PGSpider умеет соединяться с:

  • PostgreSQL (postgres_fdw);
  • MySQL (mysql_fdw);
  • DynamoDB (dynamodb_fdw);
  • GridDB (griddb_fdw);
  • JDBC (jdbc_fdw);
  • ODBC (odbc_fdw);
  • SQLite (sqlite_fdw);
  • MongoDB (mongo_fdw);
  • InfluxDB (influxdb_fdw);
  • Parquet S3 (parquet_s3_fdw).


PGSpider разрабатывают и поддерживают в Toshiba Corporation Corporate Software Engineering & Technology Center.

Классика


Database from the 1980s needs time travel says author

ИТ-журналист с 32-летним стажем, Линдзи Кларк (Lindsay Clark), не то, чтобы излагает историю идей, овладевавшими классиком — Майклом Стоунбрейкером (Michael Stonebraker), не то, чтобы анализирует, обобщает. Он делает этакий коллаж из цитат классика. Они касаются не только путешествий во времени, но и вертикального хранения и многого другого.

Там же можно найти ссылки на любопытные материалы того же автора. Он, например, в We have bigger targets than beating Oracle, say open source DB pioneers обильно цитирует и Петра Зайцева — одного из первых разработчиков MySQL (и основателя Percona, конечно).

Производительность


Column Order In PostgreSQL Does Matter

Ханс-Юрген Шёниг (Hans-Jürgen Schönig, Сybertec) затрагивает интересную тему внутри более широкой: как влияют на производительность вещи, которые на первый взгляд не должны бы влиять? Как работает оптимизатор в непростых для него условиях? Ханс-Юрген создаёт табличку шириной в 1500 столбцов и убеждается, что count по 1000-ному столбцу происходит в 20 раз медленней, чем по 1-му, и в 10, чем по 100-му.

Напомним о статье нашего коллеги Павла Толмачёва Как работает оптимизатор PostgreSQL при большом количестве таблиц в запросе. Он исследует JOIN большого числа таблиц, и убеждается: то, как записан запрос, имеет ещё какое значение, если число таблиц велико. В отличие от Ханса-Юргена, Павел подробно останавливается на работе оптимизитора при разных формах записи запроса и разных параметрах, влияющих на его работу.

Postgres and the curious case of the cursory cursors

Фритс Хоогланд (Frits Hoogland, Yugabyte) разбирает такой случай: один и тот же SQL-запрос (никаких переменных в нём) исполняется то 133 мс, то 750 мс. Но только исполняется он по-разному: один раз как обычных SQL, а другой раз внутри PL/pgSQL-процедуры, использующей
refcursor. Планы, соответственно, разные.

Working With Large PostgreSQL Databases

В блоге Percona Робер Бернье (Robert Bernier) говорит о технических ограничениях размеров баз PostgreSQL, который гибкий, но не резиновый (наблюдал и я, как реальные клиенты в реальной жизни упираются в 32ТБ для таблицы). Робер формулирует свои критерии (не)большой базы:

  • небольшие базы обычно администрируются одним человеком;
  • ими можно управлять вручную;
  • они требуют минимальной настройки;
  • небольшие базы обычно более толерантны к неэффективности, чем большие;
  • большими базами управляют при помощи автоматизированных утилит;
  • их надо постоянно мониторить, должны быть циклы настройки;
  • большие базы требуют быстрой реакции на возникающие и текущие проблемы;
  • большие базы чувствительны к накоплению технических проблем (к техническим долгам);


У этого автора немало интересных статей. Вот о postgreSQL 15: Introducing PostgreSQL 15: Working with DISTINCT

SELECT DISTINCT — базовая и важнейшая функциональность. Но принципиально изменилось не поведение (о UNIQUE NULLS NOT DISTINCT см. выше), а исполнение: теперь это распараллеливается. Робер показывает, как меняется план и как растёт производительность при подключении параллельных процессов.

Есть у Робера и статья на тему борьбы с wraparound (кстати, ну когда же, наконец, в PostgreSQL появятся 64-разрядные счётчики транзакций?): Overcoming VACUUM WRAPAROUND. В статье есть, например, список 5 причин wraparound, и не все они вполне очевидны (а некоторые, может, даже сомнительны). Есть и список тех действий, которые надо срочно предпринять, когда wraparound приблизился на опасное расстояние.

Debugging Deadlocks In PostgreSQL

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

  • логирование на стороне приложения;
  • логирование на стороне базы, префиксы;
  • увеличение deadlock_timeout


Увеличив таймаут, можно увидеть client_addr и client_port через представление pg_stat_activity. Опять же, копающим глубже рекомендуем Блокировки в PostgreSQL: 3. Блокировки других объектов.

ZFS


И возвращаясь к Роберу: он говорит о логической репликации, о катастрофоустойчивости, о том, что стоит подумать о Copy-On-Write, Btrfs, ZFS. Последнее, как ясно из 95 комментариев к статье Битвы на территории ZFS, тема очень актуальная. И, если вы сами задумывались или уже используете ZFS, вам чтение этой статьи Павла Толмачёва точно не помешает.

ZFS 101—Understanding ZFS storage and performance

Джим Солтер (Jim Salter, Ars Technica) пишет: ZFS объединяет традиционную систему управления томами и слоями файловой системы и транзакционный механизм copy-on-write (CoW), следовательно, структурно эта система сильно отличается от традиционных файловых систем и от RAID-массивов.

Этот механизм он поясняет очень наглядными динамическими картинками). Начинает с «главных кирпичиков»:
zpools, vdevs и devices.
Дальше он поясняет что такое слой «кирпичей» повыше:
datasets, blocks и sectors.

PostgreSQL + ZFS. Best Practices and Standard Procedures

В презентации Шона Читтендена (Sean Chittenden) 110 слайдов — презентация не самый мой любимый образовательный жанр, но просмотр отнюдь не бесполезен.

Ещё статьи


Вспомним здесь Робера и ещё по одному поводу: если кого-то заинтересовала тема, поднятая Лоренцом АльбеPipeline Mode For Better PostgreSQL Performance On Slow Networks, которой мы коснулись в предыдущем выпуске, то можно заглянуть и в статью Робера How PostgreSQL Pipeline Mode Works.

Fuzzy Name Matching in Postgres

Пол Рэмзи (Paul Ramsey, Crunchy Data) генерит 50 тыс. имён при помощи полезной штуки — Fake Name Generator, загружает при помощи COPY в PostgreSQL и начинает искать имена, начиная с использования LIKE. Ускоряет поиск индексом. Потом рассказывает о расстоянии Левенштейна и расширении fuzzystrmatch, работающим с ним и с алгоритмом soundex. Рассказывает, как soundex и расстояния Левенштейна использовать вместе, и что можно ускорить индексами. О таких вещах как триграммы и расширение pg_trgm в этой статье почему-то не говорится.

Postgres Indexes, Selectivity, and Statistics

Ещё одна статья Пола Рэмзи, скорее в жанре Indexes, Selectivity, and Statistics for Dummies: в ней красивые наглядные картинки и по абзацу на понятия, о расширенной статистике вообще не рассказано, только ссылка. В этом скромном качестве статья может оказаться кому-то полезна. Там есть и несколько фраз о более продвинутой пространственной статистике в PostGIS.

И, конечно, если вас интересуют эти вопросы на более глубоком уровне, почитайте Егора Рогова Запросы в PostgreSQL: 2. Статистика.

Using Postgres Schemas

Небольшая статья Аарона Эллиса (Aaron O. Ellis) с примерами использования при работе со схемами полного пути, CREATE SCHEMA, CREATE USER, CREATE ROLE, GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA… TO… и других возможностей PostgreSQL.

UUIDs to prevent Enumeration Attacks

Речь идёт об атаках, использующих URL в попытке угадать сгенерённые автоинкрементом id. В этом смысле MySQL и PostgreSQL надо защищать генераторами случайных id, и работать это в MySQL и PostgreSQL будет по-разному.

2 + 2×4 = 16?

Статья с таким странным названием появилась в блоге ads' corner Андреаса Шербаума (Andreas Scherbaum, Adjust, Берлин). Речь на самом деле о перегружаемых операторах.»+» может быть совсем и не сложением, а чем угодно, хотя бы и умножением, если кто-то захочет кастомизировать плюс под свои нужды.

Заметим, что компания Adjust GMBH, ориентированная на разработку мобильных приложений, плотно занимается PostgreSQL. Председатель совета директоров — Андрей Казаков, сооснователь Acquired.io. В компании работают знакомые PostgreSQL-разработчики.

Из песочницы


Как продавать шкуры и ловить троллей в Telegram с помощью Kafka, Kubernetes, PostgreSQL и Redis

Да, эта статья из Песочницы хабра. Автор занимается развлекательным самообразованием, предлагая читателям последовать за ним. Здесь и об играх, и телеграм-бот собирается, и Kafka, и другое, перечисленное в названии статьи.

По части PostgreSQL: очень помог на этапе создания квестового движка. Я активно использую индексы с условием и функциональные констрейнты. Также, очень помогла и сократила размер кода такая конструкция в запросах как INSERT… ON CONFLICT DO.

Предположим (детали обещаны, но пока неизвестны), что MERGE версии PostgreSQL 15 пригодился бы вместо этого INSERT… ON CONFLICT DO.

Облака


VK Cloud Solutions: Patch Management

Новый сервис Patch Management позволяет управлять обновлениями версий БД. Сейчас он доступен для PostgreSQL. Когда на платформе выходит обновление БД, появляется уведомление, и для повышения версии достаточно выбрать инстанс или кластер, нажать «Повысить» и выбрать номер версии. Система автоматически обновит БД за 1–10 минут. Так можно перейти только на следующую мажорную версию — перескочить с PostgreSQL 11 на PostgreSQL 13 нельзя. Помимо ручного обновления, можно отправить API-запрос, чтобы отследить доступные версии и запросить повышение. Предлагается войти и попробовать.

Релизы


Barman 3.0.0

Есть серьёзные изменения, нарушающие совместимость с предыдущими версиями:

  • PostgreSQL 9.6 больше не поддерживается;
  • режим Rsync-бэкапов по умолчанию теперь concurrent, а не exclusive (который уже вообще не используется)
  • в метаданных бэкапа, которые хранятся в файле backup.info, появилось новое поле, из-за чего более ранние версии Barman не смогут работать с бэкапами, полученными при помощи 3.0.0. Кроме того, пользователям pg-backup-api надо будет проапгрейдить его до версии 0.2.0.


Появилась поддержка PostgreSQL 15. Есть и другие существенные изменения.

check_pgactivity 2.6

check_pgactivity — это PostgreSQL-плагин для агента мониторинга Nagios. Документация здесь. Новая версия совместима с PostgreSQL 14. В основном исправления багов.

Magic 14.0.5

Создатели предлагают нам обернуть свою базу (будь то PostgreSQL, MySQL или MS SQL) в свой код так, что редактировать код можно хоть с телефона. Ещё предлагают быстро создать своё облако — Aista Magic Cloud. Названо в честь разработчика — Aista.

Последняя версия — 14.0.5. Загрузить можно отсюда.

PGLoader 3.6.6

Утилита Димитри Фонтейна (Dimitri Fontaine, Citus, Microsoft). Загружает данные в PostgreSQL командой COPY. В отличие от обычного копирования через FDW, здесь это происходит в транзакционном стиле: создаётся и хранится отдельный файл отвергнутых данных, а попытки копирования продолжаются.

PGLoader умеет трансформировать данные, например, MySQL datestamp 0000–00–00 и 0000–00–00 00:00:00 он преобразует в NULL для PostgreSQL, так как в календаре не бывает года номер 0.

Последняя версия — 3.6.6, в ней только исправления.

gcopydb

Ещё одно детище Фонтейна: pgcopydb — автоматизирует запуск pg_dump | pg_restore между двумя действующими Postgres-серверами. И делает это эффективно. Почему — читайте на гитхабе проекта.

© Habrahabr.ru