Recherche en texte intégral MySQL vs PostgreSQL : quand l’intégration suffit et quand c’est un piège

La ligne de décision : quand la recherche intégrée suffit

Voici la version directe : la recherche en texte intégral intégrée suffit lorsque votre recherche est une fonctionnalité interne, pas votre produit. Elle suffit quand vous pouvez expliquer vos exigences en un paragraphe et que ce paragraphe ne contient pas les mots « vouliez-vous dire », « synonymes », « flou », « classement personnalisé », « autocomplétion » ou « analyses quasi temps réel ».

L’intégrée suffit quand…

• Le volume de données est modéré et le périmètre de recherche est contraint. Pensez : recherche dans les tickets d’un outil d’assistance, articles de base de connaissances, noms de produits, documents internes.
• Les exigences de latence sont « rapides pour l’app », pas « rapides comme un moteur de recherche sous chaos ». Si le P95 peut être de 200–500 ms et que personne ne filtre en temps réel à chaque frappe, vous pouvez souvent rester dans la base.
• Le classement peut être « assez bon », et vous êtes prêt à l’ajuster en modifiant les poids et les stopwords — pas en entraînant des modèles.
• La consistance compte plus que la sophistication de la pertinence. La recherche en base peut être transactionnellement cohérente si vous la concevez ainsi ; les moteurs externes ajoutent du décalage d’index et un autre domaine de panne.
• Vous pouvez vivre avec des limites telles que des bizarreries de tokenisation, des choix d’algorithme de racinisation et des tickets occasionnels « pourquoi ça ne matche pas cette phrase exacte ? ».

La recherche intégrée devient un piège quand…

• Vous construisez un produit de recherche. Si la pertinence est votre différenciateur, ne faites pas semblant qu’un moteur de base général remplacera indéfiniment une pile de recherche dédiée.
• Vos requêtes mélangent FTS et filtres à haute cardinalité et tri et vous attendez une montée en charge linéaire. Ce ne sera pas le cas. Ce sera comme une commission qui débat.
• La recherche multi-tenant signifie « recherche sur tous les tenants » et votre conception d’index n’est pas explicitement prévue pour cela. Vous paierez en CPU, mémoire et contentions de verrous désagréables.
• « Ajoutez juste un index » devient votre modèle opérationnel. Les index de texte intégral ne sont pas des index B-tree réguliers. Vous pouvez les faire gonfler, les faire chuter et transformer la maintenance en travail à temps partiel.
• Vous avez besoin de fonctionnalités avancées : correspondance floue, synonymes par tenant, scoring complexe, analyseurs par champ, surlignage, « plus comme ceci », suggestions de requête, tolérance aux fautes de frappe, ou recherche hybride vecteur + lexicale.

Si vous hésitez : commencez par la recherche intégrée pour valider le comportement et les besoins utilisateurs, mais concevez le flux de données pour pouvoir plus tard dupliquer les documents vers un moteur dédié sans réécrire toute l’application. Autrement dit : ne faites pas de la base votre cluster de recherche puis ne soyez pas surpris quand elle commencera à se comporter comme tel.

Comment la recherche MySQL fonctionne réellement (et échoue)

La recherche en texte intégral MySQL est trompeusement simple : ajoutez un FULLTEXT index, utilisez MATCH() AGAINST(), déployez. Sous le capot, le moteur de stockage compte (InnoDB aujourd’hui, MyISAM historiquement), les règles de tokenisation comptent, et votre « pertinence » sera façonnée par des valeurs par défaut auxquelles vous n’avez pas réalisé avoir consenti.

InnoDB FULLTEXT : ce que vous achetez vraiment

InnoDB implémente FULLTEXT en maintenant des tables auxiliaires pour l’index inversé. C’est intégré, suffisamment transactionnel pour la plupart des applications, mais cela a son profil de ressources : maintenance d’index en arrière-plan, IO potentiellement lourde lors de grosses mises à jour, et sensibilité aux stopwords et à la taille minimale des tokens.

Mode langage naturel vs mode booléen : deux bêtes différentes

Le mode langage naturel est « cherchez juste ce texte » et renvoie un score de pertinence. Le mode booléen ajoute des opérateurs et vous permet d’imposer inclusion/exclusion et correspondance par préfixe. En production, le mode booléen devient populaire parce que les utilisateurs veulent « doit inclure X », mais il facilite aussi l’écriture accidentelle de requêtes qui renvoient trop, trop peu, ou qui scannent plus que prévu.

Modes d’échec que vous verrez avec MySQL

• « Pourquoi la recherche ‘to be’ ne renvoie rien ? » Stopwords et longueur minimale des mots. Votre système « fonctionne comme prévu », ce qui est rarement réconfortant.
• La pertinence semble aléatoire parce que le modèle de scoring est grossier et fortement influencé par la fréquence des termes et la longueur des documents d’une manière qui peut ne pas correspondre à votre domaine.
• Les coûts de construction/mise à jour d’index vous surprennent. Les mises à jour en masse ou les modifications fréquentes de gros champs texte peuvent créer une amplification des écritures et du retard de réplication.
• Problèmes de collation et d’encodage affectent la tokenisation et les comparaisons. Vous pouvez obtenir des problèmes « chaîne identique en apparence, tokens différents » lorsque la normalisation Unicode n’est pas cohérente.
• Les plans de requête se dégradent lorsque vous combinez des prédicats texte intégral avec d’autres filtres et tris. MySQL peut choisir des plans sous-optimaux ou forcer des tables temporaires.

