Надеемся, что вы хорошо отдохнули и попраздновали. А мы предлагаем вам очередную сводку 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 только тогда, когда необходимо (считая деньги, например), а если нет, то обходитесь INTEGER, 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_type
FROM 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_type
FROM 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 2021

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

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

Postgresso 31