Recherche plein texte PostgreSQL : bien configurée, elle est rapide (dissiper le mythe de la lenteur)

 (blog.vectorchord.ai)
33 points par GN⁺ 2025-04-10 | 3 commentaires | Partager sur WhatsApp
  • La Full-Text Search (FTS) native de PostgreSQL a la réputation d’être lente, mais avec une optimisation adaptée, elle peut être très rapide
  • Le blog de Neon compare l’extension pg_search basée sur Rust à la FTS native et affirme que cette dernière est lente
  • Mais cette comparaison a probablement été réalisée en omettant des optimisations de base indispensables à la FTS de PostgreSQL
  • Cet article démontre chiffres à l’appui qu’une simple optimisation de la configuration FTS native peut apporter un gain de performances de 50x

Aperçu de la configuration du benchmark

  • Test réalisé sur une table contenant 10 millions de lignes de logs 
    CREATE TABLE benchmark_logs (  
    id SERIAL PRIMARY KEY,  
    message TEXT,  
    country VARCHAR(255),  
    severity INTEGER,  
    timestamp TIMESTAMP,  
    metadata JSONB  
);
  • Structure de la requête problématique : 
    SELECT country, COUNT(*)  
FROM benchmark_logs  
WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
GROUP BY country  
ORDER BY country;
    • Exécution de to_tsvector() dans la requête → très inefficace
    • Même avec un index GIN, il n’est pas correctement exploité

Environnement de test (reproduction de la configuration de base)

  • Instance EC2 i7ie.xlarge avec SSD NVMe local
  • 4 vCPU, PostgreSQL 16 (Docker)
  • Principaux paramètres PostgreSQL : 
    -c shared_buffers=8GB  
-c maintenance_work_mem=8GB  
-c max_parallel_workers=4  
-c max_worker_processes=4
  • Limite de parallélisme : max_parallel_workers_per_gather = 2 (Neon utilise 8)

Facteur de dégradation n°1 : calcul de tsvector en temps réel

  • Lorsque to_tsvector() est exécuté dans la requête :
  • l’analyse du texte, la tokenisation et les traitements morphologiques sont refaits à chaque fois
  • l’index ne peut pas être utilisé du tout

  • Solution : précréer une colonne tsvector et l’indexer

    • 1. Ajouter la colonne tsvector
    ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;
    • 2. Remplir les données 
      UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);
    • 3. Créer l’index (avec fastupdate désactivé) 
      CREATE INDEX idx_gin_logs_message_tsvector  
ON benchmark_logs USING GIN (message_tsvector)  
WITH (fastupdate = off);
    • 4. Modifier la requête 
      SELECT country, COUNT(*)  
FROM benchmark_logs  
WHERE message_tsvector @@ to_tsquery('english', 'research')  
GROUP BY country  
ORDER BY country;

Facteur de dégradation n°2 : fastupdate=on sur l’index GIN

  • fastupdate=on favorise les performances en écriture, mais pénalise les performances de recherche
  • Pour des jeux de données en lecture seule ou orientés recherche, fastupdate=off est indispensable
  • L’index est plus petit et plus rapide, et il n’y a plus de gestion de pending list

  • Comment créer un index GIN optimisé

    CREATE INDEX idx_gin_logs_message_tsvector  
ON benchmark_logs USING GIN (message_tsvector)  
WITH (fastupdate = off);

Gains de performance : plus de 50x d’amélioration

  • Avant optimisation : environ 41,3 s (41 301 ms)
  • Après optimisation : environ 0,88 s (877 ms)
  • Soit un gain de performances d’environ 50x
  • Ce niveau de performance est atteignable même dans un environnement peu parallélisé

Les performances de ts_rank peuvent effectivement être lentes

  • ts_rank ou ts_rank_cd peuvent être relativement lents, car ils évaluent tous les résultats avant de les trier
  • En particulier sur de gros volumes de résultats, la charge CPU/IO peut être importante

Fonctionnalité de ranking avancée : extension VectorChord-BM25

  • Lorsque la précision du tri et la vitesse sont critiques, il peut être plus efficace d’utiliser une extension dédiée
  • VectorChord-BM25 est une extension pour PostgreSQL qui fournit une évaluation du ranking basée sur l’algorithme BM25
  • Certains retours indiquent qu’elle peut être 3x plus rapide qu’Elasticsearch

Avantages de VectorChord-BM25

  • Algorithme BM25 : un algorithme de ranking de recherche plus avancé que TF-IDF
  • Format d’index dédié : optimisé pour la recherche rapide avec notamment Block WeakAnd
  • Fournit le type bm25vector : stockage d’une représentation tokenisée
  • Améliore à la fois la précision de recherche et la vitesse

Conclusion : la FTS native de PostgreSQL est largement assez rapide

  • Avec une colonne tsvector et un index GIN approprié (fastupdate=off), la FTS native permet déjà des recherches très rapides
  • Les comparaisons de performances doivent se faire sur une base correctement optimisée
  • Si des fonctions de ranking avancées sont nécessaires, on peut envisager des extensions comme VectorChord-BM25
  • Message clé : ce n’est pas l’outil qui est lent, c’est parfois la configuration qui pose problème

À lire aussi

3 commentaires

 
stadia 2025-06-03

Grâce à cela, j’ai fait du tuning de requête.
 
pcj9024 2025-04-10

Les réactions sur Hacker News font peur... « Dix millions ? Une blague ? »
 
GN⁺ 2025-04-10
Avis Hacker News

  • En tant que mainteneur de pg_search, selon la documentation Postgres, l’article de Neon/ParadeDB et la stratégie utilisée ici sont tous deux présentés comme des alternatives valides

    • Le problème de Postgres FTS n’est pas d’optimiser une seule requête, mais d’offrir des performances de niveau Elastic sur une grande variété de requêtes réelles
    • pg_search a été conçu pour résoudre ce second problème, et les benchmarks le reflètent également
    • Le benchmark Neon/ParadeDB n’inclut que 12 requêtes au total, ce qui est peu réaliste pour des cas d’usage réels
    • pg_search fonctionne sur une variété de requêtes de style « Elastic » et de types Postgres avec une simple définition d’index

  • Calculer tsvector en temps réel est une grosse erreur

    • Quand j’ai implémenté Postgres FTS dans un projet personnel, j’ai lu la documentation et suivi les recommandations
    • La documentation explique clairement comment partir d’un cas de base non optimisé puis l’optimiser
    • J’ai l’impression que la personne qui a fait cette erreur n’a pas lu la documentation, ou qu’elle cherche à présenter Postgres FTS de manière trompeuse

  • Je ne comprends pas cette tendance à vouloir tout mettre dans Postgres

  • Je suis heureux de voir davantage d’implémentations de recherche en texte intégral natives à Postgres

    • Les solutions alternatives (lucene/tantivy) sont conçues pour des segments immuables, donc combinées avec les tables heap de Postgres elles peuvent aboutir à une moins bonne solution

  • Sans plan d’exécution, il est difficile de comprendre ce qui se passe

    • Si la requête utilise l’index, la revérification en temps réel de tsvector ne s’applique qu’aux correspondances, et comme la requête de benchmark est en LIMIT 10, il y a peu de revérifications
    • Comme les conditions de requête portent sur deux index GIN, on dirait que l’optimiseur réexamine d’abord toutes les correspondances

  • Il y a quelques années, je voulais utiliser le FTS natif, mais j’ai échoué

    • Sur une table avec des milliers d’insertions par seconde, les mises à jour globales sont devenues lentes au point que les transactions expiraient
    • J’ai ajouté des index, mais une fois le deuxième index terminé, le système s’est mis à expirer
    • J’ai dû supprimer à nouveau les index, et je n’ai jamais eu l’occasion de tester les performances réelles du FTS

  • J’ai packagé les RPM/DEB des extensions pg_search et vchord_bm25

    • Je fournis le lien pour ceux qui veulent faire leurs propres benchmarks

  • J’ai vu beaucoup d’équipes passer directement à Elasticsearch ou Meilisearch

    • Bien utilisé, le FTS natif de PG peut offrir de très bonnes performances
    • Je me demande si on pourrait obtenir des performances similaires dans le navigateur avec SQLite + FTS5 + Wasm

  • 10 millions d’enregistrements, c’est un dataset jouet

    • De gros jeux de données textuelles, comme l’intégralité de Wikipedia ou les commentaires Reddit d’avant 2022, seraient plus adaptés à un benchmark

  • J’ai utilisé pour la première fois le texte intégral de pg vers 2008

    • Le problème de la recherche en texte intégral de Postgres n’est pas qu’elle soit trop lente, mais qu’elle manque de flexibilité
    • Elle convient bien pour ajouter une recherche simple, mais atteint vite ses limites dès qu’il faut affiner la recherche
    • Solr et Elasticsearch permettent de configurer des index complexes et un traitement de recherche avancé
    • Postgres pourrait adopter ce type de fonctionnalités, mais pour l’instant il ne propose rien de tel
    • Postgres segmente sur les espaces, et on peut utiliser manuellement des stop words et du stemming
    • Il est impossible d’attribuer un score de recherche basé sur le poids des champs
    • Comparé aux alternatives, c’est un système jouet