Таблицы в PostgreSQL представлены в виде страниц, размером 8 КБ, в которых размещены записи. Когда одна страница полностью заполняется записями, к таблице добавляется новая страница. При удалалении записей с помощью DELETE или изменении с помощью UPDATE, место где были старые записи не может быть повторно использовано сразу же.
PostgreSQL как типичный представитель «версионных» СУБД (в противоположность блокирующим) самостоятельно не блокирует при изменении данных таблицы и записи от читающих транзакций (в случае 1С этим занимается сам сервер 1С). Вместо этого создаётся копия изменяемой записи, которая становится видна последующим транзакциям, действующие же продолжают видеть данные, актуальные на начало своей транзакции. А при удалении запись помечается как удаленная, но продолжает занимать физическое пространство.
Как следствие, в таблицах накапливаются устаревшие данные — предыдущие версии измененных записей и удаленные записи. Для того чтобы СУБД могла высвободившееся место использовать, необходимо произвести «сборку мусора» — определить какие из записей больше не используются. Это можно сделать явно SQL-командой VACUUM, либо дождаться когда таблицу обработает автоматический сборщик мусора — AUTOVACUUM. Так же до определенной версии сборка мусора была связана со сбором статистики (планировщик использует данные о количестве записей в таблицах и распределении значений индексированных полей для построения оптимального плана запроса).
Процесс очистки autovacuum, или команда VACUUM, пробегает по изменённым страницам и помечает такое место как свободное, после чего новые записи могут спокойно записываться в это место, то-есть размер файла таблицы физически не уменьшается.
Чтобы максимально уменьшить таблицу в PostgreSQL есть VACUUM FULL или CLUSTER, но оба эти способа требуют exclusively locks на таблицу (блокируют доступ к таблице на время работы, то есть в это время с таблицы нельзя ни читать, ни писать), что далеко не всегда является подходящим решением.
В принципе процедура очистки, autovacuum или VACUUM, все же может сама уменьшить размер таблицы убрав полностью пустые страницы, но только при условии что они находятся в самом конце таблицы.
Сборку мусора безусловно делать необходимо, чтобы таблицы не разрастались и эффективно использовали дисковое пространство, но внезапно начавшаяся уборка мусора дает дополнительную нагрузку на диск и таблицы, что приводит к увеличению времени выполнения запросов.
Есть здесь и свои нюансы — если autovacuum не справляется, например в результате активного изменения большего количества данных или просто из-за плохих настроек, то к таблице будут излишне добавляться новые страницы по мере поступления новых записей. Получается что таблица становится более разряженной в плане плотности записей. Это называется эффектом раздувания таблиц (table bloat).
Полностью отключить autovacuum можно параметром:
autovacuum = off
Так же для работы Autovacuum требуется параметр track_counts = on, в противном случае он работать не будет.
По умолчанию оба параметра включены. На самом деле autovacuum полностью отключить нельзя — даже при autovacuum = off иногда (после большого количества транзакций) autovacuum будет запускаться.
Отключать autovacuum крайне не рекомендуется, иначе имеет смысл самостоятельно запланировать регулярное выполнение команды VACUUM ANALYZE.
Если Autovacuum полностью не отключать, настроить его влияние на выполнение запросов можно следующими параметрами:
autovacuum_max_workers — максимальное количество параллельно запущенных процессов уборки.
autovacuum_naptime — минимальный интервал, реже которого autovacuum не будет запускаться. По умолчанию 1 минута. Можно увеличить, тогда при частых изменениях данных анализ будет выполняться реже.
autovacuum_vacuum_threshold, autovacuum_analyze_threshold — количество измененных или удаленных записей в таблице, необходимых для запуска процесса сборки мусора VACUUM или сбора статистики ANALYZE. По умолчанию по 50.
autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor — коэфициент от размера таблицы в записях, добавляемый к autovacuum_vacuum_threshold и autovacuum_analyze_threshold соответственно. Значения по умолчанию 0.2 (т.е. 20% от количества записей) и 0.1 (10%) соответственно.
default_statistics_target — назначает объем статистики, собираемый командой ANALYZE. Значение по умолчанию 100. Большие значения увеличивают время выполнения команды ANALYZE, но позволяют планировщику строить более эффективные планы выполнения запросов. Встречаются рекомендации по увеличению до 300.
Можно управлять производительностью AUTOVACUUM, делая его более длительным но менее нагружающим систему.
vacuum_cost_page_hit — размер «штрафа» за обработку блока, находящегося в shared_buffers. Связан с необходимостью блокировать доступ к буферу. Значение по умолчанию 1
vacuum_cost_page_miss — размер «штрафа» за обработку блока на диске. Связан с блокировкой буфера, поиском данных в буфере, чтении данных с диска. Значение по умолчанию 10
vacuum_cost_page_dirty — размер «штрафа» за модификацию блока. Связан с необходимостью сбросить модифицированные данные на диск. Значение по умолчанию 20
vacuum_cost_limit — максимальный размер «штрафов», после которых процесс сборки может быть «заморожен» на время vacuum_cost_delay. По умолчанию 200
vacuum_cost_delay — время «заморозки» процесса сборки мусора по достижению vacuum_cost_limit. Значение по умолчанию 0ms
autovacuum_vacuum_cost_delay — время «заморозки» процесса сборки мусора для autovacuum. По умолчанию 20ms. Если установить -1, будет использоваться значение vacuum_cost_delay
autovacuum_vacuum_cost_limit — максимальный размер «штрафа» для autovacuum. Значение по умолчанию -1 — используется значение vacuum_cost_limit
По публикациям использование vacuum_cost_page_hit = 6, vacuum_cost_limit = 100, autovacuum_vacuum_cost_delay = 200ms уменьшает влияние AUTOVACUUM до 80%, но увеличивает время его выполнения втрое.