Indicizzazione PostgreSQL per app SaaS: btree vs GIN vs GiST

Quale problema risolvono gli indici nelle schermate reali di un SaaS

Un indice cambia il modo in cui PostgreSQL trova le righe. Senza un indice, il database spesso deve leggere gran parte della tabella (una scansione sequenziale) e poi scartare la maggior parte dei risultati. Con l'indice giusto può saltare direttamente alle righe che corrispondono (una lookup sull'indice), poi recuperare solo ciò che serve.

Te ne accorgi presto in SaaS perché le schermate di tutti i giorni sono ricche di query. Un singolo click può innescare molte letture: la pagina elenco, un conteggio totale, qualche card della dashboard e una casella di ricerca. Quando una tabella cresce da migliaia a milioni di righe, la stessa query che sembrava istantanea comincia a rallentare.

Un esempio tipico è la pagina Orders filtrata per stato e data, ordinata dal più recente, con paginazione. Se PostgreSQL deve scansionare l'intera tabella orders per trovare gli ordini pagati degli ultimi 30 giorni, ogni caricamento di pagina fa lavoro extra. Un buon indice trasforma questo in un salto rapido alla fetta giusta di dati.

Gli indici non sono gratis. Ognuno accelera le letture per query specifiche, ma rallenta le scritture (INSERT/UPDATE/DELETE devono aggiornare gli indici) e usa più spazio su disco (oltre a mettere pressione sulla cache). Questo compromesso è il motivo per cui dovresti partire dai pattern reali di query, non dal tipo di indice.

Una regola semplice che evita lavoro inutile: aggiungi un indice solo quando puoi indicare una query specifica e frequente che ne trarrà vantaggio. Se costruisci schermate con un builder guidato da chat come Koder.ai, è utile catturare la SQL dietro le tue pagine elenco e dashboard e usarla come lista desideri per gli indici.

B-tree vs GIN vs GiST in termini semplici

La maggior parte della confusione sugli indici scompare quando smetti di pensare alle feature (JSON, ricerca, array) e inizi a pensare alla forma della query: cosa fa la clausola WHERE e come ti aspetti che siano ordinati i risultati?

B-tree: veloce per ordinamento e confronti

Usa B-tree quando la tua query somiglia a confronti normali e tieni all'ordine. È il cavallo da tiro per uguaglianze, intervalli e join.

Esempi di forma: filtrare per tenant_id = ?, status = 'active', created_at \u003e= ?, joinare users.id = orders.user_id, o mostrare “più recenti prima” con ORDER BY created_at DESC.

GIN e GiST: quando una riga contiene molti valori ricercabili

GIN (Generalized Inverted Index) è adatto quando una colonna contiene molti elementi e ti chiedi “contiene X?”. È comune con chiavi JSONB, elementi array e vettori full-text.

Esempi di forma: metadata @\u003e {'plan':'pro'} su JSONB, tags @\u003e ARRAY['urgent'], o to_tsvector(body) @@ plainto_tsquery('reset password').

GiST (Generalized Search Tree) si adatta a domande su distanza o overlap, dove i valori si comportano come intervalli o forme. Si usa spesso per tipi range, dati geometrici e alcune ricerche di “migliore corrispondenza”.

Esempi di forma: finestre temporali che si sovrappongono con colonne range, alcune ricerche per similarità (ad esempio con operatori trigram), o query spaziali (se usi PostGIS).

Un modo pratico per scegliere:

• Se filtri o ordini per colonne normali, parti da B-tree.
• Se controlli contenimento o appartenenza, guarda GIN.
• Se chiedi “quanto è vicino” o “si sovrappone”, GiST è spesso la scelta.
• Se una query è rara o la tabella è piccola, potresti non aver bisogno di un nuovo indice.
• Se non riesci a descrivere la forma della query, misura prima (EXPLAIN) prima di aggiungere qualsiasi cosa.

Gli indici velocizzano le letture, ma costano in tempo di scrittura e disco. In SaaS questo compromesso conta di più su tabelle calde come events, sessions e activity logs.

Pattern B-tree per filtri, ordinamento e paginazione

