Développer avec PostgreSQL

12 mars 2025

Dalibo SCOP

Sur ce document

Formation Formation DEVPG
Titre Développer avec PostgreSQL
Révision 25.03
ISBN N/A
PDF https://dali.bo/devpg_pdf
EPUB https://dali.bo/devpg_epub
HTML https://dali.bo/devpg_html
Slides https://dali.bo/devpg_slides

Vous trouverez en ligne les différentes versions complètes de ce document. Les solutions de TP ne figurent pas forcément dans la version imprimée, mais sont dans les versions numériques (PDF ou HTML).


Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Alexandre Anriot, Jean‑Paul Argudo, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Ronan Dunklau, Vik Fearing, Stefan Fercot, Dimitri Fontaine, Pierre Giraud, Nicolas Gollet, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Jehan-Guillaume de Rorthais, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Arnaud de Vathaire, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

PostgreSQL : historique & communauté

PostgreSQL

Préambule

  • Quelle histoire !
    • parmi les plus vieux logiciels libres
    • et les plus sophistiqués
  • Souvent cité comme exemple
    • qualité du code
    • indépendance des développeurs
    • réactivité de la communauté

L’histoire de PostgreSQL est longue, riche et passionnante. Au côté des projets libres Apache et Linux, PostgreSQL est l’un des plus vieux logiciels libres en activité et fait partie des SGBD les plus sophistiqués à l’heure actuelle.

Au sein des différentes communautés libres, PostgreSQL est souvent cité comme exemple à différents niveaux :

  • qualité du code ;
  • indépendance des développeurs et gouvernance du projet ;
  • réactivité de la communauté ;
  • stabilité et puissance du logiciel.

Tous ces atouts font que PostgreSQL est désormais reconnu et adopté par des milliers de grandes sociétés de par le monde.


Au menu

  • Origines et historique du projet
  • Versions et feuille de route
  • Projets satellites
  • Sponsors et références
  • La communauté

Cette première partie est un tour d’horizon pour découvrir les multiples facettes du système de gestion de base de données libre PostgreSQL.

Les deux premières parties expliquent la genèse du projet et détaillent les différences entre les versions successives du logiciel. PostgreSQL est un des plus vieux logiciels libres ! Comprendre son histoire permet de mieux réaliser le chemin parcouru et les raisons de son succès.

Nous verrons ensuite certains projets satellites et nous listerons plusieurs utilisateurs renommés et cas d’utilisations remarquables.

Enfin, nous terminerons par une découverte de la communauté.


Un peu d’histoire…

  • La licence
  • L’origine du nom
  • Les origines du projet
  • Les principes

Licence

  • Licence PostgreSQL
  • Droit, sans coûts de licence, de :
    • utiliser, copier, modifier, distribuer (et même revendre)
  • Reconnue par l’Open Source Initiative
  • Utilisée par un grand nombre de projets de l’écosystème

PostgreSQL est distribué sous une licence spécifique, la licence PostgreSQL, combinant la licence BSD et la licence MIT. Elle est reconnue comme une licence libre par l’Open Source Initiative.

Cette licence vous donne le droit de distribuer PostgreSQL, de l’installer, de le modifier… et même de le vendre. Certaines sociétés, comme EnterpriseDB et PostgresPro, produisent leur version propriétaire de PostgreSQL de cette façon.

PostgreSQL n’est pas pour autant complètement gratuit : il peut y avoir des frais et du temps de formation, des projets de migration depuis d’autres bases, ou d’intégration des différents outils périphériques indispensables en production.

Cette licence a ensuite été reprise par de nombreux projets de la communauté : pgAdmin, pgCluu, pgstat, etc.


PostgreSQL ?!?!

  • 1985 : Michael Stonebraker recode Ingres
  • post « ingres » postingres postgres
  • postgres PostgreSQL

PostgreSQL a une origine universitaire.

L’origine du nom PostgreSQL remonte au système de gestion de base de données Ingres, développé à l’université de Berkeley par Michael Stonebraker. En 1985, il prend la décision de reprendre le développement à partir de zéro et nomme ce nouveau logiciel Postgres, comme raccourci de post-Ingres.

En 1995, avec l’ajout du support du langage SQL, Postgres fut renommé Postgres95 puis PostgreSQL.

Aujourd’hui, le nom officiel est « PostgreSQL » (prononcé « post - gresse - Q - L »). Cependant, le nom « Postgres » reste accepté.

Pour aller plus loin :


Principes fondateurs

  • Sécurité des données (ACID)
  • Respect des normes (ISO SQL)
  • Portabilité
  • Fonctionnalités intéressant le plus grand nombre
  • Performances
    • si pas de péril pour les données
  • Simplicité du code
  • Documentation
  • Tests fonctionnels

Depuis son origine, PostgreSQL a toujours privilégié la stabilité et le respect des standards plutôt que les performances.

La sécurité des données est un point essentiel. En premier lieu, un utilisateur doit être certain qu’à partir du moment où il a exécuté l’ordre COMMIT d’une transaction, les données modifiées relatives à cette transaction se trouvent bien sur disque et que même un crash ne pourra pas les faire disparaître. PostgreSQL est très attaché à ce concept et fait son possible pour forcer le système d’exploitation à ne pas conserver les données en cache, mais à les écrire sur disque dès l’arrivée d’un COMMIT.

L’intégrité des données, et le respect des contraintes fonctionnelles et techniques qui leur sont imposées, doivent également être garanties par le moteur à tout moment, quoi que fasse l’utilisateur. Par exemple, insérer 1000 caractères dans un champ contraint à 200 caractères maximum doit mener à une erreur explicite et non à l’insertion des 200 premiers caractères en oubliant les autres, comme cela s’est vu ailleurs. De même, un champ avec le type date ne contiendra jamais un 31 février, et un champ NOT NULL ne sera jamais vide. Tout ceci est formalisé par les propriétés (ACID) que possèdent toute bonne base de données relationnelle.

Le respect des normes est un autre principe au cœur du projet. Les développeurs de PostgreSQL cherchent à coller à la norme SQL le plus possible. PostgreSQL n’est pas compatible à cette norme à 100 %, aucun moteur ne l’est, mais il cherche à s’en approcher. Tout nouvel ajout d’une syntaxe ne sera accepté que si la syntaxe de la norme est ajoutée. Des extensions sont acceptées pour différentes raisons (performances, fonctionnalités en avance sur le comité de la norme, facilité de transition d’un moteur de bases de données à un autre) mais si une fonctionnalité existe dans la norme, une syntaxe différente ne peut être acceptée que si la syntaxe de la norme est elle-aussi présente.

La portabilité est importante : PostgreSQL tourne sur l’essentiel des systèmes d’exploitation : Linux (plate-forme à privilégier), macOS, les Unix propriétaires, Windows… Tout est fait pour que cela soit encore le cas dans le futur.

Ajouter des fonctionnalités est évidemment l’un des buts des développeurs de PostgreSQL. Cependant, comme il s’agit d’un projet libre, rien n’empêche un développeur de proposer une fonctionnalité, de la faire intégrer, puis de disparaître laissant aux autres la responsabilité de la corriger le cas échéant. Comme le nombre de développeurs de PostgreSQL est restreint, il est important que les fonctionnalités ajoutées soient vraiment utiles au plus grand nombre pour justifier le coût potentiel du débogage. Donc ne sont ajoutées dans PostgreSQL que ce qui est vraiment le cœur du moteur de bases de données et que ce qui sera utilisé vraiment par le plus grand nombre. Une fonctionnalité qui ne sert que une à deux personnes aura très peu de chances d’être intégrée. (Le système des extensions offre une élégante solution aux problèmes très spécifiques.)

Les performances ne viennent qu’après tout ça. En effet, rien ne sert d’avoir une modification du code qui permet de gagner énormément en performances si cela met en péril le stockage des données. Cependant, les performances de PostgreSQL sont excellentes et le moteur permet d’opérer des centaines de tables, des milliards de lignes pour plusieurs téraoctets de données, sur une seule instance, pour peu que la configuration matérielle soit correctement dimensionnée.

La simplicité du code est un point important. Le code est relu scrupuleusement par différents contributeurs pour s’assurer qu’il est facile à lire et à comprendre. En effet, cela facilitera le débogage plus tard si cela devient nécessaire.

La documentation est là aussi un point essentiel dans l’admission d’une nouvelle fonctionnalité. En effet, sans documentation, peu de personnes pourront connaître cette fonctionnalité. Très peu sauront exactement ce qu’elle est supposée faire, et il serait donc très difficile de déduire si un problème particulier est un manque actuel de cette fonctionnalité ou un bug.

Enfin, les tests fonctionnels suite à la compilation sont un prérequis depuis quelques années. Ils permettent de tester les fonctionnalités proposées et de ne pas retomber dans des bugs déjà corrigés.

Tous ces points sont vérifiés à chaque relecture d’un patch (nouvelle fonctionnalité ou correction).


Origines

  • Années 1970 : Michael Stonebraker développe Ingres à Berkeley
  • 1985 : Postgres succède à Ingres
  • 1995 : Ajout du langage SQL
  • 1996 : Libération du code : Postgres devient PostgreSQL
  • 1996 : Création du PostgreSQL Global Development Group

L’histoire de PostgreSQL remonte au système de gestion de base de données Ingres, développé dès 1973 à l’Université de Berkeley (Californie) par Michael Stonebraker.

Lorsque ce dernier décide en 1985 de recommencer le développement de zéro, il nomme le logiciel Postgres, comme raccourci de post-Ingres. Des versions commencent à être diffusées en 1989, puis commercialisées.

Postgres utilise alors un langage dérivé de QUEL, hérité d’Ingres, nommé POSTQUEL1. En 1995, lors du remplacement par le langage SQL par Andrew Yu and Jolly Chen, deux étudiants de Berkeley, Postgres est renommé Postgres95.

En 1996, Bruce Momijan et Marc Fournier convainquent l’Université de Berkeley de libérer complètement le code source. Est alors fondé le PGDG (PostgreSQL Development Group), entité informelle — encore aujourd’hui — regroupant l’ensemble des contributeurs. Le développement continue donc hors tutelle académique (et sans son fondateur historique Michael Stonebraker) : PostgreSQL 6.0 est publié début 1997.

Plus d’informations :


Apparition de la communauté internationale

  • ~ 2000: Communauté japonaise (JPUG)
  • 2004 : PostgreSQLFr
  • 2006 : SPI
  • 2007 : Communauté italienne
  • 2008 : PostgreSQL Europe et US
  • 2009 : Boom des PGDay
  • 2011 : Postgres Community Association of Canada
  • 2017 : Community Guidelines
  • …et ça continue

Les années 2000 voient l’apparition de communautés locales organisées autour d’association ou de manière informelle. Chaque communauté organise la promotion, la diffusion d’information et l’entraide à son propre niveau.

En 2000 apparaît la communauté japonaise (JPUG). Elle dispose déjà d’un grand groupe, capable de réaliser des conférences chaque année, d’éditer des livres et des magazines. Elle compte, au dernier recensement connu, plus de 3000 membres.

En 2004 naît l’association française (loi 1901) appelée PostgreSQL Fr. Cette association a pour but de fournir un cadre légal pour pouvoir participer à certains événements comme Solutions Linux, les RMLL ou d’en organiser comme le pgDay.fr (qui a déjà eu lieu à Toulouse, Nantes, Lyon, Toulon, Marseille). Elle permet aussi de récolter des fonds pour aider à la promotion de PostgreSQL.

En 2006, le PGDG intègre Software in the Public Interest, Inc.(SPI), une organisation à but non lucratif chargée de collecter et redistribuer des financements. Elle a été créée à l’initiative de Debian et dispose aussi de membres comme LibreOffice.org.

Jusque là, les événements liés à PostgreSQL apparaissaient plutôt en marge de manifestations, congrès, réunions… plus généralistes. En 2008, douze ans après la création du projet, des associations d’utilisateurs apparaissent pour soutenir, promouvoir et développer PostgreSQL à l’échelle internationale. PostgreSQL UK organise une journée de conférences à Londres, PostgreSQL Fr en organise une à Toulouse. Des « sur-groupes » apparaissent aussi pour aider les groupes locaux : PGUS rassemble les différents groupes américains, plutôt organisés géographiquement, par État ou grande ville. De même, en Europe, est fondée PostgreSQL Europe, association chargée d’aider les utilisateurs de PostgreSQL souhaitant mettre en place des événements. Son principal travail est l’organisation d’un événement majeur en Europe tous les ans : pgconf.eu, d’abord à Paris en 2009, puis dans divers pays d’Europe jusque Milan en 2019. Cependant, elle aide aussi les communautés allemande, française et suédoise à monter leur propre événement (respectivement PGConf.DE, pgDay Paris et Nordic PGday).

Dès 2010, nous dénombrons plus d’une conférence par mois consacrée uniquement à PostgreSQL dans le monde. Ce mouvement n’est pas prêt de s’arrêter :

En 2011, l’association Postgres Community Association of Canada voit le jour. Elle est créée par quelques membres de la Core Team pour gérer le nom déposé PostgreSQL, le logo, le nom de domaine sur Internet, etc.

Vu l’émergence de nombreuses communautés internationales, la communauté a décidé d’écrire quelques règles pour ces communautés. Il s’agit des Community Guidelines, apparues en 2017, et disponibles sur le site officiel.


Progression du code

  • 1,9 millions de lignes
    • ¼ de commentaires
    • le reste surtout en C
  • Nombres de commit par mois :
Évolution du nombre de commit dans le dépôt PostgreSQL

Le dépôt principal de PostgreSQL a été un dépôt CVS, passé depuis à git. Il est en accès public en lecture.

Le graphe ci-dessus (source) représente l’évolution du nombre de commit dans les sources de PostgreSQL. L’activité ne se dément pas. Le plus intéressant est certainement de noter que l’évolution est constante. Il n’y a pas de gros pic, ni dans un sens, ni dans l’autre.

Fin 2024, le code de PostgreSQL contient 1,9 millions de lignes,dont un quart de commentaires. Ce ratio montre que le code est très commenté, très documenté, facile à lire, et donc pratique à déboguer. Et le ratio ne change pas au fil des ans. Le code est essentiellement en C, avec un peu de SQL et de Perl. pour environ 200 développeurs actifs, à environ 200 commits par mois ces dernières années. Et ce, sans compter les contributions aux outils périphériques.


Les versions de PostgreSQL

Quelle version utiliser ?

  • Historique
  • Numérotation
  • Mises à jour mineures et majeures
  • Les versions courantes
  • Quelle version en production ?
  • Les forks & dérivés

Historique


Versions & fonctionnalités

  • 1996 : v6.0 -> première version publiée
  • 2003 : v7.4 -> première version réellement stable
  • 2005 : v8.0 -> arrivée sur Windows
  • 2008 : v8.3 -> performances et fonctionnalités, organisation (commitfests)
  • 2010 : v9.0 -> réplication physique
  • 2016 : v9.6 -> parallélisation
  • 2017 : v10 -> réplication logique, partitionnement déclaratif
  • 2024 : v17 -> performances, fonctionnalités, administration…

La version 7.4 est la première version réellement stable. La gestion des journaux de transactions a été nettement améliorée, et de nombreuses optimisations ont été apportées au moteur.

La version 8.0 marque l’entrée tant attendue de PostgreSQL dans le marché des SGDB de haut niveau, en apportant des fonctionnalités telles que les tablespaces, les routines stockées en Java, le Point In Time Recovery, ainsi qu’une version native pour Windows.

La version 8.3 se focalise sur les performances et les nouvelles fonctionnalités. C’est aussi la version qui a causé un changement important dans l’organisation du développement pour encourager les contributions : gestion des commitfests, création de l’outil web associé, etc.

Les versions 9.x sont axées réplication physique. La 9.0 intègre un système de réplication asynchrone asymétrique. La version 9.1 ajoute une réplication synchrone et améliore de nombreux points sur la réplication (notamment pour la partie administration et supervision). La version 9.2 apporte la réplication en cascade. La 9.3 et la 9.4 ajoutent quelques améliorations supplémentaires. La version 9.4 intègre surtout les premières briques pour l’intégration de la réplication logique dans PostgreSQL. La version 9.6 apporte la parallélisation, ce qui était attendu par de nombreux utilisateurs.

La version 10 propose beaucoup de nouveautés, comme une amélioration nette de la parallélisation et du partitionnement (le partitionnement déclaratif complète l’ancien partitionnement par héritage), mais aussi l’ajout de la réplication logique.

Les améliorations des versions 11 à 17 sont plus incrémentales, et portent sur tous les plans. Le partitionnement déclaratif et la réplication logique sont progressivement améliorés, en performances comme en facilité de développement et maintenance. Les performances s’améliorent encore grâce à la compilation Just In Time, la parallélisation de plus en plus d’opérations, les index couvrants, l’affinement des statistiques. La facilité d’administration s’améliore : nouvelles vues système, rôles supplémentaires pour réduire l’utilisation du superutilisateur, outillage pour la réplication logique et la sauvegarde physique, activation des sommes de contrôle sur une instance existante.

Il est toujours possible de télécharger les sources depuis la version 1.0 jusqu’à la version courante sur postgresql.org.


Numérotation

  • Version récentes (10+)
    • X : version majeure (10, 11, … 17)
    • X.Y : version mineure (14.8, 17.4)
  • Avant la version 10 (toutes périmées !)
    • X.Y : version majeure (9.4, 9.6)
    • X.Y.Z : version mineure (9.6.24)

Une version majeure apporte de nouvelles fonctionnalités, des changements de comportement, etc. Une version majeure sort généralement tous les ans à l’automne. Une migration majeure peut se faire directement depuis n’importe quelle version précédente. Le numéro est incrémenté chaque année (version 12 en 2019, version 16 en 2023).

Une version mineure ne comporte que des corrections de bugs ou de failles de sécurité. Les publications de versions mineures sont plus fréquentes que celles de versions majeures, avec un rythme de sortie trimestriel, sauf bug majeur ou faille de sécurité. Chaque bug est corrigé dans toutes les versions stables alors maintenues par le projet. Le numéro d’une version mineure porte deux nombres. Par exemple, en février 2025 sont sorties les versions 17.4, 16.8, 15.12, 14.17, et 13.20. La version 12 n’étant plus supportée, il n’y aura pas d’autre version mineure sur cette branche après la 12.22.

Avant la version 10, les versions majeures annuelles portaient deux chiffres : 9.0 en 2010, 9.6 en 2016. Les mineures avaient un numéro de plus (par exemple 9.6.24). Cela a entraîné quelques confusions, d’où le changement de numérotation. Il va sans dire que ces versions sont totalement périmées et ne sont plus supportées, mais beaucoup continuent de fonctionner.


Mises à jour mineure

De M.m à M.m+n :

  • En général chaque trimestre
  • Et sans souci
    • Release notes
    • tests
    • mise à jour des binaires
    • redémarrage

Une mise à jour mineure consiste à mettre à jour vers une nouvelle version de la même branche majeure, par exemple de 14.8 à 14.9, ou de 17.3 à 17.4 (mais pas d’une version 14.x à une version 17.x). Les mises à jour des versions mineures sont cumulatives : vous pouvez mettre à jour une instance 17.0 en version 17.4 sans passer par les versions 17.1 à 17.3 intermédiaires.

En général, les mises à jour mineures se font sans souci et ne nécessitent que le remplacement des binaires et un redémarrage (et donc une courte interruption). Les fichiers de données conservent le même format. Des opérations supplémentaires sont possibles mais rarissimes. Mais comme pour toute mise à jour, il convient d’être prudent sur d’éventuels effets de bord. En particulier, il faudra lire les Release Notes et, si possible, effectuer les tests ailleurs qu’en production.


Versions courantes

  • 1 version majeure par an
    • maintenue 5 ans
  • Dernières mises à jour mineures
  • Prochaine sortie de versions mineures prévue : 8 mai 2025

La philosophie générale des développeurs de PostgreSQL peut se résumer ainsi :

« Notre politique se base sur la qualité, pas sur les dates de sortie. »

Toutefois, même si cette philosophie reste très présente parmi les développeurs, en pratique une version stable majeure paraît tous les ans, habituellement à l’automne. Pour ne pas sacrifier la qualité des versions, toute fonctionnalité supposée insuffisamment stable est repoussée à la version suivante si des correctifs satisfaisants ne peuvent être trouvés à temps. Il est aussi arrivé que la sortie de la version majeure soit repoussée de quelques semaines à cause de bugs inacceptables mais corrigeables rapidement.

La tendance actuelle est de garantir un support pour chaque version majeure pendant une durée minimale de 5 ans. Ainsi ne sont plus supportées les versions 11 depuis novembre 2023 et 12 depuis novembre 2024. Il n’y aura pour elles plus aucune mise à jour mineure, donc plus de correction de bug ou de faille de sécurité. Le support de la version 13 s’arrêtera en novembre 2025. Le support de la dernière version majeure, la 17, devrait durer jusqu’en 2029.

Pour plus de détails :


Versions 9.4 à 11

  • jsonb
  • Row Level Security
  • Index BRIN, bloom
  • Fonctions OLAP
  • Parallélisation
  • SQL/MED : accès distants
  • Réplication logique
  • Partitionnement déclaratif
  • Réduction des inconvénients de MVCC
  • JIT
  • Index couvrants

Ces versions ne sont plus supportées !

La version 9.4 (décembre 2014) a apporté le type jsonb, binaire, facilitant la manipulation des objets en JSON.

La 9.5 parue en janvier 2016 apportait notamment les index BRIN et des possibilités OLAP plus avancées que GROUP BY. Pour plus de détails :

En 9.6, la nouvelle fonctionnalité majeure est certainement la parallélisation de certaines parties de l’exécution d’une requête. Le VACUUM FREEZE devient beaucoup moins gênant.

En version 10, les fonctionnalités majeures sont l’intégration de la réplication logique et le partitionnement déclaratif, longtemps attendus, améliorés dans les versions suivantes. Sont notables aussi les tables de transition ou les améliorations sur la parallélisation.

La version 10 a aussi été l’occasion de renommer plusieurs répertoires et fonctions système, et même des outils. Attention donc si vous rencontrez des requêtes ou des scripts adaptés aux versions précédentes. Entre autres :

  • le répertoire pg_xlog est devenu pg_wal ;
  • le répertoire pg_clog est devenu pg_xact ;
  • dans les noms de fonctions, xlog a été remplacé par wal (par exemple pg_switch_xlog est devenue pg_switch_wal) ;
  • toujours dans les fonctions, location a été remplacé par lsn.

Pour plus de détails :

La version 11 (octobre 2018) améliore le partitionnement de la version 10, le parallélisme, la réplication logique… et de nombreux autres points. Elle comprend aussi une première version du JIT (Just In Time compilation) pour accélérer les requêtes les plus lourdes en CPU, ou encore les index couvrants.

Pour plus de détails :


Version 12

  • Octobre 2019 - Novembre 2024 (n’est plus supportée !)
  • Amélioration du partitionnement déclaratif
  • Amélioration des performances
    • sur la gestion des index
    • sur les CTE (option MATERIALIZED)
  • Colonnes générées
  • Nouvelles vues de visualisation de la progression des commandes
  • Refonte de la configuration de la réplication

La version 12 sortie en 2019 n’est plus supportée depuis novembre 2024. Elle améliore de nouveau le partitionnement et elle fait surtout un grand pas au niveau des performances et de la supervision.

Le fichier recovery.conf (pour la réplication et les restaurations physiques) est intégré au fichier postgresql.conf. Une source fréquente de ralentissement disparaît, avec l’intégration des CTE (clauses WITH) dans la requête principale. Des colonnes d’une table peuvent être automatiquement générées à partir d’autres colonnes.

Pour plus de détails :


Version 13

  • Septembre 2020 - Septembre 2025
  • Améliorations :
    • partitionnement déclaratif
    • réplication logique
  • Amélioration des performances :
    • index B-tree, objet statistique, tri et agrégat
  • Amélioration de l’autovacuum et du VACUUM :
    • gestion complète des tables en insertion seule
    • traitement parallélisé des index lors d’un VACUUM
  • Amélioration des sauvegardes :
    • génération d’un fichier manifeste, outil pg_verifybackup
  • Nouvelles vues de progression de commandes :
    • pg_stat_progress_basebackup, pg_stat_progress_analyze

La version 13 sortie en 2020 ne sera plus supportée dès novembre 2025. Elle est remplie de nombreuses petites améliorations sur différents domaines : partitionnement déclaratif, autovacuum, sauvegarde, etc. Les performances sont aussi améliorées grâce à un gros travail sur l’optimiseur, ou la réduction notable de la taille de certains index.

Pour plus de détails :


Version 14

  • Septembre 2021 - Novembre 2026
  • Nouvelles vues système & améliorations
    • pg_stat_progress_copy, pg_stat_wal, pg_lock.waitstart, query_id
  • Lecture asynchrone des tables distantes
  • Paramétrage par défaut adapté aux machines plus récentes
  • Améliorations diverses :
    • réplications physique et logique
    • quelques facilités de syntaxe (triggers, tableaux en PL/pgSQL)
  • Performances :
    • connexions en lecture seule plus nombreuses
    • index…

La version 14 est remplie de nombreuses petites améliorations sur différents domaines listés ci-dessus.

Pour plus de détails :


Version 15

  • Octobre 2022 - Novembre 2027
  • Nombreuses améliorations incrémentales
    • dont en réplication logique
  • Commande MERGE
  • Performances :
    • DISTINCT parallélisable
    • pg_dump & sauvegardes, recovery, partitionnement
  • Changements notables :
    • public n’est plus accessible en écriture à tous
    • sauvegarde PITR exclusive disparaît

La version 15 est également une mise à jour sans grande nouveauté fracassante, mais contenant de très nombreuses améliorations et optimisations sur de nombreux plans, comme par exemple la commande MERGE ou l’accélération du recovery sur une reprise de restauration.

Signalons deux changements de comportement importants : pour renforcer la sécurité, le schéma public n’est plus accessible en écriture par défaut à tous les utilisateurs ; et la sauvegarde physique en mode exclusif n’est plus disponible.

Pour plus de détails :


Version 16

  • Septembre 2023 - Novembre 2028
  • Plus de tris incrémentaux (DISTINCT…)
  • Réplication logique depuis un secondaire
  • Expressions régulières dans pg_hba.conf
  • Vues systèmes améliorées : pg_stat_io
  • Compression lz4 ou zstd pour pg_dump
  • Optimisation et améliorations diverses (parallélisation…)

La version 16 est parue le 14 septembre 2023. Là encore, les améliorations sont incrémentales.

On notera la possibilité de rajouter des expressions régulières dans pg_hba.conf pour faciliter la gestion des accès. En réplication logique, un abonnement peut se faire auprès d’un serveur secondaire. La réplication logique peut devenir parallélisable. pg_dump acquiert des algorithmes de compression plus modernes. Le travail de parallélisation de nouveaux nœuds se poursuit. Une nouvelle vue de suivi des entrées-sorties apparaît : pg_stat_io. Le VACUUM peut être accéléré en lui permettant d’utiliser plus de mémoire dans les shared buffers.

Pour plus de détails :


Version 17

  • Septembre 2024 - Novembre 2029
  • Améliorations du VACUUM
  • Planificateur : IN et CTE
  • BRIN : création parallélisée
  • JSON_TABLE
  • Sauvegarde incrémentale (pg_basebackup + pg_combinebackup)
  • Améliorations en réplication logique (pg_createsubscriber)
  • pg_dump --filter
  • Imports (COPY peut rejeter des lignes, MERGE)
  • Améliorations diverses

La version 17, parue le 26 septembre 2024, contient quelques nouveautés intéressantes parmi ses 2635 commits.

Le VACUUM est encore amélioré en terme de sélectivité, de suivi, de mémoire maximum utilisable, tout en réduisant celle consommée. GRANT MAINTAIN et pg_maintain autorisent son lancement sur une table sans en être propriétaire. Le planificateur comprend quelques améliorations (pour les IN et les CTE entre autres). La création des index BRIN peut être parallélisée. Le chargement en masse et la transformation de données via MERGE et COPY ont également évolué en performances et fonctionnalités (MERGE … RETURNING). COPY peut enfin ignorer quelques lignes en erreur. Le travail de fond sur le partitionnement continue, avec le support natif des contraintes d’exclusions et des colonnes d’identité.

PostgreSQL inclut désormais un nouveau fournisseur de collation immutable (builtin), en plus de la collation de l’OS et de ICU. JSON et JSONPath voient apparaître de nouvelles fonctions (notamment JSON_TABLE).

La réplication logique permet enfin la gestion du failover du primaire, et la possibilité de créer un réplica logique via la réplication physique, avec l’outil pg_createsubscriber. pg_basebackup permet enfin de créer des sauvegardes incrémentales à recombiner avec pg_combinebackup. pg_dump a une clause --filter plus flexible.

Quelques nouveaux paramètres apparaissent, comme transaction_timeout, allow_alter_system, ou ceux permettant de gérer quelques caches spécifiques.

En supervision, entre autres, des éléments de pg_stat_bgwriter sont remplacés par d’autres dans la nouvelle vue pg_stat_checkpointer.

Pour plus de détails :


Petit résumé

  • Versions 7.x :
    • fondations
    • durabilité
  • Versions 8.x :
    • fonctionnalités
    • performances
  • Versions 9.x :
    • réplication physique
    • extensibilité
  • Versions 10 à 17 :
    • réplication logique
    • parallélisation
    • partitionnement
    • maintenabilité

Si nous essayons de voir cela avec de grosses mailles, les développements des versions 7 ciblaient les fondations d’un moteur de bases de données stable et durable. Ceux des versions 8 avaient pour but de rattraper les gros acteurs du marché en fonctionnalités et en performances. Enfin, pour les versions 9, on est plutôt sur la réplication et l’extensibilité.

La version 10 est une version mémorable grâce à la réplication logique, la parallélisation et le partitionnement. Les versions 11 à 17 améliorent ces deux points, entre mille autres améliorations en différents points du moteur, notamment les performances et la facilité d’administration.


Quelle version utiliser en production ?

Au premier semestre 2025 :

  • 12 et inférieures
    • Danger !
    • planifier une migration urgemment !
  • 13, 14, 15, 16
    • OK pour la production
    • ne pas oublier les mises à jour mineures
  • Nouvelles installations
    • 17
  • Nouveaux développements
    • 17
  • https://www.postgresql.org/about/featurematrix

La version 12 n’est plus supportée depuis novembre 2024.

