Принудительное сжатие таблиц PostgreSQL

Для сжатия таблиц PostgreSQL можно воспользоваться стандартной утилитой vacuumdb, однако у неё есть недостатки:

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

Для устранения первого недостатка можно воспользоваться расширением pgstattuple, входящим в стандартную поставку большинства дистрибутивов PostgreSQL. С помощью одноимённой функции можно определить размер таблицы и процент её заполнения актуальными строками.

vacuumall.sh

Я написал скрипт vacuumall.sh, который проходится по всем базам данных и проверяет, установлено ли расширение pgstattuple в этой базе данных. Если расширение не установлено, оно устанавливается. Далее для каждой таблицы каждой базы данных скрипт определяет размер таблицы и процент её заполнения. Для таблиц объёмом больше мегабайта, заполненных менее чем на 90% выполняется сжатие и анализ. Если таблица имеет объём менее гигабайта, то выполняется полное сжатие и анализ с помощью запроса VACUUM FULL ANALYZE в расчёте на то, что эта операция заблокирует выполняющиеся запросы ненадолго. Для таблиц объёмом более гигабайта выполняется запрос VACUUM ANALYZE, который не блокирует выполнение запросов, однако обычно выполняется гораздо дольше. После обработки всех таблиц базы данных выполняется сжатие и анализ самой базы данных при помощи запроса VACUUM ANALYZE;.

Для того, чтобы операция сжатия и анализа не прерывалась, перед её выполнением снимается ограничение на длительность запроса в рамках текущего подключения. Делается это с помощью такого запроса:

SET SESSION statement_timeout = 0;

В начале скрипта имеются настройки, которые можно поменять:

  • skip_fillrate=90 - пропускать таблицы, заполненые данными более чем на 90%,
  • skip_size=1048576 - пропускать таблицы объёмом менее мегабайта,
  • full_vacuum_size=1073741824 - выполнять полное сжатие для таблиц размером менее гигабайта.

Эти настройки можно поменять, например, для того, чтобы обработать для начала самые крупные таблицы с самым низким уровнем заполнения. Например, таблицы размером более 100 мегабайт и с заполенением ниже 50%. Дале настройки можно приближать к приведённым выше и запустить скрипт для обработки остальных таблиц.

В процессе работы скрипт выводит сообщения о выполняемых им действиях. Некоторые операции могут быть достаточно длительными. Для того, чтобы узнать подробности, можно воспользоваться таким запросом:

$ SELECT * FROM pg_stat_progress_vacuum;

repackall.sh

Аналогичный скрипт repackall.sh позволяет обработать таблицы с помощью утилиты pg_pack и одноимённого расширения. Утилита pg_repack аналогична утилите pt-online-schema-change для MySQL. Действует она следующим образом:

  • создаёт новую пустую таблицу со структурой, аналогичной исходной,
  • создаёт вспомогательную таблицу для отслеживания изменений в исходной таблице и триггеры, отслеживающие изменения в исходной таблице и записывающие их во вспомогательную таблицу,
  • копирует из исходной таблицы все данные в новую таблицу,
  • создаёт индексы в новой таблице,
  • применяет к новой таблице изменения, накопившиеся во вспомогательной таблице,
  • удаляет триггеры и вспомогательную таблицу,
  • меняет таблицы местами,
  • удаляет старую таблицу.

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

В начале скрипта имеются настройки, которые можно поменять:

  • skip_fillrate=90 - пропускать таблицы, заполненые данными более чем на 90%,
  • skip_size=104857600 - пропускать таблицы объёмом менее 100 мегабайт,
  • pg_repack=/usr/lib/postgresql/9.6/bin/pg_repack - путь к утилите.

Если утилиты нет в репозитории используемой вами системы, то её можно собрать самостоятельно. Примеры сборки можно посмотреть в двух следующих статьях:

К сожалению, нельзя указать утилите снять ограничение на максимальное время выполнения запроса на уровне подключения. Поэтому на время запуска скрипта можно поменять глобальные настройки следующим образом:

ALTER SYSTEM SET statement_timeout = 0;
ALTER SYSTEM SET idle_in_transaction_session_timeout = 0;
SELECT pg_reload_conf();

Другие материалы