PostgreSQL Extension Day 2025

Le 12 mai 2025 se tiendra l’événement gratuit PostgreSQL Extension Day à Montréal, soit la veille de la conférence PostgreSQL PGConf.dev qui se tiendra pour sa part du 13 au 15 mai. Cette conférence n’est toutefois pas affiliée à la PGConf.dev.

Ce rassemblement a pour but de réunir tous les acteurs qui contribuent et utilisent les extensions de PostgreSQL constituant un écosystème de plusieurs centaines de projets: près de 400 actuellement. Le PostgreSQL Extension Day est organisé par le groupe Postgres Extension Developers Coalition.

Le CFP est ouvert jusqu’au 1er avril 2025, et les inscriptions sont ouvertes par ici.

L’extension pg_trgm

Présentation

L’extension pg_trgm (trigrammes) est fournie dans la distribution standard de PostgreSQL. Elle est présente dans /contrib et s’installe simplement dans une base de données:

Cette extension permet de décomposer une chaîne de caractères en succession de sous-chaînes de 3 caractères (trigrammes), afin de permettre des recherches sur une sous-chaîne, ou bien des recherches de similarité entre chaînes de caractères.

Fonctionnement

Jeu d’essai

Dans le cadre de cette présentation, je me suis constitué une table d’un million de lignes, laquelle contient un champ family contenant un nom de famille parmi les 1000 plus fréquent en France, et dont la fréquence dans la table est semblable à celle de la population française:

Décomposition

On peut visualiser la décomposition en trigrammes avec la fonction show_trgm() :

Similarité

La fonction similarity() permet de tester la similarité entre deux chaînes de caractères. Le résultat est un score entre 0 et 1. Zéro indique qu’il n’y a aucun trigramme en commun entre les deux chaînes, tandis que 1 indique que les deux chaînes sont identiques. On peut ainsi tester la similarité entre deux noms de famille:

L’opérateur booléen de similarité entre deux chaînes est% :

L’opérateur booléen retourne True si le score de similarité excède une limite fixée par défaut à 0.3. La limite courante peut être consultée avec la fonction show_limit() :

Et cette limite peut être modifiée au niveau de la session avec la fonction set_limit(). Ainsi, si on passe le seuil à 0.1, ‘GRANGER’ et ‘LEGRAND’ sont désormais considérés comme similaires:

Cette limite peut être configurée au niveau du cluster avec le paramètre pg_trgm.similarity_threshold.

Indexation et performances

L’indexation BTREE classique

Les champs TEXT peuvent être indexés classiquement avec un index BTREE:

Cela permet de trouver rapidement des chaînes de caractères ou des débuts de chaînes de caractères:

Cependant un tel index se révèle inutile lorsqu’on ne connaît pas le début de la chaîne recherchée. Dance ce cas on bascule sur un Seq Scan malgré la présence de l’index:

Indexation des trigrammes

Il est possible d’indexer les vecteurs de trigrammes avec un index GIN:

La recherche sur la fin de chaîne de caractères se fait maintenant en utilisant l’index nouvellement créé:

Nous pouvons maintenant effectuer une recherche de similarité:

Cette recherche par similarité peut être utile, votre serviteur en sait quelque chose avec son patronyme qui comporte un ‘B’ muet et qui entraîne souvent moultes confusions lorsque je dois épeler mon nom, et qui est donc écrit souvent approximativement:

Performances

On peut voir que sur une recherche d’égalité, ou bien sur une recherche de début de chaîne, c’est l’index B-Tree qui est préféré par le planner:

Il est cependant important de noter que si l’index B-Tree est préféré sur la recherche en début de chaîne ( LIKE 'xxxx%' ) c’est parce que la classe d’opérateurs text_pattern_ops a été utilisée lors de la création de l’index. Si nous créons un index B-Tree sans cette classe d’opérateurs, il sera préféré pour une recherche d’égalité, mais pas pour une recherche de début de chaîne du fait des problèmes complexes liés aux LOCALES des différentes langues:

Si nous supprimons définitivement l’index B-Tree, on voit que l’index sur les trigrammes est utilisé efficacement pour une recherche d’égalité (seulement après PG v13) mais pas aussi efficacement qu’avec l’index B-Tree (coût estimé 7666 vs 1370). Cependant ce coût est remarquablement constant que la recherche se fasse sur une égalité, un début de chaîne (LIKE 'xxx%') ou une recherche sur une sous-chaîne (LIKE '%xxx%').

Conclusion

• Si un champ texte fait l’objet d’une recherche d’égalité dans une clause WHERE, un index B-Tree est parfaitement adéquat.
• Si un champ texte fait l’objet d’une recherche sur un début de chaîne de type WHERE champ LIKE 'ABC%' , un index B-Tree est là encore adéquat, à condition de lui spécifier la classe d’opérateurs text_pattern_ops.
• Si un champ texte fait l’objet d’une recherche sur une sous-chaîne de type WHERE champ LIKE '%ABC%' , seul un index GIN ou GiST sur les trigrammes sera utile.
• Lorsqu’un index sur les trigrammes a été créé, dans la plupart des cas l’index B-Tree peut être supprimé. Cependant, du fait de la meilleure efficacité du B-Tree, il peut être pertinent dans de rares occasions de conserver également l’index B-Tree.

Retour sur la PG Conf Europe 2024

Cette année, la PostgreSQL Conference Europe 2024 s’est déroulée à Athènes, en Grèce, à quelques hectomètres de l’acropole. À nouveau, un record d’affluence est battu cette année avec 779 participants, ce qui en fait l’évènement PostgreSQL le plus important au monde.

La liste des conférences est disponible sur le site de l’évènement: https://2024.pgconf.eu/. Les supports de présentations, ainsi que les enregistrements vidéos sont également mis à disposition.

La conférence d’ouverture est donnée par Stacey Haysler. Le sujet abordé est celui du coût de la licence PostgreSQL. Cette dernière étant gratuite, elle demande une implication des différents acteurs pour que le projet puisse fonctionner et demeurer robuste et pérenne.

Les conférences sont ensuite réparties dans différentes salles, avec 4 conférences simultanées, dont une réservée aux sponsors. Nous résumons ici nos notes à propos des présentations auxquelles nous avons assisté.

Performance

• NUMA vs PostgreSQL

Andres Freund nous explique les particularités de NUMA, qui est une architecture d’accès à la mémoire, ce qui a des conséquences pour les processeurs, et donc les logiciels qui s’en servent. Quels sont les problèmes rencontrés dans le contexte de l’utilisation de PostgreSQL? Cette présentation est complexe, mais détaillée et permet à l’auditoire de mieux comprendre le comportement global des systèmes, tout en ouvrant vers des optimisations possibles de PostgreSQL.

• Streaming I/O and vectored I/O

Les orateurs Thomas Munro & Nazir Bilal Yavuz détaillent un point important concernant les performances des lectures et écritures de données (I/O) : après un historique des solutions, ils expliquent ce que sont les solutions modernes telles que les Streaming I/O et que peut apporter le patch AIO qui est en cours de développement.

• Debugging active queries with mid-flight instrumented explain plans

Rafael Thofehrn Castro nous présente des extensions et patchs pour suivre les plans d’exécutions à la volée dans une instance PostgreSQL. C’est bluffant, malheureusement rien n’est disponible publiquement.

• A Deep Dive into Postgres Statistics

Louise Leinweber détaille de façon claire et précise ce que sont les statistiques sur les données dans PostgreSQL, comment elles sont utilisées dans PostgreSQL et quels leviers nous avons pour agir.

• Porting on-prem performance troubleshooting skills to the cloud

Denzil Ribeiro évoque l’outillage nécessaire à la supervision d’une instance PostgreSQL dans le cloud, en particulier tout ce qui est spécifique aux environnements clouds, très utile lorsqu’on vient d’environnements dits “on-premise”.

• PostgreSQL Observed—and Explained

Stacey Haysler et Karen Jex utilisent quelques points emblématiques des problèmes souvent rencontrés par les utilisateurs de PostgreSQL pour évoquer les bonnes ou mauvaises pratiques et certains anti-patterns connus.

• Vacuuming Large Tables: How Recent Postgres Changes Further Enable Mission Critical Workloads

Robert Treat évoque avec humour ses mésaventures avec les vacuums et les ID de transactions, et les améliorations apportées depuis dans PostgreSQL 17.

• Mastering PostgreSQL Partitioning: Supercharge Performance and Simplify Maintenance

Ryan Booz évoque un outil très utile en ce qui concerne la gestion de la performance: le partitionnement des tables, ses différentes possibilités et cas d’usage, jusqu’aux extensions que sont TimescaleDB et Citus.

High availibility

• Sparta’s Dual Kingship and PostgreSQL Active-Active

Boriss Mejías détaille le fonctionnement d’une réplication active-active, avec toutes les notions, plus ou moins complexes, qui permettent de bien comprendre les contraintes qu’imposent ce type de réplication.

• Patroni Deployment Patterns

Michael Banck expose de façon pratique et claire le fonctionnement de Patroni, avec quelques éléments pertinents à retenir, correspondant à son expérience.

• Comparing Connection Poolers for PostgreSQL

Julian Markwort compare les différents gestionnaires de connexions entre eux. Quelles sont les différentes questions qui se posent pour adopter un tel outil, et pourquoi faut-il choisir pgBouncer?

• Speeding up logical replication setup

Euler Taveira présente le développement qu’il a mené dans PostgreSQL pour intégré l’outil pg_createsubscriber qui permet de convertir une réplication physique en réplication logique, accélérant ainsi la création d’un réplica logique.

Kubernetes

• Crunchy Postgres for Kubernetes: Your virtual DBA

Karen Jex explique le fonctionnement de Kubernetes et l’utilisation de l’opérateur Crunchy Postgres, et comment son fonctionnement s’articule avec le rôle et les responsabilités d’un administrateur de bases de données.

• Demystifying Kubernetes for Postgres DBAs: A Guide to Operators

Adam Wright évoque le lien entre Kubernetes et PostgreSQL: les opérateurs! Différents opérateurs pour PostgreSQL existent et ne sont pas strictement équivalents, ce qui nécessite une compréhension de chacun d’entre eux de la part de l’administrateur de bases de données pour les adopter: sécurité, réseau, sauvegarde, stockage, extension.

• From VMs to Cloud-Native PostgreSQL in Kubernetes: A Case Study of Migrating a Medium-Sized Application

David Pech fait le retour d’expérience d’une migration d’instance PostgreSQL depuis des machines virtuelles vers un cluster Kubernetes. Le choix de l’opérateur Kubernetes est un point important de la démarche. Après avoir fait tomber quelques mythes autour de Kubernetes, l’orateur détaille de plan de travail pour adopter la solution.

• Fun with Postgres High Availability Poker

Dave Pitts et Derk Van Veen introduisent les concepts de haute disponibilité de PostgreSQL par le jeu, ce qui est toujours une bonne manière d’apprendre.

Intelligence artificielle

• Dissimilarity search: implementing in-memory vector search algorithms to PostgreSQL

Jonathan Katz parle de l’extension pgvector, qui est une possibilité offerte aux utilisateurs de PostgreSQL de vectoriser des données et de faire des recherches par approximation.

• Leveraging AI as a PostgreSQL DBA, Grant Fritchey

Grant Fritchey se demande si les prompts d’IA sont de bons outils pour les DBA? Quelles sont les différentes tâches du DBA qui pourraient bénéficier de l’aide d’un assistant conversationnel?

Sécurité

• Untangling the Web of PostgreSQL Permissions

Lætitia Avrot évoque l’ensemble des fonctionnalités liées aux permissions dans PostgreSQL: rôle, groupe, privilèges, Row Level Security, privilèges par défaut.

• PostgreSQL security: defending against external Attacks

Taras Kloba détaille un sujet très important, quoique parfois trop négligé: comment protéger PostgreSQL contre les attaques. Des mises à jour de sécurité à la gestion de l’authentification en passant par la protection des données, cette présentation fait la liste des points à retenir en termes de sécurité.

• Column encryption (solutions and ideas)

Peter Eisentraut fait le tour des besoins et solutions de chiffrement de données disponibles avec PostgreSQL.

Autres

• High-concurrency distributed snapshots, Ants Aasma:

Le modèle actuel de snapshot, qui autorise la visibilité des enregistrements, est maintenant vieux de plus de vingt ans: quel modèle peut-il le remplacer, en prenant en compte la croissance de la concurrence d’accès. L’orateur évoque alors les notions de Commit Sequence Number ou d’un modèle hybride.

• UNDELETE data FROM table;

Christoph Berg explique en détail le fonctionnement de PostgreSQL lorsqu’on lui demande de supprimer un enregistrement, et ce qu’il est possible de faire pour retrouver cet enregistrement avec l’extension pg_dirtyread ou la commande pg_waldump. Dans tous les cas, faites des sauvegardes!

• pg_ivm: extension for rapid incremental view

Yugo Nagata présente l’extension pg_ivm qui permet de créer des vues matérialisées incrémentales, qui sont donc mises à jour rapidement, contrairement aux vues matérialisées existantes dans PostgreSQL qui nécessitent une régénération entière.

Le PGDG (PostgreSQL Global Development Group) a publié une mise à jour de toutes les versions supportées de PostgreSQL, incluant 17.4, 16.8, 15.12, 14.17 et 13.20.

Pour la liste complète des changements, se référer à la note de publication de versions.

Corrections de bogues et améliorations

Les problèmes ci-dessous concernent PostgreSQL 17. Certains de ces problèmes peuvent aussi concerner d’autres versions de PostgreSQL.

Les correctifs sont:

• Amélioration du comportement des fonctions d’échappement de la bibliothèque libpq. Le correctif de la vulnérabilité CVE-2025-1094 a introduit une régression amenant les fonctions d’échappement à ne pas respecter les tailles des chaînes de caractères fournies en paramètres, entraînant dans certains cas des plantages. Ce problème peut impacter une bibliothèque cliente de PostgreSQL en fonction de son intégration à la bibliothèque libpq;
• Correction de fuites mémoire dans la commande pg_createsubscriber;

Mise à jour

Toutes les publications de mises à jour de PostgreSQL sont cumulatives. Comme pour les autres mises à jour mineures, il n’est pas nécessaire d’extraire et de recharger les bases de données ni d’utiliser pg_upgrade pour appliquer cette mise à jour; il suffit simplement d’arrêter PostgreSQL et de mettre à jour les binaires.

Les utilisateurs ayant sauté une ou plusieurs mises à jour peuvent avoir besoin d’étapes additionnelles après la mise à jour. Les notes de publication des versions précédentes fournissent les détails.

Pour plus de détails, se référer à la note de publication de versions.

Liens

• Téléchargements
• Notes de version
• Page sur la sécurité
• Politique de version
• Faire un don

Si vous avez des corrections ou suggestions sur cette annonce de publication, merci de les envoyer à la mailing liste publique pgsql-www@lists.postgresql.org.

Le PGDG (PostgreSQL Global Development Group) a publié une mise à jour de toutes les versions supportées de PostgreSQL, incluant 17.3, 16.7, 15.11, 14.16, 13.19.

Cette publication corrige également une vulnérabilité de sécurité et plus de 70 bogues reportés dans les mois précédents.

Cependant, le PGDG a annoncé mettre à disposition le 20 février prochain un correctif suite à l’introduction d’une régression sur cette mise à jour. Il est recommandé de ne pas procéder à cette mise à jour, mais d’attendre la version 17.4.

Problèmes de sécurité

• CVE-2025-1094:

  • CVSS v3.1 Base Score: 8.1
  • Supported, Vulnerable Versions: 13 - 17.

Une neutralisation inadéquate d’une syntaxe avec guillemets dans les fonctions de libpq PQescapeLiteral(), PQescapeIdentifier(), PQescapeString() et PQescapeStringConn() permet de faire de l’injection SQL dans certains cas d’usage. Spécifiquement, l’injection SQL requiert à l’application d’utiliser le résultat de fonction pour construite l’entrée de psql, le terminal interactif de PostgreSQL. De même, une neutralisation inadéquate d’une syntaxe avec guillemets dans les programmes utilitaires de PostgreSQL en ligne de commande permet à une source d’arguments à ces commandes en ligne d’effectuer de l’injection SQL lorsque le paramètre client_encoding est BIG5 et server_encoding est soit EUC_TW soit MULE_INTERNAL. Les versions antérieures à PostgreSQL 17.3, 16.7, 15.11, 14.16 et 13.19 sont affectées.

Le projet PostgreSQL remercie Stephen Fewer, Principal Security Researcher, Rapid7 pour avoir signalé ce problème.

Corrections de bogues et améliorations

Cette mise à jour corrige plus de 70 bogues ayant été reportés durant les mois précédents. Les problèmes ci-dessous concernent PostgreSQL 17. Certains de ces problèmes peuvent aussi concerner d’autres versions de PostgreSQL.

Les correctifs sont:

• restauration du comportement d’avant la version 17 concernant la troncature des noms de bases de données de plus de 63 octets et les noms d’utilisateurs dans les requêtes de connexion;
• ne pas vérifier des privilèges de connexions et limites sur les processus parallèles, mais les hériter du processus principal;
• suppression du suffixe Lock des noms d’évènements d’attente LWLock;
• correction de la réutilisation de résultats obsolètes dans les agrégats de fenêtrage qui peuvent conduire à des résultats incorrects;
• correction de plusieurs conditions de concurrence pour vacuum qui dans le pire des cas peut conduire à une corruption du catalogue système;
• corrections sur la TRUNCATE de tables et d’index pour prévenir une éventuelle corruption;
• correction sur le détachement d’une partition lorsque sa propre contrainte de clé étrangère fait référence à une table partitionnée;
• correction pour les codes de format FFn (par exemple FF1) pour to_timestamp, où un code de format entier avant le FFn consommait tous les chiffres disponibles;
• corrections pour SQL/JSON et XMLTABLE() pour mettre des entrées spécifiques entre guillemets lorsque cela est nécessaire;
• inclusion de l’option ldapscheme dans la vue pg_hba_file_rules();
• corrections pour UNION, y compris le fait de ne pas fusionner des colonnes avec des collations non compatibles;
• corrections pouvant avoir un impact sur la disponibilité ou la vitesse de démarrage d’une connexion à PostgreSQL;
• correction de plusieurs fuites mémoire dans la sortie du décodage logique;
• correction de plusieurs fuites mémoire avec le langage PL/Python;
• ajout de l’autocomplétion pour la commande COPY (MERGE INTO);
• rendre pg_controldata plus résilient lors de l’affichage d’informations provenant de fichiers pg_control corrompus;
• correction d’une fuite mémoire sur la commande pg_restore avec des données compressées via zstd;
• correction de pg_basebackup pour gérer correctement les fichiers pg_wal.tar de plus de 2GB sur Windows;
• modification sur le module earthdistance pour utiliser le canevas des fonctions standards SQL pour corriger des problèmes sur des mises à jour majeures vers la version 17 quand l’extension earthdistance est utilisée;
• corrige des erreurs avec pageinspect dans des instances où la définition de la fonction brin_page_items() n’est pas à jour de la dernière version;
• corrige des conditions de concurrence lors de tentative d’annulation d’une requête distante avec postgres_fdw;

Cette publication met aussi à jour les fichiers de fuseaux horaires avec la publication de tzdata 2025a pour les changements de lois DST au Paraguay, plus des corrections historiques pour les Philippines.

Mise à jour

Toutes les publications de mises à jour de PostgreSQL sont cumulatives. Comme pour les autres mises à jour mineures, il n’est pas nécessaire d’extraire et de recharger les bases de données ni d’utiliser pg_upgrade pour appliquer cette mise à jour; il suffit simplement d’arrêter PostgreSQL et de mettre à jour les binaires.

Les utilisateurs ayant sauté une ou plusieurs mises à jour peuvent avoir besoin d’étapes additionnelles après la mise à jour. Les notes de publication des versions précédentes fournissent les détails.

Pour plus de détails, se référer à la note de publication de versions.

Liens

• Téléchargements
• Notes de version
• Page sur la sécurité
• Politique de version
• Faire un don

Si vous avez des corrections ou suggestions sur cette annonce de publication, merci de les envoyer à la mailing liste publique pgsql-www@lists.postgresql.org.

Le PGDG prévoit une livraison hors cycle pour le 20 février 2025 afin de corriger une régression introduite sur la mise à jour du 13 février 2025 portant sur les versions mineures: 17.3, 16.7, 15.11, 14.16 et 13.19. Dans cette mise à jour, vous retrouverez des correctifs pour les versions supportées (17.4, 16.8, 15.12, 14.17, 13.20). Bien que ces correctifs puissent ne pas impacter tous les utilisateurs de PostgreSQL, le PGDG a préféré adresser le problème au plus tôt et ne pas attendre la prochaine échéance prévue le 8 mai 2025.

Le correctif de sécurité CVE-2025-1094, traitant d’une vulnérabilité dans la librairie libpq de PostgreSQL, a introduit une régression portant sur la gestion des chaînes de caractères (“C string”) terminée par un caractère non nul. L’erreur pourrait être visible en fonction de comment un client PostgreSQL a implémenté ce comportement, et peut ne pas impacter tous les drivers PostgreSQL. Par précaution, le PGDG a avancé le cycle de mise à jour.

Si vous êtes impacté par ce problème, il est recommandé d’attendre la sortie des versions 17.4, 16.8, 15.12, 14.17 et 13.29 avant de mettre à jour PostgreSQL.

Supervision

L’outil pgwatch est l’un des outils les plus populaires pour la supervision des instances PostgreSQL. Le projet a été initié par la société Cybertec pour ses propres besoins. Initialement en version 2, pgwatch2 a été réécrit récemment en version 3. Pavlo Golub est en charge du projet chez Cybertec.

Cette version 3 propose les mêmes fonctionnalités éprouvées de la version 2 avec quelques nouveautés, notamment le stockage en parallèle vers plusieurs stockages, l’utilisation de l’API v3 de Etcd, factorisation du code et dépréciation de certains types de stockage (InfluxDB par exemple), mise à jour des versions de certains composants tels que Grafana ou les images Docker mis à disposition.

Pour rappel, pgwatch se base sur un collecteur écrit en go qui vient récupérer une liste de métriques prédéfinies, mais extensibles à souhait afin de récupérer des statistiques sur vos instances PostgreSQL et le système (moyennant l’utilisation de l’extension PL/Python) quelque soit leur nombre avec le minimum d’impact. Le stockage des métriques s’effectue sur PostgreSQL, mais il est aussi possible de choisir TimescaleDB, Prometheus ou des fichiers JSON.

Les tableaux de bord fournis sont utilisables dans l’outil Grafana, ce qui permet de les personnaliser finement, de créer des alertes, et de gérer finement les accès aux différents tableaux et données collectées.

pgwatch permet aussi de récupérer les statistiques des outils tels que PgBouncer, Patroni, Pgpool-II, Prometheus, parser les logs de PostgreSQL, ou de récupérer des statistiques depuis des services managés de PostreSQL chez divers fournisseurs de solution cloud (AWS, Azure, Google).

L’architecture de pgwatch est très modulaire et permet de s’adapter à votre infrastructure et à tous vos cas d’usage ou presque.

Quelques nouveautés

Remote Sinks

Une des nouveautés de la version 3 de pgwatch est le découplage de la partie stockage. L’introduction des remote sinks avec la possibilité d’utiliser l’interface (basée sur RPC) mise à disposition pour implémenter un type de stockage particulier pour y pousser les métriques de pgwatch.

Mais aussi, la possibilité de disposer en parallèle de plusieurs stockages des métriques, par exemple vers une base de données et un fichier JSON.

Etcd

Autre grosse nouveauté, c’est le passage à la version 3 de l’API de etcd.

En effet, jusqu’à la version 3.3 de etcd, l’API par défaut était la version 2. Le protocole de la version 2 n’étant pas compatible avec la version 3 de ce dernier. Lors de l’utilisation d’un cluster Patroni et de la découverte automatique des noeuds, pgwatch se base sur le protocole d'etcd.

Pour cette raison, pgwatch en version 3 utilise la version 3.5 de etcd afin d’utiliser le protocole version 3 de l’API etcd. Le protocole version 2 n’étant pas compatible avec le protocole en version 3, les clés/valeurs de la version 2 ne pourront être lues avec la version 3. Il faudra migrer en utilisant la procédure de migration donnée par etcd ici. Côté patroni, la migration repose sur le changement dans le fichier de configuration de la version de etcd en indiquant etcd3.

La documentation de pgwatch est disponible ici pour la version 3.

Migration vers pgwatch3

La question de la migration de pgwatch2 vers pgwatch3 se pose. Dans la documentation actuelle, rien n’y fait référence. Mais après discussion avec Pavlo Golub, il existe un utilitaire de conversion pour convertir des métriques personnalisées au format utilisé par pgwatch2 vers celui de pgwatch3.

Il est ainsi possible de récupérer vos anciennes métriques personnalisées pour les convertir dans le format utilisé par pgwatch avec un unique fichier .yaml:

Références

• Release notes
• Documentation
• GitHub
• etcd

L’écosystème PostgreSQL

Une collaboration entre ORANGE et LOXODATA a initié le projet libre pg-ecosystem. Ce projet se présente comme un catalogue des outils et extensions libres autour de PostgreSQL afin de répondre à deux questions:

• Quelles sont les possibilités d’industrialisation de ressources logiciels libres dans l’écosystème PostgreSQL?
• Comment limiter la dette technique ou la dépendance à un seul fournisseur?

De ces deux questions ont découlé les travaux autour d’une étude sur les outils et extensions libres. Nous avons défini des critères d’évaluation les plus objectifs possibles: vivacité du projet, perennité des contributions, adoption par la communauté…

Le projet s’est structuré en catalogue, proposant une fiche par projet (produit). Chaque fiche décrit le produit succinctement et inclut les attributs suivants:

• Catégorie
• Licence
• Documention
• Maturité
• Développement
• Compatibilité
• Déploiement

Les attributs du projet pourraient être amenés à évoluer, en fonction des retours que nous aurons.

Le projet pg-ecosystem est publié sous licence CC-BY-SA et est publié en anglais.

Le catalogue est rendu disponible via le portail pg-ecosystem. Les contributions sont acceptées et bienvenues sur Gitlab pg-ecosystem.

Le projet a été présenté à la dernière édition du Capitole du Libre 2024 dont vous pouvez retrouver la présentation: Construction d’un catalogue de l’écosystème Open Source de PostgreSQL.

La présentation de LOXODATA à l’OpenSource Experience 2024 est en ligne

Côté conférences, nous avons parlé de le sécurisation des accès aux bases de données, une présentation très rapide en 20 minutes d’un vaste sujet. Le support est disponible  Sécurisons PostgreSQL .

L’OSXP a publié les vidéos des conférences données pendant les deux jours.

Vous pouvez retrouver la captation de notre présentation «Sécurisons les accès à PostgreSQL» sur la chaîne Youtube de l’événement.

Ces deux dernières années, on aura noté l’engouement autour de l’IA, du machine learning et de son accessibilité via les plateformes telles que OpenAI. La communauté PostgreSQL s’est penchée dessus aussi afin de rendre PostgreSQL attractif comme moteur de bases de données vectorielles, grâce à son extensibilité, notamment depuis la sortie de l’extension pg_vector.

Introduction

L’extension pgvector créée par Andrew Kane, et initiée en avril 2021, vient de connaître un élan de popularité depuis deux ans, surtout à la vue de l’engouement des travaux autour de l’IA et du machine learning, et de fait des vecteurs.

Pourtant les principes mathématiques et les algorithmes utilisés dans le machine learning et l’IA existent depuis plusieurs décennies. Ce qui a changé c’est l’accessibilité de ces derniers et la mise à disposition des données issues de ces algorithmes très rapidement, ce qui implique de pouvoir stocker les données vectorielles au plus près des applications.

Pour revenir à pgvector, cette extension permet à PostgreSQL de stocker des données vectorielles grâce à un nouveau type de données spécialisé et indexable, et d’effectuer des recherches vectorielles de type K-NN (K nearest-neighbor) ou ANN (aproximate nearest-neighbor) en proposant désormais deux types d’index: IVFFlat et HNSW.

Jonathan Katz a écrit une série d’articles sur pgvector, auquel il contribue activement, et notamment cet article: Vectors are the new JSON in PostgreSQL où il y fait le parallèle entre le support du JSON par PostgreSQL et les vecteurs. Jonathan Katz nous rappelle comment PostgreSQL a fait le pari de supporter le format JSON/JSONB, à la base un format d’échange, pour répondre aux besoins des développeurs d’applications afin de stocker des données au format JSON et de pouvoir les requêter efficacement.

La communauté PostgreSQL continue d’ailleurs d’implémenter le standard SQL/JSON et pousse à son adoption. La version 17 de PostgreSQL fournit par exemple de nouvelles fonctions JSON et un nouveau constructeur JSON_TABLE().

Ce qui s’est passé pour PostgreSQL avec JSON est en train de se reproduire avec les vecteurs: le besoin de stocker et rechercher des vecteurs, donnant l’occasion à PostgreSQL de devenir une base de données vectorielles avec toutes les fonctionnalités que PostgreSQL offre déjà.

Extensibilité

Une des forces de PostgreSQL est son extensibilité éprouvée et permet d’ajouter de nouvelles fonctionnalités sans toucher au code principal. L’écosystème des extensions est immense. Vous pouvez retrouver une liste exhaustive des extensions sur PGXN. Parmi les plus connues, on peut citer pg_stat_statements pour surveiller les statistiques d’exécution des requêtes SQL, postgis pour gérer les données géospatiales ou encore timescaledb pour gérer les séries temporelles.

Parmi les vues disponibles sous PostgreSQL pour lister les extensions, vous pouvez utiliser les vues [pg_available_extensions](https://www.postgresql.org/docs/current/view-pg-available-extensions.html ou [pg_available_extension_versions](https://www.postgresql.org/docs/current/, ou la vue pg_extension.

Bases de données vectorielles

Avant de rentrer dans le détail de l’implémentation de pgvector, nous allons rappeler ce qu’on entend par bases de données vectorielles.

Une base de données vectorielles doit permettre de stocker des vecteurs et rechercher des similarités de manière efficace et précise dans un espace vectoriel à haute dimension, en offrant une indexation performante et des opérations de calcul de distance adaptés selon le cas d’usage envisagé.

Les cas d’usage de ces bases de données pouvant être les suivants:

• système de recommendation
• recherche d’images
• traitement naturel du langage
• détection d’anomalies
• bioinformatique
• chatbots (RAG)

Une base de données vectorielles doit également apporter les fonctionnalités attendues sur les bases de données relationnelles telles que les transactions, la sécurité, la scalabilité, la haute disponibilité et la recherche hybride.

Vecteurs

Les vecteurs produits par plongement vectorielle (embeddings) par les grands modèles de langage (LLM) sont des tableaux de données numériques (réels à virgule flottante, type real stocké sous 4 octects) permettant de représenter des données hétérogènes comme des images, des fichiers audios ou des textes.

Les vecteurs sont de dimension finie dont le nombre varie selon les modèles ayant produits ces derniers. Par exemple, le modèle text-embedding-3-small de chez OpenAI produit des vecteurs de 1356 dimensions, alors que le modèle text-embedding-0004 de chez Google est à 768 dimensions. Chaque modèle définit également les types d’opérations de distance acceptées.

pgvector

L’extension pgvector initié par Andrew Kane permet d’apporter à PostgreSQL le stockage et la recherche d’un nouveau type de données, le type vector à n dimensions. Il est possible d’utiliser la recherche exact et approximative (ANN).

Par défaut, le type vector utilise le type real sous quatre octets pour le stockage, mais il est possible de réduire la taille de stockage en utilisant des vecteurs de demi précision, des binaires ou le type sparse.

Pour comparer les vecteurs entre eux et faire des recherches de similarité, l’extension implémente plusieurs type d’opérations de distance en fonction de la nature des données et de l’objectif de la comparaison. On compte pas moins de six opérateurs de distance, les deux derniers opérateurs étant réservés au type vecteur binaire:

• <-> distance L2 ou Euclidienne (vector_l2_ops)
• <#> produit scalaire (vector_ip_ops)
• <=> distance cosinus (vector_cosine_ops)
• <+> distance L1 ou Manhattan (vector_l1_ops)
• <~> distance Hamming (bit_hamming_ops)
• <%> distance Jaccard (bit_jaccard_ops)

L’extension prend en charge également de nombreux langages existants pour les clients PostgreSQL.

Pour démarrer, créons une table avec une colonne de type vecteur (à trois dimensions pour l’exemple), insérons des données et effectuons une recherche de similarité de documents selon la distance Euclidienne (ou L2) avec l’opérateur <-> :

Indexation

L’extension propose deux types de vecteurs pour la recherche aproximative (ANN) : IVFFlat et HNSW, en plus de la recherche exacte (recherche séquentielle). Le choix de l’un des types d’index est un compromis à faire entre performance et rappel. La performance étant entendue aussi bien en termes de construction de l’index que de recherche via cet index. Le rappel mesure quant à lui le ratio entre les éléments pertinents retournés parmi tous les éléments pertinents. Il est aussi possible de jouer avec les différents paramètres des index afin de placer le curseur selon votre cas d’usage.

HNSW

Le premier index disponible est l’index HNSW, pour Hierarchical Navigable Small World. Cet index est basé sur un graphe multi-couches, chaque couche étant plus ou moins dense. Plus on descend dans les couches, plus il y a de vecteurs. Pour effectuer une recherche ANN, il suffira de traverser le graphe à la recherche de la plus courte distance entre les vecteurs cibles.

© Github @ skyzh

La création de l’index HNSW peut se faire sans aucune données présentes initialement. Le temps de construction de l’index sera plus long et utilisera plus de mémoire que l’index IFVFlat, mais sera plus performant lors des requêtes. Il faudra créer un index par type d’opérateur de distance.

L’index possède deux paramètres lors de la construction de ce dernier:

• m (par défaut à 16) nombre de connexions entre vecteurs, par couche
• ef_construction (par défaut à 64) nombre de vecteurs candidats par voisinage pour la construction du graphe

Par exemple:

En augmentant ces paramètres, il est possible d’améliorer la valeur de rappel mais au détriment du temps de construction de l’index et de la mémoire utilisée. Il est aussi possible d’affiner la recherche avec le paramètre ef_search, mais au détriment de la vitesse de requête:

• ef_search (défaut à 40) nombre de vecteurs candidats par voisinage pour requête

Il est possible également d’accélérer la création de l’index s’il peut contenir dans la taille définie par le paramètre maintenance_work_mem mais faire attention dans ce cas à ne pas consommer toute la mémoire disponible. On peut enfin rajouter des workers supplémentaires avec max_parallel_maintenance_workers et max_parallel_workers pour accélérer aussi la construction de l’index.

IVFFlat

Le deuxième index est l’index IVFFlat qui se base sur la création de liste (clusters) de vecteurs, et recherche un sous-ensemble de ces listes le plus proche du vecteur cible (avec l’algorithme K-means pour la recherche de centroïde). La construction de l’index ne peut se faire qu’après avoir inséré vos données. Cet index utilise moins de mémoire et est plus rapide à la construction que l’index HNSW mais est moins performant que ce dernier, en terme de vitesse et rappel.

© Github @ skyzh

Il est aussi important de noter que si la distribution des vecteurs change fréquemment, il sera nécessaire de reconstruire l’index (REINDEX CONCURRENTLY).

L’index IVFFlat dispose d’un paramètre pour la construction:

• lists nombre de listes

Plus le nombre de liste est grand, plus la durée de construction sera longue.

Pour la recherche, il est possible de modifier le paramètre:

• probes (1 par défaut) nombre de listes dans lesquelles rechercher

En augmentant le paramètre probes, il est possible d’améliorer le rappel au détriment de la performance. On peut rajouter aussi des workers supplémentaires avec max_parallel_maintenance_workers et maw_parallel_workers pour accélérer aussi la construction de l’index comme avec l’index HNSW.

Scan d’index itératif

La version 0.8.0 de pgvector a ajouté la possibilité d’activer le scan d’index itératif (désactivé par défaut). Avec cette fonctionnalité, PostgreSQL va scanner les index approximatifs à la recherche des plus proches voisins, appliquer ensuite les filtres additionnels (clause WHERE) puis scanner à nouveau les index si le nombre de voisins retournés n’est pas suffisant jusqu’à obtenir la valeur attendue.

Il est possible de l’activer pour chaque index, et choisir l’ordre du tri par rapport à la distance entre éléments:

Vous pouvez configurer le nombre d’éléments scannés pour chaque type d’index:

Quantification

Par défaut, pgvector stocke le type vecteur sous 32 bits, en nombre à virgule flottante. Depuis la version 0.7.0, il est possible d’optimiser le stockage des vecteurs et la performance des requêtes en réduisant le nombre de bits utilisés pour stocker les vecteurs, et ainsi réduire la taille des index et donc l’empreinte mémoire et disque. On peut désormais utiliser le type halfvec stocké sous 16 bits et le type binaire bit.

Par exemple:

Le type vecteur halfvec permet de stocker jusqu’à 4096 dimensions lorsque le type vecteur était limité à près de 2000 dimensions et le type binaire pouvant aller jusqu’à 64000 dimensions. L’inconvénient étant la réduction de l’information qui entraîne une diminution du rappel.

Conclusion

Comme vous venez de le voir, PostgreSQL grâce à son extensibilité permet d’adresser différents cas d’usage, et dans cet article l’extension pgvector en est la parfaite illustration tranformant PostgreSQL en une base de données vectorielles tout en fournissant les fonctionnalités de la base de données relationnelles éprouvées depuis de nombreuses années.

pgvector permet de répondre aux besoins issues autour de l’IA et sa popularité ne risque pas de retomber si l’on en croît le dépôt GitHub hébergeant le projet.

Références

• Extension pgvector
• Vectors are the new JSON in PostgreSQL
• Précision et rappel

Crédits photos: Jerry Kavan

Utilisation en base de données

Tests en base de données

Création d’une table simple contenant des documents en plusieurs langues

Pour commencer, nous allons créer une table qui va contenir des documents collectés sur le net:

La table contiendra simplement un indentifiant, l’url source du document, la langue du document, son titre et son contenu.

Programme python d’alimentation

Pour nous constituer un jeu d’essai, nous allons simplement “scrapper” des pages du site Wikipedia en anglais et en français. Ce script va chercher dans notre table une URL pour lequel le document est absent, va lire l’URL en question, ajouter dans la table les autres URLs présentes dans le document, puis enregister le contenu du document sous forme de texte brut.

Pour scrapper les sites respectivement en anglais et en français, le script se lance simplement:

Le script:

Après avoir touné “un certain temps”, nous avons un jeu d’essai correct pour nos tests:

Plus de 14.000 articles ont été chargés, dont 8.000 en français et 6.000 en anglais.

Première recherche simple

Dans cet exemple, je vais effectuer une recherche simple “thé & japonais”. Bien évidemment les résultats dépendent des pages qui auront été “scrappées” sur le wikipédia. La recherche s’effectue sur le contenu de la page. On affiche son titre et son URL. Mais surtout on utilise la fonction ts_rank() qui permet d’obtenir un score de correspondance. Zéro indiquant que le document ne correspond pas du tout, tandis que 1 indique que le document répond à 100%. Ceci permet de limiter la requête aux 10 documents les plus pertinents.

Notre recherche ne s’effectuant que sur le contenu de la page, il pourrait être pertinent d’ajouter le titre de la page au texte dans lequel on fait une recherche, au moins pour calculer le score:

On peut voir ainsi que le score de la page intitulée “Catégorie:Thé au Japon” a été légèrement amélioré, ce qui permet à ce document d’être classé avant la page générale “Thé”.

Le poids des mots… sans photo

PostgreSQL permet de donner plus d’importance (plus de poids) aux mots d’une certaine partie d’un document. Cela s’effectue avec la fonction setweight(ts_vector,poids). Le poids est une simple lettre A,B,C ou D (par ordre décroissant). Ce poids figure dans les éléments d’un ts_vector. Pour rappel, voici un ts_vector sans poids:

Voici ce même vecteur auquel on a attribué le poids ‘A’ :

Et voici maintenaant la concaténation de deux vecteurs de poids différents:

On peut donc effectuer notre test précédent en attribuant un poids supérieur au titre du document (on affiche cette fois 12 résultats au lieu de 10) :

Cela à permis de faire apparaître “Catégorie:Thé japonais” en quatrième position, tandis que l’article sur le Sencha est passé à la onzième place.

Extrait pertinent

Plutôt qu’afficher l’URL, il peut être intéressant d’afficher l’extraît de texte le plus pertinent du document. Cela peut se faire avec la fonction ts_headline que nous pouvons appliquer aux 5 premiers résultats de notre requête:

La taille du ou des extraits retournés est un paramètre de la fonction ts_headline() de même que les balises de mise en évidence (<b></b>). Attention, cette fonction est coûteuse en CPU, c’est pourquoi il est convenable d’être attentif à ne l’appliquer que sur un resultset déjà réduit au maximum.

Performances

Si vous avez essayé, de votre côté, de reproduire les exemples précédents au fur et à mesure de votre lecture, il est probable que vous éprouviez quelques inquiétudes quant aux performances du FTS. Le problème étant que la fonction to_tsvector() est “lente”. Si l’on souhaite effectuer des recherches avec des performances correctes, il est impératif de précalculer ces vecteurs de lexèmes.

On va donc ajouter une colonne de type tsvector à notre table:

Puis nous allons précalculer nos vecteurs conformément à la langue du document. Dans le cas d’une base de données en exploitation, il serait bien sûr beaucoup plus pertinent de mettre un trigger sur la table afin que le champ vector soit renseigné/mis à jour lors des INSERT ou UPDATE:

Si nous effectuons notre recherche originale sur le thé japonais avec un EXPLAIN ANALYZE successivement en calculant le vecteur puis en utilisant le champ vector qui vient d’être créé nous avons les résultats suivants:

Pour le moment, laissons de côté le temps d’éxécution qui est beaucoup lié à l’état des caches. On peut voir que le coût estimé par le planner est déjà divisé par 5 (34297.92 vs 169567.06) en utilisant notre champ précalculé.

On aurait pu être tenté de partitionner notre table pages sur la langue, mais l’intérêt serait limité puisqu’on peut également créer des index partiels. Pour rappel, si un tableau est indexé dans un index BTree, l’index permettra de trouver rapidement exactement ce tableau dans son intégralité. Tandis que des index GiN ou GiST permettront de retrouver les tableaux qui contiennent les éléments recherchés.

Nous allons donc indexer les éléments du champ vector pour chaque langue:

Le EXPLAIN ANALYZE de la requête de recherche devient ainsi:

Comme on peut le voir, cet index permet de diviser par douze le coût estimé par le planner.

Le cas de l’index GiST

Si on compare l’exécution avec un index GiST au lieu de GIN nous avons:

On peut voir que le coût estimé est supérieur avec un GiST qu’avec un GIN. Le point le plus intéressant c’est que le scan de l’index GiST a ramené plus de lignes (4540) que celui de l’index GIN lequel a ramené exactement le même nombre de lignes (3007) que la requête elle-même. Ce comportement était prévisible car si le GIN indexe des valeurs exactes, le GiST indexe lui des checksums de valeurs, ce qui entraîne de faux positifs et donc nécessite un filtrage après coup des lignes ramenées par le scan de l’index, et donc des lectures superflues, et donc forcément un coût supplémentaire. L’utilisation d’un index GiST peut cependant parfois se justifier, en particulier parce qu’il supporte l’inclusion de valeurs (CREATE INDEX ... INCLUDE (champ)). Donc si votre table de documents comporte d’autres champs quasi-systématiquement utilisés dans la clause WHERE (identifiant de l’auteur, droits d’accès etc.) l’utilisation du GiST peut être pertinente afin de pouvoir inclure cette ou ces donnée(s) à l’index.

Limites

Il faut savoir que lorsque des ts_vector sont indexés, seuls les lexèmes le sont. Le poids éventuellement associé à chaque lexème ne l’est pas. Cela ne pose en soit pas de problème, mais si vous avez précalculé votre champ vector en concaténant des fragments de texte de poids différents, ne soyez pas surpris d’avoir éventuellement 2 champs vector différents qui, selon l’index sont égaux.

Conclusion

Les capacités de recherches en texte intégral de PostgreSQL sont très largement méconnues, surtout en France, alors que quelques simples efforts de configurations permettraient dans bien des cas d’espèce de s’affranchir de coûteux moteurs spécialisés et propriétaires.

Configuration

Si la configuration par défaut de la recherche en texte intégral de PostgreSQL est assez efficace en anglais, il convient pour le français (ou l’espagnol) de compléter le paramétrage par défaut. Pour cela:

• Copier la configuration ‘french’ dans une nouvelle (french_custom par exemple).
• Installer l’extension unaccent.
• Installer éventuellement l’extension DICT_XSYN.
• Supprimer le mapping des éléments que l’on souhaite exclure de la recherche (emails, URLs, valeurs numériques…)
• Paramétrer un fichier .stop (non accentué) et attribuer ce fichier au dictionnaire french_stem.
• Paramétrer un fichier .syn ou .rules et créer le dictionnaire de synonymes.
• Modifier le mapping des éléments lexicographiques asciiword, hword, hword_part, word vers les dictionnaires unaccent, syn_fr, french_stem (unaccent est inutile pour asciiword).

Modélisation

• En plus du ou des champ(s) text contenant les textes sur lesquels seront effectués des recherches, il convient d’avoir un champ dans lequel est précalculé le ts_vector. Un trigger pour alimenter ce champ est recommandé.
• Si les documents sont suceptibles d’être dans plusieurs langues, un champ identifiant la langue du document est nécessaire.
• Un index GIN ou GiST doit être créé sur le ts_vector précalculé. Si la base est mutilingue, il est recommandé de faire un index séparé par langue. L’index GIN est recommandé, sauf s’il est pertinent, en terme de performances, d’inclure dans l’index des données annexes.

Maintenance

• Toute modification d’un dictionnaire (ajout de stop-words, de synonymes etc. ) ou modification de la configuration TEXT SEARCH impose évidemment de lancer un recalcul des champs ts_vector précalculés.
• La modification d’un dictionnaire peut être prise en compte immédiatement à l’aide d’un ALTER. Par exemple, dans le cas de la modification des stop-words:

Volumétries

Pour rappel, j’ai constitué pour rédiger cet article une base contenant 14.543 articles de wikipédia. Cela représente 493 Mo de texte brut:

Grace au mécanisme de compression intégré à pg_toast, ces textes n’occupent que 264 Mo dans la base:

Notez que j’ai utilisé la compression transparente par défaut pour les champs TOASTed PGLZ (champs TEXT et TS_VECTOR). Depuis la version 14 de PostgreSQL, la compression LZ4 est disponible, paramétrable champ par champ si besoin, laquelle permet une amélioration significative des performances au détriment d’une perte de compression infinitésimale. N’oubliez pas d’explorer ce point dans votre tuning.

Le champ ts_vector précalculé occupe 294 Mo:

Les index GIN occupent 122 Mo:

Il semble donc raisonnable de prévoir au moins 1,4 Mo d’espace de stockage pour chaque Mo de texte brut dans le cadre d’une base conçue pour la recherche en texte intégral.

Principes et configurations

Historique

En 2000, des développements pour PostgreSQL basés sur OpenFTS ont débuté. Ce projet était alors nommé Tsearch. En 2003 le projet est devenu Tsearch2, utilisant le nouveau type de données tsvector et les index GiN/GiST de PostgreSQL 8.2 ainsi que l’UTF8. La recherche en texte intégral (ou FTS pour Full-Text Search) était proposée dans une contribution séparée nommée tsearch2 et développée par Oleg Bartunov et Teodor Sigaev. La contribution a été pleinement intégrée dans PostgreSQL à partir de la version 8.3. L’objet de ce document est donc de présenter la recherche en plein texte disponible avec la distribution standard de PostgreSQL.

Principe général

Décomposition des documents

La recherche en texte intégral diffère de la simple recherche basée sur les chaînes de caractères. Il s’agit en effet d’effectuer une recherche sémantique et non pas une simple recherche d’expression régulière. PostgreSQL est doté d’outils puissants permettant de travailler sur les chaînes de caractères, évaluer la similarité entre deux chaînes, etc. On peut citer par exemple l’extension pg_trgm (trigrammes) particulièrement utile pour de telles recherches.

On peut résumer la recherche sémantique en quelques étapes simples (on nomme “document” le texte qui fera ultérieurement l’objet d’une recherche) :

• Décomposition du document en éléments lexicographiques simples (mots, nombres, adresses…);
• Factorisation des éléments lexicographiques simples en lexèmes;
• Transformation du document en un vecteur de lexèmes.

Voici un exemple simple de transformation d’un texte en vecteur de lexèmes en guise d’illustration:

On peut voir que le texte a été décomposé en 5 lexèmes, et que le lexème “love” est présent sur deux positions: 2 et 6. La fonction ts_debug() permet d’avoir plus de détails sur cette transformation:

La sortie de ts_debug() indique que les caractères virgule et dollar (",$") ont été considérés comme des espaces, «100» est bien détecté comme un entier non signé, et les mots «I,but,she,with,a» n’ont pas été convertis en lexèmes. Ces derniers sont en effet des «stop words», c’est-à-dire des mots trop courants pour être significatifs, ils sont donc éliminés du vecteur de lexèmes.

Requête de recherche

La requête de recherche va suivre le même principe: les éléments recherchés vont être décomposés en lexèmes, et PostgreSQL va chercher des éléments communs entre les deux vecteurs.

Comme vous l’aurez deviné, le & est un opérateur logique signifiant ET. Les différents opérateurs logiques pour une requête en plein texte sont:

• & ET logique
• | OU logique
• ! NON logique
• <-> Précédence

Et enfin, l’opérateur @@ teste la correspondance entre un vecteur et une requête.

On peut ainsi tester, par exemple, une requête shops & (banknotes | credit <-> card), c’est à dire «contient ‘shop’ ET (soit (‘banknotes’) soit (‘credit’ suivi de ‘card’)) » avec différentes phrases:

La requête retourne False pour la troisième phrase car le mot ‘card’ ne suit pas immédiatement ‘credit’, et la quatrième phrase retourne également False du fait de l’absence du lexème ‘shop’.

Il faut noter que les positions enregistrées dans le vecteur tiennent compte de la présence de stop words. Ainsi ‘credit for card’ ne correspondra pas à ‘credit <-> card’ bien que ‘for’ soit un stop word.

Configurations

Tout d’abord une précision: les configurations FTS se font base par base, elles ne sont pas globales au cluster (i.e. instance).

Différentes langues

Dans ces premiers exemples, nous avons utilisé uniquement l’anglais pour une raison très simple: PostgreSQL est livré bien configuré pour l’anglais. Cependant pour le français, même si le support est présent, quelques ajustements sont nécessaires.

Commençons par faire une copie de la configuration par défaut:

Nous pouvons examiner les différents éléments lexicographiques définis dans cette configuration:

Un premier test rapide nous montre une difficulté liée à la langue française: les accents.

Cela poserait un problème, par exemple, avec les participes passés, puisque “mange” et “mangé” seraient des lexèmes différents. On peut donc utiliser l’extension standard UNACCENT et l’ajouter à notre configuration:

Vérifions la prise en compte de unaccent sur notre configuration:

Nous pouvons faire un test rapide avec ts_debug() pour voir le comportement: