PostgreSQL bloat на VDS: когда нужен VACUUM FULL и как вернуть место безопасно

PostgreSQL bloat — это не «ошибка PostgreSQL», а нормальное следствие MVCC-модели. Проблемой он становится тогда, когда мертвые версии строк и лишние страницы перестают эффективно переиспользоваться, а база продолжает занимать на диске намного больше, чем реальные полезные данные.

На VDS это особенно заметно: диск ограничен тарифом, IOPS не бесконечны, а большой rewrite таблицы может упереться в свободное место, WAL, репликацию или ночное окно обслуживания. Поэтому цель администратора — не просто запустить «волшебный vacuum», а понять масштаб bloat, выбрать безопасный метод и не повторить проблему через неделю.

Главная мысль: VACUUM FULL действительно возвращает место операционной системе, но делает это ценой эксклюзивной блокировки и полной перезаписи таблицы. Его стоит запускать точечно, после замеров и проверки свободного места.

Что такое bloat в PostgreSQL и почему он появляется

PostgreSQL не перезаписывает строку «на месте» при UPDATE. Он создает новую версию строки, а старая остается в таблице до тех пор, пока ее можно безопасно убрать. При DELETE строка тоже не исчезает физически сразу: она становится мертвой версией, которую позже может очистить vacuum.

Такая модель дает хорошую конкурентность чтения и записи, но требует регулярной уборки. Обычный VACUUM помечает место внутри таблицы как доступное для повторного использования. Однако файл таблицы на диске обычно не уменьшается. Если таблица занимала 80 ГБ, а после массового удаления живых данных осталось 20 ГБ, обычный vacuum позволит будущим вставкам использовать освобожденные страницы, но df -h может по-прежнему показывать около 80 ГБ.

Именно это часто удивляет после очистки логов, очередей, временных заказов или старых событий аналитики: DELETE прошел, VACUUM прошел, а место в файловой системе почти не вернулось. Для физического уменьшения объекта нужны операции, которые переписывают таблицу или индекс: VACUUM FULL, CLUSTER, перенос данных в новую таблицу, REINDEX для индексов или инструменты вроде pg_repack.

Быстрая диагностика: есть ли проблема на самом деле

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

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

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

Добавим размер таблиц и индексов. Часто оказывается, что «таблица раздулась» на самом деле означает «индексы занимают больше самой таблицы», особенно после частых обновлений индексируемых колонок.

SELECT n.nspname AS schema_name, c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS table_size, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size, s.n_live_tup, s.n_dead_tup, s.last_autovacuum
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

Если доступно расширение pgstattuple, можно получить более точную оценку bloat для конкретной подозрительной таблицы. Но не запускайте такие проверки бездумно на всех больших объектах: они читают данные и могут создать заметную I/O-нагрузку.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('public.orders');

Для индексов полезен pgstatindex из того же расширения, но это уже точечная диагностика. Если база небольшая, проверять проще. Если это продакшен на VDS с диском под высокой нагрузкой, сначала снимите общую картину и выберите 2–3 самых подозрительных объекта.

Проверьте долгие транзакции: они могут ломать всю уборку

Одна из частых причин, почему autovacuum вроде бы работает, а bloat растет, — долгие транзакции. Пока транзакция открыта, PostgreSQL обязан сохранять старые версии строк, которые теоретически могут быть ей видны. Это касается не только явного BEGIN, но и зависших сессий приложения, долгих отчетов, неудачных миграций, забытых консольных клиентов и состояния idle in transaction.

SELECT pid, usename, application_name, state, now() - xact_start AS xact_age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '15 minutes'
ORDER BY xact_start;

Если видите сессии в состоянии idle in transaction, сначала разберитесь с ними. Запускать VACUUM FULL поверх системной причины — плохая стратегия: вы временно освободите место, но через несколько дней получите ту же картину. Для приложений полезно настроить idle_in_transaction_session_timeout, а для тяжелых отчетов — отдельные реплики или лимиты по времени выполнения.

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

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

Обычный VACUUM, autovacuum и VACUUM FULL: в чем разница

autovacuum — штатный механизм PostgreSQL, который запускает VACUUM и ANALYZE автоматически. Он нужен не только для освобождения места внутри таблиц, но и для обновления статистики планировщика, а также для защиты от wraparound по transaction ID. Отключать его на постоянной основе почти всегда ошибка.

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

VACUUM FULL работает иначе: он создает компактную новую копию таблицы и перестраивает связанные индексы. Поэтому после завершения размер файла действительно уменьшается, а место возвращается файловой системе. Цена — блокировка ACCESS EXCLUSIVE: пока идет операция, таблица недоступна для чтения и записи. Для большой таблицы это может означать минуты или часы простоя конкретной функциональности.

