Créer un Moteur de Recherche avec PostgreSQL tsvector et Trigrammes

1. Recherche plein texte rapide avec tsvector

La recherche plein texte de PostgreSQL repose sur deux types : `tsvector`, une liste triée et normalisée de lexèmes extraits de votre texte, et `tsquery`, la même normalisation appliquée à une requête de recherche. La fonction `to_tsvector('french', content)` fait le vrai travail — elle met en minuscules, retire les mots vides (« le », « la », « est »), et racinise les mots grâce aux stemmers Snowball, de sorte que « courant », « court » et « couru » se normalisent tous vers le même lexème. C’est ce qui permet à une recherche sur « courir » de matcher un document contenant uniquement « courant » — chose qu’un `ILIKE '%courir%'` naïf ne peut jamais faire correctement, et fait de toute façon bien trop lentement pour être utile en production.

L’opérateur de correspondance `@@` compare un `tsvector` à un `tsquery` et renvoie un booléen, ce qui le rend directement utilisable dans une clause `WHERE` et — c’est le point crucial — exploitable par un index GIN pour des recherches en dessous de la milliseconde même sur des millions de lignes. Sans index, PostgreSQL recalcule `to_tsvector` sur chaque ligne de la table à chaque requête, ce qui convient pour du prototypage mais devient inacceptable en production au-delà de quelques milliers de lignes.

Le pattern à retenir en pratique est la colonne générée : plutôt que de calculer `to_tsvector` au moment de la requête, on la stocke dans une colonne `GENERATED ALWAYS AS ... STORED` que PostgreSQL maintient automatiquement synchronisée à chaque insertion et mise à jour, et on indexe directement cette colonne. Cela déplace le coût de normalisation vers l’écriture, où il est faible et peu fréquent, plutôt que vers la lecture, où il serait payé à chaque recherche.

2. Tolérance aux fautes de frappe avec pg_trgm

La recherche racinisée résout les variations grammaticales mais ne fait rien pour les fautes de frappe, et celles-ci sont courantes dans les saisies réelles des utilisateurs — « kubernets » pour « kubernetes », « postgre » pour « postgres ». C’est exactement le rôle de l’extension `pg_trgm` : elle découpe les chaînes en séquences chevauchantes de trois caractères (trigrammes) et mesure la similarité comme la fraction de trigrammes partagés entre deux chaînes. « kubernetes » et « kubernets » partagent presque tous leurs trigrammes et obtiennent un score de similarité élevé ; « kubernetes » et « docker » n’en partagent presque aucun.

Activez-la une fois par base avec `CREATE EXTENSION pg_trgm`, puis utilisez soit la fonction `similarity()` directement avec un seuil, soit — pour le cas courant des interfaces de recherche en temps réel avec préfixe et sous-chaîne — construisez un index GIN ou GiST sur l’expression trigramme afin que les requêtes `ILIKE '%terme%'` et les requêtes de similarité soient toutes deux accélérées par index plutôt que par un scan complet de table. C’est le changement le plus impactant pour les barres de recherche de type autocomplétion où les utilisateurs tapent des requêtes partielles, parfois mal orthographiées.

Un pattern de production courant combine les deux extensions : utiliser la similarité `pg_trgm` pour un matching tolérant aux fautes sur des champs courts comme des noms de produits ou des noms d’utilisateur, et `tsvector`/`tsquery` pour une recherche de pertinence classée sur du contenu long comme des corps d’articles ou des tickets de support. Elles résolvent des problèmes différents et sont fréquemment utilisées ensemble dans la même requête via un score combiné, pas comme des alternatives l’une à l’autre.

3. Pondération des résultats par pertinence

Le classement par pertinence est l’endroit où beaucoup d’implémentations de recherche naïves échouent : renvoyer des lignes correspondantes dans un ordre arbitraire vaut à peine mieux que ne pas classer du tout. `ts_rank()` note une paire `tsvector`/`tsquery` selon la fréquence des lexèmes et, combiné à `setweight()` au moment de la construction de l’index, permet de pondérer les correspondances dans le titre bien plus fortement que celles dans le corps — le pattern montré en section 1, où les lexèmes du titre reçoivent le poids « A » et ceux du corps le poids « B », contrôle directement ce classement sans aucune logique de scoring côté application.

