Postgresso #7 (44)

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

---

Релизы и коммитфесты Postgres

PostgreSQL 15 Beta 3

Третья бета закрывает неожиданно обнаруженную дыру в безопасности. Ситуация объяснена в пресс-релизе и вот в этой статье Дэна Гарсии (Dan Garcia, EDB), но на наш взгляд яснее всего суть изложил Том Лейн (Tom Lane) в рассылке pgsql-committers (перевод с некоторыми вольностями):

Раньше, если скрипт расширения отрабатывал CREATE OR REPLACE, и такой объект уже существовал, но принадлежал расширению, то оно переписывало объект как часть расширения. При этом права на объект не переписывались, а наследовались. Это могло случаться и неумышленно, что тоже плохо, но злостный пользователь мог заранее создать объект с нужным именем, ожидая, что кто-то установит расширение, и тогда у атакующего будут права на переписанный объект, который можно будет модифицировать для атаки. Поэтому следует запретить операции CREATE OR REPLACE с объектами, не принадлежащими расширению. По этой же причине и CREATE IF NOT EXISTS не должна работать, когда уже есть объект с таким именем, не принадлежащий расширению.

Также исправлено ещё 40 багов. Обновлены PostgreSQL 14.5, 13.8, 12.12, 11.17, 10.22. Ветка 10.x скоро будет выведена из оборота. Общедоступная версия (general availability) намечена на конец 3-го квартала. Вся функциональность 15-й версии по сравнению с предыдущими перечислена здесь.


Postgres Pro Enterprise 14.4.1: Что нового — статистика, безопасность, анализ работы VACUUM

Сам релиз вышел два месяца назад. По многочисленным, как говорится, просьбам инженеры Postgres Professional обобщили новшества этой версии. Их много — 21 пункт, на каждый в этой статье приходится по 1-2 абзаца описания.

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

Еще не вышла официально 15-я версия, но уже закончился первый коммитфест 16-й. И мы можем посмотреть на самые интересные изменения — пишет автор обзора Павел Лузанов. В обзор попали 14 изменений:

• psql: \pset xheader_width.
• vacuumdb --schema и --exclude-schema.
• Новые возможности утилиты createuser.
• Сообщение журнала о контрольной точке дополнили номерами LSN самой точки и начала восстановления.
• pg_prepared_statements.result_types.
• auto_explain: параметр log_parameter_max_length.
• Необязательные псевдонимы для подзапросов во фразе FROM.
• REINDEX: синтаксис и не только.
• CREATE STATISTICS: необязательно указывать имя статистики.
• CREATE TABLE: атрибут STORAGE.
• У созданного при инициализации кластера пользователя нельзя отобрать полномочия суперпользователя.
• Триггеры на TRUNCATE для внешних таблиц.
• pg_read_file/pg_read_binary_file: новая вариация указания параметров.
• Собственные менеджеры ресурсов WAL

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

Пока даже быстрый Депеш (Hubert Lubaczewski) ещё не открыл у себя рубрику в ожидании PG 16 (пока там только в ожидании PG 15), а Павел уже не единственный, кто пишет о 16-й версии. Лукас Фиттл (Lukas Fittl, основатель компании pganalyze) в ожидании:

5mins of Postgres E27: Waiting for Postgres 16 — Make subquery alias optional in FROM clause

Лукас решил рассказать подробней, случай любопытный. Новшество допилили для лучшей совместимости. Но с чем? Не со стандартом SQL — там-то как раз алиас (псевдоним) требуется. На самом деле это нужно для облегчения миграции с Oracle — оракловый код без псевдонима не выдаст ошибки, но при трансляции его в PostgreSQL ошибка появится. В 2017-м из-за этого уже собирались сделать соответствующий патч, но что-то тогда не сложилось. Теперь — сложилось. Напоминаем, что о необязательных псевдонимах для подзапросов во фразе FROM есть немного и в только что процитированном обзоре Павла Лузанова.

