Indicizzazione dei dati JSONB: uso degli indici GIN e BTREE

L'indicizzazione in PostgreSQL è un modo per trovare velocemente i dati nel database. Se i dati nella tua tabella fossero libri, l'indicizzazione sarebbe come il catalogo in una biblioteca che ti permette di trovare subito il libro giusto per titolo o autore. Con JSONB è un po' più tricky, perché i dati sono salvati in formato strutturato, non come semplici righe e colonne.

Quando i dati JSONB iniziano a diventare grandi come "un libro di Harry Potter, solo senza illustrazioni", cercare dentro questa struttura può diventare lento. Per esempio, se vuoi trovare tutti gli ordini dove una certa chiave "status" ha valore "delivered", PostgreSQL deve scorrere tutte le righe per fare la ricerca. Sembra un lavoro che non vorresti fare a mano, vero?

Beh, gli indici GIN e BTREE sono i nostri eroi che arrivano a salvarci dalle attese infinite!

Tipi di indici per JSONB

GIN (Generalized Inverted Index)

L'indice GIN è stato creato apposta per lavorare con dati strutturati come array e oggetti, quindi è perfetto per JSONB. Ti permette di indicizzare non l'intero oggetto, ma le singole chiavi e valori al suo interno. Questo significa che con GIN puoi trovare velocemente le righe che contengono certe chiavi, valori o combinazioni.

Immagina una colonna JSONB con questi dati:

{"nome": "Alice", "età": 25, "città": "Berlino"}

L'indice GIN crea una struttura interna dove le chiavi "nome", "età" e "città" sono collegate ai loro valori. Quindi, quando cerchi "nome": "Alice", PostgreSQL sa già dove guardare — non deve scorrere tutta la tabella.

BTREE

L'indice BTREE è più tradizionale. Crea una struttura ordinata che permette di trovare velocemente i dati per valori specifici. Nel caso di JSONB, l'indice BTREE si usa se cerchi una corrispondenza esatta dei dati o se hai una chiave fissa (tipo vuoi confrontare il valore JSONB intero).

Se la tua colonna contiene oggetti JSONB come:

{"nome": "Bob", "età": 30}

L'indice BTREE può essere utile se cerchi le righe dove l'intero oggetto è esattamente uguale.

{"nome": "Bob", "età": 30}

Creare un indice per JSONB

Vediamo prima come si crea un indice GIN. Tutto quello che ti serve è il comando magico CREATE INDEX. Ecco come si fa:

-- Creiamo un indice GIN per la colonna JSONB
CREATE INDEX idx_jsonb_data ON ordini USING GIN (dati);

Dove:

• idx_jsonb_data — nome dell'indice.
• ordini — nome della tabella.
• dati — colonna con i dati JSONB.

Dopo aver creato questo indice, le query che cercano chiavi o valori dentro JSONB saranno molto più veloci.

Supponiamo di avere una tabella ordini con una colonna dati che contiene JSONB:

Query senza indice:

-- Trova tutti gli ordini con status "consegnato"
SELECT * FROM ordini WHERE dati @> '{"status": "consegnato"}';

Se la tabella è grande, questa query può essere lenta. Ma con l'indice GIN funzionerà molto più velocemente.

Come creare un indice BTREE

Per creare un indice BTREE devi cambiare un po' il modo di pensare. Nella maggior parte dei casi, per usare BTREE con JSONB, devi specificare che vuoi indicizzare solo una parte dell'oggetto. Ecco un esempio:

-- Creiamo un indice BTREE per una chiave specifica
CREATE INDEX idx_jsonb_totale ON ordini ((dati->>'totale'));

Nota (dati->>'totale'). Questo estrae il valore della chiave totale dall'oggetto JSONB, e proprio questo valore viene indicizzato. Ora, se cerchi ordini dove totale = 100, PostgreSQL userà questo indice.

Esempio di utilizzo con gli stessi dati:

Query:

-- Trova tutti gli ordini dove totale = 100
SELECT * FROM ordini WHERE dati->>'totale' = '100';

Con l'indice BTREE su dati->>'totale', questa query sarà molto più veloce.

Confronto tra GIN e BTREE

Se hai strutture JSONB complesse e usi spesso operatori come @> o ?, scegli GIN. Se cerchi valori o chiavi specifiche e fisse, BTREE può essere la scelta migliore.

Trappole ed errori tipici nell'indicizzazione di JSONB

Lavorare con l'indicizzazione JSONB è potente, ma ci sono alcune insidie da tenere a mente.

1. Mancanza di indice dove serve. Se usi spesso dati JSONB nei filtri (WHERE), ma non hai creato l'indice, le query saranno lente.
2. Indicizzazione eccessiva. Se crei indici per ogni possibile chiave JSONB, questo può rallentare insert e update.
3. Scelta sbagliata del tipo di indice. Se le tue query sono complesse e usano operatori tipo @> o ?, ma hai creato un indice BTREE, non avrai miglioramenti di performance.
4. Mancanza di conoscenza sui percorsi. Se accedi spesso a valori annidati, ma non hai creato un indice per il percorso specifico (tipo dati->>'some_key'), la tua query sarà comunque lenta.

In sintesi: quando usare quale indice

• Usa GIN se hai array o oggetti complessi dove cerchi spesso per chiavi e valori.
• Usa BTREE se cerchi corrispondenze esatte o accedi spesso a chiavi specifiche.