La maggior parte delle schermate elenco SaaS condivide la stessa forma: un confine tenant, un paio di filtri e un ordinamento prevedibile. Gli indici B-tree sono la scelta predefinita qui e solitamente i meno costosi da mantenere.

Un pattern comune è WHERE tenant_id = ? più filtri come status = ?, user_id = ?, e un intervallo temporale come created_at \u003e= ?. Per indici B-tree compositi, metti prima i filtri di uguaglianza (colonne confrontate con =), poi aggiungi la colonna per cui ordini.

Regole che funzionano nella maggior parte delle app:

• Parti da tenant_id se ogni query è limitata per tenant.
• Metti poi i filtri con = (spesso status, user_id).
• Metti per ultimo la colonna di ORDER BY (spesso created_at o id).
• Usa INCLUDE per coprire le pagine elenco senza allargare le chiavi.
• Preferisci la paginazione seek rispetto a OFFSET quando le pagine diventano profonde.

Un esempio realistico: una pagina Tickets che mostra gli elementi più recenti per primo, filtrata per status.

-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 50;

-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);

Quell'indice supporta sia il filtro che l'ordinamento, così Postgres può evitare di ordinare un grande set di risultati. La parte INCLUDE (title) aiuta la pagina elenco a toccare meno pagine della tabella, mantenendo le chiavi dell'indice focalizzate su filtro e ordinamento.

Per intervalli temporali, lo stesso concetto si applica:

SELECT id, created_at
FROM events
WHERE tenant_id = $1
  AND created_at \u003e= $2
  AND created_at \u003c  $3
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);

La paginazione è dove molte app SaaS rallentano. La paginazione con OFFSET (OFFSET 50000) costringe il database a scorrere molte righe. La paginazione seek resta veloce usando l'ultima chiave vista:

SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
  AND created_at \u003c $2
ORDER BY created_at DESC
LIMIT 50;

Con il B-tree giusto questo resta veloce anche quando la tabella cresce.

Indicizzazione attenta per tenant senza sovra-indicizzare

La maggior parte delle app SaaS sono multi-tenant: ogni query deve restare dentro un tenant. Se i tuoi indici non includono tenant_id, Postgres può comunque trovare righe velocemente, ma spesso scansiona molte più voci di indice del necessario. Gli indici tenant-aware mantengono i dati di ogni tenant raggruppati nell'indice così le schermate comuni restano veloci e prevedibili.

Una regola semplice: metti tenant_id per primo nell'indice quando la query filtra sempre per tenant. Poi aggiungi la colonna che filtri o ordini più spesso.

Indici noiosi ma ad alto impatto spesso sembrano così:

• (tenant_id, created_at) per liste degli elementi recenti e paginazione con cursore
• (tenant_id, status) per filtri di stato (Open, Paid, Failed)
• (tenant_id, user_id) per schermate “item di proprietà di questo utente”
• (tenant_id, updated_at) per viste admin “modificati di recente”
• (tenant_id, external_id) per lookup da webhook o import

Il sovra-indicizzare avviene quando aggiungi un nuovo indice per ogni schermata leggermente diversa. Prima di crearne un altro, controlla se un indice composito esistente copre già le colonne più a sinistra necessarie. Per esempio, se hai (tenant_id, created_at), di solito non hai anche bisogno di (tenant_id, created_at, id) a meno che non filtri davvero su id dopo quelle colonne.

Gli indici parziali possono ridurre dimensione e costo di scrittura quando la maggior parte delle righe non è rilevante. Funzionano bene con soft delete e dati “solo attivi”, per esempio: indicizza solo dove deleted_at IS NULL, o solo dove status = 'active'.

Ogni indice aggiuntivo rende le scritture più pesanti. Gli insert devono aggiornare ogni indice, e gli update possono toccare più indici anche quando modifichi una sola colonna. Se la tua app riceve molti eventi (incluso il tipo di app costruite velocemente con Koder.ai), mantieni gli indici focalizzati sulle poche forme di query che gli utenti colpiscono ogni giorno.

Indicizzare JSONB: GIN e indici di espressione mirati

JSONB è comodo quando la tua app ha campi extra flessibili come feature flag, attributi utente o impostazioni per tenant. Il problema è che diversi operatori JSONB si comportano in modo diverso, quindi il miglior indice dipende da come interroghi i dati.