Кстати: 5mins of Postgres E27 — этот префикс расшифровывается так: Эпизод #27 большой серии 5-минутных видео от pganalyze.

Интерес к версиям 16 и 15 понятен. Но пока что и новшества PostgreSQL 14 закономерно привлекают немалое внимание. Про Postgres Pro Enterprise говорили, дальше речь пойдёт о «ванильном» PostgreSQL 14:

Begin Atomic: A Better Way To Create Functions In PostgreSQL 14

Джонатан Кац (Jonathan Katz, Crunchy Data) пишет об этом потому, что из разговоров с клиентами понял: эта фича значимая.

PostgreSQL-функции и хранимые процедуры теперь можно создавать, используя прописанную в SQL-стандарте конструкцию BEGIN ATOMIC в определении функции. В этом случае тело функции можно парсить при создании. И можно отслеживать зависимости.

Об этом говорится на сайте PostgreSQL.org. Отдельного пункта в документации нет (хотя BEGIN ATOMIC там рассматривается вполне адекватно), но на эту заметку ведёт ссылка из Матрицы. Между прочим, Feature Matrix пока поднимается лишь до 15-й версии PostgreSQL. А жаль: очень наглядная табличка. И очень хорошо по ней видно, в каких направлениях сообщество движется особенно интенсивно, а какие развиваются по остаточному принципу.

Возвращаясь к новому синтаксису определения функций в PG 14: теперь тело функции или процедуры не обязательно вводится как текстовая строка. Из этого и следуют новые возможности и для производительности запросов, и для написания более надёжного кода.

На эту же тему пишет Лоренц Альбе (Laurenz Albe, Cybertec) в статье Better SQL Functions In PostgreSQL V14.

Он к этой теме относится не менее серьёзно и даже предлагает не просто пользоваться новым синтаксисом в будущем, а переписать уже написанный код, используя эти возможности отслеживания зависимостей. В статье есть интересные рассуждения о search_path как дыре в безопасности, о проблемах с интерактивными клиентами (psql) при создании функций.

Хаос полезный и вредный

How To Corrupt Your PostgreSQL Database

А в этой статье Лоренц предлагает заняться противоположным: не написанием надёжного кода, а навыками разрушения целостности базы данных. Своей. Например, для проверки инструментов, которые должны эти нарушения согласованности обнаружить.

Первый способ — установить fsync = off. Второй — Лоренц манипулирует с base backup, pgbench и файлом backup_label. В третьем случае он запускает два сервера на одних и тех же файлах данных, используя pg_upgrade --link. В четвертых, модифицирует системные каталоги.

Chaos testing of a Postgres cluster managed by the Zalando Postgres Operator

Теперь собственно хаос: в данном случае Хаос это Chaos Mesh, который помогает симулировать экстренные ситуации. В статье Николай Сивко (Nikolay Sivko, Zalando) рассказывает, как он тестировал с помощью этого инструмента Kubernetes-кластер под управлением Zalando Postgres Operator из одной ведущей ноды и трёх реплик. Николай симулировал падение реплики, переключение, отставание реплики, нарушение соединения с pgbouncer.

ChaosDB.

В отличие от Chaos Mash, ChaosDB не существует. Точнее, её онтологический статус иной: это так называемая ChaosDB.

Компания Wiz (Wiz Research Team) объявила, что они смогли получить доступ ко всем эккаунтам Microsoft Azure Cosmos DB (несколько тысяч пользователей, в том числе и из Fortune 500). И смогли бы выполнить на любых из этих баз данных операции чтения, записи и удаления. Создатели этого хоаса и назвали результат своей деятельности Chaos DB.

Пьерлуиджи Паганини (Pierluigi Paganini), состоящий в ENISA Ad-Hoc Working Group on Cyber Threat Landscapes в заметке How we broke the cloud with two lines of code: the full story of ChaosDB пишет, что по обнародованной Wiz информации, превращение Космоса в Хаос осуществлялось не через какой-нибудь зловредный SQL-запрос, а через популярные у питонистов Jupyter Notebooks, которые включены в среду Azure Cosmos DB.

На A Cloud Guru Ларс Клинт (Lars Klint) делится и коммерческой информацией: Microsoft, закрыв за двое суток дыру, наградила добрых взломщиков из Wiz денежной премией в $40,000.

PostgreSQL JDBC versions 42.4.1/42.2.26 Security Update

Интересная дыра была обнаружена в JDBC, из-за чего были выпущены эти версии. При генерации SQL названия столбцов с данными и с ключами копировались как есть в SQL. Хитроумный злоумышленник мог придумать такую таблицу, чьи названия столбцов (включая ";") могли парситься и исполняться как команды. Вот пример:

CREATE TABLE refresh_row_example (
  id     int PRIMARY KEY,
  "1 FROM refresh_row_example; SELECT pg_sleep(10); SELECT * " int
);

Миграция

Перенос данных из Oracle в PostgreSQL: секционирование, временные таблицы и инструменты

Цикл Петра Петрова о миграции с Oracle на Postgres продолжается: вышла 2-я статья из 4-х запланированных. Начало: Основные этапы, несовпадающие типы и форматы данных. Название второй статьи, конечно, не исчерпывает тему. В ней говорится и о IoT в Oracle, о команде CLUSTER, о INCLUDE-индексах, о расширении pg_pathman и о многом другом.

eXperDB-DB2PG

eXperDB-DB2PG — утилита миграции данных из гетерогенных СУБД в eXperDB (PostgreSQL). После конвертирования объектов (таблиц, представлений, последовательностей, ограничений) из разнородных СУБД, она генерирует DDL в грамматике PostgreSQL. Основана на JAVA, поэтому может работать на Linux и Windows, инсталлировать утилиту не надо. Умеет:

• Экспортировать все данные или выборочно, используя предложение WHERE;
• Экспортировать пространственные данные Oracle Spatial в PostGIS;
• Экспортировать объекты Oracle CLOB, BLOB в постгресовый тип BYTEA;
• Работает быстрее, чем PostgreSQL COPY;
• Удаляет FK и INDEX перед импортом данных и перестраивает их после его окончания;
• Экспорт данных, используя запрос с SELECT;
• Выборочная экстракция по таблице исключений;
• Поддержка Oracle, Oracle Spatial, SQL Server, Sybase, DB2, Mysql, Sybase, Altibase, Cubrid.

70 apps in 2 years: How Renault tackled database migration

Renault мигрировала с Oracle (и не только) на Cloud SQL for PostgreSQL. Вот небольшое видео: How does Renault Group use Cloud SQL? Маркетинговый, не технический ролик, но кое-какая информация есть.

Образование

Много новостей от отдела образования Postgres Professional.

DBA3 и QPT по версии PostgreSQL 13

С 8 по 12 августа на базе IT-кластера Цифровая Калмыкия в Элисте мои коллеги провели курсы, обновленные до 13-й версии PostgreSQL: DBA3. Резервное копирование и репликация PostgreSQL и QPT. Оптимизация запросов. В апреле там же был прочитан DBA1 по PG 13. DBA1 выложен целиком (в прошлом выпуске мы говорили о первых двух днях из трёх). Августовские тоже скоро будут доступны. Там же провели профессиональную сертификацию желающих. Во время чтения курсов велась видеозапись, которая по готовности будет доступна на сайте Postgres Professional.

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

Нас нередко спрашивают: а есть ли курсы Postgres Professional на английском. Да, есть (слайды и видео), но только базовый курс Intro. 2-Day Introduction to PostgreSQL 11). Видео DEV1 — Basic Server-Side Application Development for PostgreSQL 12 появится чуть позже, курс уже переведён. Русские аналоги этих курсов здесь. Они были записаны в Израиле в 2019-м.

Новость об английском переводе книжки Егора Рогова попала в основные новостные ленты, посвященные PostgreSQL. О ней (и о статьях Егора) можно регулярно читать на Planet.PostgreSQL.org. Он попал в первую полудюжину авторов по частоте публикаций. А вот скриншот с сайта Postgres Weekly:




Egor Rogov. PostgreSQL 14 Internals

В предыдущем выпуске мы уже объявили, что начался и продолжается перевод на английский книги Егора Рогова PostgreSQL изнутри. Была выложена в PDF Часть I. — Isolation and MVCC. На днях выложена ещё одна часть — II. Buffer Cache and WAL. Остались III. Locks IV. Query Execution. V. Index Types. До всех можно будет добраться по этой ссылке. Переводчик — Людмила Мантрова.

20 PostgreSQL Interview Questions with Answers
Вопросы, которые (как утверждают авторы сайта) чаще всего задают на интервью при приёме на работу со знанием Postgres. Не стоит, видимо, относиться к этим спискам слишком серьёзно, но при желании можно полистать и другие многочисленные списки вопросов к интервью. Например, не просто вопросы, а вопросы телефонные: Top 50 SQL and Database Phone Interview Questions Answers.

Индустрия

Gartner: Microsoft #1 in Database Revenue; AWS Passes Oracle; Google Cloud Gains

Gartner утверждает, что Oracle уступил своё 2-е место по доходам AWS. На 1-м по-прежнему MS. Google Cloud поднялся до #4, оттеснив SAP. IBM провалился на #5:

1. Microsoft — 24.0%
2. AWS — 23.9%
3. Oracle — 20.6%
4. Google — 6.5%
5. IBM — 5.6%

Google overtakes Microsoft in open-source contributors, research finds

Google поборол MS по части вклада в открытый код — это не просто субъективное мнение автора — Ли МакГорана (Leigh Mc Gowran): он приводит данные специализированного источника, который называется Open Source Contributor Index (OCSI).

The past, present, and future of Postgres, and the Postgres Playground

Это видео беседы с Крейгом Керстинсом (Craig Kerstiens). На странице имеются таймкоды, так что можно смотреть не подряд. Он рассказывает в том числе о Hasura (GraphQL и REST API) и, конечно, о Postgres Playground.

What PostgreSQL Contributor Heikki Linnakangas Has to Say About the State of PostgreSQL 2022 — ещё одна беседа по поводу 25-летнего именинника.

Хейкки Линнекангас (Heikki Linnakangas), которого мы теперь знаем ещё и как сооснователя компании Neon, обсуждает результаты опроса Timescale State Of PostgreSQL 2022, заодно рассказывая и о себе.

Маркетинговые службы Timescale извлекли из опроса (в самом деле интересного) целую серию заметок:

The 2022 State of PostgreSQL Survey Is Now Open!
First Findings
How to Contribute to PostgreSQL and the Community (со ссылкой на статью Александра Алексеева)
13 Tools That Aren't psql

FM и TV

Postgres.FM

Проект Николая Самохвалова (postgres.ai) и Майкла Кристофайдиса (Michael Christofides, основатель pgMustard) развивается. Список тем уже не выглядит сиротливо — 7 записей. А это только начало!

• Monitoring checklist, 19-го августа;
• Vacuum, 12-го августа;
• NULLs: the good, the bad, the ugly, and the unknown, 5-го августа;
• BUFFERS by default, 29-го июля;
• BRIN indexes, 21-го июля;
• Managed services vs. DIY, 14-го июля;
• Slow queries and slow transactions, 5-го июля.

У серии Postgres Open Talks Николая Самохвалова с Ильёй Космодемьянским (CEO Data Egret) другой формат, и началась она раньше: первая встреча в эфире состоялась ещё в апреле. Есть 8 видео:

• `psql` is awesome!, Летиция Авро (Lætitia Avrot), 18-го августа;
• 1) SPQR (Postgres sharding); 2) Data corruption monitoring & troubleshooting, Андрей Бородин, 11-го августа;
• Database testing in CI/CD, Николай Самохвалов, 4 августа;
• When it all GOes right, Павло Голуб (Pavlo Golub), 28-го июля;
• The Future of PostgreSQL, Александр Коротков, 16-го июня;
• Do you vacuum PostgreSQL everyday?, Ханну Кросинг (Hannu Krosing), 5-го мая;
• Past, Present, and Future of Logical Replication, Амит Капила (Amit Kapila), 28-го апреля;
• Postgres in a Microservices World Брюс Момджан (Bruce Momjian), 21-го апреля.

Тяжёлая DB на лаптопе

HeavyDB (бывшая OmniSciDB (бывшая OmniSci Core (бывшая MapD Core)))

HeavyDB интересна, конечно, не только интенсивной деятельностью их спецов по неймингу. Это реляционная СУБД с открытым кодом, с вертикальным хранением. Она в своих распараллеленных вычислениях может использовать GPU (пока только Nvidia), но может и не использовать. Утверждается, что она может за миллисекунды обрабатывать запросы, затрагивающие миллиарды строк без индексирования, предварительной агрегации или семплирования. Может работать не только на X86, но и на архитектурах Power и ARM (пока в экспериментальном режиме). В том числе недурно справляется с задачами на макбуке:

1.1 Billion Taxi Rides using OmniSciDB and a MacBook Pro

Статья Марка Литвинчика (Mark Litwintschik) вышла в середине июля, за это время OmniSciDB стала HeavyDB. Статься полезна хотя бы тем, что можно прочитать, как клонировать с репозитория Тода Шнайдера (Todd W Schneider) миллиард с лишним метаданных маршрутов нью-йоркских такси за 9 лет (500 ГБ в разжатом CSV-виде), чтобы играться с ними, тестировать производительность или строить сложные аналитические запросы. Марк заливает эти данные в свой лаптоп (и даёт ссылку на Billion Taxi Rides in Redshift blog, где более детально о работе с этой таксистской базой, но там они заливаются в Redshift).

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

Облака

Состоялся релиз Odyssey 1.3. В этом релизе:
Поддержка на уровне протокола подготовленных операторов (prepared statements) в режиме transaction pooling. Можно включить для базы pool_reserve_prepared_statement. Тогда для клиентских сессий с подготовленными запросами Odyssey будет, когда это необходимо, автоматически подготавливать эти операторы.

Standby lag polling: можно сконфигурировать watchdog-запрос к базе, так, что Odyssey будет предотвращать чтение устаревших данных. Подробней об этом рассказал Андрей Бородин на конференции Citus Con 2022. Вот страница релиза на гитхабе.

VACUUM

Сначала о том, что как бы лучше, чем вакуум.

pg_squeeze – Shrinks Tables Better Than Vacuum

pg_squeeze (последняя версия — 1.4.1) это расширение с открытым кодом, которое поддерживает Cybertec. Оно автоматически борется с распуханием таблиц (table bloat), не слишком интенсивно блокируя (в отличие от VACUUM FULL и CLUSTER) сами таблицы. Процессы работают в фоне.

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

В прошлом выпуске мы давали ссылку на статью Overcoming VACUUM WRAPAROUND. В этом сошлёмся на:

Debugging Postgres autovacuum problems: 13 tips

Самай Шарма (Samay Sharma, Citus) не только даёт чёртову дюжину советов, но и предлагает наглядную табличку проблема->параметр->рекомендации по настройке:



Вместо/кроме чтения можно посмотреть доклад автора на Citus Con 2022: Optimizing autovacuum: PostgreSQL’s vacuum cleaner.
Goodbye Windows XP

Обсуждение «не пора ли бросить Windows XP» началось ещё в 2016-м. И вот буквально на днях Эндрю Данстан (Andrew Dunstan) объявил, что отключает больного от ИВЛ. На XP собиралась только версия PostgreSQL 10, а она тоже лишится поддержки в скором времени. Так что возиться с ошибками, которые обнаруживаются при сборке, смысла нет. Goodbye Windows XP!

UPDATE: Эндрю Данстан, 20.08.22, 18:38. Стоило мне это запостить, как чудодейственным образом всё заработало снова. Так что, похоже, доковыляем как-нибудь до декабря, ребята.