Postgresso 31



    Надеемся, что вы хорошо отдохнули и попраздновали. А мы предлагаем вам очередную сводку 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 2020

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

    Следующий номер — Postgresso 32 — выйдет в первых числах июня.
    Postgres Professional
    Разработчик СУБД Postgres Pro

    Комментарии 1

      0
      Postgres Vision 2021, а не 2020

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

      Самое читаемое