PostgreSQL - pg_repack
ranebullГлавная проблема PostgreSQL – взаимодействие с дисковой подсистемой (с) Конфуций
При частом обновлении таблиц в ней скапливается много мертвых кортежей (dead_tuples). При запуске процесса vacuum c параметром full эти кортежи удаляются, и место возвращается операционной системе (ОС). Но обычно количество неактуальных кортежей увеличивается, приводя к росту таблицы, увеличению размера БД и ухудшению производительности.
Самый очевидный вариант в данном случае - использование стандартного механизма PostgreSQL, а именно VACUUM. Без ключа FULL неактуальные кортежи удаляются, но не передаются в управление ОС, а резервируются БД под новый набор данных. Запуск команды VACUUM FULL копирует все данные в новую таблицу на диск без dead_tuples. Здесь нужно быть осторожнее, если таблица занимает больше места, чем имеется свободного места на диске, то будет больно. На время работы vacuum full запрашивается исключительная блокировка таблицы (ACCESS EXCLUSIVE LOCK), что блокирует любые манипуляции, которые вы хотите провести с этой таблицей (SELECT, UPDATE, INSERT и так далее). Это очень плохо, в случае, если ваша БД развернута в проде и вы не можете позволить себе большой даунтайм, потерять данные на время блокировки и прочее.
Если не использовать VACUUM FULL, то ещё есть вариант - максимально облегчить БД и сделать дамп с использованием утилиты pg_dump. В таком случае, dead_tuples будут отсутствовать в дампе, и вы сможете восстановить БД за адекватное время, чтобы минимизировать даунтайм.
Утилита pg_basebackup в данном случае никак эту ситуацию не решит, так как она производит копирование данных из БД на уровне файловой системы, то есть на новой БД вы получите такую же распухшую базу.
Инструмент, который может помочь с этим вопросом - pg_repack. Цитата из официальной документации:
pg_repack — утилита и расширение Postgres Pro Enterprise для реорганизации таблиц
pg_repack запрашивает блокировку ACCESS EXCLUSIVE LOCK только во время старта работы (анализ таблицы) и окончания работы (замена старой таблицы на новую). Во время работы утилиты запросы вида SELECT, UPDATE и INSERT будут отрабатывать с некоторой задержкой – это связано с тем, что запрашивается построчная блокировка таблицы. Здесь тоже нужно быть внимательным, так как приложение, которое работает с БД может использовать все треды для работы с БД, а потом перестать функционировать, потому что свободные потоки закончились, и приложение больше не в состоянии обслуживать новых клиентов) .
После копирования основного содержимого таблицы pg_repack начинает создавать индексы. У утилиты есть возможность указания количества потоков (ключ -j), с которым будут создаваться индексы. Стоит помнить, что 1 индекс = 1 поток + некоторое количество RAM, при индексах большого размера следует аккуратно подходить к выбору оптимального количества потоков. Утилита показывает лучшую производительность и меньший даунтайм по сравнению со стандартным механизмом vacuum, поэтому может использоваться на продуктивных серверах.
Внимание! При чистке таблиц следует обращать внимание на работу autovacuum, так как он может заблокировать pg_repack.
Чтобы получить это расширение на PostgreSQL, нужно собрать его из исходников, после сборки активировать расширение в нужной БД с помощью команды:
CREATE EXTENSION pg_repack;
Для Debian-based дистрибутивов есть уже собранный пакет postgresql-11-repack в официальных репозиториях.
Если БД в докере, то лучше дописать/написать Dockerfile, который собирает и добавляет это расширение. Если процессы компиляции/установки производятся руками, то, пожалуйста, не оставляйте компиляторы и прочий набор инструментов для сборки после установки, это небезопасно.
В случае использования любой репликации (асинхронной или синхронной) нужно будет собрать расширение из исходников на каждом узле, а активировать расширение нужно только на ведущем сервере.
Примеры запуска:
# Запуск в 10 потоков на таблице table_with_dead_tuples БД prod с использованием логина user pg_repack -d prod -U user -t table_with_dead_tuples -j 10 # Вариант для самоубийц (запускаем на всей БД, когда закончится, тогда закончится) pg_repack -d prod -U user -j 10
Каким образом найти таблицы, в которых много dead_tuples?
В моём случае я использовал расширение pgstattuple, которое входит в стандартную поставку PostgreSQL, пришлось только дополнительно активировать расширение с помощью команды
CREATE EXTENSION pgstattuple;
Статистику по таблице можно собрать с помощью следующего запроса:
user=# SELECT * FROM pgstattuple('table_with_dead_tuples');
Для интерпретации результатов можно воспользоваться официальной документацией по расширению.
Остальные способы получить статистику по кортежам будут указаны в полезных ссылках в конце статьи.
В итоге мы получаем решение, которое легко интегрируется с БД, работает с репликацией, работает прозрачно и выполняет свою работу на 100%.
Полезные ссылки:
Регламентные задачи обслуживания базы данных