SQL Academy · Lezione

Manutenzione degli indici e bloat

Diagnostichi il bloat degli indici, li ricostruisca con REINDEX CONCURRENTLY e rimuova in sicurezza gli indici inutilizzati.

Manutenzione degli indici e bloat è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Perché gli indici diventano soggetti a bloat

PostgreSQL usa MVCC: UPDATE scrive una nuova versione della riga, lasciando quella precedente visibile alle transazioni già esistenti. Sono presenti voci di indice per entrambe le versioni. Con il tempo:

Le tabelle soggette a molti aggiornamenti accumulano voci di indice obsolete
Gli indici diventano più grandi del necessario
Le ricerche rallentano perché il B-tree diventa più profondo

Diagnosi del bloat

Le query per verificare il bloat non sono banali. Strumenti comuni:

Estensione pgstattuple
Report di pg_repack
Query per il bloat incluse in check_postgres e negli strumenti di monitoraggio

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

Ricostruisce l'indice. La forma classica acquisisce un lock ACCESS EXCLUSIVE, una scelta problematica in produzione:

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12+)

Variante non bloccante: le operazioni di lettura e scrittura continuano durante la ricostruzione:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

Indici inutilizzati

Un indice inutilizzato rallenta ogni scrittura, ma non velocizza alcuna lettura. Individuarli:

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Eliminare gli indici inutilizzati

Eliminarli, ma verificare prima tutti gli ambienti e gli intervalli temporali. Gli indici usati per report occasionali sembrano inutilizzati per la maggior parte del tempo.

DROP INDEX CONCURRENTLY old_unused_idx;

Indici duplicati

A volte lo stesso indice viene creato sia da un vincolo sia da un comando CREATE INDEX manuale. Controllare pg_indexes per individuare i duplicati ed eliminare quello ridondante:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Write amplification degli indici

Ogni INSERT, UPDATE o DELETE aggiorna tutti gli indici pertinenti. Tre indici su una tabella soggetta a molte scritture significano un costo di scrittura triplo. Aggiungere solo gli indici che portano vantaggi.

Pending list di GIN

Gli indici GIN raggruppano gli aggiornamenti in una pending list. Svuotarla manualmente o affidarsi all'autovacuum:

SELECT gin_clean_pending_list('events_data_gin');

VACUUM pulisce le voci degli indici

VACUUM (trattato nella lezione su MVCC) rimuove le voci di indice obsolete dalle pagine heap. Senza autovacuum, gli indici crescono senza limiti.

Monitoraggio delle dimensioni degli indici

Monitorare nel tempo le dimensioni degli indici:

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: riscrivere le tabelle online

In caso di bloat grave, pg_repack riscrive online la tabella e gli indici, senza bloccare completamente la tabella. Installarlo come pacchetto del sistema operativo e come estensione PostgreSQL.

Riepilogo

Gli indici richiedono manutenzione.

Il bloat dovuto a MVCC è normale: gestirlo con VACUUM e REINDEX CONCURRENTLY
Eliminare gli indici inutilizzati
Evitare i duplicati
Ogni nuovo indice rallenta le scritture: usarlo con criterio

Verifica rapida

Quale comando PostgreSQL ricostruisce un indice SENZA bloccare le scritture?

Domande Frequenti

La lezione «Manutenzione degli indici e bloat» è gratuita?

Sì — il testo completo di «Manutenzione degli indici e bloat» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Manutenzione degli indici e bloat»?

Diagnostichi il bloat degli indici, li ricostruisca con REINDEX CONCURRENTLY e rimuova in sicurezza gli indici inutilizzati. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Manutenzione degli indici e bloat»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy