Dans cet article, nous allons regarder comment connecter PostgreSQL vers d’autres applications avec Debezium, dans le cadre d’un cluster Patroni assurant la haute disponibilité. Debezium repose sur un ensemble de services distribués conçus pour capturer les changements survenants dans vos bases de données afin que d’autres applications puissent consulter ces changements et les consommer, communément appelé CDC pour change data capture.

Debezium

Debezium propose de capturer les flux d’évènements depuis vos bases de données permettant aux applications de répondre à des changements survenant au niveau des lignes de tables sur vos bases de données.

Debezium repose sur le broker de messages Apache Kafka et fournit un ensemble de connecteurs sources pour Kafka Connect par type de sources, par exemple pour PostgreSQL. Le connecteur enregistre l’historique des changements sur une base de données en acquittant les changements au fil de l’eau et en sauvegardant chaque évènement dans un topic Kakfa (file de messages), et la position dans les journaux de transactions (WAL).

Le connecteur Debezium pour PostgreSQL se base sur le décodage logique pour récupérer les changements commités dans les journaux de transactions et un plugin de sortie pour mettre en forme les données.

PostgreSQL ne conserve pas tout l’historique des journaux de transactions, car les fichiers WAL sont recyclés quand nécessaire par PostgreSQL. Debezium doit donc gèrer lui même l’historique des lignes, et donc l’initialiser. Ceci se fait à la première connexion par une sauvegarde de l’état de la base de données (snapshot). Pour ce faire, le connecteur suit les étapes suivantes:

• Création d’une transaction avec un niveau d’isolation à SERIALIZABLE pour assurer la cohérence des données pour les lectures pendant cette transaction;
• Lecture de la position actuelle (LSN) du journal de transaction;
• Lecture des tables et schémas de la base de données et génération d’un évènement READ pour chaque ligne, chaque évènement est ensuite écrit dans un topic Kakfa correspondant à la table;
• Commit de la transaction;
• Enregistrement de l’état de la sauvegarde et la dernière position (LSN) lue;

Ensuite, le connecteur entre dans sa boucle d’envoi d’évènements consommés depuis PostgreSQL. Le connecteur convertit chaque changement en évènement create, update ou delete définit par Debezium, avec son LSN et les enregistre dans Kakfa Connect, qui à son tour les transmets à un topic Kakfa. Kakfa Connect stocke en parallèle le LSN actuel afin de repartir de ce point lors de l’arrêt de Kakfa Connect.

À l’image de cette première architecture basée sur Kakfa Connect et Apache Kakfa, il existe deux autres types d’architecture disponible:

• Un serveur Debezium, une application pour diffuser les changements de bases de données sources vers des infrastructures de messagerie de type Redis par exemple;
• Une librairie Debezium Engine intégrée directement dans votre application Java;

Mise en place avec PostgreSQL

Le connecteur Debezium pour PostgreSQL prend en charge deux plugins de sortie: pgoutput qui est le plugin par défaut utilisé avec la réplication logique ou decoderbufs basé sur le format Protobuf et maintenu par la communauté Debezium.

Pour cet exemple, nous utilisons le plugin decoderbufs. Nous ne détaillons pas la mise en place de Debezium et ses services associés, mais uniquement la mise en place côté PostgreSQL. Pour Debezium, il convient de se référer à la documentation.

Il faut tout d’abord installer le plugin decoderbufs sur votre serveur via le système de paquet approprié.

Sous RedHat, on peut par exemple utiliser la commande dnf pour installer le paquet pour la version 14 de Postgresql en utilisant le dépôt du PGDG:

On vérifie l’installation du paquet avec:

Puis on modifie la configuration de PostgreSQL via la commande patronictl edit-config pour charger le plugin et autoriser le décodage logique via le paramètre wal_level à logical.

Et on redémarre les instances pour prise en compte des paramètres avec la commande patronictl restart car Les paramètres wal_level et shared_preload_libraries exige un redémarrage de l’instance PostgreSQL.

Il peut être nécessaire d’affiner les paramètres suivants selon votre cas d’usage:

• max_wal_senders (défaut à 10) : doit être équivalent ou plus au nombre de slots de réplication, plus le nombre de réplicas présents;
• max_replication_slots (défaut à 10) : indiquant le nombre de slots de réplication maximum (réplication logique et physique). Doit être au moins égal au nombre de réplica et de souscription (pour chaque souscription, un apply worker et plusieurs tablesync worker);
• max_slot_wal_keep_size (défaut à -1) : permet d’indiquer la taille maximale de fichiers WAL pouvant être conservés par un slot de réplication;

Nous créons maintenant le rôle de réplication dédié au connecteur Debezium et ajoutons les permissions nécessaires.

On ajoute l’utilisateur créé dans le fichier .pgpass si ce dernier est utilisé.

Il faut ensuite adapter le fichier pg_hba.conf pour autoriser le connecteur Debezium à se connecter à l’instance PostgreSQL.

Nous créons à présent la publication portant sur tous les schémas de la base de données, et créons manuellement le slot de réplication. Il est possible de limiter les tables publiées dans la commande CREATE PUBLICATION ou de définir un schéma en particulier.

Nous mettons à jour la configuration de PostgreSQL via Patroni pour déclarer l’utilisation du slot de réplication logique et rechargeons la configuration.

Supervision

Une fois la configuration en place, il est nécessaire de surveiller la consommation des WAL et l’état des slots de réplication. En effet, si le connecteur venait à s’arrêter de consommer les données du slot de réplication, le paramètre confirmed_flush_lsn n’étant pas mis à jour, alors PostgreSQL va accumuler les fichiers WAL nécessaires au slot de réplication en attendant une reprise éventuelle. Ceci peut conduire à la saturation du système de fichiers et entraîner dans le pire des cas l’arrêt de PostgreSQL.

On peut utiliser des gardes fous comme le paramètre max_slot_wal_keep_size pour limiter la quantité de fichiers WAL conservée par un slot de réplication.

Pour surveiller l’activité des slots de réplication, nous utilisons la vue pg_replication_slots (ici en version 14 de PostgreSQL).

• active vaut true si le slot émet des données;
• catalog_xmin correspondant à la transaction la plus ancienne affectant le catalogue système et requis par le slot;
• restart_lsn la position du plus ancien WAL requis par le slot;
• confirmed_flush_lsn la dernière position reçue et rejouée côté consommateur;
• wal_status indique la disponibilité des fichiers WAL requis par le slot

On vérifie que les paramètres confirmed_flush_lsn et restart_lsn soient bien incrémentés.

On peut calculer en utilisant cette même vue la quantité de WAL retenue et le lag vers le connecteur.

• retained_walsize la quantité de WAL (en octet) retenue par le slot côté publication
• subscriber_lag le retard de réplication logique (en octet) entre la publication et la souscription.

Configuration du connecteur Debezium

Afin de configurer le connecteur Debezium source pour PostgreSQL, il est nécessaire de fournir au minimum les éléments suivants dans un fichier json que l’on peut nommer pg-example-connector.json:

La liste complète des paramètres de configuration du connecteur Debezium pour PostgreSQL est disponible dans la documentation de Debezium.

L'API REST de Kafka Connect permet d’enregistrer de nouveaux connecteurs et de vérifier le status de ces derniers. Une fois enregistré, le connecteur est actif. Côté Kakfa, il est possible d’utiliser l’utilitaire bin/kafka-console-consumer.sh pour s’abonner à un topic donné et visualiser les messages reçus depuis le connecteur PostgreSQL.

Pour le reste des opérations de Apache Kafka, je vous renvoie à la documentation complète de ce dernier.

Crédits photo: Callum Blacoe

Le PGDG (PostgreSQL Global Development Group) a publié une mise à jour de toutes les versions supportées de PostgreSQL, incluant 17.5, 16.9, 15.13, 14.18 et 13.21.

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

Fin du support de la version 13 de PostgreSQL

La version 13 de PostgreSQL ne recevra plus de correctifs à partir du 13 novembre 2025. Il est donc recommandé de mettre à jour vers une version majeure vos instances en production. Se référer à la note de versions pour plus d’informations.

Problèmes de sécurité

• CVE-2025-4207:

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

