PostgreSQL | Когда нужно делать VACUUM FULL для таблицы или секции?

PostgreSQL | Когда нужно делать VACUUM FULL для таблицы или секции?

Возможны две наиболее вероятные ситуации, когда точно нужно делать VACUUM FULL в PostgreSQL:

  1. Мы сильно меняем данные в таблицах и делаем много удалений (операций DELETE). Сотнями тысяч в сутки, и даже более. При этом место на диске не освободилось для операционной системы.
  2. Запросы начинают замедляться. Таблицы засоряются старыми ненужными страницами, на которых уже нет ссылок. Хоть ссылок нет, но время всё равно тратится на «заглядывание» и проверку.

Всё равно на слух это звучит абстрактно и субъективно. Нужна какая-то метрика, чтобы не гадать.

 

Установка расширения pgstattuple

По умолчанию в базе данных после её создания нет этого расширения. Его нужно установить:

CREATE EXTENSION pgstattuple;

Данное расширение имеет параметр «tuple_percent«, который может дать объективное понимание проблемности таблицы. Зная его значение, мы точно сможем принять решение о необходимости запуска процесса VACUUM FULL в PostgreSQL для нашей таблицы.

Если значение находится ближе к 90% и более, значит с таблицей всё хорошо — плотность данных максимальная.

Если значение менее 70%, то уже пора проводить чистку.

Если значение менее 40%, то вы просто обязаны очистить такую таблицу.

 

Вызов функции pgstattuple для очищаемой таблицы

Я буду обращаться к случайной секции одной родительской таблицы. Теперь можно получить данные для нашей секции:

select * from pgstattuple('test.ptp_003m');

Обращаем внимание на параметры «tuple_percent» и «table_len«. В нашем случае получилось:

  1. «tuple_percent» — 47.85 процентов
  2. «table_len» — 234061824 байт
tuple_percent - 47 - PostgreSQL
tuple_percent — 47 — PostgreSQL

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

Теперь делаем полную очистку данной секции и смотрим на изменения:

vacuum full test.ptp_003m;
select * from pgstattuple('test.ptp_003m');

После очистки:

  1. «tuple_percent» — 95.69 процентов
  2. «table_len» — 117030912 байт
tuple_percent - 95 - PostgreSQL
tuple_percent — 95 — PostgreSQL

В результате очистки VACUUM FULL по секции таблицы мы вернули обратно в ОС почти 50% свободного места на диске.

Показатель «tuple_percent» из 47.85% превратился в 95.69%. Данные уплотнились. Таблица стала более сбалансированной. Лишнее из неё удалено.

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Официальный сайт СУБД PostgreSQLhttps://www.postgresql.org

Официальный сайт клиента DBeaver для СУБД PostgreSQLhttps://dbeaver.io

PostgreSQL | VACUUM FULL без блокировки таблицы