PostgreSQL dla zagwarantowania spójności danych wykorzystuje model wielowersyjny MVCC (Multi Version Concurrency Control). Dzięki temu każda z sesji w bazie danych widzi kompletną migawkę danych dla momentu, w którym rozpoczęła się jej transakcja. Przewagą MVCC nad metodą opartą na blokadach wykorzystywaną w innych tradycyjnych systemach bazodanowych jest minimalizacja konfliktów blokad. Zapisywanie danych nie konfliktuje z odczytywaniem, a odczytywanie nie konfliktuje z zapisywaniem.

Dzieje się tak dlatego, że w modelu wielowersyjnym MVCC zapytania UPDATE czy DELETE nie powodują bezpośredniej zmiany danego wiersza. Postgres korzysta z dodatkowych kolumn systemowych, które są niewidoczne przy normalnym użytkowaniu, m.in. xmin i xmax, które określają ID transakcji, która dodała dany wiersz oraz ID transakcji, która dany wiersz usunęła. W uproszczeniu, wykonanie DELETE na wierszu aktualizuje jedynie wartość xmax dla danego wiersza, przez co transakcje młodsze niż wartość xmax nie "widzą" go. Z kolei UPDATE, podobnie jak DELETE, aktualizuje wartość xmax zmienionego wiersza oraz dodaje nowy wiersz z wartością xmin, przez co nowe transakcje mogą zobaczyć nową wartość. Widoczność wersji wiersza sprawdzana jest na początku każdej transakcji w "commit logu", który przechowuje informacje o stanie transakcji, dzięki czemu postgres wie, które były już zacommitowane lub które są nadal w trakcie i wiersze przez niezmienione nie powinny być widoczne.

Spróbujmy to sobie zobrazować. Przed przystąpieniem do przykładów otwórzmy dodatkowy terminal.

Stwórzmy przykładową tabelę w terminalu 1:

postgres=# create table mvcc (x integer,y varchar);
CREATE TABLE

Dodajmy jeden testowy wiersz:

postgres=# insert into mvcc values (1,1);
INSERT 0 1

W widoku pg_attribute widzimy, jakie dodatkowe, domyślnie niewidoczne kolumny są dostępne dla wybranej tabeli:

postgres=# SELECT attname, format_type (atttypid, atttypmod)
FROM  pg_attribute
WHERE attrelid::regclass::text='mvcc' ORDER BY attnum;
 attname  |    format_type
----------+-------------------
 tableoid | oid
 cmax     | cid
 xmax     | xid
 cmin     | cid
 xmin     | xid
 ctid     | tid
 x        | integer
 y        | character varying
(8 rows)

Dalej na terminalu 1 wykonamy select z tabeli mvcc razem z najniższą i najwyższą transakcją, dla której dany wiersz jest widoczny, 0 czyli bez górnej granicy:

postgres=# select *,xmin,xmax from mvcc where x = 1 limit 1;
 x | y | xmin | xmax
---+---+------+------
 1 | 1 |  744 |    0
(1 row)

Na terminalu 2 sprawdzamy aktualne ID transakcji:

postgres=# select txid_current();
 txid_current
--------------
          745
(1 row)

Również na terminalu 2 rozpoczynamy transakcję, w której zmieniamy wartość jednej wartości
w zapytaniu select, takim samym jak wcześniej, widzimy, że xmin wzrosło do aktualnej wartości transakcji:

postgres=# begin;
BEGIN
postgres=*# update mvcc set y = 2 where x = 1;
UPDATE 1
postgres=*# select *,xmin,xmax from mvcc where x = 1 limit 1;
 x | y | xmin | xmax
---+---+------+------
 1 | 2 |  745 |    0
(1 row)

Wracamy na terminal 1, xmin pozostało bez zmian, ale xmax już wzrosło do wersji z transakcji w terminalu 2:

postgres=# select *,xmin,xmax from mvcc where x = 1 limit 1;
 x | y | xmin | xmax
---+---+------+------
 1 | 1 |  744 |  745
(1 row)

Commit transakcji na terminalu 2:

postgres=*# commit;
COMMIT

Na terminalu 1 wartość xmin została zaktualizowana:

postgres=# select *,xmin,xmax from mvcc where x = 1 limit 1;
 x | y | xmin | xmax
---+---+------+------
 1 | 2 |  745 |    0
(1 row)