Une réalité opérationnelle : quand FULLTEXT MySQL part en vrille, les symptômes ressemblent à « la base de données est lente ». La recherche devient le voisin bruyant qui vole le budget CPU du reste de votre appli. Et puisque c’est dans la base, il est plus difficile d’isoler le problème sans limiter le taux d’appels à la fonctionnalité ou la déplacer.

Comment la recherche PostgreSQL fonctionne réellement (et échoue)

La recherche en texte intégral PostgreSQL (FTS) est une boîte à outils. Vous construisez des documents tsvector, les interrogez avec tsquery, les indexez avec GIN (généralement) ou GiST (parfois), et classez les résultats avec ts_rank ou apparentés. C’est plus explicite que MySQL. C’est bien parce que vous pouvez le régler, et mauvais parce que vous pouvez le mal régler.

tsvector, tsquery, et pourquoi la normalisation n’est pas optionnelle

Postgres ne « recherche pas le texte brut » de la même façon. Il analyse le texte en lexèmes (tokens normalisés), en appliquant typiquement une racinisation selon une configuration de recherche textuelle (comme english). Cela signifie que vous devez décider quelle configuration de langue utiliser et si la racinisation est souhaitable. C’est fantastique pour faire correspondre « running » et « run ». C’est terrible lorsque vos codes produits ou citations légales sont déformés.

Choix d’index : GIN est rapide en lecture, pas gratuit en maintenance

Les index GIN sont le cheval de bataille pour le FTS car ils correspondent bien à la forme d’un index inversé. Les lectures sont rapides. Les écritures peuvent être coûteuses. Si vous mettez souvent à jour des documents, les listes en attente GIN et le comportement du vacuum deviennent votre nouveau hobby. (Personne ne choisit ce hobby volontairement.)

Modes d’échec que vous verrez avec PostgreSQL

• Ballonnement et ralentissements de l’index GIN si vous avez un fort churn et un vacuum insuffisant, ou si vous indexez de gros documents sans précaution.
• Conflits de classement parce que les fonctions de ranking par défaut ne correspondent pas à l’idée du product manager. Vous aurez besoin de poids, de normalisation et parfois de champs séparés.
• Mauvaise configuration de langue conduit à « pourquoi ‘analysis’ ne correspond pas à ‘analyses’ ? » ou « pourquoi la recherche d’un numéro de pièce correspond à n’importe quoi ? »
• Mauvaise construction de requête (utiliser to_tsquery directement sur l’entrée utilisateur) transforme la ponctuation en erreurs de syntaxe et fait des requêtes utilisateur des incidents opérationnels.
• Combiner FTS avec filtrage et tri peut déclencher de larges scans bitmap et une pression mémoire si vous ne structurez pas la requête et les index avec attention.

Postgres vous récompense si vous êtes explicite : vecteurs séparés par champ, classement pondéré, colonnes générées, index partiels par tenant et construction propre des requêtes via plainto_tsquery ou websearch_to_tsquery. Il vous punit aussi si vous improvisez en production.

Faits et histoire intéressants que vous pouvez utiliser

1. MyISAM avait le full-text en premier, bien avant qu’InnoDB le prenne en charge ; beaucoup d’« opinions MySQL FTS » sont fossilisées de l’ère MyISAM.
2. InnoDB FULLTEXT stocke son index dans des tables auxiliaires, d’où l’apparence de tempêtes IO internes mystérieuses lors de grosses reconstructions ou de mises à jour lourdes.
3. Le tsearch de PostgreSQL précède la tendance « recherche partout » d’aujourd’hui ; il fait partie de Postgres depuis de nombreuses versions majeures, évoluant de modules contrib vers le cœur.
4. Les index GIN ont été conçus pour des valeurs composites (tableaux, structures de type JSON, ensembles de lexèmes). La recherche texte intégral est un des meilleurs cas d’usage pour leur conception.
5. Postgres FTS dispose de multiples dictionnaires et configurations (racinisation, stopwords, parsing simple). Cette flexibilité explique pourquoi il s’adapte bien aux langues — si vous le configurez réellement.
6. MySQL a des règles de taille minimale de token qui provoquaient historiquement des non-correspondances sur les mots courts ; on ne découvre cela qu’après qu’un CEO recherche une gamme produit en deux lettres.
7. Les deux systèmes font des compromis autour des stopwords : les supprimer réduit la taille d’index et le bruit, mais peut détruire la pertinence pour des domaines où les mots communs comptent (texte légal, titres, paroles de chansons).
8. La pertinence FTS n’est pas une vérité universelle. MySQL et Postgres notent différemment parce que leurs modèles diffèrent ; migrer d’un moteur à l’autre change les résultats même si les données sont identiques.
9. La réplication amplifie la douleur : une charge lourde en FTS peut augmenter le volume de binlog/WAL et amplifier le retard lors de reindexations massives ou de mises à jour en masse.

Une idée paraphrasée à garder sur un post-it, attribuée à un ingénieur célèbre de la fiabilité : paraphrased idea — Werner Vogels (à propos de construire des systèmes en tenant compte des pannes et de concevoir pour ce qui casse). Traitez la recherche comme un domaine de panne, même quand elle vit dans la base de données.

Blague #1 : La recherche en texte intégral, c’est comme le café de bureau : quand il est bon personne n’en parle, et quand il est mauvais tout le monde ouvre un ticket en même temps.

Trois micro-récits d’entreprise depuis le terrain

Micro-récit 1 : L’incident causé par une mauvaise hypothèse

Une entreprise SaaS de taille moyenne a ajouté « rechercher toutes les notes client » dans son panneau d’administration. C’était rapide à livrer : une table MySQL, un index FULLTEXT, un nouvel endpoint qui exécutait MATCH(notes) AGAINST(?). Ça fonctionnait en staging. Même en production — jusqu’à ce que ça cesse.

Le support a signalé « la recherche est en panne » pendant les heures ouvrables, mais le reste de l’appli était aussi lent. L’astreinte a vu le CPU saturé et les requêtes s’empiler. L’équipe a supposé que l’index full-text rendait les recherches « essentiellement O(1) ». L’hypothèse erronée était plus subtile : ils croyaient qu’ajouter un prédicat full-text réduirait toujours les résultats de façon précoce et peu coûteuse.

En réalité, la requête incluait aussi des filtres par tenant, une plage de dates et un tri par « dernièrement mis à jour ». L’optimiseur a choisi un plan qui faisait beaucoup plus de travail que prévu, et le « tri par récence » a forcé des tables temporaires pour de grands ensembles candidats. Pour certains termes de recherche courants, l’ensemble candidat était massif.

L’incident s’est terminé par une limitation du débit de l’endpoint, un changement d’UI pour exiger au moins 3 caractères non-stopword, et une refonte : ils ont séparé « recherche » et « tri », préfiltré d’abord par tenant et activité récente, puis appliqué la correspondance full-text. Plus tard, ils ont ajouté un service de recherche séparé. La leçon n’était pas « le full-text MySQL est mauvais ». C’était : l’optimiseur n’est pas votre chef de produit, et « indexé » ne signifie pas « bon marché ».

Micro-récit 2 : L’optimisation qui s’est retournée contre eux

Une équipe marketplace sur PostgreSQL a tenté d’accélérer la recherche d’annonces en construisant un gros tsvector qui concaténait titre, description et notes du vendeur. Ils ont tout pondéré également. Ils l’ont aussi stocké comme colonne générée et indexé avec GIN. Les requêtes sont devenues plus rapides, et l’équipe a déclaré victoire.

Puis les écritures ont ralenti. Pas « un peu ». Ils ont commencé à voir des pics périodiques de génération WAL et du retard d’autovacuum. L’application avait une fonctionnalité permettant aux vendeurs de modifier fréquemment les descriptions, souvent en rafales (pensez : mises à jour saisonnières). Chaque édition charriait l’index GIN. La liste en attente GIN a grandi, le vacuum n’a pas suivi, et la latence des requêtes est devenue saccadée. L’« optimisation » avait déplacé le coût des lectures vers les écritures, et leur système était orienté écritures.

Ils ont tenté d’ajuster autovacuum, ce qui a aidé, puis s’est retourné contre eux quand il a concurrencé la charge normale et causé une contention IO. Finalement, ils ont éclaté le vecteur en champs plus petits, réduit le contenu indexé (les notes du vendeur n’étaient plus recherchables), et déplacé la « recherche de notes » vers un index asynchrone mis à jour hors bande. La pertinence s’est aussi améliorée parce que les poids sont devenus significatifs au lieu de « tout a la même importance ».

La leçon : le plus grand vecteur est rarement le meilleur vecteur. Indexez seulement ce dont vous avez besoin, et souvenez-vous que GIN est un accélérateur de requêtes, pas un repas gratuit.

Micro-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la situation

Une application d’entreprise utilisait Postgres FTS pour la documentation interne de plusieurs départements. Rien de sophistiqué : tsvector par document, index GIN, et quelques filtres. Ce qui était sophistiqué, c’était leur discipline opérationnelle : ils avaient une requête « canari » de recherche dédiée dans la surveillance, exécutée chaque minute avec un ensemble de termes connu.

Un mardi, ils ont vu la latence du canari augmenter progressivement pendant une heure. Pas encore d’incident, juste une pente. L’astreinte a vérifié les stats de vacuum et a constaté que l’autovacuum prenait du retard pour la table stockant les documents. Ils ont aussi vu une augmentation des tuples morts due à une nouvelle fonctionnalité qui mettait à jour les documents plus fréquemment.

Parce qu’ils avaient des bases, ils ont réagi avant que cela ne devienne visible par les utilisateurs : ils ont ajusté les seuils d’autovacuum pour cette table, planifié un VACUUM (ANALYZE) ciblé durant une période calme, et limité temporairement le job de mise à jour des documents. Pas de réunion post-mortem, pas d’e-mails internes en colère, juste un retour au normal tranquille.

C’est la pratique ennuyeuse : une requête synthétique, suivie dans le temps, liée à un sous-système spécifique. Ce n’est pas glamour. C’est moins cher que les actions héroïques.

Blague #2 : La façon la plus rapide d’améliorer la pertinence de la recherche est de renommer la fonctionnalité « filtre par mot-clé » et de regarder les attentes s’aligner sur la réalité.

Tâches pratiques : commandes, sorties, signification, décision

Ce sont des tâches réelles que vous pouvez exécuter lors d’un déploiement, d’un incident ou d’un postmortem. Chaque tâche inclut une commande, une sortie type, ce que cela signifie, et la décision associée.

Task 1 (MySQL): Confirm FULLTEXT indexes exist and what they cover

cr0x@server:~$ mysql -e "SHOW INDEX FROM articles WHERE Index_type='FULLTEXT'\G"
*************************** 1. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: ft_title_body
 Seq_in_index: 1
  Column_name: title
   Index_type: FULLTEXT
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: ft_title_body
 Seq_in_index: 2
  Column_name: body
   Index_type: FULLTEXT

Signification : Vous avez un index FULLTEXT composite sur title et body. Si la requête de recherche n’utilise que body mais que l’index composite est dans un ordre différent, vous pouvez quand même l’utiliser, mais le comportement peut varier selon le moteur/version et la forme de la requête.

Décision : Assurez-vous que le MATCH(title, body) de votre requête correspond à la liste de colonnes indexées. Sinon, changez la requête ou l’index ; ne comptez pas sur la magie de l’optimiseur.

Task 2 (MySQL): Check stopword and token-size settings that explain “missing results”

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'innodb_ft_%';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3     |
| innodb_ft_max_token_size | 84    |
| innodb_ft_enable_stopword| ON    |
+--------------------------+-------+

Signification : Les tokens plus courts que 3 caractères ne sont pas indexés. Les stopwords sont activés. Les recherches de deux lettres échoueront silencieusement.

Décision : Si votre domaine comporte des tokens courts importants (SKU, codes), planifiez un changement de configuration et une reconstruction d’index — ou redessinez pour stocker un champ normalisé séparé pour les codes et le rechercher avec des index B-tree.

Task 3 (MySQL): Validate whether the optimizer is using FULLTEXT or doing something expensive

cr0x@server:~$ mysql -e "EXPLAIN SELECT id FROM articles WHERE MATCH(title, body) AGAINST('incident response' IN NATURAL LANGUAGE MODE) AND tenant_id=42 ORDER BY updated_at DESC LIMIT 20\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
         type: fulltext
possible_keys: ft_title_body,idx_tenant_updated
          key: ft_title_body
      key_len: 0
          ref:
         rows: 12000
        Extra: Using where; Using filesort

Signification : Il utilise FULLTEXT (type: fulltext), mais fait aussi un filesort. C’est souvent l’endroit où la latence part en fumée sous charge.

Décision : Envisagez de changer l’UX/la requête : récupérez d’abord les N meilleurs résultats (sans ORDER BY updated_at), puis triez côté appli ; ou maintenez une stratégie d’index séparée « récents par tenant » ; ou acceptez un tri moins strict.

Task 4 (MySQL): Identify full-text heavy queries in the slow log

cr0x@server:~$ sudo awk '/MATCH\(|AGAINST\(/ {print}' /var/log/mysql/mysql-slow.log | head -n 5
# Query_time: 1.842  Lock_time: 0.000 Rows_sent: 20  Rows_examined: 250000
SELECT id,title FROM articles WHERE MATCH(title,body) AGAINST('status page' IN BOOLEAN MODE) AND tenant_id=42 ORDER BY updated_at DESC LIMIT 20;
# Query_time: 1.221  Lock_time: 0.000 Rows_sent: 0  Rows_examined: 180000
SELECT id FROM articles WHERE MATCH(body) AGAINST('to be' IN NATURAL LANGUAGE MODE) AND tenant_id=42 LIMIT 20;

Signification : Vos requêtes examinent un nombre énorme de lignes par rapport aux lignes renvoyées. Notez aussi que « to be » renvoie 0 (probablement stopwords/taille mini).

Décision : Ajoutez des garde-fous : longueur minimale de requête, message UI conscient des stopwords, et réécriture de requêtes qui appliquent d’abord des filtres sélectifs.

Task 5 (MySQL): Check for replication lag caused by indexing churn

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 143

Signification : Vous avez 143 secondes de retard. Les mises à jour lourdes en FULLTEXT peuvent amplifier cela lors de rafales.

Décision : Si les mises à jour de recherche causent le retard, découplez l’indexation (asynchrone), batchifiez les mises à jour hors-pointe, ou déplacez la recherche hors du chemin principal de la BD.

Task 6 (PostgreSQL): Confirm FTS index type and size (bloat early warning)

cr0x@server:~$ psql -d appdb -c "\di+ public.*fts*"
                                           List of relations
 Schema |     Name      | Type  |  Owner   |   Table   | Persistence | Access method |  Size  | Description
--------+---------------+-------+----------+-----------+-------------+---------------+--------+-------------
 public | docs_fts_gin  | index | app_user | docs      | permanent   | gin           | 845 MB |
(1 row)

Signification : L’index GIN fait 845 MB. Cela peut être normal — ou être un signe de ballonnement, selon la taille de la table et le churn.

Décision : Comparez la croissance de l’index dans le temps. Si l’index croît plus vite que la table, investiguez le vacuum et les patterns de mise à jour.