Si vous avez une version 12 ou inférieure, planifiez le plus rapidement possible une migration vers une version plus récente, comme la 17.

Les versions non supportées fonctionneront toujours aussi bien, mais il n’y aura plus de correction de bug, y compris pour les failles de sécurité ! Si vous utilisez ces versions, il est impératif d’étudier une migration de version dès que possible.

Les versions 13 à 16 restent recommandables pour une production. Le plus important est d’appliquer les mises à jour correctives.

La version 17 est conseillée pour les nouvelles installations en production.

Par expérience, quand une version x.0 paraît à l’automne, elle est généralement stable. Nombre de DBA préfèrent prudemment attendre les premières mises à jour mineures (en novembre généralement) pour la mise en production. Cette prudence est à mettre en balance avec l’intérêt pour les nouvelles fonctionnalités.

Le développement de la version 18 a déjà débuté. Les paquets du PGDG contiennent déjà une version 18 utilisable à titre de test (mais attention ! il n’est pas garanti que toutes les fonctionnalités ajoutées soient finalement conservées). La version 18 bêta est prévue pour mai 2025, et il est probable que la 18.0 paraîtra à l’automne 2025.

Pour les nouveaux développements, démarrez en version 17.

Pour plus de détails sur les fonctionnalités de chaque version, voir le tableau comparatif des versions.


Versions dérivées / Forks

Entre de nombreux autres :

  • Compatibilité Oracle :
    • EnterpriseDB
  • Data warehouse :
    • Greenplum, Netezza
  • Forks :
    • Amazon RedShift, Aurora, Neon…
    • attention : support, extensions…
  • Extensions :
    • Citus
    • timescaledb
  • Packages avec des outils & support
  • Bases compatibles

Il existe de nombreuses versions dérivées de PostgreSQL. Elles sont en général destinées à des cas d’utilisation très spécifiques et offrent des fonctionnalités non proposées par la version communautaire. Leur code est souvent fermé et nécessite l’acquisition d’une licence payante. La licence de PostgreSQL permet cela, et le phénomène existait déjà dès les années 1990 avec divers produits commerciaux comme Illustra.

Modifier le code de PostgreSQL a plusieurs conséquences négatives. Certaines fonctionnalités de PostgreSQL peuvent être désactivées. Il est donc difficile de savoir ce qui est réellement utilisable. De plus, chaque nouvelle version mineure demande une adaptation de leur ajout de code. Chaque nouvelle version majeure demande une adaptation encore plus importante de leur code. C’est un énorme travail, qui n’apporte généralement pas suffisamment de plus-value à la société éditrice pour qu’elle le réalise. La seule société qui le fait de façon complète est EnterpriseDB, qui arrive à proposer des mises à jour régulièrement. Par contre, si on revient sur l’exemple de Greenplum, ils sont restés bloqués pendant un bon moment sur la version 8.0. Ils ont cherché à corriger cela. Fin 2021, Greenplum 6.8 est au niveau de la version 9.4, version considérée alors comme obsolète par la communauté depuis plus de deux ans. En janvier 2023, Greenplum 7.0 bêta n’est toujours parvenu qu’au niveau de PostgreSQL 12.12. Depuis, le dépôt n’est plus public…

Rien ne dit non plus que la société ne va pas abandonner son fork. Par exemple, il a existé quelques forks créés lorsque PostgreSQL n’était pas disponible en natif sous Windows : ces forks ont majoritairement disparu lors de l’arrivée de la version 8.0, qui supportait officiellement Windows dans la version communautaire.

Il y a eu aussi quelques forks créés pour gérer la réplication. Là aussi, la plupart de ces forks ont été abandonnés (et leurs clients avec) quand PostgreSQL a commencé à proposer de la réplication en version 9.0.

Il faut donc bien comprendre qu’à partir du moment où un utilisateur choisit une version dérivée, il dépend fortement (voire uniquement) de la bonne volonté de la société éditrice pour maintenir son produit, le mettre à jour avec les dernières corrections et les dernières nouveautés de la version communautaire, et le rendre compatible avec la myriade d’extensions existantes. Pour éviter ce problème, certaines sociétés ont décidé de transformer leur fork en une extension. C’est beaucoup plus simple à maintenir et n’enferme pas leurs utilisateurs. C’est le cas par exemple de citusdata (racheté par Microsoft) pour son extension de sharding ; ou encore de TimescaleDB, avec leur extension spécialisée dans les séries temporelles.

Dans les exemples de forks dédiés aux entrepôts de données, les plus connus historiquement sont Greenplum, de Pivotal (racheté par WMWare), et Netezza (racheté par IBM). Autant Greenplum tente de se raccrocher au PostgreSQL communautaire toutes les quelques années, autant ce n’est pas le cas de Netezza, optimisé pour du matériel dédié, et qui a forké de PostgreSQL 7.2.

Amazon, avec notamment les versions Redshift ou Aurora, a la particularité de modifier profondément PostgreSQL pour l’adapter à son infrastructure, mais ne diffuse pas ses modifications. Même si certaines incompatibilités sont listées, il est très difficile de savoir où ils en sont et l’impact qu’a leurs modifications.

Neon.tech est un autre fork ayant réécrit la couche de stockage et permettant de dupliquer des bases rapidement, notamment à l’usage des développeurs.

EDB Postgres Advanced Server est une distribution PostgreSQL d’EnterpriseDB. Elle permet de faciliter la migration depuis Oracle. Son code est propriétaire et soumis à une licence payante. Certaines fonctionnalités finissent par atterrir dans le code communautaire (une fois qu’EnterpriseDB le souhaite et que la communauté a validé l’intérêt de cette fonctionnalité et sa possible intégration).

Supabase est un exemple de société intégrant PostgreSQL dans une plateforme plus vaste pour du développement web.

BDR, anciennement de 2nd Quadrant, maintenant EnterpriseDB, est un fork visant à fournir une version multimaître de PostgreSQL, mais le code a été refermé dans les dernières versions. Il est très difficile de savoir où ils en sont. Son utilisation implique de prendre le support chez eux.

La société russe Postgres Pro, tout comme EnterpriseDB, propose diverses fonctionnalités dans sa version propre, tout en proposant souvent leur inclusion dans la version communautaire — ce qui n’est pas automatique.

Face au leadership de PostgreSQL, une tendance récente pour certaines bases de données est de se revendiquer « compatibles PostgreSQL », par exemple YugabyteDB. Certains éditeurs de solutions de bases de données distribuées propriétaires disent que leur produit peut remplacer PostgreSQL sans modification de code côté application. Il convient de rester critique et prudent face à cette affirmation, car ces produits n’ont parfois rien à voir avec PosgreSQL. Leurs évolutions n’intégreront sans doute pas la version communautaire.

(Cet historique provient en partie de la liste exhaustive des « forks », ainsi de que cette conférence de Josh Berkus de 2009 et des références en bibliographie.)

Sauf cas très précis, il est recommandé d’utiliser la version officielle, libre et gratuite. Vous savez exactement ce qu’elle propose et vous choisissez librement vos partenaires (pour les formations, pour le support, pour les audits, etc). Vous profitez aussi de tous les outils de l’écosystème.


Quelques projets satellites

PostgreSQL n’est que le moteur ! Besoin d’outils pour :

  • Administration
  • Sauvegarde
  • Supervision
  • Migration
  • SIG

PostgreSQL n’est qu’un moteur de bases de données. Quand vous l’installez, vous n’avez que ce moteur. Vous disposez de quelques outils en ligne de commande (détaillés dans nos modules « Outils graphiques et consoles » et « Tâches courantes ») mais aucun outil graphique n’est fourni.

Du fait de ce manque, certaines personnes ont décidé de développer ces outils graphiques. Ceci a abouti à une grande richesse grâce à la grande variété de projets « satellites » qui gravitent autour du projet principal.

Par choix, nous ne présenterons ici que des logiciels libres et gratuits. Pour chaque problématique, il existe aussi des solutions propriétaires. Ces solutions peuvent parfois apporter des fonctionnalités inédites. Il faut néanmoins considérer que l’offre de la communauté Open-Source répond à la plupart des besoins des utilisateurs de PostgreSQL.


Administration, Développement, Modélisation

Entre autres, dédiés ou pas :

  • Administration :
    • pgAdmin4
    • temBoard
  • Développement :
    • DBeaver
  • Modélisation :
    • pgModeler

Il existe différents outils graphiques pour l’administration, le développement et la modélisation. Une liste plus exhaustive est disponible sur le wiki PostgreSQL.

pgAdmin4 est un outil d’administration dédié à PostgreSQL, qui permet aussi de requêter. (La version 3 est considérée comme périmée.)

temBoard est une console d’administration plus complète. temBoard intègre de la supervision, des tableaux de bord, la gestion des sessions en temps réel, du bloat, de la configuration et l’analyse des performances.

DBeaver est un outil de requêtage courant, utilisable avec de nombreuses bases de données différentes, et adapté à PostgreSQL.

Pour la modélisation, pgModeler est dédié à PostgreSQL. Il permet la modélisation, la rétro-ingénierie d’un schéma existant, la génération de scripts de migration.


Sauvegardes

Les outils listés ci-dessus sont les outils principaux et que nous recommandons pour la réalisation des sauvegardes et la gestion de leur rétention.

Ils se basent sur les outils standards de PostgreSQL de sauvegarde physique ou logique.

Liens :

Il existe plusieurs outils propriétaires, notamment pour une sauvegarde par snapshot au niveau de la baie.


Supervision

  • Nagios/Icinga2 :
    • check_pgactivity
    • check_postgres
  • Prometheus : postgres_exporter
  • PoWA

Pour ne citer que quelques projets libres et matures :

check_pgactivity est une sonde Nagios pouvant récupérer un grand nombre de statistiques d’activités renseignées par PostgreSQL. Il faut de ce fait un serveur Nagios (ou un de ses nombreux forks ou surcharges) pour gérer les alertes et les graphes. Il existe aussi check_postgres.

postgres_exporter est l’exporteur de métriques pour Prometheus.

PoWA est composé d’une extension qui historise les statistiques récupérées par l’extension pg_stat_statements et d’une application web qui permet de récupérer les requêtes et leur statistiques facilement.


Audit

pgBadger est l’outil de base pour les analyses (à posteriori) des traces de PostgreSQL, dont notamment les requêtes.

pgCluu permet une analyse du système et de PostgreSQL.


Migration

Il existe de nombreux outils pour migrer vers PostgreSQL une base de données utilisant un autre moteur. Ce qui pose le plus problème en pratique est le code applicatif (procédures stockées).

Ces outils ont été développé au fil du temps par des sociétés proposant de la prestation de service autour de la migration, ou par de grosses entreprises pour leurs besoins internes. Il sont plus ou moins avancés, efficaces, finis, maintenus, et généralement propriétaires.

Les fonctionnalités incluent généralement la migration du schéma, parfois le transfert des données de manière plus ou moins optimisée, l’estimation de la charge de migration, ou encore la migration du code applicatif (PL/SQL d’Oracle notamment). Ce dernier point est généralement le plus complexe et doit généralement être repris ou traité par des humains.

Les plus importants parmi les outils libres actifs sont :

  • Ora2Pg, de Gilles Darold, convertit le schéma de données, migre les données, et tente même de convertir le code PL/SQL en PL/pgSQL. Il convertit aussi des bases MySQL ou SQL Server.

  • pgloader, de Dimitri Fontaine, permet de migrer depuis MySQL, SQLite ou MS SQL Server, et importe les fichiers CSV, DBF (dBase) ou IXF (fichiers d’échange indépendants de la base).


PostGIS

Logo Postgis
  • Projet indépendant, GPL, https://postgis.net/
  • Module spatial pour PostgreSQL
    • Extension pour types géométriques/géographiques & outils
    • La référence des bases de données spatiales
    • « quelles sont les routes qui coupent le Rhône ? »
    • « quelles sont les villes adjacentes à Toulouse ? »
    • « quels sont les restaurants situés à moins de 3 km de la Nationale 12 ? »

PostGIS ajoute le support d’objets géographiques à PostgreSQL. C’est un projet totalement indépendant développé par la société Refractions Research sous licence GPL, soutenu par une communauté active, utilisée par des spécialistes du domaine géospatial (IGN, BRGM, AirBNB, Mappy, Openstreetmap, Agence de l’eau…), mais qui peut convenir pour des projets plus modestes.

Techniquement, c’est une extension transformant PostgreSQL en serveur de données spatiales, qui sera utilisé par un Système d’Information Géographique (SIG), tout comme le SDE de la société ESRI ou bien l’extension Oracle Spatial. PostGIS se conforme aux directives du consortium OpenGIS et a été certifié par cet organisme comme tel, ce qui est la garantie du respect des standards par PostGIS.

PostGIS permet d’écrire des requêtes de ce type :

SELECT restaurants.geom, restaurants.name FROM restaurants
  WHERE EXISTS (SELECT 1 FROM routes
                   WHERE ST_DWithin(restaurants.geom, routes.geom, 3000)
                AND route.name = 'Nationale 12')

PostGIS fournit les fonctions d’indexation qui permettent d’accéder rapidement aux objets géométriques, au moyen d’index GiST. La requête ci-dessus n’a évidemment pas besoin de parcourir tous les restaurants à la recherche de ceux correspondant aux critères de recherche.

La liste des fonctionnalités comprend le support des coordonnées géodésiques ; des projections et reprojections dans divers systèmes de coordonnées locaux (Lambert93 en France par exemple) ; des opérateurs d’analyse géométrique (enveloppe convexe, simplification…)

PostGIS est intégré aux principaux serveurs de carte, ETL, et outils de manipulation.

La version 3.0 apporte la gestion du parallélisme, un meilleur support de l’indexation SP-GiST et GiST, ainsi qu’un meilleur support du type GeoJSON.


Sponsors & Références

Au-delà de ses qualités, PostgreSQL suscite toujours les mêmes questions récurrentes :

  • qui finance les développements ? (et pourquoi ?)
  • qui utilise PostgreSQL ?

Sponsors principaux

  • Sociétés se consacrant à PostgreSQL :
    • Crunchy Data (USA) : Tom Lane, Stephen Frost, Joe Conway…
    • EnterpriseDB (USA) : Bruce Momjian, Robert Haas, Peter Eisentraut…
    • PostgresPro (Russie) : Oleg Bartunov, Teodor Sigaev
    • Cybertec (Autriche), Dalibo (France), Redpill Linpro (Suède), Credativ (Allemagne)…
  • Sociétés vendant un fork ou une extension :
    • Citusdata (Microsoft), Pivotal (VMWare), TimescaleDB

La liste des sponsors de PostgreSQL contribuant activement au développement figure sur la liste officielle des sponsors. Ce qui suit n’est qu’un aperçu.

EnterpriseDB est une société américaine qui a décidé de fournir une version de PostgreSQL propriétaire fournissant une couche de compatibilité avec Oracle. Ils emploient plusieurs développeurs importants du projet PostgreSQL (dont trois font partie de la Core Team), et reversent un certain nombre de leurs travaux au sein du moteur communautaire. Ils ont aussi un poids financier qui leur permet de sponsoriser la majorité des grands événements autour de PostgreSQL : PGEast et PGWest aux États-Unis, PGDay en Europe.

En 2020, EnterpriseDB rachète 2nd Quadrant, une société anglaise fondée par le regretté Simon Riggs, développeur PostgreSQL de longue date. 2nd Quadrant développe de nombreux outils autour de PostgreSQL comme pglogical, des versions dérivées comme Postgres-XL ou BDR, ou des outils annexes comme barman ou repmgr.

Crunchy Data offre sa propre version certifiée et finance de nombreux développements.

De nombreuses autres sociétés dédiées à PostgreSQL existent dans de nombreux pays. Parmi les sponsors officiels, nous pouvons compter Cybertec en Autriche ou Redpill Linpro en Suède. En Russie, PostgresPro maintient une version locale et reverse aussi de nombreuses contributions à la communauté.

En Europe francophone, Dalibo participe pleinement à la communauté. La société est Major Sponsor du projet PostgreSQL, ce qui indique un support de longue date. Elle développe et maintient plusieurs outils plébiscités par la communauté, comme autrefois Open PostgreSQL Monitoring (OPM) ou la sonde check_pgactivity, plus récemment la console d’administration temBoard, avec de nombreux autres projets en cours, et une participation active au développement de patchs pour PostgreSQL. Dalibo sponsorise également des événements comme les PGDay français et européens, ainsi que la communauté francophone.

Des sociétés comme Citusdata (racheté par Microsoft), Pivotal (VMWare) ou TimescaleDB proposent ou ont proposé leur version dérivée sous une forme ou une autre, mais « jouent le jeu » et participent au développement de la version communautaire, notamment en cherchant à ce que leur produit n’en diverge pas.


Autres sponsors

  • Autres sociétés :
    • VMWare, Rackspace, Heroku, Conova, Red Hat, Microsoft
    • NTT (streaming replication), Fujitsu, NEC
  • Cloud
    • nombreuses

Contribuent également à PostgreSQL nombre de sociétés non centrées autour des bases de données.

NTT a financé de nombreux patchs pour PostgreSQL.

Fujitsu a participé à de nombreux développements aux débuts de PostgreSQL, et emploie Amit Kapila.

VMWare a longtemps employé le développeur finlandais Heikki Linnakangas, parti ensuite un temps chez Pivotal. VMWare emploie aussi Michael Paquier ou Julien Rouhaud.

Red Hat a longtemps employé Tom Lane à plein temps pour travailler sur PostgreSQL. Il a pu dédier une très grande partie de son temps de travail à ce projet, bien qu’il ait eu d’autres affectations au sein de Red Hat. Tom Lane a travaillé également chez SalesForce, ensuite il a rejoint Crunchy Data Solutions fin 2015.

Il y a déjà plus longtemps, Skype a offert un certain nombre d’outils très intéressants : PgBouncer (pooler de connexion), Londiste (réplication par trigger), etc. Ce sont des outils utilisés en interne et publiés sous licence BSD comme retour à la communauté. Malgré le rachat par Microsoft, certains sont encore utiles et maintenus.

Zalando est connu pour l’outil de haute disponibilité patroni.

De nombreuses sociétés liées au cloud figurent aussi parmi les sponsors, comme Conova (Autriche), Heroku ou Rackspace (États-Unis), ou les mastodontes Google, Amazon Web Services et, à nouveau, Microsoft.


Références

  • Météo France
  • IGN
  • RATP, SNCF
  • CNAF
  • MAIF, MSA
  • Le Bon Coin
  • Air France-KLM
  • Société Générale
  • Carrefour, Leclerc, Leroy Merlin
  • Instagram, Zalando, TripAdvisor
  • Yandex
  • CNES
  • …et plein d’autres

Météo France utilise PostgreSQL depuis plus d’une décennie pour l’essentiel de ses bases, dont des instances critiques de plusieurs téraoctets (témoignage sur postgresql.fr).

L’IGN utilise PostGIS et PostgreSQL depuis 2006.

La RATP a fait ce choix depuis 2007 également.

La Caisse Nationale d’Allocations Familiales a remplacé ses mainframes par des instances PostgreSQL dès 2010 (4 To et 1 milliard de requêtes par jour).

Instagram utilise PostgreSQL depuis le début.

Zalando a décrit plusieurs fois son infrastructure PostgreSQL et annonçait en 2018 utiliser pas moins de 300 bases de données en interne et 650 instances dans un cloud AWS. Zalando contribue à la communauté, notamment par son outil de haute disponibilité patroni.

Le DBA de TripAdvisor témoigne de leur utilisation de PostgreSQL dans l’interview suivante.

Dès 2009, Leroy Merlin migrait vers PostgreSQL des milliers de logiciels de caisse.

Yandex, équivalent russe de Google a décrit en 2016 la migration des 300 To de données de Yandex.Mail depuis Oracle vers PostgreSQL.

La Société Générale a publié son outil de migration d’Oracle à PostgreSQL.

Autolib à Paris utilisait PostgreSQL. Le logiciel est encore utilisé dans les autres villes où le service continue. Ils ont décrit leur infrastructure au PG Day 2018 à Marseille.

De nombreuses autres sociétés participent au Groupe de Travail Inter-Entreprises de PostgreSQLFr : Air France, Carrefour, Leclerc, le CNES, la MSA, la MAIF, PeopleDoc, EDF…

Cette liste ne comprend pas les innombrables sociétés qui n’ont pas communiqué sur le sujet. PostgreSQL étant un logiciel libre, il n’existe nulle part de dénombrement des instances actives.


Le Bon Coin

  • Site de petites annonces
  • 4è site le plus consulté en France (2017)
  • 27 millions d’annonces en ligne, 800 000 nouvelles chaque jour
  • Instance PostgreSQL principale : 3 To de volume, 3 To de RAM
  • 20 serveurs secondaires

PostgreSQL tient la charge sur de grosses bases de données et des serveurs de grande taille.

Le Bon Coin privilégie des serveurs physiques dans ses propres datacenters.

Pour plus de détails et l’évolution de la configuration, voir les témoignages de ses directeurs technique (témoignage de juin 2012) et infrastructure (juin 2017), ou la conférence de son DBA Flavio Gurgel au pgDay Paris 2019.

Ce dernier s’appuie sur les outils classiques fournis par la communauté : pg_dump (pour archivage, car ses exports peuvent être facilement restaurés), barman, pg_upgrade.


À la rencontre de la communauté

  • Cartographie du projet
  • Pourquoi participer
  • Comment participer

PostgreSQL, un projet mondial

Carte des hackers

On le voit, PostgreSQL compte des contributeurs sur tous les continents.

Le projet est principalement anglophone. Les core hackers sont surtout répartis en Amérique, Europe, Asie (Japon surtout).

Il existe une très grande communauté au Japon, et de nombreux développeurs en Russie.

La communauté francophone est très dynamique, s’occupe beaucoup des outils, mais il n’y a que quelques développeurs réguliers du core francophones : Michael Paquier, Julien Rouhaud, Fabien Coelho…

La communauté hispanophone est naissante.


PostgreSQL Core Team

Core team

Le terme Core Hackers désigne les personnes qui sont dans la communauté depuis longtemps. Ces personnes désignent directement les nouveaux membres.

NB : Le terme hacker peut porter à confusion, il s’agit bien ici de la définition « universitaire » : https://fr.wikipedia.org/wiki/Hacker_(programmation)

La Core Team est un ensemble de personnes doté d’un pouvoir assez limité. Ils ne doivent pas appartenir en majorité à la même société. Ils peuvent décider de la date de sortie d’une version. Ce sont les personnes qui sont immédiatement au courant des failles de sécurité du serveur PostgreSQL. Exceptionnellement, elles tranchent certains débats si un consensus ne peut être atteint dans la communauté. Tout le reste des décisions est pris par la communauté dans son ensemble après discussion, généralement sur la liste pgsql-hackers.

Les membres actuels de la Core Team sont :

  • Tom Lane (Crunchy Data, Pittsburgh, États-Unis) : certainement le développeur le plus aguerri avec la vision la plus globale, notamment sur l’optimiseur ;
  • Bruce Momjian (EDB, Pennsylvanie, États-Unis) : a lancé le projet en 1995, écrit du code (pg_upgrade notamment) et s’est beaucoup occupé de la promotion ;
  • Magnus Hagander (Redpill Linpro, Stockholm, Suède) : développeur, a participé notamment au portage Windows, à l’outil pg_basebackup, à l’authentification, à l’administration des serveurs, président de PostgreSQL Europe ;
  • Andres Freund (Microsoft, San Francisco, États-Unis) : contributeur depuis des années de nombreuses fonctionnalités (JIT, réplication logique, performances…) ;
  • Dave Page (pgEdge, Oxfordshire, Royaume-Uni) : leader du projet pgAdmin, version Windows, administration des serveurs, secrétaire de PostgreSQL Europe ;
  • Peter Eisentraut (EDB, Dresde, Allemagne) : développement du moteur (internationalisation, SQL/Med…), participe à la définition du standard SQL, etc. ;
  • Jonathan Katz (Amazon Web Services, New York, États-Unis) : promotion du projet, modération, revues de patchs.

Contributeurs

Contributeurs

Actuellement, les « contributeurs » se répartissent quotidiennement les tâches suivantes :

  • développement des projets satellites (pgAdmin…) ;
  • promotion du logiciel ;
  • administration de l’infrastructure des serveurs ;
  • rédaction de documentation ;
  • conférences ;
  • traductions ;
  • organisation de groupes locaux.

Le PGDG a fêté son 10e anniversaire à Toronto en juillet 2006. Ce « PostgreSQL Anniversary Summit » a réuni pas moins de 80 membres actifs du projet. La photo ci-dessus a été prise à l’occasion.

PGCon2009 a réuni 180 membres actifs à Ottawa, et environ 220 en 2018 et 2019.

Voir la liste des contributeurs officiels.


Qui contribue du code ?

  • Principalement des personnes payées par leur société
  • 30 committers
    • Tom Lane
    • Andres Freund
    • Peter Eisentraut
    • Nikita Glukhov
    • Álvaro Herrera
    • Michael Paquier
    • Robert Haas
    • …et beaucoup d’autres
  • Commitfests : tous les 2 mois

À l’automne 2024, on compte 30 committers, c’est-à-dire personnes pouvant écrire dans tout ou partie du dépôt de PostgreSQL. Il ne s’agit pas que de leur travail, mais pour une bonne partie de patchs d’autres contributeurs après discussion et validation des fonctionnalités mais aussi des standards propres à PostgreSQL, de la documentation, de la portabilité, de la simplicité, de la sécurité, etc. Ces autres contributeurs peuvent être potentiellement n’importe qui. En général, un patch est relu par plusieurs personnes avant d’être transmis à un committer.

Les discussions quant au développement ont lieu principalement (mais pas uniquement) sur la liste pgsql-hackers. Les éventuels bugs sont transmis à la liste pgsql-bugs. Puis les patchs en cours sont revus au moins tous les deux mois lors des Commitfests. Il n’y a pas de bug tracker car le fonctionnement actuel est jugé satisfaisant.

Robert Haas publie chaque année une analyse sur les contributeurs de code et les participants aux discussions sur le développement de PostgreSQL sur la liste pgsql-hackers :


Répartition des développeurs

Répartition des développeurs

Voici une répartition des différentes sociétés qui ont contribué aux améliorations de la version 13. On y voit qu’un grand nombre de sociétés prend part à ce développement. La plus importante est EDB, mais même elle n’est responsable que d’un petit tiers des contributions.

(Source : Future Postgres Challenges, Bruce Momjian, 2021)


Utilisateurs

  • Vous !
  • Le succès d’un logiciel libre dépend de ses utilisateurs.

Il est impossible de connaître précisément le nombre d’utilisateurs de PostgreSQL. Toutefois ce nombre est en constante augmentation.

Il existe différentes manières de s’impliquer dans une communauté Open-Source. Dans le cas de PostgreSQL, vous pouvez :

  • déclarer un bug ;
  • tester les versions bêta ;
  • témoigner.

Pourquoi participer

  • Rapidité des corrections de bugs
  • Préparer les migrations / tester les nouvelles versions
  • Augmenter la visibilité du projet
  • Créer un réseau d’entraide

Au-delà de motivations idéologiques ou technologiques, il y a de nombreuses raisons objectives de participer au projet PostgreSQL.

Envoyer une description d’un problème applicatif aux développeurs est évidemment le meilleur moyen d’obtenir sa correction. Attention toutefois à être précis et complet lorsque vous déclarez un bug sur pgsql-bugs ! Assurez-vous que vous pouvez le reproduire.

Tester les versions « candidates » dans votre environnement (matériel et applicatif) est la meilleure garantie que votre système d’information sera compatible avec les futures versions du logiciel.

Les retours d’expérience et les cas d’utilisations professionnelles sont autant de preuves de la qualité de PostgreSQL. Ces témoignages aident de nouveaux utilisateurs à opter pour PostgreSQL, ce qui renforce la communauté.

S’impliquer dans les efforts de traductions, de relecture ou dans les forums d’entraide ainsi que toute forme de transmission en général est un très bon moyen de vérifier et d’approfondir ses compétences.


Ressources web de la communauté

Le site officiel de la communauté se trouve sur https://www.postgresql.org/. Ce site contient des informations sur PostgreSQL, la documentation des versions maintenues, les archives des listes de discussion, etc.

Le site « Planet PostgreSQL » est un agrégateur réunissant les blogs des Core Hackers, des contributeurs, des traducteurs et des utilisateurs de PostgreSQL.

Le site PGXN est l’équivalent pour PostgreSQL du CPAN de Perl, une collection en ligne de librairies et extensions accessibles depuis la ligne de commande.


Documentation officielle

La documentation officielle sur https://www.postgresql.org/docs/current est maintenue au même titre que le code du projet, et sert aussi au quotidien, pas uniquement pour des cas obscurs.

Elle est versionnée pour chaque version majeure.

La traduction française suit de près les mises à jour de la documentation officielle : https://docs.postgresql.fr/.


Serveurs francophones

Le site postgresql.fr est le site de l’association des utilisateurs francophones du logiciel. La communauté francophone se charge de la traduction de toutes les documentations.


Listes de discussions / Listes d’annonces

  • pgsql-announce
  • pgsql-general
  • pgsql-admin
  • pgsql-sql
  • pgsql-performance
  • pgsql-fr-generale
  • pgsql-advocacy
  • pgsql-bugs

Les mailing-lists sont les outils principaux de gouvernance du projet. Toute l’activité de la communauté (bugs, promotion, entraide, décisions) est accessible par ce canal. Les développeurs principaux du projets répondent parfois eux-mêmes. Si vous avez une question ou un problème, la réponse se trouve probablement dans les archives ! Pour s’inscrire ou consulter les archives : https://www.postgresql.org/list/.

Si vous pensez avoir trouvé un bug, vous pouvez le remonter sur la liste anglophone pgsql-bugs, par le formulaire dédié. Pour faciliter la tâche de ceux qui tenteront de vous répondre, suivez bien les consignes sur les rapports de bug : informations complètes, reproductibilité…


IRC

  • Réseau LiberaChat
  • IRC anglophone :
    • #postgresql
    • #postgresql-eu
  • IRC francophone :
    • #postgresqlfr

Le point d’entrée principal pour le réseau LiberaChat est le serveur irc.libera.chat. La majorité des développeurs sont disponibles sur IRC et peuvent répondre à vos questions.

Des canaux de discussion spécifiques à certains projets connexes sont également disponibles, comme par exemple #slony.

Attention ! Vous devez poser votre question en public et ne pas solliciter de l’aide par message privé.


