pgcompacttable. Реорганизации данных в таблицах и перестройка индексов#

Версия: 1.0.10.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: модуль pgcompacttable написан на языке Perl, поэтому для работы требует библиотеки Perl DBI с модулем поддержки PostgreSQL:

Схема размещения: не используется.

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

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

Модуль может быть запущен от имени любого пользователя ОС, в том числе на другом хосте (вариант с ключом --host).

Принцип работы pgcompacttable#

  1. Вызовом команды SET field_name = field_name выполняется фиктивное обновление всех записей таблицы, начиная с конца. Утилита проходит по таблице итеративно. На первом шаге обновляется несколько страниц (число считается динамически, максимум 5). Задержка перед обработкой каждого поля вычисляется как произведение --delay-ratio и длительности предыдущей итерации. Это позволяет контролировать нагрузку на БД.

    Внимание!

    Pangolin гарантирует, что все новые данные заполняют свободные места, начиная с начала таблицы. Таким образом, по прохождению всех строк таблица будет укомплектована.

  2. После обработки таблицы pgcompacttable перестраивает индексы за три шага:

    1. Создается новый индекс (команда CREATE INDEX с параметром CONCURRENTLY).

    2. Происходит замена (swap) имени старого индекса на новый (команда ALTER INDEX RENAME).

    3. Удаляется старый индекс (команда DROP INDEX с параметром CONCURRENTLY).

  3. На шаге ii может возникнуть длительная блокировка. Чтобы не блокировать индексы на длительное время, pgcompacttable выполняет множество коротких попыток замены индексов. Поведение управляется ключами:

    • --reindex-retry-count – максимальное количество попыток;

    • --reindex-retry-pause – задержка между попытками;

    • --reindex-lock-timeout – максимальное время выполнения переименования. При превышении количества попыток выводится WARNING-сообщение вида:

    Reindex <имя индекса>, lock has not been acquired

  4. После обработки всех строк запускается VACUUM для удаления пустых блоков с конца таблицы.

Запуск скрипта#

Для запуска скрипта требуются права superuser.

Рекомендуется запускать его от имени владельца кластера. В этом случае скрипт может использовать ionice в бэкенде PostrgreSQL для понижения приоритетов IO.

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

  • общие ключи;

  • ключи настройки соединения;

  • ключи работы с БД;

  • ключи настройки поведения инструмента.

Общие ключи:

  • -?, --help — вывести короткую справку об инструменте;

  • -m, --man — вывести полную справку об инструменте;

  • -V, --version — вывести версию инструмента;

  • -q, --quiet — включить тихий режим. В этом режиме выводятся только сообщения об ошибках и результирующее сообщение;

  • -v, --verbose — включить режим протоколирования. В этом режиме выводятся все сообщения.

Ключи настройки соединения:

  • -h HOST, --host HOST — имя или IP-адрес сервера базы данных;

  • -p PORT, --port PORT — порт для подключения к базе данных;

  • -U USER, --user USER — имя пользователя базы данных, под которым выполняется подключение. По умолчанию имя текущего пользователя, получаемое командой whoami;

  • -W PASSWD, --password PASSWD — пароль для указанного пользователя.

Примечание:

Инструмент pgcompacttable использует Perl модуль DBI для соединения с базой данных.

Если настройки соединения не передаются в ключах, то инструмент использует переменные окружения PGHOST, PGPORT, имя текущего пользователя и PGPASSWORD.

Если пароль не задан, инструмент попробует применить пароль (в порядке обращения):

  1. Из файла, указанного в переменной окружения PGPASSFILE.

  2. Из файла HOME/.pgpass.

Ключи работы с БД:

  • -a, --all – обработать все базы данных в кластере;

  • -d DBNAME, --dbname DBNAME – имя базы данных для обработки. По умолчанию – все базы данных, которыми владеет пользователь, под которым выполняется подключение;

  • -n SCHEMA, --schema SCHEMA – имя схемы для обработки. По умолчанию обрабатывается публичная (public) схема;

  • -N SCHEMA, --exclude-schema SCHEMA – имя исключаемой из обработки схемы;

  • -t TABLE, --table TABLE – имя таблицы для обработки. По умолчанию – все таблицы обрабатываемой схемы;

  • --tables-like 'LIKE expression' – SQL LIKE условие поиска таблиц для обработки. По умолчанию – все таблицы обрабатываемой схемы;

  • -T TABLE, --exclude-table TABLE – имя исключаемой из обработки схемы.

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

Все ключи, кроме --all, можно использовать несколько раз.

Ключи настройки поведения инструмента:

  • -R, --routine-vacuum – включить использование VACUUM. По умолчанию выключено;

  • -r, --no-reindex – выключить переиндексирование таблиц после их обработки;

  • --no-initial-vacuum – выключить запуск VACUUM перед обработкой таблицы;

  • -i, --initial-reindex – включить переиндексирование таблицы перед ее обработкой;

  • -s, --print-reindex-queries – выводить запросы на переиндексацию. Пример применения: выполнение самостоятельного переиндексирования после работы инструмента;

  • --reindex-retry-count – максимальное количество попыток замены имени индекса. По умолчанию 100;

  • --reindex-retry-pause – задержка между попытками реиндексации, в секундах. По умолчанию 1 секунда;

  • --reindex-lock-timeout – задержка перед переиндексацией после выполнения запросов ALTER TABLE, в миллисекундах. По умолчанию 1000 миллисекунд;

  • -f, --force – принудительная реорганизация всех таблиц в указанной базе данных;

  • -E RATIO, --delay-ratio RATIO – коэффициент для вычисления задержки между раундами. Задержка вычисляется как произведение времени выполнения прошлого раунда и указанного коэффициента. По умолчанию 2;

  • -Q Query, --after-round-query Query – SQL выражение, выполняемое после каждого раунда обработки базы данных;

  • -o COUNT, --max-retry-count COUNT – максимальное количество попыток повторной обработки в случае ошибки. По умолчанию 10.

Внимание!

Таблицы и индексы с «раздутием» меньше 20% считаются нормальными.

Фрагментация: pg_repack и pgcompacttable#

В процессе работы с Pangolin возникает table bloat — ситуация, при которой данные таблиц будут храниться неэффективно. Они фрагментируются, что приводит к ухудшению производительности и нерациональному использованию места на диске.

Основные причины фрагментации:

  • непредвиденный скачок запросов UPDATE или DELETE;

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

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

  • незавершенные PREPARED транзакции;

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

Доработка#

Доработка не проводилась.

Ограничения#

Ограничения отсутствуют.

Установка#

Установка не требуется. Расширение установлено по умолчанию.

Исполняемый файл pgcompacttable расположен в каталоге $PGHOME/bin/.

Настройка#

Настройка не требуется.

Использование модуля#

Пример использования#

Реорганизовать таблицу bloated_table:

pgcompacttable  --dbname -t bloated_table

Ссылки на документацию разработчика#

Утилита pgcompacttable: https://github.com/dataegret/pgcompacttable.