Dbms

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

Главное событие

PostgreSQL 13 Beta 2

Релиз беты состоялся. Загрузить можно отсюда, информация для бета-тестеров здесь.

Напоминаем, что в начале апреля мы сделали обзор нового в версии 13: Много ли нового в чёртовой дюжине (спойлер: много). С тех пор по определению радикальных изменений произойти не могло. Некоторые изменения по сравнению с beta 1 всё же есть, о них написано в анонсе. Release notes 13-й версии здесь. А на этой странице расписание грядущих релизов PostgreSQL.

Релизы

pg_probackup 2.4.1

Новое в версии: прежде всего инкрементальное восстановление. Можно использовать повторно валидные неизмененные страницы, которые лежат в целевой директории. Это сильно увеличивает скорость, уменьшает нагрузку на сеть и потребление I/O. Соответственно появилась новая опция для команды restore: -I | --incremental-mode mode (mode может принимать значение checksum и lsn). Документация к этой утилите здесь.

barman 2.11

Пакет облачных утилит пополнился ещё двумя:
barman-cloud-restore и
barman-cloud-wal-restore
Теперь можно восстанавливать инстанс PostgreSQL, используя полный бэкап, созданный командами barman-cloud-wal-archive и barman-cloud-backup.

Этим новшества версии не ограничиваются, вот чейнджлог. Но об этих облачных новшествах есть небольшая статья-руководство в блоге 2ndQuadrant: начиная с установки пакета barman-cli-cloud, в которым теперь собраны все облачные утилиты и настройки, до планов улучшений в грядущей версии.

pgAdmin4 4.23

В новой версии не слишком радикальные изменения: прежде всего появилась поддержка Row Security Policies. Можно добавить название группы серверов в окне Schema Diff, а при сравнении схем теперь можно добавить опцию, позволяющая игнорировать пробелы.

Cypex 1.0

Расшифровывается Cybertec Prototyping Express и претендует на самое передовое средство разработки приложений с доступом к PostgreSQL. Скорость и простота разработки будет достигнута благодаря тому, например, что Cypex умеет предлагать (предсказывать) структуру приложения, исходя из модели данных.

PostgresDAC 3.8

Вышла новая версия набора компонентов прямого доступа к PostgreSQL от Microolap Technologies из Черноголовки. В ней появилась поддержка PostgreSQL 12 и RAD Studio 10.4 Sydney от Embarcadero Technologies. Появилась поддержка RAD Studio 10.4 Tokyo (Delphi and C++ Builder). Загрузить можно отсюда.

pgwatch2 v1.8.0

В новой версии pgwatch2, утилиты для мониторинга Postgres, много изменений. Среди них поддержка метрик PostgreSQL 13, Pgpool-II, TimescaleDB.

Появился мониторинг доступа к объектам (таблицы/представления, схемы, функции, БД) и к системным ролям прежде всего к ролям суперпользователей, но и к логин-ролям и прочим.
Теперь можно кешировать и разделять (share) на уровне инстанса и глобально метрики, общие для разных БД WAL, загрузку CPU и др. чтобы не получать их для каждой базы. Это экономит трафик.

Интервал кеширования настраивается. Новые метрики через базу работают с утилитами бэкапа/восстановления WAL-G и pgBackRest, метрики на PL/Python собирают информацию на уровне OS и отдают как SQL, чтобы можно было, например, визуализировать в Grafana.

Есть обширный чейнджлог.

pgsodium 1.2.0

Это расширение адаптирует функции криптографической библиотеки libsodium к SQL.
Со времён версии 1.1.1, о которой мы писали в прошлом выпуске, появились три уровня доступа, реализованных в соответствующих ролях, для доступа к API:

• pgsodium_keyiduser может пользоваться только функциями API по идентификатору ключа, а видеть или использовать сырые ключи (в типе bytea) не может;
• pgsodium_keyholder может видеть и использовать ключи и пары ключей, но не может генерировать их или получать из имеющихся (derive);
• pgsodium_keymaker может делать всё, что душе угодно.

Есть, конечно, и другие изменения. О них здесь.

pg_chameleon 2.0.14

В версии этой утилиты репликации из MySQL в PostgreSQL улучшили поддержку пространственных типов данных. Если в целевой БД установлен PostGIS, то пространственные типы point, geometry, linestring, polygon, multipoint, multilinestring, geometrycollection преобразуются в PostGIS-овский тип geometry и данные реплицируются с использованием стандартного для геоданных формата WKB (Well-Known Binary). Поскольку реализация WKB в MySQL нестандартна, pg_chameleon убирает первые 4 байта из декодированных бинарных данные перед тем, как залить их в PostgreSQL.

Если параметр keep_existing_schema установлен в yes, то теперь хамелеон пересоздает индексы и первичные ключи в процессе init_replica. Когда реплика приходит к согласованному состоянию, пересоздаются и внешние ключи.

Crunchy PostgreSQL Operator 4.4.0

Это утилита развертывания и управления PostgreSQL-кластеров в среде Kubernetes (не путать с разработкой DBaaS Zalando Postgres Operator for Kubernetes, о которую мы упоминали в Postgresso 22). Она работает в связке с Crunchy Container Suite.

В новой версии поддерживается PostGIS 3.0. В PostGIS-контейнерах доступен pgRouting. Поддерживается pgBackRest 2.27, pgBouncer 1.14. PostgreSQL Operator протестирован с Kubernetes 1.15 1.18, OpenShift 3.11+, OpenShift 4.4+, Google Kubernetes Engine (GKE) и VMware Enterprise PKS 1.3+.

Возможно создание кластера из репозитория pgBackRest. Флаги при создании см. в Major Features релиза. Доделывается понемногу RBAC (Role-Based Access Control). Внимание! Параметр сверки DYNAMIC_RBAC переименован в RECONCILE_RBAC. TLS Authentication (аутентификация на транспортном уровне). А ещё для PostgreSQL Operator теперь есть кубернетовский Helm Chart.

ORC Foreign Data Wrapper 1.0.0

Это FDW для файлов формата Apache ORC первый релиз, который поддерживает формат файлов ORC 0.12. В этой версии FDW пока не поддерживаются DML-операции. Файлы ORC доступны только для чтения. Можно импортировать схемы из любой директории, содержащей файлы с расширением .orc.

Статьи

Заметка Брюса Момджана объясняет: json и jsonb не просто типы данных, они сами могут содержать в себе много типов.

SELECT x, jsonb_typeof(x), pg_typeof(x) FROM test;

   x    | jsonb_typeof | pg_typeof--------+--------------+----------- "abc"  | string       | jsonb 5      | number       | jsonb true   | boolean      | jsonb null   | null         | jsonb (null) | (null)       | jsonb

Далее следует несколько интересных примеров с операторами #>> и другими.

AQO адаптивная оптимизация запросов в PostgreSQL

Новый автор корпоративного хабра Postgres Professional Павел Толмачёв пишет о модуле (расширении) aqo, которое поставляется с Postgres Pro Enterprise (может использоваться и с PostgreSQL). В обстоятельной статье есть и об установке aqo, и о принципах работы, и о том, почему планировщик может выбирать неоптимальный план, о том, к чему приводит использование зависимых (коррелированных) условий. Ну и главное: о способах влияния на работу планировщика, о том, когда aqo успешно подсказывает планировщику кардинальность грядущего результата. Дока о модуле здесь, а сам он на гитхабе компании.

PgBouncer on Kubernetes and how to achieve minimal latency. Experiments with connection poolers on Kubernetes for Postgres Operator

Дмитрий Долгов, Zalando, предлагает разобраться с поддержкой пулинга соединений, появившейся в Postgres Operator 1.5 (мы писали о новшествах в этой версии в Postgresso 21). Чтобы разобраться с масштабируемостью, Дмитрий измерял на pgbench производительность инстанса PostgreSQL:
c одним инстансом PgBouncer на одном физическом ядре;
с двумя инстансами PgBouncer в своих гипертредах, но на одном физическом ядре;
с двумя инстансами PgBouncer на двух ядрах, но с возможным влиянием процессов в других гипертредах.
Анализ результатов обнаруживает не вполне ожидаемые не вполне приятные эффекты. Вывод: следует аккуратно конфигурировать использование CPU.

PostgreSQL Antipatterns: анализируем блокировки SELF JOIN vs WINDOW

В блоге компании Тензор Кирилл Боровиков aka Kilor пишет о том, как анализировать собранные из логов и уже загруженные в базу записи о блокировках still waiting for ExclusiveLock и acquired ExclusiveLock. И о подводных и надводных камнях, о том, как сильно упростить себе задачу, используя оконные функции. Это продолжение DBA: в погоне за пролетающими блокировками, где как раз рассказывалось, как распарсить записи в логах и загрузить в базу.

Foreign data wrappers: PostgreSQL's secret weapon?

В статье на сайте Splitgraph демонстрируются возможности FDW на примере использования расширения multicorn, которое даёт возможность писать расширения FDW на Python и интегрировать PostgreSQL с инструментами Splitgraph.

Postgres Tips: How to convert 2 Billion Rows to Bigint with Citus

Эту статью от Citus (то есть Microsoft) жёстко раскритиковали на Постгрес-Вторнике-25 (см. ниже в разделе Вебинары и митапы), но, может, вам будет интересно составить собственное мнение.

Recreating YikYak with Postgres

Фактически в статье речь идёт не о сети YikYak, а о расширениях cube и earthdistance. Это немного неожиданно: думалось, что речь пойдёт о PostGIS. В статье используются: функции earth_box и ll_to_earth, оператор Contains?, то есть @>, индекс GiST. Ищут не n ближайших соседей, а сколько объектов в заданной окружности. Статья простенькая, но тема earthdistance встречается не часто.

Partitioning a large table without a long-running lock

Эндрю Данстан (Andrew Dunstan) пишет о том, как секционировать большую таблицу и не уснуть в процессе. Для демонстрации Эндрю создает табличку с 10 млн строк и разбивает её на 4 секции по диапазонам. При этом используется промежуточное представление как UNION строк в несекционированной и в секционированной таблицах. Перловый скрипт перетаскивает строки в новую таблицу порциями. Такую программу можно прервать в любой момент, что удобно.

Облака

Postgres Professional и Mail.ru Cloud Solutions запускают Postgres Pro Cloud

Эта совместная разработка, облачный сервис управляемой базы данных будет предоставляться по модели Database-as-a-Service. Установка, настройка и поддержка СУБД осуществляется на стороне провайдера. Mail.ru Cloud Solutions и Postgres Pro помогают с миграцией и консультируют по построению масштабируемой и отказоустойчивой структуры хранения данных, а также по настройке взаимодействия с On Premises-системами, процессами ETL и Streaming, построению Data Warehouse и Data Lake.

Managed Databases now supports PostgreSQL 12

DigitalOcean прибавил к своей коллекции БД PostgreSQL 12. Есть средства бесшовного автоматического апгрейда с мажорных версий PostgreSQL 11 на мажорные 12 без остановки работы промышленной базы.

В DigitalOcean обращают внимание потенциальных пользователей на такие новшества 12-й версии как SQL/JSONpath, оптимизацию CTE (запросов с WITH) и генерируемых столбцов.

Персоны

Персоной недели в июне побывал уроженец Севастополя Олексий (Alex) Клюкин, работавший над Patroni и Postgres Operator в Zalando. Теперь работает в Adjust.com над инфраструктурой PostgreSQL.

С тех пор в этом качестве выступило немало и знаменитых людей, и не слишком известных в сообществе (правда они нередко уж слишком лаконичны):
Умайр Шахид (Umair Shahid)
Стейси Хейслер (Stacey Haysler)
Кохеи Кайгаи (Kohei Kaigai)
Эндрю Данстан (Andrew Dunstan)
Саймон Риггс (Simon Riggs)
Томас Вондра (Tomas Vondra)

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

