Перейти к основному содержимому

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.