Quando affiancare un motore di ricerca a PostgreSQL

La ricerca di un catalogo può rispondere rapidamente e restituire comunque il prodotto sbagliato. Prima di affiancare un motore a PostgreSQL, serve descrivere l’errore: manca un articolo cercato, compare un accessorio non pertinente, un refuso azzera i risultati oppure una modifica arriva troppo tardi.

La scelta che propongo parte da questi casi e dalle alternative che il team può realmente gestire. Un motore separato merita una prova quando offre un modo sostenibile di soddisfare un requisito che la soluzione attuale non raggiunge. Il numero di righe, da solo, non identifica quel requisito.

Una ricerca corretta può trovare troppo

Consideriamo un catalogo italiano con prodotti di più organizzazioni. Ogni ricerca deve restare nel catalogo dell’organizzazione autorizzata e mostrare soltanto prodotti pubblici. Nel nostro esempio, chi cerca «lampada tavolo» vuole una lampada; un tavolo che cita una lampada non inclusa deve essere escluso. È un requisito illustrativo fissato prima del confronto.

La ricerca full-text di PostgreSQL trasforma il testo in termini normalizzati e cerca le corrispondenze. Un indice GIN aiuta a individuare i documenti che contengono i termini. Il ranking assegna poi un punteggio per ordinarli: quel numero non certifica la pertinenza commerciale.

Questa baseline per PostgreSQL 18 usa la configurazione italiana e assegna al titolo un peso maggiore della descrizione. I pesi sono una scelta dell’esempio, da tarare sul catalogo. La documentazione spiega configurazione e ranking; GIN è l’indice preferito per la ricerca testuale.

Il blocco seguente crea una tabella dimostrativa in un database di prova vuoto. Richiede una connessione PostgreSQL 18 con permesso di creare tabelle e indici; può essere eseguito in una sessione psql. Tutti i dati sono sintetici.

CREATE TABLE products (
  tenant_id integer NOT NULL,
  id integer PRIMARY KEY,
  title text NOT NULL,
  description text NOT NULL DEFAULT '',
  is_public boolean NOT NULL,
  search_doc tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('italian', title), 'A') ||
    setweight(to_tsvector('italian', description), 'D')
  ) STORED
);
CREATE INDEX products_search_gin ON products USING gin(search_doc);

INSERT INTO products(tenant_id,id,title,description,is_public) VALUES
(1,101,'Lampada da tavolo','Luce orientabile',true),
(1,102,'Lampada da terra','Luce per il soggiorno',true),
(1,103,'Lampada da tavolo riservata','Prototipo',false),
(2,104,'Lampada da tavolo','Altro catalogo',true),
(1,105,'Tavolo in legno','Lampada da tavolo non inclusa',true);

PREPARE catalog_search(integer,text) AS
SELECT id, title, ts_rank_cd(search_doc, q) AS rank
FROM products, websearch_to_tsquery('italian', $2) AS q
WHERE tenant_id = $1 AND is_public AND search_doc @@ q
ORDER BY rank DESC, id
LIMIT 20;

EXECUTE catalog_search(1, 'lampada tavolo');

search_doc è una colonna calcolata dal database. $1 e $2 sono parametri, rispettivamente organizzazione e testo cercato. Nell’applicazione, l’organizzazione deve provenire dal contesto autorizzato sul server; accettare liberamente il numero inviato dal client non realizza l’isolamento. I filtri precedono logicamente il limite di venti risultati; l’ID rende stabile l’ordine a parità di punteggio.

Su PostgreSQL 18.6 questa query restituisce 101 e poi 105. Esclude il prodotto riservato e quello dell’altra organizzazione, ma include il tavolo: i termini compaiono nella descrizione anche se la lampada non è in vendita. Cinque righe bastano a rendere visibile questo errore; non misurano le prestazioni del sistema.

Correggere il catalogo prima di cambiare motore

Per il requisito dichiarato, la baseline fallisce sulla pertinenza. Una prima correzione è ottenere dall’utente una categoria esplicita e applicarla prima del ranking. Questo richiede che l’interfaccia consenta quella scelta e che i prodotti siano classificati in modo attendibile.

Nella stessa sessione di prova, dopo il blocco precedente, possiamo aggiungere la categoria. L’assegnazione tramite ID vale solo per i cinque dati sintetici: in un catalogo reale la categoria è un dato del prodotto, non una deduzione da questi identificatori.

ALTER TABLE products ADD COLUMN category text;
UPDATE products
SET category = CASE WHEN id = 105 THEN 'tavoli' ELSE 'lampade' END;
ALTER TABLE products ALTER COLUMN category SET NOT NULL;