Task 7 (PostgreSQL): Inspect autovacuum/vacuum health for the FTS table

cr0x@server:~$ psql -d appdb -c "SELECT relname,n_live_tup,n_dead_tup,last_autovacuum,last_vacuum FROM pg_stat_user_tables WHERE relname IN ('docs');"
 relname | n_live_tup | n_dead_tup |     last_autovacuum     |        last_vacuum
---------+------------+------------+-------------------------+-------------------------
 docs    |    2100342 |     482991 | 2025-12-28 09:42:11+00  |
(1 row)

Signification : Près d’un demi-million de tuples morts. C’est un risque de backlog de vacuum, et pour le FTS appuyé par GIN cela peut se traduire par une latence de requête plus élevée et des index plus volumineux.

Décision : Ajustez autovacuum pour cette table (seuils plus bas), et envisagez de réduire le churn des mises à jour sur le texte indexé.

Task 8 (PostgreSQL): See whether your query is using GIN and how expensive it is

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tenant_id=42 AND fts @@ websearch_to_tsquery('english','incident response') ORDER BY updated_at DESC LIMIT 20;"
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2312.44..2312.49 rows=20 width=16) (actual time=118.230..118.244 rows=20 loops=1)
   Buffers: shared hit=18543 read=412
   ->  Sort  (cost=2312.44..2320.12 rows=3071 width=16) (actual time=118.228..118.237 rows=20 loops=1)
         Sort Key: updated_at DESC
         Sort Method: top-N heapsort  Memory: 34kB
         ->  Bitmap Heap Scan on docs  (cost=122.50..2231.64 rows=3071 width=16) (actual time=24.911..113.775 rows=5208 loops=1)
               Recheck Cond: (fts @@ websearch_to_tsquery('english'::regconfig, 'incident response'::text))
               Filter: (tenant_id = 42)
               Buffers: shared hit=18512 read=412
               ->  Bitmap Index Scan on docs_fts_gin  (cost=0.00..121.73 rows=16347 width=0) (actual time=23.021..23.022 rows=16221 loops=1)
                     Index Cond: (fts @@ websearch_to_tsquery('english'::regconfig, 'incident response'::text))
 Planning Time: 1.112 ms
 Execution Time: 118.410 ms
(13 rows)

Signification : GIN est utilisé, mais le filtrage par tenant est appliqué après le bitmap heap scan : vous récupérez beaucoup de correspondances puis vous en rejetez la plupart. Les buffers montrent un travail substantiel.

Décision : Envisagez une stratégie composite : ajouter un index partiel par tenant (si le nombre de tenants est petit), stocker tenant_id dans l’identité du document et partitionner par tenant, ou maintenir un fts par shard tenant séparé.

Task 9 (PostgreSQL): Confirm text search configuration and stemming behavior

cr0x@server:~$ psql -d appdb -c "SHOW default_text_search_config;"
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

Signification : La configuration par défaut est English. Si vous indexez du contenu multilingue, c’est un risque pour la pertinence et la justesse.

Décision : Choisissez des configurations par langue de document, ou utilisez simple pour les tokens non linguistiques. Ne supposez pas qu’une seule configuration de racinisation convient à tout.

Task 10 (PostgreSQL): Validate what tokens are actually indexed

cr0x@server:~$ psql -d appdb -c "SELECT to_tsvector('english','Running runners ran easily') AS v;"
                     v
-------------------------------------------
 'easili':4 'ran':3 'run':1,2
(1 row)

Signification : « Running » et « runners » normalisés en « run », « easily » en « easili ». Voilà pourquoi la racinisation peut aider ou nuire.

Décision : Si la racinisation casse des termes métier, changez de configuration ou maintenez des champs séparés en dictionnaire simple pour les codes/noms.

Task 11 (PostgreSQL): Catch “unsafe tsquery” construction before it becomes a page

cr0x@server:~$ psql -d appdb -c "SELECT to_tsquery('english','foo:bar');"
ERROR:  syntax error in tsquery: "foo:bar"

Signification : L’entrée utilisateur peut générer des erreurs de syntaxe si vous utilisez to_tsquery directement.

Décision : Utilisez plainto_tsquery ou websearch_to_tsquery pour l’entrée utilisateur. Traitez to_tsquery brut comme une API interne.

Task 12 (System): Identify whether search is CPU-bound or IO-bound on the database host

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  0      0  81244  52120 912340    0    0   210   180  790 1460 45 12 40  3  0
 6  1      0  65010  52080 910120    0    0  4520  1100 1200 2400 32 10 35 23  0
 7  2      0  64122  51990 905332    0    0  6120  1450 1350 2600 28 11 33 28  0
 5  1      0  63200  51920 900110    0    0  5900  1500 1300 2500 29 12 34 25  0
 4  0      0  62910  51860 899820    0    0  4800  1200 1150 2300 31 11 37 21  0

Signification : wa (IO wait) monte à 20–28%. Cela suggère que le stockage est un goulot, pas seulement le CPU. Les requêtes full-text se transforment souvent en charges « lire beaucoup de pages » quand la sélectivité est faible.

Décision : Si l’attente IO est élevée, priorisez le taux de hit cache, la sélectivité des index et la performance du stockage. Si le CPU est saturé avec peu d’attente IO, concentrez-vous sur les plans de requête, les coûts de tokenisation et les limites de concurrence.

