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

Версия: 1.0.10.

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

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

• perl-Time-HiRes;
• perl-DBI;
• perl-DBD-Pg;
• модуль pgstattuple.

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

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

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

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

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

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

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

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

Расширение pg_repack

Устройство расширения

Функциональность:

• удаление пустот в таблицах и индексах;
• восстановление физического порядка кластеризованных индексов.

Особенности функциональности:

• в отличие от стандартных средств PostgreSQL, VACUUM FULL и CLUSTER, реорганизация таблиц происходит без блокировки операций над обрабатываемыми данными;
• производительность сравнима с CLUSTER;
• для работы pg\_repack требуются права superuser;
• требования к реорганизуемым таблицам: наличие primary key или хотя бы одного уникального поля;
• во время работы утилиты с обрабатываемой таблицей можно производить любые манипуляции, кроме DDL.

Алгоритм работы:

1. Создается таблица для логирования операций, которые будут производиться над оригинальной таблицей во время выполнения алгоритма.
2. В оригинальной таблице создаются триггеры для операций Insert, Update и Delete. Триггеры записывают действия в созданную таблицу для логирования.
3. Создается новая таблица, копия оригинальной таблицы.
4. Копируются индексы из оригинальной таблицы в новую.
5. К новой таблице применяются все операции из таблицы логирования (см. пункт 1).
6. Выполняется swap оригинальной и новой таблицы.
7. Удаляется оригинальная таблица.

На шагах 1, 2, 6 и 7 для оригинальной таблицы применяется короткая Access Exclusive блокировка. На остальных - Access Share блокировка.

Недостатки pg_repack:

• отсутствие ограничений на работу с диском и возможности распределить нагрузку. Приводит к значительной нагрузке на диск и падению производительности при работе с большими таблицами;
• во время работы переполняется WAL. Это приводит к задержке между Active и Standby и может стать причиной отказа экземпляра PostgreSQL. При обработке больших таблиц рекомендуется отключить слот репликации на время работы утилиты;
• для реорганизации требуется дополнительное место на диске, равное сумме оригинальной и содержащей логи таблиц и размеру индексов.

Инструмент pgcompacttable

Устройство расширения

Скрипт реорганизации данных в «раздутых» таблицах (bloated tables), восстановления индексов и возврата дискового пространства.

Преимущества:

• не влияет на производительность базы данных;
• не использует Access Exclusive блокировки.

Отличия от pg_repack:

• не требует много места на диске:

  • таблицы обрабатываются «на месте»;
  • индексы перестраиваются друг за другом, от меньшего к большему;
  • максимальное требуемое место на диске равно размеру наибольшего индекса.

• таблицы обрабатываются с настраиваемыми задержками для предотвращения перегрузки IO и всплесков задержки репликации (см. ключ --delay-ratio);

• не может переносить таблицы и/или индексы в другое табличное пространство.

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

1. Выполняется фиктивное обновление всех записей таблицы, начиная с конца, командой SET field\_name = field\_name. Задержка перед обработкой каждого поля одинакова, что позволяет контролировать нагрузку на систему.

   Примечание:

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

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

   1. Создается новый индекс (команда CREATE INDEX с параметром CONCURRENTLY).
   2. Происходит замена (SWAP) имени старого индекса на новый (команда ALTER INDEX RENAME).
   3. Удаляется старый индекс (команда DROP INDEX с параметром CONCURRENTLY).

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

   • --reindex-retry-count – максимальное количество попыток;
   • --reindex-retry-pause – задержка между попытками;
   • --reindex-lock-timeout – максимальное время выполнения переименования. При превышении количества попыток выводится WARNING сообщение: Reindex <имя индекса>, lock has not been acquired.

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

Сравнение утилит Pangolin и встроенных в PostgreSQL

	CLUSTER/VACUUM	pg_repack	pgcompacttable
Нагрузка на диск и WAL меньше, чем у CLUSTER			+
Скорость работы выше по сравнению с CLUSTER		+
Позволяет отдельно работать с индексами	-	+	-
Позволяет указать задержку между этапами реорганизации	-	-	+
Может выполнять операцию CLUSTER		+	-
Не требует наличия основных (primary) или уникальных (unique) ключей	-	-	+
Не требует дополнительное дисковое пространство	-	-	+
Может переносить таблицы и/или индексы в другие табличные пространства	-	+	-

Работу обеих утилит могут затормозить:

• долгие транзакции;
• постоянная вставка и изменение данных.

Рекомендации по использованию:

• pgcompacttable – для безопасной реорганизации таблиц;
• pg_repack – для переноса таблицы в другое табличное пространство и использования CLUSTER без блокировки.

Доработка

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

Ограничения

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

Установка

Установка утилиты в документе «Руководство администратора», раздел «Сценарии администрирования», подраздел «Инструмент pgcompacttable».

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

Настройка

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

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

Подробное описание в документе «Руководство администратора», раздел «Сценарии администрирования», подраздел «Инструмент pgcompacttable».

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

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