Abilitare e usare la ricerca FULLTEXT in PostgreSQL

PostgreSQL integra un potente motore di Full-Text Search (FTS) che consente di indicizzare e cercare testo in modo rapido e intelligente (stemming, stop-words, ranking, evidenziazione). In questa guida configuriamo l’italiano, creiamo gli indici, scriviamo query efficaci e otteniamo risultati ordinati e con snippet evidenziati.

Prerequisiti e concetti base

  • tsvector: rappresentazione indicizzabile di un testo.
  • tsquery: espressione di ricerca (operatori &, |, !, prefissi :*, ecc.).
  • Configurazione linguistica: migliora stemming/stop-words (es. pg_catalog.italian).
  • Indici: GIN (consigliato) o GiST su colonne/espessioni tsvector.

1) Scegliere la lingua e (opzionale) rendere la ricerca accent-insensitive

Verifica e imposta la configurazione di default:

SHOW default_text_search_config;
SET default_text_search_config = 'pg_catalog.italian'; -- per la sessione corrente

Per ignorare gli accenti (es. “università” ≈ “universita”) usa l’estensione unaccent e una configurazione dedicata:

CREATE EXTENSION IF NOT EXISTS unaccent;

-- Crea una config italiana che applica unaccent prima dello stemming
CREATE TEXT SEARCH CONFIGURATION it_unaccent ( COPY = pg_catalog.italian );
ALTER TEXT SEARCH CONFIGURATION it_unaccent
ALTER MAPPING FOR hword, hword_part, word
WITH unaccent, italian_stem;

2) Modellazione: colonna generata o trigger?

Opzione A — Colonna generata (PostgreSQL 12+)

Comoda e manutenzione minima: la colonna tsvector si aggiorna automaticamente.

-- Esempio di tabella
CREATE TABLE posts (
  id           bigserial PRIMARY KEY,
  title        text,
  subtitle     text,
  body         text,
  -- colonna tsvector generata con pesi diversi per campo
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('it_unaccent', coalesce(title,'')),    'A') ||
    setweight(to_tsvector('it_unaccent', coalesce(subtitle,'')), 'B') ||
    setweight(to_tsvector('it_unaccent', coalesce(body,'')),     'C')
  ) STORED
);

-- Indice GIN sulla colonna tsvector
CREATE INDEX idx_posts_fts ON posts USING GIN (search_vector);

Opzione B — Trigger (compatibile con versioni più vecchie)

ALTER TABLE posts ADD COLUMN search_vector tsvector;

CREATE OR REPLACE FUNCTION posts_fts_trg() RETURNS trigger AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('it_unaccent', coalesce(NEW.title,'')),    'A') ||
setweight(to_tsvector('it_unaccent', coalesce(NEW.subtitle,'')), 'B') ||
setweight(to_tsvector('it_unaccent', coalesce(NEW.body,'')),     'C');
RETURN NEW;
END

$$$LANGUAGE plpgsql;

CREATE TRIGGER tsv_update BEFORE INSERT OR UPDATE
ON posts FOR EACH ROW EXECUTE FUNCTION posts_fts_trg();

CREATE INDEX idx_posts_fts ON posts USING GIN (search_vector);

3) Popolare e verificare

INSERT INTO posts (title, subtitle, body) VALUES
('Ricerca full text in PostgreSQL', 'Introduzione pratica', 'Guida completa alla FTS...'),
('Università e ricerca', 'Accenti e stemming', 'Esempi con unaccent e configurazione italiana.');

-- Controlla il tsvector generato
SELECT id, search_vector FROM posts;

4) Scrivere query di ricerca

Ricerca booleana con operatori

Usa to_tsquery quando vuoi controllare operatori e prefissi.

-- Parole obbligatorie
SELECT id, title
FROM posts
WHERE search_vector @@ to_tsquery('it_unaccent', 'ricerca & full & text');

-- Prefisso (autocompletamento): ricerc* + postgr*
SELECT id, title
FROM posts
WHERE search_vector @@ to_tsquery('it_unaccent', 'ricerc:* & postgr:*');

Ricerca "alla Google" (PostgreSQL 11+)

websearch_to_tsquery interpreta virgolette, OR, esclusioni con - e spazi come AND impliciti.

SELECT id, title
FROM posts
WHERE search_vector @@ websearch_to_tsquery('it_unaccent',
  'postgres "full text" -mysql');

Ricerca per frase