Na powyższym przykładzie widać, że tabela posiada dwie wersje wiersza, które są widoczne dla różnych transakcji. Spróbujmy zajrzeć bezpośrednio do bloku danych i zobaczmy, jak zachowuje się tabela podczas zmiany danych i po vacuumie.

Zainstalujmy rozszerzenie pageinspect umożliwiające sprawdzanie zawartości stron bazy danych w formie binarnej. Nie jest zbyt użyteczne w codziennej administracji, używane jest raczej w celu debugowania PostgreSQL, ale pozwoli nam przyjrzeć się z bliska, jak zachowują się wiersze w bloku danych podczas edycji i usuwania danych. Dodajmy do tabeli kilka wierszy, zmieńmy kilka i sprawdźmy, jak wyglądają wiersze w bloku danych;

postgres=# CREATE EXTENSION pageinspect;
CREATE EXTENSION


# zmodyfikujmy i dodajmy kilka wierszy do tabeli
postgres=# update mvcc set y = 3 where x = 1;
UPDATE 1
postgres=# update mvcc set y = 4 where x = 1;
UPDATE 1
postgres=# insert into mvcc values (generate_series(1,10), 1);
INSERT 0 10

# usuńmy wszystkie wiersze z x mniejszym niż 5
postgres=# DELETE from mvcc where x > 5;
DELETE 5

# zapytanie zwracające xmin, xmax oraz binarną reprezentację wartości wiersza
postgres=# SELECT t_xmin, t_xmax, tuple_data_split('mvcc'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('mvcc', 0));
 t_xmin | t_xmax |     tuple_data_split
--------+--------+---------------------------
    742 |    743 | {"\\x01000000","\\x0531"}
    743 |    744 | {"\\x01000000","\\x0532"}
    744 |    745 | {"\\x01000000","\\x0531"}
    745 |      0 | {"\\x01000000","\\x0532"}
    746 |      0 | {"\\x01000000","\\x0531"}
    746 |      0 | {"\\x02000000","\\x0531"}
    746 |      0 | {"\\x03000000","\\x0531"}
    746 |      0 | {"\\x04000000","\\x0531"}
    746 |      0 | {"\\x05000000","\\x0531"}
    746 |    748 | {"\\x06000000","\\x0531"}
    746 |    748 | {"\\x07000000","\\x0531"}
    746 |    748 | {"\\x08000000","\\x0531"}
    746 |    748 | {"\\x09000000","\\x0531"}
    746 |    748 | {"\\x0a000000","\\x0531"}
(14 rows)

# sprawdzenie ile bloków danych wykorzystuje tabela
postgres=# SELECT oid::regclass AS tbl, relpages
FROM   pg_class
WHERE  relname = 'mvcc';

 tbl  | relpages
------+----------
 mvcc |        1
(1 row)

Tutaj w powyższym przykładzie widzimy, że w wybranej tabeli mamy jeden 8kb blok danych zawierający 14 wierszy o różnych t_xmax. Po ręcznym wykonaniu vacuuma wszystkie wersje wierszy, których t_xmax był niższy niż aktualnie najstarsza aktywna transakcja, zostały wyczyszczone.

postgres=# vacuum mvcc;
VACUUM


 t_xmin | t_xmax |     tuple_data_split
--------+--------+---------------------------
        |        |
        |        |
        |        |
    745 |      0 | {"\\x01000000","\\x0532"}
    746 |      0 | {"\\x01000000","\\x0531"}
    746 |      0 | {"\\x02000000","\\x0531"}
    746 |      0 | {"\\x03000000","\\x0531"}
    746 |      0 | {"\\x04000000","\\x0531"}
    746 |      0 | {"\\x05000000","\\x0531"}
(9 rows)

Wiersze z końca zostały "uwolnione", ale wiersze, które były na początku bloku, nadal są widoczne, jeżeli zajrzymy do nich przez "pageinspect". Te wolne przestrzenie na początku to tak zwany bloat, o którym więcej poniżej.