PREPARE category_search(integer,text,text) AS
SELECT id, title, ts_rank_cd(search_doc,q) AS rank
FROM products, websearch_to_tsquery('italian',$2) AS q
WHERE tenant_id=$1 AND is_public
  AND category=$3 AND search_doc@@q
ORDER BY rank DESC,id
LIMIT 20;

EXECUTE category_search(1,'lampada tavolo','lampade');
EXECUTE category_search(1,'tavolo legno','tavoli');

La prima query restituisce soltanto 101: il tavolo 105 non entra più fra i candidati. La seconda restituisce 105, coerentemente con la ricerca di un tavolo. Sono gli esiti verificati su PostgreSQL 18.6. Aumentare il peso del titolo avrebbe potuto cambiare l’ordine; il filtro cambia invece quali prodotti possono comparire.

Il costo emerge alterando deliberatamente la categoria di 101 in tavoli: la ricerca di lampade restituisce zero righe. Nella prova, ripristinando la categoria torna 101. Il filtro corregge un falso positivo ma può introdurre un falso negativo; per usarlo servono responsabilità sulla qualità delle categorie e un modo per segnalare prodotti classificati male.

Sceglierei questa soluzione quando la categoria è un’informazione che il cliente sa dare e che il catalogo mantiene bene. La scarterei come soluzione unica per una ricerca che deve attraversare categorie senza chiedere un filtro. In quel caso il requisito è diverso: vanno valutati altri criteri di recupero e ordinamento. Il motore candidato dovrà dimostrare di soddisfarlo, non soltanto trovare le stesse parole più velocemente.

Un refuso non decide l’architettura

Nella stessa sessione, EXECUTE catalog_search(1, 'lampda tavolo'); non restituisce righe.

PostgreSQL offre anche pg_trgm, un’estensione che confronta sequenze di tre caratteri per cercare stringhe simili. Può contribuire al recupero dei refusi insieme alla ricerca full-text. Nella verifica separata, similarity('lampda','lampada') vale 0.5: dimostra una somiglianza fra quelle due stringhe, non una correzione automatica già integrata nel catalogo.

Per questo catalogo partirei da suggerimenti espliciti: dopo «lampda tavolo», l’interfaccia può proporre «Cerca invece lampada tavolo», mantenendo visibile la richiesta originale. La conferma avvia una ricerca distinta. È una scelta di prodotto che accetta un clic in più per evitare di cambiare silenziosamente il significato della richiesta.

I candidati possono arrivare da un vocabolario dei termini del catalogo, come nell’integrazione con la ricerca full-text descritta per pg_trgm. Costruirei i suggerimenti sullo stesso perimetro autorizzato della ricerca: anche un termine ricavato da un prodotto riservato può rivelare informazioni. Il vocabolario va aggiornato e le soglie vanno provate sui termini effettivi.

Terrei separata la ricerca per codice prodotto, con un campo dedicato e corrispondenza esatta. Un codice sconosciuto simile a uno esistente non autorizza a sostituirlo. Per le parole, proverei almeno la richiesta corretta, il refuso atteso e una parola rara ma valida vicina a un’altra più comune. Registrerei separatamente risultati originali, suggerimento e risultati dopo conferma.

Un esito vuoto può dipendere anche dai filtri o dall’assenza del prodotto. Per questo non presenterei ogni ricerca senza risultati come un errore ortografico. Il comportamento proposto va verificato nell’interfaccia; la similarità fra due stringhe misurata sopra non basta a valutarne l’utilità.

Anche l’alternativa interna va oltre le sole funzioni native. ParadeDB documenta un’estensione PostgreSQL con ranking BM25, una famiglia di metodi per ordinare documenti rispetto ai termini cercati. È una candidata da valutare se compatibile con hosting, aggiornamenti e gestione del database. Un vincolo che impedisce di installarla è una ragione per escluderla, senza imporre una prova inutile.

Un servizio come Elasticsearch offre query su più campi, pesi e opzioni di tolleranza ai refusi. Sono strumenti con cui costruire la ricerca desiderata; il vantaggio sul catalogo va verificato sugli stessi casi, senza confrontare direttamente punteggi prodotti da algoritmi diversi.

Il nuovo componente porta una seconda copia

Con un indice esterno, PostgreSQL può restare il sistema autorevole per i prodotti. Ogni inserimento, modifica, cancellazione e cambio di visibilità deve però raggiungere la copia usata dalla ricerca.