Wiki

Le wiki est un outil de la communauté qui met à disposition une véritable mine d’informations.

Au départ, le wiki avait pour but de récupérer les spécifications écrites par des développeurs pour les grosses fonctionnalités à développer à plusieurs. Cependant, peu de développeurs l’utilisent dans ce cadre. L’utilisation du wiki a changé en passant plus entre les mains des utilisateurs qui y intègrent un bon nombre de pages de documentation (parfois reprises dans la documentation officielle). Le wiki est aussi utilisé par les organisateurs d’événements pour y déposer les slides des conférences. Elle n’est pas exhaustive et, hélas, souffre fréquemment d’un manque de mises à jour.


L’avenir de PostgreSQL

  • PostgreSQL est devenu la base de données de référence
  • Grandes orientations :
    • réplication logique
    • meilleur parallélisme
    • gros volumes
  • Prochaine version, la 18
  • Stabilité économique
  • De plus en plus de (gros) clients
  • Le futur de PostgreSQL dépend de vous !

Le projet avance grâce à de plus en plus de contributions. Les grandes orientations actuelles sont :

  • une réplication de plus en plus sophistiquée ;
  • une gestion plus étendue du parallélisme ;
  • une volumétrie acceptée de plus en plus importante ;
  • etc.

PostgreSQL est là pour durer. Le nombre d’utilisateurs, de toutes tailles, augmente tous les jours. Il n’y a pas qu’une seule entreprise derrière ce projet. Il y en a plusieurs, petites et grosses sociétés, qui s’impliquent pour faire avancer le projet, avec des modèles économiques et des marchés différents, garants de la pérennité du projet.


Conclusion

  • Un projet de grande ampleur
  • Un SGBD complet
  • Souplesse, extensibilité
  • De belles références
  • Une solution stable, ouverte, performante et éprouvée
  • Pas de dépendance envers UN éditeur

Certes, la licence PostgreSQL implique un coût nul (pour l’acquisition de la licence), un code source disponible et aucune contrainte de redistribution. Toutefois, il serait erroné de réduire le succès de PostgreSQL à sa gratuité.

Beaucoup d’acteurs font le choix de leur SGBD sans se soucier de son prix. En l’occurrence, ce sont souvent les qualités intrinsèques de PostgreSQL qui séduisent :

  • sécurité des données (reprise en cas de crash et résistance aux bogues applicatifs) ;
  • facilité de configuration ;
  • montée en puissance et en charge progressive ;
  • gestion des gros volumes de données ;
  • pas de dépendance envers un unique éditeur ou prestataire.

Bibliographie

  • Documentation officielle (préface)
  • Articles fondateurs de M. Stonebracker (1987)
  • Présentation du projet PostgreSQL (Guillaume Lelarge, 2008)
  • Looking back at PostgreSQL (J.M. Hellerstein, 2019)

Quelques références :

Iconographie : La photo initiale est le logo officiel de PostgreSQL.


Questions

N’hésitez pas, c’est le moment !


Quiz

Découverte des fonctionnalités

PostgreSQL

Au menu

  • Fonctionnalités du moteur

  • Objets SQL

  • Connaître les différentes fonctionnalités et possibilités

  • Découvrir des exemples concrets

Ce module propose un tour rapide des fonctionnalités principales du moteur : ACID, MVCC, transactions, journaux de transactions… ainsi que des objets SQL gérés (schémas, index, tablespaces, triggers…). Ce rappel des concepts de base permet d’avancer plus facilement lors des modules suivants.


Fonctionnalités du moteur

  • Standard SQL
  • ACID : la gestion transactionnelle
  • Niveaux d’isolation
  • Journaux de transactions
  • Administration
  • Sauvegardes
  • Réplication
  • Supervision
  • Sécurité
  • Extensibilité

Cette partie couvre les différentes fonctionnalités d’un moteur de bases de données. Il ne s’agit pas d’aller dans le détail de chacune, mais de donner une idée de ce qui est disponible. Les modules suivants de cette formation et des autres formations détaillent certaines de ces fonctionnalités.


Respect du standard SQL

  • Excellent support du SQL ISO
  • Objets SQL
    • tables, vues, séquences, routines, triggers
  • Opérations
    • jointures, sous-requêtes, requêtes CTE, requêtes de fenêtrage, etc.

La dernière version du standard SQL est SQL:2023. À ce jour, aucun SGBD ne la supporte complètement, mais :

  • PostgreSQL progresse et s’en approche au maximum, au fil des versions ;
  • la majorité de la norme est supportée, parfois avec des syntaxes différentes ;
  • PostgreSQL est le SGDB le plus respectueux du standard.

ACID

Gestion transactionnelle : la force des bases de données relationnelles :

  • Atomicité (Atomic)
  • Cohérence (Consistent)
  • Isolation (Isolated)
  • Durabilité (Durable)

Les propriétés ACID sont le fondement même de toute bonne base de données. Il s’agit de l’acronyme des quatre règles que toute transaction (c’est-à-dire une suite d’ordres modifiant les données) doit respecter :

  • A : Une transaction est appliquée en « tout ou rien ».
  • C : Une transaction amène la base d’un état stable à un autre.
  • I : Les transactions n’agissent pas les unes sur les autres.
  • D : Une transaction validée sera conservée de manière permanente.

Les bases de données relationnelles les plus courantes depuis des décennies (PostgreSQL bien sûr, mais aussi Oracle, MySQL, SQL Server, SQLite…) se basent sur ces principes, même si elles font chacune des compromis différents suivant leurs cas d’usage, les compromis acceptés à chaque époque avec la performance et les versions.

Atomicité :

Une transaction doit être exécutée entièrement ou pas du tout, et surtout pas partiellement, même si elle est longue et complexe, même en cas d’incident majeur sur la base de données. L’exemple basique est une transaction bancaire : le montant d’un virement doit être sur un compte ou un autre, et en cas de problème ne pas disparaître ou apparaître en double. Ce principe garantit que les données modifiées par des transactions valides seront toujours visibles dans un état stable, et évite nombre de problèmes fonctionnels comme techniques.

Cohérence :

Un état cohérent respecte les règles de validité définies dans le modèle, c’est-à-dire les contraintes définies dans le modèle : types, plages de valeurs admissibles, unicité, liens entre tables (clés étrangères), etc. Le non-respect de ces règles par l’applicatif entraîne une erreur et un rejet de la transaction.

Isolation :

Des transactions simultanées doivent agir comme si elles étaient seules sur la base. Surtout, elles ne voient pas les données non validées des autres transactions. Ainsi une transaction peut travailler sur un état stable et fixe, et durer assez longtemps sans risque de gêner les autres transactions.

Il existe plusieurs « niveaux d’isolation » pour définir précisément le comportement en cas de lectures ou écritures simultanées sur les mêmes données et pour arbitrer avec les contraintes de performances ; le niveau le plus contraignant exige que tout se passe comme si toutes les transactions se déroulaient successivement.

Durabilité :

Une fois une transaction validée par le serveur (typiquement : COMMIT ne retourne pas d’erreur, ce qui valide la cohérence et l’enregistrement physique), l’utilisateur doit avoir la garantie que la donnée ne sera pas perdue ; du moins jusqu’à ce qu’il décide de la modifier à nouveau. Cette garantie doit valoir même en cas d’événement catastrophique : plantage de la base, perte d’un disque… C’est donc au serveur de s’assurer autant que possible que les différents éléments (disque, système d’exploitation…) ont bien rempli leur office. C’est à l’humain d’arbitrer entre le niveau de criticité requis et les contraintes de performances et de ressources adéquates (et fiables) à fournir à la base de données.

NoSQL :

À l’inverse, les outils de la mouvance (« NoSQL », par exemple MongoDB ou Cassandra), ne fournissent pas les garanties ACID. C’est le cas de la plupart des bases non-relationnelles, qui reprennent le modèle BASE (Basically Available, Soft State, Eventually Consistent, soit succintement : disponibilité d’abord ; incohérence possible entre les réplicas ; cohérence… à terme, après un délai). Un intérêt est de débarrasser le développeur de certaines lourdeurs apparentes liées à la modélisation assez stricte d’une base de données relationnelle. Cependant, la plupart des applications ont d’abord besoin des garanties de sécurité et cohérence qu’offrent un moteur transactionnel classique, et la décision d’utiliser un système ne les garantissant pas ne doit pas être prise à la légère ; sans parler d’autres critères comme la fragmentation du domaine par rapport au monde relationnel et son SQL (à peu près) standardisé. Avec le temps, les moteurs transactionnels ont acquis des fonctionnalités qui faisaient l’intérêt des bases NoSQL (en premier lieu la facilité de réplication et le stockage de JSON), et ces dernières ont tenté d’intégrer un peu plus de sécurité dans leur modèle.


MVCC

  • MultiVersion Concurrency Control
  • Le « noyau » de PostgreSQL
  • Garantit les propriétés ACID
  • Permet les accès concurrents sur la même table
    • une lecture ne bloque pas une écriture
    • une écriture ne bloque pas une lecture
    • une écriture ne bloque pas les autres écritures…
    • …sauf pour la mise à jour de la même ligne

MVCC (Multi Version Concurrency Control) est le mécanisme interne de PostgreSQL utilisé pour garantir la cohérence des données lorsque plusieurs processus accèdent simultanément à la même table.

MVCC maintient toutes les versions nécessaires de chaque ligne, ainsi chaque transaction voit une image figée de la base (appelée snapshot). Cette image correspond à l’état de la base lors du démarrage de la requête ou de la transaction, suivant le niveau d’isolation demandé par l’utilisateur à PostgreSQL pour la transaction.

MVCC fluidifie les mises à jour en évitant les blocages trop contraignants (verrous sur UPDATE) entre sessions et par conséquent de meilleures performances en contexte transactionnel.

C’est notamment MVCC qui permet d’exporter facilement une base à chaud et d’obtenir un export cohérent alors même que plusieurs utilisateurs sont potentiellement en train de modifier des données dans la base.

C’est la qualité de l’implémentation de ce système qui fait de PostgreSQL un des meilleurs SGBD au monde : chaque transaction travaille dans son image de la base, cohérent du début à la fin de ses opérations. Par ailleurs, les écrivains ne bloquent pas les lecteurs et les lecteurs ne bloquent pas les écrivains, contrairement aux SGBD s’appuyant sur des verrous de lignes. Cela assure de meilleures performances, moins de contention et un fonctionnement plus fluide des outils s’appuyant sur PostgreSQL.


Transactions

  • Une transaction = ensemble atomique d’opérations
  • « Tout ou rien »
  • BEGIN obligatoire pour grouper des modifications
  • COMMIT pour valider
    • y compris le DDL
  • Perte des modifications si :
    • ROLLBACK / perte de la connexion / arrêt (brutal ou non) du serveur
  • SAVEPOINT pour sauvegarde des modifications d’une transaction à un instant t
  • Pas de transactions imbriquées

L’exemple habituel et très connu des transactions est celui du virement d’une somme d’argent du compte de Bob vers le compte d’Alice. Le total du compte de Bob ne doit pas montrer qu’il a été débité de X euros tant que le compte d’Alice n’a pas été crédité de X euros. Nous souhaitons en fait que les deux opérations apparaissent aux yeux du reste du système comme une seule opération unitaire. D’où l’emploi d’une transaction explicite. En voici un exemple :

BEGIN;
UPDATE comptes SET solde=solde-200 WHERE proprietaire='Bob';
UPDATE comptes SET solde=solde+200 WHERE proprietaire='Alice';
COMMIT;

Contrairement à d’autres moteurs de bases de données, PostgreSQL accepte aussi les instructions DDL dans une transaction. En voici un exemple :

BEGIN;
CREATE TABLE capitaines (id serial, nom text, age integer);
INSERT INTO capitaines VALUES (1, 'Haddock', 35);
SELECT age FROM capitaines;
 age
---
  35
ROLLBACK;
SELECT age FROM capitaines;
ERROR:  relation "capitaines" does not exist
LINE 1: SELECT age FROM capitaines;
                        ^

Nous voyons que la table capitaines a existé à l’intérieur de la transaction. Mais puisque cette transaction a été annulée (ROLLBACK), la table n’a pas été créée au final.

Cela montre aussi le support du DDL transactionnel au sein de PostgreSQL : PostgreSQL n’effectue aucun COMMIT implicite sur des ordres DDL tels que CREATE TABLE, DROP TABLE ou TRUNCATE TABLE. De ce fait, ces ordres peuvent être annulés au sein d’une transaction.

Un point de sauvegarde est une marque spéciale à l’intérieur d’une transaction qui autorise l’annulation de toutes les commandes exécutées après son établissement, restaurant la transaction dans l’état où elle était au moment de l’établissement du point de sauvegarde.

BEGIN;
CREATE TABLE capitaines (id serial, nom text, age integer);
INSERT INTO capitaines VALUES (1, 'Haddock', 35);
SAVEPOINT insert_sp;
UPDATE capitaines SET age = 45 WHERE nom = 'Haddock';
ROLLBACK TO SAVEPOINT insert_sp;
COMMIT;
SELECT age FROM capitaines WHERE nom = 'Haddock';
 age
---
  35

Malgré le COMMIT après l’UPDATE, la mise à jour n’est pas prise en compte. En effet, le ROLLBACK TO SAVEPOINT a permis d’annuler cet UPDATE mais pas les opérations précédant le SAVEPOINT.

À partir de la version 12, il est possible de chaîner les transactions avec COMMIT AND CHAIN ou ROLLBACK AND CHAIN. Cela veut dire terminer une transaction et en démarrer une autre immédiatement après avec les mêmes propriétés (par exemple, le niveau d’isolation).


Niveaux d’isolation

  • Chaque transaction (et donc session) est isolée à un certain point
    • elle ne voit pas les opérations des autres
    • elle s’exécute indépendamment des autres
  • Nous pouvons spécifier le niveau d’isolation au démarrage d’une transaction
    • BEGIN ISOLATION LEVEL xxx;
  • Niveaux d’isolation supportés
    • read commited (défaut)
    • repeatable read
    • serializable

Chaque transaction, en plus d’être atomique, s’exécute séparément des autres. Le niveau de séparation demandé sera un compromis entre le besoin applicatif (pouvoir ignorer sans risque ce que font les autres transactions) et les contraintes imposées au niveau de PostgreSQL (performances, risque d’échec d’une transaction).

Le standard SQL spécifie quatre niveaux, mais PostgreSQL n’en supporte que trois (il n’y a pas de read uncommitted : les lignes non encore committées par les autres transactions sont toujours invisibles).


Fiabilité : journaux de transactions (1)

Principe de la journalisation

Fiabilité : journaux de transactions (2)

  • Write Ahead Logs (WAL)
  • Chaque donnée est écrite 2 fois sur le disque !
  • Avantages :
    • sécurité infaillible (après COMMIT), intégrité, durabilité
    • écriture séquentielle rapide, et un seul sync sur le WAL
    • fichiers de données écrits en asynchrone
    • sauvegarde PITR et réplication fiables

Les journaux de transactions (appelés souvent WAL) sont une garantie contre les pertes de données. Il s’agit d’une technique standard de journalisation appliquée à toutes les transactions, pour garantir l’intégrité (la base reste cohérente quoiqu’il arrive) et la durabilité (ce qui est validé ne sera pas perdu).

Ainsi lors d’une modification de donnée, l’écriture au niveau du disque se fait généralement en deux temps :

  • écriture des modifications dans le journal de transactions, avec écriture forcée sur disque (« synchronisation ») lors d’un COMMIT ;
  • écriture dans le fichier de données bien plus tard, lors d’un « checkpoint ».

Ainsi en cas de crash :

  • PostgreSQL redémarre ;
  • il vérifie s’il reste des données non intégrées aux fichiers de données dans les journaux (mode recovery) ;
  • si c’est le cas, ces données sont recopiées dans les fichiers de données afin de retrouver un état stable et cohérent ;
  • PostgreSQL peut alors s’ouvrir sans perte des transactions validées lors du crash (une transaction interrompue, et donc jamais validée, est perdue).

Les écritures dans le journal se font de façon séquentielle, donc sans grand déplacement de la tête d’écriture (sur un disque dur classique, c’est l’opération la plus coûteuse). De plus, comme nous n’écrivons que dans un seul fichier de transactions, la synchronisation sur disque, lors d’un COMMIT, peut se faire sur ce seul fichier, si le système de fichiers le supporte. Concrètement, ces journaux sont des fichiers de 16 Mo par défaut, avec des noms comme 0000000100000026000000AF, dans le répertoire pg_wal/ de l’instance PostgreSQL (répertoire souvent sur une partition dédiée).

L’écriture définitive dans les fichiers de données est asynchrone, et généralement lissée, ce qui est meilleur pour les performances qu’une écriture immédiate. Cette opération est appelée « checkpoint » et périodique (5 minutes par défaut, ou plus).

Divers paramètres et fonctionnalités peuvent altérer ce comportement par défaut, par exemple pour des raisons de performances.

À côté de la sécurité et des performances, le mécanisme des journaux de transactions est aussi utilisé pour des fonctionnalités très intéressantes, comme le PITR et la réplication physique, basés sur le rejeu des informations stockées dans ces journaux.

Pour plus d’informations :


Sauvegardes

  • Sauvegarde des fichiers à froid
    • outils système
  • Import/Export logique
    • pg_dump, pg_dumpall, pg_restore
  • Sauvegarde physique à chaud
    • pg_basebackup
    • sauvegarde PITR

PostgreSQL supporte différentes solutions pour la sauvegarde.

La plus simple revient à sauvegarder à froid tous les fichiers des différents répertoires de données mais cela nécessite d’arrêter le serveur, ce qui occasionne une mise hors production plus ou moins longue, suivant la volumétrie à sauvegarder.

L’export logique se fait avec le serveur démarré. Plusieurs outils sont proposés : pg_dump pour sauvegarder une base, pg_dumpall pour sauvegarder toutes les bases. Suivant le format de l’export, l’import se fera avec les outils psql ou pg_restore. Les sauvegardes se font à chaud et sont cohérentes sans blocage de l’activité (seuls la suppression des tables et le changement de leur définition sont interdits).

Enfin, il est possible de sauvegarder les fichiers à chaud. Cela nécessite de mettre en place l’archivage des journaux de transactions. L’outil pg_basebackup est conseillé pour ce type de sauvegarde.

Il est à noter qu’il existe un grand nombre d’outils développés par la communauté pour faciliter encore plus la gestion des sauvegardes avec des fonctionnalités avancées comme le PITR (Point In Time Recovery) ou la gestion de la rétention, notamment pg_back (sauvegarde logique), pgBackRest ou barman (sauvegarde physique).


Réplication

  • Réplication physique
    • instance complète
    • même architecture
  • Réplication logique (PG 10+)
    • table par table / colonne par colonne avec ou sans filtre (PG 15)
    • voire opération par opération
  • Asynchrone ou synchrone
  • Asymétrique

PostgreSQL dispose de la réplication depuis de nombreuses années.

Le premier type de réplication intégrée est la réplication physique. Il n’y a pas de granularité, c’est forcément l’instance complète (toutes les bases de données), et au niveau des fichiers de données. Cette réplication est asymétrique : un seul serveur primaire effectue lectures comme écritures, et les serveurs secondaires n’acceptent que des lectures.

Le deuxième type de réplication est bien plus récent vu qu’il a été ajouté en version 10. Il s’agit d’une réplication logique, où les données elles-mêmes sont répliquées. Cette réplication est elle aussi asymétrique. Cependant, ceci se configure table par table (et non pas au niveau de l’instance comme pour la réplication physique). Avec la version 15, il devient possible de choisir quelles colonnes sont publiées et de filtrer les lignes à publier.

La réplication logique n’est pas intéressante quand nous voulons un serveur sur lequel basculer en cas de problème sur le primaire. Dans ce cas, il vaut mieux utiliser la réplication physique. Par contre, c’est le bon type de réplication pour une réplication partielle ou pour une mise à jour de version majeure.

Dans les deux cas, les modifications sont transmises en asynchrone (avec un délai possible). Il est cependant possible de la configurer en synchrone pour tous les serveurs ou seulement certains.


Extensibilité

  • Extensions
    • CREATE EXTENSION monextension ;
    • nombreuses : contrib, packagées… selon provenance
    • notion de confiance (v13+)
    • dont langages de procédures stockées !
  • Système des hooks
  • Background workers

Faute de pouvoir intégrer toutes les fonctionnalités demandées dans PostgreSQL, ses développeurs se sont attachés à permettre à l’utilisateur d’étendre lui-même les fonctionnalités sans avoir à modifier le code principal.

Ils ont donc ajouté la possibilité de créer des extensions. Une extension contient un ensemble de types de données, de fonctions, d’opérateurs, etc. en un seul objet logique. Il suffit de créer ou de supprimer cet objet logique pour intégrer ou supprimer tous les objets qu’il contient. Cela facilite grandement l’installation et la désinstallation de nombreux objets. Les extensions peuvent être codées en différents langages, généralement en C ou en PL/SQL. Elles ont eu un grand succès.

La possibilité de développer des routines dans différents langages en est un exemple : perl, python, PHP, Ruby ou JavaScript sont disponibles. PL/pgSQL est lui-même une extension à proprement parler, toujours présente.

Autre exemple : la possibilité d’ajouter des types de données, des routines et des opérateurs a permis l’émergence de la couche spatiale de PostgreSQL (appelée PostGIS).

Les provenances, rôle et niveau de finition des extensions sont très variables. Certaines sont des utilitaires éprouvés fournis avec PostgreSQL (parmi les « contrib »). D’autres sont des utilitaires aussi complexes que PostGIS ou un langage de procédures stockées. Des éditeurs diffusent leur produit comme une extension plutôt que forker PostgreSQL (Citus, timescaledb…). Beaucoup d’extensions peuvent être installées très simplement depuis des paquets disponibles dans les dépôts habituels (de la distribution ou du PGDG), ou le site du concepteur. Certaines sont diffusées comme code source à compiler. Comme tout logiciel, il faut faire attention à en vérifier la source, la qualité, la réputation et la pérennité.

Une fois les binaires de l’extension en place sur le serveur, l’ordre CREATE EXTENSION suffit généralement dans la base cible, et les fonctionnalités sont immédiatement exploitables.

Les extensions sont habituellement installées par un administrateur (un utilisateur doté de l’attribut SUPERUSER). À partir de la version 13, certaines extensions sont déclarées de confiance trusted). Ces extensions peuvent être installées par un utilisateur standard (à condition qu’il dispose des droits de création dans la base et le ou les schémas concernés).

Les développeurs de PostgreSQL ont aussi ajouté des hooks pour accrocher du code à exécuter sur certains cas. Cela a permis entre autres de créer l’extension pg_stat_statements qui s’accroche au code de l’exécuteur de requêtes pour savoir quelles sont les requêtes exécutées et pour récupérer des statistiques sur ces requêtes.

Enfin, les background workers ont vu le jour. Ce sont des processus spécifiques lancés par le serveur PostgreSQL lors de son démarrage et stoppés lors de son arrêt. Cela a permis la création de PoWA (outil qui historise les statistiques sur les requêtes) et une amélioration très intéressante de pg_prewarm (sauvegarde du contenu du cache disque à l’arrêt de PostgreSQL, restauration du contenu au démarrage).

Des exemples d’extensions sont décrites dans nos modules Extensions PostgreSQL pour l’utilisateur, Extensions PostgreSQL pour la performance, Extensions PostgreSQL pour les DBA.


Sécurité

  • Fichier pg_hba.conf
  • Filtrage IP
  • Authentification interne (MD5, SCRAM-SHA-256)
  • Authentification externe (identd, LDAP, Kerberos…)
  • Support natif de SSL

Le filtrage des connexions se paramètre dans le fichier de configuration pg_hba.conf. Nous pouvons y définir quels utilisateurs (déclarés auprès de PostgreSQL) peuvent se connecter à quelles bases, et depuis quelles adresses IP.

L’authentification peut se baser sur des mots de passe chiffrés propres à PostgreSQL (md5 ou le plus récent et plus sécurisé scram-sha-256 en version 10), ou se baser sur une méthode externe (auprès de l’OS, ou notamment LDAP ou Kerberos qui couvre aussi Active Directory).

Si PostgreSQL interroge un service de mots de passe centralisé, vous devez toujours créer les rôle dans PostgreSQL. Seule l’option WITH PASSWORD est inutile. Pour créer, configurer mais aussi supprimer les rôles depuis un annuaire, l’outil ldap2pg existe.

L’authentification et le chiffrement de la connexion par SSL sont couverts.


Objets SQL

  • Instances
  • Objets globaux :
    • Bases
    • Rôles
    • Tablespaces
  • Objets locaux :
    • Schémas
    • Tables
    • Vues
    • Index
    • Routines

Le but de cette partie est de passer en revue les différents objets logiques maniés par un moteur de bases de données PostgreSQL.

Nous allons donc aborder la notion d’instance, les différents objets globaux et les objets locaux. Tous ne seront pas vus, mais le but est de donner une idée globale des objets et des fonctionnalités de PostgreSQL.


Organisation logique

Organisation logique d’une instance

Il est déjà important de bien comprendre une distinction entre les objets. Une instance est un ensemble de bases de données, de rôles et de tablespaces. Ces objets sont appelés des objets globaux parce qu’ils sont disponibles quelque soit la base de données de connexion. Chaque base de données contient ensuite des objets qui lui sont propres. Ils sont spécifiques à cette base de données et accessibles uniquement lorsque l’utilisateur est connecté à la base qui les contient. Il est donc possible de voir les bases comme des conteneurs hermétiques en dehors des objets globaux.


Instances

  • Une instance
    • un répertoire de données
    • un port TCP
    • une configuration
    • plusieurs bases de données
  • Plusieurs instances possibles sur un serveur

Une instance est un ensemble de bases de données. Après avoir installé PostgreSQL, il est nécessaire de créer un répertoire de données contenant un certain nombre de répertoires et de fichiers qui permettront à PostgreSQL de fonctionner de façon fiable. Le contenu de ce répertoire est créé initialement par la commande initdb. Ce répertoire stocke ensuite tous les objets des bases de données de l’instance, ainsi que leur contenu.

Chaque instance a sa propre configuration. Il n’est possible de lancer qu’un seul postmaster par instance, et ce dernier acceptera les connexions à partir d’un port TCP spécifique.

Il est possible d’avoir plusieurs instances sur le même serveur, physique ou virtuel. Dans ce cas, chaque instance aura son répertoire de données dédié et son port TCP dédié. Ceci est particulièrement utile quand l’on souhaite disposer de plusieurs versions de PostgreSQL sur le même serveur (par exemple pour tester une application sur ces différentes versions).


Rôles

  • Utilisateurs / Groupes
    • Utilisateur : Permet de se connecter
  • Différents attributs et droits

Une instance contient un ensemble de rôles. Certains sont prédéfinis et permettent de disposer de droits particuliers (lecture de fichier avec pg_read_server_files, annulation d’une requête avec pg_signal_backend, etc). Cependant, la majorité est composée de rôles créés pour permettre la connexion des utilisateurs.

Chaque rôle créé peut être utilisé pour se connecter à n’importe quelle base de l’instance, à condition que ce rôle en ait le droit. Ceci se gère directement avec l’attribution du droit LOGIN au rôle, et avec la configuration du fichier d’accès pg_hba.conf.

Chaque rôle peut être propriétaire d’objets, auquel cas il a tous les droits sur ces objets. Pour les objets dont il n’est pas propriétaire, il peut se voir donner des droits, en lecture, écriture, exécution, etc par le propriétaire.

Nous parlons aussi d’utilisateurs et de groupes. Un utilisateur est un rôle qui a la possibilité de se connecter aux bases alors qu’un groupe ne le peut pas. Un groupe sert principalement à gérer plus simplement les droits d’accès aux objets.


Tablespaces

  • Répertoire physique contenant les fichiers de données de l’instance
  • Une base peut
    • se trouver sur un seul tablespace
    • être répartie sur plusieurs tablespaces
  • Permet de gérer l’espace disque et les performances
  • Pas de quota

Toutes les données des tables, vues matérialisées et index sont stockées dans le répertoire de données principal. Cependant, il est possible de stocker des données ailleurs que dans ce répertoire. Il faut pour cela créer un tablespace. Un tablespace est tout simplement la déclaration d’un autre répertoire de données utilisable par PostgreSQL pour y stocker des données :

CREATE TABLESPACE chaud LOCATION '/SSD/tbl/chaud';

Il est possible d’avoir un tablespace par défaut pour une base de données, auquel cas tous les objets logiques créés dans cette base seront enregistrés physiquement dans le répertoire lié à ce tablespace. Il est aussi possible de créer des objets en indiquant spécifiquement un tablespace, ou de les déplacer d’un tablespace à un autre. Un objet spécifique ne peut appartenir qu’à un seul tablespace (autrement dit, un index ne pourra pas être enregistré sur deux tablespaces). Cependant, pour les objets partitionnés, le choix du tablespace peut se faire partition par partition.

Le but des tablespaces est de fournir une solution à des problèmes d’espace disque ou de performances. Si la partition où est stocké le répertoire des données principal se remplit fortement, il est possible de créer un tablespace dans une autre partition et donc d’utiliser l’espace disque de cette partition. Si de nouveaux disques plus rapides sont à disposition, il est possible de placer les objets fréquemment utilisés sur le tablespace contenant les disques rapides. Si des disques SSD sont à disposition, il est très intéressant d’y placer les index, les fichiers de tri temporaires, des tables de travail…

Par contre, contrairement à d’autres moteurs de bases de données, PostgreSQL n’a pas de notion de quotas. Les tablespaces ne peuvent donc pas être utilisés pour contraindre l’espace disque utilisé par certaines applications ou certains rôles.


Bases

  • Conteneur hermétique
  • Un rôle ne se connecte pas à une instance
    • il se connecte forcément à une base
  • Une fois connecté, il ne voit que les objets de cette base
    • contournement : foreign data wrappers, dblink

Une base de données est un conteneur hermétique. En dehors des objets globaux, le rôle connecté à une base de données ne voit et ne peut interagir qu’avec les objets contenus dans cette base. De même, il ne voit pas les objets locaux des autres bases. Néanmoins, il est possible de lui donner le droit d’accéder à certains objets d’une autre base (de la même instance ou d’une autre instance) en utilisant les Foreign Data Wrappers (postgres_fdw) ou l’extension dblink.

Un rôle ne se connecte pas à l’instance. Il se connecte forcément à une base spécifique.


