Configurazione e ottimizzazione di Autovacuum

Benvenuto nella configurazione dell'autovacuum di PostgreSQL! Dopo aver appreso MVCC e VACUUM, vediamo come gestire questo processo in background fondamentale.

Autovacuum recupera automaticamente spazio e aggiorna le statistiche, prevenendo problemi di prestazioni come la frammentazione delle tabelle e le query lente.

Il demone autovacuum viene eseguito in background e monitora costantemente il database per individuare le tabelle che richiedono interventi. Esegue due operazioni principali:

• VACUUM: recupera lo spazio occupato dalle righe "morte", rendendolo disponibile per i nuovi dati.
• ANALYZE: aggiorna le statistiche delle tabelle, aiutando il pianificatore delle query a scegliere i piani di esecuzione più efficienti.

La maggior parte delle impostazioni di autovacuum si trova nel file di configurazione di PostgreSQL, generalmente denominato postgresql.conf. È inoltre possibile modificarle a livello di database o di tabella.

Ricordi di riavviare PostgreSQL o ricaricare la configurazione affinché le modifiche abbiano effetto!

L'impostazione di base è autovacuum. Di solito è abilitata per impostazione predefinita e, nella maggior parte dei sistemi di produzione, dovrebbe essere lasciata così!

Disabilitarla richiede l'esecuzione manuale di vacuum, che può essere facilmente dimenticata e causare gravi problemi di prestazioni.

Autovacuum attiva un VACUUM quando si accumula un determinato numero di righe morte. Questo comportamento è controllato da due parametri:

• autovacuum_vacuum_scale_factor: una percentuale delle dimensioni della tabella, ad esempio 0.2 per il 20%.
• autovacuum_vacuum_threshold: il numero minimo fisso di righe morte.

Il vacuum viene attivato quando (dead_rows > autovacuum_vacuum_threshold + table_rows * autovacuum_vacuum_scale_factor).

Supponiamo che autovacuum_vacuum_threshold sia 50 e che autovacuum_vacuum_scale_factor sia 0.2 (20%). Per una tabella con 1000 righe, il vacuum verrà attivato quando:

• dead_rows > 50 + (1000 * 0.2)
• dead_rows > 50 + 200
• dead_rows > 250

È possibile modificare questi valori per le tabelle molto attive o molto statiche.

Analogamente al vacuum, autovacuum attiva un'operazione ANALYZE in base a una soglia:

• autovacuum_analyze_scale_factor: una percentuale delle dimensioni della tabella.
• autovacuum_analyze_threshold: il numero minimo fisso di righe modificate.

L'analisi garantisce che il pianificatore delle query disponga di statistiche aggiornate per creare piani ottimali, prevenendo le query lente.

Altri due parametri importanti:

• autovacuum_naptime: il tempo di attesa di autovacuum tra i controlli sui database, ad esempio '1min'. Un naptime più breve comporta controlli più frequenti.
• autovacuum_max_workers: il numero massimo di processi autovacuum che possono essere eseguiti contemporaneamente su tutti i database. Un numero maggiore di worker consente di eseguire più operazioni di vacuum e analisi simultaneamente.

A volte una tabella può richiedere impostazioni di autovacuum diverse da quelle predefinite globali. Ad esempio, una tabella molto grande e aggiornata di frequente potrebbe trarre vantaggio da un vacuum più aggressivo.

È possibile sovrascrivere la maggior parte dei parametri di autovacuum per singole tabelle utilizzando ALTER TABLE.

Ottimo lavoro! Ha imparato a configurare e ottimizzare il demone autovacuum di PostgreSQL.

• Autovacuum esegue automaticamente VACUUM e ANALYZE.
• I parametri principali controllano quando viene eseguito, tramite il fattore di scala e la soglia, e con quale frequenza, tramite naptime.
• È possibile personalizzare le impostazioni globalmente in postgresql.conf o per singola tabella utilizzando ALTER TABLE.

Una corretta configurazione di autovacuum è essenziale per mantenere le prestazioni del database e prevenire la frammentazione.