Parametry dla autovacuuma to parametry globalne, dotyczą wszystkich baz w danej instancji. Ważne, aby o tym pamiętać, ponieważ progi dla autovacuuma są również ustawiane dla wszystkich tabel we wszystkich bazach na takim samym poziomie. W pewnych sytuacjach możemy skończyć z kilkoma małymi tabelami, które są zmieniane ze średnią częstotliwością i będą vacuumowane na okrągło, ponieważ procentowo ich zmiana będzie przewyższała tabele z miliardami rekordów, która jest aktualizowana dużo częściej, ale progi procentowe są dla niej również znacznie wyższe albo odwrotnie, duże, często aktualizowane tabele, które długo się vacuumują, mogą potrzebować następnego vacuuma od razu po skończeniu poprzedniego,.Wtedy również inne tabele mogą nie mieć okazji na wyczyszczenie przez autovacuuma.

Możemy obejść to ustawienie poprzez konfigurację indywidualnych parametrów dla tabel, które nie pasują do ogólnego trendu zmian w instancji.

ALTER TABLE tabelka SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100);

Ilość procesów autovacuum, z których każdy może pracować nad inną tabelą, określona jest parametrem autovacuum_max_workers. Musimy jednak wziąć pod uwagę, że procesy te nie działają równolegle, a są kolejkowane. Domyślną wartością jest 3, więc upraszczając, posiadając 4 bazy, jedna z nich będzie zawsze czekać na zwolnienie jednego z procesów.

Procesy autovacuum uruchamiają się, aby wykonać pewną określoną pracę, po czym udają się na drzemkę określoną parametrem autovacuum_naptime,a następnie ponownie się wybudzają. Jeżeli proces autovacuuma rozpocznie operację vacuum dla jednej z tabel, po każdej "drzemce" będzie się wybudzał i kontynuował pracę nad tą samą tabelą, aż ta nie będzie potrzebowała więcej czyszczenia. Autovacuum wybudza się co autovacuum_naptime / autovacuum_max_workers. Czyli przy domyślnej konfiguracji co 20 sekund (60s / 3) startuje kolejny proces. Przy większej liczbie procesów i domyślnym autovacuum_naptime czas, w którym proces może być aktywny, spada dość drastycznie. Dla zobrazowania, przy domyślnej konfiguracji, autovacuum pracuje nad trzema tabelami i każdej może poświęcić 20 sekund. Jeżeli zwiększymy autovacuum_max_workers do 60, pozostawiając naptime na domyślnej wartości 60 sekund, postgres będzie w teorii czyścił 60 tabel, poświęcając każdej z nich 1 sekundę z 60. Teoretycznie będziemy vacuumować 60 tabel, co nie zawsze będzie prawdą, ponieważ postgres będzie vacuumował tylko tyle tabel, ile ma przekroczone progi autovacuum_vacuum_scale_factor + autovacuum_vacuum_threshold, lub odpowiednio progi dla vacuum analyze.

Warto monitorować liczbę aktywnych procesów oraz które tabele są najczęściej czyszczone, aby w odpowiednim momencie zwiększyć liczbę workerów autovacuuma.

SELECT split_part(split_part(substring(query from 'public\..*'),'.',2),' ',1) FROM pg_stat_activity WHERE query like 'autovacuum%';

SELECT p.pid, now() - a.xact_start AS duration, coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
   CASE
       WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
       WHEN a.query ~*'^vacuum' THEN 'user'
       ELSE 'regular'
   END AS mode,
   p.datname AS database, p.relid::regclass AS table,
   p.phase,
   pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
   pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
   pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
   pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
   round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
   round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
   p.index_vacuum_count,
   round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;

Co oznacza "pewna określona praca" wspomniana przy opisie workerów autovacuuma? Autovacuum czyszcząc tabele, czyta 8kb bloki danych (domyślna wartość block_size), po czym zapisuje/modyfikuje bloki, w których znajdują się martwe wiersze. Może, więc to być dość wymagająca operacja pod względem IO, jeżeli autovacuum działałby na ogromnej tabeli zawierającej dużą iliczbę martwych wierszy w godzinach największego obciążenia. Mamy jednak możliwość ustawienia parametrów określających, ile bloków autovacuum może wyczyścić w jednym cyklu i dzięki temu zapobiec zbytniemu obciążeniu dysku.

Parametry, które pozwalają nam dostroić działanie procesu autovacuum pod względem IO, to między innymi:

• autovacuum_vacuum_cost_limit - sumaryczny koszt, który autovacuum może osiągnąć dla wszystkich procesów. Domyślnie -1, oznacza pobranie wartości z vacuum_cost_limit, domyślnie 200.


• autovacuum_vacuum_cost_delay - czas, który autovacuum odczeka po wykonaniu czyszczenia określonego przez "cost_limit". Domyślnie od PG13 2ms, wcześniej 20ms.


• vacuum_cost_page_hit - koszt przeczytania bloku danych z pamięci shared buffers. Domyślnie 1.


• vacuum_cost_page_miss - koszt przeczytania bloku danych z dysku. Domyślnie 10.


• vacuum_cost_page_dirty - koszt zapisu do bloków danych, w których znajdują się martwe wiersze. Domyślnie 20.

Przy założeniu, że żyjemy w idealnym świecie, na serwerach nie mamy żadnych opóźnień i procesy nie muszą na nic czekać, możemy wyliczyć, ile danych maksymalnie autovacuum może przeczytać w jednym cyklu pomiędzy drzemkami.

Chcąc wyliczyć liczbę analizowanych danych na sekundę:

Zakładając, że wszystkie bloki znajdują się w pamięci shared_buffers::

Zakładając, że wszystkie bloki muszą zostać przeczytane z dysku::

Zakładając, że wszystkie bloki zawierają martwe wiersze i muszą zostać wyczyszczone::

Powyższe limity są równo dzielone dla wszystkich procesów roboczych autovauuma, dlatego ustawienie zbyt wysokiej wartości dla autovacuum_max_workers może mieć skutek odwrotny od zamierzonego i spowodować, że autovacuum na dużych tabelach będzie potrzebował nawet kilkakrotnie więcej czasu. A zbytnie zwiększenie autovacuum_vacuum_cost_limit może powodować problemy z IO, choć jest to jeden z głównych parametrów, które zmieniamy, chcąc przyspieszyć autovacuuma Trzeba robić to jednak z rozwagą i dostosować do dysków, które posiadamy.

Wszystkie powyższe parametry możemy ustawić indywidualnie dla różnych tabel, czym pozwalamy postgresowi na czyszczenie niektórych tabel intensywniej niż innych, np.

ALTER TABLE tabelka SET (autovacuum_vacuum_cost_limit = 2400);

Vacuum posiada również drugi zestaw parametrów określających te same limity, ale dla ręcznego wykonania vacuuma, m.in. vacuum_cost_delay, vacuum_cost_limit. Dlatego przy bardzo aktywnych bazach zaleca się zaplanowanie ręcznego wykonania vacuuma, obok dobrze dostrojonego autovacuuma, na najbardziej zmieniających się tabelach w godzinach mniejszego obciążenia, kiedy możemy sobie pozwolić na większe wykorzystanie zasobów przez process vacuuma. Ręczne wykonania vacuuma nie korzystają z limitu autovacuum_max_workers, więc możemy ich zaplanować więcej niż jeden w tym samym czasie, bez obaw o wykorzystanie procesów dla autovacuuma.