Task 13 (PostgreSQL): Find the top time-consuming statements (including search)

cr0x@server:~$ psql -d appdb -c "SELECT calls, total_exec_time::bigint AS ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;"
 calls |   ms   | mean_ms | rows |                              query
-------+--------+---------+------+-------------------------------------------------------------------
  8123 | 984221 | 121.21  |  0   | SELECT id FROM docs WHERE tenant_id=$1 AND fts @@ websearch_to_tsquery('english',$2) ORDER BY updated_at DESC LIMIT 20
  1102 | 312110 | 283.22  |  1   | UPDATE docs SET body=$1, fts=to_tsvector('english',$1) WHERE id=$2
   989 | 221004 | 223.45  |  1   | SELECT count(*) FROM docs WHERE fts @@ plainto_tsquery('english',$1)
(3 rows)

Signification : Vos plus gros consommateurs incluent à la fois des lectures de recherche et des écritures de mise à jour FTS. C’est le profil classique « la recherche pèse tout ».

Décision : Décidez si vous devez (a) réduire la fréquence des mises à jour des champs indexés, (b) déplacer les mises à jour de vecteurs hors du chemin de la requête, ou (c) déplacer la recherche vers un système séparé.

Task 14 (MySQL): Confirm whether your FULLTEXT query is returning low selectivity (too many matches)

cr0x@server:~$ mysql -e "SELECT COUNT(*) AS matches FROM articles WHERE MATCH(title, body) AGAINST('status' IN NATURAL LANGUAGE MODE);"
+---------+
| matches |
+---------+
| 182344  |
+---------+

Signification : Un terme très courant correspond à un pourcentage énorme de documents. C’est une faible sélectivité ; cela entraîne un travail important, des problèmes de tri et des manques de cache.

Décision : Ajoutez des filtres, exigez des termes supplémentaires, ajustez les stopwords, ou basculez l’UX vers une recherche limitée (par projet/tenant/plage de dates).

Feuille de route pour un diagnostic rapide

Quand la recherche ralentit, vous n’avez pas le temps d’un débat philosophique sur les moteurs. Vous devez trouver le goulot en quelques minutes.

Premier point : est-ce le plan de requête, la saturation des ressources ou le biais des données ?

• Vérifiez la saturation du système : CPU vs IO wait. Si l’IO wait est élevé, vous lisez trop depuis le disque ou le stockage a du mal. Si le CPU est élevé avec peu d’IO wait, vous effectuez des classements/analyses coûteux ou subissez trop de concurrence.
• Vérifiez le plan de requête : Utilisez-vous l’index FTS ? Triez-vous de grands ensembles candidats ? Les filtres sont-ils appliqués tôt ou tard ?
• Vérifiez la sélectivité : Des recherches sur des termes communs renvoyant de grands ensembles correspondants ? Ce n’est pas une question de « performance », c’est des mathématiques.

Deuxième point : séparez la douleur « lecture recherche » de la douleur « écriture index »

• Douleur lecture : selects lents, misses de buffers, larges scans bitmap, filesorts, tables temporaires, grand nombre de lignes examinées.
• Douleur écriture : mises à jour lentes, retard de réplication, pics WAL/binlog, autovacuum en retard, contention de verrous autour de la maintenance.

Troisième point : décidez de la stratégie de confinement

• Confinement immédiat : rate-limit de la recherche, longueur minimale de requête, désactiver les tris coûteux, limiter les résultats, renvoyer des résultats partiels.
• Réparation à court terme : ajouter ou corriger des index, changer la structure des requêtes, tuner vacuum/autovacuum, réécrire la stratégie de tokenisation.
• Ré-architecture : si la recherche est un des principaux consommateurs de ressources, isolez-la : réplicas pour la recherche, base dédiée, ou moteur de recherche externe.

Erreurs courantes : symptôme → cause racine → correctif

1) « La recherche ne renvoie rien pour les termes courts »

Symptôme : Codes produit de deux lettres ou noms courts ne correspondent jamais.

Cause racine : innodb_ft_min_token_size MySQL trop élevé, stopwords activés ; configuration de racinisation/tokenisation Postgres inadaptée pour les codes.

Correctif : Pour MySQL, ajuster la taille minimale des tokens et reconstruire les index (prévoir une fenêtre de maintenance). Pour Postgres, indexer les codes séparément avec B-tree ou utiliser la configuration simple sur des champs dédiés.

2) « La recherche a ralenti après l’ajout de ORDER BY updated_at »

Symptôme : La requête utilise l’index FTS mais la latence et le CPU/IO augmentent.

Cause racine : Tri de grands ensembles candidats ; filesort/temp tables MySQL ; tri après bitmap heap scan Postgres.

Correctif : Changez l’UX (trier par rang plutôt que par date), utilisez une récupération en deux étapes, ou pré-calculer les « documents récents » par tenant et rechercher dans cette fenêtre.

3) « Postgres FTS devient soudainement saccadé, puis reste mauvais »

Symptôme : Pics périodiques de latence ; taille d’index qui augmente ; vacuum en retard.

Cause racine : Ballonnement d’index GIN + vacuum en retard ; mises à jour lourdes des champs texte indexés.

Correctif : Tuner autovacuum par table ; réduire le churn, batchifier les mises à jour, ou reconstruire les index lors de la maintenance. Si le churn est requis par le business, isolez la charge de recherche.

4) « Certaines recherches utilisateurs donnent des erreurs dans Postgres »

Symptôme : Une sous-partie des requêtes retourne 500 avec des erreurs de syntaxe tsquery.

Cause racine : Utiliser to_tsquery directement sur l’entrée utilisateur.

Correctif : Remplacez par websearch_to_tsquery (meilleure UX) ou plainto_tsquery. Logguez les tokens rejetés, ne plantez pas.

5) « La recherche MySQL est inconsistante entre environnements »

Symptôme : Staging renvoie des résultats ; prod non, ou le scoring diffère.

Cause racine : Listes de stopwords différentes, taille de token, collation/charset ou différences de moteur/version.

Correctif : Standardisez la configuration MySQL entre environnements ; définissez explicitement collations/charsets ; traitez la liste de stopwords comme une configuration testée.

6) « La recherche ralentit tout le reste dans la base »

Symptôme : Quand le trafic de recherche augmente, des endpoints sans rapport deviennent plus lents.

Cause racine : CPU/IO/cache partagés ; scans de texte intégral et classement en compétition avec la charge OLTP ; effets d’ondulation.

Correctif : Limitez la concurrence de la recherche ; déplacez les lectures de recherche sur des réplicas ; isolez dans une base ou un service séparé quand cela devient un contributeur majeur.

7) « Les résultats semblent stupides : des documents communs dominent »

Symptôme : Les longs documents ou documents spammy sont toujours en tête.

Cause racine : Classement mal réglé ; poids égaux ; normalisation non alignée avec votre domaine ; champs texte de faible qualité inclus.

Correctif : Pondérez le titre plus que le corps ; excluez le boilerplate ; stockez les champs structurés séparément ; dans Postgres utilisez des vecteurs pondérés et la normalisation de score ; dans MySQL envisagez des contraintes en mode booléen ou des filtres supplémentaires.

8) « La recherche multi-tenant est lente même avec des index »

Symptôme : Le filtre tenant est présent mais la requête lit toujours de gros volumes.

Cause racine : L’index FTS n’incorpore pas tenant_id, donc vous obtenez des correspondances sur tous les tenants puis vous filtrez.

Correctif : Partitionnez par tenant, utilisez des tables séparées par tenant (si faisable), ou maintenez des index par tenant/partiels ; au minimum, redessinez pour rechercher d’abord dans un sous-ensemble scoped au tenant.

Listes de contrôle / plan pas à pas

Pas à pas : déployer un FTS intégré sans détester votre vous futur

1. Rédigez le « contrat de recherche » : opérateurs supportés, longueur minimale de requête, langues supportées, règles de classement, et ce que « phrase » signifie. Si vous ne pouvez pas le décrire, les utilisateurs le découvriront à la dure.
2. Choisissez les limites de périmètre : recherche par tenant seulement, fenêtres de date, types de documents. Ajoutez des filtres qui réduisent les ensembles candidats tôt.
3. Décidez des tokenisations et configurations de langue (Postgres) ou des stopwords/taille des tokens (MySQL). Rendre cela explicite dans la gestion de configuration.
4. Indexez seulement ce qui compte : ne mettez pas tous les blobs texte dans le vecteur/index. Traitez le texte indexé comme un centre de coût.
5. Définissez la « construction de requête sûre » : ne parsez jamais l’entrée utilisateur comme syntaxe sans échappement/traduction. Postgres : préférez websearch_to_tsquery. MySQL : assainissez les opérateurs booléens si vous les exposez.
6. Testez avec des requêtes adversariales : mots courants, résultats vides, ponctuation, Unicode, chaînes extrêmement longues, « copier-coller depuis Word ».
7. Installez des garde-fous : limites de requêtes, timeouts, coupe-circuit, et réponses « désolé, affinez votre recherche ».
8. Surveillez une requête canari et suivez P95, lectures de buffers et retard de réplication. Les défaillances de recherche apparaissent souvent comme une dégradation progressive.
9. Planifiez votre sortie : documentez comment vous dupliqueriez les documents vers un système de recherche dédié. Même si vous ne l’utilisez jamais, cette discipline de conception aide.

Checklist : choisir entre FTS intégré MySQL et Postgres

• Si vous avez besoin de flexibilité et d’une pertinence réglable : Postgres l’emporte. Vous pouvez pondérer les champs, choisir des dictionnaires et construire un comportement de recherche plus structuré.
• Si vous voulez le moins de pièces mobiles possible et acceptez un scoring plus simple : MySQL FULLTEXT peut convenir, surtout pour une recherche par mot-clé simple avec contraintes.
• Si vous avez un fort churn d’écritures sur le texte indexé : soyez prudent avec les deux ; la maintenance GIN de Postgres et les mises à jour auxiliaires d’index MySQL mordent toutes les deux. Envisagez des patterns d’indexation asynchrone.
• Si le multilingue est important : Postgres est généralement plus facile à configurer correctement — à condition que vous le fassiez vraiment.
• Si vous avez besoin de la sémantique « recherche web » : websearch_to_tsquery de Postgres est un avantage pratique pour les requêtes saisies par l’utilisateur.