Также VACUUM FULL генерирует существенный объем записи на диск и WAL. На VDS это важно по трем причинам: нужно свободное место под новую копию объекта, может вырасти задержка диска для остальной базы, а репликация или архивирование WAL должны успеть переварить поток изменений.

Когда VACUUM FULL оправдан

VACUUM FULL уместен не при каждом bloat. Его стоит рассматривать, когда нужно именно вернуть место ОС или радикально уменьшить объект после разовой операции. Например, вы удалили 70–90% строк из большой таблицы, очистили старые партиции неправильным способом, импортировали и удалили временный массив данных или долго жили с неработающим autovacuum.

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

Не запускайте VACUUM FULL на всю базу «для профилактики». Команда без указания таблицы пройдет по всем таблицам текущей базы, создаст длинные блокировки и большую I/O-нагрузку. Для продакшена почти всегда правильнее работать точечно: сначала самая проблемная таблица, потом следующая, с контролем времени и метрик.

VACUUM FULL;

Команда выше приведена как пример того, чего на продакшене лучше избегать без четкого плана. Безопаснее указать конкретную таблицу:

VACUUM (FULL, VERBOSE, ANALYZE) public.orders;

Чек-лист перед запуском на VDS

Перед тем как переписывать большую таблицу, проверьте не только PostgreSQL, но и окружение сервера. В маленьких инсталляциях авария часто выглядит банально: администратор удалил данные, увидел большой объект, запустил VACUUM FULL, а в процессе закончился диск из-за новой копии таблицы и WAL.

• Проверьте размер таблицы и индексов через pg_total_relation_size. Оценивайте не только heap, но и связанные индексы.

• Проверьте свободное место в файловой системе с PGDATA, каталогом WAL и tablespace, если они разнесены.

• Убедитесь, что есть свежая резервная копия и вы проверяли восстановление хотя бы на тестовой базе. Для отдельной темы бэкапов пригодится разбор резервного копирования PostgreSQL через pgBackRest.

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

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

• Проверьте репликацию и архивирование WAL, если они используются. После rewrite может появиться заметный lag.

Оценить размер конкретного объекта можно так:

SELECT pg_size_pretty(pg_relation_size('public.orders')) AS table_size, pg_size_pretty(pg_indexes_size('public.orders')) AS indexes_size, pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size;

Если таблица весит 100 ГБ, а свободно 30 ГБ, VACUUM FULL может не поместиться. Точный запас зависит от версии PostgreSQL, индексов, WAL и tablespace, но практическое правило простое: для больших объектов нужен очень комфортный запас. Если его нет, сначала расширяйте диск VDS, переносите таблицу в другой tablespace, удаляйте ненужные файлы вне базы или выбирайте другой метод.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Как провести VACUUM FULL с минимальным риском

План работ лучше оформить как короткий runbook. Сначала зафиксируйте исходные размеры, затем проверьте блокирующие транзакции, после этого запускайте операцию в согласованное окно. Если приложение использует пул соединений, разумно временно перевести часть функциональности в maintenance mode или остановить воркеры, которые пишут в целевую таблицу.

Снимите исходные размеры:

SELECT now() AS checked_at, pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size;

Проверьте активные запросы к таблице. Универсально и идеально определить все будущие блокировки нельзя, но можно увидеть текущие тяжелые операции и долгие транзакции:

SELECT pid, state, now() - query_start AS query_age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database() AND query ILIKE '%orders%'
ORDER BY query_start;

Запустите точечный VACUUM FULL:

VACUUM (FULL, VERBOSE, ANALYZE) public.orders;

После завершения снова проверьте размер:

SELECT now() AS checked_at, pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size;

Для наблюдения за прогрессом в современных версиях PostgreSQL смотрите представления прогресса. Для обычного vacuum используется pg_stat_progress_vacuum, а операции переписывания таблиц, включая VACUUM FULL, обычно видны через pg_stat_progress_cluster.

SELECT pid, datname, relid::regclass AS relation_name, phase, heap_tuples_scanned, heap_tuples_written
FROM pg_stat_progress_cluster;

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

SELECT a.pid, a.state, l.mode, l.granted, a.wait_event_type, a.wait_event, now() - a.query_start AS age, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'public.orders'::regclass
ORDER BY l.granted, a.query_start;

Отменять VACUUM FULL посередине можно, но это не бесплатная операция: уже сделанная работа будет потеряна, временное место освободится после отката, а вы потратите окно обслуживания. Поэтому лучше заранее оценить размер и скорость на staging или на копии базы, если объект действительно большой.