Pour les requêtes qui combinent pertinence textuelle et fraîcheur ou popularité — le cas réel largement majoritaire, car « le plus pertinent » signifie rarement « le plus pertinent indépendamment de son âge » — l’approche idiomatique consiste à calculer `ts_rank` et à le mélanger à d’autres signaux directement dans la clause `ORDER BY`, plutôt que d’essayer d’encoder la fraîcheur dans l’index texte lui-même. Un score mélangé typique multiplie la pertinence textuelle par une fonction de décroissance logarithmique de l’âge, gardant du contenu récent et modérément pertinent compétitif face à du contenu ancien et très pertinent.

`websearch_to_tsquery()` mérite une mention spécifique : contrairement au plus strict `to_tsquery()`, elle analyse directement une syntaxe de requête à la Google depuis la saisie brute de l’utilisateur — phrases entre guillemets, `OR`, et un `-` en préfixe pour l’exclusion — sans que vous ayez à construire ce parsing vous-même ni à craindre qu’un `tsquery` mal formé lève une erreur de syntaxe sur un opérateur égaré tapé par l’utilisateur.

4. Limites de mise à l’échelle et pièges fréquents

La recherche plein texte de PostgreSQL gère confortablement des dizaines de millions de lignes sur un matériel raisonnablement dimensionné, mais il faut savoir où elle cesse d’être le bon outil. Elle n’a pas de sharding distribué natif, pas de moteur d’agrégation à facettes comparable aux agrégations d’Elasticsearch, et pas de support natif pour la recherche vectorielle approximative des plus proches voisins à l’échelle d’Elasticsearch/OpenSearch (même si `pgvector` couvre raisonnablement bien la recherche sémantique par embeddings jusqu’à quelques dizaines de millions de vecteurs avec un index HNSW). Si votre produit de recherche a besoin d’un sharding multi-tenant sur des dizaines de nœuds ou d’une navigation à facettes complexe avec des millions de valeurs de facette uniques, un moteur de recherche dédié commence à justifier son coût opérationnel.

L’erreur de production la plus fréquente est d’oublier de faire `VACUUM` et `ANALYZE` après des imports en masse — une colonne `GENERATED ALWAYS AS ... STORED` et son index GIN ont tous deux besoin de statistiques rafraîchies pour que le planificateur de requêtes utilise l’index efficacement, et un chargement massif de données sans `ANALYZE` ultérieur peut retomber silencieusement sur des scans séquentiels. La deuxième erreur la plus fréquente est d’indexer des colonnes `tsvector` brutes, non pondérées, sans `setweight()`, ce qui produit un classement techniquement correct mais pratiquement inutile, où une mention incidente unique dans un long document dépasse une correspondance dans le titre.

5. Synthèse

Pour la grande majorité des applications — sites de contenu, outils internes, produits SaaS avec des enregistrements interrogeables dans les dizaines de millions de lignes — la pile de recherche native de PostgreSQL (`tsvector` + index GIN pour la pertinence, `pg_trgm` pour la tolérance aux fautes, `pgvector` pour la similarité sémantique si besoin) fournit une recherche de qualité production sans ajouter un second système de base de données, un pipeline d’indexation séparé, ni la charge opérationnelle de garder un cluster de recherche externe synchronisé avec votre source de vérité.

Cela garde aussi la recherche dans votre logique transactionnelle et de sauvegarde habituelle. L’insertion d’une ligne et la mise à jour de son index de recherche se produisent dans la même transaction, donc vous n’avez jamais de fenêtre de cohérence éventuelle où un enregistrement nouvellement créé est interrogeable dans votre base principale mais pas encore recherchable, ce qui est un mode de défaillance réel et souvent négligé des architectures de recherche à deux bases de données. Votre stratégie de sauvegarde existante (`pg_dump` ou récupération à un instant donné) couvre automatiquement l’index de recherche, sans étape de réindexation séparée après une restauration.

La recette concrète : ajoutez une colonne `tsvector` générée avec des champs pondérés, indexez-la avec GIN, utilisez `websearch_to_tsquery` pour analyser la saisie brute de l’utilisateur en toute sécurité, classez avec `ts_rank` mélangé à un signal de fraîcheur ou de popularité, et superposez `pg_trgm` sur les champs courts spécifiques où la tolérance aux fautes compte le plus. Ne passez à Elasticsearch ou OpenSearch qu’une fois que vous avez une exigence concrète — mise à l’échelle horizontale multi-nœuds, recherche à facettes complexe, ou agrégation de logs à un volume que PostgreSQL ne peut réellement pas gérer — pas par défaut parce que « c’est ce que sont censés utiliser les moteurs de recherche ».