La transactional outbox è una possibilità: la stessa transazione aggiorna il prodotto e registra un evento in una tabella. Un processo separato legge gli eventi e aggiorna l’indice. Così un commit non può salvare il prodotto dimenticando il relativo evento, purché entrambe le scritture facciano parte di quella transazione.

Il processo può ripetere un invio. Chi applica l’evento deve tollerare i duplicati e impedire che una versione vecchia sovrascriva una nuova. Nella progettazione includerei una versione per prodotto e una regola esplicita per le cancellazioni: un evento ritardato non deve far ricomparire un prodotto rimosso.

Inoltre, ricevere il documento non significa averlo già reso ricercabile. Elasticsearch distingue l’indicizzazione dal refresh che rende visibili le modifiche alle ricerche. Il ritardo dal commit alla visibilità comprende propagazione, elaborazione e attesa del refresh; il solo intervallo di refresh non è una promessa sulla freschezza complessiva.

Conviene separare un titolo ancora vecchio da un accesso revocato. Se la revoca deve essere immediata, una copia aggiornata in ritardo non può essere l’unico controllo. Il percorso dell’API deve verificare l’autorizzazione corrente prima di esporre dati protetti, inclusi estratti e conteggi. Filtrare dopo aver preso i primi venti candidati può anche lasciare pagine incomplete: il requisito va progettato insieme al recupero dei risultati.

La prova che giustifica il passaggio

Fisserei il confronto prima di configurare il motore candidato, usando query reali anonimizzate e un insieme di risultati attesi concordato con chi conosce il catalogo.

Dopo aver reso privato 101, ripetendo EXECUTE catalog_search(1, 'lampada tavolo') resta soltanto 105. Il filtro di visibilità esclude il prodotto revocato; rimane il problema di pertinenza della query senza categoria. Le prove di carico, propagazione e recupero elencate nella tabella sono il lavoro successivo proposto; non sono risultati di un confronto con Elasticsearch o ParadeDB.

Provare il candidato senza cambiare subito le risposte

Se restasse un requisito insoddisfatto, preparerei una scheda di decisione prima della prova. Per esempio, ripartendo dal catalogo iniziale con 101 pubblico: il prodotto richiede ricerca libera senza scegliere una categoria; «lampada tavolo» deve recuperare 101 ed escludere 105. La query iniziale fallisce; il filtro di categoria funziona ma richiede un’interazione che, in questo scenario, il prodotto non accetta. La capacità del candidato di risolvere quel caso resta da misurare.

Questo non basta ancora a scegliere Elasticsearch. Nel confronto includerei una variante PostgreSQL che dia un ruolo diverso a titolo e descrizione, e query per cui la descrizione contiene informazioni indispensabili. Una soluzione che elimina 105 ignorando sempre la descrizione potrebbe perdere altri prodotti pertinenti. Gli esiti attesi devono precedere la configurazione di entrambe le alternative.

Inizierei con richieste di sola lettura inviate ai due percorsi, lasciando all’utente la risposta corrente. Per un campione autorizzato, confronterei gli ID restituiti con gli obbligatori e gli esclusi concordati. Le divergenze diventano casi da spiegare: prodotto mancante, falso positivo, ordine diverso o aggiornamento non ancora arrivato. Questa prova costa una seconda esecuzione; limiterei il traffico di confronto per non compromettere il servizio esistente.

Terrei separate le condizioni obbligatorie dalle preferenze. Un candidato che espone dati dopo una revoca non può compensare quel fallimento con risultati migliori sulle altre query. Solo le alternative che rispettano accessi e vincoli operativi passano al confronto su pertinenza, latenza e costo. I casi di revoca e replay richiedono prove controllate sull’API, oltre al confronto degli ID dell’indice.

Prima del passaggio, verificherei anche il ritorno al percorso precedente: deve sostenere il carico previsto e conservare gli stessi controlli. Può offrire una ricerca meno capace durante un guasto, purché questa riduzione sia accettata. Se il ritorno non è sostenibile, manca ancora una parte della decisione di esercizio.

Nel costo includerei le risorse del servizio e dell’indice, l’implementazione della sincronizzazione e il tempo ricorrente del team per monitoraggio, incidenti e aggiornamenti.

Resterei sulla soluzione più semplice fra quelle che soddisfano i requisiti e i vincoli del team. Affiancherei un motore quando una prova mostra il beneficio necessario e rende accettabile il costo della copia. La decisione da conservare è concreta: quale ricerca prima falliva, come ora viene soddisfatta e chi mantiene corretto quel risultato quando i dati cambiano.