Do tuningu autovacuuma możemy również zaliczyć HOT (Heap Only Tuple) update. Wiersze w postgresie są niezmienne, nie możemy ich bezpośrednio edytować. Zmieniając jakiekolwiek dane, zawsze tworzymy nową wersję wiersza, co zazwyczaj odbywa się w innym bloku danych, przez co zmiana ta wymaga aktualizacji wszystkich indeksów na danej tabeli, ponieważ indeksy muszą zapisać sobie wskaźnik do jego nowej wersji. Po jakimś czasie, kiedy już żadna z transakcji nie potrzebuje poprzedniej wersji, wiersz ten oraz wskaźnik w indeksie odnoszący się do niego jest oznaczany jako martwy. Następnie autovacuum musi wyczyścić i oznaczyć je jako wolne, dostępne do ponownego wykorzystania.

Przyjmując, że używamy domyślnego rozmiaru bloku danych 8kb, zmieniając jeden wiersz, wczytujemy jeden blok z aktualną wersją wiersza, wczytujemy kolejny blok, w którym zapiszemy jego nową wersję. Następnie wczytujemy bloki z każdego indeksu. Przyjmijmy, że mamy ich 4, czyli 4x 8kb z aktualną wersją, aby oznaczyć, że jest to stara wersja, oraz kolejne cztery bloki, w których będziemy zapisywali wskaźniki do nowej wersji wiersza w tabeli. Podsumowując, zmieniając jeden wiersz w tabeli, musimy przeczytać i zapisać 2*8kb, bloków danych z tabeli oraz 8*8kb bloków z indeksów, w sumie 80kb danych, a następnie wykonać na tej tabeli autovacuum, czyszcząc 5 stron zawierających stare, już niepotrzebne wersje tego wiersza, czyli kolejne 40kb danych.

Postgres posiada specjalną funkcję, która pozwala temu zaradzić, jest to HOT Update oraz parametr fillfactor. Fillfactor to procentowa wartość bloku, która może być wykorzystana na INSERT. Pozostały procent jest zarezerwowany na operacje UPDATE, aby nowa wersja wiersza mogła znajdować się w tym samym bloku danych. Zalecana wartość bezpieczna dla zdecydowanej większości typów aplikacji to fillfactor równy 85, gdzie 15% jest zarezerwowane i wykorzystywane podczas modyfikacji wierszy. Dzięki temu postgres nie musi aktualizować indeksów, ponieważ nowy wiersz znajduje się w tym samym bloku danych, więc wskaźnik pozostaje bez zmian, czyli dla tabeli z wcześniejszego przykładu, 1 tabela 4 indeksy, edytując wiersz, wystarczy wczytamy oraz zapiszemy jeden blok danych, 8kb, chyba że modyfikujemy wartość indeksowaną. Wtedy wczytujemy i zmieniamy 2 bloki po 8kb. Dzięki temu musimy przeczytać i zmienić pięć do dziesięciu razy mniej danych niż przy tradycyjnym update. Jeszcze jedną przewagą HOT update nad zwykłymi jest to, że martwe wiersze mogą być usunięte nawet przez zwykły select, nie potrzebujemy do tego wywoływać autovacuuma, właśnie dzięki temu, że obie wersje są w tym samym bloku danych. Select, czytając blok danych, jeżeli w jednym bloku znajdzie dwie wersje tego samego wiersza, gdzie jedna ma xmax mniejsze niż aktualnie najniższa aktywna transakcja, może ten starszy wiersz usunąć, zwalniając zajmowaną przestrzeń do ponownego wykorzystania.

Na podstawie statystyk możemy wyliczyć, jaka wartość fillfactora może być odpowiednia dla danej tabeli. Musimy tylko pamiętać, że to jest czysta statystyka i zależnie od tego, kiedy była ostatnio resetowana, wyniki mogą być lepsze lub gorsze. Często zabierając się do tuningu, warto zresetować statystyki i wrócić do nich za jakiś czas, żeby mieć je jak najdokładniejsze. Lub posiadać monitoring, który mógłby zbierać te dane i pozwoli na analizę wartości z przeszłości oraz charakterystyki ich wzrostu.

wyszukiwanie często aktualizowanych tabel

n_live_tup - szacunkowa liczba wierszy w tabeli, na podstawie statystyk

n_tup_ins - liczba insertów do tabeli

n_tup_del - liczba deletów z tabeli

n_tup_upd - liczba updatów w tabeli

n_tup_hot_upd - liczba hot updatów

Tabele posiadające bardzo dużą (względem innych tabel w bazie) liczbę updatów, a niską liczbę hot updatów, są dobrymi kandydatami do tuningu parametru fillfactor. Planując tuning parametru fillfactor, warto też zresetować sobie statystyki w bazie za pomocą funkcji "select pg_stat_reset()" i wrócić do sprawdzenia poniższych statystyk za jakiś czas, tydzień/miesiąc. Statystyki, które są zbierane przez kilka lat, mogą nie być do końca miarodajne, ponieważ zachowanie i logika aplikacji mogła się w międzyczasie sporo zmienić, dlatego warto mieć świeżą próbkę.

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_tup_ins,
  n_tup_del,
  n_tup_upd,
  n_tup_hot_upd
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

szacowanie fillfactora dla tabel na podstawie dostępnych danych statystycznych

nspname - nazwa schematu

relname - nazwa tabeli

n_tup_upd - ilość updatów

n_tup_hot_upd - ilość hot updatów

statement - instrukcja ALTER TABLE z zalecaną wartością fillfactora

WITH  tabs AS
(SELECT ns.nspname, c.relname , r.n_tup_ins, r.n_tup_upd, r.n_tup_del, r.n_tup_hot_upd
FROM pg_stat_user_tables r
JOIN pg_class c ON r.relid = c.oid AND c.relkind NOT IN ('t','p') AND r.n_tup_upd > 0
JOIN pg_namespace ns ON c.relnamespace = ns.oid)
SELECT nspname,relname,n_tup_upd,n_tup_hot_upd,'ALTER TABLE '||nspname||'.'||relname||' SET ( FILLFACTOR='|| 100 - 20*n_tup_upd/(n_tup_ins+n_tup_upd) + 20*n_tup_upd*n_tup_hot_upd/((n_tup_ins+n_tup_upd)*n_tup_upd) || ' );' as statement
FROM tabs
WHERE (20*n_tup_upd/(n_tup_ins+n_tup_upd) - 20*n_tup_upd*n_tup_hot_upd/((n_tup_ins+n_tup_upd)*n_tup_upd)) > 1 ;


 nspname |     relname      | n_tup_upd | n_tup_hot_upd |                         statement
---------+------------------+-----------+---------------+-
----------------------------------------------------------
 public  | pgbench_accounts |    111482 |         65381 |
ALTER TABLE public.pgbench_accounts SET ( FILLFACTOR=91 );
(1 row)