B-tree vs GIN vs GiST: guida pratica per PostgreSQL

Cosa stai veramente scegliendo quando scegli un indice

La maggior parte dei problemi di indicizzazione in PostgreSQL inizia allo stesso modo: una vista elenco sembra veloce a 1.000 righe, poi rallenta a 1.000.000. Oppure una casella di ricerca che funzionava nei test diventa una pausa di secondi in produzione. Quando succede, è allettante chiedersi “Quale indice è il migliore?” Una domanda migliore è: “Cosa chiede questa schermata al database?”

La stessa tabella può aver bisogno di tipi di indice diversi perché le schermate la leggono in modi diversi. Una vista filtra per un singolo stato e ordina per created_at. Un'altra fa full-text search. Un'altra verifica se un campo JSON contiene una chiave. Un'altra trova elementi vicino a un punto su una mappa. Sono pattern di accesso differenti, quindi un tipo di indice non vince ovunque.

Questo è ciò che scegli quando selezioni un indice: come l'app accede ai dati. Fai soprattutto confronti esatti, range e ordinamenti? Cerchi all'interno di documenti o array? Chiedi “cosa è vicino a questa posizione” o “cosa si sovrappone a questo intervallo”? La risposta determina se B-tree, GIN o GiST è la scelta giusta.

B-tree, GIN e GiST in parole semplici

Scegliere un indice riguarda meno il tipo di colonna e più ciò che le tue query fanno con essa. PostgreSQL usa gli indici in base agli operatori come =, <, @>, o @@, non in base al fatto che la colonna sia “text” o “json”. Per questo la stessa colonna può richiedere indici diversi su schermate diverse.

B-tree: veloce per ricerche ordinate

B-tree è il predefinito e la scelta più comune. Brilla quando filtri per valore esatto, filtri per un intervallo o hai bisogno dei risultati in un ordine specifico.

Un esempio tipico è una lista di admin filtrata per stato e ordinata per created_at. Un indice B-tree su (status, created_at) può aiutare sia il filtro sia l'ordinamento. B-tree è anche lo strumento abituale per l'unicità (vincoli UNIQUE).

GIN: veloce quando ogni riga contiene molte chiavi ricercabili

GIN è costruito per domande del tipo “questa riga contiene questo termine/valore?”, dove una riga può corrispondere a molte chiavi. Esempi comuni sono la ricerca full-text (un documento contiene parole) e l'appartenenza in JSONB/array (JSON contiene una chiave/valore).

