PostgreSQL La base de donnees la plus sophistiquee au monde.

La planete francophone de PostgreSQL

vendredi 19 janvier 2018

Sébastien Lardière

PostgreSQL Hebdo #2

Lu cette semaine :

par Sébastien Lardière le vendredi 19 janvier 2018 à 16h40

jeudi 11 janvier 2018

Sébastien Lardière

PostgreSQL Hebdo #1

Veille hebdomadaire autour de PostgreSQL, la plupart en anglais :

Ceci est le premier billet d'une série, reflétant mes lectures à propos de PostgreSQL. À suivre, donc…

par Sébastien Lardière le jeudi 11 janvier 2018 à 21h00

Philippe Florent

Injection SQL

Courts exemples autour d'une faille de sécurité classique

jeudi 11 janvier 2018 à 14h15

Préparer (ou non) les instructions - partie 1

PARSE ONCE, EXECUTE MANY, préparer les instructions avec PostgreSQL est-il payant au niveau des performances ? Dans cette première partie, une requête très simple entraînant toujours le même plan d'exécution est exécutée 100 000 fois dans une session via 3 programmes en php légèrement différents.

jeudi 11 janvier 2018 à 11h45

mercredi 10 janvier 2018

Loxodata

PostgreSQL et la réplication logique

La version 10 de PostgreSQL apporte de nombreuses fonctionnalités, dont l’intégration de la réplication logique.

La réplication logique, très répandue, complète les possibilités de PostgreSQL.

La réplication logique, qu’est-ce ?

Dans le domaine des bases de données, on distingue habituellement deux types de réplication : la réplication logique et la réplication physique.

Cette distinction s’applique également avec PostgreSQL.

La réplication physique consiste à répliquer les données d’une instance PostgreSQL au niveau des fichiers de données binaires. Cette technique de réplication ne s’intéresse pas à la nature des données répliquées (leur type ou valeur), mais fait simplement en sorte que l’ensemble d’une instance et des transactions exécutées sur cette instance soient répliqués.

Par opposition, la réplication logique permet de cibler une partie des données, en travaillant au même niveau que les transactions : on connaît donc la nature des données, en particulier, la table de provenance de la donnée.

On peut donc dire que la réplication logique dans PostgreSQL est de plus « haut niveau » que la réplication physique. Elle permet une plus grande finesse dans le choix des données répliquées.

La granularité de la donnée, et le fait de se situer au niveau logique, permettent de répliquer une ou plusieurs tables d’une instance à une autre, y compris dans des versions différentes de PostgreSQL.

En conséquence, il est possible de concevoir des topologies de réplication logique où une instance peut recevoir plusieurs tables distinctes, les requêter, et stocker des résultats localement, ce qui n’est pas possible avec la réplication physique.

Les possibilités avant la v10

La réplication logique est déjà possible avec PostgreSQL depuis plus d’une dizaine d’années, en utilisant des outils externes comme Slony ou Londiste.

Ces outils ajoutent des fonctionnalités dans PostgreSQL, et nécessitent des démons externes supplémentaires, ainsi que des outils d’administration spécifiques. Dans les faits, ces outils impliquent une double écriture des données, en utilisant un « trigger » pour réaliser cette double écriture, ce qui peut s’avérer pénalisant dans certains cas. En réalité, ces outils sont tout à fait stables et performants.

Historique de la réplication logique

Depuis la version 8 de PostgreSQL, il est possible de s’appuyer sur les fichiers WAL pour sauvegarder, restaurer et répliquer les données (warm standby).

La branche 9 ajoute la possibilité pour une instance répliquée de se connecter à l’instance primaire pour récupérer les transactions au fil de l’eau.

La version 9.3 permet de lancer des « Custom Background Workers », soit des démons spécifiques, attachés à l’instance PostgreSQL.

La version 9.4 ajoute la notion de décodage logique : le contenu des journaux de transactions peut être décodé pour retrouver l’information qu’il contient.

À partir de ces nouveautés, il est possible de développer un outil de réplication logique. C’est ce que propose l’extension pglogical : de la réplication dont la granularité est à la table, qui s’appuie sur les journaux de transactions, et qui est pilotée par des démons attachés à l’instance PostgreSQL.

Mais un problème de complexité reste avec cette extension. En effet, les commandes d’administration de la réplication sont des appels de fonctions, ajoutées à PostgreSQL lors de l’installation de cette extension.

La nouveauté de la v10

À partir de la version 10, l’extension pglogical est désormais complètement intégrée à PostgreSQL, et de nouvelles commandes SQL permettent de contrôler facilement ce système de réplication.

La réplication logique en résumé :

  • intégrée à PostgreSQL ;
  • s’appuie sur les journaux de transactions ;
  • permet de répliquer une ou plusieurs tables ;
  • utilise des commandes SQL.

Pour le moment, cette fonctionnalité n’est utilisable qu’avec la version 10, mais les futures versions permettront d’utiliser cette réplication entre des versions différentes de PostgreSQL.

Limites

Par rapport à l’extension pglogical dont elle provient, il manque encore à la réplication logique intégrée certaines fonctionnalités :

  • réplication des séquences ;
  • support de TRUNCATE ;
  • réplication du schéma de la table et des DDL (qu’il faut faire manuellement) ;
  • filtre des lignes et des colonnes ;
  • détection et résolution des conflits.

Ces fonctionnalités seront probablement ajoutées dans les futures versions majeures de PostgreSQL.

Utilisation

La réplication logique intégrée apporte deux nouvelles notions : la publication et la souscription.

La publication se trouve du côté d’où viennent les données, et la souscription est du côté où vont les données.

L’instance PostgreSQL qui porte la souscription se connecte à l’instance qui porte la publication.

Dans l’exemple simple utilisé dans cet article, il n’y a qu’une seule instance pour chacun des deux rôles, même s’il est possible de concevoir des topologies plus complexes.

Configuration

Pour utiliser la réplication logique dans PostgreSQL 10, il faut modifier le niveau d’information des WAL, dans le fichier postgresql.conf

wal_level = logical

D’autres paramètres sont utiles, avec des valeurs suffisantes pour démarrer, mais qui doivent être augmentées en fonction du nombre d’instances connectées :

max_wal_sender = 10
max_worker_processes = 8
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2

Ces modifications nécessitent un redémarrage de l’instance.

Le fichier pg_hba.conf doit aussi être adapté afin de permettre les connexions depuis les répliquas.

Même si ces modifications ne sont nécessaires que du côté de la publication, il est possible d’utiliser des configurations identiques du côté des souscriptions.

Côté « publication »

Dans l’instance où se trouvent les tables que l’on souhaite répliquer, il suffit de lancer la commande suivante :

CREATE PUBLICATION emp_repli
FOR TABLE employees.departments, employees.dept_emp, employees.dept_manager,
         employees.employees, employees.salaries, employees.titles ;

La publication est alors créée pour les tables indiquées.

Côté « souscription »

La réplication ne démarre réellement que lorsqu’une souscription s’abonne à une publication existante. Avant cela, il faut avoir créé les tables, avec une commande de ce type :

pg_dump -h server01 -s -C -t employees.departments -t employees.dept_emp \
     -t employees.dept_manager -t employees.employees -t employees.salaries \
     -t employees.titles -d employees | psql -d employees

La commande précédente extrait les commandes SQL de création des tables depuis la base de données où est la publication, et passe ces commandes dans la base de données de l’instance où est créée la souscription.

Il reste à lancer la souscription :

CREATE SUBSCRIPTION sub_emp_repli CONNECTION 'host=server01 dbname=employees'
   PUBLICATION emp_repli ;

Les données des tables sont alors copiées et les modifications de données sont répliquées.

Une connexion étant ouverte entre les deux instances PostgreSQL, on peut constater l’existence d’un « worker » spécifique dans chacune des instances. Côté publication, on peut voir un walsender, et côté souscription, un logical replication worker.

Supervision

Côté publication, la requête suivante permet d’identifier les tables publiées :

employees=# SELECT pubname, pr.prrelid::regclass
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid;
  pubname  |   prrelid
-----------+--------------
 emp_repli | departments
 emp_repli | dept_emp
 emp_repli | dept_manager
 emp_repli | employees
 emp_repli | salaries
 emp_repli | titles
(6 rows)

La vue pg_stat_replication permet également de suivre l’activité des souscriptions connectées.

Côté souscription, la requête suivante permet de lister les réplications en cours :

employees=# select * from pg_subscription;
-[ RECORD 1 ]---+---------------------------
subdbid         | 16384
subname         | sub_emp_repli
subowner        | 10
subenabled      | t
subconninfo     | host=server01 dbname=employees
subslotname     | sub_emp_repli
subsynccommit   | off
subpublications | {emp_repli}

C’est la vue pg_stat_subscription qui permet de suivre l’avancement de la réplication :

employees=# select * from pg_stat_subscription ;
-[ RECORD 1 ]---------+------------------------------
subid                 | 16463
subname               | sub_emp_repli
pid                   | 10114
relid                 |
received_lsn          | 4/BD38B6C8
last_msg_send_time    | 2017-12-21 18:34:15.24688+01
last_msg_receipt_time | 2017-12-21 18:34:15.246919+01
latest_end_lsn        | 4/BD38B6C8
latest_end_time       | 2017-12-21 18:34:15.24688+01

Modification des réplications

Une fois la réplication démarrée, il est possible de modifier les publications et les souscriptions.

On peut, par exemple, ajouter ou enlever une table d’une publication. On peut modifier une souscription en la suspendant, ou en forçant un rafraîchissement des données, par exemple.

Les ordres ALTER PUBLICATION et ALTER SUBSCRIPTION sont alors utilisés.

La commande suivante permet de copier à nouveau les données depuis la publication :

ALTER SUBSCRIPTION sub_emp_repli REFRESH PUBLICATION ;

Et après la v10 ?

Cette fonctionnalité simplifie grandement l’administration de vos données, en permettant la création de topologies logiques jusqu’ici dépendantes d’outils externes parfois délicats à installer et à administrer.

Cependant, certaines fonctionnalités sont absentes dans la version 10, mais déjà présentes dans l’extension pglogical.

Au-delà de cet aspect, l’extension pglogical est un choix incontournable pour les utilisateurs qui utilisent des versions plus anciennes de PostgreSQL, ou qui ont besoin des fonctionnalités citées.

On peut cependant s’attendre à voir de nouvelles fonctionnalités utiles arriver dans les futures versions majeures de PostgreSQL, comme la réplication des séquences ou des DDL.

Un outil de plus pour le DBA PostgreSQL !

par contact@loxodata.com (Loxodata) le mercredi 10 janvier 2018 à 17h26

mercredi 3 janvier 2018

Loxodata

Meilleurs vœux pour cette belle année 2018 !

Toute l’équipe de Loxodata vous présente ses meilleurs vœux pour l’année 2018. Que cette nouvelle année vous apporte la réussite et le succès pour tous vos projets.

En 2017, notre éléphant préféré a été gâté : PostgreSQL a été sacré SGBD de l’année par db-engines.com. Cette consécration est le résultat de l’adoption toujours plus grande de PostgreSQL. C’est aussi le travail de la communauté qui porte ses fruits, et la qualité de son travail qui est reconnue.

En 2018, nous serons toujours à vos côtés pour que la progression de PostgreSQL soit toujours plus importante.

Une nouvelle année, c’est aussi l’occasion de prendre de bonnes résolutions. Si vous souhaitez prendre un nouveau départ pour 2018 et intégrer une équipe où l’entraide n’est pas un vain mot, Loxodata recrute !

par contact@loxodata.com (Loxodata) le mercredi 3 janvier 2018 à 14h09

mercredi 27 décembre 2017

Daniel Verite

Large objects ou bytea: les différences de verrouillage

Dans un billet précédent, je mentionnais les différences entre objets larges et colonnes bytea pour stocker des données binaires, via un tableau d’une quinzaine de points de comparaison. Ici, détaillons un peu une de ces différences: les verrouillages induits par les opérations.

Effacement en masse

Pour le bytea, ça se passe comme avec les autres types de données de base. Les verrous au niveau ligne sont matérialisés dans les entêtes de ces lignes sur disque, et non pas dans une structure à part (c’est pourquoi ils ne sont pas visibles dans la vue pg_locks). En conséquence, il n’y pas de limite au nombre de lignes pouvant être effacées dans une transaction.

Mais du côté des objets larges, c’est différent. Une suppression d’objet via lo_unlink() est conceptuellement équivalent à un DROP, et prend un verrou en mémoire partagée, dans une zone qui est pré-allouée au démarrage du serveur. De ce fait, ces verrous sont limités en nombre, et si on dépasse la limite, on obtient une erreur de ce type:

ERROR: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

La documentation nous dit à propos de cette limite:

La table des verrous partagés trace les verrous sur max_locks_per_transaction * (max_connections + max_prepared_transactions) objets (c’est-à-dire des tables) ; de ce fait, au maximum ce nombre d’objets distincts peuvent être verrouillés simultanément.

les valeurs par défaut de ces paramètres étant (pour la version 10):

Paramètre Valeur
max_locks_per_transaction 64
max_connections 64
max_prepared_transactions 0

Ca nous donne 4096 verrous partagés par défaut. Même si on peut booster ces valeurs dans postgresql.conf, le maximum sera donc d’un ordre de grandeur peu élevé, disons quelques dizaines de milliers, ce qui peut être faible pour des opérations en masse sur ces objets. Et n’oublions pas qu’un verrou n’est libérable qu’à la fin de la transaction, et que la consommation de ces ressources affecte toutes les autres transactions de l’instance qui pourraient en avoir besoin pour autre chose.

Une parenthèse au passage, ce nombre d’objets verrouillables n’est pas un maximum strict, dans le sens où c’est une estimation basse. Dans cette question de l’an dernier à la mailing-liste: Maximum number of exclusive locks, j’avais demandé pourquoi en supprimant N objets larges, le maximum constaté pouvait être plus de deux fois plus grand que celui de la formule, avec un exemple à 37132 verrous prenables au lieu des 17920=(512*30+5) attendus. L’explication est qu’à l’intérieur de cette structure en mémoire partagée, il y a des sous-ensembles différents. Le nombre de verrous exclusifs réellement disponibles à tout moment dépend de l’attente ou non des verrous par d’autres transactions.

Pour être complet, disons aussi que le changement de propriétaire, via ALTER LARGE OBJECT nécessite aussi ce type de verrou en mémoire partagée, et qu’en revanche le GRANT … ON LARGE OBJECT … pour attribuer des permissions n’en a pas besoin.

Ecriture en simultané

Ce n’est pas ce qu’il y a de plus commun, mais on peut imaginer que deux transactions concurrentes veulent mettre à jour le même contenu binaire.

Dans le cas du bytea, la transaction arrivant en second est bloquée dans tous les cas sur le verrou au niveau ligne posé par un UPDATE qui précède. Et physiquement, l’intégralité du contenu va être remplacé, y compris si certains segments TOAST sont identiques entre l’ancien et le nouveau contenu.

Dans le cas de l’objet large, c’est l’inverse. Seul les segments concernés par le changement de valeur vont être remplacés par la fonction lowrite(). Rappelons la structure de pg_largeobject:

=# \d pg_largeobject
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 loid    | oid     |                 | not null  | 
 pageno  | integer |                 | not null  | 
 data    | bytea   |                 | not null  | 
Index :
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

Chaque entrée de cette table représente le segment numéro pageno de l’objet large loid avec son contenu data d’une longueur maximale de 2048 octets (LOBLKSIZE).

Deux écritures concurrentes dans le même objet large vont se gêner seulement si elles modifient les mêmes segments.

Conclusion

Même si le stockage TOAST des colonnes bytea et les objets larges ont des structures très similaires, en pratique leurs stratégies de verrouillage sont quasiment opposées. L’effacement d’un bytea (DELETE) est une opération à verrouillage léger alors que l’effacement d’un objet large est plutôt comparable à un DROP TABLE. En revanche, la nature segmentée de l’objet large permet des modifications plus ciblées et légères en verrouillage, alors qu’une modification de colonne bytea induit un verrouillage (et remplacement) intégral de toute la ligne qui la porte.

par Daniel Vérité le mercredi 27 décembre 2017 à 14h04

dimanche 24 décembre 2017

Guillaume Lelarge

Changements dans la 2è édition de "PostgreSQL - Architecture et notions avancées"

On m'a demandé à corps et à cri (oui, c'est un brin exagéré :) mais merci Christophe quand même :) ) de détailler un peu les changements entrepris dans la deuxième édition de mon livre, « PostgreSQL, Architecture et notions avancées ». J'ai mis un peu de temps à retrouver ça et à en faire une liste intéressante et qui a du sens. Mais voilà, c'est fait.

J'aurais tendance à dire que le plus gros changement est l'arrivée de Julien Rouhaud en tant que co-auteur. Ça aide de ne pas être seul à la rédaction. Ça permet de s'assurer qu'on n'écrit pas trop de bêtises, ça permet de discuter sur certains changements, et chacun peut apporter ses connaissances. Bref, pour moi (et certainement pour les lecteurs aussi), c'est un changement très important.

Mais au niveau contenu ? principalement, comme indiqué sur le site de l'éditeur, les changements concernent les améliorations de la v10 de PostgreSQL. Pas que ça, mais en très grande majorité. Le reste, ce sont des retours de lecteurs, qui me permettent d'améliorer le livre, ce sont aussi des découvertes, soit par la lecture d'articles, soit par des missions en clientèle, qui me forcent à creuser certains aspects ou qui me forcent à m'interroger sur certains points.

Voici une liste détaillée des changements, chapitre par chapitre. Quand cela concerne, la v10, c'est indiqué explicitement.

Changements transversaux pour la v10 (ie, ça a touché pratiquement tous les chapitres)

  • renommage des répertoires, des fonctions et des outils
  • explications sur les changements concernant pg_ctl
  • ajout des nouveaux paramètres
  • ajout des nouvelles valeurs par défaut

Chapitre fichiers

  • explications sur les nouvelles fonctions pg_ls_waldir, pg_ls_logdir et pg_current_logfile - v10
  • explications sur le nouveau fichier current_logfile - v10
  • explication sur la nouvelle ligne du fichier postmaster.pid - v10

Chapitre Contenu des fichiers

  • meilleures explications de la partie TOAST
  • explications sur la taille maximale d'une clé dans un index Btree
  • explications sur les améliorations autour des index hash - v10
  • meilleures explications sur les index BRIN
  • indication de l'existence des index bloom
  • ajout d'informations sur l'outil amcheck - v10
  • nouveaux types d'enregistrement dans les WAL - v10

Chapitre mémoire

  • meilleure description des structures de métadonnées autour du cache disque de PostgreSQL
  • meilleure description de la structure Checkpointer Data
  • meilleure description de la clé utilisée pour le sémaphore
  • plus de précisions sur les Huge Pages

Chapitre connexions

  • explication sur la nouvelle méthode d'authentification (scram) - v10

Chapitre transactions

  • explications sur la nouvelle fonction txid_status
  • explications sur les lignes affichées par un VACUUM VERBOSE

Chapitre objets

  • explications sur le nouveau système d'import d'encodages - v10
  • ajout d'une information sur l'emplacement des séquences par rapport aux tablespaces
  • explications sur le partitionnement - v10
  • explications sur la nouvelle contrainte (GENERATED AS IDENTITY) - v10
  • explications sur les améliorations autour des index hash - v10
  • explications sur les changements autour des séquences - v10
  • explications sur les tables de transition - v10

Chapitre planification

  • meilleure explication de la génération des différents plans
  • meilleures explications sur les Bitmap Scans
  • meilleures explications sur les plans parallélisés
  • explications sur les nouveautés en terme de parallélisation (Parallel Index Scan, Gather Merge) - v10
  • explications pour les nouveaux noeuds Table Function Scan et Project Set - v10
  • meilleures explications sur le fonctionnement du noeud Limit
  • explications sur la nouvelle option de la commande EXPLAIN - v10
  • explications sur les statistiques étendues - v10
  • amélioration des explications sur GeQO
  • ajout d'une section sur l'exécuteur

Chapitre sauvegarde

  • explications sur les nouvelles options des outils pg_dump/pg_dumpall/pg_restore - v10
  • explications sur la nouvelle cible de restauration (recovery_target_lsn) - v10

Chapitre réplication

  • explications sur la réplication logique - v10
  • explications sur le quorum de réplication - v10

Chapitre sécurité

  • ajout d'un message exemple sur la découverte de corruption d'un bloc
  • explications sur les nouveaux rôles créés par défaut - v10

Chapitre statistiques

  • explications des changements sur la vue pg_stat_activity, pg_stat_replication, pg_stat_statements - v10
  • explications sur le risque de réinitialisation des statistiques sur les tables (blocage de l'autovacuum)

Il manque évidemment certains chapitres (comme celui sur le protocole de communication et celui sur la maintenance). Cette absence ne signifie pas qu'il n'y a rien eu de fait sur ces chapitres, mais plutôt que, si changements il y a eu, ils ne sont pas majeurs.

Pour les lecteurs ayant acheté la première édition en version numérique sur le site des éditions D-BookeR, ils profitent automatiquement (ie, gratuitement) de la 2e édition. Il leur suffit de retélécharger le livre ! (comme pour les versions 1.1 à 1.4). Ceux qui voudraient acquérir la deuxième édition ont vraiment intérêt à passer par le site de l'éditeur pour profiter eux-aussi des prochaines mises à jour.

Parce que, oui, l'aventure ne se termine pas là. Je continue à travailler sur le livre. J'espère que Julien en fera de même. Notamment, sa connaissance du code de PostgreSQL a été très bénéfique et il est très agréable de bosser avec lui. Au niveau des prochaines améliorations, je pense que le chapitre sur les processus a été un peu oublié et devrait être revu. On avait déjà eu ce problème lors de la mise à jour du livre pour la version 9.6, on l'a de nouveau. Il va donc falloir que je recreuse cette question des processus. Et puis j'espère que sortira un jour une troisième édition. Je ferais tout pour en tout cas.

En attendant ça, bonne lecture. Et sachez que je suis toujours preneur de vos retours. Ça aide vraiment beaucoup à améliorer le livre (et donc ça vous aide :) ).

par Guillaume Lelarge le dimanche 24 décembre 2017 à 08h53

jeudi 7 décembre 2017

Damien Clochard

De grandes sociétés françaises appellent les éditeurs logiciels à supporter PostgreSQL

Le Groupe de Travail Inter-Entreprises de l’association PostgreSQLFr vient de publier une lettre ouverte destinée à tous les éditeurs de logiciels qui ne supportent pas encore PostgreSQL pour le demander d’être compatibles avec PostgreSQL.

L’ambition de ce message est de les inciter à s’adapter rapidement à la transition irrésistable qui est en cours actuemment dans le secteur public et au sein des sociétés privées.

Créé en 2016, le Groupe de Travail Inter-Entreprise PostgreSQL (PGTIE) est un espace de discussion dédié aux entreprises et aux établissement publics. Le groupe fait partie de l’association PostgreSQLFr. Au cours des derniers mois, il a grandi de manière impressionnante.

PostgreSQL Cross-Enterprise Work Group

Via une annonce de presse (lien ci-dessous) publié hier, le groupe de travail envoie une lettre ouverte aux éditeurs logiciels pour les encourager à supporter PostgreSQL

https://www.postgresql.fr/entreprises/20171206_lettre_ouverte_aux_editeurs_de_logiciels

Il s’agit d’une étape majeure pour PostgreSQL France et dans les pays francophones. Pour la première fois, plus de 20 sociétés d’envergure nationale ou internationale prend explicitement position en faveur de PostgreSQL en reconnaissant la valeur technique de ce SGBD mais aussi en soulignant les bénéfices du modèle open source lui-même.

Parmi ces sociétés et ces établissement publics, on trouve:

Air France, Carrefour , CASDEN, CNES , EDF, MSA / GIE AGORA, E.Leclerc , MAIF , Météo France , Le ministère de l’éducation nationale, SNCF, PeopleDoc,
Société Générale, et Tokheim Services Group.

Ces institutions se sont regroupées pour partager leur expérience, promouvoir PostgreSQL et contribuer à son développement. Au dela de ces 3 missions, le point remarquable est que le groupe de travail s’est structuré en adoptant les grands principes de la communauté PostgreSQL : ouverture, entraide, transparence, auto-gouvernance, etc.

Pour découvrir les activités de ce groupe, vous pouvez venir rencontrer la communauté PostgreSQL pendant le salon Paris Open Summit 2017 ou lire page de wiki du groupe de travail :

https://www.postgresql.fr/entreprises/

Le groupe de travail entreprise est l’illustration même d’un principe fondamental de l’open source : la suppression de la frontière entre producteur et consommateur. Au sein de la communauté PostgreSQL, chaque utilisateurs peut devenir un “contributeur” et jouer un role dans l’essort et la promotion du logiciel.

par Damien Clochard le jeudi 7 décembre 2017 à 17h52

mercredi 29 novembre 2017

Pierre-Emmanuel André

OpenBSD / PostgreSQL / Authentification

PostgreSQL et l’authentification BSD

Si vous êtes un utilisateur d’OpenBSD et de PostgreSQL, vous pouvez utiliser l’authentification BSD pour vous authentifier sur vos bases. Nous allons voir comment faire cela.

mercredi 29 novembre 2017 à 11h31

lundi 27 novembre 2017

Pierre-Emmanuel André

Postgresql et la réplication logique

PostgreSQL et la réplication logique

Cet article va tester la nouvelle fonctionnalité disponible depuis PostgreSQL 10.0 : la réplication logique.

Pour en savoir plus, l’excellente documentation de PostgreSQL

lundi 27 novembre 2017 à 08h32

dimanche 26 novembre 2017

Adrien Nayrat

PostgreSQL - JSONB et Statistiques

Table des matières

Rappels statistiques, cardinalité, sélectivité

Le SQL est un language dit “déclaratif”. C’est un language où l’utilisateur demande ce qu’il souhaite. Sans préciser comment l’ordinateur doit procéder pour obtenir les résultats.

C’est le SGBD qui doit trouver “comment” réaliser l’opération en s’assurant de :

  • Retourner le résultat juste
  • Idéalement, le plus vite possible

“Le plus vite possible” se traduit par :

  • Réduire au maximum les accès disques
  • Privilégier les lectures séquentielles (praticulièrement important pour les disques mécaniques)
  • Réduire le nombre d’opérations CPU
  • Réduire l’empreinte mémoire

Pour se faire, un SGBD possède ce que l’on appèle un optimiseur dont le rôle est de trouver le meilleur plan d’exécution.

PostgreSQL possède un optimiseur basé sur un mécanisme de coût. Sans rentrer dans les détails, chaque opération a un cout unitaire (lecture d’un bloc séquentiel, traitement CPU d’un enregistrement…). Le moteur calcule le coût de plusieurs plans d’exécution (si la requête est simple) et choisi le moins couteux.

Comment le moteur peut estimer le coût d’un plan? En estimant le coût de chaque noeud du plan en se basant sur des statistiques. PostgreSQL analyse les tables pour d’obtenir un échantillon statistique (opération normalement réalisée par l’autovacuum).

Quelques mots de vocabulaire :

Cardinalité : Dans la théorie des ensemble, c’est le nombre d’éléments dans un ensemble. Dans les bases de données, ça sera le nombre de lignes d’une table ou après application d’un prédicat.

Sélectivité : Fraction d’enregistrements retournés après application d’un prédicat. Par exemple, une table contenant des personnes et dont environ un tier correspond à des enfants. La sélectivité du prédicat personne = 'enfant' sera de 0.33.

Si cette table contient 300 personnes (c’est la cardinalité de l’ensemble “personnes”), on peut estimer le nombre d’enfants car on sait que le prédicat personne = 'enfant' est de 0.33 :

300 * 0.33 = 99

On peut obtenir ces estimations avec EXPLAIN qui affiche le plan d’exécution.

Exemple (simplifié) :

explain (analyze, timing off) select * from t1 WHERE c1=1;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 ...) (actual rows=100 ...)
   Filter: (c1 = 1)
   Rows Removed by Filter: 200

(cost=0.00..5.75 rows=100 …) : Indique le coût estimé et le nombre d’enregistrements estimé (rows).

(actual rows=100 …) : Indique le nombre d’enregistrement réellement obtenu.

La documentation de PostgreSQL fourni des exemples de calculs d’estimation : Row Estimation Examples

Il est assez facile de comprendre comment obtenir des estimations à partir de types de données scalaires.

Comment ça se passe pour des types particuliers? Par exemple le JSON?

Recherche sur du JSONB

Jeu de données

Comme dans les précédents articles, j’ai utilisé le jeu de données de stackoverflow. J’ai créé une nouvelle table en aggrégeant les données de plusieurs tables dans un objet JSON :

CREATE TABLE json_stack AS
SELECT t.post_id,
       row_to_json(t,
                   TRUE)::jsonb json
FROM
  (SELECT posts.id post_id,
          posts.owneruserid,
          users.id,
          title,
          tags,
          BODY,
          displayname,
          websiteurl,
          LOCATION,
          aboutme,
          age
   FROM posts
   JOIN users ON posts.owneruserid = users.id) t;

Le traitement est assez long car les deux tables concernées totalisent près de 40Go.

J’obtiens donc une table de 40Go qui ressemble à ça :

 \dt+ json_stack
                      List of relations
 Schema |    Name    | Type  |  Owner   | Size  | Description
--------+------------+-------+----------+-------+-------------
 public | json_stack | table | postgres | 40 GB |
(1 row)


\d json_stack
             Table "public.json_stack"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 post_id | integer |           |          |
 json    | jsonb   |           |          |


select post_id,jsonb_pretty(json) from json_stack
    where json_displayname(json) = 'anayrat' limit 1;
 post_id  |
----------+-----------------------------------------------------------------------------------------
 26653490 | {
          |     "id": 4197886,
          |     "age": null,
          |     "body": "<p>I have an issue with date filter. I follow [...]
          |     "tags": "<java><logstash>",
          |     "title": "Logstash date filter failed parsing",
          |     "aboutme": "<p>Sysadmin, Postgres DBA</p>\n",
          |     "post_id": 26653490,
          |     "location": "Valence",
          |     "websiteurl": "https://blog.anayrat.info",
          |     "displayname": "anayrat",
          |     "owneruserid": 4197886
          | }

Opérateurs et indexation sur du JSONB

PostgreSQL propose plusieurs opérateurs pour interroger du JSONB 1. Nous allons utiliser l’opérateur @>.

Il est également possible d’indexer du JSONB grâce aux index GIN :

create index ON json_stack using gin (json );

Enfin, voici un exemple de requête :

explain (analyze,buffers)  select * from json_stack
    where json @>  '{"displayname":"anayrat"}'::jsonb;
                                  QUERY PLAN
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on json_stack
                (cost=286.95..33866.98 rows=33283 width=1011)
                (actual time=0.099..0.102 rows=2 loops=1)
   Recheck Cond: (json @> '{"displayname": "anayrat"}'::jsonb)
   Heap Blocks: exact=2
   Buffers: shared hit=17
   ->  Bitmap Index Scan on json_stack_json_idx
                          (cost=0.00..278.62 rows=33283 width=0)
                          (actual time=0.092..0.092 rows=2 loops=1)
         Index Cond: (json @> '{"displayname": "anayrat"}'::jsonb)
         Buffers: shared hit=15
 Planning time: 0.088 ms
 Execution time: 0.121 ms
(9 rows)

En lisant ce plan on constate que le moteur se trompe complètement. Il estime obtenir 33 283 lignes, or la requête retourne seulement deux enregistrements. Le facteur d’erreur est d’environ 15 000!

Sélectivité sur du JSONB

Quelle est la cardinalité de la table? L’information est contenue dans le catalogue système :

select reltuples from pg_class where relname = 'json_stack';
  reltuples
-------------
 3.32833e+07

Quelle est la sélectivité estimée?

select 33283 / 3.32833e+07;
        ?column?
------------------------
 0.00099999098647069251

En gros 0.001.

Plongée dans le code

Je me suis amusé à sortir le débugueur GDB pour trouver d’où pouvait venir ce chiffre. J’ai fini par arriver dans cette fonction :

[...]
79 /*
80  *  contsel -- How likely is a box to contain (be contained by) a given box?
81  *
82  * This is a tighter constraint than "overlap", so produce a smaller
83  * estimate than areasel does.
84  */
85
86 Datum
87 contsel(PG_FUNCTION_ARGS)
88 {
89     PG_RETURN_FLOAT8(0.001);
90 }
[...]

Le calcul de la sélectivité dépend du type de l’opérateur. Regardons dans le catalogue système :

select oprname,typname,oprrest from pg_operator op
    join pg_type typ ON op.oprleft= typ.oid where oprname = '@>';
 oprname | typname  |   oprrest
---------+----------+--------------
 @>      | polygon  | contsel
 @>      | box      | contsel
 @>      | box      | contsel
 @>      | path     | -
 @>      | polygon  | contsel
 @>      | circle   | contsel
 @>      | _aclitem | -
 @>      | circle   | contsel
 @>      | anyarray | arraycontsel
 @>      | tsquery  | contsel
 @>      | anyrange | rangesel
 @>      | anyrange | rangesel
 @>      | jsonb    | contsel

On retrouve plusieurs types, en effet l’opérateur @>signifie (en gros) : “Est-ce que l’objet de gauche contient l’élément de droite?”. Il est utilisé pour différents types : géométrie, tableaux…

Dans notre cas, est-ce que l’objet JSONB de gauche contient l’élément '{"displayname":"anayrat"}' ?

Un objet JSON est un type particulier. Déterminer la sélectivité d’un élément serait assez complexe. Le commentaire est assez explicite :

 25 /*
 26  *  Selectivity functions for geometric operators.  These are bogus -- unless
 27  *  we know the actual key distribution in the index, we can't make a good
 28  *  prediction of the selectivity of these operators.
 29  *
 30  *  Note: the values used here may look unreasonably small.  Perhaps they
 31  *  are.  For now, we want to make sure that the optimizer will make use
 32  *  of a geometric index if one is available, so the selectivity had better
 33  *  be fairly small.
[...]

Il n’est donc pas possible (actuellement) de déterminer la sélectivité sur des objets JSONB.

Mais tout n’est pas perdu 😉

Index fonctionnels

PostgreSQL permet de créer des index dits fonctionnels. On crée un index à partir d’une fonction.

Vous allez me dire : “Oui mais on n’en a pas besoin. Dans ton exemple, postgres utilise déjà un index”.

C’est vrai, la différence, c’est que le moteur collecte des statistiques à propos de cet index. Comme si le résultat de la fonction était une nouvelle colonne.

Création de la fonction et de l’index

C’est très simple :

CREATE or replace FUNCTION json_displayname (jsonb )
RETURNS text
AS $$
select $1->>'displayname'
$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
;

create index ON json_stack (json_displayname(json));

Recherche en utilisant une fonction

Pour utiliser l’index que nous venons de créer, il faut l’utiliser dans la requête :

explain (analyze,verbose,buffers) select * from json_stack
        where json_displayname(json) = 'anayrat';
                        QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using json_stack_json_displayname_idx on public.json_stack
            (cost=0.56..371.70 rows=363 width=1011)
            (actual time=0.021..0.023 rows=2 loops=1)
   Output: post_id, json
   Index Cond: ((json_stack.json ->> 'displayname'::text) = 'anayrat'::text)
   Buffers: shared hit=7
 Planning time: 0.107 ms
 Execution time: 0.037 ms
(6 rows)

Cette fois le moteur estime obtenir 363 lignes, ce qui est bien plus proche du résultat final (2).

Autre exemple et calcul de sélectivité

Cette fois nous allons effectuer une recherche sur le champ “age” de l’objet JSON :

explain (analyze,buffers)  select * from json_stack
      where json @>  '{"age":27}'::jsonb;
                      QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on json_stack
        (cost=286.95..33866.98 rows=33283 width=1011)
        (actual time=667.411..12723.906 rows=804630 loops=1)
   Recheck Cond: (json @> '{"age": 27}'::jsonb)
   Rows Removed by Index Recheck: 2211190
   Heap Blocks: exact=391448 lossy=344083
   Buffers: shared hit=576350 read=881510
   I/O Timings: read=2947.458
   ->  Bitmap Index Scan on json_stack_json_idx
        (cost=0.00..278.62 rows=33283 width=0)
        (actual time=562.648..562.648 rows=804644 loops=1)
         Index Cond: (json @> '{"age": 27}'::jsonb)
         Buffers: shared hit=9612 read=5140
         I/O Timings: read=11.195
 Planning time: 0.073 ms
 Execution time: 12809.392 ms
(12 lignes)

set work_mem = '100MB';

explain (analyze,buffers)  select * from json_stack
      where json @>  '{"age":27}'::jsonb;
                      QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on json_stack
        (cost=286.95..33866.98 rows=33283 width=1011)
        (actual time=748.968..5720.628 rows=804630 loops=1)
   Recheck Cond: (json @> '{"age": 27}'::jsonb)
   Rows Removed by Index Recheck: 14
   Heap Blocks: exact=735531
   Buffers: shared hit=123417 read=780542
   I/O Timings: read=1550.124
   ->  Bitmap Index Scan on json_stack_json_idx
        (cost=0.00..278.62 rows=33283 width=0)
        (actual time=545.553..545.553 rows=804644 loops=1)
         Index Cond: (json @> '{"age": 27}'::jsonb)
         Buffers: shared hit=9612 read=5140
         I/O Timings: read=11.265
 Planning time: 0.079 ms
 Execution time: 5796.219 ms
(12 lignes)

Dans cet exemple on constate que le moteur estime toujours obtenir 33 283 enregistrements. Or il en obtient 804 644. Cette fois il est beaucoup trop optimiste.

PS : Dans mon exemple vous verrez que j’ai joué la même requête en modifiant la work_mem. C’est pour éviter que le bitmap ne soit lossy 2

Comme vu ci-dessus nous pouvons créer une fonction :

CREATE or replace FUNCTION json_age (jsonb )
RETURNS text
AS $$
select $1->>'age'
$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
;

create index ON json_stack (json_age(json));

A nouveau l’estimation est bien meilleure:

explain (analyze,buffers)   select * from json_stack
      where json_age(json) = '27';
                         QUERY PLAN
------------------------------------------------------------------------
 Index Scan using json_stack_json_age_idx on json_stack
  (cost=0.56..733177.05 rows=799908 width=1011)
  (actual time=0.042..2355.179 rows=804630 loops=1)
   Index Cond: ((json ->> 'age'::text) = '27'::text)
   Buffers: shared read=737720
   I/O Timings: read=1431.275
 Planning time: 0.087 ms
 Execution time: 2410.269 ms

Le moteur estime obtenir 799 908 enregistrements. nous allons le vérifier.

Comme je l’ai précisé, le moteur possède des informations de statistiques basées sur un échantillon de données. Ces informations sont stockées dans un catalogue système lisible grâce à la vue pg_stats. Avec un index fonctionnel, le moteur le voit comme une nouvelle colonne.

schemaname             | public
tablename              | json_stack_json_age_idx
attname                | json_age
[...]
most_common_vals       | {28,27,29,31,26,30,32,25,33,34,36,24,[...]}
most_common_freqs      | {0.0248,0.0240333,0.0237333,0.0236333,0.0234,0.0229333,[...]}
[...]

La colonne most_common_vals contient les valeurs les plus fréquentes et la colonne most_common_freqs la sélectivité correspondante.

Donc pour age=27 on a une sélectivité de 0.0240333.

Ensuite il suffit de multiplier la sélectivité par la cardinalité de la table :

select n_live_tup from pg_stat_all_tables where relname ='json_stack';
 n_live_tup
------------
   33283258


select 0.0240333 * 33283258;
    ?column?
----------------
 799906.5244914

D’accord, l’estimation est bien meilleure. Mais est-ce grave si le moteur se trompe? Dans les deux requêtes ci-dessus on constate que le moteur exploite bien un index et que le résultat est obtenu rapidement.

Conséquences d’une mauvaise estimation

En quoi une mauvaise estimation peut poser problème?

Quand ça entraine le choix d’un mauvais plan.

Par exemple cette requête d’aggregation qui compte le nombre de posts par age:

explain (analyze,buffers)  select json->'age',count(json->'age')
                          from json_stack group by json->'age' ;
                             QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize GroupAggregate
  (cost=10067631.49..14135810.84 rows=33283256 width=40)
  (actual time=364151.518..411524.862 rows=86 loops=1)
   Group Key: ((json -> 'age'::text))
   Buffers: shared hit=1949354 read=1723941, temp read=1403174 written=1403189
   I/O Timings: read=155401.828
   ->  Gather Merge
        (cost=10067631.49..13581089.91 rows=27736046 width=40)
        (actual time=364151.056..411524.589 rows=256 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1949354 read=1723941, temp read=1403174 written=1403189
         I/O Timings: read=155401.828
         ->  Partial GroupAggregate
            (cost=10066631.46..10378661.98 rows=13868023 width=40)
            (actual time=363797.836..409187.566 rows=85 loops=3)
               Group Key: ((json -> 'age'::text))
               Buffers: shared hit=5843962 read=5177836,
                        temp read=4212551 written=4212596
               I/O Timings: read=478460.123
               ->  Sort
                  (cost=10066631.46..10101301.52 rows=13868023 width=1042)
                  (actual time=299775.029..404358.743 rows=11094533 loops=3)
                     Sort Key: ((json -> 'age'::text))
                     Sort Method: external merge  Disk: 11225392kB
                     Buffers: shared hit=5843962 read=5177836,
                              temp read=4212551 written=4212596
                     I/O Timings: read=478460.123
                     ->  Parallel Seq Scan on json_stack
                        (cost=0.00..4791997.29 rows=13868023 width=1042)
                        (actual time=0.684..202361.133 rows=11094533 loops=3)
                           Buffers: shared hit=5843864 read=5177836
                           I/O Timings: read=478460.123
 Planning time: 0.080 ms
 Execution time: 411688.165 ms

Le moteur s’attend à obtenir 33 283 256 d’enregistrement au lieu de 86. Il a également effectué un tri très couteux puisqu’il a généré plus de 33Go (11GO * 3 loops) de fichiers temporaires.

La même requête en utilisant la fonction json_age :

explain (analyze,buffers)   select json_age(json),count(json_age(json))
                              from json_stack group by json_age(json);
                                             QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize GroupAggregate
  (cost=4897031.22..4897033.50 rows=83 width=40)
  (actual time=153985.585..153985.667 rows=86 loops=1)
   Group Key: ((json ->> 'age'::text))
   Buffers: shared hit=1938334 read=1736761
   I/O Timings: read=106883.908
   ->  Sort
      (cost=4897031.22..4897031.64 rows=166 width=40)
      (actual time=153985.581..153985.598 rows=256 loops=1)
         Sort Key: ((json ->> 'age'::text))
         Sort Method: quicksort  Memory: 37kB
         Buffers: shared hit=1938334 read=1736761
         I/O Timings: read=106883.908
         ->  Gather
            (cost=4897007.46..4897025.10 rows=166 width=40)
            (actual time=153985.264..153985.360 rows=256 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=1938334 read=1736761
               I/O Timings: read=106883.908
               ->  Partial HashAggregate
                  (cost=4896007.46..4896008.50 rows=83 width=40)
                  (actual time=153976.620..153976.635 rows=85 loops=3)
                     Group Key: (json ->> 'age'::text)
                     Buffers: shared hit=5811206 read=5210494
                     I/O Timings: read=320684.515
                     ->  Parallel Seq Scan on json_stack
                     (cost=0.00..4791997.29 rows=13868023 width=1042)
                     (actual time=0.090..148691.566 rows=11094533 loops=3)
                           Buffers: shared hit=5811206 read=5210494
                           I/O Timings: read=320684.515
 Planning time: 0.118 ms
 Execution time: 154086.685 ms

Ici le moteur effectue le tri plus tard sur beaucoup moins de lignes. Le temps d’exécution est nettement réduit et on s’épargne surtout 33Go de fichiers temporaires.

Mot de la fin

Les statistiques sont indispensables pour choisir les meilleurs plan d’exécution. Actuellement Postgres dispose de fonctionnalités avancées pour le JSON 3 Malheureusement il n’y a pas encore de possibilité d’ajouter de statistiques sur le type JSONB. A noter que PostgreSQL 10 apporte l’infrastructure pour étendre les statistiques. Espérons que dans le futur il sera possible de les étendre pour des types spéciaux.

En attendant il est possible de contourner cette limitation en utilisant les index fonctionnels.


  1. https://www.postgresql.org/docs/current/static/functions-json.html ^
  2. Un noeud bitmap devient lossy lorsque le moteur ne peut pas faire un bitmap de tous les enregistrements. Il passe donc en mode dit “lossy” où le bitmap ne se fait plus sur l’enregistrement mais pour le bloc entier. Ce qui nécessite de lire plus de blocs et de faire un “recheck” qui consiste à filter les enregistrements obtenus. ^
  3. La documentation est très complète et fournie de nombreux exemples : usage des opérateurs, indexation. ^

dimanche 26 novembre 2017 à 20h11

mardi 21 novembre 2017

Daniel Verite

pspg, un pager dédié à psql

pspg est un outil annoncé récemment par Pavel Stehule, contributeur régulier bien connu de la communauté PostgreSQL. Il est disponible sur github et installable par le classique ./configure; make ; sudo make install sur les systèmes pourvus d’un compilateur C et de la bibliothèque ncurses.

Le “pager” ou paginateur est le programme externe appelé par psql lorsqu’un résultat à afficher ne tient pas à l’écran, en largeur ou en longueur. Par défaut c’est celui configuré pour le système, more et less étant les plus connus, sachant qu’il est remplaçable via la variable d’environnement PAGER.

pspg est spécialement adapté aux résultats produits par psql, parce qu’il intègre la notion que le contenu est fait de champs organisés en colonnes et en lignes séparées par des bordures. Sont gérées les bordures ASCII par défaut, et Unicode pour des lignes mieux dessinées.

Dès la première utilisation, ce qui frappe est la colorisation marquée à la “Midnight Commander”, et les particularités du défilement horizontal et vertical commandés par les touches du curseur: la colonne la plus à gauche reste en place au lieu de disparaître vers la gauche de l’écran, et la ligne du haut avec les noms de colonnes est également fixe par rapport au défilement vertical.

L’effet est qu’on se déplace dans le jeu de résultats avec les flèches du curseur sans perdre des yeux les données les plus importantes pour se repérer. En effet, la première colonne affichée correspond souvent à une clef primaire qui détermine les autres colonnes. Mais si on souhaite qu’elle défile comme les autres, il suffira d’appuyer sur la touche “0”, et pour y revenir la touche “1”, ou bien entre “1” et “4” pour figer entre 1 et 4 colonnes. C’est très simple et pratique.

Et voici l’inévitable copie d’écran (un SELECT * from pg_type avec les bordures par défaut):

Copie d'écran pspg

Le mode étendu \x de psql, où chaque colonne est affiché sur une nouvelle ligne, est aussi pris en compte, le défilement est normal et le séparateur de lignes [ RECORD #N ] est colorisé pour être encore plus visible.

On peut également faire de la recherche de texte en avant et arrière avec les touches habituelles / et ?.

Outre les couleurs et ce mode de défilement vraiment spécifique, il y a quelques autres fonctionnalités qui diffèrent sensiblement de less:

  • une ligne horizontale surlignée toujours présente qui accompagne les déplacements au curseur
  • le support optionnel de la souris sur X-Window pour déplacer cette ligne au clic.
  • une ligne d’état en haut de l’écran avec diverses infos de positionnement.

Cette addition à psql a été accueillie avec enthousiasme sur la liste pgsql-hackers, n’hésitez pas à l’installer si vous passez un tant soit peu de temps avec psql pour visualiser des données.

par Daniel Vérité le mardi 21 novembre 2017 à 13h15

dimanche 19 novembre 2017

Adrien Nayrat

PostgreSQL 10 : ICU & Abbreviated Keys

A peu près tout le monde a entendu parler du partitionnement et de la réplication logique dans PostgreSQL 10. Avez-vous entendu parler du support des règles de collation ICU (International Components for Unicode)?

Cet article va présenter en quoi consiste cette nouvelle fonctionnalité mais également les gains possibles en exploitant les abbreviated keys.

Table des matières

Court rappel sur le collationnement

En base de données, le collationnement correspond à des règles de classement de caractères.

Voici quelques exemples :

create table t1 (c1 text);
insert into t1 values ('cote'),('coté'),('côte'),('côté');

Deux exemples de tris en fonctions de deux locales : française et allemande

select * from t1 order by c1 collate "de_DE";
  c1
------
 cote
 coté
 côte
 côté

select * from t1 order by c1 collate "fr_FR";
  c1
------
 cote
 côte
 coté
 côté

Observez bien l’ordre de tri, en langue française le tri se fait sur le dernier accent ce qui n’est pas le cas de l’allemand.

Par défaut, Postgres utilise le collationnement de l’environnement. Il est possible de spécifier le collationnement à la création de l’instance, d’une base, d’une table… Même au niveau d’une colonne.

Il existe aussi un collationnement particulier “C” où postgres effectue le tri selon l’ordre de codage des caractères.

La table pg_collation du catalogue système énumère les différentes collations. La colonne collprovider indique la source :

  • c: libc
  • i: ICU

Par exemple :

-[ RECORD 1 ]-+-----------------------
collname      | en-US-x-icu
collnamespace | 11
collowner     | 10
collprovider  | i
collencoding  | -1
collcollate   | en-US
collctype     | en-US
collversion   | 153.64
-[ RECORD 4 ]-+-----------------------
collname      | en_US
collnamespace | 11
collowner     | 10
collprovider  | c
collencoding  | 6
collcollate   | en_US.utf8
collctype     | en_US.utf8
collversion   |

Collations ICU

Postgres s’appuie sur les librairies du système d’exploitation pour effectuer les opérations de tri. Sous linux, il s’appuie sur la très connue glibc.

L’intérêt majeur de s’appuyer sur cette librairie est de ne pas avoir à réécrire et maintenir tout un ensemble de règles de tri.

Cependant, cette approche peut présenter un inconvénient:

On doit faire “confiance” à cette librairie. Au sein d’une même distribution (et même release), la libc retournera toujours les mêmes résultats. Les choses peuvent se corser si on doit comparer les résultats obtenus depuis des libcs différentes. Par exemple, en utilisant une distribution différente ou une release différente. C’est pourquoi il est déconseillé de mettre des serveurs en streaming replication avec des distributions différentes : les index pourraient ne pas être cohérents. Cf : The dangers of streaming across versions of glibc: A cautionary tale

Les collations ICU sont plus riches. Il est possible de changer l’ordre des tris. Par exemple les mascules avant les minuscules: Setting Options. Peter Geoghegan a donné quelques exemples sur la mailing list hackers : What users can do with custom ICU collations in Postgres 10

Histoire des abbreviated keys dans PostgreSQL

Petit tour dans le passé :) La version 9.5 améliorait l’algorithme de tri grâce aux abbreviated keys. Les gains annoncés étaient vraiment important, ici entre 40 et 70% : Use abbreviated keys for faster sorting of text datums

En gros, les abbreviated keys permettent de mieux exploiter le cache des processeurs. Peter Geoghegan, qui est l’auteur du patch, apporte une explication sur son blog : Abbreviated keys: exploiting locality to improve PostgreSQL’s text sort performance

Malheureusement cette fonctionnalité a dû être désactivée dans la version 9.5.2: Disable abbreviated keys for string-sorting in non-C locales

La raison? Un bug dans certaines version de la libc! Ce bug entrainait une corruption de l’index: Abbreviated keys glibc issue

Quel est le lien avec les collations ICU? C’est tout simple, avec une collation ICU le moteur ne s’appuie plus sur la libc. Il est donc possible d’utiliser les Abbreviated keys.

On peut aller le vérifier dans le code :

src/backend/utils/adt/varlena.c
1876     /*
1877      * Unfortunately, it seems that abbreviation for non-C collations is
1878      * broken on many common platforms; testing of multiple versions of glibc
1879      * reveals that, for many locales, strcoll() and strxfrm() do not return
1880      * consistent results, which is fatal to this optimization.  While no
1881      * other libc other than Cygwin has so far been shown to have a problem,
1882      * we take the conservative course of action for right now and disable
1883      * this categorically.  (Users who are certain this isn't a problem on
1884      * their system can define TRUST_STRXFRM.)
1885      *
1886      * Even apart from the risk of broken locales, it's possible that there
1887      * are platforms where the use of abbreviated keys should be disabled at
1888      * compile time.  Having only 4 byte datums could make worst-case
1889      * performance drastically more likely, for example.  Moreover, macOS's
1890      * strxfrm() implementation is known to not effectively concentrate a
1891      * significant amount of entropy from the original string in earlier
1892      * transformed blobs.  It's possible that other supported platforms are
1893      * similarly encumbered.  So, if we ever get past disabling this
1894      * categorically, we may still want or need to disable it for particular
1895      * platforms.
1896      */
1897 #ifndef TRUST_STRXFRM
1898     if (!collate_c && !(locale && locale->provider == COLLPROVIDER_ICU))
1899         abbreviate = false;
1900 #endif

Quelques tests

Quand j’ai préparé ma présentation sur le fonctionnement du Full Text Search dans Postgres, j’ai souhaité travailler sur un jeu de donnée “réel”: la base de donnée de stackoverflow.

Voici les résultats de la création de 3 index sur la colonne “title” de la table posts (38Go):

  • Un ignorant la collation: collate "C"
  • Un utilisant la collation en_US de la libc: collate "en_US"
  • Un utilisant la collation en_US de la librairie ICU collate "en-US-x-icu"

J’active quelques options pour avoir la durée d’exécution de la requête et des informations sur le tri:

\timing
set client_min_messages TO log;
set trace_sort to on;
create index idx1 on posts (title collate  "C");
LOG:  begin index sort: unique = f, workMem = 6291456, randomAccess = f
LOG:  varstr_abbrev: abbrev_distinct after 160: 56.532166 (key_distinct: 59.707363, norm_abbrev_card: 0.353326, prop_card: 0.200000)
LOG:  varstr_abbrev: abbrev_distinct after 321: 110.782140 (key_distinct: 121.985752, norm_abbrev_card: 0.345116, prop_card: 0.200000)
[...]
LOG:  varstr_abbrev: abbrev_distinct after 10485760: 523091.461475 (key_distinct: 4215367.096361, norm_abbrev_card: 0.049886, prop_card: 0.002693)
LOG:  varstr_abbrev: abbrev_distinct after 20971522: 852125.989455 (key_distinct: 8800364.815018, norm_abbrev_card: 0.040633, prop_card: 0.001750)
LOG:  performsort starting: CPU: user: 21.88 s, system: 17.27 s, elapsed: 104.98 s
LOG:  performsort done: CPU: user: 43.55 s, system: 17.27 s, elapsed: 126.65 s
LOG:  internal sort ended, 3519559 KB used: CPU: user: 48.14 s, system: 18.40 s, elapsed: 142.19 s
CREATE INDEX
Time: 142380.670 ms (02:22.381)

Ici, postgres exploite les abbreviated keys car il n’y a pas de règles de collation. Il a juste à trier selon le codage des caractères. Le tri ne tient donc pas compte des règles de collationnement.

create index idx2 on posts (title collate  "en_US");
LOG:  begin index sort: unique = f, workMem = 6291456, randomAccess = f
LOG:  performsort starting: CPU: user: 20.10 s, system: 17.32 s, elapsed: 104.80 s
LOG:  performsort done: CPU: user: 137.52 s, system: 17.32 s, elapsed: 222.25 s
LOG:  internal sort ended, 3519559 KB used: CPU: user: 142.41 s, system: 18.10 s, elapsed: 237.97 s
CREATE INDEX
Time: 238159.675 ms (03:58.160)

Postgres utilise la libc, il ne peut donc pas exploiter les abbreviated keys.

create index idx3 on posts (title collate  "en-US-x-icu");
LOG:  begin index sort: unique = f, workMem = 6291456, randomAccess = f
LOG:  varstr_abbrev: abbrev_distinct after 160: 55.475952 (key_distinct: 59.707363, norm_abbrev_card: 0.346725, prop_card: 0.200000)
LOG:  varstr_abbrev: abbrev_distinct after 321: 110.782140 (key_distinct: 121.985752, norm_abbrev_card: 0.345116, prop_card: 0.200000)
[...]
LOG:  varstr_abbrev: abbrev_distinct after 10485760: 337228.120654 (key_distinct: 4215367.096361, norm_abbrev_card: 0.032161, prop_card: 0.002693)
LOG:  varstr_abbrev: abbrev_distinct after 20971522: 521498.943210 (key_distinct: 8800364.815018, norm_abbrev_card: 0.024867, prop_card: 0.001750)
LOG:  performsort starting: CPU: user: 30.22 s, system: 16.78 s, elapsed: 105.65 s
LOG:  performsort done: CPU: user: 66.86 s, system: 16.78 s, elapsed: 142.31 s
LOG:  internal sort ended, 3519559 KB used: CPU: user: 71.23 s, system: 18.04 s, elapsed: 157.79 s
CREATE INDEX
Time: 157979.957 ms (02:37.980)

Postgres utilise la librairie ICU, il peut exploiter les abbreviated keys. Le gain est de l’ordre de 34%. Contrairement au premier exemple, le tri tient compte des règles de collationnement en_US.

Note : Si comme moi il vous manquait des collations système (lors de l’initdb), il est possible d’importer les nouvelles collations grâce à la fonction pg_import_system_collations. Par exemple :

select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-----------------------------
                           6

dimanche 19 novembre 2017 à 14h30

mercredi 15 novembre 2017

Daniel Verite

Large objects ou bytea?

Les contenus binaires peuvent être stockés avec PostgreSQL soit dans des tables utilisateurs avec des colonnes de type bytea, soit instanciés en tant qu’objets larges et gérés dans des tables systèmes et par des fonctions spécifiques, côté client comme côté serveur.

Quelles sont les raisons de choisir l’un plutôt que l’autre?

Très schématiquement, on pourrait les résumer dans ce tableau comparant les deux approches:

Caractéristique Objet Large Colonne Bytea
Taille max par donnée 4 To 1 Go
Segmentation intra-donnée Oui Non
Stockage segmenté TOAST Non Oui
Compression LZ par segment sur totalité
Une seule table par base Oui Non
Référence indirecte (OID) Oui Non
Accès extra-requête Oui Non
Réplication logique Non Oui
Lignes par donnée Taille / 2048 1 (+ Toast)
Partitionnement Impossible Possible
Transferts en binaire Toujours Possible mais rare
Verrous en mémoire partagée Oui Non
Choix du tablespace Non Oui
Triggers possibles Non Oui
Droits d’accès par donnée Oui Non (hors RLS*)
Chargement par COPY Non Oui
Disponibilité dans langages Variable Toujours

* RLS = Row Level Security

Voyons plus en détail certaines de ces différences et leurs implications.

Usage

Les colonnes en bytea sont plus simples à utiliser, dans le sens où elles s’intégrent de manière plus standard au SQL, et qu’elles sont accessibles via toutes les interfaces. Pour insérer une donnée binaire littérale dans une requête, il faut l’exprimer dans un format textuel, par exemple: '\x41420001'::bytea pour le format hex, ou encore 'AB\000\001'::bytea pour le format escape. Dans le sens inverse, pour un résultat retourné du serveur vers le client, les colonnes bytea sont encodées dans un de ces deux formats selon le paramètre bytea_output, sauf si l’appelant a appliqué une fonction explicite d’encodage telle que base64, ou encore demandé du binaire. Ce dernier cas est plutôt rare, car beaucoup de programmes et d’interfaces avec les langages ne gèrent pas les résultats de requête au format binaire, même si le protocole et la bibliothèque libpq en C le permettent.

Ce passage par un format texte présente un inconvénient: les conversions en texte gonflent la taille des données en mémoire et sur le réseau, d’un facteur 2 pour hex, variable (entre 1 et 4) pour escape, et 4/3 pour base64, et consomment du temps CPU pour coder et décoder.

Les objets larges, de leur côté, s’appuient sur une API particulière, où chaque contenu binaire se présente un peu comme un fichier, identifié par un numéro unique (OID), avec des permissions individuelles par objet, et accessible via des opérations sur le modèle de celles des fichiers:

Fonction SQL Fonction libpq Fichier (libc)
lo_create lo_create creat
lo_open lo_open open
loread lo_read read
lowrite lo_write write
lo_lseek[64] lo_lseek[64] lseek[64]
lo_tell[64] lo_tell[64] lseek/ftell
lo_truncate[64] lo_truncate[64] truncate
lo_close lo_close close
lo_unlink lo_unlink unlink
lo_import lo_import N/A
lo_export lo_export N/A
lo_put N/A N/A
lo_get N/A N/A
lo_from_bytea N/A N/A

La plupart de ces opérations sont appelables de deux manières différentes: d’une part en tant que fonctions SQL côté serveur, et d’autre part directement par le client, en-dehors d’une requête SQL. Par exemple avec psql, la commande \lo_import /chemin/fichier.bin insérera le contenu du fichier client sur le serveur sans passer par une requête INSERT ou COPY. En interne, elle appelera la fonction libpq lo_import dans une transaction, qui elle-même appelera les fonctions distantes de création et écriture à travers le protocole.

Avec les objets larges, il n’y a pas d’encodage intermédiaire en format texte, ce sont les données binaires brutes qui transitent. Par ailleurs, comme pour un fichier, le client accède généralement au contenu par morceaux, ce qui permet de travailler en flux (streaming), sans avoir besoin d’ingérer une donnée d’un seul tenant pour la traiter.

A contrario, dans le cas d’un SELECT ou COPY avec des colonnes bytea, le client ne peut pas accéder à une ligne partiellement récupérée, et encore moins à une partie de colonne, sauf à descendre au niveau du protocole et à lire directement la socket réseau.

Importons des photos

Soit un répertoire avec 1023 photos JPEG d’une taille moyenne de 4,5 Mo. On va importer ces photos dans des objets larges, puis dans une table pour faire quelques comparaisons.

Import

Déjà, comment importer un fichier dans une colonne bytea? psql n’offre pas de solution simple. Cette question sur DBA.stackexchange ouverte en 2011 : How to insert (file) data into a PostgreSQL bytea column? suggère différentes méthodes plus ou moins indirectes et compliquées, dont notamment celle de passer par un objet large temporaire.

Pour les objets larges, c’est assez simple:

$ (for i in *.JPG; do echo \\lo_import "$i" ; done) | psql

La sortie va ressembler à ça, et nos 4,5 Go sont importés en quelques minutes.

lo_import 16456
lo_import 16457
lo_import 16458
...

chacun de ces numéros étant l’OID d’un objet nouvellement créé.

Maintenant copions ces données en un seul bytea par photo avec une version simplifiée de la réponse de stackexchange (lo_get n’existait pas en 2011).

CREATE TABLE photos(id oid PRIMARY KEY, data BYTEA);

INSERT INTO photos SELECT oid, lo_get(oid) from pg_largeobject_metadata ;

Export

Pour réexporter ces images avec psql, dans le cas des objets larges il suffit d’utiliser pour chacun:

  \lo_export :oid /chemin/vers/fichier`

Pour les contenus de la table photos, le format le plus simple à restituer en binaire sur le client est le base64. Par exemple la commande psql ci-dessous fait que la donnée bytea est transformée explicitement via encode(data, 'base64') en SQL et conduite via l’opérateur ‘|’ (pipe) dans le programme base64 de la suite GNU coreutils.

SELECT encode(data, 'base64') FROM photos
  WHERE id= :id \g | base64 -d >/chemin/fichier

Stockage

Structure des objets larges

Les objets larges sont stockés dans deux tables systèmes dédiées.

pg_largeobject_metadata a une ligne par objet large, indiquant le possesseur et les droits d’accès. Comme d’autres tables systèmes (pg_class, pg_type, …), elle utilise la pseudo-colonne oid comme clef primaire.

=# \d pg_largeobject_metadata
 Colonne  |   Type    | Collationnement | NULL-able | Par défaut 
----------+-----------+-----------------+-----------+------------
 lomowner | oid       |                 | not null  | 
 lomacl   | aclitem[] |                 |           | 
Index :
    "pg_largeobject_metadata_oid_index" UNIQUE, btree (oid)

La seconde table pg_largeobject porte les données, découpées en segments ou mini-pages bytea d’un quart de bloc maximum, soit 2048 octets par défaut. Sa structure:

=# \d pg_largeobject
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 loid    | oid     |                 | not null  | 
 pageno  | integer |                 | not null  | 
 data    | bytea   |                 | not null  | 
Index :
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

Chaque ligne de cette table comporte l’OID qui référence l’entrée correspondante de pg_largeobject_metadata, le numéro de page en partant de 0, et la mini-page elle-même dans data.

Bien que le stockage de la colonne data soit déclaré extended, il n’y a délibérément pas de table TOAST associée, l’objectif étant que ces mini-pages tiennent dans les pages principales. Cette stratégie est expliquée en ces termes dans le code source:

src/include/storage/large_object.h:

/*
 * Each "page" (tuple) of a large object can hold this much data
 *
 * We could set this as high as BLCKSZ less some overhead, but it seems
 * better to make it a smaller value, so that not as much space is used
 * up when a page-tuple is updated.  Note that the value is deliberately
 * chosen large enough to trigger the tuple toaster, so that we will
 * attempt to compress page tuples in-line.  (But they won't be moved off
 * unless the user creates a toast-table for pg_largeobject...)
 *
 * Also, it seems to be a smart move to make the page size be a power of 2,
 * since clients will often be written to send data in power-of-2 blocks.
 * This avoids unnecessary tuple updates caused by partial-page writes.
 *
 * NB: Changing LOBLKSIZE requires an initdb.
 */
#define LOBLKSIZE		(BLCKSZ / 4)

Autrement cette taille est choisie pour:

  • permettre des petites mises à jour intra-données peu coûteuses.
  • être au-dessus du seuil de compression.
  • être une puissance de 2.

Structure des tables TOAST

Au contraire de pg_largeobject, la table photos a une table TOAST associée. On n’a pas besoin de le savoir pour accéder aux données binaires, puisque qu’en sélectionnant photos.data, PostgreSQL va automatiquement lire dedans si nécessaire, mais regardons quand même sous le capot pour continuer la comparaison.

La table TOAST est identifiable via cette requête:

=# SELECT reltoastrelid,
  pg_total_relation_size(reltoastrelid) FROM pg_class
  WHERE oid='photos'::regclass;

 reltoastrelid | pg_total_relation_size 
---------------+------------------------
         18521 |             4951367680

La doc nous indique à quoi s’attendre au niveau de la structure:

Chaque table TOAST contient les colonnes chunk_id (un OID identifiant la valeur TOASTée particulière), chunk_seq (un numéro de séquence pour le morceau de la valeur) et chunk_data (la donnée réelle du morceau). Un index unique sur chunk_id et chunk_seq offre une récupération rapide des valeurs

Et là, surprise (ou pas): c’est exactement le même type de structure que pg_largeobject ! Vérifions dans psql:

=# select relname from pg_class where oid=18521;
    relname     
----------------
 pg_toast_18518

=# \d+ pg_toast.pg_toast_18518
Table TOAST « pg_toast.pg_toast_18518 »
  Colonne   |  Type   | Stockage 
------------+---------+----------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain

Vu ces similarités, on pourrait penser que physiquement, les deux modèles de stockage pèsent pareillement sur disque. En fait, ce n’est pas vraiment le cas.

Poids réel des données

Calculons le surpoids global, c’est-à-dire tailles des tables versus tailles des données contenues, avec les deux méthodes de stockage, sur l’exemple du millier de photos.

D’abord les tailles des tables:

=# select n,pg_size_pretty(pg_total_relation_size(n))  from
   (values ('pg_largeobject'), ('pg_largeobject_metadata'), ('photos')) as x(n);
            n            | pg_size_pretty 
-------------------------+----------------
 pg_largeobject          | 6106 MB
 pg_largeobject_metadata | 112 kB
 photos                  | 4722 MB
(3 lignes)

La taille des données contenues à proprement parler étant:

=# select pg_size_pretty(sum(octet_length(data))) from photos;
 pg_size_pretty 
----------------
 4551 MB
(1 ligne)

Avec seulement 10% de surpoids pour la table photos versus 34% de surpoids pour pg_largeobject, il n’y a pas photo justement: sur le plan de l’espace disque, les objets larges et leur stockage “mini-page” en ligne perdent largement par rapport au stockage TOAST.

Alors on peut légitimement se demander pourquoi les mêmes contenus rangés dans des structures similaires consomment des espaces assez différents.

Première hypothèse: il y aurait plus de lignes, et le surcoût par ligne ferait la différence.

Les entêtes de ligne prennent effectivement de la place dans PostgreSQL, au minimim 27 octets comme détaillé dans le HeapTupleHeaderData.

Le nombre de lignes de la table TOAST diffère effectivement de celui de pg_largeobject, mais en fait il s’avère plus grand, ce qui invalide donc complètement cette hypothèse:

=# select (select count(*) from pg_toast.pg_toast_18518),
          (select count(*) from pg_largeobject);

  count  |  count  
---------+---------
 2390981 | 2330392

Deuxième hypothèse: les données seraient mieux compressées dans la table TOAST. Concernant des photos JPEG déjà compressées, en principe il ne faut pas s’attendre à une compression supplémentaire par l’algorithme LZ d’un côté comme de l’autre, mais vérifions quand même.

La taille nominative d’un bytea est donné par la fonction octet_length(), la taille sur disque (donc après compression éventuelle) correspond à pg_column_size() moins 4 octets pour l’entête varlena.

Muni de ça, voici une requête qui va calculer et comparer les taux de compression dans les deux cas:

SELECT
 100-avg(ratio_lo) as "% moyen compression LO",
 100-avg(ratio_bytea) as "% moyen compression BYTEA",
 sum(bcmp) as "taille post-compression BYTEA",
 sum(lcmp) as "taille post-compression LO",
 sum(braw) as "taille pré-compression BYTEA",
 sum(lraw) as "taille pré-compression LO"
FROM (
SELECT s.id, bcmp, braw, lcmp, lraw,
  (bcmp*100.0/braw)::numeric(5,2) as ratio_bytea,
  (lcmp*100.0/lraw)::numeric(5,2) as ratio_lo
FROM (
SELECT t.id,
     octet_length(t.data)::numeric as braw,
     (pg_column_size(t.data)-4)::numeric as bcmp,
     s.lraw::numeric,
     s.lcmp::numeric
   FROM photos as t
   JOIN
    (select loid,
       sum(octet_length(data)) as lraw,
       sum(pg_column_size(data)-4) as lcmp
      FROM pg_largeobject
      GROUP BY loid HAVING sum(pg_column_size(data)-4)>0) as s
   ON(loid=id)
) s
) s1;

Résultat:

-[ RECORD 1 ]-----------------+------------------------
% moyen compression LO        | 0.2545161290322581
% moyen compression BYTEA     | 0.0956207233626588
taille post-compression BYTEA | 4771356790
taille post-compression LO    | 4764013877
taille pré-compression BYTEA  | 4771604903
taille pré-compression LO     | 4771604903

Comme prévu, la compression par-dessus JPEG est très faible. Mais celle du bytea l’est encore plus celle des objets larges, avec 0,09% contre 0,25%, soit 7,1 MB de différence cumulée sur la totalité. Donc non seulement ça n’explique pas le surpoids de pg_largeobject, mais ça irait plutôt légèrement dans le sens inverse.

Troisième hypothèse: il y a trop de fragmentation ou espace inutilisé à l’intérieur de pg_largeobject par rapport à celles des tables TOAST. Au fait, quelle est cette taille des “chunks” ou mini-pages du côté TOAST? La doc nous dit encore:

Les valeurs hors-ligne sont divisées (après compression si nécessaire) en morceaux d’au plus TOAST_MAX_CHUNK_SIZE octets (par défaut, cette valeur est choisie pour que quatre morceaux de ligne tiennent sur une page, d’où les 2000 octets)

Pour estimer l’espace inutilisé dans les pages, sans aller jusqu’à les regarder à l’octet près, bien qu’en théorie faisable avec l’extension pg_pageinspect, on peut faire quelques vérifications en SQL de base. Comme le contenu dans notre exemple n’a pas été modifié après import, les données sont a priori séquentielles dans les pages. On peut donc se faire une idée de la relation entre les lignes et les pages les contenant juste en regardant comment évolue la colonne ctid sur des lignes logiquement consécutives.

Par exemple, en prenant une photo au hasard:

# select ctid,pageno,octet_length(data),pg_column_size(data)
  from pg_largeobject where loid=16460;
   ctid   | pageno | octet_length | pg_column_size 
----------+--------+--------------+----------------
 (2855,3) |      0 |         2048 |           1079
 (2855,4) |      1 |         2048 |            132
 (2855,5) |      2 |         2048 |            198
 (2855,6) |      3 |         2048 |           1029
 (2855,7) |      4 |         2048 |            589
 (2856,1) |      5 |         2048 |           2052
 (2856,2) |      6 |         2048 |           2052
 (2856,3) |      7 |         2048 |           2052
 (2857,1) |      8 |         2048 |           2052
 (2857,2) |      9 |         2048 |           2052
 (2857,3) |     10 |         2048 |           2052
 (2858,1) |     11 |         2048 |           2052
 (2858,2) |     12 |         2048 |           2052
 (2858,3) |     13 |         2048 |           2052
 (2859,1) |     14 |         2048 |           2052
 (2859,2) |     15 |         2048 |           2052
 (2859,3) |     16 |         2048 |           2052
... 1900 lignes sautées ...
 (3493,2) |   1917 |         2048 |           2052
 (3493,3) |   1918 |         2048 |           2052
 (3494,1) |   1919 |         2048 |           2052
 (3494,2) |   1920 |         2048 |           2052
 (3494,3) |   1921 |          674 |            678

Dans un ctid comme (2855,3), le premier nombre représente la page et le deuxième le numéro séquentiel de ligne relativement à cette page. On voit dans cet extrait qu’en dehors du début et de la fin, les lignes valent 1,2,3, puis ça passe à la page suivante et ainsi de suite. Très schématiquement, on a le plus souvent 3 lignes par page. C’est logique parce qu’il n’y a pas de place pour 4 lignes. 4*2052 dépasserait déjà 8192 octets, sans même compter les entêtes de ligne et les autres colonnes.

Maintenant regardons l’équivalent dans la table TOAST. C’est trop compliqué de retrouver le chunk_id qui corresponde à la même photo, donc je vais prendre le début de la table, mais on peut vérifier par échantillons aléatoires que le même motif se répète massivement dans toutes ces données.

# select ctid,chunk_id,chunk_seq,octet_length(chunk_data),pg_column_size(chunk_data)
  from pg_toast.pg_toast_18518 limit 20;

 ctid  | chunk_id | chunk_seq | octet_length | pg_column_size 
-------+----------+-----------+--------------+----------------
 (0,1) |    18526 |         0 |         1996 |           2000
 (0,2) |    18526 |         1 |         1996 |           2000
 (0,3) |    18526 |         2 |         1996 |           2000
 (0,4) |    18526 |         3 |         1996 |           2000
 (1,1) |    18526 |         4 |         1996 |           2000
 (1,2) |    18526 |         5 |         1996 |           2000
 (1,3) |    18526 |         6 |         1996 |           2000
 (1,4) |    18526 |         7 |         1996 |           2000
 (2,1) |    18526 |         8 |         1996 |           2000
 (2,2) |    18526 |         9 |         1996 |           2000
 (2,3) |    18526 |        10 |         1996 |           2000
 (2,4) |    18526 |        11 |         1996 |           2000
 (3,1) |    18526 |        12 |         1996 |           2000
 (3,2) |    18526 |        13 |         1996 |           2000
 (3,3) |    18526 |        14 |         1996 |           2000
 (3,4) |    18526 |        15 |         1996 |           2000
 (4,1) |    18526 |        16 |         1996 |           2000
 (4,2) |    18526 |        17 |         1996 |           2000
 (4,3) |    18526 |        18 |         1996 |           2000
 (4,4) |    18526 |        19 |         1996 |           2000

On retrouve la taille de 2000 octets mentionnée dans la doc, et les 4 lignes par page, dans la mesure où les numéros de ligne par page dans ctid sont typiquement 1,2,3,4 avant passage à la page suivante, et ainsi de suite.

4 lignes de chunk_data occupent 4*2000=8000 octets, et les 192 octets restants sur 8192 permettent manifestement de contenir tout le reste, notamment 27 octets d’entête par ligne plus 4+4 octets pour chunk_id et chunk_seq. Ajoutons à ça un entête par page de 24 octets, et il est clair que cette page est occupée presque totalement par des informations utiles: (27 + 4 + 4 + 2000) * 4 = 8140.

Au contraire de ça, nos pages de pg_largeobject semblent être majoritairement occupées par 3 lignes remplies de cette manière: (27 + 4 + 4 + 2052) * 3 = 6261 octets

Compte-tenu de toute ça une estimation grossière du ratio entre les tailles des photos et celle de pg_largeobject pourrait être (2048*3)/8192 = 0,75

Les données “pures” pèsent 4771604903 octets, et pg_largeobject pèse 6402637824 octets.
Le ratio réel d’utilité disque pour les objets larges vaut donc 4771604903 / 6402637824 = 0,745

Du côté TOAST, ce ratio estimé grossièrement est de (2000*4)/8192 = 0,9765625.
Le ratio réel d’utilité est de 4771604903 / 4951490560 = 0,9637

La réalité est remarquablement proche de l’estimation, du fait de la grande taille des objets, de la quasi-absence de compression, et du fait qu’il n’y a pas de désorganisation dans les pages, en l’absence de modifications post-chargement.

Mais cette structuration est assez réaliste par rapport à l’usage qui est souvent fait des contenus binaires, qui sont créés ou effaçés d’un seul tenant, mais dont l’intérieur n’est jamais modifié. Car qui saurait changer des pixels dans une image JPEG ou une phrase dans un PDF avec une requête UPDATE?

Conclusion

Dans ce billet, on a pas mal regardé la structure interne de ces contenus binaires, et observé à travers cet exemple comment une différence de paramétrage de 48 octets a des conséquences finalement non négligeables sur des données de grande taille, ici en faveur du bytea sur l’espace disque.

N’oubliez pas que ce résultat ne s’applique pas forcément à vos données, ça dépend comment elles se compressent et comment ces tables sont mises à jour sur le temps long.

Dans un ou deux autres billets à venir, j’essaierai de détailler d’autres éléments du tableau de comparaison en haut de page, avec d’autres différences assez nettes sur certains points, certaines en faveur des objets larges, d’autres en faveur des bytea.

par Daniel Vérité le mercredi 15 novembre 2017 à 12h31

lundi 13 novembre 2017

Damien Clochard

Fin de parcours pour PostgreSQL 9.2

PostgreSQL 10 est sortie il y a quelques semaines et un premier correctif de sécurité a été publié le 10 novembre.

Comme chaque année, la sortie d’une nouvelle version s’accompagne de la fin du
support d’une version précédente. En l’occurence, c’est PostgreSQL 9.2, sortie
en 2012, qui est désormais cataloguée comme “End Of Life” (EOL).

Pour les nostalgiques, la version 9.2 était une étape importante puisqu’elle a marqué l’arrivée du type JSON et de la réplication Hot Standby en cascade. 5 ans plus tard, PostgreSQL 10 complète le Hot Standby avec la réplication logique , et fait jeu égal avec MongoDB avec des fonctionnalités JSON pleinement intégrées : indexation, procédures stockées PL/V8, recherche plein texte

Tout ça pour vous dire que si vous avez des instances PostgreSQL 9.2 (ou antiérieures) en production, il est temps de prévoir une montée de version dès que possible… Vous ne serez pas déçus !

par Damien Clochard le lundi 13 novembre 2017 à 09h52

mercredi 11 octobre 2017

Thomas Reiss

Supervision de PostgreSQL 10 avec check_pgactivity

PostgreSQL 10 vient tout juste de sortir et apporte une nouveauté significative concernant la supervision d'une instance. Grâce au travail de Dave Page, PostgreSQL 10 amène un rôle système pg_monitor qui permet d'accéder à toutes les fonctions de supervision sans nécessiter d'être super-utilisateur.

La sonde check_pgactivity permet naturellement de tirer partie de cette nouvelle possibilité avec votre environnement de supervision Nagios ou compatible.

check_pgactivity 2.3 beta 1

La sonde de supervision check_pgactivity arrive en version 2.3 beta 1 et intègre le support de PostgreSQL 10.

En plus de cela, le service backend_status accepte maintenant des seuils exprimés avec des unités de temps, par exemple pour détecter les transactions en attente d'un verrou depuis plus d'un certain temps. Un bug assez ancien a par ailleurs été corrigé dans ce même service.

Le service sequences_exhausted a été corrigé de manière à accepter les séquences rattachées à une colonne d'un type non-numérique.

Enfin, pour les personnes souhaitant contribuer au projet, une nouvelle documentation vous permettra de mettre le pied à l'étrier.

Changements majeurs dans PostgreSQL 10

Dans toutes les versions précédentes, il fallait nécessairement être super-utilisateur pour pouvoir superviser une instance PostgreSQL.

Dave Page a répondu à cette problématique en proposant un rôle système pg_monitor qui permet de bénéficier des droits pour accéder aux vues pg_stat_* et utiliser les fonctions pg_database_size() et pg_tablespace_size(). Le commit 25fff40798fc4ac11a241bfd9ab0c45c085e2212 vous permettra d'avoir de plus amples explications.

Le second changement important concerne le renommage de XLOG en WAL. Ainsi, le répertoire pg_xlog, qui contient les journaux de transaction, est renommé en pg_wal. Toutes les fonctions contenant le terme xlog sont renommées de la même façon, comme pg_current_xlog_location() qui devient pg_current_wal_lsn(). À noter que location devient lsn, mais je ne détaille pas ce changement. Etc.

Enfin, un dernier changement important concerne l'arrivée de la fonction pg_ls_waldir() pour pouvoir lister le contenu du répertoire pg_wal. Pour ce faire, nous utilisions auparavant la fonction pg_ls_dir().

Pour en savoir plus sur tous ces changements, je vous invite à consulter les notes de version de PostgreSQL 10.

Mise en œuvre de la supervision

Création d'un utilisateur de supervision

Commençons par créer un rôle monitor qui ne dispose pas de privilèges particuliers, mais qui est membre de pg_monitor :

CREATE ROLE monitor LOGIN IN ROLE pg_monitor;

Pour permettre aux services btree_bloat et table_bloat de fonctionner, il faut aussi permettre au rôle monitor d'effectuer un SELECT sur pg_statistic, dans toutes les bases de données de l'instance :

GRANT SELECT ON pg_statistic TO monitor;

À noter que vous devrez configurer l'authentification par vous-même, en affectant un mot de passe ou non, à votre guise.

Supervision d'un service

Le service wal_files se connecte à une instance en utilisant le rôle monitor créé plus haut. Il permet de superviser le nombre de journaux de transaction présents dans le répertoire $PGDATA/pg_wal. Nous utilisons la sortie human pour obtenir un résultat lisible par le commun des mortels :

$ ./check_pgactivity -h localhost -p 5432 -U monitor -F human -s wal_files
Service        : POSTGRES_WAL_FILES
Returns        : 0 (OK)
Message        : 45 WAL files
Perfdata       : total_wal=45
Perfdata       : recycled_wal=44
Perfdata       : tli=1
Perfdata       : written_wal=1
Perfdata       : kept_wal=0
Perfdata       : wal_rate=74.45Bps

Si vous utilisez la sonde check_pgactivity avec Nagios, vous choisirez évidemment le format de sortie nagios, qui est d'ailleurs le format de sortie par défaut.

Incompatibilités

Comme vu plus haut, les services btree_bloat et table_bloat doivent avoir accès au catalogue pg_statistic. Sans cet accès, il n'est pas possible de calculer une estimation de la fragmentation des index et des tables le plus rapidement possible dans toutes les situations. Nous avions rencontré un gros problème de performance sur une base disposant de plusieurs milliers de tables lorsque nous utilisions encore la vue pg_stats.

Ainsi, nous devons donc donner le privilège de SELECT sur pg_statistic à notre rôle monitor (ou à pg_monitor si l'on souhaite être moins restrictif) :

GRANT SELECT ON pg_statistic TO monitor;

UPDATE Le service temp_files, qui permet de superviser le volume de fichiers temporaires créés, nécessite les privilèges de super-utilisateur car il utilise la fonction pg_ls_dir(). La sonde va être corrigée ultérieurement pour qu'elle fonctionne avec PostgreSQL 10, mais il ne sera plus possible de superviser les fichiers temporaires créés en "live".

À vous !

N'hésitez pas à nous remonter les problèmes que vous avez pu rencontrer avec cette dernière version check_pgactivity. Si nous n'avons aucun retour négatif, la version 2.3 stable va suivre d'ici quelques jours.

Téléchargement

Rendez-vous sur github :

par Thomas Reiss le mercredi 11 octobre 2017 à 14h44

mardi 10 octobre 2017

Daniel Verite

Un diff générique entre deux tables

Comment calculer les lignes qui sont dans une table mais pas dans une autre et vice-versa, sans préjuger de la structure des tables, ni du fait qu’il y ait une clef primaire et des colonnes qui la portent éventuellement?

Le principe de base

Les opérateurs ensemblistes EXCEPT et UNION nous amènent une solution assez simple et élégante, car on peut voir les différences entre deux tables T1 et T2 de même structure comme:

 T1 except T2 (les lignes présentes dans T1 et pas dans T2)
   UNION  
 T2 except T1 (les lignes qui sont dans T2 et pas dans T1)

On peut aussi chercher à exprimer ça de manière assez générique, c’est-à-dire que T1 et T2 puissent être des paramètres plutôt que d’écrire une requête différente pour chaque couple de tables (T1,T2) qu’on peut être amené à comparer.

Faire du SQL générique, c’est souvent faire du SQL dynamique à l’aide de plpgsql. Voyons à quoi pourrait ressembler une fonction plpgsql qui nous renverrait les différences entre deux tables quelconques, un peu comme la sortie d’un diff avec des signes ‘+’ et ‘-‘ en tête de ligne pour marquer les insertions et suppressions.

Transférer toute une ligne dans une valeur

Pour pouvoir sortir toute une ligne sous la forme d’un champ, toujours sans préjuger de sa structure, on utilise le fait que PostgreSQL permet la conversion d’un enregistrement du type de la table vers un champ texte avec un simple CAST, soit une expression du style: SELECT table.*::text FROM table...

Le résultat a un format compatible avec un constructeur de ligne ROW(...), c’est-à-dire qui ressemble un peu à une ligne CSV avec des parenthèses autour. En gros:

  • les champs sont séparés par des virgules.
  • des guillemets délimitent les champs dès que nécessaire.
  • les guillemets internes aux champs sont échappés.

Il reste à ajouter un ‘+’ ou ‘-‘ dans un champ à part et à retourner un type TABLE("+/-" text, text) qui représente le signe suivi de la ligne insérée ou supprimée entre une table et l’autre.

Passer en paramètre les tables à la fonction

Pour passer en paramètre nos tables à une fonction plpgsql, on pourrait utiliser leur nom en type text ou name, mais il y a plus intéressant: le type regclass.

C’est un type “alias d’OID” pour les entrées de pg_class, c’est-à-dire que le moteur SQL évalue 'nom_objet'::regclass au moment de l’exécution en cherchant cet objet dans le catalogue, avec un comportement qui gère un tas de détails pour nous:

  • le fait que le nom puisse être qualifié par un schéma ou non, suivant que le search_path en cours inclut ou non ce schéma.

  • la sensibilité à la casse (majuscule/minuscule) du nom suivant qu’il soit entouré ou non de guillemets.

  • une erreur est déclenchée par la conversion de type vers regclass si l’objet n’est pas trouvé dans le catalogue. Donc notre fonction qui prend du regclass en entrée ne démarrera même pas si on lui donne des arguments incorrects, ce qui est sécurisant car on va quand même injecter ces arguments dans une requête SQL.

En bref regclass permet de fournir un nom de table dynamique via un paramètre en lui appliquant la même gestion que si ce nom avait été présent dans une requête statique. En interne ce type représente un OID mais on n’a pas vraiment besoin de le savoir si on ne veut pas fouiller plus que ça (dans le cas contraire \dC regclass sous psql sera utile pour examiner les différentes conversions).

La fonction

Avec ces éléments, le corps de fonction n’a plus qu’à appliquer format() sur le modèle de requête, en lui faisant remplacer les %I par les références aux tables passées en type regclass, et exécuter le résultat.

Avec RETURN QUERY EXECUTE format(...), une seule instruction plpgsql suffit à faire tout:

CREATE FUNCTION diff_tables(table1 regclass, table2 regclass) 
   RETURNS TABLE("+/-" text, ligne text)
AS $func$
BEGIN
  RETURN QUERY EXECUTE format($$
     SELECT '+', d1.*::text FROM (
        SELECT * FROM %I
           EXCEPT
        SELECT * FROM %I
     ) AS d1

     UNION ALL

     SELECT '-', d2.*::text FROM (
        SELECT * FROM %I
           EXCEPT
        SELECT * FROM %I
     ) AS d2
   $$, table2, table1, table1, table2);
END
$func$ language plpgsql;

Exemple d’utilisation

Le format, un peu similaire à la commande diff avec une ligne par différence, est pratique pour comparer des résultats obtenus avec des résultats supposés, par exemple dans des tests de non-régression, quand on s’attend à des déviations nulles ou faibles.

On peut aussi mesurer des différences entre avant et après un traitement, comme une installation. Par exemple si on fait un CREATE EXTENSION, le diff avant/après de pg_extension va donner la chose suivante:

=# create temporary table backup_pg_extension as select * from pg_extension;
SELECT 1

=# create extension ltree;
CREATE EXTENSION

=# select * from diff_tables('backup_pg_extension', 'pg_extension');
 +/- |          ligne          
-----+-------------------------
 +   | (ltree,10,2200,t,1.1,,)
(1 ligne)

par Daniel Vérité le mardi 10 octobre 2017 à 15h10

vendredi 1 septembre 2017

Daniel Verite

Du nouveau dans les triggers avec PostgreSQL 10

PostgreSQL offre des triggers qui peuvent se déclencher chaque fois qu’une instruction est exécutée (AFTER ou BEFORE STATEMENT), ou chaque fois qu’une ligne est affectée (AFTER ou BEFORE ROW). Jusqu’avant la version 10, seules les fonctions associées au second type pouvaient accéder aux données modifiées par l’instruction déclencheuse, à travers les pseudo-variables OLD et NEW représentant l’état avant/après de la ligne affectée.

A compter de la version 10, les triggers AFTER STATEMENT peuvent avoir accès à l’ensemble des lignes modifiées, avant et après changement, à travers un nouveau genre de pseudo-variable de type table. Concrètement, la nouveauté est accessible via cette syntaxe, par exemple pour DELETE:

CREATE TRIGGER nom_trigger AFTER DELETE ON nom_table
REFERENCING OLD TABLE AS OLD
FOR EACH STATEMENT
EXECUTE PROCEDURE nom_procedure();

grâce à quoi dans la fonction, OLD sera utilisable comme une table en lecture seule.

Quelle est l’utilité d’accéder globalement aux lignes changées plutôt qu’une par une?

D’abord il y a d’autres SGBDs qui utilisent cette méthode, parfois exclusivement. Par exemple, MS-SQL server n’offre pas de déclenchement par ligne, mais uniquement par instruction, avec les données concernées dans des pseudo-tables inserted et deleted. Avec la version 10, il devient plus facile de porter ces trigger vers PostgreSQL, puisqu’on peut reproduire cette logique directement.

Il y aussi et surtout un intérêt pour les performances, dans les cas où il vaut mieux faire des opérations ensemblistes sur ces données plutôt que de les traiter ligne par ligne.

Voyons ça sur un exemple avec un benchmark tout simple.

Un schéma de test

Imaginons qu’on ait un million de documents, et une centaine de labels (tags) pour les catégoriser, avec ces structures de tables:

CREATE TABLE document (
 id serial primary key,
 title text,
 content text
);

CREATE TABLE tag (
 id serial primary key,
 name text
);

CREATE TABLE doc_tag (
 doc_id integer references document(id),
 tag_id integer references tag(id),
 unique(doc_id,tag_id)
);

L’information disant, pour chaque label, à combien de documents il a été affecté s’obtient normalement par:

SELECT tag_id, count(*) FROM doc_tag GROUP BY tag_id;

ou pour un seul label:

 SELECT count(*) FROM doc_tag WHERE tag_id =
  (SELECT tag_id FROM tag WHERE name = :nom_label);

Mais quand on a beaucoup d’entrées dans doc_tag du fait qu’il y a beaucoup de documents, ces requêtes seront lentes.

Si on sait que nos applis ont besoin de cette information instantanément, typiquement on va matérialiser et maintenir à jour un compteur permanent avec une seule ligne par label, dans une table de ce genre:

CREATE TABLE tag_count (
  tag_id integer references tag(id),
  cnt integer,
  unique(tag_id)
);

Si on part d’une table doc_tag déjà remplie on initialisera ces compteurs avec:

INSERT INTO tag_count(tag_id,cnt)
  SELECT tag_id,count(*) FROM doc_tag GROUP BY tag_id;

(sans les compteurs à zéro qu’on évite pour simplifier l’exemple).

Puis va créer un trigger qui met à jour ce compteur pour toute attribution ou désattribution d’un label.

En mode FOR EACH ROW, le code pour une version 9.5+ ressemblera à ça:

CREATE FUNCTION row_update_tag_count() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
      INSERT INTO tag_count(tag_id,cnt)
       values (NEW.tag_id,1)
      ON CONFLICT (tag_id) DO UPDATE set cnt = tag_count.cnt + 1;

  ELSIF TG_OP = 'DELETE' THEN
      UPDATE tag_count SET cnt = cnt - 1
      WHERE tag_id = OLD.tag_id;
  END IF;

  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trigger1 AFTER INSERT OR DELETE on doc_tag 
FOR EACH ROW
EXECUTE PROCEDURE row_update_tag_count();

En mode FOR EACH STATEMENT, le code pour une version 10+ pourrait ressembler à ça:

CREATE FUNCTION set_update_tag_count() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
      INSERT INTO tag_count(tag_id,cnt)
      select tag_id,count(*) AS insert_count from NEW group by tag_id
      ON CONFLICT (tag_id) DO UPDATE set cnt = tag_count.cnt + excluded.cnt;

  ELSIF TG_OP = 'DELETE' THEN
      UPDATE tag_count SET cnt = cnt - d.delete_count
        FROM (select tag_id,count(*) AS delete_count from OLD group by tag_id) AS d   
      WHERE tag_count.tag_id = d.tag_id;
  END IF;

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger2 AFTER INSERT on doc_tag 
REFERENCING NEW TABLE AS NEW
FOR EACH STATEMENT
EXECUTE PROCEDURE set_update_tag_count();

CREATE TRIGGER trigger3 AFTER DELETE on doc_tag 
REFERENCING OLD TABLE AS OLD
FOR EACH STATEMENT
EXECUTE PROCEDURE set_update_tag_count();

Ici on ne peut pas regrouper dans le même trigger INSERT ET DELETE (bien qu’associés à la même fonction) pour des questions de syntaxe, car la doc nous dit:

OLD TABLE may only be specified once, and only on a trigger which can fire on UPDATE or DELETE. NEW TABLE may only be specified once, and only on a trigger which can fire on UPDATE or INSERT

Le test

Il consiste simplement à faire un INSERT unique de 10000 tags, puis 20000, 30000 etc. jusqu’à 500000, tirés au hasard et distribués sur les documents de manière équiprobable.

Le but est de mettre en évidence la différence sur des insertions en masse entre les différents de type de trigger. On mesure le temps pris dans trois cas:

  • sans trigger pour avoir un temps de base.
  • avec trigger1 (FOR EACH ROW)
  • avec trigger2 (FOR EACH STATEMENT)

Avant chaque insertion la table doc_tag subit un TRUNCATE pour la ramener à zéro physiquement, index inclus, et autovacuum est désactivé pour éviter d’interférer.

Le résultat

Ce graphe montre les temps d’exécution en fonction du nombre de lignes insérées, sur Pg10 beta3 avec la configuration entièrement par défaut. Les lignes ne sont pas cumulées, chaque INSERT se faisant avec dog_tag et tag_count initialement vides.

Graphe performances triggers

C’est sans surprise que le trigger FOR EACH ROW fait s’envoler le temps d’exécution sur une insertion en masse, par rapport au cas où il n’y a pas de trigger.

Ce qui n’est pas forcément aussi prévisible est que quel que soit le volume de l’INSERT, la différence est insignifiante entre le cas FOR EACH STATEMENT et le cas où il n’y a aucun trigger,

Autrement dit, le coût induit par trigger2 est quasiment nul, alors que ce n’est pas du tout le cas pour trigger1.

En conclusion, une application avec ce style de comptage par trigger et de l’écriture en masse a potentiellement beaucoup à gagner, après un passage en PostgreSQL 10+, à les convertir de FOR EACH ROW en FOR EACH STATEMENT.

par Daniel Vérité le vendredi 1 septembre 2017 à 13h31

mardi 18 juillet 2017

Nicolas Gollet

Fitrage par IP avec les RLS

Depuis la version 9.5 de PostgreSQL, la sécurité au niveau des lignes (Row Level Security) a été introduite. Cette fonctionnalité permet de contrôler l'accès aux lignes d'une table de base de données en fonction des caractéristiques de l'utilisateur exécutant une requête.

Nous pouvons donc l'utiliser pour limiter, par exemple, le type de requêtes acceptées par rapport à l'adresse IP source du client.

La fonction interne PostgreSQL inet_client_addr() permet d'obtenir l'adresse IP du client de la connexion en cours.

Dans l'exemple ci-dessous, nous allons interdire toutes les requêtes de modification, d'insertion et de suppression sur la table "matable" lorsque l'adresse IP source de la connexion est '10.1.1.65'.

Par défaut lorsqu'un utilisateur ne rentre pas dans le critère d'une règle, celui si se voit refuser l'accès aux données.

Concernant la charge supplémentaire constatée, elle oscille entre 1% et 10% suivant le nombre de lignes rapporté par la transaction.

LINETPS RLS ON

TPS RLS OFF

DIFF%
14960056270667088
1004203045616358692
1000245852513655197
10000451345291699

La première colonne et le nombre de lignes récupéré par transaction, la seconde le nombre de transactions par seconde constaté lorsque le RLS est à ON, la troisième lorsque RLS est à OFF. La colonne DIFF est la différence entre les 2 en nombre de transactions.

Ces valeurs ont été obtenues par l'utilisation de pgbench sur une table contenant 1 colonne de taille fixe avec les paramètres suivants :

pgbench -n -j 6 -c 20 -P 1 -T 30 -U rlsdemo -h pg96.local.ng.pe -f rls_bench.sql rlsdemo

par Nicolas GOLLET le mardi 18 juillet 2017 à 19h45

lundi 12 juin 2017

Guillaume Lelarge

Début de la traduction du manuel de PostgreSQL v10

J'ai enfin terminé le merge du manuel de la version 10. Je n'aime pas ce boulot mais il est nécessaire pour pouvoir organiser la traduction. Bref, c'est fait, et on peut commencer le boulot intéressant, à savoir la traduction. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Traduction-v10

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

par Guillaume Lelarge le lundi 12 juin 2017 à 19h45

mardi 6 juin 2017

Daniel Verite

mardi 23 mai 2017

Daniel Verite

Les branches if/else/endif dans psql (PostgreSQL 10)

Une nouveauté majeure du client psql de PostgreSQL 10 est le support des branchements conditionnels, exprimables via ces nouvelles méta-commandes:

\if EXPR1
  ...
\elif EXPR2
  ... 
\else
  ...
\endif

Voyons déjà quelles sont les différences entre cette approche et les instructions IF / ELSIF / ELSE / END IF du langage plpgsql, déjà disponibles dans les fonctions et les blocs anonymes DO, avec ce type de syntaxe:

 DO $$
   BEGIN
     IF expression-sql THEN
      -- instructions
     ELSIF autre-expression-sql
       -- instructions
     END IF;
   END
 $$ language plpgsql;

COMMIT et ROLLBACK conditionnel

Il se trouve que COMMIT ou ROLLBACK ne peuvent pas être initiés de l’intérieur d’un bloc DO, ce bloc étant confiné à la transaction dont il dépend. C’est le même modèle d’exécution que pour les fonctions, qui sont soumises à la même contrainte On peut y insérer des sous-transactions via SAVEPOINT, mais la transaction principale n’est réellement contrôlable que par le client SQL.

Justement les branches psql offrent une solution simple à ce problème, puisqu’un script peut maintenant comporter une séquence du type:

 BEGIN;
 -- écritures en base
 \if :mode_test
   \echo 'Mode test: Annulation transactionnelle des modifications'
   ROLLBACK;
 \else
   \echo 'Validation transactionnelle des modifications'
   COMMIT;
 \endif

La variable psql mode_test peut être initialisée de l’extérieur, comme une option, via un appel en shell de la forme: $ psql -v mode_test=1 -U user -d database, ce qui donne un équivalent du “–dry-run” qu’ont certaines commandes comme make.

Syntaxe des expressions

L’interpréteur psql n’est pas doté d’un évaluateur interne (pas encore?), et la condition derrière un \if doit être une chaîne de caractères interprétable en booléen, c’est-à-dire true et false ou leurs diminutifs t et f, ou 0 et 1, ou encore on et off.

Quand le script n’a pas la valeur à tester directement sous cette forme, il faut la produire, soit par une commande externe via l’opérateur backtick (guillemet oblique comme dans \set var `commande`), soit par une requête SQL.

Prenons à titre d’exemple le cas où il faut comparer deux numéros sémantiques de version au format MAJOR.MINOR.PATCHLEVEL (1, 2 ou 3 nombres), l’un correspondant à une version d’un ensemble d’objets déployés en base, l’autre à un niveau de mise à jour à faire via notre script.

Appeler le shell pour tester une condition

Un avantage d’utiliser une évaluation externe est qu’elle fonctionnera indépendamment de l’état de la session SQL (notamment non connectée ou dans une transaction en échec). Par ailleurs, il peut y avoir des situations où un programme externe est plus adapté. Dans l’exemple de la comparaison de version, les systèmes basés sur Debian ont la commande dpkg --compare-versions qui fait plutôt bien l’affaire, sinon cette entrée de stackoverflow propose diverses solutions.

Cependant l’opérateur backtick appelant une commande externe présente deux subtilités qui compliquent un peu la tâche avec \if:

  • il ne lit pas le statut de la commande mais ce qu’elle affiche sur sa sortie standard, alors que la plupart des commandes de test (à commencer par la commande test justement) n’affichent rien.
  • le standard en test shell est 0 pour positif et 1 pour négatif dans le statut, alors qu’on veut les valeurs inverses dans psql.

Sachant ça et en supposant bash comme shell, ça veut dire qu’on devra écrire:

\if `! dpkg --compare-versions ":version_db" ge ":version_script"; echo $?`

(si vous trouvez plus simple, n’hésitez pas à le dire en commentaire!)

A noter au passage que l’interpolation des variables psql par l’opérateur backtick est également une nouveauté de la version 10, c’est-à-dire qu’avec une version antérieure, les :version_db et :version_script auraient été présents tels quels dans la commande.

Et que se passe-t-il en cas d’erreur de la commande shell? Généralement, le résultat dans $? n’étant ni 1 ni 0 mais plutôt 2 et plus, le \if rejette cette valeur qui n’est pas assimilable à un booléen, émet un message d’erreur et poursuit l’exécution en supposant arbitrairement faux comme résultat de la comparaison.

Utiliser l’interpréteur SQL pour tester une condition

Bien sûr, on peut aussi produire la valeur de notre comparaison par une requête. La portabilité du SQL sera aussi préférable à des commandes shell si par exemple le script doit être déployé sous Windows. Même si bash et plein de commandes shell existent sous Windows (cf MSYS ou MSYS2) et que c’est plutôt une bonne idée de les installer, on ne peut pas trop espérer qu’elles le soient par défaut.

Pour la méthode SQL, cette entrée de dba.stackexchange: How to ORDER BY typical software release versions like X.Y.Z inspire une solution à base de tableaux d’entiers.

Le résultat booléen de la comparaison est transféré dans une variable via la méta-commande \gset en fin de requête:

SELECT (string_to_array(:version_db, '.')::int[] >=
        string_to_array(:version_script, '.')::int[]) AS a_jour \gset
\if :a_jour
   \echo 'Le schéma est déjà en version' :version_db
   \quit
\endif

Contrairement au cas de la commande shell, psql n’offre pas pour le moment de syntaxe pour mettre une requête SQL derrière un \if. Il faut nécessairement passer par une variable booléenne intermédiaire. Une consolation: on peut charger plusieurs variables en une seule requête puisque \gset apparie chaque colonne du résultat à une variable de même nom.

En cas d’erreur de la requête, ou si elle ne produit aucune ligne, et s’il y avait déjà une valeur dans les variables en question, elles restent inchangées. Il faudra se méfier dans les scripts qu’un \if ne teste pas la valeur précédente d’une variable dans le cas où une requête censée l’affecter a la moindre chance d’échouer. Au pire, on devra réinitialiser ces variables avant chaque requête, avec une séquence du style:

\unset var1
\unset var2
SELECT ... AS var1, ... AS var2 FROM tables... \gset
\if :var1   -- erreur et \if faux si problème avec la requête ci-dessus 
  ...
\elif :var2  -- erreur et \elif faux si problème avec la requête ci-dessus 
  ...
\else
  ...      -- mais cette branche sera exécutée si erreur de requête
\endif

Ca peut être l’occasion de rappeler qu’il est bon de mettre

\set ON_ERROR_STOP on

dans les scripts sensibles. Cette option est un peu l’équivalent du set -e du shell, elle provoque la sortie immédiate du script en cours en cas d’erreur.

par Daniel Vérité le mardi 23 mai 2017 à 10h01

mercredi 17 mai 2017

Daniel Verite

OpenData: importer les noms de domaines de l’AFNIC

Dans le cadre de l’initiative Open Data .fr, l’AFNIC met à disposition des fichiers de données actualisés régulièrement sur tous les noms de domaines qu’elle gère, ce qui permet à quiconque de produire notamment des statistiques.

Voyons comment importer ces données dans une base PostgreSQL.

Les fichiers sont au format ZIP contenant chacun un seul fichier CSV.
Les colonnes sont documentées dans le guide d’utilisation (PDF).

Le fichier principal “Fichier A” contient une ligne par domaine, soit à mars 2017 un peu plus de 4,5 millions de lignes:

$ wc -l 201703_OPENDATA_A-NomsDeDomaineEnPointFr.csv 
4539091 201703_OPENDATA_A-NomsDeDomaineEnPointFr.csv

Comme souvent dans les fichiers CSV, les noms de colonnes figurent à la première ligne. Il s’agit de:

"Nom de domaine";"Pays BE";"Departement BE";"Ville BE";"Nom BE";"Sous domaine";"
Type du titulaire";"Pays titulaire";"Departement titulaire";"Domaine IDN";"Date 
de création";"Date de retrait du WHOIS"

Les caractères sont au format iso-8859-1, et il y a un certain nombres d’accents dans le fichier, il faut donc en tenir compte pour l’import.

J’ai choisi de créer une seule table avec ces noms simplifiés:

CREATE TABLE domain_fr (
   domaine text,
   pays_be char(2),
   dept_be text,
   ville_be text,
   nom_be text,
   sous_dom text,
   type_tit text,
   pays_tit text,
   dept_tit text,
   idn smallint,
   date_creation date,
   date_retrait date
);

Les contenus peuvent être importés sans préfiltrage dans cette structure avec le COPY de PostgreSQL. L’import passe sans erreur en une quinzaine de secondes sur un serveur basique. Les commandes:

 SET datestyle TO european;
 SET client_encoding TO 'LATIN1';
 \copy domain_fr from '201703_OPENDATA_A-NomsDeDomaineEnPointFr.csv' with (format csv, header, delimiter ';')
 RESET client_encoding;

Faisons quelques requêtes au hasard pour tester les données. Le champ idn est à 0 ou 1 suivant qu’il s’agit d’un nom de domaine internationalisé, c’est-à-dire qui peut contenir des caractères Unicode au-delà du bloc “Basic Latin”. Pour voir combien sont concernés:

 SELECT idn,count(*) from domain_fr GROUP BY idn;
  idn |  count  
 -----+---------
    0 | 4499573
    1 |   39517
 (2 rows)

Pour voir la progression de ce type de domaine par année de création (et constater d’ailleurs qu’en nombre d’ouvertures c’est plutôt en régression après l’année de démarrage):

SELECT date_trunc('year',date_creation)::date as annee, count(*)
FROM domain_fr  WHERE idn=1 GROUP BY 1 ORDER BY 1;
   annee     | count 
 ------------+-------
  2012-01-01 | 20001
  2013-01-01 |  6900
  2014-01-01 |  4734
  2015-01-01 |  3754
  2016-01-01 |  3353
  2017-01-01 |   775
 (6 rows)

On peut aussi apprendre par exemple, quels sont les bureaux d’enregistrement (prestataires) les plus actifs. Regardons le top 10 pour 2016:

SELECT nom_be,count(*)
 FROM domain_fr
 WHERE date_creation>='2016-01-01'::date AND date_creation<'2017-01-01'
 GROUP BY 1
 ORDER BY 2 DESC
 LIMIT 10;
                                  nom_be                                  | count  
 -------------------------------------------------------------------------+--------
  OVH                                                                     | 207393
  1&1 Internet SE                                                         |  74446
  GANDI                                                                   |  63861
  ONLINE SAS                                                              |  15397
  LIGNE WEB SERVICES - LWS                                                |  14138
  AMEN / Agence des Médias Numériques                                     |  13907
  KEY-SYSTEMS GmbH                                                        |  12558
  PAGESJAUNES                                                             |  11500
  Ascio Technologies Inc. Danmark - filial af Ascio Technologies Inc. USA |   9303
  InterNetX GmbH                                                          |   9028

Sans surprise on retrouve les hébergeurs français populaires, avec OVH loin devant, mais aussi 1&1 (allemand) en deuxième.

L’Open Data ouvre la possibilité de croiser des données de sources diverses. Par exemple on pourrait être intéressé par les relations entre les villes françaises et ces noms de domaines.

Un fichier CSV des communes de France peut-être récupéré via l’OpenData gouvernemental. Celui-là est en UTF-8.

Ici on va importer seulement le nom et département des communes. Puis on va utiliser le module PostgreSQL pg_trgm (trigrammes) pour son opérateur de comparaison approchée de chaînes de caractères.

CREATE EXTENSION pg_trgm;

CREATE TABLE communes(nom_commune text,dept char(3));

/*
Le COPY de PostgreSQL ne permet pas de filtrer certaines colonnes, mais
c'est faisable indirectement via la clause PROGRAM appelant le cut d'Unix
*/
\copy communes(dept,nom_commune) FROM program '(cut -d";" -f5,9) < eucircos_regions_departements_circonscriptions_communes_gps.csv' WITH (format csv,delimiter ';',header)


CREATE INDEX trgm_idx1 on communes using gist(nom_commune gist_trgm_ops);
CREATE INDEX trgm_idx2 on domain_fr using gist(domaine gist_trgm_ops);

On va chercher à titre d’exemple les domaines contenant le terme metz et qui ont une correspondance lexicale avec une ville du département 57 (Moselle).

Le degré de similarité de l’opérateur % peut être réglé via le paramètre de configuration pg_trgm.similarity_threshold (ou à l’ancienne via la fonction set_limit()). Par défaut c’est 0,3. Plus la valeur est proche de 1, plus les résultats sont resserrés autour de la correspondance exacte.

SET pg_trgm.similarity_threshold TO 0.5;

WITH v as (SELECT domaine FROM domain_fr WHERE domaine LIKE '%metz%')
SELECT domaine,nom_commune FROM v join communes ON (dept='57' and domaine % nom_commune);

Ca donne 33 résultats:

            domaine            |     nom_commune     
-------------------------------+---------------------
 agmetzervisse.fr              | Metzervisse
 aikido-longeville-les-metz.fr | Longeville-lès-Metz
 a-metz.fr                     | Metz
 a-metz.fr                     | Metz
 a-metz.fr                     | Metz
 aquabike-metzervisse.fr       | Metzervisse
 aumetz.fr                     | Aumetz
 canton-metzervisse.fr         | Metzervisse
 i-metz.fr                     | Metz
 i-metz.fr                     | Metz
 i-metz.fr                     | Metz
 institut-metzervisse.fr       | Metzervisse
 judo-metzervisse.fr           | Metzervisse
 lorry-les-metz.fr             | Lorry-lès-Metz
 lorry-metz-57.fr              | Lorry-lès-Metz
 mairie-longeville-les-metz.fr | Longeville-lès-Metz
 mairie-longeville-les-metz.fr | Longeville-lès-Metz
 metzervisse.fr                | Metzervisse
 metzervisse1972.fr            | Metzervisse
 metz.fr                       | Metz
 metz.fr                       | Metz
 metz.fr                       | Metz
 metzinger.fr                  | Metzing
 metzmetz.fr                   | Metz
 metzmetz.fr                   | Metz
 metzmetz.fr                   | Metz
 mjc-metzeresche.fr            | Metzeresche
 mma-longeville-les-metz.fr    | Longeville-lès-Metz
 mma-montigny-les-metz.fr      | Montigny-lès-Metz
 montigny-les-metz.fr          | Montigny-lès-Metz
 moulins-les-metz.fr           | Moulins-lès-Metz
 pompiers-metzervisse.fr       | Metzervisse
 rx-montigny-les-metz.fr       | Montigny-lès-Metz

On voit clairement l’effet de la correspondance approchée avec “lorry-metz-57.fr” qui se trouve apparié avec “Lorry-lès-Metz”.

Un certain nombre de domaines (4684 exactement) commençent par la chaîne “mairie-“.

On peut à nouveau utiliser l’opérateur de proximité des chaînes de caractères pour chercher, sur un département particulier, quelles communes ont choisi le nommage du type mairie-nom-de-la-commune:

WITH v AS (SELECT domaine FROM domain_fr WHERE domaine LIKE 'mairie-%')
SELECT domaine,nom_commune FROM v JOIN communes ON (dept='06' AND domaine % nom_commune);

Résultats:

             domaine              |      nom_commune      
----------------------------------+-----------------------
 mairie-beaulieu-sur-mer.fr       | Beaulieu-sur-Mer
 mairie-la-turbie.fr              | La Turbie
 mairie-le-cannet.fr              | Le Cannet
 mairie-mandelieu-la-napoule.fr   | Mandelieu-la-Napoule
 mairie-roquefort-les-pins.fr     | Roquefort-les-Pins
 mairie-roquefort-les-pins.fr     | Roquefort-les-Pins
 mairie-roquesteron.fr            | Roquesteron
 mairie-saint-jean-cap-ferrat.fr  | Saint-Jean-Cap-Ferrat
 mairie-saint-martin-du-mont.fr   | Saint-Martin-du-Var
 mairie-saint-paul.fr             | Saint-Paul
 mairie-villefranche-sur-mer.fr   | Villefranche-sur-Mer
 mairie-villefranche-sur-saone.fr | Villefranche-sur-Mer
 mairie-villeneuve-loubet.fr      | Villeneuve-Loubet
(13 rows)

A vous de jouer pour d’autres requêtes!

par Daniel Vérité le mercredi 17 mai 2017 à 08h01

mardi 7 février 2017

Nicolas Gollet

Backends générant des fichiers temporaires

Dans certain cas il est peut être utile d'obtenir en temps réel la liste des backends générant des fichiers temporaire sur disque.

Lors de certaines opérations (trie, hachages...), PostgreSQL écrit dans des fichiers temporaires. Ces fichiers sont placés dans le sous-répertoire pgsql_tmp et sont nommés de la façon suivante :

pgsql_tmpXXXX.YXXXX correspond au PID du processus qui a créé le fichier et Y au numéro de fichier créé. Par exemple voici deux fichiers temporaires : pgsql_tmp90630.8 et pgsql_tmp90630.9.

Lorsque vous utilisez des tablespaces ces fichiers temporaires sont stockés dans l'emplacement disque définit par ceux-ci ceux qui rajoute une complexité supplémentaire pour récupéré ces fichiers.

Une fois le PID identifié, vous pouvez obtenir les informations depuis la vue pg_stat_activity afin d'obtenir les informations sur le backend générant des fichiers temporaires :

select * from pg_stat_activity where pid = <PID>;

Afin de simplifier la récupération de ces informations vous pouvez obtenir ces informations de façon automatique en utilisant la requête SQL ci-dessous :

Cette requête adaptée du projet pgstats permet d'obtenir l'ensemble des requêtes en cours d’exécution générant des fichiers temporaires tout en prenant en compte les éventuelles tablespace.

Vous pouvez aussi utiliser le script bash psql_show_tempfiles.bash présent sur mon Gitub.

par Nicolas GOLLET le mardi 7 février 2017 à 15h44

mardi 31 janvier 2017

Cédric Villemain

pgDay 2017 à Paris: conférence PostgreSQL internationale

Un événement communautaire

Cette année encore 2ndQuadrant aide la communauté PostgreSQL en France en étant Partenaire du pgDay 2017 à Paris

Cette journée de conférences, en anglais exclusivement, est une opportunité unique d’en apprendre plus sur le fonctionnement et l’activité de PostgreSQL. Sécurité, benchmarks, supervision, roadmap pour la version 10, réplication, … de nombreux sujets, variés et d’actualité. A noter qu’il n’est pas nécessaire de savoir lire Shakespeare dans la langue pour comprendre ce qu’il va se dire, c’est donc également une bonne occasion de renforcer votre anglais technique!

2ndQuadrant tient à remercier Vik Fearing pour sa très forte implication dans l’organisation de cet événement communautaire appuyé par l’association Européenne.

pgDay 2017 à Paris, soutenu par 2ndQuadrant

Simon Riggs, committer PostgreSQL et CTO de 2ndQuadrant, viendra y présenter les dernières évolutions dans le domaine de la réplication: un travail qu’il a commencé avec la version 8.0 et qui nous permet aujourd’hui d’adresser des besoins très importants de réplication multi-master (BDR) ou de réplication logique.

Venez nombreux le 23 mars pour découvrir tout cela et rencontrer en personne les experts 2ndQuadrant.

S’enregistrer et venir au pgDay 2017

logo pgDay Paris 2017

Pour s’enregistrer, il est nécessaire d’avoir un compte communautaire, que cela ne vous arrête pas: il s’agit d’un compte simple à créer et qui vous permet aussi d’éditer le wiki de PostgreSQL! Laissez-vous guider depuis: http://www.pgday.paris/registration/

Les conférences ont lieu de 9h à 18h au 2bis rue Mercœur dans le 11ème, entre métro Charonne et Voltaire, facile à trouver: voir la carte Open Street Map

 

par Cédric Villemain le mardi 31 janvier 2017 à 13h26

samedi 10 décembre 2016

Guillaume Lelarge

Version 1.3 de mon livre : PostgreSQL - Architecture et notions avancées

Mon livre, PostgreSQL - Architecture et notions avancées, a été écrit pendant le développement de la version 9.5. Il est sorti en version finale un peu après la sortie de la version 9.5, donc en plein développement de la 9.6. À la sortie de la version beta de la 9.6, je me suis mis à travailler sur une mise à jour du livre, incluant des corrections suite à des commentaires qui m'étaient parvenus, ainsi que de nombreux ajouts pour traiter les nouveautés de la version 9.6. Le résultat final est disponible depuis hier. Comme il ne s'agit pas d'une nouvelle édition complète, il est disponible gratuitement en téléchargement aux personnes qui ont acheté une version électronique précédente. Ceux qui commanderaient maintenant la version papier aurait cette nouvelle version, intitulée 1.3.

Évidemment, nous allons continuer la mise à jour du livre pour qu'il ne perde pas en intérêt. Une (vraie) deuxième édition sera disponible en fin d'année prochaine, après la sortie de la version 10. Cette version promet de nombreuses nouveautés très intéressantes, comme tout dernièrement un partitionnement mieux intégré dans le cœur de PostgreSQL.

par Guillaume Lelarge le samedi 10 décembre 2016 à 15h36

jeudi 29 septembre 2016

Sébastien Lardière

PostgreSQL 9.6.0

La version 9.6.0 de PostgreSQL est publiée aujourd'hui. La fonctionnalité la plus notable de cette version majeure est la parallélisation des requêtes. De nombreuses autres fonctionnalités permettent de travailler sur des volumes de données toujours plus important. Quelques informations sur le site de Loxodata : http://www.loxodata.com/postgresql-9.6.0-publiee

Je reviendrais sur le détail des nouvelles fonctionnalités dans une série de billets à venir.

par Sébastien Lardière le jeudi 29 septembre 2016 à 14h16

mercredi 28 septembre 2016

Cédric Villemain

pgFincore 1.2, une extension PostgreSQL

pgFincore 1.2 est une extension PostgreSQL pour auditer et manipuler le cache de pages de données du système d’exploitation. L’extension a déjà une histoire de 7 ans d’utilisation, avec des évolutions correspondant aux besoins de production.

Télécharger ici la dernière version 1.2, compatible avec PostgreSQL 9.6.

Cache de données

Cache de données

Le cache de pages de données est une opération qui se réalise «naturellement», à plusieurs niveaux dans la gestion des données. L’objet est simple: une multitude de couches se superposent entre les données physiquement enregistrées sur disque et la restitution à l’utilisateur. Actuellement quasiment chaque couche de données possède une abstraction pour servir plus rapidement des ordres de lecture et d’écriture. Ainsi la majorité des disques durs proposent un cache en écriture, qui permet de retarder l’écriture physique, et un cache en lecture qui permet d’anticiper sur des prochaines demandes et servir des données plus rapidement. Un système équivalent existe dans les SAN, les cartes RAID, les système d’exploitation, les logiciels, etc.

PostgreSQL possède bien sûr son propre système de gestion pour les écritures et les lectures, les shared buffers, que l’on peut auditer avec l’extension pg_buffercache.

Il est possible d’auditer le cache du système d’exploitation avec des outils systèmes, et pgFincore porte cela dans PostgreSQL.

Read Ahead

read aheadLa plupart des systèmes d’exploitation optimisent les parcours de données en proposant une fenêtre de lecture en avance, cela permet de pré-charger des données dans le cache et ainsi les fournir plus rapidement aux applications. PostgreSQL contient plusieurs optimisations pour favoriser ce comportement au niveau système, et porte également une fonctionnalité similaire avec l’option effective_io_concurrency.

Une solution pour faciliter ces optimisations consiste à utiliser des appels systèmes POSIX_FADVISE. Là-aussi pgFincore porte cette solution dans PostgreSQL.

pgFincore 1.2

Cette extension permet donc:

  • d’obtenir des informations précises sur l’occupation d’une table ou d’un index (et quelques autres fichiers utilisés par PostgreSQL) dans le cache du système supportant POSIX (linux, BSD, …),
  • de manipuler ce cache: en faire une carte et la restaurer ultérieurement ou sur un autre serveur,
  • d’optimiser les parcours via les appels posix_fadvise.pgfincore looks at that

Obtenir pgFincore

Paquets Debian et Red Hat disponibles dans les distributions, et pour chaque version de PostgreSQL sur les dépôts Apt PGDG et RPM PGDG.

Et les sources sur le dépôt git pgfincore.

Besoin d’aide ?

En plus du support communautaire, vous pouvez contacter 2ndQuadrant.


Exemples d’utilisation

Installation

$ sudo apt-get install postgresql-9.6-pgfincore
$ psql -c 'CREATE EXTENSION pgfincore;'

Information système

# select * from pgsysconf_pretty();
 os_page_size | os_pages_free | os_total_pages 
--------------+---------------+----------------
 4096 bytes   | 314 MB        | 16 GB

Optimiser le parcours aléatoire (réduction de la fenêtre de read-ahead)

# select * from pgfadvise_random('pgbench_accounts_pkey');
          relpath | os_page_size | rel_os_pages | os_pages_free 
------------------+--------------+--------------+---------------
 base/16385/24980 | 4096         | 2            | 1853808

Optimiser le parcours séquentiel (augmentation de la fenêtre de read-ahead)

# select * from pgfadvise_sequential('pgbench_accounts');
 relpath          | os_page_size | rel_os_pages | os_pages_free 
------------------+--------------+--------------+---------------
 base/16385/25676 | 4096         | 3176         | 1829288

Audit du cache

# select * from pgfincore('pgbench_accounts');
      relpath       | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit 
--------------------+---------+--------------+--------------+-----------+-----------+---------------+---------
 base/11874/16447   |       0 |         4096 |       262144 |         3 |         1 |        408444 | 
 base/11874/16447.1 |       1 |         4096 |        65726 |         0 |         0 |        408444 | 

Charger une table en mémoire

# select * from pgfadvise_willneed('pgbench_accounts');
      relpath       | os_page_size | rel_os_pages | os_pages_free 
--------------------+--------------+--------------+---------------
 base/11874/16447   |         4096 |       262144 |         80650
 base/11874/16447.1 |         4096 |        65726 |         80650

Vider le cache d’une table

# select * from pgfadvise_dontneed('pgbench_accounts');
      relpath       | os_page_size | rel_os_pages | os_pages_free
--------------------+--------------+--------------+---------------
 base/11874/16447   |         4096 |       262144 |        342071
 base/11874/16447.1 |         4096 |        65726 |        408103

Restaurer des pages en cache

On utilise ici un paramètre de type bit-string représentant les pages à charger et décharger de la mémoire.

# select * 
  from pgfadvise_loader('pgbench_accounts', 0, true, true, 
                       B'101001'); -- Varbit décrivant les pages à manipuler
     relpath      | os_page_size | os_pages_free | pages_loaded | pages_unloaded 
------------------+--------------+---------------+--------------+----------------
 base/11874/16447 |         4096 |        408376 |            3 |              3

NOTE: pour la démo, seul 6 pages de données sont manipulées ci-dessus, 1 charge la page, 0 décharge la page.

par Cédric Villemain le mercredi 28 septembre 2016 à 07h52

vendredi 20 mai 2016

Guillaume Lelarge

Quelques nouvelles sur les traductions du manuel

J'ai passé beaucoup de temps ces derniers temps sur la traduction du manuel de PostgreSQL.

  • mise à jour pour les dernières versions mineures
  • corrections permettant de générer les PDF déjà disponibles (9.1, 9.2, 9.3 et 9.4) mais aussi le PDF de la 9.5
  • merge pour la traduction de la 9.6

Elles sont toutes disponibles sur le site docs.postgresql.fr.

De ce fait, la traduction du manuel de la 9.6 peut commencer. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Translation-9.6

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

par Guillaume Lelarge le vendredi 20 mai 2016 à 20h35

dimanche 13 mars 2016

Guillaume Lelarge

Fin de la traduction du manuel de la 9.5

Beaucoup de retard pour cette fois, mais malgré tout, on a fini la traduction du manuel 9.5 de PostgreSQL. Évidemment, tous les manuels ont aussi été mis à jour avec les dernières versions mineures.

N'hésitez pas à me remonter tout problème sur la traduction.

De même, j'ai pratiquement terminé la traduction des applications. Elle devrait être disponible pour la version 9.5.2 (pas de date encore connue).

par Guillaume Lelarge le dimanche 13 mars 2016 à 10h41

mardi 8 mars 2016

Sébastien Lardière

Dates à retenir

Trois dates à retenir autour de PostgreSQL :

  • 17 mars, à Nantes, un premier meetup, dans lequel j'évoquerai les nouveautés de PostgreSQL 9.5.
  • 31 mars, à Paris, où j'essayerai de remonter le fil du temps de bases de données.
  • 31 mai, à Lille, où je plongerai dans les structures du stockage de PostgreSQL.

Ces trois dates sont l'occasion pour tous de se rencontrer, au-delà du formalisme des présentations, autour de PostgreSQL, pour des sujets plus informels. N'hésitez donc pas à aller à la rencontre des orateurs présents.

Merci d'avance aux organisateurs pour ces moments précieux, et à Loxodata de me permettre d'y participer.

PS: Je ne peux m'empêcher de remarquer qu'il n'y a aucune oratrice lors de ces 3 événements, je suis presque certain qu'il n'y a pas eu une seule proposition de la part d'une oratrice. Je ne peux que le déplorer …

par Sébastien Lardière le mardi 8 mars 2016 à 08h30

samedi 6 février 2016

Guillaume Lelarge

Début de la traduction du manuel 9.5

J'ai enfin fini le merge du manuel de la version 9.5. Très peu de temps avant la 9.5, le peu de temps que j'avais étant consacré à mon livre. Mais là, c'est bon, on peut bosser. D'ailleurs, Flavie a déjà commencé et a traduit un paquet de nouveaux fichiers. Mais il reste du boulot. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Translation-9.5

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

par Guillaume Lelarge le samedi 6 février 2016 à 12h18

lundi 1 février 2016

Guillaume Lelarge

Parution de mon livre : "PostgreSQL, architecture et notions avancées"

Après pratiquement deux ans de travail, mon livre est enfin paru. Pour être franc, c'est assez étonnant de l'avoir entre les mains : un vrai livre, avec une vraie reliure et une vraie couverture, écrit par soi. C'est à la fois beaucoup de fierté et pas mal de questionnements sur la façon dont il va être reçu.

Ceci étant dit, sans savoir si le livre sera un succès en soi, c'est déjà pour moi un succès personnel. Le challenge était de pouvoir écrire un livre de 300 pages sur PostgreSQL, le livre que j'aurais aimé avoir entre les mains quand j'ai commencé à utiliser ce SGBD il y a maintenant plus de 15 ans sous l'impulsion de mon ancien patron.

Le résultat est à la hauteur de mes espérances et les premiers retours sont très positifs. Ce livre apporte beaucoup d'explications sur le fonctionnement et le comportement de PostgreSQL qui, de ce fait, n'est plus cette espèce de boîte noire à exécuter des requêtes. La critique rédigée par Jean-Michel Armand dans le GNU/Linux Magazine France numéro 190 est vraiment très intéressante. Je suis d'accord avec son auteur sur le fait que le début est assez ardu : on plonge directement dans la technique, sans trop montrer comment c'est utilisé derrière, en production. Cette partie-là n'est abordée qu'après. C'est une question que je m'étais posée lors de la rédaction, mais cette question est l'éternel problème de l'oeuf et de la poule ... Il faut commencer par quelque chose : soit on explique la base technique (ce qui est un peu rude), puis on finit par montrer l'application de cette base, soit on fait l'inverse. Il n'y a certainement pas une solution meilleure que l'autre. Le choix que j'avais fait me semble toujours le bon, même maintenant. Mais en effet, on peut avoir deux façons de lire le livre : en commençant par le début ou en allant directement dans les chapitres thématiques.

Je suis déjà prêt à reprendre le travail pour proposer une deuxième édition encore meilleure. Cette nouvelle édition pourrait se baser sur la prochaine version majeure de PostgreSQL, actuellement numérotée 9.6, qui comprend déjà des nouveautés très excitantes. Mais cette édition ne sera réellement intéressante qu'avec la prise en compte du retour des lecteurs de la première édition, pour corriger et améliorer ce qui doit l'être. N'hésitez donc pas à m'envoyer tout commentaire sur le livre, ce sera très apprécié.

par Guillaume Lelarge le lundi 1 février 2016 à 17h57

mercredi 13 janvier 2016

Sébastien Lardière

Version 9.5 de PostgreSQL - 3

Une nouvelle version majeure de PostgreSQL est disponible depuis le 7 janvier. Chacune des versions de PostgreSQL ajoute son lot de fonctionnalités, à la fois pour le développeur et l'administrateur. Cette version apporte de nombreuses fonctionnalités visant à améliorer les performances lors du requêtage de gros volumes de données.

Cette présentation en trois billets introduit trois types de fonctionnalités :

Ce dernier billet de la série liste quelques paramètres de configuration qui font leur apparition dans cette nouvelle version.

Suivi de l'horodatage des COMMIT

Le paramètre track_commit_timestamp permet de marquer dans les journaux de transactions ("WAL") chaque validation ("COMMIT") avec la date et l'heure du serveur. Ce paramètre est un booléen, et l'activation nécéssite un redémarrage de l'instance.

Il est donc possible de connaître la date et heure  de la validation des transactions. Une des application, et ce pourquoi cette fonctionnalité existe, est la résolution des conflits dans les ensembles d'instance multi-maître comme BDR ("Bi-Directionnal Replication"). À l'heure actuelle, BDR ne fonctionne pas avec PostgreSQL 9.5, sauf à y apporter quelques modifications, mais devrait n'être qu'un plug-in de la version 9.6.

Nom de l'instance

Le paramètre cluster_name permet d'identifier une instance selon, un nom. Il s'agit d'une demande récurrente des administrateurs, qui éprouvaient des difficultés à identifier l'instance par son numéro de port TCP ou le répertoire des données.

Le nom ainsi donné est utilisé dans l'identification des processus (commande système "ps"). L'activation et le changement nécéssite un redémarrage de l'instance.

Gestion du recyclage des WAL et des CHECKPOINTs

Ce changement dans la configuration a un impact sur la gestion des performances. Jusqu'à la version 9.4, le paramètre checkpoint_segment contrôle le moment du déclenchement d'un CHECKPOINT, en indiquant le nombre de segments de WALs. Ce paramètre disparaît dans la version 9.5 et est remplacé par deux paramètres.

Le paramètre min_wal_size désigne un seuil minimal, en volume de données, au delà duquel une opération de CHECKPOINT peut-être lancée.

Le paramètre max_wal_size désigne la limite maximale autorisée, un volume de données, déclenchant une opération CHECKPOINT. À noter que le volume de WALs peut être plus important que cette limite, en fonction de réglages telle que wal_keep_segments et de l'échec de la commande archive_command.

Ceci permet d'exprimer simplement une plage de valeur peremttant d'absorber un pic de transactions, tout en limitant de volume de données correspondant aux journaux de transactions.

Contrôle de restauration

Dans le fichier de configuration recovery.conf, un nouveau paramètre permet de contrôler la fin de la restauration, remplaçant le paramètre pause_at_recovery_target. Le paramètre recovery_target_action peut prend trois valeurs : pause, promote ou shutdown, selon le souhait de l'administrateur.

Mise à jour des index GIN

Le paramètre gin_pending_list_limit contrôle la taille de la liste d'attente des index GIN.

Cette liste d'attente est utilisée pour améliorer les performances de mise à jour des index GIN ("fastupdate"). La liste est vidée, et l'index mis à jour, lors d'une opération de VACUUM ou lorsque la limite donnée par le paramètre est atteinte.

Ce paramètre peut-être utilisé comme paramètre de stockage pour chaque index. La valeur par défaut est 4MB.

RESET SYSTEM

Cet ordre SQL permet de retirer un paramètre du fichier postgresql.auto.conf :

ALTER SYSTEM RESET wal_level;

par Sébastien Lardière le mercredi 13 janvier 2016 à 15h12

mardi 12 janvier 2016

Sébastien Lardière

Version 9.5 de PostgreSQL - 2

Une nouvelle version majeure de PostgreSQL est disponible depuis le 7 janvier. Chacune des versions de PostgreSQL ajoute son lot de fonctionnalités, à la fois pour le développeur et l'administrateur. Cette version apporte de nombreuses fonctionnalités visant à améliorer les performances lors du requêtage de gros volumes de données.

Cette présentation en trois billets introduit trois types de fonctionnalités :

Ce deuxième billet évoque donc les nouvelles méthodes internes du moteur, c'est-à-dire de nouveaux outils dont PostgreSQL dispose pour traiter les données.

Index BRIN

Il s'agit d'un nouveau type d'index, créé pour résoudre des problèmes d'accès à de très gros volumes de données ; le cas d'usage est une table de log, dans laquelle les données sont inserées mais non modifiée par la suite.

Un index BTREE, auquel nous sommes habitué, contient toutes les valeurs qu'on lui confie. Ce nouveau type, BRIN, pour Block Range INdex, ne contient que les valeurs limites de plages de blocs de la table.

Le parcours d'un index BRIN permet donc un accès rapide aux données de la table, après un parcours d'index très court, et donc très rapide. Mais l'interêt n'est visible que pour de très gros volume de données.

Il s'agit d'une des étapes de l'adoption de PostgreSQL pour les très gros volumes, d'un ordre de grandeur du péta-octet.

Foreign Table Inheritance

Cette nouvelle fonctionnalité autorise l'héritage de table, fonctionnalité déjà connue, avec les tables étrangères. Ce qui permet par exemple d'agreger plusieurs tables étrangères, ayant des origines différentes, avec une seule table mère.

Dans le cadre d'architecture complexe de sharding via SQL/MED, cette fonctionnalité simplifie amplement le déploiement des modèles et l'écriture des requêtes.

Optimisation de tris (Abbreviated Keys)

Comme bien d'autres évolutions de PostgreSQL, il s'agit içi de l'utilisation d'une fonctionnalité développée précedemment dans PostgreSQL : la possibilité d'utiliser des méthodes alternatives pour un tri de données.

Un tri est une opération très courante dans les bases de données, entre autre pour créer des index. Donc, un gain de temps sur les tris est trés avantageux.

Cette fonctionnalité utilise une version abrégée de la donnée pour réaliser le tri, soit les 8 premiers octets d'une version binaire de la donnée. Lorsque une comparaison avec cette méthode ne permet pas le tri, alors la méthode de comparaison habituelle est utilisée. Cette méthode permet de diviser par trois le temps de tri.

Ces optimisations sont valables pour les types de données text et numeric.

Améliorations du verrouillage

Lors d'opération de lecture, un verrou partagé est acquis. L'amélioration apportée dans cette version consiste à diminuer l'impact de l'acquisition de ce verrou partagé. Auparavant, l'acquisition d'un "spinlock" exclusif etait nécessaire, ce qui n'est plus le cas actuellement.

Cette amélioration de opérations de lecture ('SELECT') permet un plus grand nombre de sessions concurrentes.

Réduction de l'utilisation de la mémoire par backend

Les références à la mémoire partagée ont été optimisé afin de consommer moins de mémoire par processus d'arrière-plan ('backend'). Plus la mémoire partagée est importante, plus ce problème était présent, cette optimisation est donc pertinente pour les installations importantes, avec une mémoire partagée élevée.

pg_rewind

Il s'agit en fait d'un outil, utilisé dans le cadre d'un ensemble d'instance PostgreSQL, à des fins de Haute Disponibilité. Lorsque l'instance "primary" n'est plus disponible, on promeut une instance "standby" en "primary". On parle alors de "failover". Puis, lorsque l'ancienne instance "primary" est à nouveau disponible, on peut souhaiter l'intégrer en tant que "standby" dans l'ensemble des instances.

Le problème est que l'ancien "primary" peut avoir pris de l'avance sur le nouveau, et donc, qu'il ne puisse pas intégrer la "timeline" de ce nouveau master.

L'outil "pg_rewind" intervient à ce moment : utilisé sur l'ancien "primary", il se connecte sur le nouveau "primary", et revient dans la "timeline" jusqu'à un point commun, afin démarrer la réplication habituelle.

Il est donc évident qu'on va perdre des transactions à ce moment-là. De nombreux utilisateurs préferereont conserver l'ancien "primary" afin d'analyser les transactions potentiellement récupérables.

Cet outil est donc utilisable par les administrateurs qui vont privilégier la haute disponibilité du service à la haute disponibilité des données.

VACUUM en parallèle

L'utilitaire "vacuumdb" peut désormais lancer des tâches de maintenance en parallèle. lorsqu'un l'utilise sur des bases de données. L'utilisation du commutateur -j permet cela, et les tables les plus volumineuses sont traitées en premier. Ceci ne concerne en rien l'autovacuum, ni même la commande SQL VACUUM.

Le dernier article évoquera les nouvelles directives du fichier de configuration.

par Sébastien Lardière le mardi 12 janvier 2016 à 10h45

vendredi 8 janvier 2016

Sébastien Lardière

Version 9.5 de PostgreSQL

Une nouvelle version majeure de PostgreSQL est disponible depuis le 7 janvier. Chacune des versions de PostgreSQL ajoute son lot de fonctionnalités, à la fois pour le développeur et l'administrateur. Cette version apporte de nombreuses fonctionnalités visant à améliorer les performances lors du requêtage de gros volumes de données.

Cette présentation en trois billets introduit trois types de fonctionnalités :

Ce premier billet revient donc sur les ajouts au langage SQL de la version 9.5 de PostgreSQL.

Ces ajouts portent sur de nombreux champs de fonctionnalités, de la sécurité aux gestions de performances en passant par la gestion des transactions.

UPSERT

Ce mot clé désigne en réalité la possibilité d'intercepter une erreur de clé primaire sur un ordre INSERT, et de faire un UPDATE à la place.

Ce principe était déjà faisable, côté client ou avec une procédure stockée, mais l'intégration dans le langage SQL permet une gestion plus simple et plus efficace.

L'exemple suivant montre l'insertion dans une table d'enregistrement de statistiques.

Lorsque la clé primaire id existe déjà ("ON CONFLICT (id)"), alors une ordre UPDATE est exécuté en ajoutant la valeur insérée à la valeur existante :

INSERT INTO user_stats (id, connect)
VALUES (12345,1),(87654,1)
ON CONFLICT (id)
DO UPDATE SET connect = user_stats.connect + EXCLUDED.connect;

La fonctionnalité est explicitement utilisée par le développeur, aisément compréhensible, l'adoption dans les projets est donc tout à fait bénéfique.

Row-Level Security Policies

Cette nouvelle fonctionnalité permet de contrôler l'accès aux données, non pas en fonction des privilèges apposés aux tables et aux colonnes, mais aux tuples dans les tables. Dans la table de l'exemple précédent, lorsque l'administrateur souhaite qu'un utilisateur de puisse voir que les statistiques le concernant, il est possible d'appliquer une politique de sécurité, associant les tuples enregistrés à l'utilisateur connecté :

CREATE POLICY policy_user ON user_stats
   FOR ALL
   TO PUBLIC
   USING (id = usertoid(current_user));

La fonction usertoid() traduit un nom d'utilisateur en id. Pour des questions de performances, il est conseillé de la rendre IMMUTABLE. Une fois la politique suivante appliquée :

ALTER TABLE user_stats ENABLE ROW LEVEL SECURITY;

L'utilisateur ne peut alors voir que les tuples le concernant dans la table user_stats.

GROUPING SETS, CUBE and ROLLUP

Ces fonctions étendent les capacités de regroupement des tuples, fonctionnalité déjà connue par l'utilisation de la clause GROUP BY.

Avec la clause GROUP BY, il est possible de regrouper sur une seule dimension, même plusieurs attributs sont utilisés. Lorsque plusieurs regroupements sont nécessaires, il est alors nécessaire d'écrire plusieurs requêtes, quitte à les concaténer avec UNION ALL.

Ces nouvelles clauses de regroupements permettent d'exprimer plusieurs regroupements dans une même requête. L'exemple suivant requête la base de données employees afin de compter les employées par titre de poste et par genre :

select gender, title, count(*)
    from employees join titles using (emp_no)
  group by
    grouping sets ( ( gender, title ), (title), (gender), () ) ;

Le résultat montre le comptage des quatre regroupements exprimés.

Les trois fonctions ont chacune leur rôle :

  • GROUPING SETS ( (attr1, attr2), (attr2), (attr3, attr1) ) : regroupe les résultats selon toutes les combinaisons d'attributs exprimées.
  • CUBE( attr1, attr2, attr3 ) : regroupe selon toutes les combinaisons des attributs utilisés
  • ROLLUP( attr1, attr2, attr3 ) : regroupe selon toutes les combinaisons séquentielles (de gauche à droite). Très proche de CUBE(), cette fonction ignore les combinaisons des attributs que ne se suivent pas dans la séquence indiquée. Contrairement à CUBE(), il n'y aura pas la combinaison (attr1, attr3) dans le résultat.

Ces fonctions sont très utiles dans le cadre de bases de données analytiques (OLAP)

TABLESAMPLE

Cette fonctionnalité permet d'extraire des échantillons de données d'une table, afin de calculer une approximation d'un résultat, beaucoup plus rapidement que le calcul réel. Par exemple, le calcul du salaire moyen des salaires de la base de données employes peut prendre plusieurs secondes, alors qu'un calcul approximatif est beaucoup plus rapide :

employees=$ 
select avg(salary) from salaries ;
-[ RECORD 1 ]-----------
avg | 63810.744836143706
Time: 318.682 ms
employees=$ select avg(salary) from salaries TABLESAMPLE system ( 0.1 );
-[ RECORD 1 ]-----------
avg | 62681.119426751592
Time: 2.552 ms
employees=$ select avg(salary) from salaries TABLESAMPLE system ( 0.01 );
-[ RECORD 1 ]-----------
avg | 62807.193205944798
Time: 0.584 ms

Dans ce cas, l'algorithme de sélection repose sur le système d'exploitation. Un algorithme plus réaliste, BERNOULLI, est utilisable, mais plus coûteux.

IMPORT FOREIGN SCHEMA

Cette nouvelle instruction permet d'importer automatiquement des tables étrangères, en utilisant les fonctionnalités SQL/MED de PostgreSQL. Auparavant, il était possible de créer une table étrangère en précisant les attributs et les types de données.

Cette fonctionnalité déduit automatiquement le modèle de la table distante, et crée automatiquement la « table étrangère » avec les attributs et les types de données ainsi trouvés. Par défaut, toutes les tables d'un espace de nom sont importées, mais il est possible d'exclure des tables, ou, au contraire, de lister les tables à inclure.

Il est cependant nécessaire que le pilote SQL/MED supporte la fonctionnalité, ce qui n'est le cas que pour le pilote postgres_fdw, fourni avec PostgreSQL, pour le moment.

Opérateurs et Fonctions JSONB

Le type de données jsonb est une des grandes nouveautés de la version majeure précédente, la 9.4, ce qui permet à PostgreSQL de figurer parmi les bases de données orientées "document", la puissance du SQL en plus.

Cette version 9.5 se voit enrichie de fonctions permettant de modifier ce type de données directement, sans devoir passer par une extraction des données :

  • l'opérateur || permet de concaténer des données jsonb
  • l'opérateur - permet de supprimer une clé d'une donnée jsonb
  • la fonction jsonb_set permet de mettre à jour la valeur d'une clé d'une donnée jsonb
  • la fonction jsonb_pretty permet de créer un rendu texte lisible depuis une donnée jsonb.

ALTER TABLE ... SET LOGGED / UNLOGGED

La version 9.1 de PostgreSQL apportait la possibilité de ne pas enregistrer une table dans les WAL (journaux de transactions), ce qui accélérait grandement les imports de données en masse. Mais les données n'étaient pas sauvegardées ni repliquées, il était donc nécessaire de copier la table ainsi créée dans une table « régulière » afin d'assurer la durabilité des données.

Il est maintenant possible de modifier cette propriété avec une simple commande, ce qui simplifie les opérations de la part du DBA :

ALTER TABLE user_stats SET LOGGED ;

SKIP LOCKED

Cette clause modifie le comportement de la clause FOR UPDATE d'un ordre SELECT.

Lorsqu'une ligne est modifiée dans une transaction concurrente, l'utilisation d'un SELECT FOR UPDATE sur cette même ligne est bloquante.

L'ajout de la clause SKIP LOCKED ignore simplement la ligne verrouillée, et ne prend alors en compte que les lignes non verrouillées.

CREATE INDEX IF NOT EXISTS

Une nouvelle clause enrichi l'ordre CREATE INDEX. Lorsqu'un index exsiste déjà, cette clause permet de ne pas provoquer d'erreur, afin de ne pas interrompre l'exécution d'une transaction.

Cette clause n'est utilisable que indiquant explicitement le nom, et non pas lorsque le nom est omis et implicitement généré par le moteur. De plus, cela ne protège pas de la création d'un index indentique mais avec un nom différent. Exemple :

employees=# create unique index departments_dept_name_idx ON departments (dept_name) ; 
ERROR:  relation "departments_dept_name_idx" already exists
employees=# create unique index IF NOT EXISTS departments_dept_name_idx ON departments (dept_name) ; 
NOTICE:  relation "departments_dept_name_idx" already exists, skipping
CREATE INDEX
employees=# create unique index IF NOT EXISTS departments_dept_name_idx_fake ON departments (dept_name) ; 
CREATE INDEX
employees=# drop index departments_dept_name_idx_fake;
DROP INDEX

Le prochain article présentera les nouveautés internes au moteur PostgreSQL

par Sébastien Lardière le vendredi 8 janvier 2016 à 10h49

jeudi 19 novembre 2015

Guillaume Lelarge

Version finale du livre

Elle n'est pas encore sortie. Elle est pratiquement terminée, on attend d'avoir le livre en version imprimée.

Néanmoins, je peux déjà dire les nouveautés par rapport à la beta 0.4 :

  • Global
    • mise à jour du texte pour la 9.5
    • ajout du chapitre sur la sécurité
    • ajout du chapitre sur la planification
    • mise à jour des exemples avec PostgreSQL 9.5 beta 1
  • Fichiers
    • Ajout d'un schéma sur les relations entre tables, FSM et VM
    • Ajout de la description des répertoires pg_dynshmem et pg_logical
  • Contenu des fichiers
    • Ajout d'informations sur le stockage des données, colonne par colonne
    • Ajout d'un schéma sur la structure logique et physique d'un index B-tree
    • Ajout de la description des index GIN
    • Ajout de la description des index GiST
    • Ajout de la description des index SP-GiST
  • Architecture mémoire
    • calcul du work_mem pour un tri
    • calcul du maintenance_work_mem pour un VACUUM
  • Gestion des transactions
    • Gestion des verrous et des accès concurrents
  • Maintenance
    • Description de la sortie d'un VACUUM VERBOSE

J'avoue que j'ai hâte d'avoir la version finale entre mes mains :-) Bah, oui, c'est quand même 1 an et demi de boulot acharné !

par Guillaume Lelarge le jeudi 19 novembre 2015 à 22h36

mardi 22 septembre 2015

Guillaume Lelarge

Version beta 0.4 du livre

La dernière beta datait de mi-mai. Beaucoup de choses se sont passées pendant les 4 mois qui ont suivi. Quatre nouveaux chapitres sont mis à disposition :

  • Sauvegarde
  • Réplication
  • Statistiques
  • Maintenance

Mais ce n'est évidemment pas tout. Dans les nouveautés importantes, notons :

  • Chapitres Fichiers, Processus et Mémoire
    • Ajout des schémas disques/processus/mémoire
  • Chapitre Contenu physique des fichiers
    • Déplacement des informations sur le contenu des journaux de transactions dans ce chapitre
    • Ajout de la description du contenu d'un index B-tree
    • Ajout de la description du contenu d'un index Hash
    • Ajout de la description du contenu d'un index BRIN
    • Restructuration du chapitre dans son ensemble
  • Chapitre Architecture des processus
    • Ajout de sous-sections dans la description des processus postmaster et startup
    • Ajout d'un exemple sur la mort inattendue d'un processus du serveur PostgreSQL
  • Chapitre Architecture mémoire
    • Ajout de plus de détails sur la mémoire cache (shared_buffers)
  • Chapitre Gestion des transactions
    • Ajout d'informations sur le CLOG, le FrozenXid et les Hint Bits
  • Chapitre Gestion des objets
    • Ajout d'une section sur les options spécifiques des vues et fonctions pour la sécurité
    • Ajout d'un paragraphe sur le pseudo-type serial
  • Divers
    • Mise à jour des exemples avec PostgreSQL 9.4.4

Bref, c'est par ici.

Quant à la prochaine version ? cela devrait être la version finale. Elle comportera le chapitre Sécurité (déjà écrit, en cours de relecture) et le chapitre sur le planificateur de requêtes (en cours d'écriture). Elle devrait aussi disposer d'une mise à jour complète concernant la version 9.5 (dont la beta devrait sortir début octobre).

Bonne lecture et toujours intéressé pour savoir ce que vous en pensez (via la forum mis en place par l'éditrice ou via mon adresse email).

par Guillaume Lelarge le mardi 22 septembre 2015 à 21h59

mardi 14 juillet 2015

Guillaume Lelarge

Comment quantifier le maintenance_work_mem

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

Je suis en train d'écrire le chapitre sur la maintenance. Parmi les opérations de maintenance se trouve l'ordre VACUUM. Beaucoup de choses ont déjà été écrites dans le livre sur le VACUUM mais j'avais bizarrement oublié une chose. Une bonne configuration du paramètre maintenance_work_mem permet d'avoir un VACUUM performant. Mais comment peut-on savoir que la valeur du maintenance_work_mem est suffisante ?

J'ai donc creusé hier soir dans les sources de PostgreSQL à la recherche de ce qui est stocké dans cette mémoire. Tout se trouve dans src/backend/commands/vacuumlazy.c, principalement dans la fonction lazy_space_alloc(). En gros, PostgreSQL y place un tableau de la structure ItemPointerData. Cette structure prend six octets. Donc une estimation (très grosse) serait de dire qu'on peut stocker maintenance_work_mem/6 positions d'enregistrements morts dans cette mémoire. Un patch rapide (voir le fichier joint) nous prouve cette théorie :

Nous plaçons le paramètre client_min_messages au niveau log pour voir les traces ajoutées par le patch :

postgres=# SET client_min_messages TO log;
SET

Nous créons la table et désactivons l'autovacuum sur cette table pour le gérer nous-même :

postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1(id INTEGER PRIMARY KEY);
CREATE TABLE
postgres=# ALTER TABLE t1 SET (autovacuum_enabled = OFF);
ALTER TABLE

Nous insérons un million de lignes, puis en supprimons 900000 :

postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999

Nous configurons maintenance_work_mem à 1 Mo (en fait, suffisamment petit pour voir que le VACUUM a besoin de plusieurs passes dû au manque de mémoire) :

postgres=# SET maintenance_work_mem TO '1MB';
SET
postgres=# VACUUM t1;
LOG:  patch - vac_work_mem: 1024
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 174762
LOG:  patch - step 1
LOG:  patch - step 2
LOG:  patch - step 3
LOG:  patch - step 4
LOG:  patch - step 5
LOG:  patch - step 6
VACUUM

La fonction de calcul de la taille mémoire a bien noté le maintenance_work_mem à 1 Mo (1024 Ko). La taille de la structure est bien de 6 octets. Il est donc possible de stocker 1024*1024/6 enregistrements, soit 174762 enregistrements. Ayant supprimé 900000 enregistrements, il me faut 6 passes (l'arrondi supérieur de l'opération 900000/174762) pour traiter la table entière. Pas efficace.

Essayons dans les mêmes conditions mais avec un maintenance_work_mem trois fois plus gros :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# SET maintenance_work_mem TO '3MB';
SET
postgres=# VACUUM t1;
LOG:  patch - vac_work_mem: 3072
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 524288
LOG:  patch - step 1
LOG:  patch - step 2
VACUUM

Nous ne faisons plus que deux passes (tout d'abord 524288 enregistrements, puis 375711), c'est plus efficace mais non optimal.

Essayons maintenant avec le maintenance_work_mem de base (64 Mo) :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# RESET maintenance_work_mem;
RESET
postgres=# VACUUM VERBOSE t1;
INFO:  vacuuming "public.t1"
LOG:  patch - vac_work_mem: 65536
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 1287675
LOG:  patch - step 1
VACUUM

Seule une passe est réalisée. Il est à noter que la mémoire prise ne correspond pas au 64 Mo. 64 Mo me permet de stocker 11 millions d'enregistrements morts, mais je n'ai dans la table que 1000000 d'enregistrements dont 90% est mort. Autrement dit, j'ai besoin de beaucoup moins de mémoire. C'est bien le cas ici où, au lieu de 11 millions d'enregistrements, on peut en stocker 1287675 (soit un peu plus de 7 Mo).

De tout ça, comment puis-je savoir si mon maintenance_work_mem est bien configuré ? Il faut se baser sur le nombre d'enregistrements (morts) contenus dans les tables. Ça correspond à cette requête pour les tables de ma base de connexion :

SELECT pg_size_pretty(max(n_dead_tup*6)) AS custom_maintenance_work_mem
FROM pg_stat_all_tables;

Dans l'exemple précédent, cela me donnerait ceci :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# SELECT pg_size_pretty(max(n_dead_tup*6)) AS custom_maintenance_work_mem,
           current_setting('maintenance_work_mem') AS current_maintenance_work_mem
          FROM pg_stat_all_tables;

 custom_maintenance_work_mem | current_maintenance_work_mem 
-----------------------------+------------------------------
 5273 kB                     | 64MB
(1 row)

Il me faut au minimum 5,2 Mo. Je suis donc tranquille.

Évidemment, le nombre d'enregistrements morts évolue dans le temps et il est tout à fait possible que la quantité de mémoire nécessaire soit bien plus importante. On peut se baser sur le nombre d'enregistrements total pour avoir le pire des cas comme ici :

b1=# SELECT pg_size_pretty(max((n_live_tup+n_dead_tup)*6)) AS custom_maintenance_work_mem,
     current_setting('maintenance_work_mem') AS current_maintenance_work_mem
     FROM pg_stat_all_tables;

 custom_maintenance_work_mem | current_maintenance_work_mem 
-----------------------------+------------------------------
 472 MB                      | 512MB
(1 row)

Ce qui révèle donc une configuration adéquate pour cet utilisateur.

par Guillaume Lelarge le mardi 14 juillet 2015 à 07h54