Due forme contano di più:

• Containment: “Questo JSON contiene queste coppie chiave-valore?” usando @\u003e.
• Estrazione per percorso: “Qual è il valore di questo campo specifico?” usando -> / ->> (spesso confrontato con =).

Quando un indice GIN è la scelta giusta

Se filtri frequentemente con @\u003e, un indice GIN sulla colonna JSONB di solito paga.

-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata @\u003e '{"region":"eu","plan":"pro"}';

-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);

Se la struttura JSON è prevedibile e usi principalmente @\u003e su chiavi di primo livello, jsonb_path_ops può essere più piccolo e veloce, ma supporta meno tipi di operatori.

Quando un indice di espressione è migliore

Se la tua UI filtra ripetutamente su un campo (come plan), estrarre quel campo e indicizzarlo spesso è più veloce ed economico di un GIN ampio.

SELECT id
FROM accounts
WHERE tenant_id = $1
  AND metadata->> 'plan' = 'pro';

CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));

Una regola pratica: tieni JSONB per attributi flessibili e raramente filtrati, ma promuovi in colonne reali i campi stabili e ad alto utilizzo (plan, status, created_at). Se iteri velocemente su un'app generata, spesso è una semplice modifica di schema quando vedi quali filtri compaiono su ogni pagina.

Esempio: se memorizzi {"tags":["beta","finance"],"region":"us"} in JSONB, usa GIN quando filtri per pacchetti di attributi (@\u003e), e aggiungi indici di espressione per le poche chiavi che guidano la maggior parte delle liste (plan, region).

Indicizzare array: quando GIN brilla

Gli array sono attraenti perché sono facili da memorizzare e leggere. Una colonna users.roles text[] o projects.labels text[] può funzionare bene quando la domanda principale è: questa riga contiene un valore (o un set di valori)? È proprio lì che un indice GIN aiuta.

GIN è la scelta migliore per query di membership su array. Scompone l'array in elementi individuali e costruisce una lookup veloce alle righe che li contengono.

Forme di query per array che spesso beneficiano:

• Contiene un valore o un set: @\u003e (array contains)
• Sovrapposizione con un set: && (array condivide elementi)
• A volte: = ANY(...), ma @\u003e è spesso più prevedibile

Un esempio tipico per filtrare utenti per ruolo:

-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @\u003e ARRAY['admin'];

CREATE INDEX users_roles_gin ON users USING GIN (roles);

E filtrare progetti per un set di etichette (deve includere entrambe):

SELECT id, name
FROM projects
WHERE labels @\u003e ARRAY['billing', 'urgent'];

CREATE INDEX projects_labels_gin ON projects USING GIN (labels);

Dove le persone si sorprendono: alcuni pattern non usano l'indice come ti aspetti. Se trasformi l'array in stringa (array_to_string(labels, ',')) e poi fai LIKE, l'indice GIN non aiuterà. Inoltre, se ti servono "starts with" o match fuzzy dentro le label, sei nel territorio della ricerca testuale, non della membership di array.

Gli array possono diventare difficili da mantenere quando si trasformano in un mini-database: aggiornamenti frequenti, bisogno di metadati per elemento (chi ha aggiunto l'etichetta, quando, perché), o necessità di analisi per etichetta. A quel punto, una tabella di join come project_labels(project_id, label) è di solito più facile da validare, interrogare ed evolvere.

Indicizzazione per la ricerca: full-text e matching fuzzy (GIN e GiST)

Per le caselle di ricerca ricorrono due pattern: full-text search (trovare record su un argomento) e fuzzy matching (gestire refusi, nomi parziali e pattern ILIKE). L'indice giusto fa la differenza tra “istantaneo” e “timeout a 10k utenti”.

Full-text search: tsvector + GIN

Usa la ricerca full-text quando gli utenti digitano parole reali e vuoi risultati ordinati per rilevanza, come cercare ticket per oggetto e descrizione. La configurazione usuale è memorizzare un tsvector (spesso in una colonna generata) e indicizzarlo con GIN. Si cerca con @@ e un tsquery.

-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
  to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;

CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);

-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');

-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);

SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';

