Ricerca Full-Text in PostgreSQL: tsvector, Indici GIN e Ranking

La ricerca testuale sembra una funzionalità banale finché non la si costruisce davvero. Il catalogo prodotti, l'archivio dei documenti, i ticket di assistenza: prima o poi qualcuno chiede di poter cercare dentro ai testi, e la prima risposta è quasi sempre una query con LIKE. Funziona, sul portatile, con venti righe di prova. Poi la tabella cresce e la ricerca diventa lenta, imprecisa e impossibile da ordinare per rilevanza.

PostgreSQL ha una risposta integrata a questo problema da molti anni: la ricerca full-text nativa. Non è un motore di ricerca completo come Elasticsearch, ma copre una fetta di casi molto più ampia di quanto si pensi, senza aggiungere un servizio da installare, sincronizzare e monitorare. Vediamo come funziona, come si indicizza e soprattutto dove sono i limiti.

Perché LIKE non basta

Il problema di ILIKE '%camicia%' è che il pattern inizia con un carattere jolly. Un indice B-tree normale non può essere usato, perché non esiste un punto di partenza nell'ordinamento: il database deve leggere ogni riga e confrontare la stringa. Su una tabella di centomila righe con testi lunghi questo si traduce in un Seq Scan che cresce linearmente.

EXPLAIN ANALYZE
SELECT id, titolo
FROM articoli
WHERE titolo ILIKE '%camic%';

-- Seq Scan on articoli  (cost=... rows=...)
--   Filter: (titolo ~~* '%camic%'::text)

Ma il costo maggiore non è la velocità, è la semantica. LIKE non sa che camicia e camicie sono la stessa parola, non sa che perché e perche dovrebbero trovarsi a vicenda, non sa nulla di rilevanza: restituisce le righe nell'ordine in cui le incontra. E non può combinare più termini con logica booleana senza concatenare una serie di condizioni sempre più illeggibili.

tsvector e tsquery: il modello mentale

La ricerca full-text di PostgreSQL non confronta stringhe, confronta lessemi. Il documento viene normalizzato in un tsvector: un elenco ordinato di parole significative, con le posizioni in cui compaiono.

SELECT to_tsvector('italian', 'Le camicie di lino sono le migliori per l''estate');

-- 'camic':2 'estat':9 'lino':5 'miglior':7

Notare cosa è successo: gli articoli e le preposizioni (le, di, sono, per) sono spariti perché sono stopword, e camicie, estate, migliori sono stati ridotti alla radice. Questa operazione si chiama stemming e cambia completamente il gioco: cercare camicia troverà anche camicie.

Dall'altra parte c'è il tsquery, l'espressione di ricerca:

SELECT to_tsquery('italian', 'camicia & lino');
SELECT to_tsquery('italian', 'camicia & !seta');   -- esclude seta
SELECT to_tsquery('italian', 'camicia <-> lino');   -- lino subito dopo camicia

to_tsquery però ha una sintassi rigida: se l'utente scrive una parentesi o un & in mezzo alle parole, la query esplode con un errore di parsing. Per l'input che arriva da un campo di ricerca esistono due funzioni più adatte:

• plainto_tsquery('italian', $1) — prende il testo grezzo e lo mette in AND tra i termini.
• websearch_to_tsquery('italian', $1) — capisce virgolette per le frasi esatte, OR e - per escludere. È quella che conviene usare quando la stringa arriva da una persona.

SELECT websearch_to_tsquery('italian', 'camicia lino -seta');
-- 'camic' & 'lino' & !'seta'

Un errore comune è usare to_tsquery con l'input utente e ritrovarsi con errori 500 ogni volta che qualcuno digita una virgoletta. websearch_to_tsquery è nata esattamente per questo.

La configurazione linguistica conta

La configurazione passata come primo argomento decide stopword e stemming. PostgreSQL include una configurazione italian completa, ma non è sempre quella giusta.

Il caso classico è la colonna dei codici articolo: applicare lo stemming a ART-1234/B è inutile e potenzialmente dannoso. Meglio due colonne, o due vettori, con configurazioni diverse.

Un altro dettaglio utile è unaccent, che rimuove i segni diacritici:

CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT to_tsvector('italian', unaccent('Perché è così bello'));
-- 'bell':4 'cos':3 'perche':1

Attenzione a un dettaglio pratico: unaccent() non è marcata IMMUTABLE, perché dipende da un file di dizionario che potrebbe cambiare. Per usarla dentro una colonna generata o un indice funzionale serve una funzione wrapper dichiarata immutabile:

CREATE OR REPLACE FUNCTION immutable_unaccent(text)
RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$$ SELECT public.unaccent('public.unaccent', $1) $$;

È una pratica diffusa, con un'avvertenza onesta: se il dizionario unaccent viene aggiornato, gli indici costruiti su questa funzione vanno ricostruiti. In pratica succede raramente, ma è bene saperlo.

Salvare il vettore in una colonna generata

Il vettore va materializzato, altrimenti va calcolato a ogni riga a ogni query. Da PostgreSQL 12 si può usare una colonna generata, che elimina il vecchio pattern a base di trigger.

ALTER TABLE articoli
  ADD COLUMN ricerca tsvector
  GENERATED ALWAYS AS (
      setweight(to_tsvector('italian', immutable_unaccent(coalesce(titolo, ''))), 'A') ||
      setweight(to_tsvector('italian', immutable_unaccent(coalesce(sommario, ''))), 'B') ||
      setweight(to_tsvector('italian', immutable_unaccent(coalesce(corpo, ''))), 'C')
  ) STORED;

Due cose da notare. La prima: coalesce è obbligatorio, perché concatenare un NULL produce NULL e il vettore sparisce silenziosamente. La seconda: setweight assegna un peso alle diverse parti del documento, che servirà al momento del ranking. Una parola trovata nel titolo vale più della stessa parola trovata in fondo al testo.

Il vantaggio della colonna generata rispetto al trigger è che non può andare fuori sincrono: è il database a garantirne il contenuto. Con i trigger, prima o poi qualcuno aggiorna la tabella con una UPDATE massiva disabilitando i trigger e il vettore resta vecchio.

L'indice: GIN contro GiST

Senza indice, la condizione ricerca @@ query è un'altra scansione completa. L'indice giusto è di tipo GIN:

CREATE INDEX idx_articoli_ricerca ON articoli USING GIN (ricerca);

Per quasi tutti i casi d'uso la scelta è GIN. Diventa interessante valutare GiST quando la tabella ha un volume di scrittura molto alto e la ricerca è un caso secondario, perché GIN paga il costo di aggiornamento a ogni INSERT.

Su tabelle molto aggiornate vale la pena conoscere fastupdate, il parametro che accumula le modifiche in una lista in attesa invece di aggiornare subito la struttura. È attivo di default e sposta il costo dal INSERT alla lettura, quando la lista viene svuotata. Se le query di ricerca iniziano a rallentare senza motivo, vale la pena controllare pg_stat_user_indexes e i tempi di autovacuum prima di dare la colpa alla query.

Cercare e ordinare per rilevanza

Con l'indice a posto, la query diventa compatta:

SELECT id, titolo, ts_rank(ricerca, q) AS rank
FROM articoli, websearch_to_tsquery('italian', $1) AS q
WHERE ricerca @@ q
ORDER BY rank DESC
LIMIT 20;

ts_rank restituisce un numero che non ha un significato assoluto: serve solo a ordinare i risultati tra loro. Dipende dai pesi assegnati con setweight e dalla lunghezza del documento, quindi confrontare il rank di due tabelle diverse non ha senso. Esiste anche ts_rank_cd, che tiene conto della vicinanza dei termini tra loro: se cerchi camicia lino, un documento in cui le due parole sono nella stessa frase viene premiato rispetto a uno in cui compaiono a dieci paragrafi di distanza.

Attenzione a un dettaglio di prestazioni: ORDER BY ts_rank(...) LIMIT 20 obbliga a calcolare il rank per tutte le righe che soddisfano la condizione, non solo per venti. Se la ricerca è generica e matcha diecimila righe, il costo si sente. Le contromisure sono filtrare prima con altre condizioni (categoria, data, stato) e accettare che una ricerca molto ampia sia intrinsecamente costosa.

Per mostrare un estratto con i termini evidenziati c'è ts_headline:

SELECT id,
       ts_headline('italian', corpo, q,
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15') AS estratto
FROM articoli, websearch_to_tsquery('italian', $1) AS q
WHERE ricerca @@ q
ORDER BY ts_rank(ricerca, q) DESC
LIMIT 20;

ts_headline è costosa: ricalcola il parsing del documento originale riga per riga. Va applicata solo alle righe già filtrate e limitate, mai dentro una sottoquery che tocca tutta la tabella.

Errori di battitura: pg_trgm

La ricerca full-text non perdona i refusi. Se l'utente scrive camiciee o salmasoo, lo stemming non salva nulla, perché il lessema semplicemente non esiste. Per questo serve un secondo strumento: i trigrammi.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_clienti_nome_trgm
  ON clienti USING GIN (nome gin_trgm_ops);

SET pg_trgm.similarity_threshold = 0.3;

SELECT nome, similarity(nome, 'salmaso') AS sim
FROM clienti
WHERE nome % 'salmaso'
ORDER BY sim DESC
LIMIT 10;

L'operatore % verifica se la similarità supera la soglia, e similarity() restituisce un valore tra 0 e 1. La differenza rispetto al full-text è concettuale: i trigrammi misurano quanto due stringhe si somigliano carattere per carattere, non quanto sono vicine di significato. Sono perfetti per nomi propri, ragioni sociali, codici digitati male. Sono inutili per capire che automobile e macchina sono la stessa cosa.

Combinare i due approcci

Nella pratica convivono. Una query di ricerca decente prova prima il match esatto sui lessemi e poi recupera i quasi-match per somiglianza, fondendo i punteggi:

SELECT id, titolo, max(score) AS score
FROM (
    SELECT a.id, a.titolo, ts_rank(a.ricerca, q) AS score
    FROM articoli a, websearch_to_tsquery('italian', $1) AS q
    WHERE a.ricerca @@ q

    UNION ALL

    SELECT a.id, a.titolo, similarity(a.titolo, $1) * 0.3 AS score
    FROM articoli a
    WHERE a.titolo % $1
) AS risultati
GROUP BY id, titolo
ORDER BY score DESC
LIMIT 20;

Il GROUP BY serve perché lo stesso articolo può arrivare da entrambi i rami, e prenderne il punteggio massimo è la scelta più semplice. Il fattore 0.3 è arbitrario: serve solo a far scendere i match per sola somiglianza sotto quelli trovati dal full-text.

Un'ultima nota: full-text e ricerca vettoriale non sono alternative. Il primo è lessicale, la seconda è semantica, e nelle architetture ibride i due elenchi di risultati si fondono con tecniche come la Reciprocal Rank Fusion. Ma questo è un discorso a parte, che vale la pena affrontare solo quando la ricerca lessicale ha già dimostrato i suoi limiti.

Quando PostgreSQL non basta

Ci sono casi in cui insistere con la ricerca integrata è la scelta sbagliata:

• Corpus molto grande e distribuito. Milioni di documenti con requisiti di latenza bassa su più nodi.
• Analisi linguistica avanzata. Sinonimi personalizzati, stemming specifico di dominio, gestione di lingue senza spazi tra le parole.
• Funzionalità di ricerca vere. Facet, aggregazioni, suggerimenti mentre si digita, ordinamenti personalizzati che dipendono da decine di segnali.
• Carico di scrittura elevatissimo. Dove il costo di aggiornamento degli indici GIN diventa il collo di bottiglia.

In quei casi la risposta sono Elasticsearch, OpenSearch, Meilisearch o Typesense. Il prezzo è un secondo sistema da gestire, sincronizzare e monitorare, con la sincronizzazione stessa che diventa una pipeline da mantenere: esattamente il tipo di complessità che conviene rimandare finché non è giustificata da numeri reali.

La regola pratica che funziona bene: se i dati sono già in PostgreSQL e il corpus resta sotto qualche milione di documenti, la ricerca full-text nativa è quasi sempre la scelta giusta. Si può sempre migrare dopo, avendo nel frattempo una funzionalità che gira.

Conclusione

• LIKE '%parola%' non usa indici e non capisce la lingua: va bene solo per le prove.
• La ricerca full-text lavora su lessemi, non su stringhe: tsvector normalizza il documento, tsquery esprime la ricerca.
• Per l'input degli utenti si usa websearch_to_tsquery, non to_tsquery: evita errori di sintassi e supporta virgolette e esclusioni.
• La configurazione linguistica va scelta con cura: simple per codici e nomi propri, italian per i testi.
• Il vettore va materializzato in una colonna generata, con setweight per dare più peso al titolo.
• L'indice giusto è GIN; GiST ha senso solo con scritture molto intense e ricerche secondarie.
• ts_rank serve a ordinare, non ha valore assoluto; ts_headline va applicata solo alle righe già filtrate.
• Per i refusi servono i trigrammi di pg_trgm, che rispondono a una domanda diversa dalla ricerca full-text.
• Un motore dedicato ha senso solo quando dimensione, funzionalità o carico lo giustificano davvero.

Foto di copertina di Zetong Li su Unsplash.