Что делать, если места не хватает

Если диск почти заполнен, не пытайтесь «втиснуть» VACUUM FULL на удачу. PostgreSQL при нехватке места может прервать операцию, а база останется в том же раздутом состоянии, плюс вы получите стресс из-за заполненного WAL или временных файлов.

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

Второй вариант — перенести крупную таблицу или индексы в отдельный tablespace на дополнительный диск. Такой подход требует аккуратности, но позволяет развести нагрузку и место. Он полезен, когда один объект стал главным потребителем диска.

Третий вариант — использовать логическую миграцию данных в новую таблицу с батчами. Например, создать новую таблицу, переносить живые строки частями, затем переключить имена в короткое окно блокировки. Это сложнее, особенно с foreign key, триггерами и последовательностями, зато дает контроль над темпом записи.

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

pg_repack как альтернатива VACUUM FULL

pg_repack создает новую компактную копию таблицы, отслеживает изменения через служебные механизмы и затем быстро переключает объект. Для приложения это обычно намного менее болезненно, чем долгий VACUUM FULL. Но инструмент не отменяет физику: ему нужно место под копию, он генерирует WAL, требует установки расширения и прав, а также должен соответствовать версии PostgreSQL.

Типовой запуск для одной таблицы выглядит так:

pg_repack --dbname=appdb --table=public.orders

Перед первым использованием расширение нужно создать в базе:

CREATE EXTENSION IF NOT EXISTS pg_repack;

У pg_repack есть важные ограничения. На таблице должен быть primary key или подходящий unique index. Финальная блокировка короткая, но она есть. Кроме того, на очень активных таблицах служебный backlog изменений может расти, поэтому запуск лучше проводить в период умеренной нагрузки.

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

Настраиваем autovacuum, чтобы bloat не вернулся

После успешного освобождения места важно исправить причину. Если autovacuum не успевает за нагрузкой, таблица снова раздуется. Глобальные параметры зависят от объема RAM, CPU, диска и профиля записи, но часто полезно точечно настроить самые горячие таблицы. Больше практики по этой теме есть в материале про настройку autovacuum и индексов PostgreSQL.

По умолчанию vacuum запускается после накопления порога, который зависит от autovacuum_vacuum_threshold и autovacuum_vacuum_scale_factor. Для маленьких таблиц это не проблема, а для таблицы на сотни миллионов строк scale factor может означать, что PostgreSQL будет ждать слишком много изменений перед уборкой. Для таких таблиц разумно снижать scale factor на уровне таблицы.

ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 5000, autovacuum_analyze_scale_factor = 0.01, autovacuum_analyze_threshold = 5000);

Для таблиц с интенсивными обновлениями иногда помогает fillfactor. Он оставляет свободное место на страницах, повышая шанс HOT update, когда обновление не требует менять индексы. Это снижает индексный bloat и уменьшает нагрузку на vacuum. Но fillfactor увеличивает исходный размер таблицы, поэтому его не нужно ставить везде.

ALTER TABLE public.orders SET (fillfactor = 80);

Изменение fillfactor влияет на новые версии строк. Чтобы полностью применить его к существующей таблице, нужен rewrite: например, VACUUM FULL, CLUSTER или pg_repack. Поэтому удобно совмещать изменение fillfactor с плановой операцией уплотнения.

Следите за частотой autovacuum после изменений:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count, last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

Если n_dead_tup быстро растет, а autovacuum запускается редко, снижайте пороги. Если autovacuum запускается, но не успевает, смотрите лимиты стоимости: autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay, количество воркеров autovacuum_max_workers и общую I/O-нагрузку. На VDS не стоит бездумно выкручивать параметры в максимум: агрессивный vacuum может конкурировать с пользовательскими запросами за диск.

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

Очень частый источник PostgreSQL bloat — таблицы событий, логов, аудита, статистики и очередей, где данные живут ограниченное время. Если каждый день удалять миллионы старых строк через DELETE, вы постоянно создаете мертвые версии и нагружаете vacuum. Лучший подход — партиционирование по времени и удаление старых партиций целиком.

Удаление партиции через DROP TABLE или отсоединение через DETACH PARTITION не создает миллионы dead tuples. Файл партиции удаляется или становится отдельной таблицей, а место возвращается намного быстрее. Для таблиц с retention-политикой это архитектурное решение часто эффективнее любых настроек autovacuum.

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

Индексы тоже раздуваются

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

SELECT indexrelid::regclass AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relid = 'public.orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