Schémas

  • Espace de noms
  • Sous-ensemble de la base
  • Non lié à un utilisateur
  • Résolution des objets : search_path
  • pg_catalog, information_schema
    • pour catalogues système (lecture seule !)

Les schémas sont des espaces de noms à l’intérieur d’une base de données permettant :

  • de grouper logiquement les objets d’une base de données ;
  • de séparer les utilisateurs entre eux ;
  • de contrôler plus efficacement les accès aux données ;
  • d’éviter les conflits de noms dans les grosses bases de données.

Un schéma n’a à priori aucun lien avec un utilisateur donné.

Un schéma est un espace logique sans lien avec les emplacements physiques des données (ne pas confondre avec les tablespaces).

Un utilisateur peut avoir accès à tous les schémas ou à un sous-ensemble, tout dépend des droits dont il dispose. Depuis la version 15, un nouvel utilisateur n’a le droit de créer d’objet nulle part. Dans les versions précédentes, il avait accès au schéma public de chaque base et pouvait y créer des objets.

Lorsque le schéma n’est pas indiqué explicitement pour les objets d’une requête, PostgreSQL recherche les objets dans les schémas listés par le paramètre search_path valable pour la session en cours .

Voici un exemple d’utilisation des schémas :

-- Création de deux schémas
CREATE SCHEMA s1;
CREATE SCHEMA s2;

-- Création d'une table sans spécification du schéma
CREATE TABLE t1 (id integer);

-- Comme le montre la méta-commande \d, la table est créée dans le schéma public
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | capitaines        | table    | postgres
 public | capitaines_id_seq | sequence | postgres
 public | t1                | table    | postgres
-- Ceci est dû à la configuration par défaut du paramètre search_path
-- modification du search_path
SET search_path TO s1;

-- création d'une nouvelle table sans spécification du schéma
CREATE TABLE t2 (id integer);

-- Cette fois, le schéma de la nouvelle table est s1
-- car la configuration du search_path est à s1
-- Nous pouvons aussi remarquer que les tables capitaines et s1
-- ne sont plus affichées
-- Ceci est dû au fait que le search_path ne contient que le schéma s1 et
-- n'affiche donc que les objets de ce schéma.
postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 s1     | t2   | table | postgres
-- Nouvelle modification du search_path
SET search_path TO s1, public;

-- Cette fois, les deux tables apparaissent
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | capitaines        | table    | postgres
 public | capitaines_id_seq | sequence | postgres
 public | t1                | table    | postgres
 s1     | t2                | table    | postgres
-- Création d'une nouvelle table en spécifiant cette fois le schéma
CREATE TABLE s2.t3 (id integer);

-- changement du search_path pour voir la table
SET search_path TO s1, s2, public;

-- La table apparaît bien, et le schéma d'appartenance est bien s2
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | capitaines        | table    | postgres
 public | capitaines_id_seq | sequence | postgres
 public | t1                | table    | postgres
 s1     | t2                | table    | postgres
 s2     | t3                | table    | postgres
-- Création d'une nouvelle table en spécifiant cette fois le schéma
-- attention, cette table a un nom déjà utilisé par une autre table
CREATE TABLE s2.t2 (id integer);

-- La création se passe bien car, même si le nom de la table est identique,
-- le schéma est différent
-- Par contre, \d ne montre que la première occurence de la table
-- ici, nous ne voyons t2 que dans s1
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | capitaines        | table    | postgres
 public | capitaines_id_seq | sequence | postgres
 public | t1                | table    | postgres
 s1     | t2                | table    | postgres
 s2     | t3                | table    | postgres
-- Changeons le search_path pour placer s2 avant s1
SET search_path TO s2, s1, public;

-- Maintenant, la seule table t2 affichée est celle du schéma s2
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | capitaines        | table    | postgres
 public | capitaines_id_seq | sequence | postgres
 public | t1                | table    | postgres
 s2     | t2                | table    | postgres
 s2     | t3                | table    | postgres

Tous ces exemples se basent sur des ordres de création de table. Cependant, le comportement serait identique sur d’autres types de commande (SELECT, INSERT, etc) et sur d’autres types d’objets locaux.

Pour des raisons de sécurité, il est très fortement conseillé de laisser le schéma public en toute fin du search_path. En effet, avant la version 15, s’il est placé au début, comme tout le monde avait le droit de créer des objets dans public, quelqu’un de mal intentionné pouvait placer un objet dans le schéma public pour servir de proxy à un autre objet d’un schéma situé après public. Même si la version 15 élimine ce risque, il reste la bonne pratique d’adapter le search_path pour placer les schémas applicatifs en premier.

Les schémas pg_catalog et information_schema contiennent des tables utilitaires (« catalogues système ») et des vues. Les catalogues système représentent l’endroit où une base de données relationnelle stocke les métadonnées des schémas, telles que les informations sur les tables, et les colonnes, et des données de suivi interne. Dans PostgreSQL, ce sont de simples tables. Un simple utilisateur lit fréquemment ces tables, plus ou moins directement, mais n’a aucune raison d’y modifier des données. Toutes les opérations habituelles pour un utilisateur ou administrateur sont disponibles sous la forme de commandes SQL.

Ne modifiez jamais directement les tables et vues système dans les schémas pg_catalog et information_schema ; n’y ajoutez ni n’y effacez jamais rien !

Même si cela est techniquement possible, seules des exceptions particulièrement ésotériques peuvent justifier une modification directe des tables systèmes (par exemple, une correction de vue système, suite à un bug corrigé dans une version mineure). Ces tables n’apparaissent d’ailleurs pas dans une sauvegarde logique (pg_dump).


Tables

Par défaut, une table est :

  • Permanente
    • si temporaire, vivra le temps de la session (ou de la transaction)
  • Journalisée
    • si unlogged, perdue en cas de crash, pas de réplication
  • Non partitionnée
    • partitionnement possible par intervalle, valeur ou hachage

Par défaut, les tables sont permanentes, journalisées et non partitionnées.

Il est possible de créer des tables temporaires (CREATE TEMPORARY TABLE). Celles-ci ne sont visibles que par la session qui les a créées et seront supprimées par défaut à la fin de cette session. Il est aussi possible de les supprimer automatiquement à la fin de la transaction qui les a créées. Il n’existe pas dans PostgreSQL de notion de table temporaire globale. Cependant, une extension existe pour combler leur absence.

Pour des raisons de performance, il est possible de créer une table non journalisée (CREATE UNLOGGED TABLE). La définition de la table est journalisée mais pas son contenu. De ce fait, en cas de crash, il est impossible de dire si la table est corrompue ou non, et donc, au redémarrage du serveur, PostgreSQL vide la table de tout contenu. De plus, n’étant pas journalisée, la table n’est pas présente dans les sauvegardes PITR, ni repliquée vers d’éventuels serveurs secondaires.

Enfin, depuis la version 10, il est possible de partitionner les tables suivant un certain type de partitionnement : par intervalle, par valeur ou par hachage.


Vues

  • Masquer la complexité
    • structure : interface cohérente vers les données, même si les tables évoluent
    • sécurité : contrôler l’accès aux données de manière sélective
  • Vues matérialisées
    • à rafraîchir à une certaine fréquence

Le but des vues est de masquer une complexité, qu’elle soit du côté de la structure de la base ou de l’organisation des accès. Dans le premier cas, elles permettent de fournir un accès qui ne change pas même si les structures des tables évoluent. Dans le second cas, elles permettent l’accès à seulement certaines colonnes ou certaines lignes. De plus, les vues étant exécutées avec les mêmes droits que l’utilisateur qui les a créées, cela permet un changement temporaire des droits d’accès très appréciable dans certains cas.

Voici un exemple d’utilisation :

SET search_path TO public;

-- création de l'utilisateur guillaume
-- il n'aura pas accès à la table capitaines
-- par contre, il aura accès à la vue capitaines_anon
CREATE ROLE guillaume LOGIN;

-- ajoutons une colonne à la table capitaines
-- et ajoutons-y des données
ALTER TABLE capitaines ADD COLUMN num_cartecredit text;
INSERT INTO capitaines (nom, age, num_cartecredit)
  VALUES ('Robert Surcouf', 20, '1234567890123456');

-- création de la vue
CREATE VIEW capitaines_anon AS
  SELECT nom, age, substring(num_cartecredit, 0, 10) || '******' AS num_cc_anon
  FROM capitaines;

-- ajout du droit de lecture à l'utilisateur guillaume
GRANT SELECT ON TABLE capitaines_anon TO guillaume;

-- connexion en tant qu'utilisateur guillaume
SET ROLE TO guillaume;

-- vérification qu'on lit bien la vue mais pas la table
SELECT * FROM capitaines_anon WHERE nom LIKE '%Surcouf';
      nom       | age |   num_cc_anon
----------------+-----+-----------------
 Robert Surcouf |  20 | 123456789******
-- tentative de lecture directe de la table
SELECT * FROM capitaines;
ERROR:  permission denied for relation capitaines

Il est possible de modifier une vue en lui ajoutant des colonnes à la fin, au lieu de devoir les détruire et recréer (ainsi que toutes les vues qui en dépendent, ce qui peut être fastidieux).

Par exemple :

SET ROLE postgres;

CREATE OR REPLACE VIEW capitaines_anon AS SELECT
  nom,age,substring(num_cartecredit,0,10)||'******' AS num_cc_anon,
  md5(substring(num_cartecredit,0,10)) AS num_md5_cc
  FROM capitaines;
SELECT * FROM capitaines_anon WHERE nom LIKE '%Surcouf';
      nom       | age |   num_cc_anon   |            num_md5_cc
----------------+-----+-----------------+----------------------------------
 Robert Surcouf |  20 | 123456789****** | 25f9e794323b453885f5181f1b624d0b

Nous pouvons aussi modifier les données au travers des vues simples, sans ajout de code et de trigger :

UPDATE capitaines_anon SET nom = 'Nicolas Surcouf' WHERE nom = 'Robert Surcouf';
SELECT * from capitaines_anon WHERE nom LIKE '%Surcouf';
       nom       | age |   num_cc_anon   |            num_md5_cc
-----------------+-----+-----------------+----------------------------------
 Nicolas Surcouf |  20 | 123456789****** | 25f9e794323b453885f5181f1b624d0b
UPDATE capitaines_anon SET num_cc_anon = '123456789xxxxxx'
  WHERE nom = 'Nicolas Surcouf';
ERROR:  cannot update column "num_cc_anon" of view "capitaines_anon"
DETAIL:  View columns that are not columns of their base relation
         are not updatable.

PostgreSQL gère le support natif des vues matérialisées (CREATE MATERIALIZED VIEW nom_vue_mat AS SELECT …). Les vues matérialisées sont des vues dont le contenu est figé sur disque, permettant de ne pas recalculer leur contenu à chaque appel. De plus, il est possible de les indexer pour accélérer leur consultation. Il faut cependant faire attention à ce que leur contenu reste synchrone avec le reste des données.

Les vues matérialisées ne sont pas mises à jour automatiquement, il faut demander explicitement le rafraîchissement (REFRESH MATERIALIZED VIEW). Avec la clause CONCURRENTLY, s’il y a un index d’unicité, le rafraîchissement ne bloque pas les sessions lisant en même temps les données d’une vue matérialisée.

-- Suppression de la vue
DROP VIEW capitaines_anon;

-- Création de la vue matérialisée
CREATE MATERIALIZED VIEW capitaines_anon AS
  SELECT nom,
    age,
    substring(num_cartecredit, 0, 10) || '******' AS num_cc_anon
  FROM capitaines;
-- Les données sont bien dans la vue matérialisée
SELECT * FROM capitaines_anon WHERE nom LIKE '%Surcouf';
       nom       | age |   num_cc_anon
-----------------+-----+-----------------
 Nicolas Surcouf |  20 | 123456789******
-- Mise à jour d'une ligne de la table
-- Cette mise à jour est bien effectuée, mais la vue matérialisée
-- n'est pas impactée
UPDATE capitaines SET nom = 'Robert Surcouf' WHERE nom = 'Nicolas Surcouf';
SELECT * FROM capitaines WHERE nom LIKE '%Surcouf';
 id |      nom       | age | num_cartecredit
----+----------------+-----+------------------
  1 | Robert Surcouf |  20 | 1234567890123456
SELECT * FROM capitaines_anon WHERE nom LIKE '%Surcouf';
       nom       | age |   num_cc_anon
-----------------+-----+-----------------
 Nicolas Surcouf |  20 | 123456789******
-- Le résultat est le même mais le plan montre bien que PostgreSQL ne passe
-- plus par la table mais par la vue matérialisée :
EXPLAIN SELECT * FROM capitaines_anon WHERE nom LIKE '%Surcouf';
                         QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on capitaines_anon  (cost=0.00..20.62 rows=1 width=68)
   Filter: (nom ~~ '%Surcouf'::text)
-- Après un rafraîchissement explicite de la vue matérialisée,
-- cette dernière contient bien les bonnes données
REFRESH MATERIALIZED VIEW capitaines_anon;

SELECT * FROM capitaines_anon WHERE nom LIKE '%Surcouf';
      nom       | age |   num_cc_anon
----------------+-----+-----------------
 Robert Surcouf |  20 | 123456789******
-- Pour rafraîchir la vue matérialisée sans bloquer les autres sessions :

REFRESH MATERIALIZED VIEW CONCURRENTLY capitaines_anon;
ERROR:  cannot refresh materialized view "public.capitaines_anon" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns
       of the materialized view.
-- En effet, il faut un index d'unicité pour faire un rafraîchissement
-- sans bloquer les autres sessions.
CREATE UNIQUE INDEX ON capitaines_anon(nom);
REFRESH MATERIALIZED VIEW CONCURRENTLY capitaines_anon;

Index

  • Algorithmes supportés
    • B-tree (par défaut)
    • Hash
    • GiST / SP-GiST
    • GIN
    • BRIN
    • Bloom
  • Type
    • Mono ou multicolonne
    • Partiel
    • Fonctionnel
    • Couvrant

PostgreSQL propose plusieurs algorithmes d’index.

Pour une indexation standard, nous utilisons en général un index B-tree, de par ses nombreuses possibilités et ses très bonnes performances.

Les index hash sont peu utilisés, essentiellement dans la comparaison d’égalité de grandes chaînes de caractères.

Moins simples d’abord, les index plus spécifiques (GIN, GIST) sont spécialisés pour les grands volumes de données complexes et multidimensionnelles : indexation textuelle, géométrique, géographique, ou de tableaux de données par exemple.

Les index BRIN sont des index très compacts destinés aux grandes tables où les données sont fortement corrélées par rapport à leur emplacement physique sur les disques.

Les index bloom sont des index probabilistes visant à indexer de nombreuses colonnes interrogées simultanément. Ils nécessitent l’ajout d’une extension (nommée bloom). Contrairement aux index btree, les index bloom ne dépendent pas de l’ordre des colonnes.

Le module pg_trgm permet l’utilisation d’index dans des cas habituellement impossibles, comme les expressions rationnelles et les LIKE '%...%'.

Généralement, l’indexation porte sur la valeur d’une ou plusieurs colonnes. Il est néanmoins possible de n’indexer qu’une partie des lignes (index partiel) ou le résultat d’une fonction sur une ou plusieurs colonnes en paramètre. Enfin, il est aussi possible de modifier les index de certaines contraintes (unicité et clé primaire) pour inclure des colonnes supplémentaires.

Plus d’informations :


Types de données

  • Types de base
    • natif : int, float
    • standard SQL : numeric, char, varchar, date, time, timestamp, bool
  • Type complexe
    • tableau
    • JSON (jsonb), XML
    • vecteur (données LLM, FTS)
  • Types métier
    • réseau, géométrique, etc.
  • Types créés par les utilisateurs
    • structure SQL, C, Domaine, Enum

PostgreSQL dispose d’un grand nombre de types de base, certains natifs (comme la famille des integer et celle des float), et certains issus de la norme SQL (numeric, char, varchar, date, time, timestamp, bool).

Il dispose aussi de types plus complexes. Les tableaux (array) permettent de lister un ensemble de valeurs discontinues. Les intervalles (range) permettent d’indiquer toutes les valeurs comprises entre une valeur de début et une valeur de fin. Ces deux types dépendent évidemment d’un type de base : tableau d’entiers, intervalle de dates, etc. Existent aussi les types complexes, les données XML et JSON (préférer le type optimisé jsonb).

PostgreSQL sait travailler avec des vecteurs pour des calculs avancé. De base, le type tsvector permet la recherche plein texte, avec calcul de proximité de mots dans un texte, pondération des résultats, etc. L’extension pgvector permet de stocker et d’indexer des vecteurs utilisé par les algorithmes LLM implémentés dans les IA génératives.

Enfin, il existe des types métiers ayant trait principalement au réseau (adresse IP, masque réseau), à la géométrie (point, ligne, boite). Certains sont apportés par des extensions.

Tout ce qui vient d’être décrit est natif. Il est cependant possible de créer ses propres types de données, soit en SQL soit en C. Les possibilités et les performances ne sont évidemment pas les mêmes.

Voici comment créer un type en SQL :

CREATE TYPE serveur AS (
  nom             text,
  adresse_ip      inet,
  administrateur  text
);

Ce type de données va pouvoir être utilisé dans tous les objets SQL habituels : table, routine, opérateur (pour redéfinir l’opérateur + par exemple), fonction d’agrégat, contrainte, etc.

Voici un exemple de création d’un opérateur :

CREATE OPERATOR + (
    leftarg = stock,
    rightarg = stock,
    procedure = stock_fusion,
    commutator = +
);

(Il faut au préalable avoir défini le type stock et la fonction stock_fusion.)

Il est aussi possible de définir des domaines. Ce sont des types créés par les utilisateurs à partir d’un type de base et en lui ajoutant des contraintes supplémentaires.

En voici un exemple :

CREATE DOMAIN code_postal_francais AS text CHECK (value ~ '^\d{5}$');
ALTER TABLE capitaines ADD COLUMN cp code_postal_francais;
UPDATE capitaines SET cp = '35400' WHERE nom LIKE '%Surcouf';
UPDATE capitaines SET cp = '1420' WHERE nom = 'Haddock';
ERROR:  value for domain code_postal_francais violates check constraint
        "code_postal_francais_check"
UPDATE capitaines SET cp = '01420' WHERE nom = 'Haddock';
SELECT * FROM capitaines;
 id |      nom       | age | num_cartecredit  |  cp
----+----------------+-----+------------------+-------
  1 | Robert Surcouf |  20 | 1234567890123456 | 35400
  1 | Haddock        |  35 |                  | 01420

Les domaines permettent d’intégrer la déclaration des contraintes à la déclaration d’un type, et donc de simplifier la maintenance de l’application si ce type peut être utilisé dans plusieurs tables : si la définition du code postal est insuffisante pour une évolution de l’application, il est possible de la modifier par un ALTER DOMAIN, et définir de nouvelles contraintes sur le domaine. Ces contraintes seront vérifiées sur l’ensemble des champs ayant le domaine comme type avant que la nouvelle version du type ne soit considérée comme valide.

Le défaut par rapport à des contraintes CHECK classiques sur une table est que l’information ne se trouvant pas dans la table, les contraintes sont plus difficiles à lister sur une table.

Enfin, il existe aussi les enums. Ce sont des types créés par les utilisateurs composés d’une liste ordonnée de chaînes de caractères.

En voici un exemple :

CREATE TYPE jour_semaine
  AS ENUM ('Lundi', 'Mardi', 'Mercredi', 'Jeudi', 'Vendredi',
  'Samedi', 'Dimanche');

ALTER TABLE capitaines ADD COLUMN jour_sortie jour_semaine;

UPDATE capitaines SET jour_sortie = 'Mardi' WHERE nom LIKE '%Surcouf';
UPDATE capitaines SET jour_sortie = 'Samedi' WHERE nom LIKE 'Haddock';

SELECT * FROM capitaines WHERE jour_sortie >= 'Jeudi';
 id |   nom   | age | num_cartecredit | cp | jour_sortie
----+---------+-----+-----------------+----+-------------
  1 | Haddock |  35 |                 |    | Samedi

Les enums permettent de déclarer une liste de valeurs statiques dans le dictionnaire de données plutôt que dans une table externe sur laquelle il faudrait rajouter des jointures : dans l’exemple, nous aurions pu créer une table jour_de_la_semaine, et stocker la clé associée dans planning. Nous aurions pu tout aussi bien positionner une contrainte CHECK, mais nous n’aurions plus eu une liste ordonnée.

Exemple d’utilisation :


Contraintes

  • CHECK
    • prix > 0
  • NOT NULL
    • id_client NOT NULL
  • Unicité
    • id_client UNIQUE
  • Clés primaires
    • UNIQUE NOT NULL ==> PRIMARY KEY (id_client)
  • Clés étrangères
    • produit_id REFERENCES produits(id_produit)
  • EXCLUDE
    • EXCLUDE USING gist (room WITH =, during WITH &&)

Les contraintes sont la garantie de conserver des données de qualité ! Elles permettent une vérification qualitative des données, beaucoup plus fine qu’en définissant uniquement un type de données.

Les exemples ci-dessus reprennent :

  • un prix qui doit être strictement positif ;
  • un identifiant qui ne doit pas être vide (sinon des jointures filtreraient des lignes) ;
  • une valeur qui doit être unique (comme des numéros de clients ou de facture) ;
  • une clé primaire (unique non nulle), qui permet d’identifier précisément une ligne ;
  • une clé étrangère vers la clé primaire d’une autre table (là encore pour garantir l’intégrité des jointures) ;
  • une contrainte d’exclusion interdisant que deux plages temporelles se recouvrent dans la réservation de la même salle de réunion.

Les contraintes d’exclusion permettent un test sur plusieurs colonnes avec différents opérateurs (et non uniquement l’égalité, comme dans le cas d’une contrainte unique, qui n’est qu’une contrainte d’exclusion très spécialisée). Si le test se révèle positif, la ligne est refusée.

Une contrainte peut porter sur plusieurs champs et un champ peut être impliqué dans plusieurs contraintes :

CREATE TABLE commandes (
    no_commande     varchar(16) CHECK (no_commande ~ '^[A-Z0-9]*$'),
    id_entite_commerciale int REFERENCES entites_commerciales,
    id_client       int       REFERENCES clients,
    date_commande   date      NOT NULL,
    date_livraison  date      CHECK (date_livraison >= date_commande),
    PRIMARY KEY (no_commande, id_entite_commerciale)
);
\d commandes
                                Table « public.commandes »
        Colonne        |         Type          | … | NULL-able | Par défaut 
-----------------------+-----------------------+---+-----------+------------
 no_commande           | character varying(16) |   | not null  | 
 id_entite_commerciale | integer               |   | not null  | 
 id_client             | integer               |   |           | 
 date_commande         | date                  |   | not null  | 
 date_livraison        | date                  |   |           | 
Index :
    "commandes_pkey" PRIMARY KEY, btree (no_commande, id_entite_commerciale)
Contraintes de vérification :
    "commandes_check" CHECK (date_livraison >= date_commande)
    "commandes_no_commande_check" CHECK (no_commande::text ~ '^[A-Z0-9]*$'::text)
Contraintes de clés étrangères :
    "commandes_id_client_fkey" FOREIGN KEY (id_client) REFERENCES clients(id_client)
    "commandes_id_entite_commerciale_fkey" FOREIGN KEY (id_entite_commerciale) REFERENCES entites_commerciales(id_entite_commerciale)

Les contraintes doivent être vues comme la dernière ligne de défense de votre application face aux bugs. En effet, le code d’une application change beaucoup plus souvent que le schéma, et les données survivent souvent à l’application, qui peut être réécrite entretemps. Quoi qu’il se passe, des contraintes judicieuses garantissent qu’il n’y aura pas d’incohérence logique dans la base.

Si elles sont gênantes pour le développeur (car elles imposent un ordre d’insertion ou de mise à jour), il faut se rappeler que les contraintes peuvent être « débrayées » le temps d’une transaction :

BEGIN;
SET CONSTRAINTS ALL DEFERRED ;

COMMIT ;

Les contraintes ne seront validées qu’au COMMIT.

Sur le sujet, voir par exemple Constraints: a Developer’s Secret Weapon de Will Leinweber (pgDay Paris 2018) (slides, vidéo).

Du point de vue des performances, les contraintes permettent au planificateur d’optimiser les requêtes. Par exemple, le planificateur sait ne pas prendre en compte certaines jointures, notamment grâce à l’existence d’une contrainte d’unicité. (Sur ce point, la version 15 améliore les contraintes d’unicité en permettant de choisir si la valeur NULL est considérée comme unique ou pas. Par défaut et historiquement, une valeur NULL n’étant pas égale à une valeur NULL, les valeurs NULL sont considérées distinctes, et donc on peut avoir plusieurs valeurs NULL dans une colonne ayant une contrainte d’unicité.)


Colonnes à valeur générée

  • Valeur calculée à l’insertion
  • DEFAULT
  • Identité
    • GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  • Expression
    • GENERATED ALWAYS AS ( generation_expr ) STORED

Une colonne a par défaut la valeur NULL si aucune valeur n’est fournie lors de l’insertion de la ligne. Il existe néanmoins trois cas où le moteur peut substituer une autre valeur.

Le plus connu correspond à la clause DEFAULT. Dans ce cas, la valeur insérée correspond à la valeur indiquée avec cette clause si aucune valeur n’est indiquée pour la colonne. Si une valeur est précisée, cette valeur surcharge la valeur par défaut. L’exemple suivant montre cela :

CREATE TABLE t2 (c1 integer, c2 integer, c3 integer DEFAULT 10);
INSERT INTO t2 (c1, c2, c3) VALUES (1, 2, 3);
INSERT INTO t2 (c1) VALUES (2);
SELECT * FROM t2;
 c1 | c2 | c3
----+----+----
  1 |  2 |  3
  2 |    | 10

La clause DEFAULT ne peut pas être utilisée avec des clauses complexes, notamment des clauses comprenant des requêtes.

Pour aller un peu plus loin, à partir de PostgreSQL 12, il est possible d’utiliser GENERATED ALWAYS AS ( expression ) STORED. Cela permet d’avoir une valeur calculée pour la colonne, valeur qui ne peut pas être surchargée, ni à l’insertion, ni à la mise à jour (mais qui est bien stockée sur le disque).

Comme exemple, nous allons reprendre la table capitaines et lui ajouter une colonne ayant comme valeur la version modifiée du numéro de carte de crédit :

ALTER TABLE capitaines
  ADD COLUMN num_cc_anon text
  GENERATED ALWAYS AS (substring(num_cartecredit, 0, 10) || '******') STORED;

SELECT nom, num_cartecredit, num_cc_anon FROM capitaines;
      nom       | num_cartecredit  |   num_cc_anon
----------------+------------------+-----------------
 Robert Surcouf | 1234567890123456 | 123456789******
 Haddock        |                  |
INSERT INTO capitaines VALUES
  (2, 'Joseph Pradere-Niquet', 40, '9876543210987654', '44000', 'Lundi', 'test');
ERROR:  cannot insert into column "num_cc_anon"
DETAIL:  Column "num_cc_anon" is a generated column.
INSERT INTO capitaines VALUES
  (2, 'Joseph Pradere-Niquet', 40, '9876543210987654', '44000', 'Lundi');
SELECT nom, num_cartecredit, num_cc_anon FROM capitaines;
          nom          | num_cartecredit  |   num_cc_anon
-----------------------+------------------+-----------------
 Robert Surcouf        | 1234567890123456 | 123456789******
 Haddock               |                  |
 Joseph Pradere-Niquet | 9876543210987654 | 987654321******

Enfin, GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY permet d’obtenir une colonne d’identité, bien meilleure que ce que le pseudo-type serial propose. Si ALWAYS est indiqué, la valeur n’est pas modifiable.

ALTER TABLE capitaines
  ADD COLUMN id2 integer GENERATED ALWAYS AS IDENTITY;
SELECT nom, id2 FROM capitaines;
          nom          | id2
-----------------------+-----
 Robert Surcouf        |   1
 Haddock               |   2
 Joseph Pradere-Niquet |   3
INSERT INTO capitaines (nom) VALUES ('Tom Souville');
SELECT nom, id2 FROM capitaines;
          nom          | id2
-----------------------+-----
 Robert Surcouf        |   1
 Haddock               |   2
 Joseph Pradere-Niquet |   3
 Tom Souville          |   4

Le type serial est remplacé par le type integer et une séquence comme le montre l’exemple suivant. C’est un problème dans la mesure où la déclaration qui est faite à la création de la table produit un résultat différent en base et donc dans les exports de données.

CREATE TABLE tserial(s serial);
                            Table "public.tserial"
 Column |  Type   | Collation | Nullable |              Default
--------+---------+-----------+----------+------------------------------------
 s      | integer |           | not null | nextval('tserial_s_seq'::regclass)

Langages

  • Procédures & fonctions en différents langages
  • Par défaut : SQL, C et PL/pgSQL
  • Extensions officielles : Perl, Python
  • Mais aussi Java, Ruby, Javascript…
  • Intérêts : fonctionnalités, performances

Les langages officiellement supportés par le projet sont :

Voici une liste non exhaustive des langages procéduraux disponibles, à différents degrés de maturité :

Le wiki PostgreSQL contient un tableau des langages supportés.

Pour qu’un langage soit utilisable, il doit être activé au niveau de la base où il sera utilisé. Les trois langages activés par défaut sont le C, le SQL et le PL/pgSQL. Les autres doivent être ajoutés à partir des paquets de la distribution ou du PGDG, ou compilés à la main, puis l’extension installée dans la base :

CREATE EXTENSION plperl ;
CREATE EXTENSION plpython3u ;
-- etc.

Ces fonctions peuvent être utilisées dans des index fonctionnels et des triggers comme toute fonction SQL ou PL/pgSQL.

Chaque langage a ses avantages et inconvénients. Par exemple, PL/pgSQL est très simple à apprendre mais n’est pas performant quand il s’agit de traiter des chaînes de caractères. Pour ce traitement, il est souvent préférable d’utiliser PL/Perl, voire PL/Python. Évidemment, une routine en C aura les meilleures performances mais sera beaucoup moins facile à coder et à maintenir, et ses bugs seront susceptibles de provoquer un plantage du serveur.

Par ailleurs, les procédures peuvent s’appeler les unes les autres quel que soit le langage. S’ajoute l’intérêt de ne pas avoir à réécrire en PL/pgSQL des fonctions existantes dans d’autres langages ou d’accéder à des modules bien établis de ces langages.


