Ottimizzazione del fillfactor per tabelle soggette a molti update

PostgreSQL utilizza MVCC: un UPDATE non sovrascrive mai una riga direttamente. Scrive invece una versione completamente nuova della riga (tupla) e contrassegna quella precedente come morta.

• La nuova tupla deve essere collocata da qualche parte sul disco.
• Se finisce su una pagina diversa rispetto alla versione precedente, ogni indice della tabella deve essere aggiornato per puntare alla nuova posizione.

Nelle tabelle soggette a molti aggiornamenti, questo ricambio degli indici diventa una fonte rilevante di amplificazione delle scritture e bloat. La lezione di oggi spiega come fillfactor aiuta a evitarlo.

fillfactor è un parametro di archiviazione per tabella (e per indice), espresso come percentuale da 10 a 100.

• Indica a PostgreSQL quanto riempire ogni pagina da 8 KB quando inserisce le righe.
• Un fillfactor pari a 100 (il valore predefinito per le tabelle) riempie completamente le pagine.
• Un fillfactor pari a 90 lascia circa il 10% di ogni pagina come spazio libero riservato agli aggiornamenti futuri.

Questo spazio riservato è fondamentale per consentire aggiornamenti meno costosi sulla stessa pagina.

Un aggiornamento HOT (Heap-Only Tuple) si verifica quando:

• nessuna delle colonne aggiornate fa parte di un indice; e
• la nuova tupla entra nella stessa pagina di quella precedente.

Quando entrambe le condizioni sono soddisfatte, PostgreSQL collega la nuova versione a quella precedente all'interno della pagina e non aggiorna affatto gli indici. Non si verifica alcun ricambio degli indici, il WAL è molto più contenuto e la versione precedente può essere ripulita in modo efficiente dall'HOT pruning.

Lasciare spazio libero tramite un fillfactor più basso rende possibile soddisfare la condizione della "stessa pagina".

È possibile dichiarare il parametro di archiviazione al momento di CREATE TABLE. È l'approccio più pulito, perché la tabella viene organizzata correttamente fin dal primo inserimento.

• Scelga un valore che riservi spazio sufficiente per il numero tipico di versioni delle righe nella pagina tra un vacuum e l'altro.
• 90 è un punto di partenza comune; 70–80 è adatto alle righe soggette a modifiche molto frequenti.

ALTER TABLE ... SET (fillfactor = N) modifica il parametro, ma non riscrive le pagine esistenti. Solo le pagine scritte successivamente rispettano il nuovo valore.

Per applicarlo ai dati esistenti, riscriva la tabella con VACUUM FULL o CLUSTER (entrambi acquisiscono un lock ACCESS EXCLUSIVE), oppure utilizzi pg_repack per una riscrittura online.

Non deve procedere per supposizioni. pg_stat_user_tables espone contatori che indicano se gli aggiornamenti seguono il percorso HOT.

• n_tup_upd — totale delle tuple aggiornate;
• n_tup_hot_upd — numero di aggiornamenti HOT tra questi.

Un rapporto elevato tra n_tup_hot_upd / n_tup_upd indica che fillfactor e la progettazione degli indici stanno dando risultati.

Lo spazio libero da solo non basta. Se un UPDATE interessa una qualsiasi colonna indicizzata, PostgreSQL deve creare una nuova voce nell'indice; l'aggiornamento non può quindi essere HOT, anche se la nuova tupla entra nella stessa pagina.

• Mantenga, quando possibile, le colonne aggiornate frequentemente (contatori, timestamp, flag di stato) fuori dagli indici.
• Elimini gli indici che non sono realmente necessari: ciascuno può impedire gli aggiornamenti HOT.

Esempio: indicizzare hit_count vanificherebbe l'intero scopo della configurazione di fillfactor per questa tabella.

Un fillfactor più basso non è privo di costi. I compromessi sono:

• Fillfactor più basso → più spazio libero per pagina → più aggiornamenti HOT e meno ricambio degli indici → ma la tabella occupa più pagine, quindi le scansioni sequenziali e la cache dei buffer contengono meno righe per pagina.
• Fillfactor più alto → archiviazione più densa e migliore efficienza di scansione e cache → ma gli aggiornamenti si spostano su nuove pagine, causando ricambio degli indici e bloat.

Regola generale: mantenga 100 per le tabelle in cui si inseriscono dati in coda o che sono utilizzate principalmente in lettura; scenda a 70–90 solo per le tabelle realmente soggette a molti aggiornamenti.

Gli indici hanno un proprio fillfactor (il valore predefinito è 90 per gli alberi B). Ridurlo lascia spazio nelle pagine foglia, così le nuove voci non causano frequenti suddivisioni delle pagine nelle tabelle con molti inserimenti di chiavi monotonamente crescenti.

• Per chiavi append-only o sempre crescenti, il valore predefinito è generalmente adeguato.
• Per gli indici su chiavi distribuite casualmente e soggette a frequenti modifiche, un fillfactor dell'indice leggermente più basso può ridurre le suddivisioni.

Per verificare se una tabella ha già un fillfactor diverso da quello predefinito, legga reloptions da pg_class. Un valore NULL indica che è attivo il valore predefinito (100 per l'heap, 90 per gli alberi B).

Riunendo tutti gli elementi per una tabella soggetta a molti aggiornamenti:

• 1. Confermi che il carico di lavoro sia dominato dagli aggiornamenti e controlli il rapporto corrente di n_tup_hot_upd.
• 2. Sposti le colonne soggette a frequenti modifiche fuori dagli indici; elimini gli indici inutilizzati.
• 3. Imposti il fillfactor (iniziando da 90 e riducendolo verso 70 se il rapporto HOT è ancora basso).
• 4. Riscriva la tabella (VACUUM FULL / CLUSTER / pg_repack) affinché le pagine esistenti vengano ricreate con il nuovo impacchettamento.
• 5. Misuri nuovamente il rapporto HOT e apporti le modifiche necessarie.

Convalidi sempre i risultati tramite la vista delle statistiche: non ottimizzi alla cieca.

Concetti fondamentali per ottimizzare fillfactor nelle tabelle soggette a molti aggiornamenti:

• fillfactor riserva spazio libero in ogni pagina, così le righe aggiornate possono rimanere nella stessa pagina, abilitando gli aggiornamenti HOT.
• Gli aggiornamenti HOT saltano la manutenzione degli indici, riducendo la write amplification e il bloat.
• HOT richiede sia spazio nella stessa pagina sia che nessuna colonna indicizzata venga modificata; pertanto, mantenga le colonne soggette a frequenti modifiche fuori dagli indici.
• ALTER TABLE SET (fillfactor=N) influisce solo sulle nuove pagine; usi VACUUM FULL/CLUSTER/pg_repack per applicarlo ai dati esistenti.
• Misuri il risultato con n_tup_hot_upd / n_tup_upd in pg_stat_user_tables e ottimizzi iterativamente.
• Un fillfactor più basso sacrifica la densità di archiviazione per ridurre il numero di aggiornamenti che devono essere spostati su nuove pagine: lo utilizzi solo quando il carico di lavoro lo giustifica.