Cosa includere nel vettore: solo i campi che effettivamente cerchi. Se includi tutto (note, log interni), paghi in dimensione dell'indice e costo di scrittura.

Matching fuzzy: trigrammi con GIN o GiST

Usa la similarità a trigrammi quando gli utenti cercano nomi, email o frasi brevi e ti servono match parziali o tolleranza ai refusi. I trigrammi aiutano con ILIKE '%term%' e operatori di similarità. GIN è solitamente più veloce per lookup “corrisponde o no”; GiST può essere migliore quando ti interessa anche ordinare per similarità.

Regole pratiche:

• Usa GIN + tsvector per ricerca testuale basata su rilevanza.
• Usa trigrammi per ILIKE e ricerche nome tolleranti ai refusi.

Trappole da tenere d'occhio:

• Wildcard iniziali senza trigrammi (ILIKE '%abc') forzano scansioni.
• Termini di ricerca molto corti (1-2 caratteri) spesso non sfruttano bene i trigrammi.
• Stop words e stemming possono sorprendere negli esiti full-text, quindi scegli una configurazione che corrisponda alla lingua del prodotto.

Se stai lanciando schermate di ricerca velocemente, tratta l'indice come parte della feature: UX di ricerca e scelta dell'indice devono essere progettate insieme.

Passo dopo passo: dalla query lenta all'indice giusto

Parti dalla query esatta che la tua app esegue, non da un'ipotesi. Una “schermata lenta” è solitamente una singola istruzione SQL con WHERE e ORDER BY specifici. Copiala dai log, dall'output di debug del tuo ORM o da qualsiasi cosa usi per catturare le query.

Un workflow che funziona nelle app reali:

• Cattura l'SQL completo, inclusi WHERE, ORDER BY e LIMIT.
• Esegui EXPLAIN (ANALYZE, BUFFERS) sulla stessa query.
• Concentrati sugli operatori che fanno il lavoro (=, \u003e=, LIKE, @\u003e, @@), non solo sui nomi delle colonne.
• Aggiungi il più piccolo indice che corrisponde a quegli operatori.
• Riesegui EXPLAIN (ANALYZE, BUFFERS) con volumi di dati realistici.

Ecco un esempio concreto. Una pagina Customers filtra per tenant e status, ordina per i più recenti e fa paginazione:

SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;

Se EXPLAIN mostra una scansione sequenziale e una sort, un indice B-tree che corrisponde al filtro e all'ordinamento spesso lo risolve:

CREATE INDEX ON customers (tenant_id, status, created_at DESC);

Se la parte lenta è un filtro JSONB come metadata @\u003e '{"plan":"pro"}', quello punta a GIN. Se è una ricerca full-text come to_tsvector(...) @@ plainto_tsquery(...), anche quella punta a un indice di ricerca basato su GIN. Se è un caso di "closest match" o operatori di overlap, GiST è spesso la soluzione.

Dopo aver aggiunto l'indice misura il compromesso. Controlla dimensione dell'indice, tempo di insert e update e se aiuta le principali query lente o solo un caso di nicchia. In progetti che si muovono rapidamente (inclusi quelli costruiti su Koder.ai), questo controllo aiuta a evitare di accumulare indici inutilizzati.

Errori comuni di indicizzazione che sprecano tempo e soldi

La maggior parte dei problemi non è scegliere B-tree vs GIN vs GiST. È creare un indice che sembra giusto ma non corrisponde a come l'app interroga la tabella.

Errori che danneggiano di più:

• Indici che non vengono mai usati. La query usa un operatore diverso da quello che l'indice supporta, o un indice composito ha l'ordine delle colonne sbagliato. Se la tua WHERE inizia con tenant_id e created_at, ma l'indice inizia con created_at, il planner potrebbe evitarlo.
• Indicizzare colonne a bassa cardinalità da sole. Un indice su status, is_active o un booleano spesso aiuta poco perché corrisponde a troppe righe. Abbinati a una colonna selettiva (come tenant_id o created_at) o evitalo.
• Indici sovrapposti che ingolfano e rallentano le scritture. Indici simili sulla stessa tabella possono duplicare lo spazio e rallentare insert/update.
• Trappole della paginazione. Indicizzare solo per supportare OFFSET è comune e sbagliato. Se usi paginazione keyset, ti serve un indice che corrisponda all'ordinamento e al filtro dell'ultima chiave vista.
• Statistiche obsolete e tabelle ingombranti. Se autovacuum non tiene il passo, o ANALYZE non è stato eseguito di recente, il planner può scegliere piani sbagliati anche quando l'indice giusto esiste.

Un esempio concreto: la tua schermata Invoices filtra per tenant_id e status, poi ordina per created_at DESC. Un indice solo su status aiuterà poco. Una soluzione migliore è un indice composito che inizi con tenant_id, poi status, poi created_at (filter prima, sort dopo). Quel singolo cambiamento spesso batte l'aggiunta di tre indici separati.

Tratta ogni indice come un costo. Deve guadagnarsi il posto nelle query reali, non solo in teoria.

Checklist rapida prima di distribuire modifiche agli indici

Le modifiche agli indici sono facili da spedire e fastidiose da annullare se aggiungono costo di scrittura o bloccano una tabella molto usata. Prima di mergiare, trattalo come un piccolo rilascio.

Inizia decidendo cosa stai ottimizzando. Estrai due brevi graduatorie dai log o dal monitoring: le query che vengono eseguite più spesso e le query con la latenza più alta. Per ciascuna, annota la forma esatta: colonne di filtro, ordine, join e operatori usati (equals, range, IN, ILIKE, operatori JSONB, contiene array). Questo evita ipotesi e aiuta a scegliere il tipo di indice giusto.

Checklist pre-distribuzione:

• Conferma che la query sia scoping per tenant dove necessario.
• Abbina l'indice all'operatore: B-tree per equality/range/sort, GIN per membership (array, JSONB, full text), GiST per casi di overlap o distanza.
• Preferisci un indice composito che corrisponda al filtro comune + sort, invece di vari indici a colonna singola.
• Mantieni l'indice snello: includi solo le colonne che la query usa davvero.
• Pianifica il rollout: la creazione dell'indice bloccherà le scritture? Serve farla fuori orario?

Dopo aver aggiunto l'indice verifica che abbia migliorato il caso reale. Esegui EXPLAIN (ANALYZE, BUFFERS) sulla query esatta e confronta prima vs dopo. Poi osserva il comportamento in produzione per un giorno:

• È diminuita la latenza di lettura per le schermate target?
• È aumentata la latenza di scrittura (insert/update)?
• CPU o storage sono saliti a causa del nuovo indice?
• L'indice è effettivamente usato o è peso morto?

Se costruisci con Koder.ai, vale la pena tenere la SQL generata per una o due schermate lente accanto alla modifica, così l'indice corrisponde a quello che l'app esegue realmente.

Esempio: indicizzare un workflow tipico SaaS + prossimi passi

Immagina una schermata admin comune: lista Users con scoping per tenant, qualche filtro, ordinamento per ultimo accesso e una casella di ricerca. Qui gli indici smettono di essere teoria e iniziano a risparmiare tempo reale.

Tre forme di query che vedrai di solito:

-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;

-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
  AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;

-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
  AND metadata @\u003e '{"plan":"pro"}'::jsonb;

Un set di indici piccolo ma intenzionale per questa schermata:

• B-tree composito per la lista: (tenant_id, status, last_active_at DESC).
• GIN per la ricerca: una colonna tsvector generata con indice GIN.
• Indicizzazione JSONB basata sull'uso: GIN (metadata) quando usi molto @\u003e, o un B-tree di espressione come ((metadata->> 'plan')) quando filtri soprattutto su una singola chiave.

I bisogni misti sono normali. Se una pagina fa filtri + ricerca + JSON, evita di infilare tutto in un mega-indice. Mantieni il B-tree per ordinamento/paginazione, poi aggiungi un indice specializzato (spesso GIN) per la parte costosa.

Prossimi passi: scegli una schermata lenta, annota le sue 2-3 forme di query principali e rivedi ogni indice per scopo (filtro, sort, ricerca, JSON). Se un indice non corrisponde chiaramente a una query reale, eliminalo dal piano. Se iteri rapidamente con Koder.ai, fare questa revisione mentre aggiungi nuove schermate può prevenire l'accumulo di indici mentre lo schema è ancora in cambiamento.