Fonctions & procédures

  • Fonction
    • renvoie une ou plusieurs valeurs
    • SETOF ou TABLE pour plusieurs lignes
  • Procédure (v11+)
    • ne renvoie rien
    • peut gérer le transactionnel dans certains cas

Historiquement, PostgreSQL ne proposait que l’écriture de fonctions. Depuis la version 11, il est aussi possible de créer des procédures. Le terme « routine » est utilisé pour signifier procédure ou fonction.

Une fonction renvoie une donnée. Cette donnée peut comporter une ou plusieurs colonnes. Elle peut aussi avoir plusieurs lignes dans le cas d’une fonction SETOF ou TABLE.

Une procédure ne renvoie rien. Elle a cependant un gros avantage par rapport aux fonctions dans le fait qu’elle peut gérer le transactionnel. Elle peut valider ou annuler la transaction en cours. Dans ce cas, une nouvelle transaction est ouverte immédiatement après la fin de la transaction précédente.


Opérateurs

  • Dépend d’un ou deux types de données
  • Utilise une fonction prédéfinie :
CREATE OPERATOR //
  (FUNCTION=division0,
  LEFTARG=integer,
  RIGHTARG=integer);

Il est possible de créer de nouveaux opérateurs sur un type de base ou sur un type utilisateur. Un opérateur exécute une fonction, soit à un argument pour un opérateur unitaire, soit à deux arguments pour un opérateur binaire.

Voici un exemple d’opérateur acceptant une division par zéro sans erreur :

-- définissons une fonction de division en PL/pgSQL
CREATE FUNCTION division0 (p1 integer, p2 integer) RETURNS integer
LANGUAGE plpgsql
AS $$
BEGIN
  IF p2 = 0 THEN
      RETURN NULL;
  END IF;

  RETURN p1 / p2;
END
$$;

-- créons l'opérateur
CREATE OPERATOR // (FUNCTION = division0, LEFTARG = integer, RIGHTARG = integer);

-- une division normale se passe bien

SELECT 10/5;
 ?column?
----------
        2
SELECT 10//5;
 ?column?
----------
        2
-- une division par 0 ramène une erreur avec l'opérateur natif
SELECT 10/0;
ERROR:  division by zero
-- une division par 0 renvoie NULL avec notre opérateur
SELECT 10//0;
 ?column?
----------

(1 row)

Triggers

  • Opérations : INSERT, UPDATE, DELETE, TRUNCATE
  • Trigger sur :
    • une table, voire une colonne, et/ou avec condition
    • une vue
    • une opération DDL
    • une connexion
  • Tables de transition
  • Effet sur :
    • l’ensemble de la requête (FOR STATEMENT)
    • chaque ligne impactée (FOR EACH ROW)
  • N’importe quel langage supporté

Les triggers peuvent être exécutés avant (BEFORE) ou après (AFTER) une opération.

Il est possible de les déclencher pour chaque ligne impactée (FOR EACH ROW) ou une seule fois pour l’ensemble de la requête (FOR STATEMENT). Dans le premier cas, il est possible d’accéder à la ligne impactée (ancienne et nouvelle version). Dans le deuxième cas, des tables de transition donnent à l’utilisateur une vision des lignes avant et après modification.

Par ailleurs, les triggers peuvent être écrits dans n’importe lequel des langages de routine supportés par PostgreSQL (C, PL/pgSQL, PL/Perl, etc. )

Exemple :

ALTER TABLE capitaines ADD COLUMN salaire integer;

CREATE FUNCTION verif_salaire()
RETURNS trigger AS $verif_salaire$
BEGIN
  -- Nous verifions que les variables ne sont pas vides
  IF NEW.nom IS NULL THEN
    RAISE EXCEPTION 'Le nom ne doit pas être null.';
  END IF;

  IF NEW.salaire IS NULL THEN
    RAISE EXCEPTION 'Le salaire ne doit pas être null.';
  END IF;

  -- pas de baisse de salaires !
  IF NEW.salaire < OLD.salaire THEN
    RAISE EXCEPTION 'Pas de baisse de salaire !';
  END IF;

  RETURN NEW;
END;
$verif_salaire$ LANGUAGE plpgsql;

CREATE TRIGGER verif_salaire BEFORE INSERT OR UPDATE ON capitaines
  FOR EACH ROW EXECUTE PROCEDURE verif_salaire();

UPDATE capitaines SET salaire = 2000 WHERE nom = 'Robert Surcouf';
UPDATE capitaines SET salaire = 3000 WHERE nom = 'Robert Surcouf';
UPDATE capitaines SET salaire = 2000 WHERE nom = 'Robert Surcouf';
ERROR:  pas de baisse de salaire !
CONTEXTE : PL/pgSQL function verif_salaire() line 13 at RAISE

Questions

N’hésitez pas, c’est le moment !


Quiz

Introduction aux plans d’exécution

PostgreSQL

Introduction

  • Qu’est-ce qu’un plan d’exécution ?
  • Quels outils peuvent aider

Ce module a pour but de faire une présentation très rapide de l’optimiseur et des plans d’exécution. Il contient surtout une introduction sur la commande EXPLAIN et sur différents outils en relation.


Au menu

  • Exécution globale d’une requête
  • Optimiseur
  • EXPLAIN
  • Nœuds d’un plan
  • Outils

Niveau SGBD

Traitement d’une requête SQL

Lorsque le serveur récupère la requête, un ensemble de traitements est réalisé.

Tout d’abord, le parser va réaliser une analyse syntaxique de la requête.

Puis le rewriter va réécrire, si nécessaire, la requête. Pour cela, il prend en compte les règles, les vues non matérialisées et les fonctions SQL.

Si une règle demande de changer la requête, la requête envoyée est remplacée par la nouvelle.

Si une vue non matérialisée est utilisée, la requête qu’elle contient est intégrée dans la requête envoyée. Il en est de même pour une fonction SQL intégrable.

Ensuite, le planner va générer l’ensemble des plans d’exécutions. Il calcule le coût de chaque plan, puis il choisit le plan le moins coûteux, donc le plus intéressant.

Enfin, l’executer exécute la requête.

Pour cela, il doit commencer par récupérer les verrous nécessaires sur les objets ciblés. Une fois les verrous récupérés, il exécute la requête.

Une fois la requête exécutée, il envoie les résultats à l’utilisateur.

Plusieurs goulets d’étranglement sont visibles ici. Les plus importants sont :

  • la planification (à tel point qu’il est parfois préférable de ne générer qu’un sous-ensemble de plans, pour passer plus rapidement à la phase d’exécution) ;
  • la récupération des verrous (une requête peut attendre plusieurs secondes, minutes, voire heures, avant de récupérer les verrous et exécuter réellement la requête) ;
  • l’exécution de la requête ;
  • l’envoi des résultats à l’utilisateur.

En général, le principal souci pour les performances sur ce type d’instructions est donc l’obtention des verrous et l’exécution réelle de la requête. Il existe quelques ordres (comme TRUNCATE ou COPY) exécutés beaucoup plus directement.


Optimiseur

  • SQL est un langage déclaratif
  • Une requête décrit le résultat à obtenir
    • mais pas la façon pour l’obtenir
  • C’est à l’optimiseur de déduire le moyen de parvenir au résultat demandé : comment ?

Les moteurs de base de données utilisent un langage SQL qui permet à l’utilisateur de décrire le résultat qu’il souhaite obtenir, mais pas la manière. C’est à la base de données de se débrouiller pour obtenir ce résultat le plus rapidement possible.


Principe de l’optimiseur

Le modèle vise à minimiser un coût :

  • Énumérer tous les plans d’exécution
    • ou presque tous…
  • Statistiques + configuration + règles → coût calculé
  • Coût le plus bas = meilleur plan

Le but de l’optimiseur est assez simple. Pour une requête, il existe de nombreux plans d’exécution possibles. Il va donc énumérer tous les plans d’exécution possibles (sauf si cela représente vraiment trop de plans auquel cas, il ne prendra en compte qu’une partie des plans possibles).

Pour calculer le « coût » d’un plan, PostgreSQL dispose d’informations sur les données (des statistiques), d’une configuration (réalisée par l’administrateur de bases de données) et d’un ensemble de règles inscrites en dur.

À la fin de l’énumération et du calcul de coût, il ne lui reste plus qu’à sélectionner le plan qui a le plus petit coût.

Le coût d’un plan est une valeur calculée sans unité ni signification physique.


Exemple de requête et son résultat

SELECT nom, prenom, num_service
FROM employes
WHERE nom LIKE 'B%'
ORDER BY num_service;
    nom    |  prenom  | num_service
-----------+----------+-------------
 Berlicot  | Jules    |           2
 Brisebard | Sylvie   |           3
 Barnier   | Germaine |           4

La requête en exemple permet de récupérer des informations sur tous les employés dont le nom commence par la lettre B en triant les employés par leur service.

Un moteur de bases de données peut récupérer les données de plusieurs façons :

  • faire un parcours séquentiel de la table employes en filtrant les enregistrements d’après leur nom, puis trier les données grâce à un algorithme ;
  • faire un parcours d’index (s’il y en a un) sur la colonne nom pour trouver plus rapidement les enregistrements de la table employes satisfaisant le filtre 'B%', puis trier les données grâce à un algorithme ;
  • faire un parcours d’index sur la colonne num_service pour récupérer les enregistrements déjà triés par service, et ne retourner que ceux vérifiant le prédicat nom like 'B%'.

Et ce ne sont que quelques exemples, car il serait possible d’avoir un index utilisable à la fois pour le tri et le filtre par exemple.

Donc la requête décrit le résultat à obtenir, et le planificateur va chercher le meilleur moyen pour parvenir à ce résultat. Pour ce travail, il dispose d’un certain nombre d’opérations de base. Ces opérations travaillent sur des ensembles de lignes, généralement un ou deux. Chaque opération renvoie un seul ensemble de lignes. Le planificateur peut combiner ces opérations suivant certaines règles. Une opération peut renvoyer l’ensemble de résultats de deux façons : d’un coup (par exemple le tri) ou petit à petit (par exemple un parcours séquentiel). Le premier cas utilise plus de mémoire, et peut nécessiter d’écrire des données temporaires sur disque. Le deuxième cas aide à accélérer des opérations comme les curseurs, les sous-requêtes IN et EXISTS, la clause LIMIT, etc.


Décisions de l’optimiseur

  • Comment accéder aux lignes ?
    • parcours de table, d’index, de fonction, etc.
  • Comment joindre les tables ?
    • ordre
    • type
  • Comment agréger ?
    • brut, tri, hachage…

Pour exécuter une requête, le planificateur va utiliser des opérations. Pour lire des lignes, il peut utiliser un parcours de table (une lecture complète du fichier), un parcours d’index ou encore d’autres types de parcours. Ce sont généralement les premières opérations utilisées.

Pour joindre les tables, l’ordre dans lequel ce sera fait est très important. Pour la jointure elle-même, il existe plusieurs méthodes différentes. Il existe aussi plusieurs algorithmes d’agrégation de lignes. Un tri peut être nécessaire pour une jointure, une agrégation, ou pour un ORDER BY, et là encore il y a plusieurs algorithmes possibles, ou des techniques pour éviter de le faire.


Mécanisme de calcul de coûts

  • Chaque opération a un coût :
    • lire un bloc selon sa position sur le disque
    • manipuler une ligne
    • appliquer un opérateur
  • et généralement un paramètre associé

L’optimiseur statistique de PostgreSQL utilise un modèle de calcul de coût. Les coûts calculés sont des indications arbitraires de la charge nécessaire pour répondre à une requête. Chaque facteur de coût représente une unité de travail : lecture d’un bloc, manipulation d’une ligne en mémoire, application d’un opérateur sur un champ.


Statistiques

  • Connaître le coût de traitement d’une ligne est bien
    • mais combien de lignes à traiter ?
  • Statistiques sur les données
    • mises à jour : ANALYZE
  • Sans bonnes statistiques, pas de bons plans !

En plus du coût unitaire de traitement d’une ligne, il faut connaître le nombre total de lignes à traiter pour calculer le coût total. L’optimiseur a donc besoin d’informations sur les données, comme par exemple le nombre de blocs et de lignes d’une table, les valeurs les plus fréquentes et leur fréquence, pour chaque colonne de chaque table. Ces statistiques sur les données sont calculées lors de l’exécution de la commande SQL ANALYZE.

Le processus autovacuum permet de rafraîchir régulièrement les statistiques.

Le propriétaire de la table ou un superutilisateur peut également le faire manuellement. À partir de PostgreSQL 17, il est possible de créer un utilisateur dédié à la maintenance, qui, sans avoir de droits de lecture sur les tables, peut exécuter ces commandes. Cet utilisateur pourra intervenir soit sur toutes les tables s’il possède le rôle pg_maintain, soit sur des tables spécifiques si un GRANT MAINTAIN lui a été accordé.

Des statistiques périmées ou pas assez fines sont une source fréquente de plans non optimaux !


Exemple - parcours d’index

CREATE TABLE t1 (c1 integer, c2 integer);
INSERT INTO t1 SELECT i, i FROM generate_series(1, 1000) i;
CREATE INDEX ON t1(c1);
ANALYZE t1;
EXPLAIN SELECT * FROM t1 WHERE c1=1 ;
                         QUERY PLAN
---------------------------------------------------------------
Index Scan using t1_c1_idx on t1 (cost=0.28..8.29 rows=1 width=8)
  Index Cond: (c1 = 1)

L’exemple crée une table et lui ajoute 1000 lignes. Chaque ligne a une valeur différente dans les colonnes c1 et c2 (de 1 à 1000).

SELECT * FROM t1 ;
  c1  |  c2  
------+------
    1 |    1
    2 |    2
    3 |    3
    4 |    4
    5 |    5
    6 |    6

  996 |  996
  997 |  997
  998 |  998
  999 |  999
 1000 | 1000
(1000 lignes)

Dans cette requête :

EXPLAIN SELECT * FROM t1 WHERE c1=1 ;

nous savons qu’un SELECT filtrant sur la valeur 1 pour la colonne c1 ne ramènera qu’une ligne. Grâce aux statistiques relevées par la commande ANALYZE exécutée juste avant, l’optimiseur estime lui aussi qu’une seule ligne sera récupérée. Une ligne sur 1000, c’est un bon ratio pour faire un parcours d’index. C’est donc ce que recommande l’optimiseur.


Exemple - parcours de table

UPDATE t1 SET c1=1 ;   /* 1000 lignes identiques */

ANALYZE t1 ;           /* ne pas oublier ! */
EXPLAIN SELECT * FROM t1 WHERE c1=1;
                    QUERY PLAN
------------------------------------------------------
 Seq Scan on t1  (cost=0.00..21.50 rows=1000 width=8)
   Filter: (c1 = 1)

La même table, mais avec 1000 lignes ne contenant plus que la valeur 1. Un SELECT filtrant sur cette valeur 1 ramènera dans ce cas toutes les lignes. L’optimiseur s’en rend compte et décide qu’un parcours séquentiel de la table est préférable à un parcours d’index. C’est donc ce que recommande l’optimiseur.

Dans cet exemple, l’ordre ANALYZE garantit que les statistiques sont à jour (le démon autovacuum n’est pas forcément assez rapide).


Exemple - parcours d’index forcé

SET enable_seqscan TO off ;

EXPLAIN SELECT * FROM t1 WHERE c1=1;
                         QUERY PLAN
---------------------------------------------------------------
Index Scan using t1_c1_idx on t1 (cost=0.28..57.77 rows=1000 width=8)
  Index Cond: (c1 = 1)
RESET enable_seqscan ;

Le coût du parcours de table était de 21,5 pour la récupération des 1000 lignes, donc un coût bien supérieur au coût du parcours d’index, qui lui était de 8,29, mais pour une seule ligne. On pourrait se demander le coût du parcours d’index pour 1000 lignes. À titre expérimental, on peut désactiver (ou plus exactement désavantager) le parcours de table en configurant le paramètre enable_seqscan à off.

En faisant cela, on s’aperçoit que le plan passe finalement par un parcours d’index, tout comme le premier. Par contre, le coût n’est plus de 8,29, mais de 57,77, donc supérieur au coût du parcours de table. C’est pourquoi l’optimiseur avait d’emblée choisi un parcours de table. Un index n’est pas forcément le chemin le plus court.


Qu’est-ce qu’un plan d’exécution ?

  • Représente les différentes opérations pour répondre à la requête
  • Sous forme arborescente
  • Composé des nœuds d’exécution
  • Plusieurs opérations simples mises bout à bout

L’optimiseur transforme une grosse action (exécuter une requête) en plein de petites actions unitaires (trier un ensemble de données, lire une table, parcourir un index, joindre deux ensembles de données, etc). Ces petites actions sont liées les unes aux autres. Par exemple, pour exécuter cette requête :

SELECT * FROM une_table ORDER BY une_colonne;

peut se faire en deux actions :

  • récupérer les enregistrements de la table ;
  • trier les enregistrements provenant de la lecture de la table.

Mais ce n’est qu’une des possibilités.


Nœud d’exécution

  • Nœud
    • opération simple : lectures, jointures, tris, etc.
    • unité de traitement
    • produit et consomme des données
  • Enchaînement des opérations
    • chaque nœud produit les données consommées par le nœud parent
    • le nœud final retourne les données à l’utilisateur

Les nœuds correspondent à des unités de traitement qui réalisent des opérations simples sur un ou deux ensembles de données : lecture d’une table, jointures entre deux tables, tri d’un ensemble, etc. Si le plan d’exécution était une recette, chaque nœud serait une étape de la recette.

Les nœuds peuvent produire et consommer des données.


Récupérer un plan d’exécution

  • Commande EXPLAIN
    • suivi de la requête complète
  • Uniquement le plan finalement retenu

Pour récupérer le plan d’exécution d’une requête, il suffit d’utiliser la commande EXPLAIN. Cette commande est suivie de la requête pour laquelle on souhaite le plan d’exécution.

Seul le plan sélectionné est affichable. Les plans ignorés du fait de leur coût trop important ne sont pas récupérables. Ceci est dû au fait que les plans en question peuvent être abandonnés avant d’avoir été totalement développés si leur coût partiel est déjà supérieur à celui de plans déjà considérés.


Exemple de requête

EXPLAIN SELECT * FROM t1  WHERE c2<10 ORDER BY c1;

Cette requête va récupérer tous les enregistrements de t1 pour lesquels la valeur de la colonne c2 est inférieure à 10. Les enregistrements sont triés par rapport à la colonne c1.


Plan pour cette requête

                       QUERY PLAN
---------------------------------------------------------
 Sort  (cost=21.64..21.67 rows=9 width=8)
   Sort Key: c1
   ->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
         Filter: (c2 < 10)

L’optimiseur envoie ce plan à l’exécuteur. Ce dernier voit qu’il a une opération de tri à effectuer (nœud Sort). Pour cela, il a besoin de données que le nœud suivant va lui donner. Il commence donc l’opération de lecture (nœud SeqScan). Il envoie chaque enregistrement valide au nœud Sort pour que ce dernier les trie.

Chaque nœud dispose d’un certain nombre d’informations placées soit sur la même ligne entre des parenthèses, soit sur la ou les lignes du dessous. La différence entre une ligne de nœud et une ligne d’informations est que la ligne de nœud contient une flèche au début (->). Par exemple, le nœud Sort contient des informations entre des parenthèses et une information supplémentaire sur la ligne suivante indiquant la clé de tri (la colonne c1). Par contre, la troisième ligne n’est pas une ligne d’informations du nœud Sort mais un nouveau nœud (SeqScan).


Informations sur la ligne nœud

->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
      Filter: (c2 < 10)
  • cost : coûts de récupération
    • de la première ligne
    • de toutes les lignes
  • rows
    • nombre de lignes en sortie du nœud
  • width
    • largeur moyenne d’un enregistrement (octets)

Chaque nœud montre les coûts estimés dans le premier groupe de parenthèses. cost est un couple de deux coûts : la première valeur correspond au coût pour récupérer la première ligne (souvent nul dans le cas d’un parcours séquentiel) ; la deuxième valeur correspond au coût pour récupérer toutes les lignes (elle dépend essentiellement de la taille de la table lue, mais aussi d’opération de filtrage). rows correspond au nombre de lignes que le planificateur pense récupérer à la sortie de ce nœud. Dans le cas d’une nouvelle table traitée par ANALYZE, les versions antérieures à la version 14 calculaient une valeur probable du nombre de lignes en se basant sur la taille moyenne d’une ligne et sur une table faisant 10 blocs. La version 14 corrige cela en ayant une meilleure idée du nombre de lignes d’une nouvelle table. width est la largeur en octets de la ligne.


Informations sur les lignes suivantes

Sort  (cost=21.64..21.67 rows=9 width=8)
  Sort Key: c1
Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
  Filter: (c2 < 10)
  • Sort
    • Sort Key : clé de tri
  • Seq Scan
    • Filter : filtre (si besoin)
  • Dépend
    • du type de nœud
    • des options de EXPLAIN
    • des paramètres de configuration
    • de la version de PostgreSQL

Les informations supplémentaires dépendent de beaucoup d’éléments. Elles peuvent différer suivant le type de nœud, les options de la commande EXPLAIN, et certains paramètres de configuration. De même la version de PostgreSQL joue un rôle majeur : les nouvelles versions peuvent apporter des informations supplémentaires pour que le plan soit plus lisible et que l’utilisateur soit mieux informé.


EXPLAIN ( ANALYZE )

EXPLAIN (ANALYZE)  /* exécution !! */
SELECT   *   FROM t1   WHERE c2<10   ORDER BY c1;
                        QUERY PLAN
---------------------------------------------------------------
 Sort  (cost=21.64..21.67 rows=9 width=8)
       (actual time=0.493..0.498 rows=9 loops=1)
   Sort Key: c1
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
               (actual time=0.061..0.469 rows=9 loops=1)
         Filter: (c2 < 10)
         Rows Removed by Filter: 991
 Planning Time: 0.239 ms
 Execution Time: 0.606 ms

Le but de cette option est d’obtenir les informations sur l’exécution réelle de la requête.

Avec ANALYZE, la requête est réellement exécutée ! Attention donc aux INSERT/UPDATE/DELETE. N’oubliez pas non plus qu’un SELECT peut appeler des fonctions qui écrivent dans la base. Dans le doute, pensez à englober l’appel dans une transaction que vous annulerez après coup.

Quatre nouvelles informations apparaissent dans un nouveau bloc de parenthèses. Elles sont toutes liées à l’exécution réelle de la requête :

  • actual time
  • la première valeur correspond à la durée en milliseconde pour récupérer la première ligne ;
  • la deuxième valeur est la durée en milliseconde pour récupérer toutes les lignes ;
  • rows est le nombre de lignes réellement récupérées ;
  • loops est le nombre d’exécutions de ce nœud, soit dans le cadre d’une jointure, soit dans le cadre d’une requête parallélisée.

Multiplier la durée par le nombre de boucles pour obtenir la durée réelle d’exécution du nœud !

L’intérêt de cette option est donc de trouver l’opération qui prend du temps dans l’exécution de la requête, mais aussi de voir les différences entre les estimations et la réalité (notamment au niveau du nombre de lignes).


EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS)
SELECT   *   FROM t1   WHERE c2<10   ORDER BY c1;
                        QUERY PLAN
---------------------------------------------------------
 Sort  (cost=17.64..17.67 rows=9 width=8)
       (actual time=0.126..0.127 rows=9 loops=1)
   Sort Key: c1
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=3 read=5
   ->  Seq Scan on t1  (cost=0.00..17.50 rows=9 width=8)
                       (actual time=0.017..0.106 rows=9 loops=1)
         Filter: (c2 < 10)
         Rows Removed by Filter: 991
         Buffers: shared read=5
BUFFERS fait apparaître le nombre de blocs (buffers) impactés par chaque nœud du plan d’exécution, en lecture comme en écriture.

Il est conseillé de l’activer systématiquement. La quantité de blocs manipulés par une requête est souvent surprenante, et souvent la cause d’une mauvaise performance.

shared read=5 en bas signifie que 5 blocs ont été trouvés et lus hors du cache de PostgreSQL (shared buffers). 5 blocs est ici la taille de t1 sur le disque. Le cache de l’OS est peut-être intervenu, ce n’est pas visible ici. Un peu plus haut, shared hit=3 read=5 indique que 3 blocs ont été lus dans ce cache, et 5 autres toujours hors du cache. Les valeurs exactes dépendent donc de l’état du cache. Si on relance la requête, pour une telle petite table, les relectures se feront uniquement en shared hit.

BUFFERS compte aussi les blocs de fichiers ou tables temporaires (temp ou local), ou les blocs écrits sur disque (written).

EXPLAIN (ANALYZE, BUFFERS) n’affiche que des données réelles, pas des estimations. EXPLAIN (BUFFERS) sans ANALYZE peut être utilisé, mais il ne montre que les blocs utilisés par la planification, plutôt que accédés à l’exécution. Ces blocs sont surtout des appels aux tables systèmes, et sont moins nombreux, voire absents, quand on appelle la requête une deuxième fois, ou plus, dans la même session.


EXPLAIN (ANALYZE, WAL)

EXPLAIN (ANALYZE, WAL)
INSERT INTO t1 SELECT i, i FROM generate_series(1,1000) i ;
                      QUERY PLAN
----------------------------------------------------
 Insert on t1  (cost=0.00..10.00 rows=1000 width=8)
          (actual time=8.078..8.079 rows=0 loops=1)
   WAL: records=2017 fpi=3 bytes=162673
   ->  Function Scan on generate_series i
       (cost=0.00..10.00 rows=1000 width=8)
       (actual time=0.222..0.522 rows=1000 loops=1)
 Planning Time: 0.076 ms
 Execution Time: 8.141 ms

Désactivée par défaut et nécessitant l’option ANALYZE, l’option WAL permet d’obtenir le nombre d’enregistrements et le nombre d’octets écrits dans les journaux de transactions. (Rappelons que les écritures dans les fichiers de données se font généralement plus tard, en arrière-plan.) Il est conseillé de l’activer systématiquement, même pour les SELECT.


EXPLAIN (ANALYZE, SERIALIZE)

EXPLAIN (ANALYZE,VERBOSE,BUFFERS,SERIALIZE)
SELECT * FROM demotoast ;
                            QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on public.demotoast  (cost=0.00..177.00 rows=1000 width=1196) (actual time=0.012..0.182 rows=1000 loops=1)
   Output: i, t, png, j
   Buffers: shared hit=167
 Query Identifier: 698565989149231362
 Planning Time: 0.055 ms
 Serialization: time=397.480 ms  output=307084kB  format=text
   Buffers: shared hit=14500
 Execution Time: 397.776 ms
  • PostgreSQL 17

Cette option, apparue avec PostgreSQL 17, force la « sérialisation » du résultat de la requête, et met ainsi en ainsi en évidence sa participation dansle temps d’exécution total de la requête et volumétrie de données a transférer. Sans cette option, EXPLAIN (ANALYZE) peut afficher un résultat trop optimiste par rapport aux requêtes réelles. L’exemple montre l’impact de la lecture (pas forcément voulue) de la partie TOAST de la table, où sont relégués les gros champs textes ou binaires, à cause du SELECT *. Le coût réseau n’est toutefois pas inclus.


EXPLAIN ( GENERIC_PLAN )

Quel plan générique pour les requêtes préparées ?

EXPLAIN (GENERIC_PLAN)
SELECT * FROM t1 WHERE c1 < $1 ;
  • PostgreSQL 16

L’option GENERIC_PLAN n’est malheureusement pas disponible avant PostgreSQL 16. Elle est pourtant très pratique quand on cherche le plan d’une requête préparée sans connaître ses paramètres, ou pour savoir quel est le plan générique que prévoit PostgreSQL pour une requête préparée.

En effet, les plans des requêtes préparées ne sont pas forcément recalculés à chaque appel avec les paramètres exacts (le système est assez complexe et dépend du paramètre plan_cache_mode). La requête ne peut être exécutée sans vraie valeur de paramètre, donc l’option ANALYZE est inutilisable, mais en activant GENERIC_PLAN on peut tout de même voir le plan générique que PostgreSQL peut choisir (SUMMARY ON affiche en plus le temps de planification) :

EXPLAIN (GENERIC_PLAN, SUMMARY ON)
SELECT * FROM t1 WHERE c1 < $1 ;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using t1_c1_idx on t1  (cost=0.15..14.98 rows=333 width=8)
   Index Cond: (c1 < $1)
 Planning Time: 0.195 ms

C’est effectivement le plan qui serait optimal pour $1=1. Mais pour la valeur 1000, qui ramène toute la table, un Seq Scan serait plus pertinent.


EXPLAIN (SETTINGS)

SET enable_seqscan TO off ;
SET work_mem TO '100MB';
EXPLAIN (SETTINGS)
SELECT   *   FROM t1   WHERE c2<10   ORDER BY c1;
                         QUERY PLAN
---------------------------------------------------------------
Index Scan using t1_c1_idx on t1  (cost=0.28..57.77 rows=9 width=8)
  Filter: (c2 < 10)
Settings: enable_seqscan = 'off', work_mem = '100MB'
RESET ALL ;

Désactivée par défaut, l’option SETTINGS permet d’obtenir la liste des paramètres influant sur la planification et qui ne sont pas à leur valeur par défaut pour la session ou la requête en cours. Il est conseillé de l’activer systématiquement. Elle est pratique quand il faut transmettre le plan à un collègue ou un prestataire qui n’a pas forcément accès à la machine et à sa configuration.


Autres options

  • VERBOSE
    • affichage verbeux : schémas, colonnes, workers : conseillé
  • MEMORY
    • mémoire utilisée pour le plan (v17)
  • COSTS OFF
    • masquer les coûts
  • TIMING OFF
    • désactiver le chronométrage & des informations vues/calculées par l’optimiseur
  • SUMMARY
    • affichage du temps de planification et exécution (si applicable)
  • FORMAT
    • sortie en texte, JSON, XML, YAML

Ces options sont moins utilisées, mais certaines restent intéressantes dans des cas précis.

Option VERBOSE

N’hésitez pas à utiliser l’option VERBOSE pour afficher des informations supplémentaires comme :

  • la liste des colonnes en sortie ;
  • le nom des objets qualifiés par le nom du schéma ;
  • des statistiques sur les workers (pour les requêtes parallélisées) ;
  • le code SQL envoyé à un serveur distant (pour les tables distantes avec postgres_fdw notamment).