Lors de l’utilisation de l’encodage GB18030 (encodage officiel chinois), il est possible à un attaquant d’effectuer une lecture au-delà du tampon de validation de cet encodage. Cette lecture provoque un arrêt du processus et induit un déni de service temporaire. Cette vulnérabilité concerne le serveur de bases de données PotgreSQL et la bibliothèque libpq. Les versions antérieures à PostgreSQL 17.5, 16.9, 15.13, 14.18, et 13.21 sont affectées.

Corrections de bogues et améliorations

Cette mise à jour corrige plus de 60 bogues ayant été signalé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:

• gestion correcte des clés étrangères autoréférentielles sur les tables partitionnées. La création ou l’attachement d’une partition ne créait pas les entrées de catalogue requises pour une contrainte de clé étrangère si la table référencée par la contrainte était la même table partitionnée. Il en résultait que la contrainte pouvait ne pas être respectée. Pour corriger ce problème, veuillez consulter les instructions de la section «Mise à jour» ;
• correction d’un problème de perte de données lors de l’utilisation d’index BRIN bloom (en utilisant par exemple la classe d’opérateur date_bloom_ops) ;
• correction de MERGE dans une table partitionnée avec des actions DO NOTHING;
• prévention de l’échec des commandes INSERT lorsque la table possède une colonne GENERATED d’un type de domaine et que les contraintes du domaine interdisent les valeurs NULL;
• correction de la commande ALTER TABLE ... ADD COLUMN pour gérer correctement le cas d’un type de domaine qui a sa propre valeur par défaut et que la valeur DEFAULT pour la colonne n’est pas définie.
• correction de problèmes lors de conversion de types dans les clés des expressions de constructeurs JSON;
• correction de XMLSERIALIZE() pour que l’option INDENT soit correctement supprimée lorsqu’elle est présente dans les vues ou les règles. Ceci était perceptible lors de restaurations;
• corrections pour le planificateur de requêtes, évitant l’évaluation prématurée des arguments dans une fonction d’agrégation ayant à la fois des clauses FILTER et ORDER BY ou DISTINCT qui pourraient conduire à des échecs;
• correction sur le retour de résultats incorrects lors d’un bitmap scan sans colonnes de sortie qui est exécuté alors que vacuum est également en cours d’exécution sur la même table;
• correction des problèmes de performance dans le démarrage de la recherche d’index GIN lorsqu’il y a beaucoup de clés de recherche, par exemple, jsonbcol?| array[...] avec des dizaines de milliers d’éléments dans la liste;
• s’assurer que les statistiques d’E/S des WAL senders actifs soient rapportées dans un délai maximum d’une seconde;
• correction d’une condition de concurrence dans la gestion de synchronous_standby_names immédiatement après le démarrage, où un backend pourrait ne pas attendre un commit synchrone;
• éviter une boucle infinie si scram_iterations est fixé à INT_MAX.
• corrections pour la réplication logique, y compris la gestion de vacuum autour des lignes supprimées qui sont toujours nécessaires pour le décodage logique;
• prévention des pertes de données potentielles lorsque des opérations de modification de schéma (DDL) qui ne prennent pas de verrous forts affectent des tables qui sont répliquées logiquement;
• prévenir les problèmes dans la réplication logique qui pourraient permettre l’enregistrement de données dupliquées en raison de la gestion des erreurs du apply worker;
• amélioration de la manière dont reindexdb gère la planification des opérations de réindexation parallèle afin d’obtenir le niveau de parallélisme attendu;

Cette version met également à jour les fichiers de données de fuseaux horaires avec la version 2025b de tzdata pour les changements de loi de l’heure d’été au Chili, ainsi que des corrections historiques pour l’Iran. En outre, un nouveau fuseau horaire America/Coyhaique a été créé pour la région d’Aysén au Chili, afin de tenir compte du fait qu’elle passe à UTC-03 tout au long de l’année, ce qui diverge d'America/Santiago.

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.

Cette version 18 de PostgreSQL promet de nombreuses améliorations de performances avec notamment le sous-système d’entrées/sorties asynchrones pour les lectures, la conservation des statistiques lors d’une mise à jour de version majeure, l’ajout du support de l’authentification OAuth 2.0 et la dépréciation de md5, l’ajout de nombreuses statistiques par défaut lors d’un EXPLAIN ANALYZE et d’autres fonctionnalités encore autour de la réplication logique.

Vous pouvez retrouver la documentation pour la version 18 et les notes de publications.

PostgreSQL 18 Bêta 1 publiée

Le PostgreSQL Global Development Group annonce la disponibilité de la première bêta de PostgreSQL 18 en téléchargement. Cette publication contient un aperçu des fonctionnalités qui seront disponibles dans la version finale de PostgreSQL 18. Des modifications peuvent toutefois intervenir d’ici là.

Vous pouvez trouver des informations sur toutes les fonctionnalités et les changements de PostgreSQL 18 dans les notes de version

Dans l’esprit de la communauté open source PostgreSQL, nous vous encourageons fortement à tester les nouvelles fonctionnalités de PostgreSQL dans vos systèmes de base de données. Ceci afin de nous aider à éliminer les bogues et autres problèmes qui pourraient exister. Bien que nous ne vous conseillons pas de faire fonctionner PostgreSQL 18 Bêta 1 dans vos environnements de production, nous vous encourageons à trouver des moyens de faire fonctionner votre charge applicative typique avec cette publication bêta.

Vos tests et vos commentaires aideront la communauté à s’assurer que PostgreSQL 18 respecte nos standards de stabilité et fiablité. Vous pouvez également vous renseigner sur notre processus de beta testing et comment y contribuer.

Principales fonctionnalités de PostgreSQL 18

Vous trouverez ci-dessous les principales fonctionnalités planifiées pour PostgreSQL 18. Cette liste n’est pas exhaustive: pour la liste complète des fonctionnalités, se référer à la note de publication.

Optimisation des performances

PostgreSQL 18 introduit un sous-système d’entrées/sorties asynchrones (AIO). Ce nouveau sous-système permet d’augmenter le débit des E/S et de masquer leur latence. Sous Linux, io_uring peut être utilisé pour les AIO, une implantation basée sur un worker est disponible pour toutes les plateformes. Cette version initiale prend en charge les lectures du système de fichiers telles que les lectures séquentielles, les bitmap heap scan et les vacuum, avec des tests montrant une amélioration des performances de 2 à 3 fois plus rapides.

Ces gains de performance s’étendent aux optimisations des requêtes et aux nouvelles fonctionnalités d’indexation. PostgreSQL 18 ajoute le support de l’utilisation des recherches skip scan sur les index multicolonnes B-tree, ce qui peut résulter en des temps d’exécution plus rapides pour les requêtes qui omettent une condition = sur un ou plusieurs préfixe de colonnes de l’index. Cette version inclut également des optimisations pour les clauses WHERE qui contiennent des instructions OR et IN afin de mieux utiliser les récentes améliorations d’indexation, ce qui peut également améliorer les performances des requêtes. Il y a aussi de nombreuses améliorations de performance dans la façon dont PostgreSQL planifie et exécute les jointures de tables, depuis l’amélioration de la performance globale des jointures de type hash jusqu’à la possibilité pour les jointures de type merge d’utiliser des tris incrémentaux.

Il existe une variété d’autres fonctionnalités de PostgreSQL 18 qui améliorent les performances pour d’autres opérations de requête et de maintenance. PostgreSQL 18 supporte maintenant les créations parallèles pour les index GIN, qui sont couramment utilisés pour la recherche sur les données JSON et plein texte. Cette version permet également de définir des clés de partition et des vues matérialisées avec des index uniques qui ne sont pas de type B-tree. PostgreSQL 18 améliore également les performances globales de verrouillage pour les requêtes qui accèdent à de nombreuses relations, et ajoute plusieurs améliorations aux requêtes sur les tables partitionnées, y compris un meilleur support du pruning et des jointures. PostgreSQL 18 a également amélioré les performances du traitement de texte, y compris l’accélération des fonctions upper/lower et une nouvelle collation intégrée PG_UNICODE_FAST.

Expérience des mises à jours de versions majeures

