Ghid Complet MVCC, Table Bloat și Reglarea Fină a Autovacuum-ului în PostgreSQL

În arhitectura MVCC (Multi-Version Concurrency Control) din PostgreSQL, operațiunile DELETE și UPDATE nu șterg fizic rândurile de pe disc. Un DELETE marchează rândul ca fiind mort prin setarea antetului xmax, iar un UPDATE marchează vechea versiune ca moartă și creează un rând nou. Rândurile vechi se numesc Dead Tuples. Dacă aceste tupluri moarte nu sunt curățate periodic, baza de date suferă de Bloat: tabelele și indecșii se umflă masiv, consumă memoria RAM și încetinesc interogările.

-- 1. VACUUM standard (rulează în fundal fără a bloca aplicația):
VACUUM (VERBOSE, ANALYZE) utilizatori;
-- Ce face: Curăță pointerii morți din paginile de 8 KB și marchează spațiul ca reutilizabil
-- pentru viitoarele comenzi INSERT. 
-- Important: NU returnează de regulă spațiul fizic pe disc către sistemul de operare!

-- 2. VACUUM FULL (PERICOL DE DOWNTIME):
VACUUM FULL utilizatori;
-- Ce face: Rescrie complet întreg tabelul într-un fișier nou de date și returnează spațiul pe disc.
-- PERICOL: Obține un blocaj exclusiv dur (ACCESS EXCLUSIVE LOCK) pe tabel!
-- Nicio altă conexiune nu poate citi sau scrie pe tabel pe durata procesului.

-- Soluția modernă zero-downtime pentru compactare: extensia pg_repack!

De ce setările implicite de Autovacuum sunt problematice?

Valorile implicite din fișierul postgresql.conf au fost gândite pentru servere cu resurse minime. Pe un tabel mare de 50 de milioane de rânduri, formula implicită de declanșare:

autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) * numar_randuri

înseamnă că Autovacuum se va declanșa abia după ce 10 milioane de rânduri au fost modificate sau șterse! Până la atingerea acestui prag, tabelul a acumulat deja un bloat uriaș.

Reglarea Fină a Autovacuum-ului pe Servere Moderne (SSD / NVMe)

Pentru a menține tabelele curate și compacte fără a impacta performanța tranzacțiilor curente, aplicați următoarea configurație în postgresql.conf:

-- Dacă avem un tabel masiv de evenimente sau comenzi, suprascriem pragul doar pentru el:
ALTER TABLE comenzi SET (
    autovacuum_vacuum_scale_factor = 0.01, -- Se declanșează la 1% rânduri moarte
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_cost_limit = 5000
);

Pericolul Major: Transaction ID Wraparound

PostgreSQL folosește un număr pe 32 de biți pentru identificatorii de tranzacții (XID), ceea ce înseamnă că există un spațiu de circa 4 miliarde de numere. Pentru a compara dacă o tranzacție este în trecut sau în viitor, numerele sunt comparate modular pe un cerc de 2 miliarde de tranzacții.

Dacă un tabel nu este vizitat de VACUUM timp de 2 miliarde de tranzacții, apare riscul catastrofal ca datele vechi să pară că aparțin „viitorului” și să devină invizibile! Pentru a preveni acest lucru, PostgreSQL declanșează Autovacuum to prevent wraparound (care nu poate fi oprit) și aplică operațiunea de Freezing (marchează tuplurile vechi cu un flag special înghețat, independent de XID).

Monitorizați permanent riscul de wraparound cu interogarea:

SELECT 
    datname,
    age(datfrozenxid) AS varsta_tranzactii,
    2147483648 - age(datfrozenxid) AS tranzactii_ramase
FROM pg_database
ORDER BY age(datfrozenxid) DESC;