Полный вакуум с помощью pg_repack на гибком сервере База данных Azure для PostgreSQL

В этой статье вы узнаете, как использовать pg_repack для устранения раздувания и повышения производительности гибкого сервера База данных Azure для PostgreSQL. Раздувание — это возникновение ненужных данных, накапливающихся в таблицах и индексах из-за частого обновления и удаления. Раздувание базы данных может привести к тому, что её размер станет больше ожидаемого, и это может серьёзно повлиять на производительность некоторых запросов. Используйте pg_repack для восстановления пустого пространства и более эффективной реорганизации данных.

Что такое pg_repack?

pg_repack — это расширение PostgreSQL, которое устраняет раздувание таблиц и индексов и более эффективно реорганизует их. pg_repack работает следующим образом: создаёт новую копию целевой таблицы или индекса, вносит все изменения, произошедшие в ходе этого процесса, а затем атомарно заменяет старую версию новой. pg_repack не требует простоя или монопольных блокировок доступа к обработанной таблице или индексу, за исключением краткого периода в начале и в конце операции. Вы можете использовать pg_repack для оптимизации любой таблицы или индекса в базах данных База данных Azure для PostgreSQL — Flexible Server.

Как использовать pg_repack?

Чтобы использоватьpg_repack, необходимо установить расширение в гибкой базе данных сервера База данных Azure для PostgreSQL, а затем выполнить pg_repack команду, указав имя таблицы или индекс, который требуется оптимизировать. Расширение устанавливает блокировки на таблицу или индекс, чтобы предотвратить выполнение других операций во время оптимизации. Он устраняет избыточность и более эффективно реорганизует данные.

Как выполняется полная перепаковка таблицы

Чтобы выполнить перепаковку полной таблицы, расширение выполняет следующие действия.

  1. Создает таблицу журналов для записи изменений, внесенных в исходную таблицу.
  2. Добавляет триггер в исходную таблицу, регистрируя операции INSERT, UPDATE и DELETE в таблице журнала.
  3. Создает новую таблицу, содержащую все строки в исходной таблице.
  4. Создает индексы в новой таблице.
  5. Применяет все изменения, записанные в таблице журнала, к новой таблице.
  6. Переключает исходные и новые таблицы, включая индексы и всплываемые таблицы.
  7. Удаляет исходную таблицу.

На этих этапах pg_repack удерживает блокировку монопольного доступа лишь в течение короткого времени: во время начальной настройки (шаги 1 и 2), а затем снова на заключительном этапе подмены и удаления (шаги 6 и 7). В остальное время pg_repack нужно лишь удерживать разделяемую блокировку на исходной таблице, позволяя операциям INSERT, UPDATE и DELETE выполняться как обычно.

Ограничения

pg_repack имеет некоторые ограничения, которые следует учитывать перед его использованием:

  • Целевая таблица должна иметь первичный ключ или уникальный индекс в столбце NOT NULL для успешной операции.
  • Пока выполняется pg_repack, вы не можете выполнять никакие команды языка определения данных (DDL) для целевых таблиц, кроме VACUUM и ANALYZE. Чтобы обеспечить соблюдение этих ограничений, pg_repack удерживает блокировку общего доступа на целевой таблице во время полной перепаковки таблицы.

Настройка

Предпосылки

  1. pg_repack Настройте расширение, указав список разрешений и создав расширение.

Создание клиентского приложения pg_repack

Для использования этого расширения требуется клиентское приложение, которое можно создать и установить в экземпляре Ubuntu.

Чтобы установить версию 1.5.1 pg_repack, выполните следующий сценарий bash на компьютере Ubuntu.

# Create the file repository configuration
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists
sudo apt-get update
# Install required packages to build the code
sudo apt-get install -y postgresql-server-dev-14 unzip make gcc libssl-dev liblz4-dev zlib1g-dev libreadline-dev libzstd-dev
# Download compressed version of build tree for version 1.5.1 of pg_repack
wget 'https://api.pgxn.org/dist/pg_repack/1.5.1/pg_repack-1.5.1.zip'
# Uncompress build tree
unzip pg_repack-1.5.1.zip
# Set current directory to where build tree was uncompressed
cd pg_repack-1.5.1
# Build code
sudo make
# Copy resulting binaries to /usr/local/bin
sudo cp bin/pg_repack /usr/local/bin
# Run pg_repack to check its version
pg_repack --version

