Épuisez la recherche plein texte de Postgres avant d'ajouter un moteur

L'affirmation

En dessous d'environ un million de lignes indexables, la recherche plein texte intégrée à PostgreSQL n'est pas une solution d'attente. C'est la bonne réponse, et ajouter un moteur de recherche distinct à cette échelle vous achète un problème de synchronisation, une seconde surface d'exploitation et une facture mensuelle, en échange de gains de pertinence que vos utilisateurs ne remarqueront pas.

Ce que vous obtenez d'emblée

Postgres fournit la racinisation, les mots vides, le classement, la correspondance par préfixe, la recherche de locutions et des dictionnaires multilingues. Voici une implémentation fonctionnelle pour une table de produits, colonne générée comprise :

ALTER TABLE products ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('french', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('french', coalesce(sku, '')), 'B') ||
    setweight(to_tsvector('french', coalesce(description, '')), 'C')
  ) STORED;

CREATE INDEX products_search_idx ON products USING GIN (search);

La colonne générée signifie qu'il n'y a aucun déclencheur à entretenir et aucune possibilité que l'index dérive par rapport à la ligne — les deux modes de défaillance qui rendent pénible une recherche bricolée à la main. L'interrogation :

SELECT id, name, ts_rank(search, q) AS rank
FROM products, websearch_to_tsquery('french', $1) q
WHERE search @@ q
ORDER BY rank DESC, id
LIMIT 20;

websearch_to_tsquery est la fonction à employer pour tout ce qu'une personne tape. Elle accepte les locutions entre guillemets, le or et un moins initial pour l'exclusion, et elle ne lève pas d'erreur de syntaxe sur une saisie déséquilibrée, contrairement à to_tsquery. Passer une saisie brute à to_tsquery est de loin la cause la plus fréquente d'erreurs 500 sur les pages de recherche.

Le nombre qui compte

Sur une instance de 4 Go avec un index GIN couvrant 300 000 lignes de produits, une requête à deux termes de cette forme revient typiquement en quelques millisecondes une fois l'index en cache. Vérifiez la vôtre plutôt que de croire cette phrase :

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Vous cherchez un balayage d'index bitmap sur l'index GIN plutôt qu'un balayage séquentiel, et des compteurs shared read proches de zéro sur un cache chaud. Si vous voyez un balayage séquentiel, c'est que le planificateur juge votre table assez petite pour ne pas s'en soucier, ce qui convient également.

Gérer les fautes de frappe sans grappe dédiée

La raison habituelle de quitter Postgres est la correspondance approximative. Elle est disponible aussi :

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT name FROM products
WHERE name % $1
ORDER BY similarity(name, $1) DESC
LIMIT 5;

Le motif pragmatique est une recherche en deux temps : exécutez d'abord la requête tsvector et, si elle renvoie moins de trois lignes, repliez-vous sur la requête trigramme en étiquetant les résultats comme suggestions. Cela couvre « scie à chaine » et « aspirateurr » sans aucune infrastructure supplémentaire.

Là où Postgres atteint vraiment sa limite

Soyez honnête sur le plafond. Passez à un moteur dédié quand vous rencontrez l'un de ces cas, pas avant :

• Comptes à facettes sur des millions de lignes. Compter les documents correspondants par catégorie et par requête est précisément ce pour quoi les moteurs à index inversé sont optimisés, et précisément ce qui fait travailler Postgres davantage.
• Le réglage de pertinence comme fonctionnalité produit. Si le travail de quelqu'un consiste à ajuster des facteurs de pondération chaque semaine, il lui faut un outillage que Postgres n'offre pas.
• Le trafic de recherche qui concurrence le trafic transactionnel. Quand les requêtes de recherche commencent à affecter la latence du paiement sur la même instance, l'isolation vaut son coût d'exploitation. Une réplique en lecture est le premier geste, moins cher.
• Des collections multilingues dans un seul index avec détection de langue par document. Faisable dans Postgres, désagréable.

La taxe de synchronisation

Le coût qu'on oublie n'est pas la facture mensuelle de la grappe, souvent modeste. C'est que vous avez désormais deux sources de vérité. Chaque chemin d'écriture doit mettre les deux à jour, chaque déploiement doit tenir compte des schémas d'index, et chaque post-mortem inclut la question de savoir si l'index était périmé. Les équipes consacrent couramment plus d'heures d'ingénierie au pipeline de synchronisation le premier trimestre que ne valait le gain de qualité sur l'année suivante.

Une séquence défendable

1. Livrez la colonne tsvector générée et un index GIN. Un après-midi.
2. Journalisez chaque chaîne recherchée et son nombre de résultats. Une semaine de ces données vous dit ce qui échoue réellement.
3. Ajoutez le repli trigramme pour les requêtes à zéro résultat.
4. Ajoutez la pondération et une règle d'affaires favorisant les articles en stock.
5. Seulement si les journaux montrent encore un taux d'échec significatif, évaluez un moteur dédié — avec un chiffre précis à battre.

La plupart des équipes n'atteignent jamais la cinquième étape, et celles qui y arrivent le font avec des preuves plutôt qu'une intuition.