Immagina un record cliente con un oggetto JSONB preferences, e una schermata che filtra gli utenti dove preferences contiene {\"newsletter\": true}. Quella è una ricerca adatta a GIN.

GiST: flessibile per range, geo e similarità

GiST è un framework generale usato da tipi di dato che non si adattano a un semplice ordinamento. È naturale per range (sovrapposizioni, contiene), query geometriche e geografiche (vicino, dentro) e alcune ricerche per similarità.

Quando decidi tra B-tree vs GIN vs GiST, inizia scrivendo gli operatori che le tue schermate più trafficate usano. L'indice giusto spesso diventa chiaro dopo.

Tabella decisionale per schermate comuni (filtri, ricerca, JSON, geo)

La maggior parte delle app ha bisogno solo di pochi pattern di indice. Il trucco è abbinare il comportamento della schermata agli operatori usati nelle query.

Due indici possono coesistere quando gli endpoint differiscono. Per esempio, una lista admin potrebbe aver bisogno di un B-tree su (tenant_id, created_at) per un ordinamento rapido, mentre una pagina di ricerca ha bisogno di un GIN per @@. Mantieni entrambi solo se entrambe le forme di query sono frequenti.

Se non sei sicuro, guarda prima l'operatore. Gli indici aiutano quando il database può saltare grandi parti della tabella.

Filtri e ordinamenti: dove B-tree di solito vince

Per la maggior parte delle schermate quotidiane, B-tree è la scelta noiosa che funziona. Se la tua query è del tipo “seleziona righe dove una colonna è uguale a un valore, forse ordinale, poi mostra la pagina 1”, B-tree è quasi sempre il primo tentativo.

I filtri di uguaglianza sono il caso classico. Colonne come status, user_id, account_id, type o tenant_id compaiono spesso in dashboard e pannelli admin. Un indice B-tree può saltare direttamente ai valori corrispondenti.

Anche i filtri per intervallo si adattano bene a B-tree. Quando filtri per tempo o range numerici, la struttura ordinata aiuta: created_at >= ..., price BETWEEN ..., id > .... Se la tua UI offre “Ultimi 7 giorni” o “$50 a $100”, B-tree fa esattamente quello che vuoi.

Ordinamento e paginazione sono dove B-tree può farti risparmiare più lavoro. Se l'ordine dell'indice corrisponde al tuo ORDER BY, PostgreSQL può spesso restituire le righe già ordinate invece di ordinarne un grande insieme in memoria.

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Gli indici compositi seguono una regola semplice: PostgreSQL può usare in modo efficiente solo la parte iniziale dell'indice. Pensa “da sinistra a destra”. Con (user_id, status, created_at), le query che filtrano per user_id (e opzionalmente status) ne traggono beneficio. Una query che filtra solo per status di solito no.

Gli indici parziali sono un grande miglioramento quando la tua schermata si interessa solo a una fetta di dati. Fette comuni sono “solo righe attive”, “non soft-deleted” o “attività recente”. Mantengono l'indice più piccolo e più veloce.

Colonne ad alta cardinalità e il costo di indici extra

Le colonne ad alta cardinalità hanno molti valori unici, come user_id, order_id, email o created_at con precisione di secondi. Gli indici tendono a brillare qui perché un filtro può rapidamente restringere i risultati a una fetta piccola della tabella.

Le colonne a bassa cardinalità sono l'opposto: booleani e piccoli enum come is_active, status IN ('open','closed') o plan IN ('free','pro'). Un indice su queste spesso delude perché ogni valore corrisponde a molte righe. PostgreSQL può scegliere correttamente una scansione sequenziale dato che usare l'indice significa comunque leggere molte pagine della tabella.

Un altro costo sottile è il fetching delle righe. Anche se un indice trova rapidamente gli ID corrispondenti, il database potrebbe dover comunque visitare la tabella per le altre colonne. Se la query ha bisogno solo di pochi campi, un indice covering può aiutare, ma lo rende anche più grande e costoso da mantenere.

Ogni indice aggiuntivo ha un prezzo sulle scritture. Gli insert devono scrivere in ogni indice. Gli update che modificano colonne indicizzate devono aggiornare anche quegli entry. Aggiungere indici “per sicurezza” può rallentare l'intera app, non solo una schermata.

Indicazioni pratiche:

• Parti con 1-2 indici principali per ogni tabella trafficata, basati su filtri e ordinamenti reali.
• Favorisci colonne ad alta cardinalità usate in WHERE e ORDER BY.
• Sii cauto nell'indicizzare booleani e enum piccoli a meno che non si combinino con un'altra colonna selettiva.
• Aggiungi un indice solo dopo poter nominare la query esatta che accelererà.

Esempio: una lista di ticket support filtrata per assignee_id (alta cardinalità) beneficia di un indice, mentre is_archived = false da sola spesso no.

Schermate di ricerca: full-text, prefissi e “contains”

Le caselle di ricerca sembrano semplici, ma gli utenti si aspettano molto: parole multiple, forme diverse delle parole e un ranking sensato. In PostgreSQL questo significa quasi sempre full-text search: memorizzi un tsvector (testo preparato) e lo interroghi con un tsquery (ciò che l'utente ha digitato, parsato in termini).

Per full-text search, GIN è il predefinito comune perché è veloce nel rispondere a “questa documento contiene questi termini?” su molte righe. Il compromesso è scritture più pesanti: inserire e aggiornare righe tende a costare di più.

GiST può funzionare anche per il full-text. Spesso è più piccolo e meno costoso da aggiornare, ma di solito più lento in lettura rispetto a GIN. Se i tuoi dati cambiano continuamente (per esempio tabelle di eventi), quel bilanciamento lettura/scrittura può contare.

La ricerca per prefisso non è full-text

La ricerca per prefisso significa “inizia con”, come cercare clienti per prefisso email. Questo non è ciò per cui è pensato il full-text. Per pattern prefisso, un indice B-tree può aiutare (spesso con la giusta operator class) perché si allinea con l'ordinamento delle stringhe.

Per ricerche “contains” come ILIKE '%error%', B-tree di solito non aiuta. Qui entrano in gioco gli indici trigrammi o un approccio di ricerca diverso.

Quando gli utenti vogliono filtri più ricerca testuale

La maggior parte delle schermate reali combina la ricerca con filtri: status, assegnatario, intervallo di date, tenant e così via. Un setup pratico è:

• Un indice GIN (o a volte GiST) per la colonna tsvector.
• Indici B-tree per i filtri più selettivi (per esempio account_id, status, created_at).
• Una regola rigorosa di “mantieni minimo”, perché troppi indici rallentano le scritture.

Esempio: una schermata di ticket che cerca “refund delayed” e filtra status = 'open' e uno specifico account_id. Il full-text trova le righe rilevanti, mentre i B-tree aiutano PostgreSQL a restringere rapidamente a account e status corretti.

Campi JSONB: scegliere tra GIN e indici B-tree mirati

JSONB è ottimo per dati flessibili, ma può trasformarsi in query lente se lo tratti come una colonna normale. La decisione centrale è semplice: cerchi “ovunque in questo JSON”, o filtri su pochi percorsi specifici e ripetuti?

Per query di contenimento come metadata @> '{"plan":"pro"}', un indice GIN è di solito la prima scelta. È costruito per “questo documento contiene questa forma?” e supporta anche controlli di esistenza di chiavi come ?, ?| e ?&.

Se la tua app filtra per una o due chiavi JSON molto spesso, un indice B-tree su espressione mirata è spesso più veloce e più piccolo. Aiuta anche quando hai bisogno di ordinare o fare confronti numerici su valori estratti.

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Una buona regola pratica:

• Usa GIN quando gli utenti cercano molte chiavi, tag o strutture annidate.
• Usa indici B-tree su espressione quando filtri su percorsi specifici ripetutamente.
• Indicizza ciò che appare nelle schermate reali, non tutto.
• Se le prestazioni dipendono da poche chiavi JSON sempre usate, considera di promuoverle a colonne reali.

Esempio: una schermata di support può filtrare i ticket per metadata->>'priority' e ordinare per created_at. Indicizza il percorso JSON della priorità e la colonna created_at. Evita un GIN ampio a meno che gli utenti non cerchino anche tag o attributi annidati.

Query geo e range: dove GiST è la scelta naturale

Le schermate geo e range sono dove GiST spesso diventa la scelta ovvia. GiST è costruito per “questa cosa si sovrappone, contiene o sta vicino a quell'altra?” piuttosto che per “questo valore è uguale a quello”.

I dati geo di solito sono punti (una sede), linee (un percorso) o poligoni (una zona di consegna). Schermate comuni includono “negozi vicino a me”, “lavori entro 10 km”, “mostra elementi dentro questa bounding box” o “un indirizzo è dentro la nostra area di servizio?” Un indice GiST (di solito tramite PostGIS geometry o geography) accelera questi operatori spaziali così il database può saltare la maggior parte delle righe invece di controllare ogni forma.

I range sono simili. PostgreSQL ha tipi range come daterange e int4range, e la domanda tipica è sovrapposizione: “questa prenotazione collide con un'altra?” o “mostra sottoscrizioni attive durante questa settimana.” GiST supporta efficacemente operatori di overlap e containment, ecco perché è comune in calendari, scheduling e controlli di disponibilità.

B-tree può comunque avere un ruolo nelle schermate geo-like. Molte pagine prima filtrano per tenant, status o tempo, poi applicano una condizione spaziale e infine ordinano. Per esempio: “solo le consegne della mia azienda, degli ultimi 7 giorni, più vicine prima.” GiST gestisce la parte spaziale, ma B-tree aiuta con filtri selettivi e ordinamenti.

Come scegliere un indice passo dopo passo

La scelta dell'indice riguarda soprattutto l'operatore, non il nome della colonna. La stessa colonna può richiedere indici diversi a seconda che tu usi =, >, LIKE 'prefix%', full-text search, contenimento JSON o distanza geo.

Leggi la query come una checklist: WHERE decide quali righe qualificano, JOIN decide come si collegano le tabelle, ORDER BY decide l'ordine di output e LIMIT quante righe ti servono davvero. L'indice migliore spesso è quello che ti aiuta a trovare le prime 20 righe velocemente.

Un processo semplice che funziona per la maggior parte delle schermate app:

1. Annota gli operatori esatti che la tua schermata usa (esempio: status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Parti con un indice che corrisponde al filtro più selettivo o all'ordinamento predefinito. Se la schermata ordina per created_at DESC, comincia da lì.
3. Aggiungi un indice composito solo quando vedi gli stessi filtri insieme frequentemente. Metti prima le colonne per uguaglianza, poi quelle per range, quindi la chiave di ordinamento.
4. Usa un indice parziale quando filtri sempre a una sottofetta (esempio: solo status = 'open'). Usa un indice su espressione quando interroghi un valore computato (esempio: lower(email) per ricerche case-insensitive).
5. Convalida con EXPLAIN ANALYZE. Mantienilo se taglia molto il tempo di esecuzione e riduce le righe lette.

Esempio concreto: una dashboard di support filtra i ticket per status e ordina per i più recenti. Un B-tree su (status, created_at DESC) è un buon primo tentativo. Se la stessa schermata filtra anche su una flag JSONB come meta @> '{"vip": true}', quello è un operatore diverso e di solito necessita di un indice focalizzato su JSON.

Errori comuni che fanno perdere tempo (e rallentano le scritture)

Un modo comune per restare delusi è scegliere il “tipo giusto” di indice per l'operatore sbagliato. PostgreSQL può usare un indice solo quando la query corrisponde a ciò che l'indice è costruito per rispondere. Se la tua app usa ILIKE '%term%', un B-tree semplice su quella colonna testo resterà inutilizzato e continuerai a scansionare la tabella.

Un altro tranello è costruire indici multi-colonna giganteschi “per sicurezza”. Sembrano sicuri, ma sono costosi da mantenere e spesso non corrispondono ai pattern di query reali. Se le colonne più a sinistra non sono usate nel filtro, il resto dell'indice potrebbe non aiutare.

È anche facile sovraccaricare colonne a bassa selettività. Un B-tree su un booleano come is_active o uno status con pochi valori può essere quasi inutile a meno che non lo renda parziale per la fetta che interroghi.

JSONB aggiunge i suoi tranelli. Un GIN ampio può essere ottimo per filtri flessibili, ma molti controlli su percorsi JSON sono più veloci con un indice espressione sull valore estratto. Se la tua schermata filtra sempre payload->>'customer_id', indicizzare quell'espressione è spesso più piccolo e più veloce che indicizzare l'intero documento.

Infine, ogni indice extra tassa le scritture. Su tabelle con molte scritture (ticket, ordini), ogni insert e update deve aggiornare tutti gli indici.

Prima di aggiungere un indice, fermati e controlla:

• L'indice corrisponde esattamente all'operatore che la tua query usa?
• Puoi sostituire un indice multi-colonna ampio con uno o due più mirati?
• Dovrebbe essere un indice parziale per evitare rumore da bassa selettività?
• Per JSONB, un indice su espressione potrebbe andare meglio?
• La tabella è così write-heavy che il costo dell'indice supera il guadagno in lettura?

Controlli rapidi prima di aggiungere (o mantenere) un indice

Prima di creare un nuovo indice, sii specifico su cosa fa davvero l'app. Un indice “bello da avere” spesso si traduce in scritture più lente e più storage con poco guadagno.

Inizia con le tue top 3 schermate (o endpoint API) e scrivi la forma esatta delle query: filtri, ordine e cosa l'utente digita. Molti “problemi di indice” sono in realtà “problemi di query poco chiari”, specialmente quando si discute di B-tree vs GIN vs GiST senza nominare l'operatore.

Una checklist semplice:

• Scegli 3 schermate reali e annota i loro pattern WHERE e ORDER BY esatti (inclusa direzione e gestione dei NULL).
• Conferma il tipo di operatore: uguaglianza (=), range (>, BETWEEN), prefisso, contenimento, overlap o distanza.
• Scegli un indice per ogni pattern comune, testalo e mantieni solo quelli che riducono misurabilmente il tempo o le righe lette.
• Se la tabella è write-heavy, sii rigoroso: indici extra moltiplicano il costo delle scritture e possono aumentare la pressione sul vacuum.
• Riesamina dopo cambi di feature. Un nuovo filtro, un nuovo ordinamento predefinito o il passaggio da “starts with” a “contains” può rendere un indice obsoleto.

Esempio: una dashboard aggiunge un nuovo ordinamento predefinito last_activity DESC. Se avevi indicizzato solo status, il filtro può restare veloce, ma l'ordinamento ora può forzare lavoro aggiuntivo.

Esempio: mappare schermate reali all'indice giusto

Una tabella decisionale aiuta solo se la applichi alle schermate reali che pubblichi. Ecco tre schermate comuni e come si allineano con scelte di indice.

Un modo pratico per applicare questo è partire dall'UI:

• Elenca ogni controllo che restringe i risultati (filtri).
• Annota l'ordinamento predefinito.
• Sii specifico sul comportamento di ricerca (full-text vs starts-with vs contains).
• Evidenzia i campi “speciali” (JSONB, geo, range).

Passi successivi: rendi l'indicizzazione parte del tuo processo di sviluppo

Buoni indici seguono le tue schermate: i filtri che gli utenti cliccano, l'ordinamento che si aspettano e la casella di ricerca che effettivamente usano. Tratta l'indicizzazione come un'abitudine durante lo sviluppo e eviterai la maggior parte delle sorprese di prestazioni più avanti.

Rendilo ripetibile: identifica le 1-3 query che una schermata esegue, aggiungi il più piccolo indice che le corrisponde, testa con dati realistici, poi rimuovi ciò che non ripaga.

Se stai costruendo uno strumento interno o un portale clienti, pianifica gli indici presto perché queste app crescono spesso aggiungendo filtri e più schermate elenco. Se costruisci con AppMaster (appmaster.io), aiuta trattare la configurazione di filtri e ordinamenti di ogni schermata come un pattern di query concreto, poi aggiungere solo gli indici che corrispondono a quei click reali.