Checklist : signes qu’il faut passer à un moteur de recherche dédié

• La recherche est parmi les 3 premiers consommateurs CPU ou IO de la base.
• Vous avez besoin de correspondance floue, synonymes, autocomplétion, surlignage, analyseurs par champ, ou classement hybride lexical+vecteur.
• Vous ajoutez des règles de pertinence par tenant et votre schéma commence à ressembler au fichier de configuration d’un moteur de recherche.
• Les incidents impliquent régulièrement « le trafic de recherche a rendu la base triste ».
• Votre organisation peut opérer un autre système étatful sans le traiter comme un animal de compagnie.

FAQ

1) Est-ce que MySQL FULLTEXT est « mauvais » ?

Non. Il est simplement opinionné et limité. Il est excellent pour la recherche par mot-clé simple avec contraintes. Il devient pénible quand vous avez besoin d’une pertinence sophistiquée ou quand la charge de recherche concurrence la charge OLTP.

2) Est-ce que la recherche PostgreSQL remplace Elasticsearch/OpenSearch ?

Parfois, pour des fonctionnalités de recherche internes ou modérées. Si vous avez besoin de tolérance aux fautes de frappe, d’analyseurs riches, de faceting rapide à grande échelle, ou de pipelines de classement avancés, un moteur dédié vous manquera vite.

3) Pourquoi mes recherches Postgres se comportent-elles bizarrement avec la ponctuation et les caractères spéciaux ?

Parce que tsquery a une syntaxe. Si vous construisez un tsquery directement à partir de l’entrée utilisateur, la ponctuation peut devenir des opérateurs ou générer des erreurs de syntaxe. Utilisez websearch_to_tsquery ou plainto_tsquery.

4) Pourquoi ORDER BY updated_at est-il si coûteux avec FTS ?

Parce que FTS renvoie un ensemble de documents correspondants ; ordonner par un autre attribut nécessite souvent de trier un grand ensemble candidat. Si l’ensemble est grand, vous triez beaucoup. Envisagez d’ordonner par rang, de préfiltrer, ou une récupération en deux étapes.

5) Comment améliorer le classement dans Postgres ?

Séparez les champs (titre, corps, tags), attribuez des poids, et utilisez des vecteurs pondérés. Envisagez aussi de retirer le texte boilerplate du champ indexé. Le classement est autant une question d’hygiène des données que de mathématiques.

6) Quelle est la meilleure façon de gérer la recherche multi-tenant ?

Limitez d’abord la recherche au tenant. Pour Postgres, envisagez la partition par tenant ou les index partiels quand le nombre de tenants est gérable. Pour MySQL, assurez-vous que les filtres tenant sont sélectifs et évitez les recherches globales qui filtrent ensuite.

7) Faut-il stocker tsvector comme colonne générée ?

Les colonnes générées sont pratiques, mais elles mettent le coût d’indexation sur le chemin d’écriture. Si vous avez un fort churn, envisagez des mises à jour asynchrones ou des batches, selon vos besoins de cohérence.

8) Comment savoir si le problème vient de l’index ou de la requête ?

Regardez le plan et les buffers/lignes examinées. Si l’index FTS est utilisé mais que vous lisez beaucoup, vous avez probablement une faible sélectivité ou un tri/filtrage coûteux. Si l’index n’est pas utilisé, c’est la construction de la requête ou les statistiques du planificateur.

9) Peut-on faire des recherches de phrase avec le FTS intégré ?

Postgres supporte la recherche de phrase via phraseto_tsquery et des fonctionnalités associées, mais ce n’est pas identique à une « correspondance exacte de sous-chaîne ». MySQL en mode booléen a des opérateurs mais le comportement de phrase n’équivaut pas aux moteurs de recherche dotés d’index positionnels optimisés pour le surlignage.

10) Quel est le plus grand risque opérationnel avec le FTS intégré ?

La charge de recherche est éruptive et pilotée par les utilisateurs. Elle transforme votre base primaire en un pool de calcul partagé pour des requêtes imprévisibles, et c’est ainsi que des endpoints non liés subissent des dégâts collatéraux.

Prochaines étapes pratiques

Si vous utilisez déjà la recherche en texte intégral intégrée, votre travail consiste à l’empêcher de devenir silencieusement le principal consommateur de votre base.

1. Ajoutez un canari de recherche (une requête, un tableau de bord, une alerte). Suivez latence, lignes examinées et lectures de buffers au fil du temps.
2. Mettez en place des garde-fous : longueur minimale de requête, limites de débit, timeouts, et une fenêtre maximale de résultats raisonnable.
3. Exécutez les tâches diagnostics ci-dessus pendant une fenêtre calme et enregistrez des bases : tailles d’index, stats de vacuum, requêtes lentes, formes de plans.
4. Décidez de votre stratégie de confinement : réplicas pour lectures de recherche, ou isolation de la recherche dans un service séparé quand elle devient un consommateur majeur.
5. Faites de la pertinence une décision produit, pas un résultat accidentel des valeurs par défaut. Si vous avez besoin de plus que des poids et de la tokenisation, arrêtez de négocier avec votre base et budgétez un moteur de recherche dédié.

La recherche intégrée peut être un outil tranchant. Ne l’utilisez simplement pas comme un marteau sur un problème qui appelle clairement une cloueuse.