Prügikoristus (VACUUM) eemaldab PostgreSQLis surnud reaversioonidest tekkinud tarbetu ruumikasutuse ja valmistab ruumi uute andmete jaoks. Statistika värskendamine (ANALYZE) kogub tabelite andmete kohta statistikat, mida päringuoptimeerija kasutab sobiva täitmisplaani valimiseks. Neid saab soovi korral käivitada käsitsi vastavalt VACUUM ja ANALYZE lausega.

CREATE TABLE test_kasutaja (
kasutaja_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nimi VARCHAR(100) NOT NULL,
skoor DECIMAL(5,2) NOT NULL,
registreerimis_aeg TIMESTAMPTZ(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0));
 
INSERT INTO test_kasutaja (nimi, skoor, registreerimis_aeg)
SELECT 'Kasutaja ' || i,   /*Loob nimed: Kasutaja 1, Kasutaja 2 jne.*/
round((random() * 100)::numeric, 2), /*Juhuslik skoor 0.00 ja 100.00 vahel*/
CURRENT_TIMESTAMP(0) - (random() * interval '365 days') /*Juhuslik aeg viimase 1 aasta jooksul*/
FROM generate_series(1, 10000) AS i;
 
SELECT pg_size_pretty(pg_relation_size('test_kasutaja')) AS tabeli_suurus;
/*Tabeli suurus: 664 kB*/

DELETE FROM Test_kasutaja;
SELECT pg_size_pretty(pg_relation_size('test_kasutaja')) AS tabeli_suurus;
/*Tabeli suurus endiselt: 664 kB*/

VACUUM test_kasutaja;
SELECT pg_size_pretty(pg_relation_size('test_kasutaja')) AS tabeli_suurus;
/*Tabeli suurus: 0 bytes*/

PostgreSQLis korraldab automaatset prügikoristust (VACUUM) ja automaatset statistika värskendamist (ANALYZE) autovacuum-mehhanism, mis koosneb autovacuum launcher'ist ja selle käivitatavatest worker-protsessidest.
 
Need protsessid ei käivitu rangelt fikseeritud ajakava alusel (näiteks "iga viie minuti järel"), vaid reageerivad tabelites toimunud muudatustele. Autovacuum launcher (käivitaja) käivitab perioodiliselt worker'eid (töölisi), mis kontrollivad andmebaaside tabeleid ja otsustavad, kas mõni tabel vajab VACUUM-i (prügikoristus) või ANALYZE-i (statistika värskendamine).
 

1. Millise sagedusega need käivituvad?

A. Kontrollimise sagedus (autovacuum_naptime)

autovacuum_naptime = 1min
 
See tähendab, et autovacuum launcher korraldab andmebaaside kontrollimist ligikaudu selle intervalli alusel. Kui PostgreSQLi kobaras on mitu andmebaasi, jaotatakse worker'ite käivitamine nende vahel ajas.

B. Automaatne prügikoristus (VACUUM)

Automaatne VACUUM käivitub tabelil siis, kui surnud reaversioonide arv ületab vastava lävendi.
 
PostgreSQL 18-s arvutatakse lävend põhimõtteliselt järgmiselt:
MIN(
    autovacuum_vacuum_max_threshold,
    autovacuum_vacuum_threshold
    + autovacuum_vacuum_scale_factor
      * tabeli hinnanguline ridade arv
)
 
Vaikeseaded on:
  • autovacuum_vacuum_threshold = 50
  • autovacuum_vacuum_scale_factor = 0,2
  • autovacuum_vacuum_max_threshold = 100 000 000
Surnud reaversioonid tekivad eeskätt UPDATE- ja DELETE-operatsioonide tulemusena.
Näiteks 100 000 reaga tabeli puhul:
50 + (0,2 * 100 000) = 20 050
 
Seega käivitub automaatne VACUUM, kui surnud reaversioonide arv ületab ligikaudu 20  050. Arvutuses kasutatakse tabeli ridade arvu hinnangut, mitte tingimata täpset hetke ridade arvu.

PostgreSQL reageerib ka tabelitesse ainult uute ridade lisamisele. Selleks kasutatakse parameetreid:
  • autovacuum_vacuum_insert_threshold
  • autovacuum_vacuum_insert_scale_factor
Need aitavad käivitada VACUUM-i ka insert-only (ainult lisamiseks) tabelites, muu hulgas selleks, et ridu õigeaegselt külmutada ning nähtavuskaarti ajakohastada.

C. Automaatne statistika värskendamine (ANALYZE)

Automaatne statistika uuendamine käivitub, kui pärast viimast ANALYZE-i lisatud, muudetud või kustutatud ridade arv ületab järgmise lävendi:
autovacuum_analyze_threshold
+ autovacuum_analyze_scale_factor
  * tabeli hinnanguline ridade arv
 
Vaikeseaded on:
  • autovacuum_analyze_threshold = 50
  • autovacuum_analyze_scale_factor = 0,1
Näiteks 100 000 reaga tabeli puhul:
50 + (0.1 * 100 000) = 10 050
 
Seega värskendatakse statistikat ligikaudu pärast 10 050 muudatust.
Vaikeseadete korral on ANALYZE lävend üldjuhul madalam kui VACUUM-i lävend.

2. Kuidas vaadata praegusi seadeid ja viimaseid käivitusi?

A. Konfiguratsiooniparameetrite vaatamine

Kõigi autovacuum-iga algavate seadete vaatamiseks:
 
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%';
 
Üksikute parameetrite väärtuseid saab vaadata näiteks nii:
 
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;
 

B. Millal VACUUM ja ANALYZE tabelil viimati automaatselt käivitusid?

SELECT relname AS tabel,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count,
       autoanalyze_count
FROM pg_stat_user_tables;
 
last_autovacuum ja last_autoanalyze näitavad viimast automaatset käivitusaega. autovacuum_count ja autoanalyze_count aitavad hinnata, kui sageli neid operatsioone on toimunud.

3. Kuidas sagedust muuta?

Lävendeid saab muuta kahel tasemel:
  • globaalselt kogu PostgreSQLi andmebaaside kobara jaoks,
  • tabelipõhiselt.
Sageli on mõistlik alustada vaikimisi globaalsetest seadetest ja häälestada vajaduse korral suuri või väga aktiivseid tabeleid eraldi.

A. Tabelipõhine muutmine

Kui tabelis on näiteks 10 miljonit rida, annab vaikimisi 0,2 skaalategur lävendiks:
50 + (0.2 * 10 000 000) = 2 000 050
 
See võib aktiivse tabeli jaoks olla liiga suur. Sel juhul saab näiteks vähendada skaalategurit:
ALTER TABLE minu_tabel
SET (autovacuum_vacuum_scale_factor = 0.05);
 
Statistika sagedasemaks värskendamiseks:
ALTER TABLE minu_tabel
SET (autovacuum_analyze_scale_factor = 0.02);
 
Algse seadistuse taastamiseks:
ALTER TABLE minu_tabel
RESET (autovacuum_vacuum_scale_factor);

B. Globaalne muutmine

Kui soovida muuta vaikeseadeid kogu PostgreSQLi kobara jaoks, saad kasutada näiteks:
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
 
Seejärel laadi konfiguratsioon uuesti:
SELECT pg_reload_conf();
 
Nende parameetrite muutmine ei nõua serveri taaskäivitamist.

ALTER SYSTEM kasutamine eeldab vastavaid õigusi; tavaliselt teeb seda ülikasutaja või kasutaja, kellele on antud vastava parameetri muutmise õigus.