SELECT id, title
FROM posts
WHERE search_vector @@ phraseto_tsquery('it_unaccent', 'ricerca full text');

5) Ordinare per rilevanza (ranking)

Usa ts_rank (o ts_rank_cd con normalizzazione diversa). I pesi A>B>C impostati in fase di indicizzazione influenzano il punteggio.

WITH q AS (
  SELECT websearch_to_tsquery('it_unaccent', 'ricerca full text') AS query
)
SELECT p.id, p.title,
       ts_rank(p.search_vector, q.query) AS rank
FROM posts p, q
WHERE p.search_vector @@ q.query
ORDER BY rank DESC, p.id ASC;

6) Evidenziare gli snippet con ts_headline

Genera un estratto del testo con i termini evidenziati; puoi personalizzare i marcatori HTML.

WITH q AS (
  SELECT websearch_to_tsquery('it_unaccent', 'universita ricerca') AS query
)
SELECT id,
       ts_headline(
         'it_unaccent',
         body,
         q.query,
         'StartSel=<mark>,StopSel=</mark>,MaxFragments=2,ShortWord=2,FragmentDelimiter=&hellip;'
       ) AS snippet
FROM posts, q
WHERE search_vector @@ q.query
ORDER BY id;

7) Indici: consigli pratici

  • GIN è lo standard per FTS; su tabelle molto grandi puoi creare l’indice CONCURRENTLY per evitare lock di scrittura.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_fts ON posts USING GIN (search_vector);
  • Per bulk load massivi, valuta di disabilitare l’inserimento nel pending list (fastupdate) o creare l’indice dopo l’import.
  • Esegui periodicamente VACUUM (ANALYZE) per statistiche accurate.

8) Query su colonne multiple, JSON e normalizzazione

Hai già visto come combinare più campi con pesi. Su JSON puoi estrarre e concatenare:

-- Esempio: indicizza body + tag da JSONB
ALTER TABLE posts ADD COLUMN meta jsonb;

ALTER TABLE posts
  ADD COLUMN search_vector_ext tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('it_unaccent', coalesce(body,'')), 'B') ||
    setweight(to_tsvector('it_unaccent', coalesce(meta ->> 'tags','')), 'A')
  ) STORED;

CREATE INDEX idx_posts_fts_ext ON posts USING GIN (search_vector_ext);

9) Test e debug

-- Come viene tokenizzato/stemmed il testo?
SELECT to_tsvector('it_unaccent', 'Università della ricerca in PostgreSQL');

-- Cosa genera la tua query testuale?
SELECT websearch_to_tsquery('it_unaccent', 'postgres "full text" -mysql');

-- Perché un record non combacia? Verifica con @@ e stampa il vettore:
SELECT id, search_vector, search_vector @@ to_tsquery('it_unaccent', 'ricerca')
FROM posts
ORDER BY id;

10) Checklist rapida

  1. Imposta la configurazione linguistica (pg_catalog.italian) e, se serve, crea it_unaccent.
  2. Crea una colonna tsvector (generata o con trigger) con pesi per campo.
  3. Indicizza con GIN.
  4. Interroga con websearch_to_tsquery, to_tsquery o phraseto_tsquery.
  5. Ordina con ts_rank e genera snippet con ts_headline.

Appendice: migrazione minimale esistente

-- 1) Estensione e config
CREATE EXTENSION IF NOT EXISTS unaccent;
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_ts_config WHERE cfgname = 'it_unaccent'
  ) THEN
    CREATE TEXT SEARCH CONFIGURATION it_unaccent ( COPY = pg_catalog.italian );
    ALTER TEXT SEARCH CONFIGURATION it_unaccent
      ALTER MAPPING FOR hword, hword_part, word
      WITH unaccent, italian_stem;
  END IF;
END $$;

-- 2) Colonna generata + indice
ALTER TABLE posts
  ADD COLUMN IF NOT EXISTS search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('it_unaccent', coalesce(title,'')),    'A') ||
    setweight(to_tsvector('it_unaccent', coalesce(subtitle,'')), 'B') ||
    setweight(to_tsvector('it_unaccent', coalesce(body,'')),     'C')
  ) STORED;

CREATE INDEX IF NOT EXISTS idx_posts_fts ON posts USING GIN (search_vector);

Con questi passaggi la tua applicazione ottiene ricerche testuali veloci, pertinenti e facili da mantenere, con supporto nativo di PostgreSQL e senza dipendenze esterne.

Torna su