Avant PostgreSQL 18, une étape importante après avoir effectué une mise à jour majeure était d’exécuter la commande ANALYZE pour générer des statistiques, ce qui est un composant critique pour aider PostgreSQL à sélectionner le plan de requête le plus efficace. En fonction de la taille et de l’activité globale d’un cluster PostgreSQL, ce processus pouvait prendre beaucoup de temps et avoir un impact potentiel sur les performances des requêtes jusqu’à ce que le processus soit terminé. PostgreSQL 18 introduit la possibilité de conserver les statistiques du planificateur lors d’une mise à jour majeure, ce qui permet à un cluster mis à jour d’atteindre ses performances attendues plus rapidement une fois qu’il est disponible.

De plus, pg_upgrade, l’utilitaire utilisé pour faciliter la mise à jour d’une version majeure, a ajouté plusieurs améliorations de performance pour aider à accélérer les mises à jour avec de nombreux objets, tels que les tables et les séquences. Cette version permet également à pg_upgrade de faire ses vérifications en parallèle en fonction des paramètres de l’option --jobs, et ajoute également l’option --swap, qui échange les répertoires de mise à niveau au lieu de copier, cloner ou lier des fichiers.

Expérience développeur

PostgreSQL 18 introduit les colonnes virtuelles générées qui calculent les valeurs des colonnes pendant l’exécution de la requête, au lieu de les stocker. C’est maintenant l’option par défaut pour les colonnes générées. De plus, les colonnes générées stockées peuvent maintenant être répliquées logiquement.

Cette version ajoute la possibilité d’accéder à la fois aux valeurs précédentes (OLD) et actuelles (NEW) dans la clause RETURNING pour les commandes INSERT, UPDATE, DELETE et MERGE. De plus, PostgreSQL 18 ajoute le support de la génération d'UUIDv7 à travers la fonction uuidv7(), permettant la génération des UUIDs aléatoires qui sont ordonnés en fonction de l’horodatage pour supporter de meilleures stratégies de cache (cette version ajoute également uuidv4() comme alias pour gen_rand_uuuid).

Maintenant, avec PostgreSQL 18, vous pouvez faire des comparaisons LIKE sur du texte qui utilise une collation non déterministe, ce qui rend plus simple la recherche de motifs complexes. De plus, cette version introduit CASEFOLD pour faciliter les correspondances insensibles à la casse.

Cette version ajoute également des contraintes temporelles, ou des contraintes sur des plages, pour les contraintes PRIMARY KEY et UNIQUE à l’aide de la clause WITHOUT OVERLAPS, et pour les contraintes FOREIGN KEY à l’aide de la clause PERIOD.

Sécurité

PostgreSQL 18 introduit l’authentification oauth, qui permet aux utilisateurs de s’authentifier en utilisant les mécanismes OAuth 2.0 supportés par l’utilisation d’extensions PostgreSQL. De plus, PostgreSQL 18 ajoute plusieurs fonctionnalités pour valider et renforcer le comportement du mode FIPS, et ajoute également ssl_tls13_ciphers pour permettre aux utilisateurs de configurer les suites de chiffrement TLS v1.3 que le serveur peut utiliser.

Cette version déprécie l’authentification par mot de passe md5 en faveur de l’utilisation de l’authentification SCRAM qui a été ajoutée pour la première fois dans PostgreSQL 10. L’authentification md5 sera complètement supprimée dans une prochaine version majeure. De plus, PostgreSQL 18 ajoute le support de l’authentification SCRAM passthrough avec postgres_fdw et dblink lors de l’authentification d’instances PostgreSQL distantes.

Supervision

PostgreSQL 18 ajoute plus de détails à l’utilitaire EXPLAIN, qui fournit des informations sur l’exécution du plan de requête, et depuis cette version montre automatiquement combien de buffers (l’unité fondamentale de stockage des données, par défaut 8KB) sont accédés lors de l’exécution d'EXPLAIN ANALYZE. En outre, EXPLAIN ANALYZE indique désormais le nombre de lectures d’index au cours d’un scan d’index, et EXPLAIN ANALYZE VERBOSE inclut des statistiques sur l’utilisation CPU, les WAL et la lecture moyenne. Cette version inclut également des informations sur le temps total passé en vacuum et analyse de table dans pg_stat_all_tables, et montre maintenant des statistiques par connexion sur l’utilisation des E/S et des WAL.

PostgreSQL 18 fournit également plus d’informations sur les conflits d’écriture qui se produisent durant la réplication logique, et affiche ces informations à la fois dans les journaux et dans la vue pg_stat_subscription_stats.