Постгрес-вторники (#RuPostgres)

В-27: Разговор про коррупцию и антикоррупционные меры в PG, опыт Яндекс.Облака, DataEgret, Postgres.ai. В гостях эксперты Яндекс.Облака Андрей Бородин и Дмитрий Сарафанников.

В-26: Аналитика в Postgres. Снова (см. В-24) про шардинг. Миграции БД. JOIN в Монге.
Кое-что про то, как делать изменения под нагрузкой, имея в виду:
Database Migration Style Guide GitLab
Partitioning a large table without a long-running lock
PostgreSQL at Scale: Database Schema Changes Without Downtime

В-25:
В-25: Быстрая диагностика проблем Postgres с Алексеем Лесовским. pgCenter.
pgCenter с чего всё началось, как развивалось. Возможности; демо; ограничения (statement_timeout и прочее) pgCenter.

В-24:
Транзакции, новые распределённые СУБД (YDB и др.), FDW, шардинг.
В гостях: Стас Кельвич (сейчас Яндекс, до этого Postgres Professional).
YDB, Google Spanner, CockroachDB каковы различия.
Согласование в распределённых транзакциях и зачем атомные часы.

Вот файл с информацией о вторниках, в него можно дописывать свои предложения.

Конференции

PGConf.EU 2020
Отменена. Переносится на 2021.

PGDay Ukraine
Должна состояться во Львове 5-го сентября.

pgDay Israel 2020
Намечена на 10-е сентября в Тель-Авиве.

PGDay Austria
Ожидается в замке Шёнбрунн (около Вены) 8-го сентября.

---

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

Жизнь продолжается. А мы продолжаем знакомить вас с самыми интересными новостями 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 Mejas.

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

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


Пополнение в Core Team

Напоминаем о неписанном правиле сообщества: в Core Team не должно быть большинство из одной компании. После слияния-поглощения EDB 2ndQuadrant 3 из 5 участников Основной Команды оказались коллегами по EDB. К счастью, никого не сократили, а добавили двух достойных: Андреса Фройнда (Andres Freund, Microsoft, Citus) и Джонатана Каца (Jonathan Katz, Crunchy Data).

Любимые области Андреса Фройнда: репликация, производительность и масштабируемость (смотрите три недавние статьи на эту тему, ссылки в нашем разделе Статьи. Производительность), хранение.

Джонатан Кац (Jonathan Katz, Crunchy Data) занимался патчами и ревью, но больше концентрировался на разработке и поддержке сайта, выпуске релизов и прочей сопутствующей, но необходимой деятельности. Он вообще важный человек: председатель совета директоров Ассоциации PostgreSQL в США (United States PostgreSQL Association) и директор Ассоциации PostgreSQL-сообщества Канады (PostgreSQL Community Association of Canada), которая выступает как юридическое лицо сообщества.

Прекрасное, взвешенное решение. Впрочем, не все с этим согласны: Альваро Эрнандес (lvaro Hernndez Tortosa если полностью) поздравил новоизбранных (непонятно кем и непонятно как по его мнению) и предложил задуматься над следующими 10 проблемами управления сообществом:
Влияние компаний:

• 40% из Core Team были из одной компании, теперь 43%, 71% из двух;
• 100% из всего лишь 4 компаний.

Многообразие (diversity):

• 100% это белые мужчины;
• 100% из США или Европы;
• все кроме одного работают в американских компаниях.

Демократия:

• членов Core Team назначают члены Core Team;
• срок неограничен, четверо являются членами уже больше 15 лет.

Прозрачность:

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

Альваро предлагает высказаться. И Ханс-Юрген Шёниг (Hans-Jrgen Schnig) высказывается:
Никогда не замечал и тени расизма при принятии патчей. Может и дальше будем продолжать как было думать о компетентности, а не о расе, гендере или о чём там? У нас с этим никогда не было проблем. Так зачем проблему создавать? Клаус Расмуссен (ClausRasmussen) ещё решительней: зачем нам этот crap с идентичностями? У нас технологическое сообщество, а не Liberal_arts_college. Желающие могут запастись попкорном и следить за дискуссией. Этот текст обсуждается также здесь.

Я опустил детали в обращении Альваро. Ещё одна из упомянутых им проблем (существующих с точки зрения Альваро): Core Team это центральный орган проекта. А юридически проект представляет Postgres Association of Canada, определяя в том числе интеллектуальную собственность: доменные имена, торговые марки и прочее. Как бы чего не вышло.

CF-новость

Анастасия Лубенникова из Postgres Professional стала распорядителем текущего коммитфеста. В этом ей помогает Георгиос Коколатос (Georgios Kokolatos).

Новости PG-этики

А ещё Анастасия входит в Комитет по этике (Code of Conduct Committee) сообщества (а Илья Космодемьянский вышел из комитета).

Кстати, благодаря то ли Альваро, то ли общему настроению, Комитет по этике объявил вакансии: нужны люди из разных стран и разных народов, чтобы отразить многообразие PostgreSQL-сообщества. Пишите на coc@postgresql.org

Документация к PostgreSQL 13.0

The PostgreSQL Global Development Group объявила о доступности русской документации к версии 13. Перевод на русский язык компания Postgres Professional. Официальная страница русскоязычной документации.

Обучение

DEV2: Разработка серверной части приложений PostgreSQL 12. Расширенный курс.

Новый курс продолжительностью 4 дня. В нём:

• понимание внутренней организации сервера;
• полное использование возможностей, предоставляемых PostgreSQL для реализации логики приложения;
• расширение возможностей СУБД для решения специальных задач.

Основная идея курса показать не просто базовые функции PostgreSQL, но и его расширяемость: возможность дополнить серверные механизмы собственным кодом, что позволяет использовать PostgreSQL для решения самых разнообразных задач.

Статьи

Масштабируемость и производительность

Measuring the Memory Overhead of a Postgres Connection

Андрес Фройнд (тот самый, кто только что обосновался в PostgreSQL Core Team) опубликовал серию из 3 статей о производительности PostgreSQL при большом числе соединений. Они дублируются в блоге Citus и в блоге Microsoft (пока 20 лайков, 2 подписчика).

В статье об издержках памяти начинается с популярного мотива а если бы треды, а не процессы? Спойлер Андреса: если аккуратно померить, то издержки меньше 2 мебибайта. А неаккуратно это при помощи top и ps.

Для более тонких замеров памяти Андрес использует системные /proc/$pid/status и /proc/$pid/smaps_rollup. Так можно увидеть значения VmRSS, VmRSS, RssAnon, RssFile, RssShmem если вы не знали, что это, то из статьи узнаете и поймёте, почему они важны. Чтобы не обмануться с причиной перерасхода памяти, он замеряет с включенным и отключенным huge_pages. Ещё: надо помнить о copy-on-write при форке процесса.

Analyzing the Limits of Connection Scalability in Postgres

Андрес исследует узкие места с тем, чтобы далее предложить путь их решения, и аргументирует не только из общих соображений, а с примерами и листингами. Раздувание кеша (cache bloat) тоже (как и оверхед при форке) не критично. Управление work_mem тоже удовлетворительно. А собака зарыта в куче снэпшотов: функция GetSnapshotData() дорогая и вызывается часто. Вывод: надо менять саму модель соединений (connection model), а может и модель исполнения запросов (query execution model). А от себя добавим: эта тема более, чем активно обсуждалась в рассылке hackers. Более того: в Postgres Professional давно ведутся разработки в этом направлении. Начиная с 12-й версии в Postgre Pro Enterprise Edition есть встроенный пул соединений. Это не совсем то, что сделал Андрес, но это тоже в тему масштабируемости клиентских соединений.

За диагностической 2-й статьёй следует 3-я конструктивная: предложения Андреса уже в форме патчей, которые должны войти в версию PostgreSQL 14:

Improving Postgres Connection Scalability: Snapshots

Пересказывать эту статью в паре абзацев, кажется, бессмысленно. Даём ссылки на серию патчей Андреса (все они начинаются с snapshot scalability: здесь опускаем):
Dont compute global horizons while building snapshots
Move PGXACT->xmin back to PGPROC
Introduce dense array of in-progress xids
Move PGXACT->vacuumFlags to ProcGlobal->vacuumFlags
Move subxact info to ProcGlobal, remove PGXACT.
cache snapshots using a xact completion counter
(Об этом также здесь)

Другую серию из 3 статей в жанре от 8.3 и до 13 опубликовал Томаш Вондра (Tomas Vondra, 2ndQuadrant то есть EDB).

OLTP performance since PostgreSQL 8.3

В этой статье Томаш сначала объясняет замысел серии: почему начал с 8.3, почему именно эти тесты, зачем ему тестировать полнотекстовый поиск, на какой машине тестировать. Он не ставит цели сверхкорректного сравнения, это скорее упражнение для лучшего понимания PostgreSQL. До 8.3 он уж слишком отличался от нынешнего, охват и так недурен: 12 лет. А машина обычный офисный компьютер.

В 1-й статье серии Томаш исследует производительность OLTP на bgbench, взятой из 13-й версии, scale 100 (1.6 ГБ), 1 000 (16 ГБ) и 10 000 (160 ГБ). Клиенты от 1 до 256. Хранение NVMe SSD / SATA RAID; режимы: read-only (pgbench -S) / read-write (pgbench -N)

Графики с NVMe SSD ведут себя прилично: производительность в основном монотонно растёт с номером версии. А вот с SATA творятся чудеса: c SATA RAID в режиме чтения некоторые флюктуации и, похоже, регресс в версии 9.6. А вот на записи-чтении грандиозное ускорение с версии 9.1 в 6 раз!

Томаш уверен, что постгрессистам придут в голову блестящие идеи, как эффективней использовать ресурсы железа. Патчи по улучшению масштабированию соединений или патч по неволатильным буферам WAL тому пример. Можно ждать радикальных улучшений в хранении (более эффективный формат файлов на диске, использование прямого ввода-вывода, например), более эффективные индексы.

TPC-H performance since PostgreSQL 8.3

Для измерения производительности на аналитических нагрузках Томаш запускал бенчмарк TPC-H (его ещё называют бенчмарком принятия решений decision support), получал результаты, которые можно анализировать ещё очень долго, нарисовал красивые графики, и сделал свои выводы в меру отпущенного на это времени.

В TPC-H 22 запроса на 3 наборах данных: малом, среднем и большом. Томаш гоняет их на версиях от 8.3 до 13, да ещё и то включает, то отключает параллелизм. Коэффициенты масштабирования (scale factor) он выбирает такие: 1 (цель поместиться в shared-buffers), 10 (в память) и 75 (не поместиться в память). Комбинаций море, для анализа простор. Иногда автор действительно опускается до отдельных запросов и анализирует причины странного поведения. Кривая производительности немонотонно меняется с версией, а по отдельным запросам скачет совсем неожиданно. Причина простая: планировщик и оптимизатор умнеют с новыми версиями за счёт новых планов и/или за счет новых способов использования статистики, но оборотная сторона промахи: неверный выбор плана из-за плохой статистики, оценок стоимостей или других ошибок. Примерно то же и с параллелизмом: появляются новые планы, но если стоимости и оценки расходятся с реальностью, выбираются планы, хуже старых, последовательных.


Диаграмма из статьи TPC-H performance since PostgreSQL 8.3. Можно было поместить в наш раздел Прекрасное.

Full-text search since PostgreSQL 8.3

В преамбуле Томаш рассказывает историю FTS в PostgreSQL, которая началась с Олега Бартунова и Фёдора Сигаева лет за 20 до основания Postgres Professional. Далее Томаш сетует на отсутствие индустриальных стандартов тестирования полнотекстового поиска и обращается к собственным ресурсам ПО: в незапамятные времена он сочинил утилиту archie парочку питоновых скриптов, которые загружают архивы переписки PostgreSQL, превращая их в базу, которую можно индексировать, в которой можно искать тексты. Сейчас в таких архивах около миллиона строк 9.5 ГБ не считая индексов. В качестве тестовых запросов он взял 33 тыс. реальных поисковых запросов к архиву на сайте PostgreSQL.org.


Фёдор Сигаев и Олег Бартунов. Фотография из статьи Full-text search since PostgreSQL 8.3

Запросы были разного типа, но для статьи взял вот такие с tsvector, придуманным ещё Бартуновым и Сигаевым:

SELECT id, subject FROM messages WHERE body_tsvector @@ $1SELECT id, subject FROM messages WHERE body_tsvector @@ $1ORDER BY ts_rank(body_tsvector, $1) DESC LIMIT 100

Кроме того Томаш тестировал влияние индексов GIN и GiST. Оба запроса с использованием GIN дают огромный скачок в производительности в 4 с лишним раза! Томаш благодарит за это Александра Короткова и Хейкки Линнакангас (Heikki Linnakangas), придумавших патч Improve speed of multi-key GIN lookups. А вот если использовать GiST, то ничего хорошего вообще не будет. А будет плавная деградация. Почему ж никто не жаловался? вопрошает автор и предполагает, что вместе с апгрейдом версий многие апгрейдили и железо, и это маскировало эффект. Или просто не использовали GiST для текстового поиска.

Олег, Теодор [Фёдор] и их коллеги напоминает Томаш работали над более мощными вариантами GIN-индексов VODKA и RUM [примечание редакции: об индексах RUM, о том, чем они лучше GIN, о расширении rum можно почитать здесь. Про водку не будем :)]. Это как минимум поможет некоторым типам запросов. Особенно автор надеется на улучшение поддержки новых типов полнотекстовых запросов, так как новые типы индексов спроектированы для того, чтобы ускорить фразовый поиск (см. там же).

Книжечки

Кстати, о текстовых файлах и поиске в них. Вот 196640 книг (файлов) в текстовом формате. Их, скорее всего, будут использовать для обучения больших сетей, но можно их, скажем, использовать и в каких-нибудь тестах производительности текстового поиска или ещё каких-то манипуляций текстом. Собирали тексты энтузиасты с the-eye.eu (почему-то недоступного честному пользователю из РФ).

PostgreSQL 14: Часть 2 или в тени тринадцатой (Коммитфест 2020-09)

Эта статья Павла Лузанова из отдела образования Postgres Professional и о производительности тоже: постольку, поскольку патчи, принятые на этом коммитфесте, имели отношение к производительности (о патчах Андреса, которые он упоминал, там тоже есть). Это, как и Часть 1 (Коммитфест 2020-07), MUST READ для тех, кто следит за технологическими новшествами PostgreSQL без IMHO.

Жизнь в PostgreSQL

памяти Джона Хортона Конвея, умершего от COVID-19

Открывает эту мемориальную подборку ссылок недавняя статья Егора Рогова: Жизнь на PostgreSQL

Некто Сергей aka ildarovich делает это на языке запросов 1С, а точнее одним запросом: Игра Жизнь в одном запросе

А вот на C#: Как ускорить игру Жизнь в сто раз, в комментариях есть SQL-код.

На JS, огромная статья, очень красивая визуализация: Эволюционирующие клеточные автоматы

Кстати, о Конвее: Джо (Joe), однофамилец классика клеточных автоматов (в прошлом выпуске мы ссылались на статью 2007-го года про то, как использовать PL/R для GIS) теперь, в начале ноября 2020, пишет на тему сверх-актуальную:

Election Night Prediction Modeling using PL/R in Postgres

Он использует пакеты mvtnorm (3 алгоритма нормального распределения), politicaldata (специальные тулзы для сбора и анализа политических данных) и tidyverse (разные средства анализа данных). Для развлечения Джо предлагает разобраться в немалом количестве строк кода, создаёт свой тип данных и ещё предлагает придумать SQL-запросы в качестве упражнения.

Релизы

PostgreSQL 13.1

А также 12.5, 11.10, 10.15, 9.6.20 и 9.5.24. В новых версиях исправлены обнаруженные баги, в том числе связанные с безопасностью. Сейчас мы не будем на них останавливаться. Они описаны на этой странице.

OpenGauss 1.0.1

Сотрудник Huawei Вадим Гусев сообщает на хабре о появлении openGauss: новая СУБД от Huawei для нагруженных enterprise-проектов прибавила в функциональности

