Recherche « partout » avec PostgreSQL : texte intégral, trigrammes et index partiels

Ce que « recherche partout » signifie réellement pour les outils internes

Sur un écran interne, « recherche partout » veut souvent dire : « Aidez‑moi à trouver l’enregistrement exact auquel je pense, rapidement, même si je ne m’en souviens pas parfaitement. » Les gens ne naviguent pas ; ils essaient d’aller directement vers un client, un ticket, une facture ou un appareil.

C’est pourquoi une recherche lente paraît pire qu’un chargement de page lent. Un chargement de page se produit une fois. La recherche se répète souvent, parfois pendant un appel ou un triage. Si les résultats prennent 2–3 secondes, les utilisateurs modifient la requête, suppriment des caractères, essaient un autre terme, et vous vous retrouvez avec plus de charge et plus de frustration.

Depuis une seule boîte de recherche, les utilisateurs attendent un ensemble de comportements : correspondances partielles ("alex" trouve "Alexander"), tolérance aux petites fautes ("microsfot" trouve toujours "Microsoft"), un ordre « meilleur résultat » sensé (les IDs ou e‑mails exacts remontent en tête), un léger biais de récence, et des filtres appliqués par défaut (tickets ouverts, clients actifs).

Le point délicat est qu’une seule saisie cache souvent plusieurs intentions. Un agent peut coller un numéro de ticket, taper un fragment de nom, chercher un e‑mail ou saisir un numéro de téléphone. Chaque intention nécessite une stratégie différente, des index différents et parfois une règle de classement différente.

Donc, ne commencez pas par les index. Commencez par lister les quelques intentions de recherche que vos utilisateurs ont réellement, et séparez les champs d’identité (IDs, e‑mails) des champs flous (noms, sujets) et des textes longs (notes).

Commencez par nommer les données et les comportements de recherche

Avant de choisir un index, écrivez ce que les gens tapent réellement. « PostgreSQL search everywhere » semble une seule fonctionnalité, mais c’est généralement un mélange de recherches très différentes.

Les outils internes mêlent des identifiants « durs » (ID de commande, numéro de ticket, code de facture) à du texte « souple » (nom du client, e‑mail, notes, tags). Ces groupes se comportent différemment sous PostgreSQL, donc les traiter pareil est un chemin rapide vers des requêtes lentes.

Ensuite, séparez les comportements :

• Recherche exacte : quelqu’un qui cherche TCK-104883 s’attend à un résultat précis.
• Recherche floue : quelqu’un qui tape john smth veut une correspondance tolérante sur les noms (et peut‑être les e‑mails) et examinera une courte liste.
• Recherche pilotée par filtres : quelqu’un qui sélectionne « Statut = Ouvert » et « Assigné à = Moi » filtre surtout ; la boîte de texte est secondaire.

Décidez tôt si les résultats doivent être classés (meilleures correspondances en tête) ou simplement filtrés. Le classement compte pour les notes et longues descriptions. Pour les IDs et e‑mails, le classement semble souvent aléatoire et ajoute du coût.

Une courte checklist suffit généralement :

• Quels champs sont recherchés quotidiennement ?
• Quelles saisies sont exactes (IDs, codes), floues (noms) ou textes longs (notes) ?
• Quels filtres s’appliquent presque à chaque recherche ?
• Avez‑vous besoin d’un ordre « meilleure correspondance », ou n’importe quelle correspondance suffit‑elle ?
• Quelle taille la table va‑t‑elle atteindre : milliers, centaines de milliers ou millions ?

Si vous nommez ces décisions dès le départ, le choix des index cessera d’être de la devinette.

La base : correspondances exactes et pourquoi ILIKE pose souvent problème

Verrouillez d’abord les gains faciles. Pour beaucoup d’écrans internes, un simple index B-tree donne déjà des résultats instantanés pour les correspondances exactes comme les IDs, numéros de commande, e‑mails et références externes.

Si les gens collent une valeur exacte, assurez‑vous que votre requête est vraiment exacte. WHERE id = ... ou WHERE email = ... peut être extrêmement rapide avec un index normal. Un index unique sur l’e‑mail rapporte souvent deux fois : vitesse et meilleure qualité de données.

Les ennuis commencent quand « recherche partout » se transforme en ILIKE. Une requête comme name ILIKE '%ann%' a un joker en début de motif, donc PostgreSQL ne peut pas utiliser un index B-tree classique. Il finit par vérifier beaucoup de lignes, et ça devient prévisible­ment plus lent quand la table grandit.

La recherche préfixe peut fonctionner, mais seulement quand le motif est ancré au début : name ILIKE 'ann%'. Même là, les détails comptent (collation, gestion des majuscules, et si vous avez indexé la même expression que celle que vous interrogez). Si votre UI doit être insensible à la casse, une approche courante est d’interroger lower(name) et de créer un index correspondant sur lower(name).

Il aide aussi de s’accorder sur ce que « réactif » signifie :

• Environ 200 ms ou moins pour le travail DB sur cache chaud
• Moins d’une seconde de bout en bout, réseau et rendu inclus
• Aucun état de chargement visible pour les recherches courantes

Avec des cibles comme celles‑ci, il est plus simple de décider si vous pouvez rester sur des correspondances exactes et préfixes, ou si vous devez passer au full‑text ou aux index trigrammes.

Quand la recherche en texte intégral est l’outil approprié

La recherche en texte intégral est le meilleur choix quand les gens tapent en langage naturel et s’attendent à ce que le système trouve les éléments pertinents, pas seulement des correspondances exactes. Pensez aux messages de tickets, notes internes, longues descriptions, articles de base de connaissances et journaux d’appels.

Le grand avantage est le classement. Au lieu de renvoyer une longue liste où le meilleur résultat est noyé, la recherche en texte intégral peut trier par pertinence. Dans les outils internes, ça compte : quelqu’un a besoin d’une réponse en quelques secondes, pas après avoir parcouru 50 lignes.

À haut niveau, la recherche en texte intégral comporte trois éléments :

• Un tsvector (le texte indexable, stocké ou généré)
• Un tsquery (ce que l’utilisateur a tapé, converti en requête)
• Une configuration de langue (comment normaliser les mots)

La configuration de langue est là où le comportement devient visible. PostgreSQL supprime les mots vides (comme « le » ou « et ») et applique le stemming, donc « payer », « payé » et « paiement » peuvent correspondre. C’est excellent pour les notes et messages, mais ça peut surprendre quand quelqu’un cherche un mot court et courant et n’obtient rien.

Les synonymes sont un autre point de décision. Ils aident quand votre entreprise utilise plusieurs mots pour la même chose (par exemple « remboursement » vs « chargeback »), mais ils demandent un peu d’entretien. Gardez la liste courte et basée sur ce que le support ou les ops tapent réellement.

Un exemple concret : chercher « can’t login after reset » devrait ramener des tickets où le message dit « cannot log in after password reset », même si la formulation diffère. Ce comportement « trouver ce qui est pertinent » est ce pour quoi la recherche en texte intégral est conçue, et c’est souvent un meilleur choix que d’essayer de forcer ILIKE à agir comme un moteur de recherche.

Quand les index trigrammes gagnent

Les index trigrammes sont un excellent choix quand les utilisateurs tapent des fragments, font des fautes ou se souviennent seulement d’« quelque chose comme ça ». Ils excellent sur des champs courts où le full‑text est trop strict : noms de personnes, noms d’entreprise, sujets de ticket, SKU, numéros de commande et codes produits.

Un trigramme est un segment de 3 caractères. PostgreSQL compare deux chaînes par le nombre de trigrammes partagés. C’est pourquoi il peut rapprocher "Jon Smth" de "John Smith", ou "ACM" de "ACME", et trouver des résultats quand la requête est au milieu d’un mot.

C’est souvent le chemin le plus rapide vers une boîte « recherche partout » tolérante quand le travail est « trouver la bonne ligne », pas « trouver des documents sur un sujet ».

Là où il bat la recherche en texte intégral

La recherche en texte intégral est excellente pour les textes longs et le classement sémantique, mais elle ne gère pas naturellement les sous‑chaînes partielles ou les petites fautes sur des champs courts. Le trigramme est conçu pour ce type de flou.

Garder le coût en écriture raisonnable

Les index trigrammes sont plus volumineux et ajoutent une surcharge aux écritures, donc soyez sélectif. indexez les colonnes que les gens utilisent réellement :

• Nom, e‑mail, entreprise, nom d’utilisateur
• Identifiants courts (SKU, code, référence)
• Un champ de titre concis (pas un grand champ de notes/commentaires)

Si vous pouvez nommer les champs exacts que votre équipe saisit dans la boîte de recherche, vous pouvez généralement garder l’index trigramme petit et rapide.

Index partiels pour les filtres que les gens utilisent vraiment

Une boîte « recherche partout » a souvent des valeurs par défaut cachées. Les gens cherchent dans un espace de travail, sur des éléments actifs, avec les supprimés exclus. Si ces filtres sont présents dans presque toutes les requêtes, rendez le cas courant rapide en indexant seulement les lignes qui les respectent.

Un index partiel est un index classique avec une clause WHERE. PostgreSQL le garde plus petit car il ne stocke que les entrées des lignes qui vous intéressent le plus. Cela signifie souvent moins de pages à lire et de meilleurs taux de cache.

Cibles courantes d’index partiels : lignes actives (status = 'active'), suppressions logiques (deleted_at IS NULL), séparation par tenant, et fenêtres « récentes » (par exemple, 90 derniers jours).

L’important est d’aligner l’index sur votre UI. Si l’écran cache toujours les lignes supprimées, vos requêtes doivent toujours inclure deleted_at IS NULL, et votre index partiel doit utiliser la même condition. De petites incohérences, comme utiliser is_deleted = false à un endroit et deleted_at IS NULL à un autre, peuvent empêcher le planner d’utiliser l’index.

Les index partiels fonctionnent aussi avec la recherche en texte intégral et les trigrammes. Par exemple, indexer la recherche textuelle uniquement pour les lignes non supprimées permet de garder la taille de l’index sous contrôle.

Compromis : les index partiels sont moins utiles pour les requêtes rares. Si quelqu’un recherche occasionnellement dans les enregistrements supprimés ou dans tous les espaces de travail, PostgreSQL pourra revenir à un plan plus lent. Gérez cela avec un chemin réservé aux admins, ou ajoutez un second index seulement si la requête rare devient courante.

Mélanger les approches sans transformer la recherche en mystère

La plupart des équipes finissent par mélanger des techniques parce qu’une boîte de recherche unique doit gérer différentes intentions. L’objectif est de rendre l’ordre des opérations clair pour que les résultats paraissent prévisibles.

Un ordre de priorité simple aide, que vous l’implémentiez par des requêtes séparées ou une seule requête avec une logique CASE claire.

Une échelle de priorité prédictible

Commencez strict, puis devenez plus permissif si nécessaire :

• D’abord la correspondance exacte (IDs, e‑mail, numéro de ticket, SKU) en utilisant des B-tree
• Ensuite la correspondance préfixe quand elle a du sens
• Puis la correspondance trigramme pour les fautes et fragments sur les noms et titres
• Enfin la recherche en texte intégral pour les notes longues, descriptions et contenus libres

En respectant cette échelle, les utilisateurs apprennent ce que signifie la boîte. Ils arrêtent de penser que le système est cassé quand « 12345 » trouve un ticket instantanément alors que « politique de remboursement » recherche le texte plus long.

Filtrer d’abord, puis flouter

La recherche floue coûte cher quand elle doit considérer toute la table. Réduisez l’ensemble de candidats avec les filtres que les gens utilisent réellement (statut, équipe assignée, plage de dates, compte), puis lancez le trigramme ou le full‑text sur ce qui reste. Même un index trigramme rapide peut sembler lent si vous lui demandez de scorer des millions de lignes.

Il vaut aussi la peine d’écrire une règle d’une phrase que des collègues non techniques comprendront, par exemple : « On matche d’abord le numéro de ticket, puis le nom du client tolérant aux fautes, puis on recherche dans les notes. » Cette définition partagée évite les disputes plus tard sur pourquoi une ligne est apparue.

Étape par étape : choisir une approche et l’implémenter en sécurité

Une boîte « recherche partout » rapide est un ensemble de petites décisions. Écrivez‑les d’abord, et le travail sur la base devient plus simple.

1. Définissez les entrées. Est‑ce une seule boîte ou une boîte plus des filtres (statut, propriétaire, intervalle de dates) ?
2. Choisissez le type de correspondance par champ. Les IDs et codes veulent des correspondances exactes. Les noms et e‑mails demandent souvent du préfixe ou du flou. Les longues notes et descriptions sont mieux traitées par la recherche en langage naturel.
3. Ajoutez les bons index et confirmez qu’ils sont utilisés. Créez l’index, puis vérifiez votre requête réelle avec EXPLAIN (ANALYZE, BUFFERS).
4. Ajoutez un classement ou un tri qui correspond à l’intention. Si les utilisateurs tapent « invoice 1042 », les correspondances exactes doivent remonter. Si le nom est mal orthographié, le classement par similarité doit primer.
5. Testez avec des requêtes réelles. Essayez des fautes, des termes très courts (comme « al »), de longs textes collés, une saisie vide et le mode « uniquement filtres ».

Pour déployer en sécurité, changez une chose à la fois et gardez un rollback facile. Pour de nouveaux index sur de grandes tables, préférez CREATE INDEX CONCURRENTLY pour ne pas bloquer les écritures. Si possible, déployez derrière un feature flag et comparez la latence avant/après.

Un schéma pratique pour « PostgreSQL search everywhere » : d’abord la correspondance exacte (rapide et précise), trigramme pour les champs « humains » où les gens font des fautes, et full‑text pour les textes longs qui bénéficient du classement.

Un exemple réaliste : une seule boîte de recherche dans un panneau admin support

Imaginez un panneau admin support où l’équipe a une seule boîte de recherche, mais s’attend à trouver clients, tickets et même des notes. C’est le problème classique de « une saisie, plusieurs sens ».

Le premier gain est de rendre l’intention visible sans ajouter de friction. Si la requête ressemble à un e‑mail ou un numéro de téléphone, traitez‑la comme une recherche client. Si elle ressemble à un ID de ticket (par exemple, "TKT-10482"), routez‑la directement vers les tickets. Tout le reste retombe sur une recherche textuelle sur le sujet du ticket et les notes.

Pour la recherche client, les index trigrammes sont souvent les plus pertinents. Les noms et entreprises sont désordonnés et les gens tapent des fragments. Un index trigramme rend des recherches comme « jon smi » ou « acm » rapides et tolérantes.

Pour les notes de ticket, utilisez la recherche en texte intégral. Les notes sont des phrases et vous voulez généralement des correspondances pertinentes, pas « contient cette sous‑chaîne ». Le classement aide quand des dizaines de tickets mentionnent le même mot‑clé.

Les filtres comptent plus que la plupart des équipes ne l’imaginent. Si les agents vivent dans « tickets ouverts », ajoutez un index partiel qui couvre seulement les lignes ouvertes. Faites pareil pour « clients actifs ». Ça garde les index petits et rend le chemin commun rapide.

Les requêtes très courtes méritent des règles, sinon la base effectue un travail coûteux pour du bruit :

• 1–2 caractères : afficher les tickets ouverts récents et les clients récemment mis à jour
• 3+ caractères : lancer le trigramme pour les champs client et le full‑text pour le texte des tickets
• Pas d’intention claire : afficher une liste mixte, en limitant chaque groupe (par exemple 10 clients et 10 tickets)

Erreurs courantes qui ralentissent ou rendent la recherche confuse

La plupart des problèmes « pourquoi la recherche est lente ? » sont auto‑infligés. L’objectif n’est pas d’indexer tout, mais d’indexer ce que les gens font réellement.

Un piège courant est d’ajouter des index sur de nombreuses colonnes « au cas où ». Les lectures s’améliorent peut‑être, mais chaque insertion et mise à jour a maintenant du travail supplémentaire. Dans les outils internes où les enregistrements changent toute la journée (tickets, commandes, utilisateurs), la vitesse d’écriture compte.

Autre erreur : utiliser la recherche en texte intégral quand ce dont vous avez vraiment besoin est une recherche tolérante aux fautes sur les noms ou e‑mails. Le full‑text est excellent pour les documents et descriptions. Ce n’est pas une solution magique pour « Jon » vs « John » ou « gmail.con » vs « gmail.com ». Là, c’est généralement le domaine du trigramme.

Les filtres peuvent aussi casser silencieusement votre plan. Si la plupart des recherches se font avec un filtre fixe (comme status = 'open' ou org_id = 42), le meilleur index peut être un index partiel qui correspond à cette condition. Oubliez‑le et PostgreSQL peut scanner bien plus de lignes que prévu.

Quelques fautes récurrentes :

• Ajouter beaucoup d’index sans mesurer le coût en écriture
• S’attendre à ce que le full‑text se comporte comme un autocomplete tolérant aux fautes
• Ignorer comment les filtres courants changent quel index peut être utilisé
• Tester sur de petites données propres au lieu de fréquences de termes réalistes (mots communs vs IDs rares)
• Trier par une colonne sans index de support, forçant un tri lent

Exemple : un écran support recherche les tickets par sujet, nom du client et numéro de ticket, puis trie par activité la plus récente. Si latest_activity_at n’est pas indexé pour l’ensemble filtré (par exemple, tickets ouverts), ce tri peut annuler la vitesse gagnée par l’index de recherche.

Vérifications rapides avant le déploiement

Avant de considérer la fonctionnalité « recherche partout » comme terminée, soyez concret sur le comportement promis.

• Les gens cherchent‑ils un enregistrement par identifiant exact (numéro de ticket, e‑mail) ?
• Attendent‑ils une correspondance floue pour les fautes ?
• Veulent‑ils des résultats classés depuis des notes et descriptions longues ?

Si vous mélangez les modes, décidez lequel prime en cas de conflit.

Identifiez ensuite les 2–3 champs qui pilotent la plupart des recherches. Si 80 % des recherches se font par e‑mail, nom et ID de ticket, optimisez‑les en premier et traitez le reste comme secondaire.

Une courte checklist avant livraison :

• Confirmer le mode principal de recherche par champ (lookup exact, correspondance floue, ou texte classé)
• Lister les filtres que les utilisateurs appliquent quotidiennement et vérifier que les index prennent en charge ces combinaisons
• Décider comment gérer les recherches très courtes et les saisies vides (par exemple exiger 2–3 caractères pour la recherche floue ; afficher « récents » pour vide)
• Rendre l’ordre explicable : plus récent, meilleure correspondance textuelle, ou une règle combinée simple

Enfin, testez avec une taille et des délais réalistes, pas seulement la correction. Une requête instantanée sur 1 000 lignes peut traîner sur 1 000 000.

Prochaines étapes : transformer le plan en un écran de recherche interne rapide

Une boîte de recherche reste rapide quand l’équipe s’accorde sur ce qu’elle doit faire. Écrivez les règles en langage clair : ce que signifie « correspondre » (exact, préfixe, tolérance aux fautes), quels champs sont recherchés et comment les filtres transforment l’ensemble de résultats.

Gardez un petit jeu de tests de recherches réelles et traitez‑le comme une suite de régression. Dix à vingt requêtes suffisent généralement : quelques noms courants, quelques e‑mails partiels, une faute, un extrait de note long et un cas de « aucun résultat ». Exécutez‑les avant et après chaque changement pour que le travail de performance ne casse pas silencieusement la pertinence.

Si vous construisez des outils internes avec AppMaster (appmaster.io), il aide de définir ces règles de recherche en parallèle du modèle de données et de la logique métier, afin que le comportement UI et les choix de base de données ne divergent pas quand les exigences évoluent.