Full-text Search PostgreSQL: guida operativa a tsvector, tsquery e ranking

Hai mai cercato un prodotto nel database con WHERE nome LIKE '%scarpa%' e aspettato secondi, ottenendo risultati parziali, senza capire perché "scarpe da ginnastica" non corrisponde a "scarpa sportiva"? È il problema classico delle ricerche testuali sui database relazionali: LIKE è lento, non gestisce declinazioni, sinonimi o ranking di pertinenza. Noi, di Meteora Web, lo abbiamo visto decine di volte nei progetti che ci arrivano. La soluzione si chiama Full-text Search nativa di PostgreSQL, basata su tsvector e tsquery, e ti permette di implementare una ricerca avanzata senza installare Elasticsearch o Apache Solr. In questa guida ti mostriamo come funziona, come configurarla e come ottimizzare il ranking dei risultati.

Perché LIKE non basta

Quando un cliente cerca "giacca impermeabile rossa" sul tuo e-commerce, non si aspetta solo le righe che contengono esattamente quella stringa. Vuole anche "giacche impermeabili", "giacche da pioggia", "giacca rossa antipioggia". LIKE opera a livello di caratteri, non di parole: non capisce che "giacche" e "giacca" sono la stessa radice, non gestisce stop words (articoli, preposizioni) e non sa dare un punteggio ai risultati più pertinenti. Inoltre, su tabelle con centinaia di migliaia di righe, una query LIKE con wildcard all'inizio (%testo%) forza un full table scan, uccidendo le performance.

PostgreSQL offre un motore di ricerca testuale integrato dal 2005 circa, maturo e performante. Lo usiamo in produzione per clienti con cataloghi di oltre 200.000 prodotti, e i tempi di risposta restano sotto i 200 ms con indicizzazione corretta.

tsvector e tsquery: i mattoni della ricerca full-text

Cosa è un tsvector

Un tsvector è una rappresentazione normalizzata del testo: suddivide il contenuto in lessemi (lexemes), ovvero la radice delle parole dopo aver applicato stemming (riduzione a forma base) e rimosso le stop word. Ogni lessema è associato alle posizioni nel testo originale, che servono per le ricerche di prossimità.

Esempio con la funzione to_tsvector:

SELECT to_tsvector('italian', 'Le scarpe da corsa sono comode e leggere');
-- Risultato: 'cors':4 'comod':6 'legger':8 'scar':3

Nota: "le" e "da" sono stop word e vengono eliminate; "scarpe" diventa "scar" (radice), "comode" → "comod", "leggere" → "legger".

Cosa è un tsquery

Un tsquery è l'espressione di ricerca normalizzata allo stesso modo. Puoi usare operatori booleani ( & per AND, | per OR, ! per NOT ) e il prefisso :* per il prefix matching.

SELECT to_tsquery('italian', 'scarpa & (comoda | leggera)');
-- Risultato: 'scar' & ( 'comod' | 'legger' )

Usiamo plainto_tsquery per query in linguaggio naturale (trasforma "scarpa comoda leggera" in 'scar' & 'comod' & 'legger'), oppure phraseto_tsquery per cercare la frase esatta.

Match semplice

SELECT * FROM prodotti
WHERE to_tsvector('italian', nome || ' ' || descrizione) @@ to_tsquery('italian', 'scarpa & comoda');

Questa query funziona, ma non è performante: calcola il tsvector al volo per ogni riga. Serve un indice.

Indice GIN: il segreto per la velocità

PostgreSQL offre due tipi di indice per full-text: GiST (Generalized Search Tree) e GIN (Generalized Inverted Index). GIN è quasi sempre la scelta migliore: più veloce nella ricerca, più lento nella scrittura, ma per carichi di lettura è imbattibile.

-- Aggiungi una colonna tsvector calcolata (gestita automaticamente via GENERATED ALWAYS)
ALTER TABLE prodotti ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('italian', coalesce(nome,'') || ' ' || coalesce(descrizione,''))) STORED;

-- Crea l'indice GIN
CREATE INDEX idx_prodotti_search ON prodotti USING GIN (search_vector);

Ora la ricerca diventa:

SELECT id, nome, descrizione,
       ts_rank(search_vector, query) AS rank
