Причины возникновения bloat в PostgreSQL

Bloat (раздувание, рост) таблиц — это когда таблица или индекс занимает на диске гораздо больше места, чем необходимо. Эта проблема сильно влияет на производительность PostgreSQL.

Давайте разберемся откуда появляется нежелательный рост. PostgreSQL использует систему под названием MVCC (Multi-Version Concurrency Control) с целью того, чтобы несколько пользователей могли одновременно работать с данными и не мешать друг другу. Когда пользователь обновляет или удаляет строку PostgreSQL не стирает старую запись сразу, помечает ее как “мертвую” и делает новую рядом. Такие мертвые записи занимают место и когда их становится слишком много, мы говорим, что таблица “разрослась” (bloat).

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

Роль и возможности стандартного VACUUM при очистке данных

VACUUM и его автоматическая версия под названием “Autovacuum” — это дефолтный уборщик PostgreSQL, главной задачей которого является уборка старых записей.

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

Пример работы VACUUM на практике

Допустим у вас есть таблица размером 1 ГБ. В результате большого количества операций delete и update в ней образовалось много устарелых, ненужных записей.

Выполняем команду VACUUM my_table.
PostgreSQL помечает 500 МБ свободного места, но размер файла таблицы не меняется.
В таблицу добавляются новые строки (insert), заполняют 500 МБ свободного места, и не увеличивают размер файла пока это место не будет исчерпано.

Понятная аналогия для объяснения процесса VACUUM

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

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

Часто спрашивают: PostgreSQL и JSONB: как расширение jsquery ускоряет точные выборки

Механизм и преимущества использования pg_repack

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

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

Вот как инструмент работает:

Создает временную таблицу, идентичную исходной.
Накладывает на исходную таблицу методы, отслеживающие операции insert, update, и delete, происходящие во время активности
Копирует все актуальные данные исходной таблицы во временную.
Применяет изменения, зафиксированные методами, к временной таблице.
Переименовывает старую таблицу на новую, что требует недолгой блокировки.
Удаляет старую, разросшуюся таблицу.

Это интересно: Очистка и оптимизация PostgreSQL с помощью команды VACUUM

Сравнение эффективности pg_repack и VACUUM

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

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

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

Для регулярного поддержания “чистоты” в таблице используйте Vacuum; прибегайте к pg_repack, когда вам нужно уменьшить размер сильно разросшейся таблицы.

Образная аналогия работы pg_repack

Продолжая аналогию с блокнотом, pg_repack перепечатывает блокнот начисто, пока вы продолжаете писать в старом.

Он:

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

Интеграция и использование pg_repack в среде DBaaS Selectel

В среде DBaaS процесс установки и настройки pg_repack очень прост:

Вам не нужно ничего скачивать или компилировать в Selectel. Зайдите в панель управления кластером PostgreSQL и ключите расширение
Подключитесь к базе данных и запустите команду, указав таблицу SQL: pg_repack -d <имя_базы> -t <имя_таблицы>.