Sposób gwarancji spójności danych w postgresie ogólnie zapewnia lepszą wydajność niż metoda oparta na blokadach, operacje zmieniające dane nie blokują w żaden sposób odczytu, a inserty są po prostu zapisywane bezpośrednio w plikach danych. Ale MVCC w postgresie posiada też swoje wady, m.in. potrzebę czyszczenia wierszy, które zostały usunięte bądź zmienione, jednak nadal istnieją w plikach danych, czy wyżej wspomniane zarządzanie historią ID transakcji. Autovacuum jest właśnie procesem, który dba o porządek w plikach danych, czyści je z już niepotrzebnych wierszy oraz zbiera statystyki dla optymalizatora zapytań. Kiedy najstarsza transakcja jest młodsza niż xmax wiersza, jest on uznawany za "martwy", a autovacuum może taki wiersz usunąć, uwalniając przestrzeń, którą zajmował. Przestrzeń ta nie jest zwracana do systemu operacyjnego, tzn. rozmiar pliku pozostaje taki sam, chyba że był to ostatni blok w pliku danych i jest całkiem wyczyszczony, ale ta uwolniona przestrzeń może zostać wykorzystana przez kolejne zapytania dodające lub zmieniające dane. Zbyt duże nagromadzenie takiej wolnej przestrzeni to tak zwany "bloat" lub po polsku rozdęcie/napompowanie tabeli, które może prowadzić do spadku wydajności czytania danych z tabel lub indeksów, ponieważ postgres, czytając tabelę lub index, musi również "przeczytać" wolne przestrzenie w plikach danych, co przy znacznej wielkości bloatu może wydłużyć czas potrzebny na wykonanie zapytań.

Wiedząc już, za co odpowiedzialny jest autovacuum, nasuwa się pytanie, kiedy jest wykonywany na tabelach? Autovacuum posiada dwa progi, które wywołują jego wykonanie, osobne dla analizy tabel oraz czyszczenia.

Próg wywołujący autovacuum VACUUM dla tabeli = autovacuum_vacuum_scale_factor * liczba wierszy w tabeli + autovacuum_vacuum_threshold
Próg wywołujący autovacuum ANALYZE dla tabeli = autovacuum_vacuum_scale_factor * liczba wierszy w tabeli + autovacuum_vacuum_threshold

Domyślne wartości parametrów

autovacuum_vacuum_scale_factor = 0.2 # 20% wierszy w tabeli autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.1 # 10% wierszy w tabeli autovacuum_analyze_threshold = 50

Dla tabeli zawierającej 1000 wierszy:

VACUUM threshold to (0.2 * 1000) + 50 = 250

ANALYZE threshold to (0.1 * 1000) + 50 = 150

Dostosowując progi dla wywoływania automatycznego vacuuma i analyze, warto wyliczyć je tak, aby tabele czyściły się przynajmniej raz w tygodniu, maksymalnie co dwa tygodnie. Jeżeli nie jesteśmy w stanie dostosować globalnych parametrów w ten sposób, warto rozważyć zaplanowanie ręcznego vacuuma lub ustawić dla niej parametry indywidualne. Jeżeli któraś z tabel okupuje vacuuma, czyszcząc się wielokrotnie w ciągu jednego dnia czy nawet godziny, warto dla niej ustawić indywidualne progi dla automatycznego vacuuma i analyze.

Domyślnie, próg procentowy wywołania analyze jest połową progu dla vacuuma. Jednak nie zawsze potrzebujemy tak częstej analizy danych. Vacuum analyze zbiera informacje o najczęściej występujących wartościach oraz histogram pokazujący przybliżony rozkład danych w każdej kolumnie. Jeżeli najczęstsze wartości i ich rozkład nie zmieniają się zbytnio dla danej tabeli, może ona nie potrzebować zbyt częstej aktualizacji statystyk. Możemy wtedy indywidualnie podnieść jej progi autovacuum_analyze_threshold oraz autovacuum_analyze_scale_factor.

Jeżeli natomiast statystyki zbieramy dość często, rozkład danych jest bardzo różny i często dostajemy nieoptymalne plany wykonywania zapytań, może to oznaczać, że korzystamy ze zbyt małego zbioru najczęstszych wartości w kolumnach. Możemy zmienić wtedy dla takiej tabeli parametr default_statistics_target. Jest to liczba najczęstszych wartości oraz liczba przedziałów w histogramach przechowujących dane statystyczne. Domyślna wartość to 100, podnosząc ją na wyższe wartości, możemy dostać optymalniejsze plany wykonania, kosztem dłuższej analizy na tabeli oraz wykorzystania większej przestrzeni na dysku, gdzie przechowywane są te statystyki.

