СУБД Postgres Pro Standard придумана для того, чтобы доставлять наши разработки пользователям быстрее, чем мы можем это сделать через PostgreSQL. Те фичи, которые еще не вошли в PostgreSQL, но находятся на твердом пути туда, мы включаем в Postgres Pro Standard. Также в Postgres Pro Standard входят некоторые расширения, которые востребованы нашими клиентами, но отсутствуют в обычной поставке PostgreSQL.
Иногда бывают исключения, когда в Postgres Pro Standard по просьбам юзеров и для их удовлетворения включаются и менее тривиальные фичи, которым по-хорошему место только в Postgres Pro Enterprise. В частности, это PTRACK, о нём ниже.
Не все, но изрядная доля дополнительных расширений и утилит, входящих в Standard, разработана в Postgres Professional. Все патчи Postgres Pro придуманы и реализованы нашими силами. Начнем с улучшений, потребовавших вмешательства в ядро СУБД.
Postgres Pro Standard отличается от PostgreSQL на двух уровнях: набором расширений и утилит, которые есть в сборке, и самим ядром. К ядру применены некоторые полезные патчи, которые оптимизируют производительность (например, нетормозящий детектор блокировок) и патчи, повышающие эффективность утилит и расширений (например, для того, чтобы pg_probackup работал в полную силу, применяется патч PTRACK 2.0). Отличия ядра версии Standard от PostgreSQL сведены к минимуму ради максимально возможной совместимости. Скажем, расширение pg_pathman входит в Standard, но его можно скачать с гитхаба, собрать и установить и на ванильный PostgreSQL, никаких проблем с совместимостью не будет.
Начнём с изменений в ядре.
В PostgreSQL по умолчанию для сортировки строк используется обычное их сравнение средствами стандартной библиотеки C. Но существует также возможность использовать для той же цели библиотеку ICU, разрабатываемую IBM. Эта библиотека ценна для нас прежде всего тем, что обеспечивает платформенно-независимую сортировку. Именно поэтому, например, она используется в 1С, и сборки PostgreSQL «для один-эс» давно работают с этой библиотекой.
Кроме того, сравнение строк через ICU иногда бывает быстрее, чем через libc, и количество символов, известных ей, больше. В общем, полезная библиотека. Postgres Pro Standard с самой первой версии (9.5) работает с ней. В PostgreSQL работа с ICU стала возможной с 10-й версии.
Библиотека полезная, но надо иметь в виду некоторые нештатные ситуации. Предположим, пользователь СУБД решил проапгрейдить ОС. Вместе с ОС может проапгрейдиться и библиотека ICU, и порядок слов в сортировке поменяется. После этого сразу же все индексы станут непригодными: индексный поиск станет давать некорректные результаты. В таких случаях база говорила, что изменилась версия ICU, и останавливалась.
Но это уж больно жесткое решение вопроса. После обсуждений и опроса заказчиков решено было смягчить поведение. Теперь проверяются только версии COLLATION (правила сортировки). Если изменились те версии COLLATION, которые используются в базе, база выдает предупреждение при старте СУБД, но не останавливается. Она еще и напоминает об этом пользователю в начале каждой сессии.
Механизм обнаружения взаимных блокировок (deadlock detection) может ухудшать производительность. В Standard уже не может: патч ядра позволяет ему работать без торможения. После серьезных улучшений механизма проверки эти неприятности проявляются только на большом количестве ядер и коннектов.
Улучшена оценка количества результатов джойнов при наличии подходящих индексов.
Появилась возможность использования подходящих индексов для группировки и сортировки полей. Эта фича впервые была включена в Standard 11.1.1 и Enterprise 11.2.1. В нашей Standard 12 она тоже есть.
Фёдор Сигаев, технический директор Postgres Professional, предложил эти полезные патчи сообществу, они рассматриваются и, будем надеяться, попадут в версию PG 13.
Оптимизацию работы GROUP BY проиллюстрируем примерами: они наглядны и легко воспроизводимы.
Смысл этого патча в том, что Postgres не оптимизировал порядок полей, перечисленных в GROUP BY. А от последовательности группировки зависит время исполнения (при одном и том же результате запроса). В обсуждении в рассылке hackers есть детали.
Если в первом обрабатываемом столбце значение уникально, то сравнивать больше ничего не надо. Если начать с другого столбца, то сравнивать придется.
Приступаем к испытаниям:
В текстовом поле v генерятся 60 пробелов, после которых следуют числа 0 или 1. Выглядят записи так:
Группируем результаты:
План в PostgreSQL:
Теперь в обратном порядке: v, а уже потом p:
Получается, что в обратном порядке заметно медленнее. Это происходит потому, что первым анализируется поле
Посмотрим, как тот же запрос будет работать с патчем, выбирающим оптимальный порядок обработки столбцов:
И в обратном порядке:
В плане прописано, что и там, и там порядок обработки одинаков: Sort Key: p, v. Соответственно и время примерно одинаковое. А теперь сравним, что будет, когда используется индекс.
В PostgreSQL:
И в обратном порядке:
Теперь в Standard:
И в обратном порядке:
Время опять одинаковое, что естественно: ведь по сути действия одни и те же.
Postgres Pro не принимает нулевые байты (0x00) в данных, поэтому при COPY FROM их надо заменять, иначе будет ошибка. Это реальная проблема, с которой столкнулся заказчик, импортируя данные из CSV-файла. Решение ее — замена нулевых байтов на заданный ASCII-символ. Он должен отличаться от символов QUOTE и DELIMITER, применяемых при выполнении COPY FROM; в противном случае результат может быть неожиданным. По умолчанию значение переменной nul_byte_replacement_on_import (string) '\0', то есть замена не производится.
LSN — это последовательный номер в журнале, то есть указатель на позицию в WAL (Log Sequence Number). Команда WAITLSN ждёт воспроизведения заданного LSN. Если приложение работает и с мастером, и с репликой, то необходимо время от времени убеждаться в их синхронности. WAITLSN — межпроцессный механизм в PostgrePro, управляющий синхронизацией при синхронной репликации. По умолчанию время ожидания неограниченно. Ожидание можно прервать, нажав Ctrl+C или остановив сервер postgres. Вы также можете задать время ожидания, добавив указание TIMEOUT, или проверить состояние целевого LSN без ожидания, воспользовавшись указанием NOWAIT.
Допустим, приложение совершает некое действие, получает от СУБД номер LSN на мастере и теперь хочет убедиться, что действия на реплике будут синхронизированы с мастером, т.е. приложение может быть уверено, что то, что оно записало на мастере, уже пришло на реплику и готово к чтению. По умолчанию это обычно не гарантируется. WAITLSN позволяет контролировать это взаимодействие и выбирать режим ожидания от INFINITELY по умолчанию, до TIMEOUT и NOWAIT.
По сигналу SIGHUP PostgreSQL перечитывает postgresql.conf, но не recovery.conf. Относительно новый патч ядра, появившийся в Standard и Enterprise 10.4.1. заставлял перечитывать и recovery.conf. Но в версии Postgres 12 файла recovery.conf вообще нет: все переменные из него перенесены в postgresql.conf. Тем не менее, хотя файл перечитывается весь, переменные из recovery.conf не переопределялись по SIGHUP, а требовали перезапуска Postgres. В Standard этого не требуется: всё прочитается и переопределится.
PTRACK 2.0 это переработанный механизм PTRACK версий Standard и Enterprise 11 и ранее. На уровне СУБД он работал благодаря патчу ядра, а теперь к патчу добавилось расширение ptrack. PTRACK 2.0 отслеживает факты изменения страниц с данными и предоставляет интерфейс для получения этой информации. Ее можно использовать как в диагностических целях, например, получить представление о том, насколько сильно инстанс «мутировал» относительно указанной точки во времени, заданной как последовательный номер в журнале (LSN), так и для создания инкрементальных резервных копий.
Самой сложной и «дорогой» частью процедуры инкрементального резервного копирования, как правило, является вычленение подмножества изменившихся страниц из всего множества страниц кластера. Благодаря тому, что сервер может взять на себя эту задачу и быстро предоставить информацию об изменившихся страницах, время снятия инкрементальных резервных копий с использованием PTRACK значительно уменьшается.
В PTRACK 2.0 используется хеш-таблица задаваемого размера в разделяемой памяти, периодически синхронизируемая с файлом ptrack.map.
Из-за кардинальной переделки внутреннего механизма работы и пользовательского интерфейса, несовместимого со старыми версиями, расширение ptrack доступно только в 12-й версии PostgresPro Standard и Enterprise, и будет доступно в виде патча и расширения на PostgreSQL 12.
Расширенная поддержка редактирования вводимых команд в psql для Windows реализована с использованием WinEditLine. Теперь можно выводить символы разных алфавитов одновременно (в частности, на нерусской Windows нормально выводится кириллица).
Cтруктура пакетов двоичных файлов для всех дистрибутивов Linux унифицирована, чтобы упростить миграцию между ними и позволяющая устанавливать несколько различных продуктов на базе PostgreSQL совместно без каких-либо конфликтов. Об этом можно прочитать в Главе 16 Документации.
Теперь о расширениях:
Появилось еще в 9.5. При переносе или восстановлении данных обычно не переносится накопленная статистика. Если повторно собирать ее командой ANALYZE, то выполняться она будет для всего кластера, а не для заданной базы. Это может потребовать немало лишнего времени для больших баз данных.
Расширение dump_stat предоставляет функции, позволяющие выгрузить и восстановить содержимое таблицы pg_statistic. При выполнении выгрузки/восстановления данных можно воспользоваться dump_stat для переноса существующей статистики на новый сервер, обойдясь без выполнения команды ANALYZE для всего кластера.
Функция dump_statistic выгружает содержимое системного каталога pg_statistic. Она выдаёт INSERT для каждого кортежа в pg_statistic, кроме тех, что содержат статистические данные о таблицах в схемах information_schema и pg_catalog.
Напомним, что это расширение для работы с JSON(B), а не JS. Оно предоставляет набор функций для обработки этих типов данных. Это специальный язык запросов для эффективного, с использованием индексов, поиска в JSON(B). В статье на хабре можно посмотреть некоторые примеры jsquery и альтернативных способах работы с JSON(B), например JSONPath (и то, и другое разработки нашей компании).
Это расширение даёт набор функций, которые немедленно обновляют статистику в таблицах, которые указаны, после операций INSERT, UPDATE, DELETE или SELECT INTO в них. Автор расширения Фёдор Сигаев.
Чтобы использовать модуль online_analyze, надо загрузить разделяемую библиотеку:
Обновления статистики можно настраивать. Например, задать процент от размера таблицы или минимальное (пороговое) число изменений строк, после достижения которых немедленно начнется сбор статистики.
Расширение pg_pathman в Postgres Professional создано раньше, чем в ядре PostgreSQL реализовали достаточно полный полный набор функций для создания секций (партиций). Поэтому многие операции с секциями можно проделывать и с тем, и с другим механизмом. Желательно только не перемешивать секции, созданные декларативным секционированием и pg_pathman.
Однако, до сих пор многие операции с pg_pathman работают быстрее, а некоторые фичи в PostgreSQL отсутствуют. Например, автоматическое создание (нарезание) секций. В PostgreSQL надо задать границы каждой секции. Если мы заливаем данные, о которых заранее не известно, на сколько секций их можно и нужно разбросать, то удобно просто задать интервал и предоставить софту самому нарезать секции — столько, сколько понадобится. pg_pathman это умеет, PostgreSQL — нет. Но, начиная с PG 11, есть секция по умолчанию (дефолтная), куда можно сваливать все записи, не попавшие в секции с заданными границами.
Есть принципиальная договоренность с лидерами сообщества PostgreSQL, что в будущем лучшие, пока уникальные фичи pg_pathman попадут в основную ветку. Но до этого времени pg_pathman может облегчить жизнь прикладным БД-админам и прикладным программистам.
Создаём расширение:
pg_pathman позволяет разбивать большие таблицы на секции и предоставляет удобный API — набор функций для создания секций и работы с ними. Например, используя функцию
мы можем задать
после чего добавляем секции:
Архивный лог создастся в табличном пространстве ts0, остальные по умолчанию. Но можно не задавать секции в явном виде, а доверить эту операцию СУБД, задав интервал и создав секции в один прием:
На простейшей таблице это будет выглядеть так:
В PostgreSQL нам бы пришлось создавать каждую секцию своей командой. В таких случаях пишут, конечно, скрипт, который формирует требуемый код DDL автоматически. В pg_pathman скрипты писать не нужно, всё уже есть. Но это не самое интересное. Вставим запись, которая не только не попадет по id ни в одну из существующих секций, но не попадет и в ближайшую:
Опять проверим содержимое таблицы при помощи \d+ pg_pathmania:
Произошло вот что: pg_pathman увидел, что запись с id=2000 не попадает в уже созданные секции, рассчитал сколько их нужно создать, зная интервал RANGE, с которым до этого секционировали таблицу, и создало секцию, куда попадает новая запись, и, конечно, все секции между верхней границей старых секций и нижней границей новой секции. Это очень удобно, а в случаях, когда значения поля разбиения обновляющихся данных плохо предсказуемы, это серьезное преимущество pg_pathman.
Это разработанное нами расширение позволяет узнавать текущее состояние запросов в обслуживающем процессе. Существует еще с версии 9.5 и обязано рождением многочисленным просьбам админов заказчиков.
Дело в том, что EXPLAIN ANALYZE позволяет просмотреть статистику выполнения, собранную с каждого узла дерева плана, но эта статистика собирается только после выполнения запроса. Но в жизни, увы, встречаются ситуации, когда надо посмотреть, чем занят запрос еще не завершенный и, может быть, не собирающийся завершаться. pg_query_state позволяет просмотреть актуальную статистику запроса, работающего во внешнем обслуживающем процессе. При этом формат результирующего вывода практически идентичен выводу обычной команды EXPLAIN ANALYZE.
Утилиты:
Это настолько популярный пулер соединений, что рассказывать о нем здесь было бы даже странно. Просто он есть в составе Standard, и его придется устанавливать отдельно в случае ванильной PostgreSQL.
pg_probackup — одна из самых популярных наших разработок. Это менеджер резервного копирования и восстановления, который развивается и обновляется усилиями прежде всего Анастасии Лубенниковой, Григория Смолкина и сообщества пользователей.
Конкурентные преимущества pg_probackup: инкрементальное копирование с блочной гранулярностью (8КБ), три режима инкрементального копирования (PAGE, DELTA, PTRACK), проверка целостности резервных копий по требованию, верификация кластера PostgreSQL, сжатие резервных копий, частичное восстановление и пр.
Режим инкрементального копирования PTRACK, полагающийся на одноименное расширение как части переработанного механизма – PTRACK 2.0 – стал еще быстрее и теперь однозначно является самым быстрым и «дешевым» из режимов pg_probackup.
pg_repack популярная утилита, ее работа похожа на VACUUM FULL или CLUSTER. Она не только перепаковывает таблицы, убирая пустоты, но и умеет восстанавливать физический порядок кластеризованных индексов. В отличие от CLUSTER и VACUUM FULL, она выполняет эти операции «на ходу», обходясь без исключительных блокировок таблиц и вообще работая эффективно. В ванильную версию она не входит.
Об этом расширении на хабре есть интересная статья нашего сотрудника Ивана Фролкова. Причина появления расширения в том, что работать с промежуточными результатами иногда неудобно и накладно. В статье разобраны альтернативы. Самая распространенная из них — временные таблицы.
Как хранилище временных данных расширение pg_variables значительно более производительно, чем временные таблицы (в статье есть тесты pgbench), и более удобно: набор данных определяется парой «пакет — переменная», которые вполне могут быть переданы как параметры, возвращены из функции и т. д. Для работы с переменными есть функции set/get. Вот так, например, можно хранить много переменных (package — имя пакета, а выражение после запятой — переменные в этом пакете:
У переменных есть интересное свойство: не баг и не преимущество, а фича: данные, сохраненные средствами расширения, существуют вне транзакций — они сохраняются как в случае фиксации транзакции, так и в случае отката; более того, даже при выполнении отдельной команды можно получить частичные данные:
С одной стороны, это не очень удобно — в ряде случаев необходимо предусмотреть удаление некорректно внесенных данных, но в других может оказаться весьма полезным — например, сохранять какие-то данные даже в случае отката транзакции. В документации есть подробности.
В заключение еще несколько расширений:
sr_plan сохраняет и восстанавливает планы запросов. Включают его так:
После этого планы всех последующих запросов будут сохраняться в таблице sr_plans, пока этой переменной не будет присвоено значение false. Сохраняются планы всех запросов, включая повторные.
plantuner поддерживает указания (хинты) для планировщика по подключению или отключению указанных индексов при выполнении запроса. GUC-переменных всего две: enable_index / desable_index:
Расширение shared_ispell, позволяющее разместить словари в общей памяти, есть в Standard и отсутствует в PostgreSQL. В нашем наборе hunspell-dict есть словари для языков:
Расширение pg_tsparser — альтернативный анализатор текстового поиска. Это расширение меняет стандартную стратегию разбора текста для слов, включающих подчёркивания, а также цифры и буквы, разделённые знаком подчеркивания. В дополнение к отдельным частям слова, возвращаемых по умолчанию, pg_tsparser также возвращает всё слово. Это очень важно для технической документации или статей вроде этой, в которой встречается программный код, а в нем встречаются слова вроде «pg_tsparser», «pg_probackup», «jsonb_build_object». Этот парсер воспринимает данные слова не только как набор составляющих, но и как единую лексему, и тем самым повышает качество поиска.
На этом заканчивается этот короткий обзор, но не отличия PostgresPro Standard от PostgreSQL. Основные мы упомянули, а детально сравнить версии можно, стартовав, например, с этой страницы.
Иногда бывают исключения, когда в Postgres Pro Standard по просьбам юзеров и для их удовлетворения включаются и менее тривиальные фичи, которым по-хорошему место только в Postgres Pro Enterprise. В частности, это PTRACK, о нём ниже.
Не все, но изрядная доля дополнительных расширений и утилит, входящих в Standard, разработана в Postgres Professional. Все патчи Postgres Pro придуманы и реализованы нашими силами. Начнем с улучшений, потребовавших вмешательства в ядро СУБД.
Postgres Pro Standard отличается от PostgreSQL на двух уровнях: набором расширений и утилит, которые есть в сборке, и самим ядром. К ядру применены некоторые полезные патчи, которые оптимизируют производительность (например, нетормозящий детектор блокировок) и патчи, повышающие эффективность утилит и расширений (например, для того, чтобы pg_probackup работал в полную силу, применяется патч PTRACK 2.0). Отличия ядра версии Standard от PostgreSQL сведены к минимуму ради максимально возможной совместимости. Скажем, расширение pg_pathman входит в Standard, но его можно скачать с гитхаба, собрать и установить и на ванильный PostgreSQL, никаких проблем с совместимостью не будет.
Начнём с изменений в ядре.
Проверка версий ICU
В PostgreSQL по умолчанию для сортировки строк используется обычное их сравнение средствами стандартной библиотеки C. Но существует также возможность использовать для той же цели библиотеку ICU, разрабатываемую IBM. Эта библиотека ценна для нас прежде всего тем, что обеспечивает платформенно-независимую сортировку. Именно поэтому, например, она используется в 1С, и сборки PostgreSQL «для один-эс» давно работают с этой библиотекой.
Кроме того, сравнение строк через ICU иногда бывает быстрее, чем через libc, и количество символов, известных ей, больше. В общем, полезная библиотека. Postgres Pro Standard с самой первой версии (9.5) работает с ней. В PostgreSQL работа с ICU стала возможной с 10-й версии.
Библиотека полезная, но надо иметь в виду некоторые нештатные ситуации. Предположим, пользователь СУБД решил проапгрейдить ОС. Вместе с ОС может проапгрейдиться и библиотека ICU, и порядок слов в сортировке поменяется. После этого сразу же все индексы станут непригодными: индексный поиск станет давать некорректные результаты. В таких случаях база говорила, что изменилась версия ICU, и останавливалась.
Но это уж больно жесткое решение вопроса. После обсуждений и опроса заказчиков решено было смягчить поведение. Теперь проверяются только версии COLLATION (правила сортировки). Если изменились те версии COLLATION, которые используются в базе, база выдает предупреждение при старте СУБД, но не останавливается. Она еще и напоминает об этом пользователю в начале каждой сессии.
Оптимизация блокировок, джойнов и GROUP BY
Механизм обнаружения взаимных блокировок (deadlock detection) может ухудшать производительность. В Standard уже не может: патч ядра позволяет ему работать без торможения. После серьезных улучшений механизма проверки эти неприятности проявляются только на большом количестве ядер и коннектов.
Улучшена оценка количества результатов джойнов при наличии подходящих индексов.
Появилась возможность использования подходящих индексов для группировки и сортировки полей. Эта фича впервые была включена в Standard 11.1.1 и Enterprise 11.2.1. В нашей Standard 12 она тоже есть.
Фёдор Сигаев, технический директор Postgres Professional, предложил эти полезные патчи сообществу, они рассматриваются и, будем надеяться, попадут в версию PG 13.
Оптимизацию работы GROUP BY проиллюстрируем примерами: они наглядны и легко воспроизводимы.
Смысл этого патча в том, что Postgres не оптимизировал порядок полей, перечисленных в GROUP BY. А от последовательности группировки зависит время исполнения (при одном и том же результате запроса). В обсуждении в рассылке hackers есть детали.
Если в первом обрабатываемом столбце значение уникально, то сравнивать больше ничего не надо. Если начать с другого столбца, то сравнивать придется.
Приступаем к испытаниям:
DROP TABLE IF EXISTS btg;
SELECT
i AS id,
i/2 AS p,
format('%60s', i%2) AS v
INTO btg
FROM
generate_series(1, 1000000) i;В текстовом поле v генерятся 60 пробелов, после которых следуют числа 0 или 1. Выглядят записи так:
SELECT * FROM btg ORDER BY id DESC LIMIT 3;
id | p | v
---------+--------+--------------------------------------------------------------
1000000 | 500000 | 0
999999 | 499999 | 1
999998 | 499999 | 0
(3 rows)VACUUM btg;
ANALYSE btg;
SET enable_hashagg=off;
SET max_parallel_workers= 0;
SET max_parallel_workers_per_gather = 0;Группируем результаты:
VACUUM btg;
EXPLAIN ANALYZE
SELECT
count(*)
FROM
btg
GROUP BY
p, v;План в PostgreSQL:
QUERY PLAN
------------------------------------------------------
GroupAggregate (cost=204036.84..218981.05 rows=494421 width=73) (actual time=843.999..1194.985 rows=1000000 loops=1)
Group Key: p, v
-> Sort (cost=204036.84..206536.84 rows=1000000 width=65) (actual time=843.990..946.769 rows=1000000 loops=1)
Sort Key: p, v
Sort Method: external sort Disk: 73320kB
-> Seq Scan on btg (cost=0.00..22346.00 rows=1000000 width=65) (actual time=0.158..151.645 rows=1000000 loops=1)
Planning time: 0.317 ms
Execution time: 1250.086 ms
(8 rows)Теперь в обратном порядке: v, а уже потом p:
EXPLAIN ANALYZE
SELECT
count(*)
FROM
btg
GROUP BY
v, p;
QUERY PLAN
------------------------------------------------
GroupAggregate (cost=204036.84..218981.05 rows=494421 width=73) (actual time=2552.477..3353.890 rows=1000000 loops=1)
Group Key: v, p
-> Sort (cost=204036.84..206536.84 rows=1000000 width=65) (actual time=2552.469..3111.516 rows=1000000 loops=1)
Sort Key: v, p
Sort Method: external merge Disk: 76264kB
-> Seq Scan on btg (cost=0.00..22346.00 rows=1000000 width=65) (actual time=0.082..126.578 rows=1000000 loops=1)
Planning time: 0.060 ms
Execution time: 3411.048 ms
(8 rows)Получается, что в обратном порядке заметно медленнее. Это происходит потому, что первым анализируется поле
v с малым разбросом значений. Приходится делать много проверок по оставшимся полям (здесь — полю p).Посмотрим, как тот же запрос будет работать с патчем, выбирающим оптимальный порядок обработки столбцов:
QUERY PLAN
----------------------------------------------------------------
GroupAggregate (cost=237400.11..252417.09 rows=501698 width=73) (actual time=415.541..703.647 rows=1000000 loops=1)
Group Key: p, v
-> Sort (cost=237400.11..239900.11 rows=1000000 width=65) (actual time=415.533..507.785 rows=1000000 loops=1)
Sort Key: p, v
Sort Method: external merge Disk: 73488kB
-> Seq Scan on btg (cost=0.00..22346.00 rows=1000000 width=65) (actual time=0.059..139.587 rows=1000000 loops=1)
Planning Time: 0.123 ms
Execution Time: 742.118 ms
(8 rows)И в обратном порядке:
QUERY PLAN
------------------------------------------------------
GroupAggregate (cost=237400.11..252417.09 rows=501698 width=73) (actual time=414.322..714.593 rows=1000000 loops=1)
Group Key: p, v
-> Sort (cost=237400.11..239900.11 rows=1000000 width=65) (actual time=414.312..517.707 rows=1000000 loops=1)
Sort Key: p, v
Sort Method: external merge Disk: 76384kB
-> Seq Scan on btg (cost=0.00..22346.00 rows=1000000 width=65) (actual time=0.071..129.835 rows=1000000 loops=1)
Planning Time: 0.140 ms
Execution Time: 753.031 ms
(8 rows)В плане прописано, что и там, и там порядок обработки одинаков: Sort Key: p, v. Соответственно и время примерно одинаковое. А теперь сравним, что будет, когда используется индекс.
CREATE INDEX ON btg(p, v);
SET enable_seqscan=off;
SET enable_bitmapscan=off;
VACUUM btg;
EXPLAIN ANALYZE
SELECT
count(*)
FROM
btg
GROUP BY
v, p
;В PostgreSQL:
QUERY PLAN
---------------------------------------------------------
GroupAggregate (cost=0.55..74660.04 rows=494408 width=73) (actual time=0.013..391.317 rows=1000000 loops=1)
Group Key: p, v
-> Index Only Scan using btg_p_v_idx on btg (cost=0.55..62216.16 rows=999974 width=65) (actual time=0.009..120.298 rows=1000000 loops=1)
Heap Fetches: 0
Planning time: 0.078 ms
Execution time: 442.923 ms
(6 rows)И в обратном порядке:
QUERY PLAN
------------------------------------------------------
GroupAggregate (cost=243904.22..258848.04 rows=494408 width=73) (actual time=2558.485..3352.240 rows=1000000 loops=1)
Group Key: v, p
-> Sort (cost=243904.22..246404.16 rows=999974 width=65) (actual time=2558.478..3110.242 rows=1000000 loops=1)
Sort Key: v, p
Sort Method: external merge Disk: 76264kB
-> Index Only Scan using btg_p_v_idx on btg (cost=0.55..62216.16 rows=999974 width=65) (actual time=0.011..133.563 rows=1000000 loops=1)
Heap Fetches: 0
Planning time: 0.093 ms
Execution time: 3409.335 ms
(9 rows)Теперь в Standard:
QUERY PLAN
--------------------------------------------------------------
GroupAggregate (cost=0.55..74196.82 rows=501685 width=73) (actual time=0.150..412.174 rows=1000000 loops=1)
Group Key: p, v
-> Index Only Scan using btg_p_v_idx on btg (cost=0.55..61680.16 rows=999974 width=65) (actual time=0.134..149.669 rows=1000000 loops=1)
Heap Fetches: 0
Planning Time: 0.175 ms
Execution Time: 448.635 ms
(6 rows)И в обратном порядке:
QUERY PLAN
-------------------------------------------------------------
GroupAggregate (cost=0.55..74196.82 rows=501685 width=73) (actual time=0.014..307.258 rows=1000000 loops=1)
Group Key: p, v
-> Index Only Scan using btg_p_v_idx on btg (cost=0.55..61680.16 rows=999974 width=65) (actual time=0.008..89.204 rows=1000000 loops=1)
Heap Fetches: 0
Planning Time: 0.054 ms
Execution Time: 337.766 ms
(6 rows)Время опять одинаковое, что естественно: ведь по сути действия одни и те же.
Замена нулевого байта при загрузке
Postgres Pro не принимает нулевые байты (0x00) в данных, поэтому при COPY FROM их надо заменять, иначе будет ошибка. Это реальная проблема, с которой столкнулся заказчик, импортируя данные из CSV-файла. Решение ее — замена нулевых байтов на заданный ASCII-символ. Он должен отличаться от символов QUOTE и DELIMITER, применяемых при выполнении COPY FROM; в противном случае результат может быть неожиданным. По умолчанию значение переменной nul_byte_replacement_on_import (string) '\0', то есть замена не производится.
WaitLSN
LSN — это последовательный номер в журнале, то есть указатель на позицию в WAL (Log Sequence Number). Команда WAITLSN ждёт воспроизведения заданного LSN. Если приложение работает и с мастером, и с репликой, то необходимо время от времени убеждаться в их синхронности. WAITLSN — межпроцессный механизм в PostgrePro, управляющий синхронизацией при синхронной репликации. По умолчанию время ожидания неограниченно. Ожидание можно прервать, нажав Ctrl+C или остановив сервер postgres. Вы также можете задать время ожидания, добавив указание TIMEOUT, или проверить состояние целевого LSN без ожидания, воспользовавшись указанием NOWAIT.
Допустим, приложение совершает некое действие, получает от СУБД номер LSN на мастере и теперь хочет убедиться, что действия на реплике будут синхронизированы с мастером, т.е. приложение может быть уверено, что то, что оно записало на мастере, уже пришло на реплику и готово к чтению. По умолчанию это обычно не гарантируется. WAITLSN позволяет контролировать это взаимодействие и выбирать режим ожидания от INFINITELY по умолчанию, до TIMEOUT и NOWAIT.
Перечитывание переменных из бывшего recovery.conf
По сигналу SIGHUP PostgreSQL перечитывает postgresql.conf, но не recovery.conf. Относительно новый патч ядра, появившийся в Standard и Enterprise 10.4.1. заставлял перечитывать и recovery.conf. Но в версии Postgres 12 файла recovery.conf вообще нет: все переменные из него перенесены в postgresql.conf. Тем не менее, хотя файл перечитывается весь, переменные из recovery.conf не переопределялись по SIGHUP, а требовали перезапуска Postgres. В Standard этого не требуется: всё прочитается и переопределится.
Поддержка PTRACK
PTRACK 2.0 это переработанный механизм PTRACK версий Standard и Enterprise 11 и ранее. На уровне СУБД он работал благодаря патчу ядра, а теперь к патчу добавилось расширение ptrack. PTRACK 2.0 отслеживает факты изменения страниц с данными и предоставляет интерфейс для получения этой информации. Ее можно использовать как в диагностических целях, например, получить представление о том, насколько сильно инстанс «мутировал» относительно указанной точки во времени, заданной как последовательный номер в журнале (LSN), так и для создания инкрементальных резервных копий.
Самой сложной и «дорогой» частью процедуры инкрементального резервного копирования, как правило, является вычленение подмножества изменившихся страниц из всего множества страниц кластера. Благодаря тому, что сервер может взять на себя эту задачу и быстро предоставить информацию об изменившихся страницах, время снятия инкрементальных резервных копий с использованием PTRACK значительно уменьшается.
В PTRACK 2.0 используется хеш-таблица задаваемого размера в разделяемой памяти, периодически синхронизируемая с файлом ptrack.map.
Из-за кардинальной переделки внутреннего механизма работы и пользовательского интерфейса, несовместимого со старыми версиями, расширение ptrack доступно только в 12-й версии PostgresPro Standard и Enterprise, и будет доступно в виде патча и расширения на PostgreSQL 12.
Редактирование команд в psql для Windows
Расширенная поддержка редактирования вводимых команд в psql для Windows реализована с использованием WinEditLine. Теперь можно выводить символы разных алфавитов одновременно (в частности, на нерусской Windows нормально выводится кириллица).
Унифицированная структура пакетов
Cтруктура пакетов двоичных файлов для всех дистрибутивов Linux унифицирована, чтобы упростить миграцию между ними и позволяющая устанавливать несколько различных продуктов на базе PostgreSQL совместно без каких-либо конфликтов. Об этом можно прочитать в Главе 16 Документации.
Теперь о расширениях:
dump_stat
Появилось еще в 9.5. При переносе или восстановлении данных обычно не переносится накопленная статистика. Если повторно собирать ее командой ANALYZE, то выполняться она будет для всего кластера, а не для заданной базы. Это может потребовать немало лишнего времени для больших баз данных.
Расширение dump_stat предоставляет функции, позволяющие выгрузить и восстановить содержимое таблицы pg_statistic. При выполнении выгрузки/восстановления данных можно воспользоваться dump_stat для переноса существующей статистики на новый сервер, обойдясь без выполнения команды ANALYZE для всего кластера.
Функция dump_statistic выгружает содержимое системного каталога pg_statistic. Она выдаёт INSERT для каждого кортежа в pg_statistic, кроме тех, что содержат статистические данные о таблицах в схемах information_schema и pg_catalog.
jsquery
Напомним, что это расширение для работы с JSON(B), а не JS. Оно предоставляет набор функций для обработки этих типов данных. Это специальный язык запросов для эффективного, с использованием индексов, поиска в JSON(B). В статье на хабре можно посмотреть некоторые примеры jsquery и альтернативных способах работы с JSON(B), например JSONPath (и то, и другое разработки нашей компании).
online_analyze
Это расширение даёт набор функций, которые немедленно обновляют статистику в таблицах, которые указаны, после операций INSERT, UPDATE, DELETE или SELECT INTO в них. Автор расширения Фёдор Сигаев.
Чтобы использовать модуль online_analyze, надо загрузить разделяемую библиотеку:
LOAD 'online_analyze'; Обновления статистики можно настраивать. Например, задать процент от размера таблицы или минимальное (пороговое) число изменений строк, после достижения которых немедленно начнется сбор статистики.
pg_pathman
Расширение pg_pathman в Postgres Professional создано раньше, чем в ядре PostgreSQL реализовали достаточно полный полный набор функций для создания секций (партиций). Поэтому многие операции с секциями можно проделывать и с тем, и с другим механизмом. Желательно только не перемешивать секции, созданные декларативным секционированием и pg_pathman.
Однако, до сих пор многие операции с pg_pathman работают быстрее, а некоторые фичи в PostgreSQL отсутствуют. Например, автоматическое создание (нарезание) секций. В PostgreSQL надо задать границы каждой секции. Если мы заливаем данные, о которых заранее не известно, на сколько секций их можно и нужно разбросать, то удобно просто задать интервал и предоставить софту самому нарезать секции — столько, сколько понадобится. pg_pathman это умеет, PostgreSQL — нет. Но, начиная с PG 11, есть секция по умолчанию (дефолтная), куда можно сваливать все записи, не попавшие в секции с заданными границами.
Есть принципиальная договоренность с лидерами сообщества PostgreSQL, что в будущем лучшие, пока уникальные фичи pg_pathman попадут в основную ветку. Но до этого времени pg_pathman может облегчить жизнь прикладным БД-админам и прикладным программистам.
Создаём расширение:
CREATE EXTENSION pg_pathman;pg_pathman позволяет разбивать большие таблицы на секции и предоставляет удобный API — набор функций для создания секций и работы с ними. Например, используя функцию
create_range_partitions(relation REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE);мы можем задать
SELECT create_range_partitions('log', 'dt', NULL::date, '1 month'::interval);после чего добавляем секции:
SELECT add_range_partition('log', NULL, '2017-01-01'::date, 'log_archive', 'ts0');
SELECT add_range_partition('log', '2017-01-01'::date, '2017-02-01'::date, 'log_1');
SELECT add_range_partition('log', '2017-02-01'::date, '2017-03-01'::date', log_2');Архивный лог создастся в табличном пространстве ts0, остальные по умолчанию. Но можно не задавать секции в явном виде, а доверить эту операцию СУБД, задав интервал и создав секции в один прием:
SELECT create_range_partitions('log', 'dt', '2017-01-01'::date, '1 month'::interval);На простейшей таблице это будет выглядеть так:
CREATE TABLE pg_pathmania(id serial, val float);
INSERT INTO pg_pathmania(val) SELECT random() * 1000 FROM generate_series(1, 1000);
SELECT create_range_partitions('pg_pathmania', 'id', 0, 50);
test_parti=# \d+ pg_pathmania
Table "public.pg_pathmania"
Column | Type | Collation | Nullable | Default | Storage | S
tats target | Description
--------+------------------+-----------+----------+-----------------------+---------+------+------
id | integer | | not null | nextval('pg_pathmania_id_seq'::regclass) | plain |
|
val | double precision | | | | plain |
|
Child tables: pg_pathmania_1,
pg_pathmania_10,
pg_pathmania_11,
pg_pathmania_12,
pg_pathmania_13,
pg_pathmania_14,
pg_pathmania_15,
pg_pathmania_16,
pg_pathmania_17,
pg_pathmania_18,
pg_pathmania_19,
pg_pathmania_2,
pg_pathmania_20,
pg_pathmania_21,
pg_pathmania_3,
pg_pathmania_4,
pg_pathmania_5,
pg_pathmania_6,
pg_pathmania_7,
pg_pathmania_8,
pg_pathmania_9В PostgreSQL нам бы пришлось создавать каждую секцию своей командой. В таких случаях пишут, конечно, скрипт, который формирует требуемый код DDL автоматически. В pg_pathman скрипты писать не нужно, всё уже есть. Но это не самое интересное. Вставим запись, которая не только не попадет по id ни в одну из существующих секций, но не попадет и в ближайшую:
INSERT INTO pg_pathmania(id, val) VALUES (2000, 277.835794724524);Опять проверим содержимое таблицы при помощи \d+ pg_pathmania:
Child tables: pg_pathmania_1,
pg_pathmania_10,
...
pg_pathmania_39,
pg_pathmania_4,
pg_pathmania_40,
pg_pathmania_41,Произошло вот что: pg_pathman увидел, что запись с id=2000 не попадает в уже созданные секции, рассчитал сколько их нужно создать, зная интервал RANGE, с которым до этого секционировали таблицу, и создало секцию, куда попадает новая запись, и, конечно, все секции между верхней границей старых секций и нижней границей новой секции. Это очень удобно, а в случаях, когда значения поля разбиения обновляющихся данных плохо предсказуемы, это серьезное преимущество pg_pathman.
pg_query_state
Это разработанное нами расширение позволяет узнавать текущее состояние запросов в обслуживающем процессе. Существует еще с версии 9.5 и обязано рождением многочисленным просьбам админов заказчиков.
Дело в том, что EXPLAIN ANALYZE позволяет просмотреть статистику выполнения, собранную с каждого узла дерева плана, но эта статистика собирается только после выполнения запроса. Но в жизни, увы, встречаются ситуации, когда надо посмотреть, чем занят запрос еще не завершенный и, может быть, не собирающийся завершаться. pg_query_state позволяет просмотреть актуальную статистику запроса, работающего во внешнем обслуживающем процессе. При этом формат результирующего вывода практически идентичен выводу обычной команды EXPLAIN ANALYZE.
Утилиты:
pgBouncer
Это настолько популярный пулер соединений, что рассказывать о нем здесь было бы даже странно. Просто он есть в составе Standard, и его придется устанавливать отдельно в случае ванильной PostgreSQL.
pg_probackup
pg_probackup — одна из самых популярных наших разработок. Это менеджер резервного копирования и восстановления, который развивается и обновляется усилиями прежде всего Анастасии Лубенниковой, Григория Смолкина и сообщества пользователей.
Конкурентные преимущества pg_probackup: инкрементальное копирование с блочной гранулярностью (8КБ), три режима инкрементального копирования (PAGE, DELTA, PTRACK), проверка целостности резервных копий по требованию, верификация кластера PostgreSQL, сжатие резервных копий, частичное восстановление и пр.
Режим инкрементального копирования PTRACK, полагающийся на одноименное расширение как части переработанного механизма – PTRACK 2.0 – стал еще быстрее и теперь однозначно является самым быстрым и «дешевым» из режимов pg_probackup.
pg_repack
pg_repack популярная утилита, ее работа похожа на VACUUM FULL или CLUSTER. Она не только перепаковывает таблицы, убирая пустоты, но и умеет восстанавливать физический порядок кластеризованных индексов. В отличие от CLUSTER и VACUUM FULL, она выполняет эти операции «на ходу», обходясь без исключительных блокировок таблиц и вообще работая эффективно. В ванильную версию она не входит.
pg_variables
Об этом расширении на хабре есть интересная статья нашего сотрудника Ивана Фролкова. Причина появления расширения в том, что работать с промежуточными результатами иногда неудобно и накладно. В статье разобраны альтернативы. Самая распространенная из них — временные таблицы.
Как хранилище временных данных расширение pg_variables значительно более производительно, чем временные таблицы (в статье есть тесты pgbench), и более удобно: набор данных определяется парой «пакет — переменная», которые вполне могут быть переданы как параметры, возвращены из функции и т. д. Для работы с переменными есть функции set/get. Вот так, например, можно хранить много переменных (package — имя пакета, а выражение после запятой — переменные в этом пакете:
SELECT pgv_set_int('package','#'||n,n), n FROM generate_series(1,1000000) AS gs(n);У переменных есть интересное свойство: не баг и не преимущество, а фича: данные, сохраненные средствами расширения, существуют вне транзакций — они сохраняются как в случае фиксации транзакции, так и в случае отката; более того, даже при выполнении отдельной команды можно получить частичные данные:
SELECT pgv_insert('package', 'errs', row(n)) FROM generate_series(1,5) AS gs(n) WHERE 1.0/(n-3)<>0;
ERROR: there is a record in the variable "errs" with same key
test_parti=# SELECT * FROM pgv_select('package','errs') AS r(i int);
i
---
1
2
(2 rows)С одной стороны, это не очень удобно — в ряде случаев необходимо предусмотреть удаление некорректно внесенных данных, но в других может оказаться весьма полезным — например, сохранять какие-то данные даже в случае отката транзакции. В документации есть подробности.
В заключение еще несколько расширений:
sr_plan, plantuner
sr_plan сохраняет и восстанавливает планы запросов. Включают его так:
SET sr_plan.write_mode = true;После этого планы всех последующих запросов будут сохраняться в таблице sr_plans, пока этой переменной не будет присвоено значение false. Сохраняются планы всех запросов, включая повторные.
plantuner поддерживает указания (хинты) для планировщика по подключению или отключению указанных индексов при выполнении запроса. GUC-переменных всего две: enable_index / desable_index:
SET plantuner.disable_index='id_idx2';Расширения для полнотекстового поиска: shared_ispell, pg_tsparser
Расширение shared_ispell, позволяющее разместить словари в общей памяти, есть в Standard и отсутствует в PostgreSQL. В нашем наборе hunspell-dict есть словари для языков:
- hunspell_en_us,
- hunspell_fr,
- hunspell_nl_nl,
- hunspell_ru_ru
Расширение pg_tsparser — альтернативный анализатор текстового поиска. Это расширение меняет стандартную стратегию разбора текста для слов, включающих подчёркивания, а также цифры и буквы, разделённые знаком подчеркивания. В дополнение к отдельным частям слова, возвращаемых по умолчанию, pg_tsparser также возвращает всё слово. Это очень важно для технической документации или статей вроде этой, в которой встречается программный код, а в нем встречаются слова вроде «pg_tsparser», «pg_probackup», «jsonb_build_object». Этот парсер воспринимает данные слова не только как набор составляющих, но и как единую лексему, и тем самым повышает качество поиска.
Расширения для 1С
- mchar — дополнительный тип данных для совместимости с Microsoft SQL Server;
- fulleq — предоставляет дополнительный оператор равенства для совместимости с Microsoft SQL Server;
- fasttrun — предоставляет транзакционно-небезопасную функцию для усечения временных таблиц, что предотвращает разрастание каталога pg_class.
На этом заканчивается этот короткий обзор, но не отличия PostgresPro Standard от PostgreSQL. Основные мы упомянули, а детально сравнить версии можно, стартовав, например, с этой страницы.