Les extensions : pgvector, pg_trgm, full-text

L'idée : une base, plusieurs moteurs de recherche

Le cours Stack IA a montré la recherche hybride du matcher — quatre sources fusionnées, toutes dans PostgreSQL. Cette leçon détaille les trois extensions qui rendent ça possible, et pourquoi les regrouper dans une seule base est un choix architectural fort (une transaction, une sauvegarde, une RLS — pas de moteur de recherche séparé à synchroniser).

Full-text search (natif) : les mots, pas les octets

-- une colonne tsvector (souvent GÉNÉRÉE) + un index GIN
ALTER TABLE variant ADD COLUMN search_text tsvector
  GENERATED ALWAYS AS (to_tsvector('french', name || ' ' || description)) STORED;
CREATE INDEX ON variant USING gin (search_text);

-- rechercher
SELECT *, ts_rank_cd(search_text, query) AS score
FROM variant, websearch_to_tsquery('french', 'boulon inox') query
WHERE search_text @@ query ORDER BY score DESC;

to_tsvector découpe et lemmatise (« boulons » → « boulon »), websearch_to_tsquery parse une requête façon moteur de recherche, ts_rank_cd classe par densité de couverture. La colonne générée (calculée par la base à chaque écriture) garantit qu'elle est toujours à jour — un pattern JSONB/FTS élégant.

pg_trgm : la recherche floue (fautes de frappe)

CREATE EXTENSION pg_trgm;
CREATE INDEX ON variant USING gin (name gin_trgm_ops);
SELECT *, similarity(name, 'bulon inox') AS sim
FROM variant WHERE name % 'bulon inox' ORDER BY sim DESC;   -- % = assez similaire

Les trigrammes (« bou », « oul », « ulo »…) mesurent la similarité de chaîne — c'est ce qui attrape « bulon »≈« boulon » que le full-text rate (il exige des lemmes corrects). L'index GIN trigram rend ça rapide.

pgvector : la recherche sémantique

CREATE EXTENSION vector;
ALTER TABLE variant ADD COLUMN search_vec vector(1024);          -- embedding Cohere
CREATE INDEX ON variant USING hnsw (search_vec vector_cosine_ops);
SELECT * FROM variant ORDER BY search_vec <=> :embedding LIMIT 10;  -- <=> = distance cosinus

<=> (cosinus), <-> (L2), <#> (produit interne) : les opérateurs de distance. L'index HNSW (graphe de voisinage) rend le plus-proche-voisin quasi instantané sur des millions de vecteurs — le « WHERE indexé de l'espace sémantique » du cours ML.

Le fil conducteur

Chaque extension attrape ce que les autres ratent : full-text = les mots exacts (pluriels inclus), trigram = les fautes, vecteur = le sens. Les fusionner par RRF (cours Stack IA) donne une recherche robuste — et le fait que tout vive dans la même base signifie : une seule requête, la RLS qui s'applique partout, zéro pipeline de synchro vers Elasticsearch/Pinecone. Postgres monte en gamme jusqu'à ce que le volume justifie (peut-être) un moteur dédié — et ce moment arrive bien plus tard qu'on ne le croit.