Это форк PostgreSQL, опенсорсный вариант проприетарной GaussDB, который работает на x86 и китайских процессорах Kunpeng 920, у которых архитектура ARM64 (к слову: напоминаем, что ARM ltd куплена Nvidia), то есть мы можем предположить курс на китайское импортозамещение (в нише ARM у нас не Эльбрусы, а Байкалы).
Как утверждают создатели, у OpenGauss гибридная ориентация в духе HTAP, и она многое умеет :

• колоночное хранение;
• in-memory engine;
• развертывается решение как в контейнерах, так и на физических серверах;
• ИИ (глубокое обучение с подкреплением в сочетании с эвристическими алгоритмами) рекомендует параметры.;
• инкрементальное резервное копирование;
• Standby на удаленной площадке в синхронном или асинхронном режиме (до четырех реплик на физическом уровне).

В статье с длинным интернациональным списком авторов (фамилии от индийских до русских, китайцы в меньшинстве) оценивается производительность на TPC-C.

Database Lab 2.0

Николай Самохвалов и Артём Картасов из Postgres.ai (Артём делал бОльшую часть кода) на Постгрес-вторнике 3 ноября рассказали (за полтора часа) о Database Lab 2.0 новой, сильно отличающейся версии своей среды для тестирования и разработки с тонкими клонами (при клонировании копируются только измененные блоки).

Новое:

• поддержка RDS и других облачных Postgres-сервисов;
• физическое развертывание с нативной поддержкой WAL-G;
• декларативное развертывание;
• управление снэпшотами, политики снэпшотов;
• предобработка данных (анонимизация);
• time travel для диагностики, контроля изменений, быстрого точечного восстановления;
• оптимизация SQL на новом уровне: serverless EXPLAIN и бот-помощник для оптимизации;
• 100% покрытие миграций БД (изменение схемы) автоматическими тестами на полноразмерных копиях БД;
• регрессивные тесты;
• поддержка docker-имиджей для Postgres 9.6, 10, 11, 12 и 13; по умолчанию в них расширения Timescale, Citus, PoWA и много других, а также большинство расширений, поддерживаемых Amazon RDS;
• документация сильно расширена.
  

pg_statement_rollback 1.0

pg_statement_rollback это расширение Жиля Дароля (Gilles Darold), Жульена Руо (Julien Rouhaud) и Дэйва Шарпа (Dave Sharpe), которое реализует в PostgreSQL откат транзакции на уровне оператора (server side rollback at statement level for PostgreSQL) как в Oracle или DB2. Это значит, что при ошибке в выполнении оператора его результаты не видны как будто оператора и не было. При этом результаты операторов, выполненных в транзакции до этого, не теряются. В PostgreSQL это можно было сделать только на клиенте, в psql, например:

\set ON_ERROR_ROLLBACK on

Теперь всё будет работать на сервере таким образом, как будто для каждого оператора серверу посылаются

SAVEPOINT autosaveиRELEASE SAVEPOINT autosave

а такая роскошь раньше могла сказаться на производительности. Авторы дают результаты тестов TPS-B и честно рассказывают о проблемах.

pgbitmap 0.9.3

Бета-релиз расширения pgbitmap, доступно на pgxn и github.

Это расширение Марка Манро (Marc Munro) создаёт тип pgbitmap с полным набором функций, операторов и агрегатов. Он отличается от стандартных типов Postgres bit и bit varying тем, что строка не начинается с нулевого бита и тем, что набор операций намного богаче. Этот тип разрабатывался под Virtual Private Database для управления привилегиями. В этом релизе исправлены ошибки, он считается релизом-кандидатом. Сейчас открытых багов не осталось присылайте, если найдёте.
Документация здесь.

pgpool-II 4.2 beta1

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

• улучшено и упрощено конфигурирование логирования;
• добавлен новый режим кластера: snapshot_isolation_mode, который гарантирует не только модификацию данных нескольким инстансам, но и согласованность по чтению;
• поддержка LDAP-аутентификации между клиентом и Pgpool-II;
• импорт SQL-парсера PostgreSQL 13.

и прочее, о чём можно прочитать в Release notes.

Загрузить можно отсюда.

pg_activity 1.6.2

pg_activity это интерфейс в стиле top для мониторинга бэкендов PostgreSQL в реальном времени. Поддерживается Бенуа Лабро (Benoit Lobrau, Dalibo Labs). В нём можно:

• настраивать частоту обновления;
• переключаться между тремя представлениями запросов: исполняющиеся/ждущие/блокирующие;
• сортировать по PostgreSQL-метрикам: READ/s, WRITE/s

Зависимостей теперь мало. Работает на Python 2.6+. Исходники здесь.

pgcenter 0.6.6

На гитхабе Алексея Лесовского (Data Egret) появилась новая версия. В ней:

• рейтинги запросов адаптированы к версии PostgreSQL 13;
• тайминги операторов адаптированы к версии 13;
• надо проапдейтить конфигурацию travic-ci: отключить skip_cleanup; проапгерйдить Go до версии 1.14.

pglogical 2.3.3

Появилась поддержка PostgreSQL 13. Загружать отсюда. Чейнджлог недоступен, за информацией велено обращаться к info@2ndQuadrant.com.

repmgr 5.2.0

Добавлена поддержка PostgreSQL 13. Из изменений:

• новая опция --verify-backup запускает утилиту pg_verifybackup после сканирования реплики, чтобы убедиться в консистентности скопированных данных (только для PostgreSQL 13 и позже);
• у failover_validation_command появились новые параметры и конфигурационная опция always_promote для управления промоутированием ноды в случае, когда метаданные repmgr уже неактуальны;
• поддержка PostgreSQL 9.3 прекращена.

Есть и другие изменения, о которых можно узнать здесь. Сорсы находятся здесь, а инструкции по инсталляции здесь.

Прекрасное

Популярность баз 2006 2020

Скриншоты не передадут гипнотической мощи этой динамической инфографики от DB weekly. Это кино увлекательно, познавательно воодушевляюще и даже чуть-чуть отрезвляюще в то же время.



а через 14 лет популярность PostgreSQL выросла более, чем в 2 раза:



Postgres Observability

Интерактивный шедевр наглядности & информативности (этот скриншот в подмётки не годится). Автор Алексей Лесовский из DataEgret.

Конференции

Highload++

Внимание: переносится! Новые даты конференции 17 и 18 февраля 2021 года!

Ибица 2020 зачеркнуто 2021

Одна из самых любимых PG-народом конференций Postgres Ibiza 2020 должна состояться в 2021 году 23-25-го июня (дата предварительная). Следите за новостями на pgibz.io или на сайте FUNDACIN POSTGRESQL сообщества с испаноязычным уклоном. Про Бали пока не слышно.

Postgres Build 2020

Виртуальная европейская конференция по PostgreSQL, посещение бесплатное. Фокус на кейсы реальных клиентов. Пройдёт 8-9 декабря 2020 он-лайн. Twitter и LinkedIn: #postgresbuild.

Ну и год выдался! Подходит к концу. 21-му надо изрядно постараться, чтобы стать хуже. Но он надеемся стараться не будет. А жизнь продолжается. И мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL.

Но сначала поделимся воспоминаниями: как проводил время на хабре отдел образования компании Postgres Professional:

• Начнём с того, что под рукой с Postgresso. Из фонового и иногда побочного занятия Postgresso сместился к центру, стал новостным каналом со стабильной периодичностью примерно месяц. Мы отказались от плоского формата большой простыни с большим списком релизов и статей по 3-5 строчек на каждую. В 21-м продолжим экспериментировать, но от периодичности не откажемся.
• Наш коллективный труд PostgreSQL 13. Чертова дюжина. Первый (задержка в 37 минут после заморозки) и самый полный обзор возможностей 13 версии. Далее последовали обзоры коммитфестов: Июльский, Сентябрьский и Ноябрьский Павла Лузанова. Эта практика 20-го года будет продолжена и в 21-м. Мы часто сами на них ссылаемся а как не сослаться? Они действительно информативны.
• Жизнь в PostgreSQL и в Postgresso 26 подборка других реализаций Жизни памяти Джона Хортона Конвея, умершего от COVID-19.
• Автор статьи Серверное программирование на человеческом языке, очень понравившейся хабр-читателям Иван Панченко. Мы помогали Ивану в подготовке статьи.
• Сотрудник нашего отдела образования Павел Толмачёв написал для хабра статью о модуле aqo. Тема непростая, а тема использование ИИ для оптимизации запросов актуальна, а станет ещё актуальней.
• К тому же бОльшая часть статей была переведена на английский (спасибо Елене Индрупской за титанический труд). Это серии очень глубоких погружений Егора Рогова Locks in PostgreSQL (ru), WAL in PostgreSQL (ru), MVCC in PostgreSQL (ru) и Indexes in PostgreSQL (ru). Кроме того переведён ещё десяток статей, наиболее интересных для англоязычной аудитории. Некоторые из этих статей попадали в англоязычные обзоры самых интересный статей.

Релизы

Вышла Postgres Pro Standard 13

18 декабря 2020 года компания Postgres Professional выпустила новый релиз Postgres Pro Standard 13.1.1. Это первый из тринадцатых релизов Postgres Pro.

Среди новых возможностей:

Новое расширение pgpro_pwr (или PWR, читается как power). Это расширение позволяет получать отчёты по нагрузке, полезные для выявления наиболее ресурсоёмких операций в базе данных. Оно умеет использовать данные расширения pgpro_stats, предназначенного для сбора статистики выполнения SQL-операторов и подсчёта статистики событий ожидания. pgpro_stats обновлено. В январе мы опубликуем на хабре отдельную статью о PWR.

Появилась поддержка операционной системы ОСнова 2.0. Также исправлены ошибки в PostgreSQL 13.1. Среди этих исправлений устранение уязвимостей CVE-2020-25694, CVE-2020-25695 и CVE-2020-25696 (6 патчей сотрудников Postgres Professional).

Postgres Operator v1.6.0

Релиз поддерживает последнюю PostgreSQL 13 и новый образ Spilo 13 (спило слон по-грузински), в котором имеется Patroni 2.0 (но последняя версия Patroni на сегодня 2.0.1). Апгрейд ещё не автоматический, но сильно упростился. Проще стало развертывание pgBouncer на репликах. Подробности в чейнджлоге и в доке.

Pgpool-II 4.2.0

Изменения:

• в этом релизе теперь во всех образцах файла pgpool.conf путь к сокетам /var/run/postgresql;
• Используется единственный сегмент разделяемой памяти для всех разделяемых переменных родительского процесса pgpool;
• при старте убиваются существовавшие до того файлы сокетов watchdog

Загрузить можно отсюда.

pg_timetable: Advanced PostgreSQL Scheduling

Это шедулер, написанный на Go разработчиками Cybertec и работающий как отдельное приложение (для сравнения: pgpro_scheduler выполнен как расширение). Он умеет выполнять задания, состоящие из нескольких разнородных действий, например:

• начать транзакцию;
• записать в лог;
• загрузить файл;
• импортировать файл;
• запустить агрегирование;
• закоммитить транзакцию.

pg_timetable на гитхабе.

Новый начальник Коммитфеста

Масахико Савада (Masahiko Sawada, NTT) стал распорядителем нового Коммитфеста (предыдущий координировала Анастасия Лубенникова)

Статьи

PostgreSQL 14: Часть 3 или ноябрьское затишье (Коммитфест 2020-11)

Это изменения после ноябрьского коммитфеста, последнего в 2020. Павел Лузанов сам предлагает обратить особое внимание на вопросы:

• Не пора ли увеличивать wal_buffers?
• Можно ли перегружать хранимые подпрограммы по OUT-параметрам?
• По умолчанию pg_stat_statements собирает данные о 5000 запросов. Как понять много это или мало?
• Что будет, если в операционной системе обновится библиотека libc?

Текст огромный, так как список изменений полный. Продолжение следует после январского коммитфеста.

Обзор операторов PostgreSQL для Kubernetes: Часть 1: наш выбор и опыт и Часть 2: дополнения и итоговое сравнение"

В первой части Николай Богданов в блоге компании Флант, советовал начать с доклада на Highload++ своего коллеги Дмитрия Столярова, где тот знакомит с общими принципами работы баз данных в Kubernetes (K8s). Николай же формулирует 6 основных требований со стороны K8s и рассматривает операторы:

• Stolon. Этот довольно популярный отказоустойчивый кластер интегрирован в K8s. Но Stolon не подошёл, так как первое же (деплой из Git и с Custom Resources) из тех кубернетовских требований не удовлетворено (нет Custom).
• Crunchy Data PostgreSQL Operator разработка нашего старого postgres-знакомого CrunchyData (автор называет молодым стартапом) богат фичами, но он оттолкнул несоответствием принятым в K8s стандартным возможностям работы с ресурсами.
• Zalando Postgres Operator понравился больше всего. И возможностей много, и развивается быстро, и соответствует look & feel в глазах истых кубернетчиков.

Дальше Николай начинает работать с Crunchy Data PostgreSQL Operator, делится впечатлениям. А они не столько радужны, как хотелось. Список проблем и их решений, а также план миграции прилагаются.
Во второй части обзора, вышедшей 13-го ноября, добавились ещё два K8s-оператора:
KubeDB и
StackGres.
В результате появилась сводная таблица матрица имеющихся возможностей этих 5 операторов. Но сердце автора уже прикипело к Zalando, он объявлен лучшим вариантом для тру кубернетчика.

What are table access methods, and what is their importance to PostgreSQL?

Статья Панкаджа Капура (Pankaj Kapoor, Fujitsu) этакое обозрение уже не такой уж короткой (4 года) истории попыток интегрировать вертикальное хранение в PostgreSQL. Автор наблюдал этот процесс не как посторонний: Fujitsu, где он работает, предлагала сообществу свой Vertical Clustered Index в 2016, одновременно с патчем подобной направленности, предложенным Альваро Эррера (lvaro Herrera, 2ndQuadrant, теперь EDB). Со стороны Fujitsu внедрением Vertical Clustered Index занимался Харибабу Коми (Haribabu Komi). Но сообщество пошло другим путём: сосредоточило усилия на универсальном решении на API методов доступа к таблицам, по образцу методов доступа к индексам.

