Обнаружение и устранение раздувания базы данных в PostgreSQL с помощью VACUUM

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

Решение зависит от того, какой тип пространства вам нужно освободить. Стандартный VACUUM делает пространство мертвых кортежей повторно используемым внутри PostgreSQL. VACUUM FULL, REINDEX и такие инструменты, как pg_repack, могут физически сжимать объекты, но они сопряжены с разными затратами на блокировки и операции.

Понимание MVCC и раздувания в PostgreSQL

Чтобы эффективно бороться с раздуванием, необходимо сначала понять его первопричину. Архитектура MVCC PostgreSQL гарантирует, что читатели никогда не блокируют писателей и наоборот. Когда строка обновляется, PostgreSQL не перезаписывает старую строку; он вставляет новую версию и помечает старую версию как мертвую. Аналогично, удаленные строки оставляют после себя мертвые кортежи.

Раздувание происходит, когда эти мертвые кортежи накапливаются быстрее, чем процессы обслуживания (Autovacuum или ручной VACUUM) могут их очистить или повторно использовать пространство.

Последствия раздувания базы данных

Раздувание влияет на производительность в нескольких ключевых областях:

Увеличение использования дискового пространства: Мертвые кортежи занимают физическое место, заставляя таблицы и индексы потреблять больше памяти, чем необходимо.
Замедление последовательного сканирования: Движок базы данных должен считывать мертвые кортежи во время сканирования таблиц, увеличивая нагрузку на ввод-вывод.
Неэффективное индексирование: Раздутые индексы имеют больший размер, что приводит к большему количеству чтений с диска для обхода структуры индекса.
Напрасные усилия Autovacuum: Autovacuum вынужден работать усерднее и дольше для очистки таблиц, потенциально задерживая критическое обслуживание других таблиц.

Обнаружение раздувания базы данных

Обнаружение начинается со статистики каталога и размеров объектов. Воспринимайте простые запросы как триаж, а не точное измерение раздувания, потому что макет таблицы, fillfactor, данные TOAST и индексы влияют на реальное число.

1. Выявление раздутых таблиц с помощью `pg_stat_user_tables`

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

Ключевые метрики для мониторинга:

n_dead_tup: Количество мертвых кортежей.
last_autovacuum, last_vacuum: Когда в последний раз выполнялось обслуживание.

Простые подсчеты полезны, потому что они показывают, где нарастает давление вакуума. Большое количество n_dead_tup по сравнению с n_live_tup является веской причиной для более тщательной проверки таблицы.

Пример запроса (Поиск кандидатов на вакуумирование):

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

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY
    n_dead_tup DESC
LIMIT 10;

2. Оценка раздутых индексов

Раздувание часто существенно влияет на индексы. Начните с поиска необычно больших индексов в таблицах с высокой активностью изменений:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Большой размер сам по себе не доказывает раздувание, но он указывает, какие индексы стоит проверить с помощью более глубоких инструментов, таких как расширение pgstattuple или запрос на раздувание вашей платформы мониторинга.

Управление раздуванием: Роль VACUUM

VACUUM является основным инструментом PostgreSQL для освобождения пространства от мертвых кортежей и обновления карт видимости.

Autovacuum: Первая линия защиты

По умолчанию PostgreSQL автоматически запускает процессы autovacuum. Autovacuum выполняет стандартный VACUUM (который помечает пространство как повторно используемое внутри, но не возвращает его операционной системе), когда достигается пороговое значение. Этот порог определяется autovacuum_vacuum_scale_factor (по умолчанию 0,2 или 20% от размера таблицы) плюс autovacuum_vacuum_threshold (по умолчанию 50 кортежей).

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

-- Пример: Установка агрессивных параметров autovacuum для критической таблицы 'orders'
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 100);

Стандартный VACUUM против VACUUM FULL

Существует два основных режима очистки:

Стандартный `VACUUM`

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

VACUUM table_name;
VACUUM (VERBOSE) table_name; -- Показывает статистику по очищенным кортежам

`VACUUM FULL` (Инструмент освобождения пространства)

VACUUM FULL перезаписывает весь файл таблицы, чтобы физически удалить мертвые кортежи и вернуть пространство операционной системе.

Предупреждение: VACUUM FULL требует блокировки ACCESS EXCLUSIVE для таблицы на время своего выполнения. Это означает, что все операции чтения и записи для этой таблицы будут заблокированы до завершения VACUUM FULL. Используйте эту команду с осторожностью для больших, активно используемых таблиц.

VACUUM FULL table_name;

Лучшая практика: Используйте VACUUM FULL только в случае сильного раздувания, когда вы можете позволить себе время простоя, или в запланированные окна обслуживания.

Продвинутые стратегии борьбы с раздуванием

Когда VACUUM FULL слишком разрушителен, существуют альтернативные методы для освобождения пространства с меньшим временем простоя.

1. Перестроение индексов

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

REINDEX INDEX CONCURRENTLY index_name;

Обычный REINDEX INDEX index_name; выполняется быстрее, но требует более строгих блокировок, поэтому используйте его в окно обслуживания.

2. Использование `pg_repack` для онлайн-перезаписи таблиц

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

Как работает pg_repack:

Создается временная таблица (_new), зеркально отражающая оригинал.
Непрерывно отслеживаются изменения в оригинальной таблице с помощью триггеров.
Выполняется финальная синхронизированная копия и замена.

Установка и использование (Типичный процесс):

Установите расширение и пакет CLI для вашей версии PostgreSQL, включите расширение в базе данных, затем запустите команду pg_repack из оболочки:

CREATE EXTENSION pg_repack;

pg_repack --table=public.critical_table --dbname=mydb

Примечание о pg_repack: Хотя он значительно уменьшает блокировки по сравнению с VACUUM FULL, он все равно требует создания триггеров и копирования данных, что временно потребляет дополнительный ввод-вывод и хранилище.

Вывод

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

Мониторинг: Регулярно проверяйте pg_stat_user_tables на высокие значения n_dead_tup.
Настройка Autovacuum: Для активных таблиц уменьшите scale factor, чтобы стандартный VACUUM запускался чаще.
Восстановление: Если раздувание незначительное, стандартного VACUUM table_name может быть достаточно, если активность таблицы снизится.
Агрессивное восстановление (Низкое время простоя): Используйте pg_repack для онлайн-перезаписи структуры таблицы.
Экстренное восстановление (Высокое время простоя): Используйте VACUUM FULL только как крайнюю меру, когда время простоя приемлемо, так как он удерживает исключительные блокировки.

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