Используйте pg_repack

Пример того, как выполнить pg_repack для таблицы с именем info в схеме public в экземпляре гибкого сервера Базы данных Azure для PostgreSQL с конечной точкой pgserver.postgres.database.azure.com, именем пользователя azureuser и базой данных foo с помощью следующей команды.

  1. Используя клиент по своему выбору, подключитесь к экземпляру гибкого сервера База данных Azure для PostgreSQL. В этом примере используется psql.

        psql "host=<server>.postgres.database.azure.com port=5432 dbname=<database> user=<user> password=<password> sslmode=require"

  2. Найдите версию расширения pg_repack, установленного в базе данных.

    SELECT installed_version FROM pg_available_extensions WHERE name = 'pg_repack';

  3. Версия расширения должна соответствовать версии клиентского приложения, которую можно проверить, выполнив следующую команду:

    azureuser@azureuser:~$ pg_repack --version

  4. Запустите pg_repack клиент против таблицы под названием сведения, которая существует в базе данных foo.

    pg_repack --host=<server>.postgres.database.azure.com --username=<user> --dbname=<database> --table=info --jobs=2 --no-kill-backend --no-superuser-check

параметры pg_repack

Полезные pg_repack варианты для производственных рабочих нагрузок:

  • -k, --no-superuser-check: Пропустить проверки суперпользователя в клиенте. Этот параметр полезен при использовании pg_repack на платформах, где его можно запускать без прав суперпользователя, например на экземплярах База данных Azure для PostgreSQL Flexible Server.

  • -j, --jobs: создайте указанное количество дополнительных подключений к База данных Azure для PostgreSQL — гибкий сервер и используйте эти дополнительные подключения для параллелизации перестроения индексов в каждой таблице. Параллельные сборки индекса поддерживаются только для перепаковки полной таблицы.

  • параметры индексов --index или --only: Если у вашего гибкого сервера База данных Azure для PostgreSQL есть дополнительные ядра и доступная пропускная способность дискового ввода-вывода, использование этого параметра может быть полезным способом ускорить pg_repack.

  • -D, --no-kill-backend: Вместо завершения серверных клиентов, выполняющих блокирующие запросы, пропустите перепаковку таблицы, если не удаётся получить блокировку после ожидания в течение времени, указанного в --wait-timeout. По умолчанию --wait-timeout установлено значение 60 секунд. Значение параметра по умолчанию — false.

  • -E LEVEL, --elevel=LEVEL: выберите уровень вывода сообщения из DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATALи PANIC. Значение по умолчанию — INFO.

Чтобы понять все параметры, ознакомьтесь с документацией pg_repack.

Часто задаваемые вопросы

Является ли pg_repack расширение или исполняемый файл на стороне клиента, например psql или pg_dump?

pg_repack на самом деле и то и другое. pg_repack/lib содержит код расширения, включая созданную схему и артефакты SQL, а библиотека C реализует код нескольких этих функций.

С другой стороны, pg_repack/bin имеет код клиентского приложения, который знает, как взаимодействовать с элементами программирования, реализованными в расширении. Это клиентское приложение призвано упростить взаимодействие с различными интерфейсами, которые отображаются расширением на стороне сервера. Он предлагает пользователю некоторые параметры командной строки, которые проще понять. Клиентское приложение не используется без расширения, созданного в базе данных, на которую он указывает. Расширение на стороне сервера будет полностью функциональным, но пользователю потребуется понять сложный шаблон взаимодействия. Этот шаблон состоит в выполнении запросов для получения данных, которые используются в качестве входных данных для функций, реализованных расширением, и т. д.

Связанный контент

  • Last updated on