Сейчас, на конец 2019-го через слой методов доступа идёт интеграция с таблицами альтернативного типа хранения, zheap, например. Но пока только с доступом на базе кортежей, то есть до интеграции вертикального хранилища (реальный претендент Zedstore) ещё далеко.

Автор предлагает заодно ознакомиться со своей презентацией на PGCon2019.

Напомним и о vops интересном расширении Postgres Professional, поддерживающем векторные операции. Данные там группируются по значениям столбцов и хранятся в виде плиток (паркета).

Insert-Only Data Modelling To Smooth Peaks On Slow Disks

Каарел Моппел (Kaarel Moppel, Cybertec) предлагает неожиданный и даже контринтуитивный способ сглаживания пиков: вместо UPDATE данных только INSERT на время пиков нагрузки, чтобы потом, в спокойные часы разобраться с данными, вставленными в экстремальной ситуации. Выигрыш в скорости INSERT vs UPDATE на тестовых данных Каарела (100 млн записей) получился раза в 3. Конечно, этот способ подходит отнюдь не во всех случаях, но Каарел говорит об опыте конкретной проблемы заказчика, у которого не было возможности или желания апгрейдить железо из-за пиков, в то время, как в обычных условиях система справлялась.

10 Things I Hate About PostgreSQL

Под Новый Год лучше бы уж не о ненависти, а о любви. Ну да ладно. Рик Бронсон (Rick Branson), работавший в том числе с петабайтного масштаба проектами, решил подытожить 2020-й десяткой самых ненавистных ему особенностей PostgreSQL (некоторые наши спойлеры курсивом):

#1: Wraparound, чреватый катастрофой
[скорее всего когда-то в будущем XID-ы станут 64-разрядными целыми (то есть как уже давно в Postgres Pro Enterprise)];
#2: При переключении кластера (failover) могут потеряться данные;
#3: Неэффективная репликация, распространяющая испорченные данные;
#4: Частая сборка мусора в СУБД типа MVCC проходит болезненно
[Вся надежда Рика на будущий zheap];
#5: Принцип по процессу на соединение мешает масштабируемости
[Рик рассказывает, как использовал 2 слоя pgbouncer-ов и как доходило в общей сложности до миллиона процессов; а также скучает про тред-на-соединение в MySQL];
#6: Индекс по Primary Key очень прожорлив по части ресурсов
[Рик предлагает использовать индекс-таблицы];
#7: Для апгрейда мажорных версий может потребоваться остановка СУБД
[Из-за несовместимости бинарных форматов хранения файлов на диске могут потребоваться часы простоя. Это при потоковой репликации. Переход на логическую может решить проблему в будущем];
#8: Неуклюжая настройка репликации;
#9: Странная догма Никаких-подсказок-планировщику;
#10: Отсутствие компрессии на уровне блоков.

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

Waiting for PostgreSQL 14 Multirange datatypes

Как всегда активен Депеш, он же Хуберт Любашевски (Hubert Lubaczewski). Здесь он пишет о патче Александра Короткова. Как можно догадаться, многодиапазонные типы собираются из непересекающихся диапазонов. Как и диапазоны, они строятся на базе integer, bigintint, numeric, timestamp without time zone, timestamp with time zone, date.

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

SELECT * FROM testWHERE ranges = '{[77.7909859996235,177.7909859996235],(1035.84122266822,1135.84122266822],(1000099.99954803,1000199.99954803]}';

How to install and configure PostgreSQL Debian/Ubuntu for developer use part 1

А здесь Депеш решил расписать шаги по установке PostgreSQL-13, настройке пользователей, редактировании pg_hba.conf и запуске PgAdmin под произвольным пользователем. Это азбука, но какие-то нюансы могут и пригодиться. Содержание следующих частей пока не анонсировано. На всякий случай напоминаем о существовании Малютки.

Waiting for PostgreSQL 14 pg_stat_statements: Track time at which all statistics were last reset.

Идёт постоянное усовершенствование pg_stat_statements. В 1-м и 3-м обзорах коммитфестов от Павла Лузанова уже было о некоторых коммитах. Депеш пишет о важном коммите Фуджи Масао (Fujii Masao): времени последнего ресета статистики. Информацию в pg_stat_statements время от времени очищают приложения и отдельные запросы:

SELECT pg_stat_statements_reset();

Теперь можно спросить у pg_stat_statements о времени последней чистки:

SELECT stats_reset FROM pg_stat_statements_info; dealloc |          stats_reset          ---------+-------------------------------       0 | 2020-12-20 12:06:02.099943+01

Postgres, PL/Python and SciPy/NumPy for Processing Images

Это продолжение статьи о сохранении картинок через Django-приложение в тип PostgreSQL bytea. На этот раз картинки ещё и обрабатывают фильтром.

Is Update The Same As Delete + Insert In PostgreSQL

Ответ: почти. И дальше Лоренц Альбе (Laurenz Albe) из Cybertec исследует это почти. Речь о блокировках при стандартном уровне изоляции: READ COMMITTED.

Session 1                     Session 2 BEGIN; UPDATE uptest SET id = 2   WHERE val = 42;                               SELECT id FROM uptest                                  WHERE val = 42                                  FOR UPDATE;  -- hangsCOMMIT;                               -- one row is returned

А в другой раз:

Session 1                     Session 2 BEGIN; DELETE FROM uptest   WHERE id = 1; INSERT INTO uptest VALUES (2, 42);                               SELECT id FROM uptest                                  WHERE val = 42                                  FOR UPDATE;  -- hangsCOMMIT;                               -- no row is returned

в первый раз возвращается 1 запись, во втором 0.
Дальше Лоренц исследует эту ситуацию, используя расширение pageinspect, да ещё и рассказывает о разнице поведения атрибутов infomask и infomask2 в этих двух случаях.

Конференции

Неопределённость сохраняется. Кто-то уже объявил о переформатировании в он-лайн.

PGCon 2021

В 2021-м пройдёт 28-го мая в сокращенном формате. От конференции осталась только Unconference, которая уместится в zoom. Записаться можно здесь.

Nordic PGDay 2021

Запланирована на 18 марта в Хельсинки. Об он-лайне пока ни слова. Год назад эта конференция была отменена из-за эпидемии.

Облака

Want more PostgreSQL? You just might like Babelfish

Этот проект откровенно ориентирован на тех, кто хочет беспроблемно мигрировать с MS SQL Server на PostgreSQL. Утверждается, что Bablefish это PostgreSQL, совместимый с SQL Server настолько, что приложения, под него написанные (в том числе с T-SQL и протоколом TDS), будут сразу работать.

Новости юриспруденции

Trademark Policy изменилась

Изменения касаются и Slonik-а то есть милой сердцам постгресистов картинки, и торговых марок.

Кто ты, бек-эндер?

Может ты бэкендер? Этот в высшей степени непростой вопрос разбирается в пространном исследовании Острые орфографические боли по всей длине слова и как от них избавиться на сайте ГЗОМ. Любители отгадывать зажмурьтесь: дальше ответы-спойлеры.

Сегодня нормативно:
Бэк-энд, бэк-энд-разработчик. В профессиональных текстах back-end-разработчик.

Соответствуют русской орфографии:
Бэкендер, бэк-эндовый.

Лет через семь могут возобладать:
Бэкенд, бэкендовый.

---

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

Привет всем уже в 21-м году. Надеемся, он будет добрей к нам, чем прошлый. Жизнь продолжается. И мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL. Для разнообразия начну с конференций: этот жанр больше всего пострадал.

Конференции

Организаторы конференций выбирают разные стратегии в условиях карантина. Самый простой отменить, но лучше чем-то компенсировать.

Nordic PGDay 2021

Отменена. Рассчитывают на Хельсинки в марте 2022. Виртуального варианта не будет, но собираются сфокусироваться на PostgreSQL-треке FOSDEM 2021 в феврале. На сайте написано 2022, но имеется в виду, судя по всему FOSDEM 2021, о котором ниже.

А вот подход Highload++. Бескомпромиссный никакого онлайна:
Highload++ 2020 (2021)

Конференцию HighLoad++ не стали переносить в онлайн решили, что она для этого слишком масштабная. Но даты передвинули с 9-10 ноября 2020 г. на 20-21 мая 2021 года. Должна пройти в Москве в Крокус Экспо 3.

А вот полная противоположность:
FOSDEM 2021

Никакого Брюсселя, в 2021 только онлайн. Не только бесплатно, но и регистрации даже не требуется. Среди участников этой огромной конференции немало докладчиков, известных среди российских постгресистов: Олег Бартунов, Павел Борисов, Алексей Кондратов, Анастасия Лубенникова, Никита Глухов (Postgres Professional), Николай Самохвалов (Postgres.ai), Пётр Зайцев (Percona), Андрей Бородин (Yandex), Олег Иванов (Samsung AI Center, он автор плагина AQO в Postgres Pro Enterprise).
Расписание можно попробовать изучить здесь. Поток PostgreSQL здесь.

PGConf.Online 2021

Последняя в этом списке, компенсирую большим количеством знаков: у меня просто больше информации.
Здесь комбинация оф и он: офлайн-конференция PGConf.Russia 2021 запланирована на на конец мая начало июня 2021 года. А 1-3 марта будет проведена онлайн-конференция с соответствующим названием PGConf.Online 2021.

Темы конференции:

• Postgres на предприятии;
• Масштабируемость;
• Высокие нагрузки и очень большие базы данных;
• devops;
• Переход на Postgres.

Участие в онлайновой конференции бесплатное. Всем желающим участвовать нужно предварительно зарегистрироваться на сайте, трансляция докладов будет вестись из личных кабинетов. Если уже оплатили PGConf.Russia 2021, то регистрироваться повторно не нужно. Регистрация действительна для обоих событий PGConf.Online и ближайшего PGConf.Russia. Также можно отказаться от участия в PGConf.Russia и вернуть свои деньги. Для этого надо написать на info@pgconf.ru.

Доклады принимаются до 10 февраля в двух форматах: кратком (22 мин + вопросы) и полном (45 мин + вопросы) на русском и английском языках. Также приветствуются мастер-классы с практическими упражнениями и обучающие лекции по вопросам расширенной разработки и DBA. Мастер-классы могут длиться 90 или 180 минут.

На PGConf.Online 2021 возможны доклады как онлайн, так и предварительно записанные, по желанию. Планируется, что даже после записанного выступления докладчик будет онлайн отвечать на вопросы, которые будут собираться в чате модераторами.

Соревнования

PostgreSQL is the DBMS of the Year 2020

СУБД года! Это не рейтинг популярности, а рейтинг роста популярности. Из рейтингов на январь 2021 вычитаются рейтинги за январь 2020. А они вычисляются по методологии экспертов db-engines. По абсолютной, а не дифференциальной популярности postgreSQL по-прежнему на 4-м месте.
О соревновании x86 с ARM в облаках см. далее.

Облака

Тема ARM в облаках набирает обороты. Что не удивительно ARM наступает широким фронтом: суперкомпьютер на ARM взобрался на верхушку Top500; новые попытки Apple; процессор Whitechapel у Google; процессоры от Ampere Computing появятся в облаках Oracle; ну и, конечно, процессоры AWS Graviton2 с ядром Arm Neoverse в исполнении Amazon.

Вот две статьи: в одной Hosting Postgres on an AWS EC2 t4g Graviton2 ARM Instance рассказывается, как запустить и настроить инстансы t4g (но ещё и о выборе EC2 vs RDS); в другой PostgreSQL on ARM-based AWS EC2 Instances: Is It Any Good? исследуется производительность. Об этом чуть подробней: Жобин Аугустин (Jobin Augustine) и Сергей Кузьмичев (Sergey Kuzmichev) из Percona тестировали ARM vs. x86. ARM на инстансах m6gd.8xlarge на базе ARM-процессоров AWS Graviton2. Сам Amazon позиционирует их как обеспечивающий на 40 % лучшее соотношение цены и производительности по сравнению с показателями x86-инстансов M5 в тестах m5d.8xlarge. В обоих инстансах по 32 виртуальных процессора.

Для разминки прогнали на pgbench, ARM выиграл и на Read-Write и на Read-Only в районе 20%. При этом тестировщики не забывали отключать и включать проверку контрольных сумм мало ли что, архитектура разная. Затем перешли к основным перконовским тестам sysbench-tpcc. Размер базы подбирали так, чтобы она умещалась в память. Стали смотреть результаты на числе потоков от 16 до 128. Получилось, что на 16 примерно та же картина, как и на pgbench, а когда потоков больше, чем виртуальных процессоров, игра в ничью. Чтобы уж совсем не огорчать поклонников x86, авторы констатировали худшую производительность у ARM на тестах, оценивающих ввод-вывод. Но и то при 128 потоках. Подробности в статье и на гитхабе.

Теперь информация, связанных с апгрейдом в облаках Amazon:
Ensuring Consistent Performance After Version Upgrades with Amazon Aurora PostgreSQL Query Plan Management

Query Plan Management это расширение apg_plan_mgmt. В статье показано, как после апгрейда кластера Aurora PostgreSQL с 9.6.11 на 10.12 при помощи этого инструмента можно легко проверить, использует ли планировщик одобренный в предыдущей версии план запроса (планы могут получать статус Approved, Rejected, Unapproved, или Preferred).

Кстати, о версиях:
Amazon RDS for PostgreSQL Supports 12.5

RDS теперь поддерживает минорные версии: 12.5, 11.10, 10.15, 9.6.20 и 9.5.24.

Релизы

pgAdmin 4 v4.30

В этой версии появился (пока в статусе бета) новый инструмент: ERD диаграммы сущность-связь, то есть графическая репрезентация таблиц, столбцов и их взаимосвязей. Можно визуализировать уже существующие схемы БД, можно создавать мышью новые, а ERD сгенерит соответствующие SQL. Также появилась поддержка Kerberos.

