Indicizzazione dei dati di tipo JSONB: uso degli indici GIN e BTREE

Prima domanda: ma perché lavoriamo con JSONB? JSONB ti permette di salvare dati in formato JSON, dandoti la possibilità di avere una struttura flessibile. È super comodo quando i dati sono complessi e annidati (tipo i profili utente con una lista di indirizzi o impostazioni). Diversamente dal semplice JSON, JSONB salva i dati in formato binario, il che rende le operazioni di ricerca e filtro molto più veloci.

Però senza indici, cercare dentro JSONB può essere davvero lento, soprattutto se la tabella ha migliaia o milioni di righe. Tipo, immagina di avere una tabella con info sugli utenti, dove salviamo le impostazioni di ogni utente in formato JSONB. Provare a trovare tutti gli utenti con un certo valore in queste impostazioni senza indici — è una roba che consuma un sacco di risorse. Ed è qui che entrano in gioco i nostri indici!

Indicizzazione JSONB: cose importanti

Per lavorare con JSONB PostgreSQL supporta l'indicizzazione in due modi principali:

1. GIN (Generalized Inverted Index) — per cercare tra chiavi e valori dentro JSONB.
2. BTREE — per ricerche e ordinamenti più semplici.

Ognuno ha le sue particolarità. Vediamo meglio come funzionano.

Indice GIN per JSONB

GIN è un indice potente che lavora con array, testi e anche con dati JSONB. "Scompone" il contenuto dell'oggetto JSONB in singole chiavi e valori, creando una struttura speciale per cercare tutto questo ben di Dio in modo super veloce.

Vantaggi di GIN per JSONB:

• Ti permette di cercare sia per chiavi che per valori.
• Funziona con strutture annidate.
• Accelera le operazioni con gli operatori @>, ?, ?|, ?& (filtraggio di chiavi e valori).

Supponiamo di avere una tabella users, dove nella colonna settings salviamo le impostazioni degli utenti in formato JSONB. Esempio di dati:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    settings JSONB
);

INSERT INTO users (name, settings) VALUES
('Alice', '{"tema": "scuro", "notifiche": {"email": true, "sms": false}}'),
('Bob', '{"tema": "chiaro", "notifiche": {"email": false, "sms": true}}'),
('Charlie', '{"tema": "scuro", "notifiche": {"email": true, "sms": true}}');

Ora vogliamo trovare velocemente tutti gli utenti con il tema scuro (tema: scuro). Prima creiamo l'indice:

CREATE INDEX idx_users_settings_gin ON users USING GIN (settings);

Poi fai una query con l'operatore @> (ricerca per valore):

SELECT name
FROM users
WHERE settings @> '{"tema": "scuro"}';

Ora PostgreSQL usa l'indice GIN per la ricerca, e la query va molto più veloce.

Come funziona? Quando crei un indice GIN su una colonna JSONB, PostgreSQL costruisce un indice "invertito", cioè crea voci separate per tutte le chiavi e i valori JSON. Ad esempio, dall'oggetto:

{"tema": "scuro", "notifiche": {"email": true, "sms": false}}

creerà l'indicizzazione per le chiavi tema, notifiche.email, notifiche.sms e i loro valori. Questo rende la ricerca sui singoli elementi molto più veloce.

Indice BTREE per JSONB

BTREE è il classico tipo di indice. Si usa se vuoi confrontare oggetti JSONB interi o fare ordinamenti. Però, a differenza di GIN, BTREE non scompone il contenuto dell'oggetto JSON.

Vantaggi di BTREE per JSONB:

• Perfetto per operazioni di ordinamento e confronto di oggetti.
• Va più veloce se JSONB viene usato come "blocco unico" (tipo, lo confronti con un altro oggetto o cerchi righe dove JSONB è uguale a un certo valore).

Facciamo un esempio di uso dell'indice BTREE. Supponiamo che nella tabella users vogliamo spesso confrontare la colonna settings con un oggetto specifico:

{"tema": "scuro", "notifiche": {"email": true, "sms": false}}

Prima creiamo l'indice:

CREATE INDEX idx_users_settings_btree ON users USING BTREE (settings);

Ora puoi fare query di confronto tra oggetti:

SELECT name
FROM users
WHERE settings = '{"tema": "scuro", "notifiche": {"email": true, "sms": false}}';

Questa query userà l'indice BTREE per andare più veloce.

Confronto tra GIN e BTREE

Quindi, GIN è adatto per query più complesse, mentre BTREE è utile quando ti serve confrontare oggetti interi o fare ordinamenti.

Quale indice scegliere?

• Se vuoi cercare per singole chiavi e valori dentro JSONB, usa GIN.
• Se ti serve confrontare o ordinare oggetti JSONB interi, meglio BTREE.

Ma ricorda, nessuno ti vieta di combinare questi indici! Tipo, puoi creare sia GIN che BTREE sulla stessa colonna, se la tabella richiede entrambi i tipi di query.

Errori tipici nell'indicizzazione di JSONB

Creare indici inutili: non sempre ha senso indicizzare ogni campo JSONB. Gli indici occupano spazio e possono rallentare le operazioni di insert e update.

Indicizzare operatori usati raramente: non indicizzare un campo solo perché "sembra giusto". Analizza le query e usa l'indicizzazione solo dove davvero velocizza le operazioni.

Ignorare le particolarità di GIN: GIN può richiedere più tempo per creare l'indice rispetto a BTREE. Tienilo a mente quando indicizzi tabelle grandi.

Applicazioni pratiche

Lavorare con JSONB è utile nei progetti reali dove i dati sono flessibili e dinamici. Ad esempio:

• Web app con impostazioni utente personalizzate.
• Salvataggio di log che hanno campi diversi per eventi diversi.
• Caching di dati in formato JSON.

Indicizzare questi dati con GIN e BTREE aiuta a migliorare di molto le performance delle query. Tipo, ai colloqui puoi mostrare come hai velocizzato il sistema aggiungendo indici per strutture dati complesse.

La documentazione ufficiale di PostgreSQL sugli indici JSON è disponibile qui. Non dimenticare di darci un'occhiata per chiarimenti ed esempi.