Dans l’exemple suivant, le nom du schéma est ajouté au nom de la table. La nouvelle ligne Output indique la liste des colonnes de l’ensemble de données en sortie du nœud.

EXPLAIN (VERBOSE) SELECT * FROM t1 WHERE c2<10 ORDER BY c1 ;
                           QUERY PLAN
----------------------------------------------------------------
 Sort  (cost=21.64..21.67 rows=9 width=8)
   Output: c1, c2
   Sort Key: t1.c1
   ->  Seq Scan on public.t1  (cost=0.00..21.50 rows=9 width=8)
         Output: c1, c2
         Filter: (t1.c2 < 10)

Option MEMORY

À partir de PostgreSQL 17, cette option affiche la mémoire consommée par le planificateur. Ce n’est utile que pour les requêtes compliquées. Cet exemple montre que même pour une requête très basique, un peu de mémoire est utilisée uniquement pour la planification :

EXPLAIN (MEMORY) SELECT relname FROM pg_class ;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..27.27 rows=527 width=64)
 Planning:
   Memory: used=9kB  allocated=16kB

Option COSTS

Cette option est activée par défaut. Il peut être intéressant de la désactiver pour n’avoir que le plan.

EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE c2<10 ORDER BY c1 ;
        QUERY PLAN
---------------------------
 Sort
   Sort Key: c1
   ->  Seq Scan on t1
         Filter: (c2 < 10)

Option TIMING

Cette option est activée par défaut. Il peut être intéressant de le désactiver sur les systèmes où le chronométrage prend beaucoup de temps et allonge inutilement la durée d’exécution de la requête. Mais de ce fait, le résultat devient beaucoup moins intéressant.

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t1 WHERE c2<10 ORDER BY c1 ;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Sort  (cost=21.64..21.67 rows=9 width=8) (actual rows=9 loops=1)
   Sort Key: c1
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8) (actual rows=9 loops=1)
         Filter: (c2 < 10)
         Rows Removed by Filter: 991
 Planning Time: 0.155 ms
 Execution Time: 0.381 ms

Option SUMMARY

Elle permet d’afficher ou non le résumé final indiquant la durée de la planification et de l’exécution. Un EXPLAIN simple n’affiche pas le résumé par défaut (la durée de planification est pourtant parfois importante). Par contre, un EXPLAIN ANALYZE l’affiche par défaut.

EXPLAIN (SUMMARY ON) SELECT * FROM t1 WHERE c2<10 ORDER BY c1;
                       QUERY PLAN
---------------------------------------------------------
 Sort  (cost=21.64..21.67 rows=9 width=8)
   Sort Key: c1
   ->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
         Filter: (c2 < 10)
 Planning Time: 0.185 ms
EXPLAIN (ANALYZE, SUMMARY OFF) SELECT * FROM t1 WHERE c2<10 ORDER BY c1;
                    QUERY PLAN
---------------------------------------------------------
 Sort  (cost=21.64..21.67 rows=9 width=8)
       (actual time=0.343..0.346 rows=9 loops=1)
   Sort Key: c1
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on t1  (cost=0.00..21.50 rows=9 width=8)
               (actual time=0.031..0.331 rows=9 loops=1)
         Filter: (c2 < 10)
         Rows Removed by Filter: 991

Option FORMAT

L’option FORMAT permet de préciser le format du texte en sortie. Par défaut, il s’agit du format texte habituel, mais il est possible de choisir un format semi-structuré parmi JSON, XML et YAML. Les formats semi-structurés sont utilisés principalement par des outils d’analyse comme explain.dalibo.com, car le contenu est plus facile à analyser, et même un peu plus complet. Voici ce que donne la commande EXPLAIN avec le format JSON :

psql -X -AtX \
-c 'EXPLAIN (FORMAT JSON) SELECT * FROM t1 WHERE c2<10 ORDER BY c1' | jq '.[]'
{
  "Plan": {
    "Node Type": "Sort",
    "Parallel Aware": false,
    "Async Capable": false,
    "Startup Cost": 34.38,
    "Total Cost": 34.42,
    "Plan Rows": 18,
    "Plan Width": 8,
    "Sort Key": [
      "c1"
    ],
    "Plans": [
      {
        "Node Type": "Seq Scan",
        "Parent Relationship": "Outer",
        "Parallel Aware": false,
        "Async Capable": false,
        "Relation Name": "t1",
        "Alias": "t1",
        "Startup Cost": 0,
        "Total Cost": 34,
        "Plan Rows": 18,
        "Plan Width": 8,
        "Filter": "(c2 < 10)"
      }
    ]
  }
}

Paramètre track_io_timing

SET track_io_timing TO on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT   *   FROM t1   WHERE c2<10   ORDER BY c1 ;
                            QUERY PLAN
---------------------------------------------------------
 Sort  (cost=52.14..52.21 rows=27 width=8) (actual time=1.359..1.366 rows=27 loops=1)

   Buffers: shared hit=3 read=14
   I/O Timings: read=0.388
   ->  Seq Scan on t1  (cost=0.00..51.50 rows=27 width=8) (actual time=0.086..1.233 rows=27 loops=1)
         Filter: (c2 < 10)
         Rows Removed by Filter: 2973
         Buffers: shared read=14
         I/O Timings: read=0.388
 Planning:
   Buffers: shared hit=43 read=14
   I/O Timings: read=0.469
 Planning Time: 1.387 ms
 Execution Time: 1.470 ms

La configuration du paramètre track_io_timing permet de demander le chronométrage des opérations d’entrée/sortie disque. Sur ce plan, nous pouvons voir que 14 blocs ont été lus en dehors du cache de PostgreSQL et que cela a pris 0,388 ms pour les lire (ils étaient certainement dans le cache du système d’exploitation).

Cette information permet de voir si le temps d’exécution de la requête est dépensé surtout dans la demande de blocs au système d’exploitation (donc hors du cache de PostgreSQL) ou dans l’exécution même de la requête (donc interne à PostgreSQL).


Détecter les problèmes

  • Temps d’exécution de chaque opération
  • Différence entre l’estimation du nombre de lignes et la réalité
  • Boucles
    • appels, même rapides, nombreux
  • Opérations utilisant beaucoup de blocs (BUFFERS)
  • Opérations lentes de lecture/écriture (track_io_timing)

Lorsqu’une requête s’exécute lentement, cela peut être un problème dans le plan. La sortie de EXPLAIN peut apporter quelques informations qu’il faut savoir décoder.

Par exemple, une différence importante entre le nombre estimé de lignes et le nombre réel de lignes laisse un doute sur les statistiques présentes. Soit elles n’ont pas été réactualisées récemment, soit l’échantillon n’est pas suffisamment important pour que les statistiques donnent une vue proche du réel du contenu de la table.

Les boucles sont à surveiller. Par exemple, un accès à une ligne par un index est généralement très rapide, mais répété des millions de fois à cause d’une boucle, le total est parfois plus long qu’une lecture complète de la table indexée. C’est notamment l’enjeu du réglage entre seq_page_cost et random_page_cost.

L’option BUFFERS d’EXPLAIN permet également de mettre en valeur les opérations d’entrées/sorties lourdes. Cette option affiche notamment le nombre de blocs lus en/hors du cache de PostgreSQL. Sachant qu’un bloc fait généralement 8 kilo-octets, il est aisé de déterminer le volume de données manipulé par une requête.


Nœuds d’exécution les plus courants (introduction)

  • Parcours
  • Jointures
  • Agrégats
  • Tri

Nous n’allons pas détailler tous les nœuds existants, mais évoquer simplement les plus importants. Une analyse plus poussée des nœuds et une référence complète sont disponibles dans les modules J2 et J6.


Parcours

  • Table
    • Seq Scan, Parallel Seq Scan
  • Index
    • Index Scan, Bitmap Scan, Index Only Scan
    • et les variantes parallélisées
  • Autres
    • Function Scan, Values Scan

Plusieurs types d’objets peuvent être parcourus. Pour chacun, l’optimiseur peut choisir entre plusieurs types de parcours.

Les tables passent par un Seq Scan qui est une lecture simple de la table, bloc par bloc, ligne par ligne. Ce parcours peut filtrer les données mais ne les triera pas. Une variante parallélisée existe sous le nom de Parallel Seq Scan.

Les index disposent de plusieurs parcours, principalement suivant la quantité d’enregistrements à récupérer :

  • Index Scan quand il y a très peu d’enregistrements à récupérer ;
  • Bitmap Scan quand il y en a un peu plus ou quand on veut lire plusieurs index d’une même table pour satisfaire plusieurs conditions de filtre ;
  • Index Only Scan quand les champs de la requête correspondent aux colonnes de l’index (ce qui permet d’éviter la lecture de tout ou partie de la table).

Ces différents parcours sont parallélisables. Ils ont dans ce cas le mot Parallel ajouté en début du nom du nœud.

Enfin, il existe des parcours moins fréquents, comme les parcours de fonction (Function Scan) ou de valeurs (Values Scan).


Jointures

  • Algorithmes
    • Nested Loop
    • Hash Join
    • Merge Join
  • Parallélisation possible
  • Pour EXISTS, IN et certaines jointures externes
    • Semi Join
    • Anti Join

Trois nœuds existent pour les jointures.

Le Nested Loop est utilisé pour toutes les conditions de jointure n’utilisant pas l’opérateur d’égalité. Il est aussi utilisé quand un des deux ensembles de données renvoie très peu de données.

Le Hash Join est certainement le nœud le plus commun. Il est utilisé un peu dans tous les cas, sauf si les deux ensembles de données arrivent déjà triés. Dans ce cas, il est préférable de passer par un Merge Join qui réclame deux ensembles de données déjà triés.

Les Semi Join et Anti Join sont utilisés dans des cas très particuliers et peu fréquents.


Agrégats

  • Un résultat au total
    • Aggregate
  • Un résultat par regroupement
    • Hash Aggregate
    • Group Aggregate
    • Mixed Aggregate
  • Parallélisation
    • Partial Aggregate
    • Finalize Aggregate

De même il existe plusieurs algorithmes d’agrégation qui s’occupent des sommes, des moyennes, des regroupements divers, etc. Ils sont souvent parallélisables.


Opérations unitaires

  • Sort
  • Incremental Sort
  • Limit
  • Unique (DISTINCT)
  • Append (UNION ALL), Except, Intersect
  • Gather (parallélisme)
  • Memoize (14+)

Un grand nombre de petites opérations ont leur propre nœud, comme le tri avec Sort et Incremental Sort, la limite de lignes (LIMIT) avec Limit, la clause DISTINCT avec Unique), etc. Elles prennent généralement un ensemble de données et renvoient un autre ensemble de données issu du traitement du premier.

Le groupe des nœuds Append, Except et Intersect ne se comporte pas ainsi. Notamment, Append est le seul nœud à prendre potentiellement plus de deux ensembles de données en entrée.

Apparu avec PostgreSQL 14, le nœud Memoize est un cache de résultat qui permet d’optimiser les performances d’autres nœuds en mémorisant des données qui risquent d’être accédées plusieurs fois de suite. Pour le moment, ce nœud n’est utilisable que pour les données de l’ensemble interne d’un Nested Loop.


Outils graphiques

  • pgAdmin
  • explain.depesz.com
  • explain.dalibo.com

L’analyse de plans complexes devient très vite fastidieuse. Nous n’avons vu ici que des plans d’une dizaine de lignes au maximum, mais les plans de requêtes réellement problématiques peuvent faire plusieurs centaines, voire milliers de lignes. L’analyse manuelle devient impossible. Des outils ont été créés pour mieux visualiser les parties intéressantes des plans.


pgAdmin

  • Vision graphique d’un EXPLAIN
  • Une icône par nœud
  • La taille des flèches dépend de la quantité de données
  • Le détail de chaque nœud est affiché en survolant les nœuds

pgAdmin propose depuis très longtemps un affichage graphique de l’EXPLAIN. Cet affichage est intéressant car il montre simplement l’ordre dans lequel les opérations sont effectuées. Chaque nœud est représenté par une icône. Les flèches entre chaque nœud indiquent où sont envoyés les flux de données, la taille de la flèche précisant la volumétrie des données.

Les statistiques ne sont affichées qu’en survolant les nœuds.


pgAdmin - copie d’écran

EXPLAIN par pgAdmin

Voici un exemple d’un EXPLAIN graphique réalisé par pgAdmin 4. En cliquant sur un nœud, un message affiche les informations statistiques sur le nœud.


explain.depesz.com

  • Site web avec affichage amélioré du EXPLAIN ANALYZE
  • Lignes colorées pour indiquer les problèmes
  • Installable en local

Hubert Lubaczewski est un contributeur très connu dans la communauté PostgreSQL. Il publie notamment un grand nombre d’articles sur les nouveautés des prochaines versions. Cependant, il est aussi connu pour avoir créé un site web d’analyse des plans d’exécution, explain.depesz.com.

Il suffit d’aller sur ce site, de coller le résultat d’un EXPLAIN ANALYZE, et le site affichera le plan d’exécution avec des codes couleurs pour bien distinguer les nœuds performants des autres.

Le code couleur est simple : blanc indique que tout va bien, jaune est inquiétant, marron est plus inquiétant, et rouge très inquiétant.

Plutôt que d’utiliser le service web, il est possible d’installer ce site en local :


explain.depesz.com - exemple

explain.depesz.com

Cet exemple montre l’affichage d’un plan sur le site explain.depesz.com.

Voici la signification des différentes colonnes :

  • Exclusive : durée passée exclusivement sur un nœud ;
  • Inclusive : durée passée sur un nœud et ses fils ;
  • Rows x : facteur d’échelle de l’erreur d’estimation du nombre de lignes ;
  • Rows : nombre de lignes renvoyées ;
  • Loops : nombre de boucles.

Sur une exécution de 600 ms, un tiers est passé à lire la table avec un parcours séquentiel.


explain.dalibo.com

  • Reprise de pev d’Alex Tatiyants, par Pierre Giraud (Dalibo)
  • Page web avec affichage graphique d’un EXPLAIN [ANALYZE]
  • Repérage des nœuds longs, lourds…
  • Affichage flexible
  • explain.dalibo.com
  • Installable en local

À l’origine, pev (PostgreSQL Explain Visualizer) est un outil libre offrant un affichage graphique du plan d’exécution et pointant le nœud le plus coûteux, le plus long, le plus volumineux, etc. Utilisable en ligne, il n’est hélas plus maintenu depuis plusieurs années.

explain.dalibo.com en est un fork, très étendu et activement maintenu par Pierre Giraud de Dalibo. Les plans au format texte comme JSON sont acceptés. Les versions récentes de PostgreSQL sont supportées, avec leurs spécificités : nouvelles options d’EXPLAIN, nouveaux types de nœuds… Tout se passe en ligne. Les plans peuvent être partagés. Si vous ne souhaitez pas qu’ils soient stockés chez Dalibo, utilisez la version strictement locale de pev2.

Le code est sous licence PostgreSQL. Techniquement, c’est un composant VueJS qui peut être intégré à vos propres outils.


explain.dalibo.com - exemple

EXPLAIN par pev

explain.dalibo.com permet de repérer d’un coup d’œil les parties les plus longues du plan, celles utilisant le plus de lignes, les écarts d’estimation, les dérives du temps de planification… Les nœuds peuvent être repliés. Plusieurs modes d’affichage sont disponibles.

Un grand nombre de plans d’exemple sont disponibles sur le site.


Conclusion

  • Un optimiseur très avancé
  • Ne vous croyez pas plus malin que lui
  • Mais il est important de savoir comment il fonctionne

Cette introduction à l’optimiseur de PostgreSQL permet de comprendre comment il fonctionne et sur quoi il se base. Cela permet de pointer certains des problèmes. C’est aussi un prérequis indispensable pour voir plus tard l’intérêt des différents index et nœuds d’exécution de PostgreSQL.


Questions

N’hésitez pas, c’est le moment !


Quiz

Travaux pratiques

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/j0_solutions.

Tous les TP se basent sur la configuration par défaut de PostgreSQL, sauf précision contraire.

Manipuler explain

But : Première manipulation d’EXPLAIN

Créer une base machines et y générer les données comme indiqué ci-dessous. L’exécution peut durer une minute ou deux suivant la machine.

curl -kL https://dali.bo/tp_machines_donnees -o machines_donnees.sql
createdb machines 
psql machines < machines_donnees.sql

La base machines contiendra alors deux tables :

  • machines est une liste de machines ;
  • donnees contient des données horodatées de quelques capteurs de ces machines, entre janvier et août 2023.
Tables machines et donnees

Nettoyage et mise à jour des statistiques :

VACUUM ANALYZE machines,donnees;

Quelles sont les tailles des tables ?

Pour simplifier certains plans, désactivons le parallélisme et la compilation à la volée :

SET max_parallel_workers_per_gather TO 0 ;
SET jit TO off ;

Requêtes sur les périodes :

Quel est le plan prévu pour récupérer les données du 31 janvier dans la table donnees ?

EXPLAIN
SELECT * FROM donnees
WHERE horodatage = '2023-01-31'::date ;

Quel est le plan prévu pour récupérer les données du mois de janvier dans la table donnees ?

EXPLAIN
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-01-01'::date AND '2023-01-31'::date ;

Quel est le plan prévu pour cette variante de la requête sur janvier ?

EXPLAIN
SELECT * FROM donnees
WHERE to_char (horodatage, 'YYYYMM') = '202301';

Pourquoi est-il différent ? Comparer avec le précédant en utilisant EXPLAIN ANALYZE.

Quel est le plan pour la même requête, cette fois sur deux mois ?

EXPLAIN
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-03-01'::date AND '2023-04-30'::date;

Relancer avec EXPLAIN (ANALYZE).

Jointure :

Quel est le plan prévu pour cette jointure sur toutes les données d’une machine ?

EXPLAIN
SELECT *
FROM donnees INNER JOIN machines USING (id_machine)
WHERE machines.code = 'E4DA3B' AND type = 5;

Quel est le plan prévu pour la requête suivante, qui récupère toutes les données d’après juillet pour un type de machines donné ? Quelles en sont les 3 étapes ?

EXPLAIN
SELECT description, horodatage, valeur1 
FROM donnees INNER JOIN machines USING (id_machine)
WHERE machines.type = 1
AND donnees.horodatage > '2023-07-01' ;

Manipuler explain (base magasin)

But : Manipuler explain.

  • Créer une base de données nommée magasin.
  • Importer le jeu de données d’exemple :

La base magasin (dump de 96 Mo, pour 667 Mo sur le disque au final) peut être téléchargée et restaurée comme suit dans une nouvelle base magasin :

createdb magasin
curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump
pg_restore -d magasin  /tmp/magasin.dump
# le message sur public préexistant est normal
rm -- /tmp/magasin.dump

Les données sont dans deux schémas, magasin et facturation. Penser au search_path.

Pour ce TP, figer les paramètres suivants :

SET max_parallel_workers_per_gather to 0;
SET seq_page_cost TO 1 ;
SET random_page_cost TO 4 ;
  • Le schéma à utiliser se nomme également magasin.
  • Consulter les tables.
  • Lancer un ANALYZE sur la base.

Le but est de chercher une personne nommée Moris Russel dans la table contacts par les champs prenom et nom.

  • Quel est le plan qu’utilisera PostgreSQL pour le trouver ?
  • À combien de résultats le planificateur s’attend-il ?
  • Afficher le résultat.
  • Quel est le plan réellement exécuté ?
  • Rechercher la même personne par son contact_id.
  • Quel est le plan ?
  • La requête suivante recherche tous les fournisseurs résidents d’Hollywood.
SELECT c.nom, c.prenom FROM contacts c
INNER JOIN fournisseurs f
ON (f.contact_id = c.contact_id)
WHERE c.ville = 'Hollywood' ;
  • Quel est le plan prévu ?
  • Que donne-t-il à l’exécution ?

Travaux pratiques (solutions)

Manipuler explain

Créer une base machines et y générer les données comme indiqué ci-dessous. L’exécution peut durer une minute ou deux suivant la machine.

curl -kL https://dali.bo/tp_machines_donnees -o machines_donnees.sql
createdb machines 
psql machines < machines_donnees.sql

La base machines contiendra alors deux tables :

  • machines est une liste de machines ;
  • donnees contient des données horodatées de quelques capteurs de ces machines, entre janvier et août 2023.
Tables machines et donnees

Nettoyage et mise à jour des statistiques :

VACUUM ANALYZE machines,donnees;

Cette opération est à faire systématiquement sur des tables récentes, ou au moindre doute. L’autovacuum n’est parfois pas assez rapide pour effectuer ces opérations.

Quelles sont les tailles des tables ?

Sous psql :

=# \dt+
                                                 Liste des relations
 Schéma |       Nom       | Type  | Propriétaire |  … | Taille  | …
--------+-----------------+-------+--------------+----+---------+--
 public | donnees         | table | postgres     |    | 284 MB  | 
 public | machines        | table | postgres     |    | 112 kB  | 

Quant aux nombres de lignes :

SELECT count (*) FROM machines ;
 count 
-------
  1000
SELECT count (*) FROM donnees ;
  count  
---------
 4950225

Tout plan d’exécution dépend de la configuration de PostgreSQL. Sauf précision contraire, nous partons toujours de la configuration par défaut.

Pour simplifier certains plans, désactivons le parallélisme et la compilation à la volée :

SET max_parallel_workers_per_gather TO 0 ;
SET jit TO off ;

Requêtes sur les périodes :

Quel est le plan prévu pour récupérer les données du 31 janvier dans la table donnees ?

EXPLAIN
SELECT * FROM donnees
WHERE horodatage = '2023-01-31'::date ;

Le plan prévu est :

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
Index Scan using donnees_horodatage_idx on donnees (cost=0.43..8.64 rows=12 width=30)
  Index Cond: (horodatage = '2023-01-31'::date)

Il existe un index sur le critère, il est naturel qu’il soit utilisé.

Quel est le plan prévu pour récupérer les données du mois de janvier dans la table donnees ?

EXPLAIN
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-01-01'::date AND '2023-01-31'::date ;

Le plan prévu est le même, au critère près :

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using donnees_horodatage_idx on donnees  (cost=0.43..933.87 rows=28722 width=30)
   Index Cond: ((horodatage >= '2023-01-01'::date) AND (horodatage <= '2023-01-31'::date))

Noter la réécriture du BETWEEN sous forme d’inégalités.

Quel est le plan prévu pour cette variante de la requête sur janvier ?

EXPLAIN
SELECT * FROM donnees
WHERE to_char (horodatage, 'YYYYMM') = '202301';

Pourquoi est-il différent ? Comparer avec le précédant en utilisant EXPLAIN ANALYZE.

Le plan cette fois est un parcours de table. L’index est ignoré, toute la table est lue :

                            QUERY PLAN                            
------------------------------------------------------------------
 Seq Scan on donnees  (cost=0.00..110652.25 rows=24751 width=30)
   Filter: (to_char(horodatage, 'YYYYMM'::text) = '202301'::text)

Si le parallélisme est activé, il existe une variante parallélisée de ce plan :

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather  (cost=1000.00..70812.96 rows=24751 width=30)
   Workers Planned: 2
   ->  Parallel Seq Scan on donnees  (cost=0.00..67337.86 rows=10313 width=30)
         Filter: (to_char(horodatage, 'YYYYMM'::text) = '202301'::text)

La raison du changement de plan est le changement du critère. C’est évident pour un humain, mais PostgreSQL ne fait pas l’équivalence entre les deux formulations du critère sur le mois de janvier. Or il n’y a pas d’index sur la fonction to_char(horodatage, 'YYYYMM') (il serait possible d’en créer un).

Si l’on compare les deux plans en les exécutant réellement, avec EXPLAIN (ANALYZE), on obtient pour la variante avec BETWEEN :

EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-01-01'::date AND '2023-01-31'::date ;
                                  QUERY PLAN                                   
------------------------------------------------------------------------------- 
 Index Scan using donnees_horodatage_idx on donnees  (cost=0.43..933.75 rows=28716 width=30) (actual time=0.060..9.405 rows=19600 loops=1)
   Index Cond: ((horodatage >= '2023-01-01'::date) AND (horodatage <= '2023-01-31'::date))
   Buffers: shared hit=6 read=191
 Planning:
   Buffers: shared hit=8
 Planning Time: 0.072 ms
 Execution Time: 10.472 ms

et pour la variante avec to_char :

EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM donnees
WHERE to_char (horodatage, 'YYYYMM') = '202301';
                                  QUERY PLAN                                   
------------------------------------------------------------------------------- 
 Seq Scan on donnees  (cost=0.00..110652.25 rows=24751 width=30) (actual time=0.013..1503.631 rows=19600 loops=1)
   Filter: (to_char(horodatage, 'YYYYMM'::text) = '202301'::text)
   Rows Removed by Filter: 4930625
   Buffers: shared hit=16063 read=20336
 Planning Time: 0.025 ms
 Execution Time: 1504.379 ms

La dernière ligne indique 10 ms pour la variante avec BETWEEN contre 1,5 s pour la variante avec to_char : l’utilisation de l’index est nettement plus intéressante que le parcours complet de la table. Le plan indique aussi que beaucoup plus de blocs (buffers) ont été lus.

Quel est le plan pour la même requête, cette fois sur deux mois ?

EXPLAIN
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-03-01'::date AND '2023-04-30'::date;

Relancer avec EXPLAIN (ANALYZE).

On s’attend au même plan que pour la recherche sur janvier, mais PostgreSQL prévoit cette fois un parcours complet :

                                  QUERY PLAN                                   
------------------------------------------------------------------------------- 
 Seq Scan on donnees  (cost=0.00..110652.25 rows=4184350 width=30)
   Filter: ((horodatage >= '2023-03-01'::date) AND (horodatage <= '2023-04-30'::date))

En effet, il y a beaucoup plus de lignes à récupérer sur mars-avril qu’en janvier. La mention rows indique l’estimation des lignes ramenées et indique 4,2 millions de lignes sur les 4,9 de la table ! Le plus efficace est donc de lire directement la table. Les statistiques permettent donc à PostgreSQL de changer de stratégie suivant les volumétries attendues.

Une exécution réelle indique que cette estimation est bonne, et dure logiquement à peu près aussi longtemps que le parcours complet ci-dessus :

EXPLAIN (ANALYZE)
SELECT * FROM donnees
WHERE horodatage BETWEEN '2023-03-01'::date AND '2023-04-30'::date ;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Seq Scan on donnees  (cost=0.00..110652.25 rows=4184350 width=30) (actual time=160.385..1255.020 rows=4182160 loops=1)
   Filter: ((horodatage >= '2023-03-01'::date) AND (horodatage <= '2023-04-30'::date))
   Rows Removed by Filter: 768065
 Planning Time: 0.470 ms
 Execution Time: 1378.383 ms

Jointure :

Quel est le plan prévu pour cette jointure sur toutes les données d’une machine ?

EXPLAIN
SELECT *
FROM donnees INNER JOIN machines USING (id_machine)
WHERE machines.code = 'E4DA3B' AND type = 5;

Le plan est :

                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.71..5033.11 rows=4950 width=75)
   ->  Index Scan using machines_type_code_key on machines  (cost=0.28..8.29 rows=1 width=49)
         Index Cond: ((type = 5) AND ((code)::text = 'E4DA3B'::text))
   ->  Index Scan using donnees_id_machine_idx on donnees  (cost=0.43..4124.77 rows=90004 width=30)
         Index Cond: (id_machine = machines.id_machine)

Il s’agit :

  • d’un accès à machines par l’index sur machines (type, code) (cet index marque l’unicité) ;
  • suivi d’un accès à donnees, toujours par l’index sur le champ indexé id_machine.

Quel est le plan prévu pour la requête suivante, qui récupère toutes les données d’après juillet pour un type de machines donné ? Quelles en sont les 3 étapes ?

EXPLAIN
SELECT description, horodatage, valeur1 
FROM donnees INNER JOIN machines USING (id_machine)
WHERE machines.type = 1
AND donnees.horodatage > '2023-07-01' ;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
Hash Join  (cost=30.67..8380.56 rows=138788 width=47)
   Hash Cond: (donnees.id_machine = machines.id_machine)
   ->  Index Scan using donnees_horodatage_idx on donnees  (cost=0.43..7671.54 rows=257492 width=16)
         Index Cond: (horodatage > '2023-07-01 00:00:00+02'::timestamp with time zone)
   ->  Hash  (cost=23.50..23.50 rows=539 width=39)
         ->  Seq Scan on machines  (cost=0.00..23.50 rows=539 width=39)
               Filter: (type = 1)

Il s’agit ici d’une jointure en hash join, courante dans les jointures brassant beaucoup de lignes.

PostgreSQL commence par un parcours complet de machines (type = 1 concerne la plupart des machines ). Puis il crée une « table de hachage » à partir des id_machine des lignes résultantes. Il parcoure donnees en se basant sur l’index sur la date. Les lignes résultantes seront comparées au contenu de la table de hachage pour savoir s’il faut garder les valeurs.

Manipuler explain (base magasin)

  • Créer une base de données nommée magasin.

Si l’on est connecté à la base, en tant que superutilisateur postgres :

CREATE DATABASE magasin;

Alternativement, depuis le shell, en tant qu’utilisateur système postgres :

postgres$ createdb --echo  magasin
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE magasin;
  • Importer le jeu de données d’exemple :

La base magasin (dump de 96 Mo, pour 667 Mo sur le disque au final) peut être téléchargée et restaurée comme suit dans une nouvelle base magasin :

createdb magasin
curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump
pg_restore -d magasin  /tmp/magasin.dump
# le message sur public préexistant est normal
rm -- /tmp/magasin.dump

Les données sont dans deux schémas, magasin et facturation. Penser au search_path.

Pour ce TP, figer les paramètres suivants :

SET max_parallel_workers_per_gather to 0;
SET seq_page_cost TO 1 ;
SET random_page_cost TO 4 ;
  • Le schéma à utiliser se nomme également magasin.
  • Consulter les tables.

Le schéma par défaut public ne contient effectivement aucune table intéressante.

\dn
     Liste des schémas
     Nom     | Propriétaire
-------------+--------------
 facturation | postgres
 magasin     | postgres
 public      | postgres
SET search_path to magasin ;
 \dt+
                                     Liste des relations
 Schéma  |         Nom          | Type  | Propriétaire | Persistence |   Taille   | D…
---------+----------------------+-------+--------------+-------------+------------+---
 magasin | clients              | table | postgres     | permanent   | 8248 kB    |
 magasin | commandes            | table | postgres     | permanent   | 79 MB      |
 magasin | conditions_reglement | table | postgres     | permanent   | 16 kB      |
 magasin | contacts             | table | postgres     | permanent   | 24 MB      |
 magasin | etats_retour         | table | postgres     | permanent   | 16 kB      |
 magasin | fournisseurs         | table | postgres     | permanent   | 840 kB     |
 magasin | lignes_commandes     | table | postgres     | permanent   | 330 MB     |
 magasin | lots                 | table | postgres     | permanent   | 74 MB      |
 magasin | modes_expedition     | table | postgres     | permanent   | 16 kB      |
 magasin | modes_reglement      | table | postgres     | permanent   | 16 kB      |
 magasin | numeros_sequence     | table | postgres     | permanent   | 16 kB      |
 magasin | pays                 | table | postgres     | permanent   | 16 kB      |
 magasin | pays_transporteurs   | table | postgres     | permanent   | 8192 bytes |
 magasin | produit_fournisseurs | table | postgres     | permanent   | 216 kB     |
 magasin | produits             | table | postgres     | permanent   | 488 kB     |
 magasin | regions              | table | postgres     | permanent   | 16 kB      |
 magasin | transporteurs        | table | postgres     | permanent   | 16 kB      |
 magasin | types_clients        | table | postgres     | permanent   | 16 kB      |

Conseils pour la suite :

  • Préciser \timing on dans psql pour afficher les temps d’exécution de la recherche.

  • Pour rendre les plans plus lisibles, désactiver le JIT et le parallélisme :

SET jit TO off ;
SET max_parallel_workers_per_gather TO 0 ;
  • Lancer un ANALYZE sur la base.
ANALYZE ;

Le but est de chercher une personne nommée Moris Russel dans la table contacts par les champs prenom et nom.

  • Quel est le plan qu’utilisera PostgreSQL pour le trouver ?
  • À combien de résultats le planificateur s’attend-il ?
 EXPLAIN SELECT * FROM contacts WHERE nom ='Russel'  AND prenom = 'Moris' ;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on contacts  (cost=0.00..4693.07 rows=1 width=298)
   Filter: (((nom)::text = 'Russel'::text) AND ((prenom)::text = 'Moris'::text))

La table sera entièrement parcourue (Seq Scan). PostgreSQL pense qu’il trouvera une ligne.

  • Afficher le résultat.
 SELECT * FROM contacts WHERE nom ='Russel' AND prenom = 'Moris' ;
-[ RECORD 1 ]----------------------------------------
contact_id  | 26452
login       | Russel_Moris
passwd      | 9f81a90c36dd3c60ff06f3c800ae4c1b
email       | ubaldo@hagenes-kulas-and-oberbrunner.mo
nom         | Russel
prenom      | Moris
adresse1    | 02868 Norris Greens
adresse2    | ¤
code_postal | 62151
ville       | Laguna Beach
code_pays   | CA
telephone   | {"+(05) 4.45.08.11.03"}

Temps : 34,091 ms

La requête envoie bien une ligne, et l’obtenir a pris 34 ms sur cette machine avec SSD.

  • Quel est le plan réellement exécuté ?

Il faut relancer la requête :

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM contacts
  WHERE nom ='Russel' AND prenom = 'Moris' ;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on contacts  (cost=0.00..4693.07 rows=1 width=297)
                       (actual time=3.328..16.789 rows=1 loops=1)
   Filter: (((nom)::text = 'Russel'::text) AND ((prenom)::text = 'Moris'::text))
   Rows Removed by Filter: 110004
   Buffers: shared hit=3043
 Planning Time: 0.052 ms
 Execution Time: 16.848 ms

PostgreSQL a à nouveau récupéré une ligne. Ici, cela n’a pris que 17 ms.

La table a été parcourue entièrement, et 110 004 lignes ont été rejetées. La ligne shared hit indique que 3043 blocs de 8 ko ont été lus dans le cache de PostgreSQL. La requête précédente a apparemment suffi à charger la table entière en cache (il n’y a pas de shared read).

  • Rechercher la même personne par son contact_id.
  • Quel est le plan ?
 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM contacts WHERE contact_id = 26452 ;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using contacts_pkey on contacts (cost=0.42..8.44 rows=1 width=297)
                                     (actual time=0.057..0.058 rows=1 loops=1)
   Index Cond: (contact_id = 26452)
   Buffers: shared hit=4 read=3
 Planning:
   Buffers: shared hit=6 read=3
 Planning Time: 0.137 ms
 Execution Time: 0.081 ms

PostgreSQL estime correctement trouver une ligne. Cette fois, il s’agit d’un Index Scan, en l’occurrence sur l’index de la clé primaire. Le résultat est bien plus rapide : 137 µs pour planifier, 81 µs pour exécuter.

Les blocs lus se répartissent entre read et hit : une partie était en mémoire, notamment ceux liés à la table, puisque la table aussi a été interrogée (l’index ne contient que les données de contact_id) ; mais l’index n’était pas en mémoire.

  • La requête suivante recherche tous les fournisseurs résidents d’Hollywood.
SELECT c.nom, c.prenom FROM contacts c
INNER JOIN fournisseurs f
ON (f.contact_id = c.contact_id)
WHERE c.ville = 'Hollywood' ;
  • Quel est le plan prévu ?
  • Que donne-t-il à l’exécution ?

Le plan simplifié est :

 EXPLAIN (COSTS OFF)
 SELECT c.nom, c.prenom
 FROM contacts c INNER JOIN fournisseurs f ON (f.contact_id = c.contact_id)
 WHERE c.ville = 'Hollywood' ;
                    QUERY PLAN
-----------------------------------------------------
 Merge Join
   Merge Cond: (c.contact_id = f.contact_id)
   ->  Index Scan using contacts_pkey on contacts c
         Filter: ((ville)::text = 'Hollywood'::text)
   ->  Sort
         Sort Key: f.contact_id
         ->  Seq Scan on fournisseurs f

Il consiste à parcourir intégralement la table fournisseurs (Seq Scan), à trier sa colonne contact_id, et à effectuer une jointure de type Merge Join avec la clé primaire de la table contacts. En effet, un Merge Join s’effectue entre deux ensembles triés : l’index l’est déjà, mais fournisseurs.contact_id ne l’est pas.

Noter qu’aucune donnée n’est récupérée de fournisseurs. Il est pourtant nécessaire de la joindre à contacts car de nombreux contacts ne sont pas des fournisseurs.

Exécutée, cette requête renvoie le plan suivant :

EXPLAIN (ANALYZE,BUFFERS)
SELECT c.nom, c.prenom FROM contacts c
INNER JOIN fournisseurs f ON (f.contact_id = c.contact_id)
WHERE c.ville = 'Hollywood' ;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Merge Join  (cost=864.82..1469.89 rows=31 width=14)
             (actual time=5.079..11.063 rows=32 loops=1)
   Merge Cond: (c.contact_id = f.contact_id)
   Buffers: shared hit=7 read=464
   ->  Index Scan using contacts_pkey on contacts c
                                    (cost=0.42..6191.54 rows=346 width=22)
                                    (actual time=0.029..4.842 rows=33 loops=1)
         Filter: ((ville)::text = 'Hollywood'::text)
         Rows Removed by Filter: 11971
         Buffers: shared hit=7 read=364
   ->  Sort  (cost=864.39..889.39 rows=10000 width=8)
             (actual time=5.044..5.559 rows=10000 loops=1)
         Sort Key: f.contact_id
         Sort Method: quicksort  Memory: 853kB
         Buffers: shared read=100
         ->  Seq Scan on fournisseurs f (cost=0.00..200.00 rows=10000 width=8)
                                        (actual time=0.490..2.960 rows=10000 loops=1)
               Buffers: shared read=100
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.150 ms
 Execution Time: 11.174 ms

Ce plan est visible graphiquement sur https://explain.dalibo.com/plan/dum :

Plan d’exécution

Le Seq Scan sur fournisseurs lit 10 000 lignes (100 blocs, hors du cache), ce qui était prévu. Cela prend 2,96 ms. Le nœud Sort trie les contact_id et consomme 853 ko en mémoire. Il renvoie bien sûr aussi 10 000 lignes, et il commence à le faire au bout de 5,04 ms.

La jointure peut commencer. Il s’agit de parcourir simultanément l’ensemble que l’on vient de trier d’une part, et l’index contacts_pkey d’autre part. À cette occasion, le nœud Index Scan va filtrer les lignes récupérées en comparant à la valeur de ville, et en exclue 11 971. Au final, le parcours de l’index sur contacts renvoie 33 lignes, et non les 346 estimées au départ (valeur dérivée de l’estimation du nombre de lignes où la ville est « Hollywood »). Si l’on regarde les coûts calculés, c’est cette étape qui est la plus lourde (6191).

En haut, on peut lire qu’au total 464 blocs ont été lus hors du cache, et 7 dedans. Ces valeurs varient bien sûr en fonction de l’activité précédente sur la base. Au final, 32 lignes sont retournées, ce qui était attendu.

Le temps écoulé est de 11,17 ms. La majorité de ce temps s’est déroulé pendant le Merge Join (11,0-5,0 = 6 ms), dont l’essentiel est constitué par le parcours de l’index.

PostgreSQL : Optimisations SQL

PostgreSQL

Introduction

L’optimisation doit porter sur :

  • Le matériel
    • serveur, distribution, kernel, stockage, réseau…
  • Le moteur de la base :
    • postgresql.conf & co
    • l’organisation des fichiers de PostgreSQL
  • L’application
    • schéma, requêtes, vues…

Les bases de données sont des systèmes très complexes. Afin d’en tirer toutes les performances possibles, l’optimisation doit porter sur un très grand nombre de composants différents : le serveur qui héberge la base de données, et de manière générale tout l’environnement matériel, les processus faisant fonctionner la base de données, les fichiers et disques servant à son stockage, le partitionnement, mais aussi, et surtout, l’application elle-même. C’est sur ce dernier point que les gains sont habituellement les plus importants. (Les autres sont traités entre autres dans le module de formation J1.)

Ce module se focalise sur ce dernier point. Il n’aborde pas les plans d’exécution à proprement parler, ceux-ci étant traités ailleurs.


Axes d’optimisation

« 80% des effets sont produits par 20% des causes. » (Principe de Pareto)

  • Il est illusoire d’optimiser une application sans connaître les sources de ralentissement
  • Cibler l’optimisation :
    • trouver ces « 20% »
    • ne pas micro-optimiser ce qui n’a pas d’influence

Le principe de Pareto et la loi de Pareto sont des constats empiriques. On peut définir mathématiquement une distribution vérifiant le principe de Pareto.

Le pourcentage de la population dont la richesse est supérieure à une valeur x est proportionnel à A/x^α »

Vilfredo Pareto, économiste du XIXe siècle

De nombreux phénomènes suivent cette distribution. Dans le cadre de PostgreSQL, cela se vérifie souvent. Une poignée de requêtes peut être responsable de l’essentiel du ralentissement. Une table mal conçue peut être responsable de la majorité de vos problèmes. Le temps de développement n’était pas infini, il ne sert à rien de passer beaucoup de temps à optimiser chaque paramètre sans avoir identifié les principaux consommateurs de ressource.


Quelles requêtes optimiser ?

Seul un certain nombre de requêtes sont critiques

  • Identification (outil de profiling)
    • à optimiser prioritairement
  • Différencier
    • longues en temps cumulé = coûteuses en ressources serveur
    • longues et interactives = mauvais ressenti des utilisateurs

Toutes les requêtes ne sont pas critiques, seul un certain nombre d’entre elles méritent une attention particulière. Il y a deux façon de déterminer les requêtes qui nécessitent d’être travaillées.

La première dépend du ressenti utilisateur : un utilisateur devant son écran est notoirement impatient. Il faudra donc en priorité traiter les requêtes interactives. Certaines auront déjà d’excellents temps de réponse, d’autres pourront être améliorées.

L’autre méthode pour déterminer les requêtes à optimiser consiste à utiliser les outils de profiling habituels, dont nous allons voir quelques exemples. Ces outils permettent de déterminer les requêtes les plus fréquemment exécutées, et d’établir un classement des requêtes qui ont nécessité le plus de temps cumulé à leur exécution (voir l’onglet Time consuming queries (N) d’un rapport pgBadger). Les requêtes les plus fréquemment exécutées méritent également qu’on leur porte attention. Même si leur temps d’exécution cumulé est acceptable, leur optimisation peut permettre d’économiser quelques ressources du serveur, et une dérive peut avoir vite de gros impacts.


Recherche des axes d’optimisation

Trois outils courants permettent d’identifier rapidement les requêtes les plus consommatrices sur un serveur. Les outils pour PostgreSQL ont le fonctionnement suivant :

pgBadger :

pgBadger est un analyseur de fichiers de traces PostgreSQL. Il nécessite de tracer dans les journaux applicatifs de PostgreSQL toutes les requêtes et leur durée. L’outil sait repérer des requêtes identiques avec des paramètres différents. Il les analyse et retourne les requêtes les plus fréquemment exécutées, les plus gourmandes unitairement, les plus gourmandes en temps cumulé (somme des temps unitaires).

Pour plus de détails, voir https://dali.bo/h1_html#pgbadger.

pg_stat_statements :

L’extension pg_stat_statements est livrée avec PostgreSQL. Elle trace pour chaque ordre (même SQL, paramètres différents) exécuté sur l’instance son nombre d’exécutions, sa durée cumulée, et un certain nombre d’autres statistiques très utiles.

Si elle est présente, la requête suivante permet de déterminer les requêtes dont les temps d’exécution cumulés sont les plus importants :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls AS avg_time, s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.total_exec_time DESC
 LIMIT 10 ;

Et la requête suivante permet de déterminer les requêtes les plus fréquemment appelées :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls  AS avg_time, s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.calls DESC
 LIMIT 10;

Pour plus de détails sur les métriques relevées, voir https://dali.bo/h2_html#pg_stat_statements, et pour l’installation et des exemples de requêtes, voir https://dali.bo/x2_html#pg_stat_statements, ou encore la documentation officielle

PoWA :

PoWA est une extension disponible dans les dépôts du PGDG, qui s’appuie sur pg_stat_statements pour historiser l’activité du serveur. Une interface web permet ensuite de visualiser l’activité ainsi historisée et repérer les requêtes problématiques avec les fonctionnalités de drill-down de l’interface.


SQL et requêtes

Le SQL :

  • est un langage déclaratif :
    • on décrit le résultat, pas la façon de l’obtenir
    • c’est le travail de la base de déterminer le traitement à effectuer
  • décrit un traitement ensembliste :
    • ≠ traitement procédural
    • « on effectue des opérations sur des relations pour obtenir des relations »
  • est normalisé

Le langage SQL a été normalisé par l’ANSI en 1986 et est devenu une norme ISO internationale en 1987. Il s’agit de la norme ISO 9075. Elle a subi plusieurs évolutions dans le but d’ajouter des fonctionnalités correspondant aux attentes de l’industrie logicielle. Parmi ces améliorations, notons l’intégration de quelques fonctionnalités objet pour le modèle relationnel-objet. La dernière version stable de la norme est SQL:2023 (juin 2023).


Opérateurs relationnels

Les opérateurs purement relationnels :

  • Projection = SELECT
    • choix des colonnes
  • Sélection = WHERE
    • choix des enregistrements
  • Jointure = FROM/JOIN
    • choix des tables
  • Bref : tout ce qui détermine sur quelles données on travaille

Tous ces opérateurs sont optimisables : il y a 40 ans de théorie mathématique développée afin de permettre l’optimisation de ces traitements. L’optimiseur fera un excellent travail sur ces opérations, et les organisera de façon efficace.

Par exemple : a JOIN b JOIN c WHERE c.col=constante sera très probablement réordonné en c JOIN b JOIN a WHERE c.col=constante ou c JOIN a JOIN b WHERE c.col=constante. Le moteur se débrouillera aussi pour choisir le meilleur algorithme de jointure pour chacune, suivant les volumétries ramenées.


Opérateurs non-relationnels

Les autres opérateurs sont non-relationnels :

  • ORDER BY
  • GROUP BY/DISTINCT
  • HAVING
  • sous-requête, vue
  • fonction (classique, d’agrégat, analytique)
  • jointure externe

Ceux-ci sont plus difficilement optimisables : ils introduisent par exemple des contraintes d’ordre dans l’exécution :

SELECT * FROM table1
WHERE montant > (
 SELECT avg(montant) FROM table1 WHERE departement='44'
);

On doit exécuter la sous-requête avant la requête.

Les jointures externes sont relationnelles, mais posent tout de même des problèmes et doivent être traitées prudemment.

SELECT * FROM t1
LEFT JOIN t2 on (t1.t2id=t2.id)
JOIN t3 on (t1.t3id=t3.id) ;

Il faut faire les jointures dans l’ordre indiqué : joindre t1 à t3 puis le résultat à t2 pourrait ne pas amener le même résultat (un LEFT JOIN peut produire des NULL). Il est donc préférable de toujours mettre les jointures externes en fin de requête, sauf besoin précis : on laisse bien plus de liberté à l’optimiseur.

Le mot clé DISTINCT ne doit être utilisé qu’en dernière extrémité. On le rencontre très fréquemment dans des requêtes mal écrites qui produisent des doublons, afin de corriger le résultat — souvent en passant par un tri de l’ensemble du résultat, ce qui est coûteux.


Données utiles

Le volume de données récupéré a un impact sur les performances.

  • N’accéder qu’aux tables nécessaires
  • N’accéder qu’aux colonnes nécessaires
    • viser Index Only Scan
    • se méfier : stockage TOAST
  • Plus le volume de données à traiter est élevé, plus les opérations seront lentes :
    • tris et Jointures
    • éventuellement stockage temporaire sur disque pour certains algorithmes

Éviter les SELECT * :

C’est une bonne pratique, car la requête peut changer de résultat suite à un changement de schéma, ce qui risque d’entraîner des conséquences sur le reste du code.

Ne récupérer que les colonnes utilisées :

Dans beaucoup de cas, PostgreSQL sait repérer des colonnes qui figurent dans la requête mais sont finalement inutiles. Mais il n’est pas parfait. Surtout, il ne pourra pas repérer que vous n’avez pas réellement besoin d’une colonne issue d’une requête. En précisant uniquement les colonnes nécessaires, le moteur peut parfois utiliser des parcours plus simples, notamment des Index Only Scans. Il peut aussi éviter de lire les colonnes à gros contenu qui sont généralement déportés dans la partie TOAST d’une table (des fichiers séparés de la table principale pour certains grands champs, transparents à l’utilisation mais dont l’accès n’est pas gratuit).

Éviter les jointures sur des tables inutiles :

Il n’y a que peu de cas où l’optimiseur peut supprimer de lui-même l’accès à une table inutile. Notamment, PostgreSQL le fait dans le cas d’un LEFT JOIN sur une table inutilisée dans le SELECT, au travers d’une clé étrangère, car on peut garantir que cette table est effectivement inutile.


Limiter le nombre de requêtes

SQL : langage ensembliste

  • Ne pas faire de traitement unitaire par enregistrement
  • Utiliser les jointures, ne pas accéder à chaque table une par une
  • Une seule requête, parcours de curseur
  • Fréquent avec les ORM

Les bases de données relationnelles sont conçues pour manipuler des relations, pas des enregistrements unitaires. Le langage SQL (et même les autres langages relationnels qui ont existé comme QUEL, SEQUEL) est conçu pour permettre la manipulation d’un gros volume de données, et la mise en correspondance (jointure) d’informations. Une base de données relationnelle n’est pas une simple couche de persistance.

Le fait de récupérer en une seule opération l’ensemble des informations pertinentes est aussi, indépendamment du langage, un gain de performance énorme, car il permet de s’affranchir en grande partie des latences de communication entre la base et l’application.

Préparons un jeu de test :

CREATE TABLE test (id int, valeur varchar);
INSERT INTO test SELECT i,chr(i%94+32) FROM generate_series (1,1000000) g(i);
ALTER TABLE test ADD PRIMARY KEY (id);
VACUUM ANALYZE test ;

Le script perl génère 1000 ordres pour récupérer des enregistrements un par un, avec une requête préparée pour être dans le cas le plus efficace :

#!/usr/bin/perl -w
print "PREPARE ps (int) AS SELECT * FROM test WHERE id=\$1;\n";
for (my $i=0;$i<=1000;$i++)
{
        print "EXECUTE ps($i);\n";
}

Exécutons ce code :

time perl demo.pl  | psql > /dev/null

real    0m44,476s
user    0m0,137s
sys     0m0,040s

La durée totale de ces 1000 requêtes dépend fortement de la distance au serveur de base de données. Si le serveur est sur le même sous-réseau, on peut descendre à une seconde. Noter que l’établissement de la connexion au serveur n’a lieu qu’une fois.

Voici maintenant la même chose, en un seul ordre SQL, avec la même volumétrie en retour :

time psql -c "SELECT * FROM test WHERE id BETWEEN 0 AND 1000" > /dev/null

real    0m0,129s
user    0m0,063s
sys     0m0,018s

Les allers-retours au serveur sont donc très coûteux dès qu’ils se cumulent.

Le problème se rencontre assez fréquemment avec des ORM. La plupart d’entre eux fournissent un moyen de traverser des liens entre objets. Par exemple, si une commande est liée à plusieurs articles, un ORM permettra d’écrire un code similaire à celui-ci (exemple en Java avec Hibernate) :

List commandes = sess.createCriteria(Commande.class);

for(Commande cmd : commandes)
{
  // Un traitement utilisant les produits
  // Génère une requête par commande !!
  System.out.println(cmd.getProduits());
}

Tel quel, ce code générera N+1 requête, N étant le nombre de commandes. En effet, pour chaque accès à l’attribut “produits”, l’ORM générera une requête pour récupérer les produits correspondants à la commande.

Le SQL généré sera alors similaire à celui-ci :

SELECT * FROM commande;
SELECT * from produits where commande_id = 1;
SELECT * from produits where commande_id = 2;
SELECT * from produits where commande_id = 3;
SELECT * from produits where commande_id = 4;
SELECT * from produits where commande_id = 5;
SELECT * from produits where commande_id = 6;
...

La plupart des ORM proposent des options pour personnaliser la stratégie d’accès aux collections. Il est extrêmement important de connaître celles-ci afin de permettre à l’ORM de générer des requêtes optimales.

Par exemple, dans le cas précédent, nous savons que tous les produits de toutes les commandes seront utilisés. Nous pouvons donc informer l’ORM de ce fait :

List commandes = sess.createCriteria(Commande.class)
  .setFetchMode('produits', FetchMode.EAGER);

for(Commande cmd : commandes)
{
  // Un traitement utilisant les produits
  System.out.println(cmd.getProduits());
}

Ceci générera une seule et unique requête du type :

SELECT * FROM commandes
LEFT JOIN produits ON commandes.id = produits.commande_id;

Sous-requêtes dans un IN

Un Semi Join peut être très efficace (il ne lit pas tout)

SELECT * FROM t1
  WHERE val1  IN (  SELECT val2 …  )
  • Sinon attention s’il y a beaucoup de valeurs dans la sous-requête !
    • dédoublonner :
    SELECT * FROM t1
      WHERE val1 IN (  SELECT  DISTINCT  val2 …  )
    • surtout : réécriture avec EXISTS (si index disponible)

De la même manière que pour la clause EXISTS, un des intérêts du IN est de savoir quand il n’y a pas besoin de lire toute la sous-requête, comme ici, où seuls 5 blocs de la grande table lots sont lus, qui contiennent déjà toutes les valeurs possibles de transporteur_id :

EXPLAIN (ANALYZE,BUFFERS)
SELECT nom
FROM transporteurs
WHERE transporteur_id IN (
        SELECT transporteur_id
        FROM lots
        WHERE date_depot IS NOT NULL
        ) ;
                               QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..19478.49 rows=5 width=12) (actual time=0.032..0.042 rows=5 loops=1)
   Join Filter: (transporteurs.transporteur_id = lots.transporteur_id)
   Rows Removed by Join Filter: 13
   Buffers: shared hit=5 read=1
   ->  Seq Scan on transporteurs  (cost=0.00..1.05 rows=5 width=20) (actual time=0.004..0.005 rows=5 loops=1)
         Buffers: shared hit=1
   ->  Seq Scan on lots  (cost=0.00..19476.04 rows=1006704 width=8) (actual time=0.005..0.006 rows=4 loops=5)
         Filter: (date_depot IS NOT NULL)
         Buffers: shared hit=4 read=1
 Planning:
   Buffers: shared hit=173 read=1
 Planning Time: 0.664 ms
 Execution Time: 0.069 ms

Mais la requête dans le IN peut être arbitrairement complexe, l’optimisation peut échouer et PostgreSQL peut basculer sur une forme de jointure plus lourde avec un regroupement des valeurs de la sous-requête :

EXPLAIN (ANALYZE,COSTS OFF)
SELECT nom
FROM transporteurs
WHERE transporteur_id IN (
        SELECT transporteur_id + 0  /* modification du critère */
        FROM lots
        WHERE date_depot IS NOT NULL
        ) ;
                               QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join (actual time=139.280..139.283 rows=5 loops=1)
   Hash Cond: (transporteurs.transporteur_id = (lots.transporteur_id + 0))
   ->  Seq Scan on transporteurs (actual time=0.010..0.011 rows=5 loops=1)
   ->  Hash (actual time=139.265..139.265 rows=5 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  HashAggregate (actual time=139.259..139.261 rows=5 loops=1)
               Group Key: (lots.transporteur_id + 0)
               Batches: 1  Memory Usage: 24kB
               ->  Seq Scan on lots (actual time=0.010..70.184 rows=1006704 loops=1)
                     Filter: (date_depot IS NOT NULL)
 Planning Time: 0.201 ms
 Execution Time: 139.325 ms

Le dédoublonnage explicite au sein même de la sous-requête est alors parfois une bonne idée, même s’il vient forcément avec un certain coût :

EXPLAIN (ANALYZE,BUFFERS)
SELECT nom
FROM transporteurs
WHERE transporteur_id IN (
        SELECT   DISTINCT   transporteur_id + 0
        FROM lots
        WHERE date_depot IS NOT NULL
        ) ;
                               QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join (actual time=46.385..48.943 rows=5 loops=1)
   Hash Cond: (transporteurs.transporteur_id = ((lots.transporteur_id + 0)))
   ->  Seq Scan on transporteurs (actual time=0.005..0.006 rows=5 loops=1)
   ->  Hash (actual time=46.375..48.931 rows=5 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Unique (actual time=46.367..48.925 rows=5 loops=1)
               ->  Sort (actual time=46.366..48.922 rows=15 loops=1)
                     Sort Key: ((lots.transporteur_id + 0))
                     Sort Method: quicksort  Memory: 25kB
                     ->  Gather (actual time=46.306..48.914 rows=15 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           ->  HashAggregate (actual time=44.707..44.708 rows=5 loops=3)
                                 Group Key: (lots.transporteur_id + 0)
                                 Batches: 1  Memory Usage: 24kB
                                 Worker 0:  Batches: 1  Memory Usage: 24kB
                                 Worker 1:  Batches: 1  Memory Usage: 24kB
                                 ->  Parallel Seq Scan on lots (actual time=0.008..23.330 rows=335568 loops=3)
                                       Filter: (date_depot IS NOT NULL)
 Planning Time: 0.096 ms
 Execution Time: 48.979 ms

Mais on ne retrouve pas les performances de la première version.

Une autre possibilité est de réécrire la requête avec EXISTS mais cela n’a d’intérêt ici que si on peut indexer le champ calculé ; auquel cas les requêtes ci-dessus peuvent redevenir efficaces.

SELECT nom
FROM transporteurs t
WHERE EXISTS (
        SELECT 'ok'
        FROM lots l
        WHERE date_depot IS NOT NULL
        AND l.transporteur_id+0 /* à indexer */
                          = t.transporteur_id
        ) ;

Sous-requêtes liées

À éviter :

SELECT a,b
  FROM t1
  WHERE val IN (  SELECT f(b)  )
  • un appel de fonction ou sous-requête par ligne !
  • est-ce voulu ?
  • transformer en clause WHERE
  • penser à la clause LATERAL

Dans l’exemple ci-dessus, le résultat de la sous-requête dépend de la valeur b de chaque ligne de t1. On a donc autant d’appels, ce qui peut être une catastrophe. L’expérience montre que ce n’est parfois pas vraiment voulu…

Selon la complexité de la sous-requête, elle peut être réécrite en une simple clause WHERE. Il faut connaître aussi la clause LATERAL[] (https://docs.postgresql.fr/current/queries-table-expressions.html#QUERIES-LATERAL) dédiée à ce genre de chose et qui a au moins le mérite d’être lisible et explicite.


Sous-requêtes : équivalences IN/EXISTS/LEFT JOIN

Ces sous-requêtes sont strictement équivalentes (Semi-join) :

SELECT * FROM t1
WHERE fk IN ( SELECT pk FROM t2 WHERE … )

SELECT * FROM t1
WHERE EXISTS  ( SELECT 1 FROM t2 WHERE t2.pk = t1.fk AND … )

SELECT t1.*
FROM   t1   LEFT JOIN t2   ON (t1.fk=t2.pk)
WHERE
     t2.id IS NULL

(Et Anti-join pour les variantes avec NOT)

  • Attention à NOT IN : préférer NOT EXISTS

Les seules sous-requêtes sans danger sont celles qui retournent un ensemble constant et ne sont exécutés qu’une fois, ou celles qui expriment un Semi-Join (test d’existence) ou Anti-Join (test de non-existence), qui sont presque des jointures : la seule différence est qu’elles ne récupèrent pas l’enregistrement de la table cible.

Attention toutefois à l’utilisation du prédicat NOT IN, ils peuvent générer des plans d’exécution catastrophiques, avec une exécution de la sous-requête par ligne.

EXPLAIN SELECT *
FROM    commandes c
WHERE   numero_commande NOT IN (SELECT l.numero_commande
                                FROM   lignes_commandes l );
                               QUERY PLAN
-------------------------------------------------------------------------------
 Gather  (cost=1000.00..22803529388.17 rows=500000 width=51)
   Workers Planned: 2
   ->  Parallel Seq Scan on commandes c  (cost=0.00..22803478388.17 rows=208333 width=51)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..101602.11 rows=3141807 width=8)
                 ->  Seq Scan on lignes_commandes l  (cost=0.00..73620.07 rows=3141807 width=8)

La raison est la suivante : si un NULL est présent dans la liste du NOT IN, NOT IN vaut systématiquement false. Or, nous savons qu’il n’y aura pas de numero_commandes à NULL. (Dans cette requête précise, PostgreSQL aurait pu le deviner car le champ lignes_commandes.numero_commande est NOT NULL, mais il n’en est pas encore capable.) Une réécriture avec EXISTS est strictement équivalente et produit un plan d’exécution largement plus intéressant avec un Hash Right Anti Join :

EXPLAIN SELECT *
FROM commandes
WHERE NOT EXISTS ( SELECT 1
                   FROM lignes_commandes l
                   WHERE l.numero_commande = commandes.numero_commande );
                                 QUERY PLAN
------------------------------------------------------------------------------
 Gather  (cost=24604.00..148053.15 rows=419708 width=51)
   Workers Planned: 2
   ->  Parallel Hash Right Anti Join  (cost=23604.00..105082.35 rows=174878 width=51)
         Hash Cond: (l.numero_commande = commandes.numero_commande)
         ->  Parallel Seq Scan on lignes_commandes l  (cost=0.00..55292.86 rows=1309086 width=8)
         ->  Parallel Hash  (cost=14325.67..14325.67 rows=416667 width=51)
               ->  Parallel Seq Scan on commandes  (cost=0.00..14325.67 rows=416667 width=51)

Les vues

Une vue est une requête pré-déclarée en base.

  • Équivalent relationnel d’une fonction
  • Si utilisée dans une autre requête, elle est traitée comme une sous-requête
  • et inlinée
  • Pas de problème si elle est relationnelle…

Les vues sont des requêtes dont le code peut être inclus dans une autre requête, comme s’il s’agissait d’une sous-requête. Les vues sont très pratiques en SQL et, en théorie, permettent de séparer le modèle physique (les tables) de ce que voient les développeurs, et donc de faire évoluer le modèle physique sans impact pour le développement. Elles sont surtout très pratiques pour rendre les requêtes plus lisibles, permettre la réutilisation de code SQL, et masquer la complexité à des utilisateurs peu avertis.

Dans le cas idéal, une vue reste relationnelle et donc ne contient que SELECT, FROM et WHERE, et elle peut être fusionnée avec le reste de la requête ; y compris avec des vues basées sur des vues. Ici les critères de deux vues imbriquées se retrouvent dans un même nœud :

CREATE OR REPLACE VIEW v_test_az
AS SELECT * FROM test
WHERE valeur BETWEEN 'A' AND 'Z' ;

CREATE OR REPLACE VIEW v_test_1000_az
AS SELECT * FROM v_test_az
WHERE id < 100 ;

EXPLAIN SELECT * FROM v_test_1000_az ;
                                 QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.42..10.68 rows=54 width=6)
   Index Cond: (id < 100)
   Filter: (((valeur)::text >= 'A'::text) AND ((valeur)::text <= 'Z'::text))

Éviter les vues non-relationnelles

  • Attention aux vues avec DISTINCT, GROUP BY etc.
    • tous les problèmes des sous-requêtes déjà vus
    • impossible de l’inliner
    • barrière d’optimisation
    • …et mauvaises performances
  • Les vues sont dangereuses en termes de performance
    • masquent la complexité
  • Penser aux vues matérialisées si la requête est lourde
En pratique, les vues sont souvent sources de ralentissement : elles masquent la complexité, et l’utilisateur crée alors sans le savoir des requêtes très complexes, mettant en jeu des dizaines de tables (voire des dizaines de fois les mêmes tables !).

Avant d’utiliser une vue, il faut s’intéresser un peu à son contenu et ce qu’elle fait.

On retrouve aussi toute la complexité liée aux sous-requêtes, puisqu’une vue en est l’équivalent. En particulier, il faut se méfier des vues contenant des opérations non-relationnelles, qui peuvent empêcher de nombreuses optimisations. En voici un exemple simple :

CREATE OR REPLACE VIEW v_test_did
AS SELECT DISTINCT ON (id) id,valeur FROM test ;

EXPLAIN (ANALYZE, COSTS OFF)
  SELECT id,valeur
  FROM v_test_did
  WHERE valeur='b' ;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Subquery Scan on v_test_did (actual time=0.070..203.584 rows=10638 loops=1)
   Filter: ((v_test_did.valeur)::text = 'b'::text)
   Rows Removed by Filter: 989362
   ->  Unique (actual time=0.017..158.458 rows=1000000 loops=1)
         ->  Index Scan using test_pkey on test (actual time=0.015..98.200 rows=1000000 loops=1)
 Planning Time: 0.202 ms
 Execution Time: 203.872 ms

On constate que la condition de filtrage sur b n’est appliquée qu’à la fin. C’est normal : à cause du DISTINCT ON, l’optimiseur ne peut pas savoir si l’enregistrement qui sera retenu dans la sous-requête vérifiera valeur='b' ou pas, et doit donc attendre l’étape suivante pour filtrer. Le coût en performances, même avec un volume de données raisonnable, peut être astronomique.


Accès aux données

L’accès aux données est coûteux.

  • Quelle que soit la base
  • Dialogue entre client et serveur
    • plusieurs aller/retours potentiellement
  • Analyse d’un langage complexe
    • SQL PostgreSQL : gram.y de 19000 lignes
  • Calcul de plan :
    • langage déclaratif => converti en impératif à chaque exécution

Dans les captures réseau ci-dessous, le serveur est sur le port 5932.

SELECT * FROM test, 0 enregistrement :

10:57:15.087777 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [P.], seq 109:134, ack 226, win 350,
    options [nop,nop,TS val 2270307 ecr 2269578], length 25
10:57:15.088130 IP 127.0.0.1.5932 > 127.0.0.1.39508:
    Flags [P.], seq 226:273, ack 134, win 342,
    options [nop,nop,TS val 2270307 ecr 2270307], length 47
10:57:15.088144 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [.], ack 273, win 350,
    options [nop,nop,TS val 2270307 ecr 2270307], length 0

SELECT * FROM test, 1000 enregistrements :

10:58:08.542660 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [P.], seq 188:213, ack 298, win 350,
    options [nop,nop,TS val 2286344 ecr 2285513], length 25
10:58:08.543281 IP 127.0.0.1.5932 > 127.0.0.1.39508:
    Flags [P.], seq 298:8490, ack 213, win 342,
    options [nop,nop,TS val 2286344 ecr 2286344], length 8192
10:58:08.543299 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [.], ack 8490, win 1002,
    options [nop,nop,TS val 2286344 ecr 2286344], length 0
10:58:08.543673 IP 127.0.0.1.5932 > 127.0.0.1.39508:
    Flags [P.], seq 8490:14241, ack 213, win 342,
    options [nop,nop,TS val 2286344 ecr 2286344], length 5751
10:58:08.543682 IP 127.0.0.1.39508 > 127.0.0.1.5932:
    Flags [.], ack 14241, win 1012,
    options [nop,nop,TS val 2286344 ecr 2286344], length 0

Un client JDBC va habituellement utiliser un aller/retour de plus, en raison des requêtes préparées : un dialogue pour envoyer la requête et la préparer, et un autre pour envoyer les paramètres. Le problème est la latence du réseau, habituellement : de 50 à 300 μs. Cela limite à 3 000 à 20 000 le nombre d’opérations maximum par seconde par socket. On peut bien sûr paralléliser sur plusieurs sessions, mais cela complique le traitement.

En ce qui concerne le parser : comme indiqué dans ce message : gram.o, le parser fait 1 Mo une fois compilé !


Coût des connexions

Se connecter coûte cher :

  • Authentification, permissions
  • Latence réseau
  • Négociation SSL
  • Création de processus & contexte d’exécution
  • Acquisition de verrous

→ Maintenir les connexions côté applicatif, ou utiliser un pooler.

L’établissement d’une connexion client au serveur est coûteuse, en temps comme ressource. Il faut plusieurs allers-retours réseau pour établir la connexion. Il y a souvent une négociation pour le chiffrement SSL. PostgreSQL doit authentifier l’utilisateur, puis créer son processus, le contexte d’exécution, poser quelques verrous, avant que les requêtes puissent arriver. Pour des requêtes répétées, il est beaucoup plus efficace d’ouvrir une connexion et de la réutiliser pour de nombreuses requêtes.

On peut tester l’impact d’une connexion/déconnexion avec pgbench, dont l’option -C lui demande de se connecter à chaque requête :

$ pgbench pgbench -T 20 -c 10 -j5 -S -C
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 5
duration: 20 s
number of transactions actually processed: 16972
latency average = 11.787 ms
tps = 848.383850 (including connections establishing)
tps = 1531.057609 (excluding connections establishing)

Sans se reconnecter à chaque requête :

$ pgbench pgbench -T 20 -c 10 -j5 -S
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 5
duration: 20 s
number of transactions actually processed: 773963
latency average = 0.258 ms
tps = 38687.524110 (including connections establishing)
tps = 38703.239556 (excluding connections establishing)

On passe de 900 à 38 000 transactions par seconde.

Un pooler est souvent intégré d’office aux serveurs d’applications (par exemple Tomcat). Sinon, PgBouncer est l’outil généralement recommandé. Pour les détails, voir notre module de formation W6.


Penser relationnel

  • Les spécifications sont souvent procédurales, voire objet !
  • Prendre du recul
  • Réfléchir de façon ensembliste
    • on travaille sur des ensembles de données
    • penser aux CTE (WITH)

Si les spécifications disent (version simplifiée bien sûr) :

  • vérifier la présence du client ;
  • s’il est présent, mettre à jour son adresse ;
  • sinon, créer le client avec la bonne adresse,

on peut être tenter d’écrire (pseudo-code client) :

SELECT count(*) from clients where client_name = 'xxx'
INTO compte

IF compte > 0
  UPDATE clients set adresse='yyy' WHERE client_name='xxx'
ELSE
  INSERT client SET client_name='xxx', adresse='yyy'
END IF

D’où 3 requêtes, systématiquement 2 appels à la base. On peut facilement économiser une requête :

UPDATE clients set adresse='yyy' WHERE client_name='xxx'
IF NOT FOUND
  INSERT client SET client_name='xxx', adresse='yyy'
END IF

Les versions modernes de PostgreSQL permettent de tout faire en un seul ordre. L’exemple suivant utilise une fusion des enregistrements dans PostgreSQL avec des CTE.

WITH
  enregistrements_a_traiter AS (
    SELECT * FROM (VALUES ('toto' , 'adresse1' ),('tata','adresse2'))
    AS val(nom_client,adresse)
  ),
  mise_a_jour AS (
    UPDATE client SET adresse=enregistrements_a_traiter.adresse
    FROM enregistrements_a_traiter
    WHERE enregistrements_a_traiter.nom_client=client.nom_client
    RETURNING client.nom_client
  )
INSERT INTO client (nom_client,adresse)
  SELECT nom_client,adresse from enregistrements_a_traiter
  WHERE NOT EXISTS (
    SELECT 1 FROM mise_a_jour
    WHERE mise_a_jour.nom_client=enregistrements_a_traiter.nom_client
  );

Dans beaucoup de cas on peut faire encore plus simple grâce à la clause ON CONFLICT … DO UPDATE (« upsert ») :

INSERT INTO client (nom_client,adresse) VALUES ('toto' , 'adresse1' ), ('tata','adresse2')
    ON CONFLICT (nom_client) DO UPDATE
    SET adresse = EXCLUDED.adresse
    WHERE client.nom_client = EXCLUDED.nom_client;

PostgreSQL 15 apporte même une commande MERGE. Par rapport à INSERT ON CONFLICT, MERGE permet aussi des suppressions, et possède un mécanisme différent.


Pas de DDL applicatif

Le schéma est la modélisation des données

  • Une application n’a pas à y toucher lors de son fonctionnement normal + exception : tables temporaires
  • SQL manipule les données en flux continu :
    • chaque étape d’un plan d’exécution n’attend pas la fin de la précédente
    • donc : une table temporaire est souvent une perte de temps

Il est fortement déconseillé qu’une application modifie le schéma de données pendant son fonctionnement, notamment qu’elle crée des tables ou ajoute des colonnes. Une exception fréquente concerne les tables « temporaires », qui n’existent que le temps d’une session. Elles sont inévitables dans certaines circonstances, assez courantes pendant des batchs, mais dans le flux normal de l’applicatif l’utilisation de tables temporaires ne sert généralement qu’à multiplier les étapes et à poser des sortes de points d’arrêt artificiels dans le maniement des données. C’est très net sur cette réécriture de l’exemple précédent :

CREATE TEMP TABLE temp_a_inserer  (nom_client text, adresse text);

INSERT INTO temp_a_inserer
  SELECT * FROM (VALUES ('toto' , 'adresse1' ), ('tata','adresse2')) AS tmp;

UPDATE client SET adresse=temp_a_inserer.adresse
FROM temp_a_inserer
WHERE temp_a_inserer.nom_client=client.nom_client;

INSERT INTO client (nom_client,adresse)
SELECT nom_client,adresse from temp_a_inserer
WHERE NOT EXISTS (
          SELECT 1 FROM client
          WHERE client.nom_client=temp_a_inserer.nom_client
          );
DROP TABLE temp_a_inserer;

Optimiser chaque accès

Les moteurs SQL sont très efficaces, et évoluent en permanence

  • Ils ont de nombreuses méthodes de tri, de jointure, choisies en fonction du contexte
  • En SQL :
    • optimisation selon volume & configuration
    • évolution avec le moteur
  • Dans l’application cliente : vous devrez le maintenir et l’améliorer
  • Faites le maximum côté SQL :
    • agrégats, fonctions analytiques, tris, numérotations, CASE, etc.
    • Commentez avec -- et /* */

L’avantage du code SQL est, encore une fois, qu’il est déclaratif. Il aura donc de nombreux avantages sur un code procédural côté applicatif, quel que soit le langage. L’exécution par le moteur évoluera pour prendre en compte les variations de volumétrie des différentes tables. Les optimiseurs sont la partie la plus importante d’un moteur SQL. Ils progressent en permanence. Chaque nouvelle version va donc potentiellement améliorer vos performances.

Si vous écrivez du procédural avec des appels unitaires à la base dans des boucles, le moteur ne pourra rien optimiser. Si vous faites vos tris ou regroupements côté client, vous êtes limités aux algorithmes fournis par vos langages, voire à ceux que vous aurez écrit manuellement à un moment donné. Alors qu’une base de données bascule automatiquement entre une dizaine d’algorithmes différents suivant le volume, le type de données à trier, ce pour quoi le tri est ensuite utilisé, etc., voire évite de trier en utilisant des tables de hachage ou des index disponibles. La migration à une nouvelle version du moteur peut vous apporter d’autres techniques prises alors en compte de manière transparente.


Ne faire que le nécessaire

Prendre de la distance vis-à-vis des spécifications fonctionnelles (bis) :

  • Ex : mise à jour ou insertion ?
    • tenter la mise à jour, et regarder combien d’enregistrements ont été mis à jour
    • surtout pas de COUNT(*)
    • éventuellement un test de l’existence d’un seul enregistrement
    • gérer les exceptions plutôt que de vérifier préalablement que les conditions sont remplies (si l’exception est rare)
    • et se renseigner sur la syntaxe

Toujours coder les accès aux données pour que la base fasse le maximum de traitement, mais uniquement les traitements nécessaires : l’accès aux données est coûteux, il faut l’optimiser. Et le gros des pièges peut être évité avec les quelques règles d’« hygiène » simples qui viennent d’être énoncées.


Index

  • Objets destinés à l’optimisation des accès
  • À poser par les développeurs :
CREATE INDEX ON ma_table (nom colonne) ;

Les index sont des objets uniquement destinés à accélérer les requêtes (filtrage mais aussi jointures et tris, ou respect des contraintes d’unicité). Ils ne modifient jamais le résultat d’une requête (tout au plus : ils peuvent changer l’ordre des lignes résultantes si celui-ci est indéfini.) Il est capital pour un développeur d’en maîtriser les bases, car il est celui qui sait quels sont les champs interrogés dans son application. Les index sont un sujet en soi qui sera traité par ailleurs.


Impact des transactions

  • Verrous : relâchés à la fin de la transaction
    • COMMIT
    • ROLLBACK
  • Validation des données sur le disque au COMMIT
    • écriture synchrone : coûteux
  • Contournements :
    • tables temporaires/unlogged ?
    • parfois : synchronous_commit = off (…si perte possible)
  • → Faire des transactions qui correspondent au fonctionnel
    • pas trop nombreuses
    • courtes, pas de travail inutile une fois des verrous posés

Réaliser des transactions permet de garantir l’atomicité des opérations : toutes les modifications sont validées (COMMIT), ou tout est annulé (ROLLBACK). Il n’y a pas d’état intermédiaire. Le COMMIT garantit aussi la durabilité des opérations : une fois que le COMMIT a réussi, la base de données garantit que les opérations ont bien été stockées, et ne seront pas perdues… sauf perte du matériel (disque) sur lequel ont été écrites ces opérations bien sûr.

L’opération COMMIT a bien sûr un coût : il faut garantir que les données sont bien écrites sur le disque, il faut les écrire sur le disque (évidemment), mais aussi attendre la confirmation du disque, voire de serveurs répliqués distants parfois. Même avec des disques SSD, plus performants que les disques classiques, cette opération reste coûteuse. Un disque dur classique doit attendre la rotation du disque et placer sa tête au bon endroit (dans le journal de transaction), écrire la donnée, et confirmer au système que c’est fait. Un disque SSD doit écrire réellement le bloc demandé, c’est-à-dire l’effacer (relativement lent) puis le réécrire. Dans les deux cas, il faut compter de l’ordre de la milliseconde.

Il est parfois acceptable de perdre les dernières données en cas de panne de courant (par défaut, celles committées pendant les derniers 300 ms). On peut donc réduire la fréquence de la synchronisation des journaux avec :

SET synchronous_commit TO off ;          /* pour une session */
SET LOCAL synchronous_commit TO off ;    /* pour une transaction */

La synchronisation n’aura plus lieu aussi fréquemment. L’impact peut être énorme sur les petites transactions nombreuses.

De plus, les transactions devant garantir l’atomicité des opérations, il est nécessaire qu’elles prennent des verrous : sur les enregistrements modifiés, sur les tables accédées (pour éviter les changements de structure pendant leur manipulation), sur des prédicats (dans certains cas compliqués comme le niveau d’isolation serializable)… Tout ceci a un impact :

  • par le temps d’acquisition des verrous, bien sûr ;
  • par la contention entre sessions : certaines risquent d’en bloquer d’autres.

Les verrous étant posés lors des ordres d’écriture et relâchés en fin de transaction, on fera en sorte que la transaction fasse le minimum de choses après les premières écritures.

Il est donc très difficile de déterminer la bonne durée d’une transaction. Trop courte : on génère beaucoup d’opérations synchrones. Trop longue : on risque de bloquer d’autres sessions. Le mieux (et le plus important en fait) est de coller au besoin fonctionnel.


Verrouillage et contention

  • Chaque transaction prend des verrous :
    • sur les objets (tables, index, etc.) pour empêcher au moins leur suppression ou modification de structure pendant leur travail
    • sur les enregistrements
    • libérés à la fin de la transaction : les transactions très longues peuvent donc être problématiques
  • Sous PostgreSQL, on peut quand même lire un enregistrement en cours de modification : on voit l’ancienne version (MVCC)

Afin de garantir une isolation correcte entre les différentes sessions, le SGBD a besoin de protéger certaines opérations. On ne peut par exemple pas autoriser une session à modifier le même enregistrement qu’une autre, tant qu’on ne sait pas si cette dernière a validé ou annulé sa modification. On a donc un verrouillage des enregistrements modifiés.

Certains SGBD verrouillent totalement l’enregistrement modifié. Celui-ci n’est plus accessible même en lecture tant que la modification n’a pas été validée ou annulée. Cela a l’avantage d’éviter aux sessions en attente de voir une ancienne version de l’enregistrement, mais le défaut de les bloquer, et donc de fortement dégrader les performances.

PostgreSQL, comme Oracle, utilise un modèle dit MVCC (Multi-Version Concurrency Control), qui permet à chaque enregistrement de cohabiter en plusieurs versions simultanées en base. Cela permet d’éviter que les écrivains ne bloquent les lecteurs ou les lecteurs ne bloquent les écrivains. Cela permet aussi de garantir un instantané de la base à une requête, sur toute sa durée, voire sur toute la durée de sa transaction si la session le demande (BEGIN ISOLATION LEVEL REPEATABLE READ).

Dans le cas où il est réellement nécessaire de verrouiller un enregistrement sans le mettre à jour immédiatement (pour éviter une mise à jour concurrente), il faut utiliser l’ordre SQL SELECT FOR UPDATE. Ce dernier possède une très intéressante option SKIP LOCKED pour ne pas être bloqué par une ligne déjà verrouillée.


Deadlocks

« Verrous mortels » : comment les éviter ?

  • Théorie : prendre toujours les verrous dans le même ordre
  • Pratique, ça n’est pas toujours possible ou commode
  • Conséquence : une des transactions est tuée
    • erreurs, ralentissements

Les deadlocks se produisent quand plusieurs sessions acquièrent simultanément des verrous et s’interbloquent. Par exemple :

Session 1 Session 2
BEGIN BEGIN
UPDATE demo SET a=10 WHERE a=1;
UPDATE demo SET a=11 WHERE a=2;
UPDATE demo SET a=11 WHERE a=2; Session 1 bloquée. Attend session 2.
UPDATE demo SET a=10 WHERE a=1; Session 2 bloquée. Attend session 1.

Bien sûr, la situation ne reste pas en l’état. Une session qui attend un verrou appelle au bout d’un temps court (une seconde par défaut sous PostgreSQL) le gestionnaire de deadlocks, qui finira par tuer une des deux sessions. Dans cet exemple, il sera appelé par la session 2, ce qui débloquera la situation.

Une application qui a beaucoup de deadlocks a plusieurs problèmes :

  • les transactions, mêmes celles qui réussissent, attendent beaucoup (ce qui bride l’utilisation de toutes les ressources machine) ;
  • certaines finissent annulées et doivent donc être rejouées (travail supplémentaire).

Dans notre exemple, on aurait pu éviter le problème, en définissant une règle simple : toujours verrouiller par valeurs de a croissante. Dans la pratique, sur des cas complexes, c’est bien sûr bien plus difficile à faire. Par ailleurs, un deadlock peut impliquer plus de deux transactions. Mais simplement réduire le volume de deadlocks aura toujours un impact très positif sur les performances.

On peut aussi déclencher plus rapidement le gestionnaire de deadlock. 1 seconde, c’est quelquefois une éternité dans la vie d’une application. Sous PostgreSQL, il suffit de modifier le paramètre deadlock_timeout. Plus cette variable sera basse, plus le traitement de détection de deadlock sera déclenché souvent. Et celui-ci peut être assez gourmand si de nombreux verrous sont présents, puisqu’il s’agit de détecter des cycles dans les dépendances de verrous.


Quelle durée pour les sessions & transactions ?

Divers seuils possibles, jamais globalement.

SET …_timeout TO '5s' ;
ALTER ROLEIN DATABASESET ..._timeout TO '…s'
Paramètre Cible du seuil
lock_timeout Attente de verrou
statement_timeout Ordre en cours
idle_session_timeout Session inactive
idle_in_transaction_session_timeout Transaction en cours, inactive
transaction_timeout (v17) Transaction en cours

PostgreSQL possède divers seuils pour éviter des ordres, transactions ou sessions trop longues. Le dépassement d’un seuil provoque la fin et l’annulation de l’ordre, transaction ou session. Par défaut, aucun n’est activé.

Ne définissez jamais des paramètres globalement, les ordres de maintenance ou les batchs de nuit seraient aussi concernés, tout comme les superutilisateurs. Utilisez un ordre SET dans la session, ou ALTER ROLE … IN DATABASE … SET ….

Il est important que l’application sache gérer l’arrêt de connexion ou l’annulation d’un ordre ou d’une transaction et réagir en conséquence (nouvelle tentative, abandon avec erreur…). Dans le cas contraire, l’application pourrait rester déconnectée, et suivant les cas des données pourraient être perdues.

lock_timeout permet d’interrompre une requête qui mettrait trop de temps à acquérir son verrou. Il faut l’activer par précaution avant une opération lourde (un VACUUM FULL notamment) pour éviter un « empilement des verrous ». En effet, si l’ordre ne peut pas s’exécuter immédiatement, il bloque toutes les autres requêtes sur la table.

statement_timeout est la durée maximale d’un ordre. Défini au niveau d’un utilisateur ou d’une session, cet ordre permet d’éviter que des requêtes aberrantes chargent la base pendant des heures ou des jours ; ou encore que des requêtes « s’empilent » sur une base totalement saturée ou avec trop de contention. Autre exemple : les utilisateurs de supervision et autres utilisateurs non critiques.

À noter : l’extrait suivant d’une sauvegarde par pg_dump montre que l’outil inhibe ces paramètres par précaution, afin que la restauration n’échoue pas.

-- Dumped from database version 17.2 (Debian 17.2-1.pgdg120+1)
-- Dumped by pg_dump version 17.2 (Debian 17.2-1.pgdg120+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;

L’idée peut être reprise pour des scripts batchs, par exemple.


Quelle durée pour une session ?

  • Courte
    • coût & temps des connexions
    • pooler ?
  • Longue
    • risque de saturation du nombre de connexions
    • (rare) gaspillage mémoire par les backends

Il est généralement conseillé d’utiliser des sessions assez longues, car la création d’une connexion à PostgreSQL est une opération lourde. Si l’applicatif ne cesse de se connecter/déconnecter, il faudra penser à un pooler (pgBouncer, ou côté applicatif).

Des sessions très longues et inactives ne sont en général pas un souci. Un pooler garde justement ses sessions longtemps. Mais si les sessions ne se ferment jamais, le nombre maximal de sessions (max_connections) peut être atteint, et de nouvelles connexions refusées.

De plus, pour les performances, il n’est pas très bon qu’il y ait des milliers de sessions ouvertes, malgré les progrès des versions récentes de PostgreSQL. Enfin, si la consommation mémoire du backend associé à une session est raisonnable, il arrive, exceptionnellement, qu’il se mette à occuper durablement de la mémoire (par exemple en cas d’accès à des milliers de tables et index durant son existence).

La durée maximale des sessions peut être réglée par idle_session_timeout, ou au niveau du pooler s’il y en a un.


Quelle durée pour une transaction ?

  • Courte
    • synchronisation fréquente coûteuse
  • Longue
    • verrous bloquants

Les transactions ouvertes et figées durablement sans faire de COMMIT ou ROLLBACK sont un problème sérieux, car l’autovacuum ne pourra pas nettoyer de ligne que cette session pourrait encore voir. Si l’applicatif ne peut être corrigé, ou si des utilisateurs ouvrent un outil quelconque sans le refermer, une solution est de définir idle_in_transaction_session_timeout, par exemple à une ou plusieurs heures.

Pour les performances, il faut éviter les sessions trop courtes car le coût de la synchronisation sur disque des journaux lors d’un COMMIT est coûteux (sauf table unlogged ou synchronous_commit). Typiquement, un traitement de batch ou de chargement regroupera de nombreuses opérations en une seule transaction.

D’un autre côté, chaque transaction maintient ses verrous jusqu’à sa fin, et peut donc bloquer d’autres transactions et ralentir toute l’application. En utilisation transactionnelle, il vaut donc mieux que les transactions soient courtes et n’en fassent pas plus que ce qui est dicté par le besoin fonctionnel, et si possible le plus tard possible dans la transaction.

transaction_timeout (à partir de PostgreSQL 17) peut alors servir comme sécurité en cas de problème. À noter que cette limite ne concerne pas les transactions préparées, liées au transactions en deux phases, et dont la longueur est parfois un souci.


Base distribuée

Écrire sur plusieurs nœuds ?

  • Complexité (applicatif/exploitation)
    • → risque d’erreur (programmation, fausse manipulation)
    • reprise d’incident complexe
  • Essayez avec un seul serveur plus gros
    • après avoir optimisé bien sûr
    • PostgreSQL peut vous étonner

Il y a plusieurs variantes de l’utilisation de plusieurs nœuds. PostgreSQL permet nativement la lecture sur des réplicas d’une instance (et la technologie est fiable), mais pas l’écriture. Il est cependant possible de faire du sharding en répartissant les données sur plusieurs instances indépendantes (sharding). L’extension Citus permet de faire cela de manière transparente. Mais il ne faut surtout pas négliger tous les coûts de cette solution : non seulement le coût du matériel, mais aussi les coûts humains : procédures d’exploitation, de maintenance, complexité accrue de développement, etc.

Performance et robustesse peuvent être des objectifs contradictoires.

Avant de complexifier votre système, pensez à augmenter les ressources du serveur (après avoir optimisé autant que possible et identifié quelle ressource pose problème : RAM, CPU, disque… ?)


Bibliographie

  • Quelques références :
    • The Art of SQL, Stéphane Faroult
    • Refactoring SQL Applications, Stéphane Faroult
    • SQL Performance Explained, Markus Winand
    • Introduction aux bases de données, Chris Date
    • The Art of PostgreSQL, Dimitri Fontaine
    • Vidéos de Stéphane Faroult sous Youtube

Il existe bien des livres sur le développement en SQL. Voici quelques sources intéressantes parmi bien d’autres :

Livres pratiques non propres à PostgreSQL :

  • The Art of SQL, Stéphane Faroult, 2006 (ISBN-13: 978-0596008949)
  • Refactoring SQL Applications, Stéphane Faroult, 2008 (ISBN-13: 978-0596514976)
  • SQL Performance Explained, Markus Winand, 2012 : même si ce livre ne tient pas compte des dernières nouveautés des index de PostgreSQL, il contient l’essentiel de ce qu’un développeur doit savoir sur les index B-tree sur diverses bases de données courantes

En vidéos :

Livres spécifiques à PostgreSQL :

  • The Art of PostgreSQL de Dimitri Fontaine (2020) ; l’ancienne édition de 2017 se nommait Mastering PostgreSQL in Application Development.

Sur la théorie des bases de données :

  • An Introduction to Database Systems, Chris Date (8è édition de 2003, ISBN-13 en français :‎ 978-2711748389 ; en anglais : 978-0321197849) ;
  • The World and the Machine, Michael Jackson (version en ligne .

Quiz

Techniques d’indexation