Autres fonctionnalités

Depuis PostgreSQL 18, les sommes de contrôle des données, qui sont utilisées pour valider l’intégrité des données stockées, sont maintenant activées par défaut sur les nouveaux clusters PostgreSQL. Vous pouvez choisir de désactiver ce comportement en utilisant la commande initdb --no-data-checksums. Notez que cela peut nécessiter des changements dans vos scripts de mise à jour.

De plus, de nouveaux comportements sont disponibles dans plusieurs fonctionnalités de contraintes. Tout d’abord, les contraintes de clé étrangère et de vérification peuvent être définies comme NOT ENFORCED et, inversement, rendues exécutoires. De plus, les contraintes NOT NULL préservent maintenant leurs noms comme l’exige le standard SQL, supportent les clauses NOT VALID et NO INHERIT, et se comportent maintenant de manière plus cohérente avec l’héritage.

pg_createsubscriber supporte maintenant le drapeau --all pour que vous puissiez créer des réplicas logiques pour toutes les bases de données d’une instance avec une seule commande. De plus, PostgreSQL 18 vous permet de créer la définition du schéma d’une table étrangère en utilisant la définition d’une table locale en utilisant la commande CREATE FOREIGN TABLE ... LIKE.

PostgreSQL 18 introduit également une nouvelle version (3.2) du protocole de communication, qui est la première nouvelle version du protocole depuis PostgreSQL 7.4 (2003). libpq utilise toujours la version 3.0 par défaut en attendant que les clients (par exemple, les pilotes, les poolers, les proxies) ajoutent le support de la nouvelle version du protocole.

Fonctionnalités supplémentaires

De nombreuses autres fonctionnalités et améliorations ont été ajoutées à PostgreSQL. En fonction des cas d’usages, leur importance peut paraître plus ou moins grande que celles mentionnées ci-dessus.

Vous pouvez consulter les notes de publications pour une liste complète des nouveautés et changements: https://www.postgresql.org/docs/18/release-18.html

Tests pour le débogage et la compatibilité

La stabilité de chaque publication de PostgreSQL dépend de vous, la communauté. En testant la version à venir avec votre charge et vos outils de tests, vous pourrez nous aider à trouver les bogues et régressions avant la publication de PostgreSQL 18.

Étant donné qu’il s’agit d’une version bêta, des changements mineurs dans le comportement de la base de données, des détails et des APIs sont toujours possibles. Vos retours et tests aideront à déterminer les ajustements finaux des nouvelles fonctionnalités.

La qualité des tests aide à déterminer le moment de la publication finale.

Une liste des problèmes ouverts est publiquement disponible dans le wiki de PostgreSQL. Vous pouvez rapporter des bogues en utilisant le formulaire présent sur le site web de PostgreSQL: https://www.postgresql.org/account/submitbug/.

Planning Bêta

Il s’agit de la première publication bêta de la version 18. Le projet PostgreSQL publiera autant de bêtas que cela est nécessaire pour tester. Celles-ci seront suivies par une ou plusieurs publications de versions candidates, jusqu’à la publication de la version finale vers septembre/octobre 2025.

Pour plus d’information, veuillez consulter la page Beta Testing.

Liens

• Téléchargement
• Information sur le Bêta Testing
• Note de publication de PostgreSQL 18 Bêta
• Problème connu de PostgreSQL 18
• Soumettre un bogue
• Faire un don

Crédits photo: Carlos Gonzalez

Le programme 2025

Pour sa quatrième année, l’évènement virtuel POSETTE revient du 10 au 12 juin 2025.

Cette conférence virtuelle et gratuite proposera durant ces trois jours 42 présentations de 45 orateurs différents, réparties en quatres diffusions en direct. Il sera possible de rejoindre un serveur Discord afin de poser des questions aux orateurs et de discuter avec les autres participants.

Le programme complet est disponible sur le site de la conférence et le planning des diffusions:

• Livestream 1
• Livestream 2
• Livestream 3
• Livestream 4

Cette année, la conférence proposera une keynote de Bruce Momjian autour de l’IA et des bases de données et de Charles Feddersen sur les contributions de Microsoft pour PostgreSQL sur l’année 2025.