PostgreSQL-плагин для Zabbix 5.2.4rc1

В новой версии появилась поддержка custom query для плагина PostgreSQL. Теперь можно создать файл .sql и положить его на свою машину. Далее в web-интерфейсе своего Zabbix-сервера в шаблоне для Zabbix-Agent2 находим элемент под названием pgsql.query.custom и в нем указываем макрос, который должен иметь значение имени sql файла с запросом (при этом в конфигурационном файле Zabbix-Agent2 нужно указать путь на машине к папке с sql файлом. И тогда агент сам выполняет запрос в sql файле и пришлет результат на Zabbix-сервер с остальными, дефолтными метриками. Автор плагина Дарья Вилкова, Postgres Professional.

Целая серия новых версий FDW:

sqlite_fdw 1.3.1
InfluxDB fdw 0.3
griddb_fdw 1.3

PostgresNIO 1.0

Это неблокирующий, event-driven клиент для Swift от Vapor, построенный на эппловской SwiftNIO. Этот клиент устанавливает соединение, авторизует и отправляет запрос на сервер, а результат обратно. Использует протокол PostgreSQL. Умеет создавать пул соединений. И ещё есть пакеты более высокого уровня поверх PostgresNIO postgres-kit.

PGMoon 12.0-1

pgmoon это клиентская библиотека, написанная на чистом Lua (MoonScript). pgmoon с самого начала была разработана для использования в OpenResty web-платформе на базе докрученного Nginx), чтобы можно было пользоваться API 100% неблокирующих cosockets для асинхронных запросов.

Ещё статьи

Расширение кластера PostgreSQL размером 5,7 ТБ и переход с версии 9.6 на 12.4

Статья в блоге Альфа-Банка, автор оригинала Томми Ли (Tommy Li, Coffee Meets Bagel приложение для романтических знакомств с системой курирования).

Базы работали на 6 серверах Postgres на инстансах i3.8xlarge в амазоновском облаке: одна главная нода, три реплики для раздачи веб-трафика только для чтения, балансируемые с помощью HAProxy, один сервер для асинхронных воркеров и один сервер для ETL (Extract, Transform, Load) и Business Intelligence. Для поддержания реплик в актуальном состоянии использовалась потоковая репликация.

Надо было одновременно проапгрейдить Postgres и перейти с i3.8xlarge на i3.16xlarge при минимальной суммарной остановке 4 ч. (а вышло полчаса). Для миграции использовали pglogical. Также в статье из этого опыта извлекли уроки. Эта статья вызвала справедливые и несправедливые замечания в комментариях. Так что примечателен не только сам случай, но и реакция на него, да и тот факт, что перевод статьи появился не где-нибудь, а на хабр-блоге Альфа-Банка (до этого там о базах данных ничего, кажется, не было).

PostgreSQL Scaling Advice For 2021

Каарел Моппел (Kaarel Moppel, Cybertec), чьи статьи регулярно попадают в наши обзоры, дерзнул дать советы тем, кто озабочен будущим масштабированием своих систем. Каарел признаётся, что воодушевился роликом Distributed MySQL Architectures Past, Present, Future Петра Зайцева, основателя и гендира Percona, и приложил (так как, по его, Каарела, словам, MySQL и Postgres суть сводные братья) некоторые выводы Петра к родной PostgreSQL и добавил собственные.

Итого: что даёт обычный Postgres?

• один инстанс PostgreSQL легко выполняет сотни тысяч транзакций в секунду;
• одна нода обычно выполняет десятки тысяч пишущих транзакций в секунду;
• один инстанс Postgres легко справляется с десятками ТБ данных;
• один инстанс на одной ноде даёт буквально пуленепробиваемую надёжность при должной заботе о согласованности данных;
• в причинах сбоев легко разобраться, поэтому данные можно восстановить.

О чём не стоит забывать, когда озаботился масштабированием?

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

Агрегаты в БД

Кирилл Боровиков aka Kilor (компания Тензор) на этот раз обратился к агрегатам. Это мини-серия из двух статей: Агрегаты в БД зачем, как, а стоит ли? и продолжение Агрегаты в БД эффективная обработка потока фактов. В первой движение мысли от count(*) к подсчетам с парсингом EXPLAIN, к сбору агрегатов в отдельную таблицу, к хранению временных агрегатов в памяти процесса и даже к хранению их вообще в другой СУБД.

Во второй статье агрегация обретает бизнес-очертания: пересчет остатков и ведение сводных продаж по товарам при их постоянных отгрузках, или агрегация сальдо и оборотов по бухгалтерским счетам, при массовых изменениях проводок. Цель: агрегаты по продажам в разрезе товар/день/кол-во. Разных товаров очень много и отгрузок много. Решение: конкурентная обработка очередей, чтение из курсора читайте подробности.

Образование

Чёрная Малютка

Вышла новая версия знаменитой книжки-малышки
Postgres: первое знакомство.



Книжечка проапгрейдилась до версии PostgreSQL 13. В бумажном виде она, как и раньше, будет раздаваться на конференциях, которые проходят с участием Postgres Professional. PDF можно скачать.

DEV2: Разработка серверной части приложений PostgreSQL 12. Расширенный курс.

Напоминаем о появившемся курсе. Задача его не просто познакомить с базовыми функциями PostgreSQL, но и научить пользоваться его расширяемостью: дополнять серверные механизмы собственным кодом.

Митапы и подкасты

Постгрес-вторник с Петром Зайцевым

Петра Зайцева, основателя Percona, Николай Самохвалов и Илья Космодемьянский зазывали на свои Вторники целый год. Свершилось. Был разговор о компании (из которого выяснилось, что сейчас в компании около 300 сотрудников, из них человек 50 постгресистов); о причинах дрейфа компании от MySQL и MongoDB в сторону PostgreSQL (не по любви, и не из-за технологических причин, а просто в это сторону двигались клиенты и потенциальные клиенты); о разной атмосфере в комьюнити MySQL, MongoBD и PostgreSQL (второе самое монополистическое, а третье самое открытое). Но гвоздь программы перконовская утилита мониторинга pg_stat_monitor.

Монитор опирается на расширении pg_stat_statements, но добавляет некоторую функциональность. Можно, например, посмотреть тексты запросов, отбирающих много ресурсов, сравнить прожорливость одного и того же запроса с разными планами; монитор знает название приложения, отправившего запрос. В этом контексте возник и разговор о новом расширении PWR (pgpro_pwr), вошедшем в Postgres Pro Standard и Enterprise 13. Это, кажется, обсудят на следующем Вторнике (мы же обещали статью о нём и обещание скоро сдержим).

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

Конференция PGConf.Online 2021

Она начинается уже 1-го марта и закончится 3-го. О ней подробно написано в статье Ивана Панченко, зам. гендира Postgres Professional.

На этой конференции (которая не вместо, а кроме офлайновой, теплой-ламповой, она ожидается в конце весны) будет рекордное число иностранных гостей чему явно поспособствовал онлайн-формат. В том числе на этот раз поучаствует и Саймон Риггс (Simon Riggs). Доклады в 3 потока с 10 утра до 6 вечера. А также мастер-классы.

Статьи

PostgreSQL 14: Часть 4 или январское наступление (Коммитфест 2021-01)

Очередной must read Павла Лузанова. Крупные изменения после первых трех относительно скромных коммитфестов (июльский, сентябрьский, ноябрьский).

Вопросы для затравки, предложенные Павлом:

• Могут ли диапазоны содержать пропуски значений?
• Зачем нужна индексная нотация типу json?
• Может ли индекс при частых обновлениях разрастаться меньше, чем таблица? А вообще не разрастаться?
• Сколько времени простаивали сеансы в idle_in_transaction?
• Как построить ER-диаграмму для таблиц системного каталога?

Deep PostgreSQL Thoughts: The Linux Assassin

Слово deep уже пугает: не про ИИ ли это. Но нет. Джо Конвей (Joe Conway, Crunchy Data) действительно копает вглубь. Даже не Постгреса, не своего же расширения plr. На этот раз тема Жуткий Убийца, являющийся из недр Linux OOM Killer.

Джо начинает с истории: первые дискуссии в Postgres-сообществе и первые патчи в 2003-м году как заставить киллера работать по понятиям Postgres. Далее Джо поясняет отношения киллера и Postgres на уровне хоста (oom_score и oom_score_adj) и на уровне CGroup, поясняет, почему так важно не допустить прихода киллера.

И дальше Джо Конвей переходит к специфическим проблемам OOM Killer в Kubernetes это, видимо, и был главный повод для написания этой статьи. Оказывается, что там, в K8s, разрушительная деятельность киллера может начаться даже при относительно благополучном состоянии памяти. В K8s своп по умолчанию выключен. До этого момента автор не трогал тему свопа, которая, вообще-то, очень даже обсуждается среди постгресистов-практиков.

Джо ссылается на обстоятельную статью Криса Дауна (Chris Down) In defence of swap: common misconceptions, причём есть и русский перевод (не автопереводчиком): В защиту свопа: распространенные заблуждения. О Postgres там нет речи, но может заинтересовать и постгресистов.

Также ссылается он на статью The weird interactions of cgroups and linux page cache in hypervisor environments в блоге компании StorPool, где в команде в основном болгарские фамилии.

Далее Джо Конвей плавно переходит к разработкам и усилиям Crunchy Data в треугольнике PostgreSQL Kubernetes ядро Linux.

??
Акула жуёт гугловый кабель (The Guardian??)

Things I Wished More Developers Knew About Databases

Статья не (только) о Postgres. Иногда полезно ещё разок глянуть на разные СУБД с птичьего полёта. Вот внушительный список тем, о которых стоит помнить разработчикам приложений. В статье Джоанна Доган (Jaana Dogan) не поленилась их разворачивать и развивать. Иногда в неожиданную сторону: в пункте #1 мы, например, узнаём, что гугловские кабели давеча покусали акулы. Немало SQL-примеров, схем и есть матрица PostgreSQL vs. MySQL.

• Если сеть доступна 99.999% времени, вам сильно повезло;
• ACID понимают по-разному;
• у каждой СУБД свои возможности поддержки согласованности и изоляции;
• оптимистические блокировки могут помочь, когда удерживать эксклюзивные блокировки нет возможности;
• есть аномалии кроме грязного чтения и потери данных;
• моя СУБД, в каком порядке хочу исполнять транзакции, в таком и исполняю;
• шардинг на уровне приложения не означает шардинг вне СУБД;
• AUTOINCREMENT может преподнести неприятные сюрпризы;
• устаревшие данные могут быть полезны и помогают обойтись без блокировок;
• рассогласования из-за часов;
• под задержками (latency) могут подразумевать разное;
• надо оценивать производительность не по усредненным показателям, а по критическим операциям/транзакциям;
• вложенные транзакции небезопасны;
• транзакции не должны поддерживать состояния приложений;
• планировщик поможет узнать многое о базе данных;
• миграции без останова сложны, но возможны;
• существенный рост базы данных увеличивает непредсказуемость.

Troubleshooting Performance Issues Due to Disk and RAM

Хамид Ахтар (Hamid Akhtar, HighGo, Китай) написал простенькую, но небесполезную памятку для тех, кто хочет быстро сузить круг подозреваемых при поиске проблем с железом. Начав с совсем очевидных top, free и df, он обращается к утилитам анализа производительности дисков, процессора и памяти, и предлагает полезные наборы их опций:
iostat (информация и о диске, и о процессоре), напр. iostat -dmx sda 1
sar (System Activity Report, часть пакета sysstat), напр. sar -f /var/log/sa/sa03 -b -s 02:00:00 -e 02:30:00 -r -S
dstat, напр. dstat -cdngy

А вот скриптик для анализа памяти:

#!/bin/bashgrep -A3 "MemTotal" /proc/meminfo  grep "Swap" /proc/meminfogrep -A1 "Dirty\|Active" /proc/meminfo

.

Starting with Pg where is the config?

Депеш (Хуберт Любашевски) в короткой заметке напоминает, как можно найти конфигурационные файлы, если они лежат в нестандартном месте. Способы, которыми он предлагает воспользоваться не сенсационны, но может быть полезен, скажем, удобный набор опций.
Например, так:
ps -fxao pid,command | grep -E 'post(gres|master)'
на выходе будет path. И отсюда:
sudo grep -E '(hba|ident)\.conf' <путь к postgresql.conf>
Или теперь танцуем от pid:
sudo cat /proc/<подставляем pid>/environ | tr '\0' '\n' | grep ^PG | sort
Или:
sudo lsof -p <подставляем pid> -a -d cwd
получаем каталог данных и сведения о нём.
Если такие советы не понадобились, можно порефлексировать на тему я бы сделал по-другому. Скажем, просто-напросто используя find, например.

Агрегаты в БД

Кирилл Боровиков aka kilor завершил мини-серию статей про агрегаты:

Зачем, как, а стоит ли?

как сделать отчеты быстрыми, способы их реализации и какие грабли встречаются на этом пути;

Эффективная обработка потока фактов

как обработать БОЛЬШОЕ (капслок kilor-а) количество записей и при этом особо никого не заблокировать, включая набегающий поток данных. Например, это может быть пересчет остатков и ведение сводных продаж по товарам при их постоянных отгрузках, или агрегация сальдо и оборотов по бухгалтерским счетам, при массовых изменениях проводок.

Многомерные суперагрегаты

иерархичные агрегаты в нескольких одновременных разрезах;

Прокси-таблицы

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

Облака

Babelfish: the Elephant in the Room?

Русский перевод названия этой статьи, появившейся на сайте фонда испаноговорящего сообщества FUNDACIN POSTGRESQL звучал бы так: "Вавилонская рыбка или слона-то я и не приметил?" Мы уже упоминали, что идея проекта сверхамбициозная: Bablefish это PostgreSQL, совместимый с SQL Server настолько, что приложения, под него написанные (в том числе с T-SQL и протоколом TDS), будут сразу работать, не зная, что работают с PostgreSQL.

Автор статьи Альваро Эрнандес (lvaro Hernndez Tortosa, OnGres) начинает с рыночной конъюнктуры, чтобы дальше предъявить гамлетовский вопрос, которым авторы Вавилонской Рыбки должны были задаться: форкать или не форкать?

Babelfish пока не может работать как расширение без доработки ядра PostgreSQL. Альваро напоминает, что 25-го января заслуженный и авторитетный в сообществе человек Ян Вик (Jan Wieck) предложил обсудить расширяемость протокола PostgreSQL: сделать такие хуки, которые позволят реализовать протокол SQL Server в виде расширения без изменений в ядре. Но это процесс небыстрый. Заодно решили обсудить и совместимость с MySQL. Но что делать AWS с Bablefish, если сообщество проигнорирует этот путь или интеграция пойдёт ни шатко, ни валко? Вероятней всего, считает Альваро, AWS будет развивать Bablefish как форк (так уже случилось с Aurora), как бы им не хотелось бы обойтись без форка. А если всё же придётся, то AWS это по силам.

Далее Альваро привлекает Дилемму инноватора. И задаёт ещё один интересный вопрос: хотим ли мы (то есть сообщество), чтобы Babelfish стала MariaDB у PostgreSQL?

Персона

Очередной PG-персоной недели стал Александр Сосна, живущий в небольшом городке на Нижнем Рейне и в свободное от работы в credativ время преподающий ИТ-безопасность в Нижнерейнском Университете. Он работает над довольно необычным расширением: pg_snakeoil. Это антивирус специально для PostgreSQL: он ищет вирусы в данных так, чтобы не мешать работе базы, что отнюдь не характерно для обычных антивирусов. Как замечает Александр, за вирусами охотятся не всегда из-за их вредоносности, иногда только потому, что этого требуют нормативные документы.

Релизы

PostgreSQL 13.2

Вышли PostgreSQL 13.2, 12.6, 11.11, 10.16, 9.6.21, 9.5.25 (последний выпуск ветки 9.5). В этих релизах одолели две проблемы безопасности:
в PostgreSQL 13 можно было, имея права на SELCT одного столбца, получить при помощи изощрённого запроса все столбцы таблицы;
вторая проблема касалась версий 11, 12 и 13. Если у пользователя есть права на UPDATE партицированной таблицы, но нет прав на SELECT некоторого столбца, он мог получить данные столбца из сообщений об ошибке.
Кроме того исправлено более 80 багов.

pg_probackup 2.4.9

Появился флаг --force для инкрементального режима. Теперь можно переписывать содержимое в каталоге, указанном в PGDATA, если system-identifier в целевом экземпляре и копии НЕ совпадают (раньше приходило сообщение об ошибке).


pgAdmin 4 v. 5.0

В версии 5.0 среди прочего появилась поддержка логической репликации; поддержка публикаций и подписок в Schema Diff.

Apache AGE 0.3.0

Apache AGE это расширение, добавляющее в PostgreSQL функциональность графовой базы данных. Цель проекта единое хранилище для реляционной и графовой моделей данных, чтобы пользователи могли использовать и стандартный SQL, и языки запросов к графовым базам openCypher и GQL.

Мы продолжаем знакомить вас с самыми интересными новостями PostgreSQL. Этот выпуск получился с некоторым уклоном в средства диагностики. Нет, не только. Например:

Хардверные ускорители: FPGA

В небольшом сообщении Энди Эликотта (Andy Ellicott) в блоге Swarm64 3 hardware acceleration options Postgres users should know in 2020 рассказывается о трёх аппаратных ускорителях, не GPU, а FRGA, и все они в облаках. У автора свой интерес: у Swarm64 есть собственное решение на FPGA-ускорителе. Значимым сигналом он считает объявление Amazon об FPGA-ускорителе кэша (FPGA-powered caching layer) в Redshift AQUA (Advanced Query Accelerator) в Amazon, который убыстряет запросы на порядок. А вообще уже почти все облака (во всяком случае Amazon, Alibaba, и Azure) используют сейчас FPGA-ускорители, просвещает нас Энди.

Итак:

Swarm64 Data Accelerator (DA)
это расширение, которое умеет переписывать обычные SQL-запросы, чтобы распараллеливать вычисления на всех этапах их исполнения, а сотни читающих или пишущих процессов будут работать параллельно на FPGA. Кроме того, там реализованы индексы columnstore, как в MS SQL Server. Есть техническое описание в PDF, но именно про FPGA в нём ничего нет. Зато есть демонстрационное видео, показывающее, как можно легко и быстро развернуть Postgres на инстансе Amazon EC2 F1 с FPGA. Ещё есть результаты тестов TPC-H (а позиционируется эта комбинация с FPGA прежде всего как ускоритель для гибридных транзакционно-аналитических нагрузок HTAP), и там показывает выигрыш в 50 раз по скорости.

Другой вариант, который предлагает Энди: Intel Arria 10 GX FPGA в связке с NVM-памятью Intel Optane DC, SSD и PostgreSQL 11 с тем же расширением Swarm64 DA. Всё это собрано в демо, которое вбрасывает в PostgreSQL потоки биржевых котировок со скоростью 200 тыс инсертов в секунду, и дальше работает с ними с обычным SQL.

Третий вариант с Samsung SmartSSD, в которой внутри FPGA-чип от Xilinx. Испытания (с тем же свормовским расширением, как можно догадаться) дали выигрыш в 40 раз на TPC-H и в 10-15 раз на JOIN-ах.

С маркетинговой точки зрения эти усилия нацелены прежде всего против хардверных решений для WH вроде Netezza или Teradata.

Обещано, что будет и сравнение эффективности FPGA vs. GPU (в т. ч. и в контексте проекта PGStrom).

(спасибо Александру Смолину за наводку в FB-группе PostgreSQL в России)

Конференции

были:

PGConf.online

Теперь выложены все видео и презентации доступ через расписание.

FOSDEM 21

Поток PostgreSQL devroom тёк два дня 6-7 февраля с 10 утра до 6 вечера. Материалов конференции очень много. Вот имеется однобокая, зато систематизированная выборка доклады от Postgres Professional (глаголы будущего времени там надо поменять в уме на глаголы прошедшего).

будет:

Highload++

Объявлено, что состоится офлайн 17 -18 мая 2021 в Крокус-Экспо, Москва. Есть Расписание. Я бы обратил особое внимание на потоки
СУБД и системы хранения, тестирование в Зале 3, например:
Микросервисы с нуля, Семен Катаев (Авито);
Прокрустово ложе или испанский сапог мифы и реальность СУБД в Облаках, Александр Зайцев (Altinity)
и на
Архитектуры, масштабируемость, безопасность в Зал 4 (главном), например:
Архитектура процессора Эльбрус 2000, Дмитрий Завалишин (Digital Zone);
SQL/JSON в PostgreSQL: настоящее и будущее, Олег Бартунов (Postgres Professional);
Распространённые ошибки изменения схемы базы данных PostgreSQL, Николай Самохвалов (Postgres.ai).

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

RuPostgres-вторник s02e13 Андрей Зубков (PostgresPro) pg_profile, pgpro_pwr

Вторник, посвященный pg_profile / PWR, так заинтересовал устроителей, что с большой вероятностью в ближайшее время можно ожидать продолжения: разобрались не во всех тонкостях работы этого весьма практичного инструмента, ну а расширения pgpro_stats, которое используется в PWR, коснулись по касательной.

После это был ещё вторник с Александром Кукушкиным (Zalando). Тема риски апгрейда мажорных версий с фокусом на PG12 и PG13, а пособник апгрейда Spilo: как выяснилось, бесшовный апгрейд в контексте Patroni задача слишком амбициозная, а вот Spilo, то есть Docker-образ с PostgreSQL и Patroni, с задачей справляется. Но опасностей и нюансов при апгрейде остаётся немало. Говорилось о сюрпризах от VACUUM, ANALYZE, о параллелизме по умолчанию, о CTE и материализации, о JIT.

Database Delivery: The Big Problem

Это была презентация от Ростелеком-ИТ, которую провёл Роман Гордеев (в видео глюки, надо прокрутить первые 11 минут). Его пригласили на один из стримов Tver.io сообщества тверских айтишников (но мне удобней было смотреть этот же ролик на на youtube). Речь шла об инкрементальной стратегии миграции. Роман рассказывал о вещах, применимых к разным СУБД и средам разработки, но для примера был выбран переход с базы PostgreSQL на H2 в графическом DataGrip. Соответственно в реальном времени наблюдались и решались проблемы с постгресовым типом text и с последовательностями.

В качестве механизма, который контролирует миграцию, был взят плагин liquibase для среды gradle. О настройках для такой работы можно почитать на страничке liquibase gradle на гитхабе Гордеева. Кстати, Ростелеком Информационные Технологии компания с населением под 2 тыс. человек. На официальной странице есть информация об опенсорсной СУБД in-memory Reindexer собственной разработки. Больше о базах там ничего пока найти не удалось.

Обучение

Выложены в общий доступ видео:

• Разработка серверной части приложений PostgreSQL 12. Базовый курс.
• Разработка серверной части приложений PostgreSQL 12. Расширенный курс

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

Тем, кто интересуется более пристально, советую прослушать доклад о курсах Егора Рогова на PGConf.online 2021.

Мониторинг

Monitoring PostgreSQL with Nagios and Checkmk

Пишет опять Хамид Ахтар (Hamid Akhtar, китайская компания High Go), на этот раз пишет о средствах мониторинга Nagios (рекурсивный акроним Nagios Ain't Gonna Insist On Sainthood Nagios не собирается настаивать на святости, в отличие от его предшественника NetSaint) и Checkmk. Публикация без претензий: как установить и настроить, не претендуя даже в этом на полноту.

Explaining Your Postgres Query Performance

Идём от простого к сложному. Пока URL подсказывает возможный подзаголовок статьи: Get Started with EXPLAIN ANALYZE. Кэт Бэтьюйгас (Kat Batuigas, Crunchy Data) действительно знакомит с самыми азами EXPLAIN, даже без опций. Жанр For dummies, и наглядно: показывает, как с помощью EXPLAIN ANALYZE можно наблюдать решения планировщика об (не)использовании индексов, и вообще что там происходит. Иллюстрируется это всё на базе Geonames.

Предыдущая её статья была о Query Optimization in Postgres with pg_stat_statements.

Вот ещё одна её статья: Three Easy Things To Remember About Postgres Indexes. В ней не только напоминания о том, что индекс занимает место на диске, но и, например, такие соображения:
Важен и тип запроса. Например, если в запросе есть знаки подстановки (wildcards)
wildcards, e.g. WHERE name LIKE 'Ma%',
то планировщик задействует по умолчанию индекс B-tree, но вам, возможно, стоит указать класс оператора, чтобы был выбран эффективный индекс.

Can auto_explain (with timing) Have Low Overhead?

Михаэль Христофидес (Michael Christofides) показывает работу расширения auto_explain с включённым и отключённым таймингом. Выводы:

Если задать ощутимый промежуток времени min_duration, издержки от auto_explain на небольшой транзакционной нагрузке )была меньше 1% с отключённым таймингом и ~2% с включённым. Семплинга не было, поэтому детали прослеживались для каждого запроса, но попадали в лог для медленных. А когда min_duration=0ms, и логировалось всё, издержки оказались больше 25%, даже без тайминга и ANALYZE. Видимо, издержки auto_explain связаны в основном с логированием.

Интерес у Михаэля не невинный он разработчик утилиты pgMustard, которая визуализирует планы. Она также расписывает, сколько тратится времени и сколько строк возвращает каждая операция (в т.ч. циклы; дочерние узлы планов subplans; CTE). Мало того, pgMustard умеет подсказывать. Например:

• (не)эффективность индексов;
• плохая оценка числа строк;
• неэффективность кэша;
• угроза распухания индекса (bloat);
• CTE-скан использовался только 1 раз.

How to create a system information function in PostgreSQL

Давид Ян (David Zhang, старший системный архитектор в той же High Go) делится опытом написания собственных информационных функций. Ему мало тех, что можно найти на вот этой странице. Например, его не устраивает, что txid_current() возвращает ему тот же идентификатор транзакции, что и было до SAVEPOINT.

Ссылаясь на страничку Исходные данные системных каталогов, Давид показывает, как выбрать OID для новой функции, чтобы он не конфликтовал с существующими. Потом приводит код своей функции, определяющей xtid после SAVEPOINT. Называется она txid_current_snapshot и написана на C. И тестирует её. Теперь идентификатор транзакции показывается корректно.

How The PostgreSQL Optimizer Works

Ханс-Юрген Шёниг (Hans-Jrgen Schnig, Crunchy Data) написал не то, чтобы концептуальную, но большую по объёму статью, в которой есть примеры, демонстрирующие:

обработку констант: почему

WHERE x = 7 + 1

для оптимизатора не то же, что

WHERE x - 1 = 7

встраивание функций (function inlining): умение оптимизатора встраивать функции зависит от языка, в SQL он как дома, но не в PL-ях.

как обрабатываются функции, если они VOLATILE/STABLE/IMMUTABLE. Например:

WHERE x = clock_timestamp()

против

WHERE x = now()

что способен понять PostgreSQL, задумавшись о том, что чему равно:
понять, что если x = y AND y = 4, то x = 4, а значит можно использовать индекс по x это он может.

что такое view inlining и subselect flattening:
как представление превращается во вложенные SELECT-ы, а они в обычный, плоский SELECT.

Ну и, конечно, центральный вопрос как оптимизатор расправляется с JOIN. Тут Ханс-Юрген рассказывает об очерёдности джойнов, о явных и неявных; об OUTER JOIN; автоматическом исключении (pruning) ненужных; об EXIST и анти-джойнах.

Случайности:

Они не случайны

Кирилл Боровиков ака kilor выступил в роли волшебника: он угадывает случайные числа! Он придумал волшебную функцию и даже назвал её magic(). В качестве аргумента она берёт только что сгенерённое функцией random() число и предсказывает следующее:

SELECT r random, magic(r) random_next FROM random() r;       random       |    random_next--------------------+-------------------- 0.3921143477755571 | 0.6377947747296489tst=# SELECT r random, magic(r) random_next FROM random() r;       random       |    random_next--------------------+-------------------- 0.6377947747296489 | 0.5727554063674667

Чтобы исследовать содержание внутренностей волшебной функции, автор предлагает разобраться в линейном конгруэнтном алгоритме, который используется в random(), залезает в код функции setseed() в файле float.c и там находит источник вдохновения для создания своей волшебной функции.

Итого, случайные числа random() не слишком случайны, о криптографии и речи не может быть. Но кое-какие альтернативы имеются: более безопасны функции в расширении pgcrypto.

Восстановление

Speeding up recovery & VACUUM in Postgres 14

Статья на сайте Citus, но речь не о Citus, а о патче в основную ветку PostgreSQL. Написана статья (и патч) Дэвидом Роули (David Rowley), работавшим над этим уже внутри Microsoft. Он переписал внутреннюю функцию compactify_tuples, которая используется, когда PostgreSQL стартует после внештатного (нечистого) шатдауна (crash recovery), и когда идёт восстановление standby-сервера проигрыванием WAL по их прибытии с primary-сервера; VACUUM.

Эти случаи Дэвид и расписывает, поясняя схемами. Новая версия функции избавляет от ненужной внутренней сортировки кортежей в heap, поэтому и работает быстрее. На pgbench выигрыш в 2.4 раза на восстановлении и на 25% при вакууме.

Соревнования

Performance differences between Postgres and MySQL

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

• надо работать со сложно устроенными или объёмистыми данными;
• аналитические нагрузки;
• нужна транзакционная база общего назначения;
• требуется работа с геоданными.

А на этот раз решили померить, причём с уклоном в JSON, поскольку эта тема интересует в сообществе очень многих и очень сильно. Вот что было сделано:
создан проект, в котором использовались PostgreSQL и MySQL;
создали объект JSON для тестирования чтения и записи, размер объекта около14 МБ, около 200210 записей в базе данных.

И опять приятный вывод:
JSON-запросы быстрей в Postgres!

Кроме этого автор по касательной упоминает индексы по выражениям и прочие, особенности репликации, принципиальные отличия MVCC в InnoDB MySQL и в PostgreSQL.

PostGIS

Traveling Salesman Problem With PostGIS And pgRouting

У Флориана Надлера (Florian Nadler, Cybertec) проблемный коммивояжер странствует по окрестностям Гамбурга. Это продолжение статьи 'Catchment Areas' With PostgreSQL And PostGIS. Там собрали множества городов, ближайших к крупным аэропортам, разбросав их по диаграммам Вороного.

Теперь, надо решить, как лучше эти города обойти, для чего кроме PostGIS Флориан использует функции расширения pgRouting. Чтобы превратить множество точек в граф, он выбирает утилиту osm2po.

Дальше pgr_createverticestable функция из pgRouting превратит граф в таблицу. Эта таблица-граф накладывается как слой поверх слоёв OpenStreetMap. После этого Флориан, используя функцию pgr_dijkstraCostMatrix из pgRouting, решает эту знаменитую задачу оптимизации с помощью замысловатого запроса с CTE, учитывая стоимости/веса, присвоенные ещё osm2po.

Performance Improvements in GEOS

GEOS важнейшая для геовычислений библиотека (алгоритмы портированы на C из Java Topology Suite или JTS). Crunchy Data вкладывают в её развитие не меньше сил, чем в саму PostGIS.

Пол Рамси ( Paul Ramsey) рассказывает не просто о тестах производительности GEOS (довольно специфических), а взглядом историка GEOS иллюстрирует ими хронологию улучшений в этой библиотеке от релиза 3.6 к свежайшему 3.9. Вообще-то, о GEOS 3.9 Пол говорил и раньше в начале декабря в блоге Crunchy Data Waiting for PostGIS 3.1: GEOS 3.9 и в собственном. Там тоже есть роскошные иллюстрации, но нет графиков производительности.

А вот заметку Пола Рамси Dumping a ByteA with psql можно увидеть только в его блоге. Она короткая, но может оказаться полезной тем, кто:

• хранит двоичные файлы в столбцах базы, например изображения-ярлычки (thumbnails);
• хочет сформировать на выходе двоичный файл изображения, песни, protobuf или LIDAR-файл;
• использует двоичный формат для транзита двух разных типов.

Хранить в двоичном виде картинку можно, а вот посмотреть нельзя нужен файл. Вот скриптик, который берёт из базы ярлычок в типе bytea, через psql двоичное значение обертывается функцией encode и выгружается как обычное текстовое. Вывод psql перенаправляется в утилиту xxd, которая декодирует входной поток (ключ -r) обратно в двоичный вид и записывает в файл .png:

echo "SELECT encode(thumbnail, 'hex') FROM persons WHERE id = 12345" \  | psql --quiet --tuples-only -d dbname \  | xxd -r -p \  > thumbnail.png

Такой способ будет работать для любого поля bytea.

Активная жизнь в коммьюнити

How many engineers does it take to make subscripting work?

Дмитрий Долгов (Zalando) пишет в своём личном блоге в смешанном жанре: о довольно сложных и специальных технических проблемах самого патча, но и извлекает из истории их решения этакую мораль для писателей патчей. Таких, чтобы сообщество PostgreSQL было в состоянии принять их за время меньше бесконечности.

Патч добавляет subscripting в синтаксис функций JSONB то есть как у массивов, например:

SET jsonb_column['key'] = '"value"';

вместо

SET jsonb_column = jsonb_set(jsonb_column, '{"key"}', '"value"');

Началась история этого патча в 2015 году с беседы Дмитрия с Олегом Бартуновым и последовавшего простенького патча Долгова. Сообщество отнеслось к патчу сочувственно, но предложило переписать его в более универсальной манере, чтобы подобную функциональность можно было бы использовать и для других типов данных. Соответствующий патч Дмитрия был непрост, и ревюеры не торопились его разобрать и оценить. Ещё в истории этого патча фигурируют Том Лейн (Tom Lane), закоммитивший финальный патч Александр Коротков, Павел Стехуле (Pavel Stehule) и Никита Глухов.

Затрагиваются темы универсальности и, пожалуй, лучшее враг хорошего. Патч продвигался настолько вяло, что Дмитрий в отчаянии даже написал в комментарии к патчу: если вы дочитали до этой фразы и напишете рьвю, я выставлю бутылку пива на следующей PGConfEU (о судьбе бутылки из статьи мы не узнали).

В финале статьи 8 советов. Вот некоторые из них в моём вольном переводе, начиная с последнего Last but not least:

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

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

Разбейте патч на несколько частей это всегда облегчает работу ревьюеров.

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

Облака и контейнеры

Running Postgres In Docker Why And How?

Каарел Моппел (Kaarel Moppel, Cybertec) задаёт себе вопрос можно и нужно ли использовать PostgreSQL в Docker в качестве продакшн, будет ли он вообще там работать? и отвечает: да, работать будет, если сильно постараться, и если для фана или для тестирования.

В статье несколько разделов, но начнём с предпоследнего Капли дёгтя в бочку мёда.

Докер-имиджи да и вся концепция контейнеров оптимизированы под моментальное разворачивание в стиле стартапов . По умолчанию там даже данные не разведены как следует по томам (persistent units). Если этого не сделать, затея может закончится катастрофой.

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

У вас будет относительно лёгкая жизнь только в том случае, если вы используете такой всеобъемлющий фреймворк, как Kubernetes плюс выбираете оператор (скорее всего от Zalando или Crunchy).

Поведение

The PostgreSQL Community Code of Conduct Committee Annual Report for 2020

Этот документ сообщества переводили на русский Анастасия Лубенникова, Александр Лахин и Анастасия Распопина (все из Postgres Professional), также участвовали Виктор Егоров и Валерия Каплан. Ещё он переведён с английского на японский и иврит.

Число жалоб увеличилось в 2020: 18 против 12 в прошлом году. Мужчины жалуются чаще: 15/3. Обычно от страны по жалобе. По 2 только от РФ, Аргентины, UK и US.

Надеемся, что вы хорошо отдохнули и попраздновали. А мы предлагаем вам очередную сводку Postgres-новостей.

PostgreSQL 14 Beta 1

Релизная группа в составе Пит Гейган (Pete Geoghegan, Crunchy Data), Мишель Пакье (Michael Paquier, VMWare) и Эндрю Данстан (Andrew Dunstan, EDB) предлагают опубликовать бету 20-го мая, как это и происходило с предыдущими бетами.

Commitfest afterparty

PostgreSQL 14: Часть 5 или весенние заморозки (Коммитфест 2021-03)

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

Вот авторский тизер:

• Может ли один запрос параллельно выполняться на разных серверах?
• Как найти запрос из pg_stat_activity в pg_stat_statements?
• Можно ли добавлять и удалять секции секционированной таблицы не останавливая приложение?
• Как пустить разработчиков на прод чтобы они могли всё видеть, но ничего не могли изменить?
• Почему VACUUM после COPY FREEZE заново переписывает всю таблицу и что с этим делать?
• Можно ли сжимать TOAST чем-то кроме медленного zlib?
• Как понять сколько времени длится блокировка найденная в pg_locks?
• Для чего нужны CYCLE и SEARCH рекурсивному запросу?
• Текст функций на каких языках (кроме C) не интерпретируется при вызове?

Миграция

CHAR(1) to Boolean transformation while migrating to PostgreSQL

В Oracle нет типа boolean, а в PostgreSQL есть. Но почему бы не использовать этот тип, если в исходной оракловой базе есть столбец boolean, который хранится там в виде CHAR(1) с ограничением CHECK? Можно. Но хотелось бы ещё получить гарантию, что значения, отличные от резрешенных для Postgres не остановят работу приложения, а будут должным образом обработаны. Для этого можно создать CAST:

CREATE CAST (char as bool) WITH FUNCTION char_to_bool(char);

Далее автор Дилип Кумар (Dileep Kumar, MigOps) показывает изменение поведения при определении CAST как IMPLICIT, а потом прогоняет запрос (обычный SELECT) на тестах, чтобы увидеть разницу CHAR(1) vs Explicit Casting vs Implicit Casting vs Boolean. Побеждает, как и ожидалось, Boolean.

Choice of Table Column Types and Order When Migrating to PostgreSQL

В статье Стивена Фроста (Stephen Frost) с участием его коллеги по Crunchy Data Дэвида Юатта (David Youatt) тоже говорится о том, какой тип выбрать в PostgreSQL при миграции, но ещё и о том, в каком порядке располагать столбцы, чтобы данные выбранных типов хранились максимально эффективно. Сначала самые широкие поля с фиксированной шириной, затем менее широкие с фиксированной и только потом поля переменной ширины иначе появятся дыры в данных. Стивен рассказывает и про неприятные сюрпризы с выравниванием, которые можно получить, излишне рьяно экспериментируя с типами PostgreSQL. Ещё совет: выбирайте NUMERIC или DECIMAL только тогда, когда необходимо (считая деньги, например), а если нет, то обходитесь NTEGER, BIGINT, REAL, DOUBLE PRECISION это проще и эффективней.

Масштабирование

Lessons Learned From 5 Years of Scaling PostgreSQL

Джо Уилм (Joe Wilm) обобщает опыт использования PostgreSQL в компании OneSignal. Система доросла за 5 лет до 75 ТБ на 40 серверах. Понятно, что не все технические решения были приняты сразу на вырост. Как решают проблемы масштабирования, и как их можно было избежать об этом и рассказывает автор. Для удобства он разбил статью по разделам (сознательно не перевожу, слишком много английских слов пришлось бы писать кириллицей):
Bloat таблиц и индексов. Коротко о (хорошо известных) причинах распухания. pg_repack справлялся так себе (см. причины), написали собственный демон, координирующий его работу. Перешли к pgcompacttable там, где pg_repack обваливает производительность (перешли не везде, pgcompacttable работает надёжней, но медленней). Есть и об уловках по ситуации: в системе были таблицы, в которых большие поля (около 1 КБ) в личных данных, и поле last_seen_time int, которое часто обновлялось. Их разнесли по разным таблицам: одним JOIN больше, зато не копятся килобайты при обновлении строки.
Database upgrade. Мажорные и минорные. С мажорными справлялись при помощи логической репликации pglogical. При минорых просто перестартовывали postgres.
Wraparound. Серьёзная проблема для таких нагрузок. Остановились на оповещениях при приближении к 250 млн оставшихся XID. Напомним, конечно, что в Postgres Pro Enterprise 64-битные XID.
Replica Promotion. Для этого обходятся средствами haproxy. Упоминается только Patroni, но и то в контексте мы не используем, но может и стоило. Для каждой логической базы данных есть два бэкенда: один read-write, другой read-only. Переключение занимает пару секунд.
Partitioning и Sharding. Важнейшая штука для такой базы, конечно. Сначала порезали на 16 секций, потом на 256, а в ближайших планах 4096. Резали на куски выбирая в качестве критерия разбиения id пользователей системы. Сейчас думают над созданием data proxy слое, который будет знать, как разрезаны данные и где лежат, и действовать соответственно. Чтобы приложениям этого не требовалось знать для нормальной работы. Сетуют, что не сделали так с самого начала.

Самокритика

Чего энтерпрайзу в PostgreSQL не хватает

Вот чего ему не хватает в порядке важности (по Кириллу Боровикову, автору статьи)

• легковесного менеджера соединений (он же built-in connection pooler);
• 64-bit XID;
• микротаблиц (речь о том, что у каждой таблицы и индекса в PostgreSQL есть 3 форка файла, но почему бы не обойтись 1 файлом (heap) для мелких справочных табличек?);
• zheap;
• append-only storage (а в идеале, считает Кирилл хотелось иметь возможность назначать часть полей индексов или целых таблиц как no-MVCC чтобы иногда экономить на полях поддержки MVCC);
• отложенная индексация (чтобы сервер мог размазать необходимые операции во времени для балансировки нагрузки эта тема особенно важна для конкуренции с поисковыми системами, где основная задача найти вообще, а не найти прямо сразу сейчас);
• columnar storage (в идеале в ядре или в contrib);
• in-memory storage (очень быстрого нетранзакционного хранилища без сброса на диск);
• не пухнущих TEMPORARY TABLE, в том числе на репликах;
• multimaster из коробки;
• SQL-defined index (уметь описывать новые виды индексов прямо на SQL/PLpgSQL);
• мониторинга производительности запросов (здесь Кирилл предлагает глянуть, как это визуализируется на родном explain.tensor.ru);
• снапшотов статистики таблиц (как в pg_profile [а тем более в pgpro_pwr примечание редакции]).

К ЭТОМУ ДОБАВИЛИСЬ ХОТЕЛКИ ИЗ КОММЕНТАРИЕВ:

• IS NOT DISTINCT FROM при индексации;
• failover из коробки (аналогично Always on у MS SQL) без Patroni и сопутствующих;
• Asynchronous IO и Direct IO;
• бесшовного обновления мажорной версии;
• flashback queries;
• edition-based redefinition;
• нормальной компрессии.

Некоторые из этих хотелок на пути к дальнейшим версиям, некоторые уже есть в Postgres Pro Enterprise (о чём не умалчивает и автор).


Видео-вторник s02e15: Десять проблем PostgreSQL. Мониторинг запросов, pg_profile

(это продолжение вторника ) с Андреем Зубковым)

Статья Рика Брэнсона: (Rick Branson) 10 things I Hate In Postgres внезапно попала в топ обсуждаемых. Вот её не миновали и устроители ruPostgres.Вторников Николай Самохвалов и Илья Космодемьянский.

О ней мы писали в Postgreso 20. На ruPostgres.вторнике s02e15 6-го апреля самые жаркие вопросы возникали, как всегда, вокруг MVCC и VACUUM, переполнения 32-битных счётчиков XID.

На 50-й минуте обсуждения 10 ненавистных вещей Андрей Зубков продолжил рассказал о pg_profile (до pgpro_pwr речь опять не дошла, говорили даже о том, чтобы наверстать в 3-й серии) и о своём патче pg_stat_statements: Track statement entry timestamp (ровно 1:00 записи).

Вторник 20-го апреля назывался Как поменять тип колонки в таблице PostgreSQL с 1 млрд строк без даунтайма?. Два разных варианта решения на уровне колонки и на уровне таблицы.

А совсем недавний 4-го мая о разном, например, о WAL-G vs. pgBackRest, об амазоновских инстансах на ARM, о которых чуть ниже. Список тем лежит в файле.

Облака и контейнеры

Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances

Александр Коротков в своём блоге пишет об опыте работы с новейшими облаками инстансы Graviton2 работают на амазоновских ARM-процессорах. Но следующие за модой расплачиваются некоторыми сложностями у ARM есть специфика (по мнению Александра работа с ними скорее напоминает работу с IBM Power).

Команды LSE (Large System Extensions), доступные с версии 8.1, действительно ускоряют работу. Вот здесь это разъясняют с некоторыми подробностями, испытывая MySQL на включенных и отключенных LSE. Александр же получил колоссальный выигрыш на pgbench, скомпилировав PostgreSQL 14 с поддержкой LSE. Но это касается только амазоновских ARM AWR Graviton2. Apple M1 не удалось оптимизировать (возможно, в этих процессорах есть какая-то внутренняя оптимизация), а на китайских Kunpeng 920 результаты даже ухудшились.

Что делать

Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL

Кит Фиске (Keith Fiske, Crunchy Data) регулярно пишет в своём собственном блоге Keith's Ramblings о вакууме, распухших индексах и других важнейших для вдумчивого постгресиста вещах.

В этой статье есть конкретные SQL-запросы, использующие autovacuum_freeze_max_age для получения внятной информации о происходящем с конкретными таблицами, так как vacuumdb --all --freeze --jobs=2 --echo --analyze всего кластера баз данных во многих случаях слишком радикальная мера. Если недовакуумированных таблиц очень много, то Кит советует вакуумировать в батчах не больше сотни в каждом. Сам он предпочитает держать max XID < to 50% autovacuum_freeze_max_age, лучше 30-40%.

Он написал статью и о настройке автовакуума: Per-Table Autovacuum Tuning. Но даже аккуратно настроив автовакуум, стоит с не меньшей аккуратностью мониторить ситуацию. Риск не велик, но ставка высока, как говорили наши деды.

Не удержусь от перечисления собственных проектов Кита (или с его существенным участием):
pg_partman расширение с автоматической поддержкой секционирования по времени и serial id;
pg_extractor продвинутый фильтр дампа;
pg_bloat_check скрипт для мониторинга таблиц и индексов;
mimeo расширение PostgreSQL для потабличной логической репликации;
pg_jobmon расширение для логирования и мониторинга автономных функций.

Postgres is Out of Disk and How to Recover: The Dos and Don'ts

Статья Элизабет Кристинсен (Elizabeth Christensen) с участием Дэвида Кристинсена (David Christensen), Джонатана Каца (Jonathan Katz) и Стивена Фроста (Stephen Frost) все из Crunchy Data. Почему забился диск, что НЕ делать, и что делать.
Возможные причины:

• отказала archive_command и WAL начал заполнять диск;
• остались слоты репликации у стендбая, а реплика стала недоступна: опять же WAL заполняет диск;
• изменения в базе настолько большие, что генерящийся WAL съедает всё доступное дисковое пространство;
• просто-напросто данных было слишком много, а средства мониторинга и предупреждения не сработали.

Что НЕЛЬЗЯ делать:
удалять WAL-файлы нельзя категорически;

• не дайте переписать существующие данные, восстанавливаясь из бэкапа;
• Никакого resize.

Что надо делать:

• сделайте сразу бэкап на уровне файловой системы;
• создайте новый инстанс (или хотя бы новый том) с достаточным местом, убедитесь, что Postgres остановлен и сделайте бэкап директории данных PostgreSQL (обязательно директории pg_wal и недефолтные табличные пространства), чтобы вам было куда вернуться, если понадобится;
• когда база данных заработала, просмотрите логи, разберитесь, из-за чего возникли проблемы и почините поломки, если это возможно.

В статье рассказывается, как архивируется WAL, об попорченных архивах, кое-что о pgBackRest, а ещё предлагается почитать How to Recover When PostgreSQL is Missing a WAL File.

Кстати, о WAL. Если нужно порекомендовать хорошую статью англоязычным коллегам, то в блоге Postgre Pofessional опубликован перевод 3-й части серии Егора Рогова о WAL: WAL in PostgreSQL: 3. Checkpoint. Оригинал её здесь, en-начало-серии здесь, а ru-начало здесь.

Из блога БРЮСА МОМДЖАНА

(то есть отсюда)

Jsonb Multi-Column Type Casting

Брюс делится радостью, что есть jsonb_to_record() и можно без всяких двойных двоеточий сразу сказать:

SELECT a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER);

(А ведь добавим от себя есть ещё и jsonb_to_recordset(jsonb)).

Брюс обращает внимание на устройство таких запросов. Если сказать

SELECT x.a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER)WHERE b <= 4;

то это будет работать, ведь b уже integer потому, что запрос уже создал табличку x с областью видимости только внутри запроса, где типы уже преобразованы. Немногословный (как обычно в своём блоге) Брюс предлагает ознакомиться с деталями в тредах json_to_record Example и Abnormal JSON query performance.

Oracle vs. PostgreSQL

Брюс решил оценить функциональную полноту обеих СУБД в %, в ответ на чьё-то сравнение Postgres и Oracle это как резиновая уточка против танкера водоизмещением 300 тыс. тонн. Он считает:
Более реалистичной была бы оценка в 80-90%, в зависимости от того, какая функциональность для вас важней. Но можно бы поговорить и том, что в Postgres есть, а в Oracle нет. С точки зрения админа получится, может быть, и меньше 80%, а вот с точки зрения разработчика в Oracle нет многого, и оценка перевалит за 100%.

Challenging Assumptions

Следующие, некогда справедливые допущения теперь сомнительны:

• платный софт всегда лучше бесплатного;
• открытый код не столь безопасен, так как слабые места видны;
• серьёзные люди софт с открытым кодом не разрабатывают;
• Oracle лучшая СУБД;
• со знанием Oracle без работы я не останусь;

Кто закрывает дыры и латает щели (в оригинале Database Software Bundles)

Проект Postgres дал миру великолепную, полнофункциональную СУБД. Но когда пользователь думает о бэкапе, мониторинге, высокой доступности, ему приходится смотреть на сторону, так как возможности Postgres могут не совпадать с его потребностями. Иногда бреши закрывают проекты с открытым кодом, но в других случаях решают проблемы коммерческие Postgres-компании: Cybertec, edb, HighGo, Ongres, Postgres Pro, sra oss и другие, которые поставляют сервисы последней мили для корпоративных решений.

Также можно заглянуть в

Shared Memory Sizing
или, скажем, в
Replica Scaling by the Numbers

ИИ

Regression Analysis in PostgreSQL with Tensorflow

Дейв Пейдж (Dave Page, вице-президент и главный архитектор EDB) продолжает серию, посвященную ИИ и статистическим методам анализа данных. Из последнего: вышли две статьи посвященные регрессионному анализу, который ускоряют с помощью Tensorflow. В приведенных примерах можно увидеть много ласкающих слух питониста слов: pandas, numpy, matplotlib и seaborn. Подчеркнём, что используется расширение PostgreSQL plpython3u, а не просто внешние по отношению к базе библиотеки.

Во второй части дело доходит до пред-обработки данных. Используется популярный у педагогов машинного обучения набор данных Boston Housing Dataset по ним тренируются угадывать цену дома в Бостоне в зависимости от некоторых факторов. Из набора выкидывают значения, сильно отличающиеся от общей массы, чтобы не запутать нейронную сеть при обучении. Ещё смотрят распределения и строят корреляции. Третья статья ещё не вышла. Обещано, что в ней уже воспользуются достижениями 2-й части, чтобы обучать нейронную сеть регрессионному анализу.

Релизы

Kubegres

Обычно в разговоре о PostgreSQL в Kubernetes на третьей фразе появляются операторы от Crunchy Data и Zalando. Kubegres, возможно, вклинится в разговор. Разработчик Алекс Арика (Alex Arica, Reactive Tech Limited). Создавался Kubegres на базе фреймворка Kubebuilder version 3 (SDK для разработки Kubernetes APIs с использованием CRD. Можно забрать отсюда.

KuiBaDB

KuiBaDB это Postgres для OLAP, переписанный с Rust и многопоточностью. У этой СУБД есть только базовая функциональность. Она, например, поддерживает транзакции, но не вложенные транзакции. KuiBaDB создан для разработчиков, чтобы они могли быстренько проверить на ней свои идеи. В ней есть векторный движок и колоночное хранение, она опирается на каталоги (catalog-driven).

pgBackRest 2.33

Появилась поддержка нескольких репозиториев данные и WAL можно копировать сразу в несколько хранилищ.
pgBackRest поддерживает теперь GCS Google Cloud Storage.
Отныне можно задать путь вручную с ./configure --with-configdir. Стало удобней работать с не-Linux ОС, например с FreeBSD.
Появилось логирование в процессе бэкапа.

pg_probackup 2.4.15

В новой версии pg_probackup при бэкапе в инкрементальном режиме автоматически обнаруживается переключение таймлайнов, за счёт использования команды TIMELINE_HISTORY протокола репликации (предложил Алексей Игнатов).

При операциях merge и retention merge теперь тоже можно использовать флаги --no-validate и --no-sync.

pgmetrics 1.11.0

pgmetrics утилита с открытым кодом для сбора статистики работающего PostgreSQL, распространяемая в виде единого бинарного файла без внешних зависимостей. Разработчик RapidLoop, у которой есть ещё и pgDash, для которой pgmetrics собирает статистику.

Новое в версии:

• собирает и парсит логи из AWS RDS и Aurora, используя CloudWatch;
• поддержка пулера Odyssey v1.1;
• улучшена поддержка Postgres 13;
• улучшена поддержка метрик AWS RDS;
• появились бинарники для ARMv8

Скачать можно отсюда.

HypoPG 1.2

HypoPG одно из произведений Жульена Руо (Julien Rouhaud). Это расширение для работы с гипотетическими индексами. Новое в версии: работая на стендбае, hypopg использует фальшивый (fake) генератор oid, который одалживает их внутри интервала FirstBootstrapObjectId / FirstNormalObjectId, а не генерит реальные. Если потребуется, можно работать по-старому, используя опцию hypopg.use_real_oids. Есть и ещё изменения, hypopg_list_indexes(), подробности в документации.

pgstats.dev

Это динамическая диаграмма Postgres Observability упрощенное представление устройства PostgreSQL и доступные системные представления и функции для получения статистики о работе подсистем Postgres. Этому необычному произведению Алексея Лесовского (Data Egret) всего 5 месяцев, но её знают многие DBA, спорят и интересуются: что новенького? Новое, например, вот:

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

AGE 0.4.0

Расширение, добавляющее графовую функциональность. Новшества в 0.4.0 здесь.

pg_log_statements 0.0.2

pg_log_statements расширение PostgreSQL, которое позволяет логировать SQL-запросы так, что переменная log_statement может быть установлена для отдельного серверного процесса (по id или фильтру), а не на уровне базы или инстанса.

Можно зайти на PGXN или на гитхабе создателя Пьера Форстмана, специалиста по Oracle.

Конференции

PostgresLondon 2021

Состоится уже 12-го мая, виртуальная. Расписание.

Highload++

Состоится офлайн 17 -18 мая в Крокус-Экспо, Москва. Расписание.

Postgres Vision 2020

Postgres Vision виртуальная конференция EDB, но участие свободное. Состоится 22-23 июня. Регистрация.

Следующий номер Postgresso 32 выйдет в первых числах июня.