Не удаляйте индексы только потому, что idx_scan равен нулю за короткий период. Статистика могла сбрасываться, а индекс может быть нужен редкому, но важному отчету или ограничению. Сначала проверьте период наблюдения, запросы приложения и constraints.

Для перестройки индекса без долгой блокировки записи часто используют:

REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

У concurrent-операций тоже есть требования и фазы ожидания, но для продакшена они обычно мягче, чем полная блокировка. Если же вы все равно делаете VACUUM FULL таблицы, отдельный REINDEX ее обычных индексов чаще всего не нужен: они будут пересозданы в процессе rewrite.

Особенности эксплуатации на VDS

На выделенном сервере с большим запасом диска и отдельным массивом под WAL можно позволить себе более широкие маневры. На VDS обычно важнее аккуратность: один большой rewrite может повлиять не только на PostgreSQL, но и на веб-приложение, фоновые очереди, резервное копирование и мониторинг.

Следите за iowait, задержками диска, размером WAL и lag реплик. Если база и приложение живут на одном VDS, во время VACUUM FULL может вырасти TTFB сайта, начнут копиться задания очереди, а пул соединений будет занят ожидающими запросами. Иногда проще остановить часть воркеров на 20 минут, чем потом разбирать лавину таймаутов.

Если у вас включены файловые снапшоты на уровне платформы, помните: интенсивная перезапись большой таблицы может увеличить объем copy-on-write на стороне хранилища. Это не всегда видно внутри гостевой ОС, но может сказаться на скорости. Для критичных баз лучше иметь отдельный план резервного копирования PostgreSQL, а снапшоты использовать как дополнительный уровень, а не единственный способ восстановления.

Не забывайте про WAL. После крупного VACUUM FULL архив WAL может резко вырасти. Если архивирование настроено на отдельный диск или во внешнее хранилище, проверьте квоты и скорость выгрузки. Если реплика отстала, не удаляйте WAL вручную из pg_wal: это прямой путь к поломке восстановления и репликации.

Мини-runbook для аварийной ситуации

Если диск уже близок к 100%, действуйте осторожно. Цель — стабилизировать систему, а не добить ее тяжелой операцией. Сначала определите, что именно растет: таблицы, индексы, WAL, логи PostgreSQL, временные файлы, дампы или файлы приложения. Bloat — частая, но не единственная причина заполнения диска.

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;

SELECT relid::regclass AS relation_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Если виноват WAL, сначала проверьте replication slots и архивирование. Если виноваты логи, настройте ротацию. Если виновата одна таблица после массового удаления, оцените свободное место и только потом выбирайте метод уплотнения. При нехватке места безопаснее временно расширить диск, чем запускать VACUUM FULL впритык.

Если нужно быстро остановить рост bloat, найдите и завершите явно зависшие транзакции, но не убивайте процессы вслепую. Завершение backend может откатить большую транзакцию, что тоже даст нагрузку. Сначала посмотрите запрос, пользователя, приложение и возраст транзакции.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - xact_start > interval '1 hour';

Команда выше — пример, а не рекомендация нажимать Enter без проверки. В продакшене лучше сначала вывести список кандидатов, связаться с владельцем приложения и понять последствия.

Частые ошибки

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

• Ориентироваться только на n_dead_tup. Это оценка. Для решения о rewrite нужны размеры объектов, история роста и понимание нагрузки.

• Делать массовый DELETE вместо удаления партиций. Для time-series и логов это создает много лишней работы.

• Забывать про WAL и реплики. Большая перезапись таблицы может заполнить архив или сильно отставить standby.

• Лечить симптом без настройки autovacuum. Если причина не устранена, bloat вернется.

• Не проверять свободное место. VACUUM FULL требует места для новой копии объекта и сопутствующих записей.

Практический вывод

VACUUM FULL — полезный, но тяжелый инструмент. Он хорошо подходит для точечного возврата места после крупного удаления или исправления сильно раздутой таблицы, когда допустима блокировка. Но это не регулярная профилактика и не замена нормальному autovacuum.

Правильная последовательность такая: сначала найти крупные и подозрительные таблицы через pg_stat_user_tables и размеры объектов, затем проверить долгие транзакции, оценить свободное место и WAL, выбрать метод уплотнения, провести работу в окно обслуживания и после этого настроить autovacuum, индексы, fillfactor или партиционирование. Для больших активных таблиц смотрите в сторону pg_repack, потому что короткая финальная блокировка часто важнее простоты штатной команды.

На VDS особенно ценится предсказуемость. Чем меньше у сервера запас по диску и I/O, тем важнее предварительные замеры и поэтапный план. Тогда PostgreSQL bloat перестает быть внезапной аварией и становится обычной задачей обслуживания базы.