🔬 Как бороться с раздуванием таблиц (bloat)? Раздувание (bloat) - это свободное пространство внутри страниц базы данных, которое образуется, когда AUTOVACUUM удаляет большое количество кортежей (tuples). Когда строка в таблице обновляется, Postgres не перезаписывает старые данные. Вместо этого он помечает старую версию строки (кортеж) как "мёртвую" и создаёт новую версию строки. Со временем, по мере обновления или удаления строк, пространство, занимаемое этими мёртвыми кортежами, может накапливаться. В какой-то момент AUTOVACUUM (или ручной VACUUM) удаляет мёртвые кортежи, оставляя свободное пространство внутри страниц, доступное для повторного использования. Но если накапливается большое количество мёртвых кортежей, могут оставаться большие объёмы свободного пространства - в худших случаях оно может занимать 99% всего пространства таблицы или индекса, или даже больше. Низкие уровни раздувания (скажем, ниже 40%) не следует считать проблемой, в то время как высокие уровни определённо являются проблемой, так как приводят к негативным последствиям: ❌ Повышенное использование дискового пространства; ❌ Больше операций ввода-вывода для запросов на чтение и запись; ❌ Снижение эффективности кеширования (как буферного пула, так и файлового кеша ОС); ❌ Как следствие - ухудшение производительности запросов. Уровень раздувания индексов и таблиц следует проверять регулярно. Рекомендации для использования в системах мониторинга: ✅ Нет смысла запускать проверочные запросы для определения размера таблиц часто (например, каждую минуту), так как уровень раздувания меняется не так быстро; ✅ Для больших баз данных выполнение запроса может занять много времени, до нескольких секунд, поэтому может потребоваться настроить частоту проверок и параметр statement_timeout. Подходы для более точного определения уровня раздувания: ✅ Запросы на основе расширения pgstattuple (требует установки этого расширения); ✅ Проверка размеров объектов БД на клоне базы. Запускать VACUUM FULL ресурсоёмко и он блокирует запросы, поэтому не для продакшена. Делаем это на клоне и сравниваем "до/после". К сожалению, в базах данных, где происходит много операций UPDATE и DELETE, состояние индексов неизбежно ухудшается со временем. Это означает, что индексы необходимо регулярно перестраивать. Рекомендации: ✅ Используйте REINDEX CONCURRENTLY для неблокирующего перестроения раздутого индекса; ✅ Помните, что REINDEX CONCURRENTLY удерживает горизонт xmin во время выполнения. Это влияет на способность AUTOVACUUM очищать недавно умершие кортежи во всех таблицах и индексах; ✅ Предпочтительно использовать версии Postgres 14+, поскольку в PG14 btree-индексы были значительно оптимизированы, чтобы деградировать гораздо медленнее при нагрузках на запись. Некоторый уровень раздувания таблиц можно считать не таким уж плохим явлением, потому что он увеличивает шансы на оптимизированные UPDATE - HOT - обновления (Heap-Only Tuples). Однако, если уровень вызывает беспокойство, рассмотрите возможность использования pg_repack для перестроения таблицы без длительных эксклюзивных блокировок. Альтернатива pg_repack - pg_squeeze. Проактивное предотвращение раздувания: ✅ Настройте AUTOVACUUM; ✅ Не допускайте ненужных длительных транзакций (например, > 1 часа), ни на основном сервере, ни на репликах с включённым параметром hot_standby_feedback; ✅ Если используется Postgres 13, то рассмотрите возможность обновления до 14+, чтобы воспользоваться оптимизациями btree-индексов; ✅ Партиционируйте большие таблицы (100+ ГБ); ✅ Не используйте массовые UPDATE и DELETE, всегда работайте пакетами (длительностью не более 1-2 секунд); ✅ Убедитесь, что AUTOVACUUM оперативно очищает мёртвые кортежи, или выполняйте VACUUM вручную, когда необходимы массовые изменения данных. На этом все! До связи! #pgmonitor