Crédits photo: Microsoft

Le 20 mars 2025 s’est tenue la 9e édition du pgDay Paris 2025. Cette édition s’est déroulée au sein de l’espace Saint Martin, dans le 3e arrondissement de Paris, qui dispose d’un bel auditorium.

LOXODATA était, cette année encore, sponsor «partner» de l’évènement. Nous avions un stand, ce qui nous a permis des échanges intéressants avec les auditeurs de cette édition.

Cette année encore, 2 salles de conférences étaient dédiées à l’événement. La seconde était majoritairement attribuée à des conférences courtes faites par des sponsors.

Le programme complet est sur le site du pgDay Paris 2025

Les conférences

Le programme de la journée de conférences comportait onze sessions en anglais, et une en français, réparties entre l’auditorium et une autre salle plus petite. S’ajoutait également une session de conférences éclair. Parmi toutes ces conférences, on pouvait retrouver plusieurs présentations non techniques.

Tuning community parameters: Hosting PostgreSQL events that perform

Après l’introduction faite par Vik Fearing, la keynote d’ouverture s’est déroulée dans l’auditorium.

Ellyne Phneah nous présenta les clés de l’organisation d’évènements communautaires PostgreSQL, en s’appuyant sur l’expérience du M-PUG (Malmö PostgreSQL User Group) et des conférences PostgreSQL. Une première partie insiste sur l’importance des communautés et des évènements PostgreSQL, et comment les faire reconnaître officiellement par la communauté. Puis, elle nous exposa l’importance dans la sélection des sujets, les formats engageants, la logistique et l’implication d’orateurs.
Elle nous donna également des astuces sur la manière de maximiser l’engagement, mesurer le succès et maintenir l’élan après l’événement, mais aussi quelques astuces pour gérer des problèmes de dernière minute. Que vous lanciez un groupe ou revitalisiez un meetup, cette conférence vous donne des stratégies concrètes pour faire vivre le groupe.

Professional PostgreSQL monitoring made easy

Après cette conférence, deux choix se présentaient.
Dans l’auditorum où Pavlo Golub présenta d’abord tous les niveaux pertinents de surveillance des bases de données, puis se concentra sur PostgreSQL et les moyens qu’il fournit.
Il présenta pgwatch de Cybertec comme une solution Open Source pour une surveillance simplifiée et complète et il aborda également des sujets avancés, comme la détection d’anomalies et les alertes.

Maintenir un inventaire de l’écosystème communautaire autour de PostgreSQL

Dans le même temps, dans la salle Karnak, Jérôme Desroziers (Orange France) et Stéphane Schildknecht (LOXODATA) présentèrent en français comment «maintenir un inventaire de l’écosystème communautaire autour de PostgreSQL». Cette présentation avait pour but d’introduire un nouveau projet libre de catalogue des outils et extensions open source autour de PostgreSQL.
Le projet est d’ores et déjà utilisable sur https://pg-ecosystem.gitlab.io/pg-ecosystem/.

Full-Text-Search Explained from A to Z with French Food

Avant la pause déjeuner, Matt Cornillon nous ouvrit l’appétit avec une conférence sur le full text search sur le thème de la nourriture française.
Cette présentation était décomposée comme un repas traditionnel, avec comme apéritif une introduction aux bases de la recherche plein texte – qu’est-ce que c’est et pourquoi l’utiliser.
Une entrée sur l’exploration des concepts clés comme tsvector, tsquery et le classement des résultats.
En plat de résistance, une analyse des mécanismes de stemming, de lexèmes et du parsing des documents.
En dessert, techniques avancées – ajustement des poids, dictionnaires de synonymes et configurations personnalisées.
Et pour terminer, une fusion de la recherche plein texte traditionnelle avec la recherche sémantique pour une approche hybride combinant recherche plein texte et compréhension contextuelle.
Pendant ce temps deux conférences se sont succédées dans l’autre salle.

Dear Azure Database for PostgreSQL, can you automate my index?

Tout d’abord Emmanuel Deletang nous à présenté une fonctionnalité d’«Azure Database for PostgreSQL - Flexible Server» qui facilite l’optimisation des index. Cet assistant intégré surveille, analyse et ajuste en continu les index pour améliorer les performances.

Distributed vector database with aidb and PGD

Nous n’avons malheureusement pas assisté à cette conférence. C’est pourtant toujours un plaisir de voir Boriss Mejías, qui est un excellent orateur.

Lightning Talks!

Après la pause méridienne, ce fut le moment des conférences éclair, dans l’auditorium. 7 personnes se sont succédées pour nous présenter 6 sujets, chacun en moins de 5 minutes chrono. L’animation était réalisée par Magnus Hagander.

• Devrim Gündüz a débuté la session en nous parlant de façon humoristique du stress causé par la création et la livraison des paquets RedHat/Centos de PostgreSQL, ainsi que de sa manière de gérer cela. #🍺
• Reda Agaoua nous a parlé de Transparent Data Encryption, une fonctionnalité qui est souvent réclamée pour garantir à elle seule un niveau de sécurité, mais qui n’est pas magique.
• Martín Marqués a enchaîné sur 3 erreurs basiques à absolument éviter dans la mise en place de politiques de sauvegarde.
• Floor Drees et Boriss Mejías nous ont parlé de Karaoké et de possibilité de choisir des chansons par similitude avec la chanson courante, avec une invitation à venir constater cela le soir même.
• Xavier Fischer nous a ensuite présenté une manière d’utiliser wireshark pour capturer les échanges du protocole postgres et le représenter sous un format graphique avec latex.
• Enfin, Pavlo Golub nous a fait jouer à une version de wordle spécialisée sur certains mots clés de PostgreSQL, en nous proposant des mots à trouver et en attendant des propositions de la salle.

Why PostgreSQL people should really care about Kafka and Debezium?

Après ce moment un peu plus léger, une autre conférence par Dirk Krautschick nous a intéressés à Kafka et Debezium. Le constat de départ est que le métier de DBA change, et désormais, beaucoup de choses se basent sur des flux.
Le Change Data Capture transforme les données en flux, qui passent par Kafka avec un plugin Debezium. Les différents outils ont été présentés et des démonstrations ont été faites du fonctionnement de chacun.

Waiting for PostgreSQL 18

En parallèle dans le deuxième salle, Frederic Delacourt donnait des détails pour vous aider à prévoir les migrations et anticiper ce qui se passera à l’avenir.
La session se termina par une brève discussion sur les fonctionnalités qui enthousiasment le plus Data Bene, ainsi qu’un aperçu des extensions en cours de développement qui sont planifiées pour une sortie en même temps que PostgreSQL 18, en tenant compte des changements à venir.

PostgreSQL and NodeJs as a Core Feature of Backend for Frontend

Viktoriia Hrechukha nous a présenté 2 dérivés de nodejs, et la manière de se connecter à PostgreSQL depuis nextjs. Dans l’optique d’un front end, les différentes manières de rendre une page ont été évoquées (SSR, CSR, SSG, ISR, mixte).

My Journey in PostgreSQL bug fixing

Bertrand Drouvot nous à partagé son expérience sur quelques corrections de bogues et a approfondi la façon de les reproduire, la tentative initiale de correction et le correctif final.

Explore the role of PostgreSQL in GitLab ecosystem

Hana Litavská, chercheuse doctorante à l’université de Pragues, s’est intéressée à la manière dont PostgreSQL est utilisé avec GitLab. En utilisant une méthode de reverse engineering, elle a cherché les stratégies et techniques utilisées par GitLab pour optimiser ses interactions avec la base de données. GitLab n’est pas un sujet très simple puisqu’il existe plusieurs types d’environnements possibles selon les clients.

Puis, Vik Fearing conclua la journée avec le mot de la fin.

Conclusion

Ce pgday Paris a fait la place à des conférences plus accessibles et moins centrées sur PostgreSQL, explorant des méthodes de travail et des outils liés à PostgreSQL. Le public était un peu moins nombreux que les années précédentes, et le passage à deux salles de conférences de l’année dernière différait légèrement cette année, puisque la deuxième salle était majoritairement occupée par les sponsors.

Espérons que l’année prochaine, le dixième pgday Paris aura bien lieu et rassemblera de nouveau un public nombreux. Nous espérons vous y retrouver!

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: