В PostgreSQL операции UPDATE и DELETE не удаляют строки физически. Вместо этого старая
версия записи помечается как "мёртвая" (dead tuple), а новая версия сохраняется отдельно. Это ключевая часть
реализации MVCC (Multiversion Concurrency Control), благодаря которой чтение не блокирует запись, а параллельные
транзакции могут работать практически независимо.
Однако у MVCC есть минус: старые версии строк продолжают занимать место в таблицах и индексах. Со временем это приводит к bloat — раздуванию данных, при котором PostgreSQL вынужден читать значительно больше страниц с диска. В результате увеличивается нагрузка на I/O и ухудшается производительность запросов. Для очистки неактуальных версий строк PostgreSQL использует механизм VACUUM.
Как работает VACUUM
Обычный VACUUM (без FULL) сканирует таблицу и помечает пространство, занятое мёртвыми строками, как доступное
для повторного использования. Физический размер файла таблицы при этом обычно не уменьшается: свободное место
остается внутри relation-файла и используется будущими INSERT или UPDATE.
Иногда PostgreSQL может вернуть часть места операционной системе — например, если после очистки полностью освобождаются страницы в конце файла таблицы. Но в большинстве случаев VACUUM лишь помечает место как доступное для повторного использования внутри самой таблицы.
VACUUM table_name;
Чем опасен VACUUM FULL
VACUUM FULL работает иначе: PostgreSQL полностью перестраивает таблицу, создавая новый компактный файл без мусора.
Такая операция возвращает дисковое пространство ОС, но требует AccessExclusiveLock и практически
полностью останавливает работу с таблицей на время выполнения. Чтение и запись становятся недоступны до
завершения операции.
VACUUM FULL table_name;
На больших таблицах VACUUM FULL может выполняться десятки минут или часы, поэтому запускать его на production без подготовки крайне рискованно. Вместо этого стоит настроить autovacuum.
Autovacuum: автоматическая очистка PostgreSQL
В реальных системах очисткой занимается autovacuum — фоновый процесс PostgreSQL, который автоматически запускает VACUUM и ANALYZE по мере необходимости.
Основной критерий запуска определяется формулой:
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number_of_tuples.
По умолчанию scale_factor равен 0.2 (20%) — т.е. для таблицы на 10 тысяч строк vacuum будет запущен после
накопления 2000 dead tuples (примерно).
При таких условиях, для таблицы на 50 миллионов строк vacuum запустится когда накопится примерно
10 000 000 мёртвых строк — проблемы с производительностью начнутся, скорее всего, даже раньше, чем наберётся
половина от этого количества. Для крупных таблиц scale_factor стоит уменьшить.
ALTER TABLE table_name
SET (autovacuum_vacuum_scale_factor = 0.01);
Ещё одно из решений — отключить процентный порог вообще и использовать фиксированное количество устаревших версий строк:
ALTER TABLE table_name
SET (
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 5000
);
После этого autovacuum будет запускаться после накопления примерно 5000 мёртвых строк независимо от общего размера таблицы. Это позволит избежать ситуации, когда VACUUM начнёт срабатывать слишком поздно после увеличения количества строк в таблице.
VACUUM ANALYZE и статистика planner
Очистка устаревших версий строк, которую делает VACUUM, очень полезна для работы системы. Но кроме этого, у PostgreSQL есть планировщик запросов которому важна статистика по данным внутри таблиц: количество строк, доля NULL-значений и многое другое. Для сбора этой статистики используется ANALYZE.
Если статистика устарела, PostgreSQL может начать выбирать неоптимальные планы выполнения запросов: например, использовать Sequential Scan вместо Index Scan или неправильно оценивать стоимость JOIN. Именно поэтому autovacuum автоматически запускает не только VACUUM, но и ANALYZE.
VACUUM ANALYZE table_name;
Почему autovacuum может не справляться
Часто может возникнуть проблема когда autovacuum не успевает очищать мусор быстрее, чем его генерирует приложение. По умолчанию PostgreSQL ограничивает интенсивность работы VACUUM, чтобы фоновая очистка не создавала слишком большую нагрузку на диск и не мешала выполнению пользовательских запросов. Для этого после определенного объема работы процесс autovacuum делает небольшие паузы. Это поведение регулируется двумя параметрами:
autovacuum_vacuum_cost_limit— сколько работы VACUUM может выполнить без остановки (по умолчанию 200)autovacuum_vacuum_cost_delay— длительность паузы после достижения лимита (по умолчанию 2ms)
Для небольших проектов значений по умолчанию, как правило, достаточно. Но в системах с большим количеством
UPDATE и DELETE, autovacuum слишком часто делает паузы и перестает успевать за ростом
неактуальных версий строк.
В этом случае лимит (cost_limit) можно увеличить до 1000 и выше, но нужно учитывать возможности сервера и диска потому что при этом возрастёт нагрузка на них.
HOT UPDATE и fillfactor
PostgreSQL может оптимизировать UPDATE-операции с помощью механизма HOT UPDATE (Heap Only Tuple).
При обычном UPDATE PostgreSQL создает новую версию строки и обновляет записи в индексах. Но если
изменяемые поля не участвуют в индексах, а на странице таблицы осталось свободное место, PostgreSQL может выполнить
HOT UPDATE — сохранить новую версию строки на той же странице без обновления индексов.
Это помогает существенно снизить нагрузку на индексы, уменьшает их раздувание и сокращает объем работы для
VACUUM.
Вероятность HOT UPDATE можно повысить через параметр fillfactor.
ALTER TABLE table_name
SET (fillfactor = 70);
При этом PostgreSQL будет заполнять страницы таблицы не полностью, а оставлять часть свободного места для
будущих UPDATE. Однако уменьшение fillfactor приведёт к тому, что таблица станет занимать больше
места на диске, поскольку данные хранятся менее плотно. Поэтому fillfactor обычно снижают только для таблиц в
которых очень часто обновляются данные. Для редко изменяемых таблиц такая настройка чаще всего не имеет смысла.
Bloat индексов: проблема, которую часто игнорируют
Много где можно встретить информацию про bloat таблиц, но часто упускают что существует ещё и index bloat. VACUUM удаляет из индексов ссылки на мёртвые версии строк, но не уменьшает их физический размер. Со временем индекс может вырасти в несколько раз относительно реального объема данных.
Небольшой index bloat — нормальное состояние PostgreSQL, но его существенное раздувание может привести к увеличению
времени выполнения UPDATE и INSERT за счёт:
- замедления работы запросов по индексам
- ухудшения эффективности использования кэша
- увеличения количества случайных чтений с диска
Обычный reindex блокирует запись в индекс/таблицу на время перестроения, это может вызвать проблемы в production,
поэтому чаще запускают запрос используя CONCURRENTLY, он будет дольше выполняться, но не будет блокировать
работу приложения:
REINDEX INDEX idx_index_name;
--или
REINDEX TABLE table_name;
-- Более долгий вариант, но без блокировок:
REINDEX TABLE CONCURRENTLY table_name;
Visibility Map и Index Only Scan
Vacuum влияет не только на очистку мусора, но и на производительность SELECT-запросов. Он обновляет visibility map — структуру, в которой PostgreSQL хранит информацию о страницах, полностью видимых всем транзакциям. Если страница помечена в visibility map как all-visible, PostgreSQL может читать данные исключительно из индекса, не обращаясь к heap-таблице. При больших объемах данных это существенно уменьшает I/O.
Типичные ошибки при работе с VACUUM
Полное отключение autovacuum
Отключенный autovacuum — это одна из самых опасных ошибок. Это может привести к:
- росту bloat
- ухудшению производительности запросов
- увеличению размера WAL
Самая большая проблема к которой это может привести — transaction ID wraparound (переполнение счётчика транзакций). При этом PostgreSQL вынужден будет запустить aggressive anti-wraparound vacuum, а при достижении критических значений может ограничивать создание новых транзакций, чтобы предотвратить потерю данных.
Долгие транзакции
VACUUM не сможет удалить версии строк, которые потенциально видимы хотя бы одной активной транзакции. Т.е. во время работы долгих транзакций мёртвые строки будут копиться несмотря на работу autovacuum.
В качестве одной из мер можно ограничить время неактивных транзакций, указав в конфиге
idle_in_transaction_session_timeout = '5min'.
Проблемы с replication slots
Replication slots — это механизм PostgreSQL, который не позволяет серверу удалять WAL-файлы и некоторые старые данные, пока они могут потребоваться реплике. Если репликация настроена неправильно или слот больше не используется, PostgreSQL продолжит удерживать старые данные и WAL, из-за чего VACUUM может не удалять некоторые устаревшие строки, что может привести к росту bloat и переполнению диска.
SELECT slot_name,
active,
restart_lsn
FROM pg_replication_slots;
Нехватка autovacuum workers
С увеличением количества таблиц в системе воркеры которые делают autovacuum могут стать "узким местом". По умолчанию
их количество autovacuum_max_workers = 3. Для крупных приложений этого часто не хватает. Одна огромная
таблица может занять воркера на длительное время, из-за чего другие таблицы перестают своевременно чиститься. Так же
как и в случае с autovacuum_vacuum_cost_limit, увеличивать значение по умолчанию надо с осторожностью
опираясь на возможности CPU и I/O сервера
При увеличении количества воркеров, важно учесть что для них действует общий
autovacuum_vacuum_cost_limit. Т.е. если увеличить количество воркеров, но оставить стандартный лимит,
то все воркеры начнут работать медленнее и скорость очистки крупных таблиц наоборот упадёт.
Как понять, что autovacuum не справляется
Вот несколько признаков по которым можно определить проблемы с autovacuum:
- постоянный рост размера таблиц
- рост времени sequential scan
- быстрое накопление WAL
- большое количество "мёртвых" строк (dead tuples)
SELECT relname,
n_live_tup,
n_dead_tup,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;