FROM prodotti, to_tsquery('italian', 'scarpa & comoda') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Attenzione: se i dati cambiano spesso, aggiornare una colonna GENERATED ALWAYS è trasparente. In ambienti con update massivi, valuta l'uso di tsvector_update_trigger (trigger) o aggiornamento batch.

Ranking: come ordinare per pertinenza

ts_rank e ts_rank_cd

ts_rank calcola un punteggio basato sulla frequenza dei termini trovati e sulla loro posizione (conta anche quante volte appaiono e se sono vicini tra loro). ts_rank_cd usa la copertura del documento (quanto del documento è coperto dai termini).

Per migliorare la qualità del ranking, puoi usare pesi diversi sulle colonne. Ad esempio, dare più importanza al titolo rispetto alla descrizione:

ALTER TABLE prodotti ADD COLUMN search_vector_weighted tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('italian', coalesce(nome,'')), 'A') ||
    setweight(to_tsvector('italian', coalesce(descrizione,'')), 'B')
  ) STORED;

CREATE INDEX idx_prodotti_weighted ON prodotti USING GIN (search_vector_weighted);

SELECT id, nome, descrizione,
       ts_rank(search_vector_weighted, query) AS rank
FROM prodotti, to_tsquery('italian', 'scarpa & comoda') AS query
WHERE search_vector_weighted @@ query
ORDER BY rank DESC
LIMIT 20;

I pesi vanno da A (massimo) a D (minimo). setweight assegna un peso a tutti i lessemi di quel vettore. ts_rank li considera nella normalizzazione.

Normalizzazione del ranking

ts_rank accetta un secondo parametro intero che specifica il metodo di normalizzazione (document length, coverage, ecc.). Il valore predefinito (0) tiene conto della lunghezza del documento. Noi, di Meteora Web, consigliamo il valore 32 (normalizza per la lunghezza del documento divisa per la media) per cataloghi omogenei, oppure 4 (solo conteggio dei termini) se vuoi dare peso assoluto. Prova entrambi.

SELECT ts_rank(search_vector_weighted, query, 32) AS rank ...

Casi avanzati: ricerche fuzzy e sinonimi

Prefix matching e wildcard

Usa :* per cercare qualsiasi parola che inizi con quel prefisso:

SELECT to_tsquery('italian', 'scar:*');
-- matcha 'scarpa', 'scarpe', 'scarpetta', ecc.

Sinonimi con thesaurus

PostgreSQL supporta il thesaurus tramite file di configurazione. Crei un file di mapping (es. italian.ths) e lo carichi. Utile per mappare "casa" → "abitazione" o "PC" → "computer". La configurazione è complessa, ma potente per domini specifici.

Ricerche fonetiche

Se ti serve una ricerca che gestisca errori di battitura, il full-text nativo non basta. Puoi combinarlo con l'estensione pg_trgm (trigrammi) per similitudine, usando un indice GiST. Ma quello è un altro capitolo.

Errori comuni e come evitarli

• Non indicizzare la colonna calcolata: se crei il tsvector al volo nella query, perdi ogni beneficio. Sempre colonna materializzata + indice GIN.
• Dimenticare la configurazione linguistica: usa sempre il codice lingua corretto ('italian' per italiano, 'english' per inglese). Se salti il parametro, PostgreSQL prende 'english' di default.
• Stop word personalizzate: se il tuo dominio usa parole che il database considera stop word (es. "tipo" in ambito moda?), puoi creare un dizionario personalizzato.
• Ignorare la normalizzazione del ranking: risultati troppo lunghi o corti possono distorcere l'ordine. Prova diversi valori di normalizzazione.

In sintesi — cosa fare adesso

1. Aggiungi una colonna tsvector alla tabella delle tue entità ricercabili (prodotti, articoli, documenti).
2. Crea un indice GIN su quella colonna.
3. Usa to_tsquery e plainto_tsquery per trasformare la ricerca utente in tsquery.
4. Ordina per ts_rank con pesi e normalizzazione adeguati.
5. Testa con dati reali: confronta i tempi prima e dopo con EXPLAIN ANALYZE.

Se vuoi approfondire ulteriormente, consulta la nostra guida pillar su PostgreSQL Avanzato dove trattiamo anche performance, JSONB e replica. Per un approccio completo all'amministrazione di server Linux, ti rimandiamo alla guida Linux per sviluppatori e sysadmin.

Noi usiamo queste tecniche ogni giorno per i nostri clienti. Funzionano. Prova anche tu.