Warto tutaj jeszcze wspomnieć, że liczba transakcji w postgresie nie jest nieskończona. Typ zmiennej odpowiedzialnej za śledzenie ID transakcji to Integer, dlatego górny limit transakcji to 4 294 967 295 (~4 miliardy), w tym 2 miliardy rezerwowane są na transakcje historyczne, a drugie 2 miliardy przeznaczone są na nowe transakcje. Każdy ID transakcji może być jednak wykorzystany ponownie, Stare ID mogą zostać "uwolnione" z powrotem do puli, gotowe do ponownego wykorzystania przez process vacuuma/autovacuuma. Zwykły vacuum nie zawsze jednak uwalnia wszystkie transakcje, często pomija wiele z nich, dlatego postgres posiada mechanikę zwaną vacuum freeze, której głównym zadaniem jest skanowanie tabel i "uwalnianie" txid (ID transakcji) do ponownego wykorzystania.

Zwykły vacuumem/autovacuum różni się sposobem wykonywania "czyszczenia" tabel od vacuum/autovacuum freeza. Zwykły vacuum działa na podstawie statystyk, mapy wolnych przestrzeni (free space map, pliki _fsm w katalogu $PGDATA/base/<dboid>) oraz visibility mapy (pliki _vm w katalogu $PGDATA/base/<dboid>), najczęściej skanuje tylko te wiersze, które rzeczywiście były zmienione. Vacuum freeze skanuje całą tabelę, blok po bloku, nie patrząc, czy dany blok potrzebuje vacuuma czy też nie, dlatego jest też dużo kosztowniejszy, ale uwalnia wszystkie możliwe do uwolnienia ID transakcji. Zwykły vacuum również to robi, ale tylko dla wierszy, które mają wiek traksacyjny powyżej parametru vacuum_freeze_min_age, domyślnie 50 milionów. Parametr vacuum_freeze_min_age ma limit wielkości równy połowie wartości autovacuum_freeze_max_age, domyślnie 200 milionów. Parametr autovacuum_freeze_max_age określa, kiedy postgres powinien automatycznie uruchomić vacuum freeze, widoczny w logu postgresa jako "aggressive vacuum", agresywny proces vacuuma i uwolnić jak najwięcej transakcji, aby nie dopuścić do tak zwanego "transaction wraparound", czyli zatoczenia się transakcji, kiedy postgres próbuje rozpocząć nową transakcję, a jej ID nadal jest wykorzystywane przez jedną ze starszych transakcji.

Po wystąpieniu transaction wraparounda postgres przechodzi w tryb pojedynczego użytkownika i jedyną dozwoloną operacją jest ręczny vacumm tabel, aby uwolnić jak najwięcej transakcji do puli i pozwolić postgresowi wznowić pracę.

Domyślne 200 000 000 dla parametru autovacuum_freeze_max_age jest wartością odpowiednią dla większości mniejszych i średnich rozwiązań. Jednak w niektórych przypadkach, dla bardzo obciążonych baz danych, może to być za mało. Jeżeli w logach widzimy informację, że agresywny autovacuum odpala się automatycznie na tabelach codziennie lub regularnie co parę dni, warto pomyśleć o zwiększeniu tego limitu. Zmiany można dokonywać online, bez konieczności restartu postgresa, dlatego warto go zwiększać stopniowo o 100-200 milionów i obserwować zachowanie postgresa. Wartości do 800 milionów można uznać za stosunkowo bezpieczne, ale cokolwiek powyżej powinno być ustawiane z najwyższą ostrożnością.

Wiek dla tabel możemy sprawdzić poniższym zapytaniem:

postgres=# select n.nspname,c.relname,age(c.relfrozenxid) FROM pg_class c, pg_namespace n where c.relname = 'mvcc' and c.relkind IN ('r','t') and n.oid = c.relnamespace;
 nspname | relname | age
---------+---------+-----
 public  | mvcc    |  28
(1 row)


postgres=# vacuum freeze mvcc;
VACUUM
postgres=# select n.nspname,c.relname,age(c.relfrozenxid) FROM pg_class c, pg_namespace n where c.relname = 'mvcc' and c.relkind IN ('r','t') and n.oid = c.relnamespace;
 nspname | relname | age
---------+---------+-----
 public  | mvcc    |   0
(1 row)

Po wykonaniu "vacuum freeze mvcc" widzimy, że wiek tabeli został wyzerowany. Wszystkie ID transakcji, które były ciągle używane przez tabelę MVCC, mogą być ponownie wykorzystane przez nowe transakcje.