Nouveautés autour des requêtes hiérarchiques avec PostgreSQL 14 et versions supérieures
Quelques rappels sur le TOAST et présentation d’un changement apparu avec PostgreSQL 11.
Vous êtes-vous déjà posé la question sur comment Postgres fait pour stocker des lignes dépassant la taille d’un bloc? Pour rappel, la taille par défaut d’un bloc est de 8Ko.
Postgres utilise un mécanisme appelé TOAST pour The Oversized-Attribute Storage Technique.
Lorsqu’un enregistrement devient trop gros pour être stocké dans un bloc, le moteur va le stocker “à part”, dans une table de toast. L’enregistrement sera découpé en chunks, ainsi la table principale (appelée heap) contiendra un pointeur (chunk_id) pointant vers le bon chunk dans la table de toast.
Ce chunk sera stocké sur plusieurs lignes, pour un chunk_id on peut avoir plusieurs lignes dans cette table de toast. Ainsi, cette table de toast est composée de 3 colonnes:
La réalité est un peu plus complexe, en vrai le moteur va tenter d’éviter de stocker la donnée dans la table toast.
Si la ligne dépasse TOAST_TUPLE_THRESHOLD (2Ko), il va tenter de compresser les colonnes pour essayer de faire rentrer la ligne dans le bloc.
Plus précisément, il faut que la taille soit inférieure à TOAST_TUPLE_TARGET (2Ko par défaut, on va en reparler).
Si on a de la chance, la ligne compressée rentre dans la heap. Sinon, il va tenter de compresser les colonnes, de la plus grande à la plus petite et les stocker dans la partie toast jusqu’à ce que les colonnes restantes rentrent dans une ligne de la heap. 1
A noter également que si le gain en compression est trop faible, il considère qu’il est inutile de dépenser de la ressource de calcul à tenter de compresser. Il stocke donc la donnée sans compression. 2
Avez-vous déjà prêté attention à la colonne “Storage” lorsque vous affichez les caractéristiques d’une table à l’aide de la méta commande \d+ table ?
stackoverflow=# \d+ posts
Table "public.posts"
Column | Type | Collation | Nullable | Default | Storage |
---------------+---------+-----------+----------+---------+----------+
id | integer | | not null | | plain |
posttypeid | integer | | not null | | plain |
score | integer | | | | plain |
viewcount | integer | | | | plain |
body | text | | | | extended |
Dans cet exemple, la colonne prend comme valeur plain ou extended. En réalité, il existe 4 valeurs possibles selon le type de donnée :
Au premier abord, on peut penser que l’intérêt est surtout sur la possibilité de stocker des lignes dépassant la taille d’un bloc et de compresser la donnée pour gagner de l’espace disque.
Il y a un autre intérêt : lors d’une mise à jour d’une ligne, si les colonnes “toastées” ne sont pas modifiées, le moteur n’a pas besoin de modifier la table toast. On va ainsi éviter de devoir décompresser et recompresser le toast et écrire tout ça dans des journaux de transaction.
Nous allons voir qu’un autre avantage est que le moteur peut éviter de lire le toast si ce n’est pas nécessaire.
Pour étudier ça, on va utiliser le type JSONB. De manière générale, je déconseille l’usage de ce type :
Cependant, il y a quelques exceptions où le JSON peut être utile :
NULL.Par exemple, pour stocker des caractéristiques de produit où une version normalisée entrainerait l’usage de beaucoup de colonnes dont la plupart seraient à NULL.
Imaginons que vous stockez des produits, une télévision aurait des caractéristiques spécifiques (type d’écran, taille etc). Une machine à laver aurait aussi d’autre caractéristiques spécifiques (vitesse essorage, poids accepté…).
On pourrait ainsi envisager d’avoir des colonnes “normales” comprenant le modèle, son prix, sa référence etc, et une colonne contenant toutes les caractéristiques. On accèderait à la ligne via la référence et ainsi on récupèrerait toutes les caractéristiques du produit stockées dans le json.
Je vais réutiliser la table des posts de Stackoverflow en déplaçant quelques colonnes dans une colonne de type jsonb (colonne jsonfield dans cet exemple):
\d posts
Unlogged table "public.posts"
Column | Type | Collation | Nullable | Default
-----------------------+-----------------------------+-----------+----------+---------
id | integer | | not null |
posttypeid | integer | | not null |
acceptedanswerid | integer | | |
parentid | integer | | |
creationdate | timestamp without time zone | | not null |
score | integer | | |
viewcount | integer | | |
body | text | | |
owneruserid | integer | | |
lasteditoruserid | integer | | |
lasteditordisplayname | text | | |
lasteditdate | timestamp without time zone | | |
lastactivitydate | timestamp without time zone | | |
title | text | | |
tags | text | | |
answercount | integer | | |
commentcount | integer | | |
favoritecount | integer | | |
closeddate | timestamp without time zone | | |
communityowneddate | timestamp without time zone | | |
jsonfield | jsonb | | |
Voici une requête toute simple d’agrégation :
SELECT
avg(viewcount),
avg(answercount),
avg(commentcount),
avg(favoritecount)
FROM posts;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10265135.77..10265135.78 rows=1 width=128) (actual time=170221.557..170221.558 rows=1 loops=1)
Buffers: shared hit=1 read=9186137
I/O Timings: read=138022.290
-> Seq Scan on posts (cost=0.00..9725636.88 rows=53949888 width=16) (actual time=0.014..153665.913 rows=53949886 loops=1)
Buffers: shared hit=1 read=9186137
I/O Timings: read=138022.290
Planning Time: 0.240 ms
Execution Time: 170221.627 ms
(8 rows)
La requête lit 70 Go de données et met environ 2min 50s à s’exécuter.
Maintenant la même requête, mais cette fois en utilisant les clés présentes dans le json.
SELECT
avg((jsonfield ->> 'ViewCount')::int),
avg((jsonfield ->> 'AnswerCount')::int),
avg((jsonfield ->> 'CommentCount')::int),
avg((jsonfield ->> 'FavoriteCount')::int)
FROM posts;
QUERY PLAN
------------------------------------------------------------------------------
Aggregate (cost=11883632.41..11883632.42 rows=1 width=128)
(actual time=520917.028..520917.030 rows=1 loops=1)
Buffers: shared hit=241116554 read=13625756
-> Seq Scan on posts (cost=0.00..9725636.88 rows=53949888 width=570)
(actual time=0.972..70569.365 rows=53949886 loops=1)
Buffers: shared read=9186138
Planning Time: 0.118 ms
Execution Time: 520945.395 ms
(10 rows)
La requête met environ 8min 40s à s’exécuter. En revanche le nombre de blocs lus semble un peu délirant :
Le Seq Scan indique comme tout à l’heure 70Go. En revanche, le nœud parent indique plus de 1.9 To lus!
Voici la taille de la table avec le paramétrage par défaut. Il faut savoir que pour certains enregistrements, le moteur va, soit compresser la ligne dans la heap, soit la compresser et la placer dans le toast.
SELECT
pg_size_pretty(pg_relation_size(oid)) table_size,
pg_size_pretty(pg_relation_size(reltoastrelid)) toast_size
FROM pg_class
WHERE relname = 'posts';
table_size | toast_size
------------+-----------
70 GB | 33 GB
(1 row)
Comment expliquer les 1.9 To lus ?
Par curiosité, j’ai fait la même requête, mais avec une seule agrégation et j’obtiens environ 538 Go.
On peut se poser plusieurs questions :
Aggregate ?Pour répondre à la première question, il suffit de lire la vue pg_statio_user_tables.
Avant exécution de la requête :
select relid,schemaname,relname,heap_blks_read,heap_blks_hit,toast_blks_read,toast_blks_hit from pg_statio_all_tables where relname in ('posts','pg_toast_26180851');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | toast_blks_read | toast_blks_hit
----------+------------+-------------------+----------------+---------------+-----------------+----------------
26180851 | public | posts | 422018238 | 87673549 | 129785076 | 628153337
26180854 | pg_toast | pg_toast_26180851 | 129785076 | 628153337 | |
(2 rows)
Après :
relid | schemaname | relname | heap_blks_read | heap_blks_hit | toast_blks_read | toast_blks_hit
----------+------------+-------------------+----------------+---------------+-----------------+----------------
26180851 | public | posts | 431204376 | 87673549 | 134156898 | 686299551
26180854 | pg_toast | pg_toast_26180851 | 134156898 | 686299551 | |
(2 rows)
Ce qui nous fait :
SELECT
pg_size_pretty(
((431204376 + 87673549) - (422018238 + 87673549) ) * 8*1024::bigint
) heap_buffers,
pg_size_pretty(
((134156898 + 686299551) - (129785076 + 628153337) ) * 8*1024::bigint
) toast_buffers;
heap_buffers | toast_buffers
--------------+---------------
70 GB | 477 GB
(1 row)
Le moteur va bien lire le toast. En revanche les compteurs laissent penser que le moteur va lire plusieurs fois le toast.
Si je fais le même calcul, mais cette fois en effectuant l’agrégation que sur un seul champ, j’obtiens 119 Go (~ 477 Go / 4) J’imagine que le moteur lit le toast pour chaque fonction.
Ensuite, l’écart du temps d’exécution s’explique par plusieurs facteurs :
Avec la première requête, le moteur n’avait pas à lire le toast. D’une part, il a moins de données à lire, d’autre part, il n’a pas à manipuler le json pour identifier la clé et extraire la valeur à calculer.
Enfin, les compteurs du nœud aggregate doivent correspondre aux données décompressées pour chaque fonction qui va lire dans le json. En effet, si on prend le total moins le seqscan de la table, donc que la partie toast, on a :
C’est ce qui explique pourquoi on obtient une valeur aussi élevée.
Maintenant, on va encourager Postgres à placer le maximum de données dans le toast grâce à l’option toast_tuple_target apparue avec la version 11 de Postgres.
Cette option permet de manipuler le seuil à partir duquel les données sont stockée dans le toast.
Par ailleurs, étant sous Postgres 14, j’en ai profité pour utiliser l’algorithme de compression lz4 (paramètre default_toast_compression). Cet algorithme offre un ratio de compression similaire à pglz, cependant, il est beaucoup plus rapide (Voir What is the new LZ4 TOAST compression in PostgreSQL 14, and how fast is it?).
CREATE TABLE posts_toast
WITH (toast_tuple_target = 128) AS
SELECT *
FROM posts;
Voici la taille de la table obtenue.
SELECT
pg_size_pretty(pg_relation_size(oid)) table_size,
pg_size_pretty(pg_relation_size(reltoastrelid)) toast_size
FROM pg_class
WHERE relname = 'posts_toast';
table_size | toast_size
------------+------------
59 GB | 52 GB
Au total, la table avec le toast fait grosso-modo la même taille. Dans l’exemple avec la première table, il faut savoir que le moteur compresse aussi les données dans la heap.
Rejouons notre requête d’agrégation :
SELECT
avg(viewcount),
avg(answercount),
avg(commentcount),
avg(favoritecount)
FROM posts_toast;
Cette fois la requête lit 59 Go de données et met 2min 17 secondes. On a gagné environ 20% de temps d’exécution sur cet exemple.
On pourrait gagner beaucoup plus si la partie stockée en toast était plus importante. Le volume de donnée à lire dans la heap serait beaucoup plus réduit.
Par curiosité, j’ai aussi exécuté la requête qui fait l’agrégation depuis les données du champ json. J’obtiens un temps d’exécution de 7min 17s.
Résumé en quelques chiffres :
On constate que l’usage du JSON est bien plus couteux que d’utiliser les types standards. Le moteur doit faire plus d’opérations pour accéder à la valeur d’une clé json.
Par ailleurs, il est obligé de décompresser les données dans le toast pour y accéder. Néanmoins, on peut aussi jouer avec le paramètre toast_tuple_target pour pousser plus
d’informations dans le toast. Ainsi, dans certains cas, cela peut permettre de réduire la quantité de données lues en évitant de lire le toast.
Comment souvent dans Postgres, tout évolue au fil des versions. Le TOAST n’échappe pas à cette règle. Ainsi, quelques nouveautés pourraient apparaitre dans les prochaines versions :
Il existe deux algorithmes de compression supportés : pglz (historique et intégré dans Postgres) et lz4 (depuis Postgres 14).
Voir les slides de la conférence d’Oleg Bartunov et Nikita Glukhov : json or not json that is the question ↩︎
Après une période d’inactivité, je reprends l’écriture d’articles techniques sur Postgres. C’est aussi pour moi l’occasion de vous annoncer mon changement d’activité. Depuis courant 2021 je suis passé freelance pour permettre aux entreprises de bénéficier de mon expérience sur Postgres.
PostgreSQL permet depuis très longtemps de partitionner des tables en exploitant l’héritage de table. Toutefois, cette méthode était assez lourde à mettre en oeuvre : elle impliquait de mettre en place soi-même des triggers pour rediriger les écritures (moins performant que le partitionnement natif), le temps de planification pouvait augmenter fortement au-delà d’une centaine de partitions…
Le partitionnement natif est arrivé avec la version 10. C’est depuis cette version que le moteur est capable (entre autres) de diriger lui-même les écritures vers les bonnes tables, lire seulement les tables concernées, d’utiliser des algorithmes exploitant le partitionnement etc. Il offre ainsi de meilleures performances et une facilité d’exploitation. On peut entre autres :
Toutes ces fonctionnalités sont intéressantes, mais on en vient à se poser une question toute bête : quand mettre en oeuvre le partitionnement?
Je vais vous présenter plusieurs cas d’usages que j’ai pu rencontrer. Mais avant, voici quelques erreurs courantes sur le partitionnement.
Déjà, qu’est-ce qu’une volumétrie “importante”?
Certains diront que c’est au-delà de plusieurs centaines de Go, d’autres au-delà du téraoctet, d’autres encore au-delà du pétaoctet…
Il n’existe pas vraiment de réponse à cette question et globalement ça va dépendre du type d’activité : ratio INSERT/UPDATE/DELETE, type de SELECT (OLTP, OLAP…). Ca dépendra également du matériel. Il y a 10 ans, quand les serveurs n’avaient que quelques Go de RAM avec des disques mécaniques, il était probable qu’une base de quelques centaines de Go soit perçue comme une grosse base. Maintenant il n’est pas rare de voir des serveurs avec plus d’un téraoctet de RAM, des disques NVMe.
Ainsi, une base de quelques centaines de Go n’est plus considérée comme une grosse base. Mais plutôt comme une base de taille modeste.
Petite anecdote, pour se rassurer, un client m’a questionné si Postgres était déjà utilisé pour des volumétries “importantes”. On parlait alors d’une base d’une quarantaine de Go sur un serveur qui disposait de 64Go de RAM. Toutes les lectures se faisaient depuis le cache… :). J’ai pu le rassurer sur la taille de sa base qui était relativement modeste.
Il peut tout à fait être superflu de partitionner une base de quelques To comme il peut être nécessaire de partitionner une base de quelques centaines de Go. Par exemple, si l’activité consiste juste à ajouter des lignes à des tables et que les requêtes se résument à de simple WHERE colonne = 4 qui retournent quelques lignes. Un simple Btree fera l’affaire. Et si la requête retourne un nombre assez important de lignes, il est possible d’utiliser les index BRIN ou les bloom filter.
Les index BRIN présentent des bénéfices proches du partitionnement ou sharding en évitant la complexité de mise en oeuvre1.
L’idée serait de créer des partitions et des tablespaces sur différents disques afin de répartir les opérations d’entrées/sorties.
Pour PostgreSQL, un tablespace n’est ni plus, ni moins qu’un chemin vers un répertoire. Il est tout à fait possible de gérer le stockage au niveau du système d’exploitation et d’agréger plusieurs disques (en RAID10) par exemple. Ensuite, il suffit de stocker la table sur le volume créé. Ainsi, on peut répartir les I/O sur un ensemble de disques.
Dans ce cas, il n’est donc pas nécessaire de mettre en oeuvre le partitionnement. Toutefois, nous verrons un cas où il pourrait avoir du sens.
Maintenant nous allons nous intéresser à des cas d’usage “légitimes” du partitionnement.
A cause du modèle MVCC, la suppression massive de données entraine de la fragmentation dans les tables.
Un cas d’usage possible est de partitionner par date. Supprimer les anciennes données revient à supprimer une partition complète. L’opération sera rapide et les tables ne seront pas fragmentées
L’ajout et modification de données dans une table fragmente les index au fil du temps. Pour faire simple, on ne peut pas récupérer l’espace libre dans un bloc tant qu’il n’est pas vide. Avec le temps les splits d’index créent du “vide” dans ce dernier et le seul moyen de récupérer cet espace est de reconstruire l’index.
On appelle cela le “bloat”. Il y a eu de nombreuses améliorations sur les dernières versions de Postgres:
Improve performance and space utilization of btree indexes with many duplicates (Peter Geoghegan, Heikki Linnakangas)
Previously, duplicate index entries were stored unordered within their duplicate groups. This caused overhead during index inserts, wasted space due to excessive page splits, and it reduced VACUUM’s ability to recycle entire pages. Duplicate index entries are now sorted in heap-storage order.
More efficiently store duplicates in B-tree indexes (Anastasia Lubennikova, Peter Geoghegan)
This allows efficient B-tree indexing of low-cardinality columns by storing duplicate keys only once. Users upgrading with pg_upgrade will need to use REINDEX to make an existing index use this feature.
Allow btree index additions to remove expired index entries to prevent page splits (Peter Geoghegan)
This is particularly helpful for reducing index bloat on tables whose indexed columns are frequently updated.
Pour contrôler le bloat, on pourrait reconstruire l’index à intervalles réguliers (merci REINDEX CONCURRENTLY arrivé en version 12). Cette solution serait contraignante, car il faudrait régulièrement reconstruire l’intégralité de l’index.
Si la majorité des modifications sont faites sur les données récentes, par exemple: table de logs, commandes clients, rendez-vous… On pourrait imaginer un partitionnement par mois. Ainsi, à chaque début de mois on part sur une table “neuve” et on peut ré-indexer la précédente table pour supprimer le bloat.
On peut aussi en profiter pour faire un CLUSTER sur la table pour avoir une bonne corrélation des données avec le stockage.
Petit à petit on va voir des cas d’usages un peu plus compliqués :)
Prenons un exemple : une table de commande comprenant un statut de livraison, au bout de quelques années 99% des commandes sont livrées (on l’espère!) et très peu en cours de paiement ou livraison.
Imaginons qu’on souhaite récupérer 100 commandes en cours de livraison. On va créer un index sur le statut et l’utiliser pour récupérer les enregistrements. En étant un peu astucieux, on peut créer un index partiel sur ce statut particulier. Problème, cet index va se fragmenter assez vite au fur et à mesure que les commandes seront livrées.
Dans ce cas on pourrait faire un partitionnement sur le statut. Ainsi, récupérer 100 commandes en cours de livraison revient à lire 100 enregistrements de la partition.
Pour déterminer le meilleur plan d’exécution, Postgres prend des décisions à partir des statistiques d’une table. Ces statistiques sont obtenues à partir d’un échantillon de la table (le default_statistic_target qui vaut 100 par défaut).
Par défaut le moteur va collecter 300 x default_statistic_target lignes, soit 30 000 lignes. Avec une table de plusieurs centaines de millions de lignes, cet échantillon est parfois trop petit.
On peut augmenter de manière drastique la taille de l’échantillon, mais cette approche présente quelques inconvénients:
ANALYZEAvec le partitionnement on pourrait avoir un même échantillon, mais par partition, ce qui permet de gagner en précision.
Ce serait également utile quand on a des données corrélées entre colonnes. Je vais reprendre l’exemple des commandes. On a une année entière de commandes: toutes les commandes qui ont plus d’un mois sont livrées, celles du dernier mois sont livrées à 90% (10% sont en cours de livraison).
Intuitivement, si je cherche une commande en cours de livraison il y a plus de 6 mois je ne devrais pas avoir de résultat. Inversement, si je cherche des commandes en cours de livraison sur le dernier mois, je devrais obtenir 10% de la table. Or, le moteur ne le sait pas, pour lui les commandes en cours de livraison sont réparties sur toute la table.
Avec un partitionnement par date, il peut estimer qu’il n’y a pas de commande en cours de livraisons de plus d’un mois. Ce type d’approche permet surtout de réduire une erreur d’estimation dans un plan d’exécution.
Voici un exemple avec cette table de commandes, orders_p est la version partitionnée par mois de la table orders. Les données étant identiques dans les deux tables.
On peut remarquer que l’estimation est bien meilleure dans le cas où la table est partitionnée, le moteur ayant des statistiques par partition.
|
|
Maintenant prenons la même requête sur le dernier mois:
|
|
Ici aussi on peut remarquer que l’estimation est meilleure.
Un autre intérêt du partitionnement est de bénéficier de meilleurs algorithmes pour les jointures et agrégation.
Le partitionwise aggregate permet de faire une agregation ou un regroupement partition par partition. Un exemple vaut mieux qu’un long discours:
|
|
Dans le premier cas l’agrégation se fait une fois pour toutes les tables, alors que dans le second exemple, on fait l’agrégation par partition. On peut également remarquer que le coût total est inférieur dans le plan avec agrégation par partition.
Le partitionwise join fonctionne sur le même principe, on fait une jointure partition par partition. C’est utile pour joindre deux tables partitionnées.
Enfin, un autre cas d’usage serait de vouloir stocker une partie de la table sur un stockage différent:
On peut stocker une table partitionnée dans des tablespaces différents. Par exemple les données récentes sur un tablespace rapide sur SSD NVMe. Puis les données plus rarement accédées sur un autre tablespace, avec des disques mécaniques moins couteux.
Cette approche peut aussi avoir du sens à l’heure du cloud où le stockage est très onéreux.
Voilà, je pense avoir fait le tour des principaux cas d’usages qui me venaient en tête.
Evidemment, la mise en oeuvre du partitionnement implique une plus grande complexité (gestion des partitions…) et des limitations qu’il faudra étudier en amont.
“BRIN indexes provide similar benefits to horizontal partitioning or sharding but without needing to explicitly declare partitions.” - https://en.wikipedia.org/wiki/Block_Range_Index ↩︎
Étonnamment, depuis que les requêtes parallèles ont été ajoutées dans
PostgreSQL 9.6, il était impossible de savoir à quel processus client était lié
un worker parallèle. Ainsi, comme Guillaume l’a fait
remarquer, it makes
il est assez difficile de construire des outils simples permettant
d’échantillonner les événements d’attente liés à tous les processus impliqués
dans une requête. Une solution simple à ce problème est d’exporter
l’information de lock group leader disponible dans le processus client au
niveau SQL :
commit b025f32e0b5d7668daec9bfa957edf3599f4baa8
Author: Michael Paquier <michael@paquier.xyz>
Date: Thu Feb 6 09:18:06 2020 +0900
Add leader_pid to pg_stat_activity
This new field tracks the PID of the group leader used with parallel
query. For parallel workers and the leader, the value is set to the
PID of the group leader. So, for the group leader, the value is the
same as its own PID. Note that this reflects what PGPROC stores in
shared memory, so as leader_pid is NULL if a backend has never been
involved in parallel query. If the backend is using parallel query or
has used it at least once, the value is set until the backend exits.
Author: Julien Rouhaud
Reviewed-by: Sergei Kornilov, Guillaume Lelarge, Michael Paquier, Tomas
Vondra
Discussion: https://postgr.es/m/CAOBaU_Yy5bt0vTPZ2_LUM6cUcGeqmYNoJ8-Rgto+c2+w3defYA@mail.gmail.com
Avec cette modification, il est maintenant très simple de trouver tous les processus impliqués dans une requête parallèle. Par exemple :
=# SELECT query, leader_pid,
array_agg(pid) filter(WHERE leader_pid != pid) AS members
FROM pg_stat_activity
WHERE leader_pid IS NOT NULL
GROUP BY query, leader_pid;
query | leader_pid | members
-------------------+------------+---------------
select * from t1; | 31630 | {32269,32268}
(1 row)Attention toutefois, comme indiqué dans le message de commit, si la colonne
leader_pid à la même valeur que la colonne pid, cela ne veut pas forcément
dire que le processus client est actuellement en train d’effectuer une requête
parallèle, car une fois que le champ est positionné il n’est jamais
réinitialisé. De plus, pour éviter tout surcoût, aucun verrou supplémentaire
n’est maintenu lors de l’affichage de ces données. Cela veut dire que chaque
ligne est traitée indépendamment. Ainsi, bien que cela soit fort peu probable,
vous pouvez obtenir des données incohérentes dans certaines circonstances,
comme par exemple un worker paralèlle pointant vers un pid qui est déjà
déconnecté.
Nouveau dans pg13: Colonne leader_pid dans pg_stat_activity was originally published by Julien Rouhaud at rjuju's home on March 08, 2020.
Les moteurs SQL permettent aux transactions concurrentes d’être isolées les unes des autres pour éviter les interférences. Cette propriété d’isolation correspond à la lettre I de l’acronyme bien connu “ACID”, les autres propriétés étant Atomicité, Cohérence (Consistency en anglais) et Durabilité.
Une particularité de l’isolation est qu’il y a plusieurs niveaux offerts au choix de l’utilisateur, qui diffèrent par leurs comportements en présence de transactions concurrentes.
Ces niveaux définis par le standard SQL dès la version 1992 sont dans l’ordre de l’isolation la plus faible à la plus forte:
Read Uncommitted: une transaction voit les changements des autres transactions avant qu’elle ne soient validées. Ce mode n’est pas mis en oeuvre dans PostgreSQL.
Read Committed: une transaction voit les changements d’une autre dès que l’autre a validé.
Repeatable Read: quand une transaction relit une ligne déjà lue via une requête précédente, elle doit relire les mêmes valeurs, même si la ligne a été changée par une autre transaction validée entre temps.
Serializable: une transaction ne peut pas voir ou produire des résultats qui n’auraient pas été possibles si d’autres transactions ne passaient pas concurremment.
Le niveau par défaut est Read Committed dans PostgreSQL et Repeatable Read dans MySQL ou MariaDB (en tout cas avec le moteur InnoDB). Au passage, le standard SQL indique que par défaut, c’est le mode Serializable qui doit être utilisé, donc cette recommandation n’est pas suivie (elle ne l’est pas non plus d’ailleurs par Oracle, ni MS SQL Server, ni DB2, ni Sybase ASE…).
Voyons un premier exemple très simple pour illustrer la différence entre MySQL et PostgreSQL dans leurs niveaux d’isolation par défaut:
On a une table avec 4 valeurs:
CREATE TABLE a(x int);
INSERT INTO a VALUES (1),(2),(3),(4);
Une transaction Tx1 fait la somme et la moyenne des valeurs, pendant qu’une transaction Tx2 ajoute une valeur, avec un ordre d’exécution tel que l’insertion est committée entre les lectures.
Avec PostgreSQL dans son mode par défaut Read Committed:
-- Tx1 -- Tx2
=# BEGIN; =# BEGIN;
BEGIN BEGIN
=# SELECT SUM(x) FROM a;
sum
-----
10
(1 ligne)
=# INSERT INTO a VALUES(50);
INSERT 0 1
=# COMMIT;
COMMIT
=# SELECT AVG(x) FROM a;
avg
---------------------
11.6666666666666667
(1 ligne)
=# COMMIT;
COMMIT
La valeur 50 n’a pas été prise en compte dans la somme mais a été
intégrée dans la moyenne. Si on regarde uniquement Tx1, on a donc
deux résultats mathématiquement incohérents entre eux. C’est parce qu’en
Read Committed, chaque nouvelle instruction SQL démarre à l’instant T
sur un état de la base incluant tous les changements des autres
transactions validées à cet instant T, en l’occurrence ici l’insertion de 50
par Tx2 et le COMMIT de Tx2 qui précèdent T.
Avec MySQL/MariaDB dans son isolation par défaut Repeatable Read, le résultat est différent:
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> SELECT SUM(x) FROM a;
+--------+
| SUM(x) |
+--------+
| 10 |
+--------+
1 row in set (0.00 sec)
> INSERT INTO a VALUES(50);
Query OK, 1 row affected (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.02 sec)
> SELECT AVG(x) FROM a;
+--------+
| AVG(x) |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.00 sec)
Dans le cas de MySQL/MariaDB, la valeur 50 insérée par Tx2 est ignorée par Tx1, en application de l’isolation Repeatable Read. Pour avoir la même chose dans PostgreSQL sur cet exemple, il faudrait passer à un niveau d’isolation supérieur (Repeatable Read ou Serializable)
Ce qu’il faut retenir ici, c’est que dans les configurations par défaut, des requêtes SQL très simples exécutées dans des sessions concurrentes peuvent donner des résultats différents d’un moteur à l’autre.
On pourrait penser que pour aligner systématiquement les comportements entre MySQL et PostgreSQL, il suffit de les régler au mêmes niveaux d’isolation. Mais en réalité, les comportements à un même niveau d’isolation peuvent différer sensiblement d’un moteur SQL à l’autre. Voyons un autre exemple qui illustre ça.
A présent on va utiliser l’isolation Repeatable Read dans les deux moteurs. Pour passer la session à ce niveau sans préjuger de la valeur par défaut, on peut exécuter les ordres suivants:
pour MySQL/MariaDB:
> SET SESSION transaction isolation level Repeatable Read;
pour PostgreSQL:
=# SET default_transaction_isolation TO 'Repeatable Read';
On considère deux tables vides:
CREATE TABLE a(xa int);
CREATE TABLE b(xb int);
Ensuite on exécute deux transactions concurrentes qui vont insérer
dans chaque table le count(*) de l’autre table.
Voici ce que ça donne avec PostgreSQL:
-- Tx1 -- Tx2
=# BEGIN; =# BEGIN;
BEGIN BEGIN
=# INSERT INTO a
SELECT count(*) FROM b;
INSERT 0 1
=# INSERT INTO b
SELECT count(*) FROM a;
INSERT 0 1
=# COMMIT;
COMMIT
=# SELECT COUNT(*) FROM a;
count
-------
0
(1 ligne)
=# COMMIT;
COMMIT
Une fois que Tx1 et Tx2 sont terminées, voici les résultats:
=# SELECT * FROM a;
xa
----
0
(1 ligne)
=# SELECT * FROM b;
xb
----
0
(1 ligne)
Au final, la valeur 0 se retrouve dans les deux tables car pour chacune des transactions,
la table dont elle calcule le count(*) est vide dans le cadre de sa visibilité.
Mais avec MySQL/MariaDB, le comportement et le résultat final sont différents, comme montrés dans la transcription ci-dessous. Tx2 se met en attente de Tx1 et récupère ses résultats avant de continuer, plutôt que d’être isolée de Tx1.
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
> INSERT INTO b SELECT count(*)
FROM a;
-- (Ici Tx2 se trouve bloquée)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
-- (Ici Tx2 est débloquée)
Query OK, 1 row affected (5.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
> -- résultat différent de PG
> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
-- Tx1 et Tx2 sont terminées
> SELECT * FROM a;
+------+
| xa |
+------+
| 0 |
+------+
1 row in set (0.01 sec)
> -- résultat différent de PG
> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Bien que la séquence d’instructions soit très simple et que les transactions
soient en Repeatable Read pour les deux moteurs, le résultat
obtenu diffère entre Postgres et MySQL/MariaDB au sens où la table
b contient au final une ligne avec 0 dans PostgreSQL et 1 dans MySQL.
Dans la mesure où Tx1 et Tx2 n’écrivent pas sur la même ligne (et en
l’occurrence même pas dans la même table), pour PostgreSQL l’INSERT de
Tx1 n’interfère pas du tout avec ce que fait Tx2. Tx2 n’a pas besoin
d’attendre que Tx1 finisse, elle peut compter les lignes de a via son
“snapshot”.
En revanche avec MySQL, Tx2 attend la fin de Tx1, et compte les
lignes de a en intégrant ce que Tx1 a fait (soit une insertion).
Tx1 et Tx2 sont donc moins isolées. Du point de vue d’un utilisateur
habitué au Repeatable Read de PostgreSQL, ce comportement
est plutôt étonnant (c’est-à-dire qu’il correspond en fait au Read Committed
de PostgreSQL)
Et on va voir que les différences ne s’arrêtent pas là avec une variante
de cet exemple, dans laquelle Tx2 interroge la table a en début
de transaction.
Voici donc une variante de l’exemple 2 dans laquelle Tx2 va lire et
renvoyer le count(*) FROM a en début de transaction.
Pour PostgreSQL, ça ne change rien, tout au long de Tx2, count(*) FROM a vaut toujours
0, que ce soit pour le renvoyer ou l’insérer.
Mais pour MySQL, il y a une nette différence de comportement pour éviter le phénomène “Non Repeatable Read”, qui est le minimum attendu pour le niveau d’isolation Repeatable Read.
Voyons la séquence suivante avec MySQL/MariaDB:
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
> -- initie la lecture répétable
> SELECT count(*) FROM a;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
> INSERT INTO b SELECT count(*)
FROM a;
-- (Ici Tx2 se trouve bloquée)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
-- (Ici Tx2 est débloquée)
Query OK, 1 row affected (3.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
> -- répète la lecture répétable
> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
Le résultat final dans a et b est le même que précédemment avec la table b
qui contient 1, mais cette
fois en fin de Tx2, il s’avère que SELECT COUNT(*) FROM a renvoie 0
alors que précédemment il renvoyait 1.
La différence est que l’interdiction du phénomène “Non Repeatable Read”
est mise en oeuvre parce qu’il y a eu un SELECT count(*) FROM a
qui renvoyait 0 en début de transaction.
Il faut donc qu’un SELECT count(*) FROM a
ultérieur continue de produire 0.
Mais ceci est vrai uniquement quand il est exécuté directement, et pas quand il est exécuté
en tant que sous-requête via INSERT INTO b SELECT count(*) FROM a,
C’est pourquoi il y a cette incohérence assez troublante entre le 1 qu’on
trouve dans b.xb et le 0 renvoyé au client, alors que c’est censé
être le résultat d’un même calcul dans la même transaction.
Il faut voir aussi que cette différence de résultat perdure dans Tx2 jusqu’à sa fin. Par exemple avant le COMMIT on pourrait avoir la séquence suivante:
mysql> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
mysql> INSERT INTO b SELECT COUNT(*) FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM b;
+------+
| xb |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
Ce dernier exemple illustre une spécificité du Repeatable Read de PostgreSQL qui évite un conflit d’écriture sur une même ligne au prix d’une annulation de la transaction. A contrario, MySQL/MariaDB au même niveau d’isolation n’annule pas la transaction, mais empêche l’effacement sans pour autant provoquer une erreur.
On va reprendre une table avec une colonne portant 4 nombres entiers, de 1 à 4. Les deux transactions concurrentes sont d’un côté une transaction Tx1 qui soustrait 1 à chaque valeur, et de l’autre une transaction Tx2 qui supprime la ligne portant la valeur maximale de la table. L’idée est que Tx2 veut supprimer une ligne que Tx1 change concurremment.
CREATE TABLE list(x int);
INSERT INTO list VALUES (1),(2),(3),(4);
Transcript PostgreSQL:
-- Tx1 -- Tx2
=# BEGIN; =# BEGIN;
BEGIN BEGIN
=# UPDATE list SET x=x-1; =# SELECT * FROM list;
x
---
1
2
3
4
(4 lignes)
=# DELETE FROM list WHERE x=4;
-- (Ici Tx2 se trouve bloquée)
=# COMMIT
COMMIT
-- Tx2 part en erreur
ERROR: could not serialize access
due to concurrent update
=# \echo :SQLSTATE
40001
=# ROLLBACK;
ROLLBACK
En isolation Repeatable Read, le moteur rejete l’écriture de Tx2 (au sens large du terme, la suppression d’une ligne étant une forme d’écriture) sur une ligne que Tx1 a déjà modifié. Ce refus se manifeste par une erreur spécifique (SQLSTATE 40001) et l’annulation de la transaction.
Avec MySQL/MariaDB ci-dessous, il n’y a pas d’annulation de la transaction.
Le DELETE ne provoque pas d’erreur mais n’efface pas la ligne x=4,
bien que cette ligne reste perpétuellement visible de Tx2.
-- Tx1 -- Tx2
mysql> BEGIN; mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
> UPDATE list SET x=x-1; > SELECT * FROM list;
Query OK, 4 rows affected (0.00 sec) +------+
Rows matched: 4 Changed: 4 Warnings: 0 | x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
> DELETE FROM list WHERE x=4;
-- (Ici Tx2 se trouve bloquée)
Query OK, 0 rows affected (5.73 sec)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
> SELECT * FROM list;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.01 sec)
> DELETE FROM list WHERE x=4;
Query OK, 0 rows affected (0.00 sec)
> SELECT * FROM list;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
> COMMIT;
Query OK, 0 rows affected (0.00 sec)
En répétant le SELECT * FROM list puis l’effacement, on voit que la ligne
avec x=4 est toujours là mais que le DELETE n’arrive pas à l’atteindre.
La seule indication que le moteur ne veut pas
la supprimer est le 0 rows affected dans l’information d’accompagnement
du retour du DELETE.
Par comparaison avec PostgreSQL, le non-effacement de la ligne dans Tx2 correspondrait au comportement de Read Committed de PostgreSQL, mais la persistence à montrer cette ligne alors que Tx1 l’a supprimée correspondrait plutôt à Repeatable Read. En ce sens le Repeatable Read de MySQL est une sorte d’entre-deux entre les deux niveaux Read Committed et Repeatable Read de Postgres.
Quand on porte des applications de MySQL à PostgreSQL ou inversement, ou qu’on veut faire des services qui fonctionnent sur les deux, il faut s’attendre à des comportements différents avec les transactions concurrentes, même quand on les configure sur le même niveau d’isolation.
Le standard SQL dit que certains niveaux d’isolation doivent éviter certaines anomalies d’accès concurrents (dits “phenomena” dans les termes du standard), mais chaque implémentation SQL interprète ça de manière plus ou moins étendue et contraignante, avec des résultats visibles clairement différents pour le code SQL.
PostgreSQL utilise Read Committed par défaut alors que MySQL a choisi Repeatable Read qui est mieux isolé, mais lorsque PostgreSQL est utilisé au niveau Repeatable Read, ses transactions sont concrètement mieux isolées qu’avec MySQL.
Remplacer une chaîne par une autre dans une chaîne plus large est simple
en SQL, avec la fonction replace:
select replace('la valeur est bar', 'bar', 'foo');
replace
-------------------
la valeur est foo
Mais il n’y a pas dans les fonctions de base de PostgreSQL de fonction
pour substituer des chaînes multiples, chacune par leur propre
remplaçante, comme on peut trouver en PHP avec
strtr, ou en
Python ou Perl avec des opérateurs de substitution via expressions
régulières (qu’on va utiliser plus loin dans ce billet justement).
En SQL, regexp_replace veut bien chercher une série de chaînes en une seule passe,
mais pas les remplacer individuellement, c’est-à-dire que
regexp_replace(string, 'foo|bar', 'baz', 'g') remplacerait
foo et bar par baz, mais on ne peut pas lui demander de remplacer
foo par une chaîne et bar par une autre.
Dans certaines situations, des appels successifs à replace peuvent
convenir, et c’est cette solution qui semble émerger sur les réponses
toutes faites trouvables sur le web, mais dans le cas général, cette
méthode a un risque de produire des résultats faux par rapport à un
remplacement en une seule passe.
Par exemple, dans la conversion d’un texte brut en entités HTML, ces cinq substitutions au minimum doivent être appliquées pour avoir du HTML valide:
> → >
< → <
& → &
" → '
' → "
Imaginons qu’on les applique dans cet ordre, avec une requête de ce genre:
select replace(replace(replace(replace(replace(
rawtext,
'>', '>'),
'<', '<'),
'&', '&'),
'"', '''),
'''', '"');
Si le texte à transformer est <strong> AT&T </strong>, le résultat
produit sera:
&lt;strong&gt; AT&T &lt;/strong&gt;
ce qui est clairement faux (si on le reconvertit en texte brut on ne retrouve pas l’original). Le bon résultat devrait être:
<strong> AT&T </strong>
Le problème avec la requête ci-dessus est qu’une fois que
> a été remplacé par > (ou < par <), l’étape suivante
de remplacement n’a aucun moyen de distinguer l’esperluète
dans AT&T (qui doit être remplacée) de celle dans >
qui doit être laissée telle quelle.
Dans cet exemple en particulier, on peut réordonner les remplacements
pour que & soit substitué par & en premier.
Mais dans le cas général ça peut être impossible à faire, s’il y a des références
circulaires entre les chaînes à remplacer, par exemple si on veut
inverser deux mots:
-- remplacer foo par bar et bar par foo.
-- mauvais résultat, version 1
select replace(replace('foo et bar', 'foo', 'bar'), 'bar', 'foo');
replace
------------
foo et foo
-- mauvais résultat, version 2
select replace(replace('foo et bar', 'bar', 'foo'), 'foo', 'bar');
replace
------------
bar et bar
Pour le remplacement multiple, il faut un algorithme parcourant la chaîne et faisant les substitutions en une seule passe. Il faut aussi gérer le fait qu’il y ait parfois plusieurs substitutions possibles, avec une règle discriminante (souvent, le segment le plus long possible est remplacé, mais on peut choisir une autre règle si besoin).
On n’a donc pas cette fonction de base dans PostgreSQL, mais l’opérateur de substitution
de Perl (s/pattern/replacement/flags) a cette fonctionnalité, puisque
pattern peut être une suite d’alternatives et replacement peut être un
tableau associatif (hash) clef/valeur avec toutes les substitutions, c’est-à-dire
qu’on peut écrire ça:
my %subs = (foo=>bar, bar=>foo);
my $string = "foo et bar";
$string =~ s/(foo|bar)/$subs{$1}/g;
print $string;
En plus l’implémentation depuis Perl 5.10 de cette forme d’expression régulière a été spécifiquement optimisée pour être particulièrement efficace:
Trie optimisation of literal string alternations
Alternations, where possible, are optimised into more efficient matching structures. String literal alternations are merged into a trie and are matched simultaneously. This means that instead of O(N) time for matching N alternations at a given point, the new code performs in O(1) time. A new special variable, ${^RE_TRIE_MAXBUF}, has been added to fine-tune this optimization. (Yves Orton)
PostgreSQL permet d’écrire notre fonction en Perl via l’extension
plperl, et en voici une version prête à l’emploi:
CREATE FUNCTION multi_replace(string text, orig text[], repl text[])
RETURNS text
AS $BODY$
my ($string, $orig, $repl) = @_;
my %subs;
if (@$orig != @$repl) {
elog(ERROR, "array sizes mismatch");
}
if (ref @$orig[0] eq 'ARRAY' || ref @$repl[0] eq 'ARRAY') {
elog(ERROR, "array dimensions mismatch");
}
@subs{@$orig} = @$repl;
my $re = join "|", map quotemeta,
sort { (length($b) <=> length($a)) } keys %subs;
$re = qr/($re)/;
$string =~ s/$re/$subs{$1}/g;
return $string;
$BODY$ language plperl strict immutable;
Cette fonction en Perl s’avère très rapide, même avec beaucoup de chaînes à remplacer (testé jusqu’à 1000, sachant qu’elles se cumulent dans une seule expression régulière) et beaucoup de remplacements effectifs.
On peut aussi l’écrire en plpgsql, mais le fait que l’ensemble des
substitutions ne puisse pas se faire en une seule passe en SQL est
très pénalisant dès qu’il y en a beaucoup à faire. Je ne mets pas
le code ici, mais il est sur le wiki de postgresql.org: multi_replace en
plpgsql.
A part qu’il prend en entrée les remplacements via un paramètre
de type jsonb plutôt que deux tableaux séparés, parce que c’est
pratique et que ça permet de faire coexister les fonctions sous
le même nom, le résultat produit doit être identique.
Par exemple, cet appel inversera foo et bar et en plus laissera
foobar inchangé en utilisant l’astuce de le remplacer par lui même:
select multi_replace(
'foo et bar ne sont pas foobar',
'{foo,bar,foobar}',
'{bar,foo,foobar}');
multi_replace
-------------------------------
bar et foo ne sont pas foobar
Que ce soit en plperl ou plpgsql, cette fonction évite le problème des
replace() imbriqués avec leurs remplacements qui se chevauchent, et
elle permet indifférement une liste de substitutions fixe ou
dynamique. Mais si vous en avez besoin sur des chaînes de grande
taille avec potentiellement beaucoup de substitutions, c’est un cas
où Perl est vraiment beaucoup plus efficace que l’équivalent
avec les fonctions SQL de base.
Parvenir à une suggestion d’index de qualité peut être une tâche complexe. Cela nécessite à la fois une connaissance des requêtes applicatives et des spécificités de la base de données. Avec le temps de nombreux projets ont essayé de résoudre ce problème, l’un d’entre eux étant PoWA version 3, avec l’aide de pg_qualstats extension. Cet outil donne de plutôt bonnes suggestions d’index, mais il est nécessaire d’installer et configurer PoWA, alors que certains utilisateurs aimeraient n’avoir que la suggestion d’index globale. Pour répondre à ce besoin de simplicité, l’algorithme utilisé dans PoWA est maintenant disponible dans pg_qualstats version 2, sans avoir besoin d’utiliser des composants additionnels.
EDIT: La fonction pg_qualstats_index\_advisor() a été changée pour retourner
du json plutôt que du jsonb, afin de conserver la compatibilité avec PostgreSQL
9.3. Les requêtes d’exemples sont donc également modifiées pour utiliser
json_array_elements() plutôt que jsonb_array_elements().
Une manière simple d’expliquer ce qu’est pg_qualstats serait de dire qu’il s’agit d’une extension similaire à pg_stat_statements mais travaillant au niveaux des prédicats.
Cette extension sauvegarde des statistiques utiles pour les clauses WHERE et JOIN : à quelle table et quelle colonne un prédicat fait référénce, le nombre de fois qu’un prédicat a été utilisé, le nombre d’exécutions de l’opérateur sous-jacent, si le prédicat provient d’un parcours d’index ou non, la sélectivité, la valeur des constantes et bien plus encore.
Il est possible de déduire beaucoup de choses depuis ces informations. Par exemple, si vous examinez les prédicats qui contiennent des références à des tables différentes, vous pouvez trouver quelles tables sont jointes ensembles, et à quel point les conditions de jointures sont sélectives.
Comment je l’ai mentionné, la suggestion d’index globale ajoutée dans pg_qualstats 2 utilise la même approche que celle de PoWA, ainsi cet article peut servir à décrire le fonctionnement des deux outils. La seule différence est que vous obtiendrez probablement une suggestion de meilleure qualité avec PoWA, puisque plus de prédicats seront disponibles, et que vous pourrez également choisir sur quel intervalle de temps vous souhaitez effectuer une suggestion d’index manquants.
La chose importante à retenir ici est qu’il s’agit d’une suggestion effectuée de manière globale, c’est-à-dire en prenant en compte tous les prédicats intéressant en même temps. Cette approche est différente de toutes les autres dont j’ai connaissance, qui ne prennent en compte qu’une seule requête à la fois. Selon moi, une approche globale est meilleure, car il est possible de réduire le nombre total d’index, en maximisant l’efficacité des index multi-colonnes.
La première étape consiste à récupérer tous les prédicats qui pourraient bénéficier de nouveaux index. C’est particulièrement facile à obtenir avec pg_qualstats. En filtrant les prédicats venant d’un parcours séquentiel, exécutés de nombreuses fois et qui filtrent de nombreuses lignes (à la fois en nombre et en pourcentage), vous obtenez une liste parfaite de prédicats qui auraient très probablement besoin d’un index (ou alors dans certains cas une liste des requêtes mal écrites). Voyons regardons par exemple le cas d’une applications qui utiliserait ces 4 prédicats:
Ensuite, il faut construire l’ensemble entier des chemins de toutes les prédicats joints par un AND logique, qui contiennent d’autres prédicats, qui peuvent être eux-meme également joints par des AND logiques. En utilisants les même 4 prédicats vus précédemments, nous obtenons ces chemins :
Une fois tous les chemins construits, il suffit d’obtenir le meilleur chemin pour trouver le meilleur index à suggérer. Le classement de ces chemins est pour le moment fait en donnant un poids à chaque nœud de chaque chemin qui correspond au nombre de prédicats simple qu’il contient, et en additionnant le poids pour chaque chemin. C’est une approche très simple, et qui permet de favoriser un nombre minimal d’index qui optimisent le plus de requêtes possible. Avec nos exemple, nous obtenons :
Bien évidemment, d’autres approches de classement pourraient être utilisée pour prendre en compte d’autres paramètres, et potentiellement obtenir une meilleur suggestion. Par exemple, en prenant en compte également le nombre d’exécution ou la sélectivité des prédicats. Si le ratio de lecture/écriture pour chaque table est connu (ce qui est disponible avec l’extension powa-archivist), il serait également possible d’adapter le classement pour limiter la suggestion d’index pour les tables qui ne sont accédées presque exclusivement en écriture. Avec cet algorithme, ces ajustements seraient relativement simples à faire.
Une fois que le meilleur chemin est trouvé, on peut générer l’ordre de création de l’index ! Comme l’ordre des colonnes peut être important, l’ordre est généré en récupérant les colonnes de chaque nœud par poids croissant. Avec notre exemple, l’index suivant est généré :
CREATE INDEX ON t1 (id, ts, val);Une fois que l’index est trouvé, on supprime simplement les prédicats contenus de la liste globale de prédicats et on reprendre de zéro jusqu’à ce qu’il n’y ait plus de prédicats.
Bien évidemment, il s’agit ici d’une version simplifiée de l’algorithme de
suggestion, car d’autres informations sont nécessaires. Par exemple, la liste
des prédicats est en réalité ajustée avec les classes d’opérateurs et méthode
d’acces en
fonction du type de la colonne et de sont opérateur, afin de s’assurer
d’obtenir des index valides. Si plusieurs méthodes d’accès aux index sont
trouvées pour un même meilleur chemin, btree sera choisi en priorité.
Cela nous amène à un autre détail : cette approche est principalement pensée pour les index btree, pour lesqules l’ordre des colonnes est critiques. D’autres méthodes d’accès ne requièrent pas un ordre spécifique pour les colonnes, et pour ces méthodes d’accès il est possible qu’une suggestion plus optimale soit possible si l’ordre des colonnes n’était pas pris en compte.
Un autre point important est que les classes d’opérateurs et méthodes d’accès ne sont pas gérés en dur mais récupérés à l’exécution en utilisant les catalogues locaux. Par conséquent, vous pouvez obtenir des résultats différents (et potentiellement meilleurs) si vous faites en sorte d’avoir toutes les classes d’opérateur additionelles disponibles quand vous utilisez la suggestion d’index globale. Cela pourrait être les extensions btree_gist et btree_gist, mais également d’autres méthodes d’accès aux index. Il est également possible que certain types / opérateurs n’aient pas de méthode d’accès associée dans les catalogues. Dans ce cas, ces prédicats sont retournées séparément dans une liste de prédicats non optimisables automatiquement, et pour lequel une analyse manuelle est nécessaire.
Enfin, comme pg_qualstats ne traite pas les prédicats composés d’expressions, l’outil ne peut pas suggérer d’index sur des expressions, par exemple en cas d’utilisateur de recherche plein texte.
Une simple fonction est fournie, avec des paramètres facultatifs, qui retourne une valeur de type json :
CREATE OR REPLACE FUNCTION pg_qualstats_index_advisor (
min_filter integer DEFAULT 1000,
min_selectivity integer DEFAULT 30,
forbidden_am text[] DEFAULT '{}')
RETURNS jsonLes noms de paramètres sont parlants :
min_filter: combien de lignes le prédicat doit-il filtrer en moyenne pour
être pris en compte par la suggestion globale, par défaut 1000 ;min_selectivity: quelle doit être la sélectivité moyenne d’un prédicat
pour qu’il soit pris en compte par la suggestion globale, par défaut
30% ;forbidden_am: liste des méthodes d’accès aux index à ignorer. Aucune par
défaut, bien que pour les version 9.6 et inférieures les index hash sont
ignoré en interne, puisque ceux-ci ne sont sur que depuis la version 10.Voici un exemple simple, tirés des tests de non régression de pg_qualstats :
CREATE TABLE pgqs AS SELECT id, 'a' val FROM generate_series(1, 100) id;
CREATE TABLE adv (id1 integer, id2 integer, id3 integer, val text);
INSERT INTO adv SELECT i, i, i, 'line ' || i from generate_series(1, 1000) i;
SELECT pg_qualstats_reset();
SELECT * FROM adv WHERE id1 < 0;
SELECT count(*) FROM adv WHERE id1 < 500;
SELECT * FROM adv WHERE val = 'meh';
SELECT * FROM adv WHERE id1 = 0 and val = 'meh';
SELECT * FROM adv WHERE id1 = 1 and val = 'meh';
SELECT * FROM adv WHERE id1 = 1 and id2 = 2 AND val = 'meh';
SELECT * FROM adv WHERE id1 = 6 and id2 = 6 AND id3 = 6 AND val = 'meh';
SELECT * FROM adv WHERE val ILIKE 'moh';
SELECT COUNT(*) FROM pgqs WHERE id = 1;Et voici ce que la fonction retourne :
SELECT v
FROM json_array_elements(
pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
ORDER BY v::text COLLATE "C";
v
---------------------------------------------------------------
"CREATE INDEX ON public.adv USING btree (id1)"
"CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
"CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)
SELECT v
FROM json_array_elements(
pg_qualstats_index_advisor(min_filter => 50)->'unoptimised') v
ORDER BY v::text COLLATE "C";
v
-----------------
"adv.val ~~* ?"
(1 row)La version 2 de pg_qualstats n’est pas encore disponible en version stable, mais n’hésitez pas à la tester et rapporter tout problème que vous pourriez rencontrer !
pg qualstats 2: Suggestion d'index globale was originally published by Julien Rouhaud at rjuju's home on January 06, 2020.
Cet article fait partie d’une série d’article sur la beta de PoWA 4, et décrit le nouveau daemon powa-collector.
Ce daemon remplace le précédent background worker lorsque le nouveau mode remote est utilisé. Il s’agit d’un simple daemon écrit en python, qui s’occupera de toutes les étapes nécessaires pour effectuer des snapshots distants. Il est disponible sur pypi.
Comme je l’ai expliqué dans mon précédent article introduistant PoWA 4, ce daemon est nécessaire pour la configuration d’un mode remote, en gardant cette architecture à l’esprit :
Sa configuration est très simple. Il vous suffit tout simplement de renommer
le fichier powa-collector.conf.sample fourni, et d’adapter l’URI de
connexion
pour décrire comment se connecter sur votre serveur repository dédié, et
c’est fini.
Une configuration typique devrait ressembler à :
{
"repository": {
"dsn": "postgresql://powa_user@server_dns:5432/powa",
},
"debug": true
}La liste des serveur distants, leur configuration ainsi que tout le reste qui est nécessaire pour le bon fonctionnement sera automatiquement récupéré depuis le serveur repository que vous ave déjà configuré. Une fois démarré, il démarrera un thread dédié par serveur distant déclaré, et maintiendra une connexion persistente sur ce serveur distant. Chaque thread effectuera un snapshot distant, exportant les données sur le serveur repository en utilisant les nouvelles fonctions sources. Chaque thread ouvrira et fermera une connexion sur le serveur repository lors de l’exécution du snapshot distant.
Bien évidemment, ce daemon a besoin de pouvoir se connecter sur tous les
serveurs distants déclarés ainsi que le serveur repository. La table
powa_servers, qui stocke la liste des serveurs distants, a un champ pour
stocker les nom d’utilisateur et mot de passe pour se connecter aux serveur
distants. Stocker un mot de passe en clair dans cette table est une hérésie,
si l’on considère l’aspect sécurité. Ainsi, comme indiqué dans la
section sécurité de
PoWA,
vous pouve stocker un mot de passe NULL et utiliser à la place n’importe
laquelle des autres méthodes d’authentification supportées par la
libpq (fichier
.pgpass, certificat…). C’est très fortement recommandé pour toute
installation sérieuse.
La connexion persistente sur le serveur repository est utilisée pour superviser la daemon :
Il est à noter que vous pouvez également demander au daemon de recharger sa configuration en envoyant un SIGHUP au processus du daemon. Un rechargement est nécessaire pour toute modification effectuée sur la liste des serveurs distants (ajout ou suppression d’un serveur distant, ou mise à jour d’un existant).
Veuillez également noter que, par choix, powa-collector n’effectuera pas de snapshot local. Si vous voulez utiliser PoWA pour le serveur repository, il vous faudra activer le background worker original.
La page de configuration est maintenant modifiée pour donner toutes les informations nécessaires sur le status du background worker, le powa-collector daemon (incluant tous ses threads dédiés) ainsi que la liste des serveurs distants déclarés. Voici un exemple de cette nouvelle page racine de configuration :
Si le daemon powa-collector est utilisé, le status de chaque serveur distant sera récupéré en utilisant le protocole de communication. Si le collecteur rencontre des erreurs (lors de la connexion à un serveur distant, durant un snapshot par exemple), celles-ci seront également affichées ici. À noter également que ces erreurs seront également affichées en haut de chaque page de toutes les pages de l’UI, afin d’être sûr de ne pas les rater.
De plus, la section configuration a maintenant une hiérarchie, et vous pourrez voir la liste des extensions ainsi que la configuration actuelle de PostgreSQL pour le serveur local ou distant en cliquant sur le serveur de votre choix!
Il y a également un nouveau bouton Reload collector sur le bandeau d’en-tête qui, comme on pourrait s’y attendre, demandera au collecteur de recharger sa configuration. Cela peut être utile si vous avez déclarés de nouveaux serveurs mais n’ave pas d’accès au serveur sur lequel le collecteur s’exécute.
Cette article est le dernier de la séurie concernant la nouvelle version de PoWA. Il est toujours en beta, n’hésitez donc pas à le tester, rapporter tout bug rencontré ou donner tout autre retour!
PoWA 4: Nouveau daemon powa-collector was originally published by Julien Rouhaud at rjuju's home on December 10, 2019.
Dalibo Labs vient de publier la version version 0.5 de PostgreSQL Anonymizer, une extension qui masque les données sensibles à l’intérieur d’une base de données PostgreSQL.
L’extension disposait déjà jusqu’ici d’un large panel de méthodes d’anonymisation : randomisation, imitation, destruction partielle, brassage, bruit, etc.
Pour cette nouvelle version, j’ai travaillé sur une nouvelle approche appelée [Géneralisation] et le concept de k-anonymat.
L’idée de la Généralisation est toute simple: pour éviter de donner une information sensible, on peut la remplacer par une valeur moins précise.
Par exemple, au lieu de dire “Bob a 28 ans”, on peut déclarer “Bob a entre 20 et 30 ans”. C’est quelque chose que nous faisons tous inconsciemment au quotidien lorsque nous ne voulons pas révéleer une information précise. Typiquement quand quelqu’un demande ‘Où habitez-vous ?”, le plus souvent nous répond de manière vague ( “J’habite à Paris” ou “J’habite dans le sud de la France”) au lieu de donner notre adresse complète.
Ce qui est intéressant avec cette approche, c’est que les données anonymisées restent vraies.
La Généralisation est donc une manière simple et intuitive de protéger son anonymat pour les êtres humains. Mais comment appliquer cela à un système de
gestion de base de données ? Après tout l’objectif principal d’une base de
données comme PostgreSQL est de stocker des données précises. Si vous avez
une colonne date_de_naissance dans une table, vous ne pouvez pas insérer
la valeur “dans les années 90”… 😀
Et bien il se trouve que PostgreSQL peut gérer la généralisation très facilement avec les types RANGE, un outil puissant pour stocker et manipuler des plages de données bornées par une limite basse et une limite haute.
Voici une table basique contenant des données de santé:
# SELECT * FROM patient;
ssn | firstname | zipcode | birth | disease
-------------+-----------+---------+------------+---------------
253-51-6170 | Alice | 47012 | 1989-12-29 | Heart Disease
091-20-0543 | Bob | 42678 | 1979-03-22 | Allergy
565-94-1926 | Caroline | 42678 | 1971-07-22 | Heart Disease
510-56-7882 | Eleanor | 47909 | 1989-12-15 | Acne
098-24-5548 | David | 47905 | 1997-03-04 | Flu
118-49-5228 | Jean | 47511 | 1993-09-14 | Flu
263-50-7396 | Tim | 47900 | 1981-02-25 | Heart Disease
109-99-6362 | Bernard | 47168 | 1992-01-03 | Asthma
287-17-2794 | Sophie | 42020 | 1972-07-14 | Asthma
409-28-2014 | Arnold | 47000 | 1999-11-20 | Diabetes
(10 rows)
Nous voulons que les données anonymisées restent vraies car elles vont être utilisées pour calculer des statistiques. Nous allons donc construire une vue matérialisée au-dessus de cette table avec 2 actions :
D’abord, nous devons retirer les clés primaires et les identifiants
directs. Dans ce cas, nous allons simplement supprimer le champs ssn
et nous allons détruire les valeurs du champs firstname.
Ensuite, nous allons réduire la précision des identifiants indirects
( zipcode and birth) avec 2 [fonctions de généralisation] fournies
par l’extension PostgreSQL Anonymizer : anon.generalize_int4range et
anon.generalize_daterange.
C’est parti:
CREATE MATERIALIZED VIEW generalized_patient AS
SELECT
'REDACTED'::TEXT AS firstname,
anon.generalize_int4range(zipcode,1000) AS zipcode,
anon.generalize_daterange(birth,'decade') AS birth,
disease
FROM patient;
Notez que les [fonctions de generalisation] prennent 2 arguments: la valeur originale en première option et un deuxième paramètre pour décrire la taille des “paliers” du type RANGE.
Pour les valeurs numeriques, le second paramètre est un entier. Ici le code postal (la colonne zipcode) est généralisé par palier de 1000.
Pour les valeurs temporelles, les paliers possibles sont : microseconds, milliseconds, second, minute, hour, day, week, month,
year, decade, century and millennium. Ici la colonne birth est
généralisée par tranches de 10 ans.
Ce qui nous donne une “vue vague” des données :
# SELECT * FROM generalized_patient;
firstname | zipcode | birth | disease
-----------+---------------+-------------------------+---------------
REDACTED | [47000,48000) | [1980-01-01,1990-01-01) | Heart Disease
REDACTED | [42000,43000) | [1970-01-01,1980-01-01) | Allergy
REDACTED | [42000,43000) | [1970-01-01,1980-01-01) | Heart Disease
REDACTED | [47000,48000) | [1980-01-01,1990-01-01) | Acne
REDACTED | [47000,48000) | [1990-01-01,2000-01-01) | Flu
REDACTED | [47000,48000) | [1990-01-01,2000-01-01) | Flu
REDACTED | [47000,48000) | [1980-01-01,1990-01-01) | Heart Disease
REDACTED | [47000,48000) | [1990-01-01,2000-01-01) | Asthma
REDACTED | [42000,43000) | [1970-01-01,1980-01-01) | Asthma
REDACTED | [47000,48000) | [1990-01-01,2000-01-01) | Diabetes
(10 rows)
La beauté des types RANGE est qu’ils peuvent être facilement manipulés pour des traitements statistiques. Par exemple, on peut répondre à la question “Combien de personnes nées avant 1990 ont eu la grippe ?” avec la requête ci-dessous:
SELECT count(*)
FROM generalized_patient
WHERE disease = 'Flu'
AND upper(birth) < '1990-01-01';
Comme on peut le voir, plus on généralise avec des paliers très larges,
moins les données seront précises. Par exemple, utiliser la valeur
millennium dans la fonction anon.generalize_daterange() va complètement
détruire les données. A l’inversie, utiliser la valeur week sera trop
juste et il y aura un risque de réidentification.
La question corrolaire liée à la Généralisation est donc : “Est-ce que ce jeu de données est suffisament généralisé ?” et c’est ici que nous rencontrons le concept de k-anonymat.
k-anonymat est un terme utilisé pour décrire une propriété d’un ensemble
de données anonymisées. Le principe de k-anonymat stipule qu’à l’intérieur
d’un ensemble de données, tout individu anonymisé ne peut pas être distingué
d’au moins k-1 autres individus. Autrement dit, le k-anonymat évalue le
degré de redondance des informations identifiantes. Une valeur faible de
de k indique qu’il y a un risque de ré-identification en utilisant
notamment la technique du recoupement avec des jeux de données externes.
Avec PostgreSQL Anonymizer, on peut évaluer le facteur k en 2 étapes :
SECURITY LABEL FOR anon ON COLUMN patient.zipcode IS 'INDIRECT IDENTIFIER';
SECURITY LABEL FOR anon ON COLUMN patient.birth IS 'INDIRECT IDENTIFIER';
k_anonymity():SELECT anon.k_anonymity('generalized_patient')
Dans l’exemple ci-dessus, le facteur k est 3, ce qui signifie que pour
chaque valeur du couple (zipcode,birth), il y a au moins 3 individus
dans la table generalized_patient. Lorsque k = 1, certaines patients
peuvent être réidentifiés à partir de données externes ou par inférence.
Vous avez probablement déjà compris qu’il y a 2 limitations liées à la méthode de Généralisation:
A/ Cette technique marche très bien pour les jeu de données ayant peu de
dimensions (i.e les tables avec peu de colonnes). En revanche si un
ensemble de données contient beaucoup d’ identifiants indirects, le
la facteur k va chuter.
B/ La vue généralisée et la table originale ont des schémas de données
différents. Concrètement, les valeurs numériques et temporelles sont
remplacées par des RANGE. Puisque la Généralisation “casse” le
modèle de données, il n’est pas possible d’utiliser le
Masquage Dynamique, les exports anonymes et la substitution
avec cette stratégie. Autrement dit: la Généralisation ne peut pas
être utilisée pour les tests d’intégration continue, pour les
environnements de développement ou sur des instances de formation.
On a ici l’illustration qu’il n’y a pas d’approche unique de l’anonymisation. En fait, dans certains vous devrez probablement construire plusieurs flux d’anonymisation pour la même base de données, car vos interlocuteurs auront des besoins et des contraintes différentes.
C’est tout pour aujourd’hui ! Si le RGPD et l’anonymisation sont un sujet important pour vous, jetez un oeil à l’extension PostgreSQL Anonymizer et envoyez-nous du feedback !
On adore le feedback 😀
Ce projet est open source et disponible à l’adresse ci-dessous:
https://gitlab.com/dalibo/postgresql_anonymizer/
Depuis la version 12, les collations de PostgreSQL peuvent être créées avec un paramètre nommé deterministic, qui peut être vrai ou faux, si bien que les collations sont maintenant soit déterministes (ce qu’elles sont par défaut), soit non déterministes.
Qu’est-ce que ce terme signifie exactement? Il se réfère à ce qu’Unicode appelle comparaisons déterministes ou en VO deterministic comparisons entre chaînes de caractères:
This is a comparison where strings that do not have identical binary contents (optionally, after some process of normalization) will compare as unequal
En français, c’est une comparaison où les chaînes qui n’ont pas un contenu binaire identique (optionnellement, après un processus de normalisation) ne sont pas égales.
Avant la version 12, les comparaisons de types concernés par les collations dans Postgres sont toujours déterministes d’après la définition ci-dessus. Spécifiquement, lorsque le fournisseur de collation (libc ou ICU) indique que deux chaînes sont égales, un tie-breaker binaire est appliqué, de sorte que seules les chaînes égales au niveau binaire sont considérées comme vraiment égales par le SGBD.
Depuis la version 12, le nouveau paramètre “deterministic” peut être mis
à faux dans CREATE COLLATION pour demander que les comparaisons
évitent le tie-breaker, de sorte qu’une différence de représentation
en mémoire entre les chaînes ne soit pas un obstacle à les reconnaître
comme égales si la locale sous-jacente dit qu’elles le sont.
Non seulement cela affecte les comparaisons directes ou les recherches en tables avec une clause WHERE, mais aussi les résultats de GROUP BY, ORDER BY, DISTINCT, PARTITION BY, les constraintes uniques, et tout ce qui implique l’opérateur d’égalité entre chaînes.
Qu’est-ce qui peut être fait avec les collations non déterministes?
La fonctionnalité la plus évidente est la comparaison en ignorant
la casse ou les accents, qui peut maintenant être mise en oeuvre avec
une clause COLLATE,
plutôt qu’en appelant explicitement les fonctions pour translater
la casse (upper, lower) et supprimer les accents (unaccent).
Maintenant que ceci est accessible via le service de collation,
la recommandation classique d’utiliser le type citext datatype
pour ces usages peut être reconsidérée.
Au-delà de cet usage, les collations non déterministes permettent de reconnaître l’égalité de chaînes qui sont équivalentes canoniquement (différant uniquement par leur forme normale Unicode), ou qui diffèrent seulement par des séquences compatibles entre elles, ou par la ponctuation, ou par des caractères non affichables.
Excepté pour l’équivalence canonique, ces fonctionnalités de comparaison
sont optionnelles, et elles sont activables en déclarant des attributs
dans le paramètre locale, et en particulier les
niveaux de comparaison.
La doc Unicode Technical Report #35 fournit une table de paramètres de collation avec des clés et valeurs au format BCP47, mais les exemples de ce billet utiliseront la syntaxe
“ancienne” des attributs avec ICU:
colStrength, colCaseLevel, colAlternate
plutôt que les “nouvelles” clés en question (respectivement ks, kc, ka).
La raison est que les attributs pré-cités fonctionnent avec toutes les versions
de ICU, alors que les clés ne sont reconnues que lorsque PostgreSQL est compilé
avec ICU version 54 (sorti en 2014) ou plus récent.
Il se trouve que que les binaries pré-compilés pour Windows sont compilés avec ICU version 53, donc
au moins pour cette raison il est préférable d’utiliser la syntaxe la plus ancienne.
A présent, voyons une liste des fonctionnalités avancées de comparaison qui sont rendues possibles par les collations non déterministes.
Il s’agit d’une exigence d’Unicode que PostgreSQL ne pouvait pas satisfaire jusqu’à présent.
Pour comprendre cela, il faut se rappeler que le point de code (codepoint en anglais) est un numéro Unicode, qui désigne le plus souvent un caractère, mais qui par extension peut désigner aussi certains marqueurs dans le flux de texte, comme l’indicateur de direction pour dire que le texte doit s’afficher de gauche à droite ou de droite à gauche, ou encore un accent tout seul qui doit être interprété en lien avec un caractère précédent. Pour le meilleur ou pour le pire, Unicode est nettement plus compliqué qu’une association un-à-un de caractères avec des numéros.
Comme expliqué dans Équivalence Unicode:
L’équivalence canonique est une forme d’équivalence qui préserve visuellement et fonctionnellement les caractères équivalents. Ils ont un codage binaire différents mais représentent un texte identique.
Les collations non déterministes reconnaissent les séquences équivalentes
comme égales sans avoir à spécifier d’attribuer particulier dans
l’argument locale.
L’exemple ci-dessous qui illustre cela utilise une locale non liée
à un langage particulier: déclarée par une chaîne vide, qui sélectionne
la collation racine. und peut aussi être utilisée, en tant que tag BCP-47 de 3 lettres
signifiant “undefined”. Autrement un code de langue peut être utilisé, suivi optionnellement par un code de “script” (type d’écriture), et un code de région. Par exemple on pourra utiliser 'fr-CA'
pour “français tel que pratiqué au Canada”.
Exemple d’équivalent canonique entre les formes normales NFD et NFC:
CREATE COLLATION nd (
provider = 'icu',
locale='', -- or 'und' (pas de langue ou région spécifiée)
deterministic = false
);
SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
(VALUES (E'El Nin\u0303o', E'El Ni\u00F1o')) AS s(s1,s2);
s1 | s2 | equal
---------+---------+-------
El Niño | El Niño | t
Par opposition, avec n’importe quelle collation déterministe, on
obtiendrait le résultat f pour false dans la colonne equal, étant
donné que les chaînes s1 and s2 ne sont pas égales au niveau
binaire (c.a.d au niveau des octets qui les composent après encodage).
Sans être équivalentes, des séquences de points de codes peuvent être simplement compatibles, auquel cas elles peuvent optionnellement être considérées comme égales.
Notamment, une ligature typographique peut souvent être représentée avec un seul point de code spécifique ou comme deux caractères distincts qui se suivent.
Au niveau tertiaire (le niveau par défaut), ces séquences ne sont pas égales.
Voyons ça en SQL, en réutilisation la collation "nd" définie plus haut:
SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
(VALUES ('shelffull', E'shel\ufb00ull')) AS s(s1,s2);
s1 | s2 | equal
-----------+----------+-------
shelffull | shelffull | f
Au niveau secondaire en revanche, ces séquences sont considérées comme égales:
CREATE COLLATION nd2 (
provider = 'icu',
locale = '@colStrength=secondary', -- ou 'und-u-ks-level2'
deterministic = false
);
SELECT s1, s2, s1 = s2 COLLATE nd2 AS equal FROM
(values ('shelffull', E'shel\ufb00ull')) AS s(s1,s2);
s1 | s2 | equal
-----------+----------+-------
shelffull | shelffull | t
L’usage le plus typique des collations non déterministes est certainement la comparaison insensible à la casse.
Au niveau secondaire, les chaînes qui diffèrent par la casse sont considérées comme égales:
SELECT s1, s2, s1 = s2 COLLATE nd2 AS equal FROM
(values ('Abc', 'ABC')) AS s(s1,s2);
s1 | s2 | equal
-----+-----+-------
Abc | ABC | t
Les chaînes qui diffèrent par les accents ou la casse (ou les deux) sont considérées comme égales au niveau primaire:
CREATE COLLATION nd1 (
provider = 'icu',
locale = '@colStrength=primary', -- ou 'und-u-ks-level1'
deterministic = false
);
SELECT s1, s2, s1 = s2 COLLATE nd1 AS "equal-nd1",
s1 = s2 COLLATE nd2 AS "equal-nd2"
FROM (values ('Été', 'ete')) AS s(s1,s2);
s1 | s2 | equal-nd1 | equal-nd2
-----+-----+-----------+-----------
Été | ete | t | f
Il est possible d’ignorer les accents mais pas la casse en restant au niveau primaire de comparaison, et en activant un attribut booléen spécifique: colCaseLevel.
Exemple:
CREATE COLLATION nd2c (
provider = 'icu',
locale = 'und@colStrength=primary;colCaseLevel=yes' , -- ou 'und-u-ks-level1-kc'
deterministic = false
);
SELECT 'Ete' = 'Eté' COLLATE nd2c AS eq1,
'Ete' = 'ete' COLLATE nd2c AS eq2;
eq1 | eq2
-----+-----
t | f
Pour la ponctuation, on peut d’abord l’ignorer complètement, comme indiqué dans le chapitre “Ignore Punctuation” Options de la documentation ICU.
C’est faisable en activant “Alternate Handling” aux niveaux de comparaison
de 1 à 3.
Etant donné que colStrength=tertiary par défaut, on peut laisser colStrength non
spécifié, comme ci-dessous:
CREATE COLLATION "nd3alt" (
provider = 'icu',
locale='und@colAlternate=shifted',
deterministic = false
);
SELECT '{your-name?}' = 'your name' COLLATE "nd3alt" AS equal;
equal
-------
t
colAlternate mis à shifted au niveau de comparaison “quaternary”
peut être utilisé pour faire reconnaître l’égalité entre des symboles
y compris de ponctuation qui sont équivalent sur le plan linguistique,
mais sont exprimés via des codes de points différents.
Par exemple HORIZONTAL ELLIPSIS (U+2026) est équivalent à trois points
consécutifs en US-ASCII (FULL STOP, U+002E), et FULLWIDTH COMMERCIAL AT (U+FF20) est
équivalent à COMMERCIAL AT (U+0040) tel qu’utilisé dans les adresses mail
exprimées en caractères US-ASCII.
CREATE COLLATION "nd4alt" (
provider = 'icu',
locale='und@colStrength=quaternary;colAlternate=shifted',
deterministic = false
);
SELECT 'Wow…!' = 'Wow...!' COLLATE "nd4alt" AS equal;
equal
-------
t
Au niveau de comparaison tertiaire ou inférieur, les points de
codes qui sont dans les intervalles
[\u0001-\u0008], [\u000E-\u001F] [\u007f-\u009F]
(caractère de contrôles) sont ignorés dans les comparaisons.
C’est aussi vrai des points de codes pour les caractères d’espacement comme ceux-là (liste non exhaustive ne comprenant que les plus fréquents):
Exemple:
SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
(VALUES ('ABC', E'\u200eA\u0001B\u00adC')) AS s(s1,s2);
s1 | s2 | equal
-----+-----------+-------
ABC | A\x01BC | t
Pour que ces points de codes ne soient pas ignorés, le niveau de comparaison
doit être mis au maximum, c’est-à-dire colStrength=identical (ou
ks-identic pour la syntaxe avec tags).
A ce niveau, la seule différence avec l’égalité binaire est l’interprétation
des séquences en équivalence canonique.
CREATE COLLATION "nd-identic" (
provider = 'icu',
locale='und@colStrength=identical', -- or und-u-ks-identic
deterministic = false
);
SELECT 'abc' = E'a\u0001bc' COLLATE "nd-identic" AS equal;
equal
-------
f
Le umlaut allemand est parfois converti en séquences de caractères US-ASCII comme suit:
Ces équivalences ne sont pas reconnues comme égales par les collations ICU, y compris au niveau primaire de comparaison et en spécifiant Allemand (de) comme langue. En revanche ß (eszett) et ss sont égaux au niveau primaire.
A partir de la version 60, ICU fournit de-ASCII comme en tant que
règle de transformation.
Les transformations sont réalisées par un service différent des collations, et qui n’est
pas exposé par PostgreSQL (voir icu_transform() dans icu_ext si vous avez besoin de ce genre de transformation,
ou plus généralement des translitérations entre systèmes d’écritures (scripts)).
Dans des tables utilisateurs, on peut avoir besoin de stocker une référence aux rôles PostgreSQL, par exemple pour représenter des droits d’accès à des fonctionnalités applicatives, ou des méta-données associées aux comptes. Se pose alors la question de ce qu’on peut utiliser comme référence pour désigner un rôle.
Bien sûr un rôle en tant qu’objet dans le catalogue a une clef unique, et même deux. Il a premièrement un OID (entier 32 bits non signé) attribué automatiquement, et deuxièmement le nom du rôle qui est aussi évidemment unique. Mais l’OID n’est pas portable d’une instance à l’autre, et un nom de rôle peut aussi changer dans le dos de l’applicatif, si bien qu’on peut vouloir chercher une troisième voie. Regardons ça de plus près.
Un rôle est stocké avec ses propriétés principales dans la table système
pg_catalog.pg_authid, qui est partagée par toutes les bases:
postgres=# \d pg_authid
Table "pg_catalog.pg_authid"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
oid | oid | | not null |
rolname | name | | not null |
rolsuper | boolean | | not null |
rolinherit | boolean | | not null |
rolcreaterole | boolean | | not null |
rolcreatedb | boolean | | not null |
rolcanlogin | boolean | | not null |
rolreplication | boolean | | not null |
rolbypassrls | boolean | | not null |
rolconnlimit | integer | | not null |
rolpassword | text | C | |
rolvaliduntil | timestamp with time zone | | |
Indexes:
"pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
Avec une version 11 ou inférieure, on ne verra pas la première colonne
oid visible ici, mais elle est quand même là, et avec un index unique
pg_authid_oid_index qui porte dessus.
La sortie ci-dessus est faite avec PostgreSQL 12, dans laquelle
l’OID est devenue une colonne normale.
Généralement on accèdera plutôt aux rôles via la vue pg_roles, qui
porte aussi cet OID mais qui diffère en ce que:
elle n’est pas réservée aux super-utilisateurs.
rolpassword est forcé en dur à '********' pour ne rien révéler des mots de passe.
elle a en plus rolconfig avec les variables de configuration
optionnelles associées au rôle, qu’on va justement utiliser pour
ajouter des méta-données un peu plus loin dans ce billet.
postgres=# \d pg_roles
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
Si pg_authid était une table normale, on pourrait écrire une
déclaration s’y référant via une clef étrangère, du style:
CREATE TABLE nomtable(u_id oid REFERENCES pg_authid(oid), ...);
Mais comme c’est une table système, ce n’est pas possible. Si on essaie, on obtient:
ERREUR: droit refusé : « pg_authid » est un catalogue système
Et même si on pouvait faire des contraintes d’intégrité référentielles vers
des tables systèmes, il est probable que pg_authid serait
exclue pour une autre raison: c’est une table partagée par toutes les bases,
donc à chaque fois qu’une ligne change, il faudrait que Postgres vérifie
dans toutes les bases pour tester la validité de la contrainte, et ce
ne serait pas compatible avec son architecture étanche où une connexion
établie sur une base donnée n’a un accès direct qu’à cette seule base.
Le login ou nom du rôle est conservé lors d’un pg_restore,
et il est unique, mais est-ce une bonne clef primaire?
Un renommage peut être fait avec un ordre SQL du genre:
ALTER ROLE nom_role RENAME to nouveau_nom;
qui peut être exécuté de n’importe quelle base.
Cet ordre ne déclenche pas de trigger, parce que comme répondu dans la discussion Event trigger and CREATE/ALTER ROLE/USER sur pgsql-hackers, un trigger est rattaché à une seule base alors qu’un rôle est global à l’instance:
If you were to create an event trigger in database A, then a user gets created in database B, your function would not be invoked, which becomes a problem.
Pour être tranquille, il faudrait interdire les changements de login, par principe. Mais imposer qu’un compte ne changera jamais de login n’est pas forcément réaliste quand des comptes sont associés à des personnes physiques. Une personne dont le login est son nom de famille peut vouloir le changer pour des raisons liées à son état civil. Ou encore quand un utilisateur hérite du compte nominatif d’un prédécesseur, difficile de lui imposer d’utiliser ad aeternam le nom de l’autre personne.
Quand on exporte une instance avec pg_dumpall, les rôles sont exportés en commandes de création du style:
CREATE ROLE foo PASSWORD '...' ;
L’ennui est qu’il n’y a rien dans cette commande qui conserverait l’OID. Au moment de la restauration, les rôles seront donc certainement recréés avec des OIDs différents, qui ne correspondront plus aux références stockées dans les tables utilisateurs.
On aura d’ailleurs le même problème avec toute forme de réplication autre que réplication physique, où le réplicat est un clone de la source à tous points de vue.
Cette possibilité qui m’a été suggérée sur Twitter permet de faire face élégamment au problème de changement des OIDs par pg_restore, ainsi qu’à un éventuel renommage du rôle.
regrole est un type alias d’OID, c’est-à-dire qu’au stockage c’est
physiquement un OID, mais lorsqu’on y accède en lecture ou écriture, le nom du rôle
remplace dynamiquement la valeur numérique de l’OID.
La documentation v11
exprime ça en ces termes:
Les types alias d’OID ne disposent pas d’opérations propres à l’exception des routines spécialisées de saisie et d’affichage. Ces routines acceptent et affichent les noms symboliques des objets système, plutôt que la valeur numérique brute que le type oid utilise.
Voyons un exemple:
CREATE TABLE users(
id regrole,
fonction text,
permissions text[]
);
En entrée dans la table, on peut utiliser le nom du rôle en tant que littéral:
CREATE ROLE ope_front;
INSERT INTO users VALUES ('ope_front', 'Opérateur Front Office', '{saisie}');
En sortie, il ressort aussi en littéral:
SELECT * FROM users;
id | fonction | permissions
-----------+------------------------+-------------
ope_front | Opérateur Front Office | {saisie}
Et c’est pareil en export avec pg_dump, où les données de la table ressortiront sous cette forme:
COPY public.users (id, fonction, permissions) FROM stdin;
ope_front Opérateur Front Office {saisie}
\.
L’intérêt est que si ce dump est rejoué dans une instance où l’utilisateur
a été recréé, l’OID qui se retrouvera dans users.id sera
correct contrairement à ce qui se passerait si l’OID était
en format numérique dans l’export.
Par ailleurs, un éventuel changement de nom du rôle est transparent
pour notre table users, ce qui est un avantage par rapport à une colonne
de type text.
Par exemple:
ALTER USER ope_front RENAME TO operateur_front;
SELECT * FROM users;
id | fonction | permissions
-----------------+------------------------+-------------
operateur_front | Opérateur Front Office | {saisie}
CREATE ROLE a un paramètre SYSID qui semble avoir été pensé au départ
pour être un identifiant se référant à l’extérieur de Postgres.
Mais on peut refermer cette piste tout de suite car
il est obsolète et ignoré si on essaie de l’utiliser:
=# CREATE USER test SYSID 42;
NOTICE: SYSID can no longer be specified
CREATE ROLE
On peut associer à un rôle des valeurs pour certains paramètres
de configuration qu’on trouve dans postgresql.conf, comme
work_mem, default_transaction_isolation, etc. via ALTER ROLE,
par exemple:
ALTER ROLE web_user SET work_mem TO '128MB';
Depuis PostgreSQL 9.3, cette possibilité est étendue à n’importe quel
paramètre personnalisé déclaré avec un préfixe
(avant il fallait une déclaration préalable dans custom_variable_classes).
En fait le préfixe est plutôt prévu pour être le nom d’une extension,
mais ça fonctionne très bien aussi sans extension, si bien qu’on
peut faire, par exemple, en étant super-utilisateur:
ALTER ROLE nom_role SET users.id = '759f5abb-64b1-4dbc-8b71-f1d7bd18ad85';
Les choix de users comme espace de nom et id comme nom de paramètre
sont arbitraires. Tous ces paramètres sont de toute manière exportés
par pg_dumpall sous forme de commandes ALTER ROLE.
Les paramètres de configuration sont accessibles dans la colonne
pg_roles.rolconfig
de type text[], contenant un tableau avec des chaînes au format nom=valeur.
Pour extraire le nom et la valeur, on peut
utiliser la fonction plpgsql ci-dessous inspirée de la fonction
ParseLongOption() dans le code de Postgres en langage C:
CREATE FUNCTION parse_option(string text, name OUT text, value OUT text)
RETURNS record
AS $$
declare
p int := strpos(string, '=');
begin
if (p > 0) then
name := replace(left(string, p-1), '-', '_');
value := substr(string, p+1);
else
name := replace(string, '-', '_');
value := NULL;
end if;
end
$$ LANGUAGE plpgsql immutable strict;
On peut donc obtenir l’ID personnalisé d’un rôle à partir de son login avec une requête du style:
SELECT o.value
FROM pg_roles,
unnest(rolconfig) as c(x),
parse_option(c.x) as o
WHERE rolname = :'nom_du_role'
AND o.name = 'users.id';
Si c’est juste pour le compte avec lequel on est connecté,
pas la peine de faire si compliqué, un SHOW suffira:
SHOW users.id; -- ou SELECT pg_catalog.current_setting('users.id')
Si une session a besoin d’accéder répétitivement à cette information sur tous les rôles, elle peut avoir intérêt à créer une table temporaire pour l’avoir en accès rapide, ce qui peut se faire via une requête de ce style:
CREATE TEMP TABLE cache_users AS
SELECT r.oid, r.rolname, o.value AS user_id
FROM pg_roles,
unnest(rolconfig) as c(x),
parse_option(c.x) as o
WHERE o.name='users.id';
En utilisant le type regrole, on peut éviter les principaux
inconvénients d’une référence directe à l’OID ou au nom du rôle.
En attribuant à des rôles des IDs synthétiques stockés sous forme de paramètres de configuration, on atteint les buts suivants:
pg_roles.En revanche, dans les tables utilisateurs, avoir une vraie contrainte d’intégrité référentielle vers cette information reste impossible, quelle que soit l’option retenue.
Il y a un an, j’ai lancé un projet appelé PostgreSQL Anonymizer pour étudier et aprrendre différentes techniques de protection des données privées en utilisant la puissance de PostgreSQL. Ce projet fait maintenant partie de l’initiative Dalibo Labs et nous venons de publier une nouvelle version la semaine dernière….
C’est l’occasion d’analyser les progrès réalisés, l’impact du RGPD et les futures directions du projet.
Depuis le début de ce projet, le paysage a radicalement changé…. Lorsque le RPGD est entré en vigueur en mai 2018, uns des grandes questions concerant les pénalités financières infligées. Est-ce que les montants serait assez significatifs pour provoquer un réel changement dans la manière dont les entreprises gèrent les données personnelles ?
De ce que l’on peut voir, les amendes RGPD ont commencé à tomber depuis le début d’année et rien que pour le mois de juillet 2019 : Bristish Airways a reçu une sanction de 204 M€ et les hotels Marriott Hotels en a reçu une de 110 M€.
Il y a aussi des amemdes moins élevée pour des sociétés de petites tailles. Ce qui est intéressant, c’est que les sanctions les plus importantes concernent l’Article 32 et « des mesures techniques et organisationnelles insuffisante pour garantir le sécurity de l’information ».
En d’autres termes : des fuites de données.
Voici un domaine ou l’anonymisation peut aider ! Sur la base de mon expérience, il est possible de réduire les risques de fuites d’informations sensibles en limitant la dispersion de ces données. Dans beaucoup d’environnements ( pré-production, formation, développement, intégration, statistiques, etc…) les données réelles ne sont pas absolument nécessaires. Avec une politique d’anonymisation très forte, on peut resteindre l’usage des données personnelles uniquement lorsque c’est indispensable et travailler sur des données aléatoires ou artificielles partout ou c’est possible… Lorsque l’anonymisation est faite correctement, les jeux de données anonymisées ne sont plus soumises aux contraintes du RGPD.
Pour résumer: l’anonymisation est une méthode puissante pour réduire la
surface d’attaque et c’est un clé pour limiter les risques
liés aux fuites de données.
C’est pourquoi nous investissons beaucoup d’efforts pour développer des outils de masquage directement à l’intérieur de PostgreSQL !
Le mois dernier, j’ai travaillé sur différents aspects de l’extension, notamment :
mélange et
la génération de bruit.Un défaut de l’implémentation actuel de PostgreSQL Anonymizer est que les
Règles de Masquage sont déclarées avec la syntaxe COMMENT, ce
qui peut être ennuyeux lorsque votre modèle de données contient déjà
des commentaires.
Grace à une idée d’Alvaro Herrera, je travaille actuellement sur une nouvelle syntaxe basée sur les Labels de Sécurité, une fonctionnalité méconnue qui est utilisée principalement par l’extension sepgsql.
SECURITY LABEL FOR anon ON COLUMN people.zipcode
IS 'MASKED WITH FUNCTION anon.fake_zipcode()'
Cette syntaxe devrait disponible dans quelques semaines. Bien sur la syntaxe précédentes sera toujours supportées pour assurer la retro-compatibilité.
Le RGPD et la protectée des données personnelles sont deux sujets brulants ! Je parlerai de tout cela dans différents événement cet automne, notamment:
If you have any ideas or comments on PostgreSQL Anonymizer or more generally about protecting data privacy with progress, please send me a message at damien@dalibo.com.
On peut depuis bien longtemps exporter des résultats de requête
en CSV, soit avec COPY (SELECT ...) TO STDOUT CSV qui est une
commande SQL, soit via la méta-commande \copy de psql qui appelle
en sous-main COPY en gérant le flux de données côté client.
Mais il reste quelques situations non couvertes par cette
fonctionnalité, et c’est pourquoi dans PostgreSQL 12, le CSV a été
ajouté aux formats de sortie en général, c’est-à-dire qu’il est disponible
pour toutes les commandes produisant des résultats tabulaires.
On peut opter pour ce format avec la commande \pset format csv, ou en ligne de
commande avec l’option --csv.
Concrètement, quels sont les cas où on peut en avoir
besoin préférentiellement à \copy?
quand on récupère les données via une méthode non supportée par COPY,
par exemple un curseur:
on peut faire fetch from c mais pas copy (fetch from c) to stdout csv.
quand le résultat est produit par une méta-commande plutôt qu’une requête
directe: \crosstabview, \l, \d etc…
Jusqu’à présent, les besoins d’export simple au format tabulaire sont
souvent couverts par le format “unaligned” et son séparateur de champ
paramétrable “fieldsep” (option -F).
Mais ce format a deux faiblesses:
Exemple:
$ query="select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2"
$ psql -AtF',' -c "$query"
ab,cd,ef
gh
Le problème est qu’en partant de cette sortie, on ne sait plus où démarrent et finissent
les champs ni combien d’enregistrements il y avait au départ.
Alors qu’il y a un seul enregistrement et deux colonnes, cette sortie
pourrait aussi bien représenter une seule colonne avec ab,cd,ef
en valeur du premier enregistrement et gh pour le second.
Le “vrai” CSV règle ce problème:
$ psql --csv -t -c "$query"
"ab,cd","ef
gh"
La règle qui fait toute la différence est que des guillemets sont ajoutés pour encadrer un champ dès qu’il contient le séparateur ou un saut de ligne, ou un guillemet (dans ce dernier cas les guillemets intra-champs sont doublés).
En principe, les scripts qui importent du CSV sont capables de relire ce contenu en restituant les valeurs de départ dans tous les cas, y compris les champs multi-lignes.
Le CSV peut aussi être utilisé comme intermédiaire pour produire au final un autre format, que psql ne gèrerait pas directement.
Il faut pour ça intercaler un script entre la sortie de psql et une commande de transformation de CSV vers cet autre format. Pour la partie lecture des données CSV, le script sera souvent simple parce que la plupart des languages de script sont dotés d’analyseurs CSV déjà faits, l’intérêt de ce format étant justement son ubiquité.
Voici par exemple un programme Perl csv-to-markdown qui transforme
du CSV UTF-8 vers le format markdown tel qu’utilisé notamment par github,
et destiné à produire du HTML.
Il ne faudra probablement pas plus de code pour faire la même chose
en Ruby, Python ou un language comparable, et aussi certainement
guère plus d’effort pour gérer d’autres formats que markdown, ou
encore personnaliser cette sortie.
#!/usr/bin/perl
use Text::CSV;
use open qw( :std :encoding(UTF-8) );
my $csv = Text::CSV->new({ binary => 1, eol => $/ });
sub do_format {
s/&/&/g;
s/</</g;
s/>/>/g;
s/\n/<br>/g;
s/\|/|/g;
return $_;
}
my $header = $csv->getline(STDIN);
for (@{$header}) {
$_ = do_format($_);
}
print join ('|', @{$header}), "\n";
print join ('|', map { "---" } @{$header}), "\n";
while (my $row = $csv->getline(STDIN)) {
my @contents = map { do_format($_) } @{$row};
print join('|', @contents), "\n";
}Et voici quelques possibilités d’appeler ce programme depuis psql pour lui faire sortir du format markdown:
\pset format csv
-- méthode 1 avec \g (par requête)
select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2
\g |csv-to-markdown >/tmp/table1.md
-- méthode 2 avec \o (pour toutes les requêtes et métacommandes à suivre)
\o |csv-to-markdown >/tmp/table2.md
select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2;
-- revient à l'affichage normal (aligned) et à l'écran
\a \o
Si on a une seule requête à sortir, on peut aussi l’écrire en une seule ligne de commande.
$ psql --csv -c "$query" | csv-to-markdown >/tmp/table3.md
Dans tous les cas ci-dessus, le résultat produit est:
col1|col2
---|---
ab,cd|ef<br>gh
où les deux champs sont bien interprétés comme ils doivent.
Cet article fait partie d’une série d’article sur la beta de PoWA 4, et décrit les changements présents dans powa-archivist.
Pour plus d’information sur cette version 4, vous pouvez consulter l’article de présentation général.
Tout d’abord, il faut savoir qu’il n’y a pas d’upgrade possible depuis la v3
vers la v4, il est donc nécessaire d’effectuer un DROP EXTENSION powa si vous
utilisiez déjà PoWA sur vos serveurs. Cela est du au fait que la v4 apporte
de très nombreux changements dans la partie SQL de l’extension, ce qui en
fait le changement le plus significatif dans la suite PoWA pour cette nouvelle
version. Au moment où j’écris cet article, la quantité de changements apportés
dans cette extension est :
CHANGELOG.md | 14 +
powa--4.0.0dev.sql | 2075 +++++++++++++++++++++-------
powa.c | 44 +-
3 files changed, 1629 insertions(+), 504 deletions(-)L’absence d’upgrade ne devrait pas être un problème en pratique. PoWA est un outil pour analyser les performances, il est fait pour avoir des données avec une grande précision mais un historique très limité. Si vous cherchez une solution de supervision généraliste pour conserver des mois de données, PoWA n’est définitivement pas l’outil qu’il vous faut.
En ce qui concerne les changements à proprement parler, le premier petit changement est que le background worker n’est plus nécessaire pour le fonctionnement de powa-archivist, car il n’est pas utilisé pour le mode distant. Cela signifie qu’un redémarrage de PostgreSQL n’est plus nécessaire pour installer PoWA. Bien évidemment, un redémarrage est toujours nécessaire si vous souhaitez utiliser le mode local, en utilisant le background worker, or si vous voulez installer des extensions additionelles qui nécessitent elles-même un redémarrage.
Ensuite, comme PoWA requiert un peu de configuration (fréquence des snapshot,
rétention des données et ainsi de suite), certaines nouvelles tables sont
ajouter pour permettre de configurer tout ça. La nouvelle table powa_servers
stocke la configuration de toutes les instances distantes dont les données
doivent être stockées sur cette instance. Cette instance PoWA locale est
appelée un serveur repository (qui devrait typiquement être dédiée à
stocker des données PoWA), en opposition aux instances distantes qui sont
les instances que vous voulez monitorer. Le contenu de cette table est tout ce
qu’il y a de plus simple :
\d powa_servers
Table "public.powa_servers"
Column | Type | Collation | Nullable | Default
-----------+----------+-----------+----------+------------------------------------------
id | integer | | not null | nextval('powa_servers_id_seq'::regclass)
hostname | text | | not null |
alias | text | | |
port | integer | | not null |
username | text | | not null |
password | text | | |
dbname | text | | not null |
frequency | integer | | not null | 300
powa_coalesce | integer | | not null | 100
retention | interval | | not null | '1 day'::intervalSi vous avez déjà utilisé PoWA, vous devriez reconnaître la plupart des options de configuration qui sont maintenant stockées ici. Les nouvelles options sont utilisées pour décrire comment se connecter aux instances distances, et peuvent fournir un alias à afficher sur l’UI.
Vous avez également probablement remarqué une colonne password. Stocker un mot de passe en clair dans cette table est une hérésie pour n’importe qui désirant un minimum de sécurité. Ainsi, comme mentionné dans la section sécurité de la documentation de PoWA , vous pouvez stocker NULL pour le champ password et à la place utiliser n’importe laquelle des autres méthodes d’authentification supportée par la libpq (fichier .pgpass, certificat…). Une authentification plus sécurisée est chaudement recommandée pour toute installation sérieuse.
Une autre table, la table powa_snapshot_metas, est également ajoutée pour
stocker quelques métadonnées concernant les informations de snapshot pour
chaque serveur distant.
Table "public.powa_snapshot_metas"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------------------------------------
srvid | integer | | not null |
coalesce_seq | bigint | | not null | 1
snapts | timestamp with time zone | | not null | '-infinity'::timestamp with time zone
aggts | timestamp with time zone | | not null | '-infinity'::timestamp with time zone
purgets | timestamp with time zone | | not null | '-infinity'::timestamp with time zone
errors | text[]Il s’agit tout simplement d’un compteur pour compter le nombre de snapshots effectués, un timestamp pour chaque type d’événement survenu (snapshot, aggrégation et purge) et un tableau de chaîne de caractères pour stocker toute erreur survenant durant le snapshot, afin que l’UI pour l’afficher.
Bien que ces tables soient très simples, une API SQL basique est disponible pour déclarer de nouveaux serveurs et les configurer. 6 fonctions de bases sont disponibles :
powa_register_server(), pour déclarer un nouveau servuer distant, ainsi
que la liste des extensions qui y sont disponiblespowa_configure_server() pour mettre à jour un des paramètres pour le
serveur distant spécifié (en utilisant un paramètre JSON, où la clé est
le nom du paramètre à changer et la valeur la nouvelle valeur à utiliser)powa_deactivate_server() pour désactiver les snapshots pour le serveur
distant spécifiqué (ce qui concrètement positionnera le paramètre
frequency à -1)powa_delete_and_purge_server() pour supprimer le serveur distant
spécifié de la liste des serveurs et supprimer toutes les données associées
aux snapshotspowa_activate_extension(), pour déclarer qu’une nouvelle extension est
disponible sur le serveur distant spécifiépowa_deactivate_extension(), pour spécifier qu’une extension n’est plus
disponible sur le serveur distant spécifiéToute action plus compliquée que ça devra être effectuée en utilisant des requêtes SQL. Heureusement, il ne devrait pas y avoir beaucoup d’autres besoins, et les tables sont vraiment très simple donc cela ne devrait pas poser de soucis. N’hésitez cependant pas à demander de nouvelles fonctions si vous aviez d’autres besoins. Veuillez également noter que l’UI ne vous permet pas d’appeler ces fonctions, puisque celle-ci est pour le moment entièrement en lecture seule.
Puisque les métriques sont maintenant stockées sur une instance PostgreSQL différente, nous avons énormément changé la façon dont les snapshots (récupérer les données fournies par une extensions statistique et les stockées dans le catalogue PoWA de manière à optimiser le stockage) sont effectués.
La liste de toutes les extensions statistiques, ou sources de données, qui
sont disponibles sur un serveur (soit distant soit local) et pour
lesquelles un snapshot devrait être effectué est stockée dans une table
appelée powa_functions:
Table "public.powa_functions"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
srvid | integer | | not null |
module | text | | not null |
operation | text | | not null |
function_name | text | | not null |
query_source | text | | |
added_manually | boolean | | not null | true
enabled | boolean | | not null | true
priority | numeric | | not null | 10Un nouveau champ query_source a été rajouté. Celui-ci fournit le nom de la
fonction source, nécessaire pour la compatibilité d’une extension
statistique
avec les snapshots distants. Cette fonction est utilisée pour exporter les
compteurs fournis par cette extension sur un serveur différent, dans une table
transitoire dédiée. La fonction de snapshot effectuera alors le snapshot
en utilisant automatiquement ces données exportées plutôt que celles fournies
par l’extension statististique locale quand le mode distant est utilisé. Il
est à noter que l’export de ces compteurs ainsi que le snapshot distant est
effectué automatiquement par le nouveau daemon
powa-collector
que je présenterai dans un autre article.
Voici un exemple montant comment PoWA effectue un snapshot distant d’une liste de base données. Comme vous allez le voir, c’est très simple ce qui signifie qu’il est également très simple d’ajouter cette même compatibilité pour une nouvelle extension statistique.
La table transitoire:
Unlogged table "public.powa_databases_src_tmp"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
srvid | integer | | not null |
oid | oid | | not null |
datname | name | | not null |Pour de meilleurs performances, toutes les tables transitoires sont non journalisées (unlogged), puisque leur contenu n’est nécessaire que durant un snapshot et sont supprimées juste après. Dans cet examlple, la table transitoire ne stocke que l’identifiant du serveur distant correspondant à ces données, l’oid ainsi que le nom de chacune des bases de données présentes sur le serveur distant.
Et la fonction source :
CREATE OR REPLACE FUNCTION public.powa_databases_src(_srvid integer,
OUT oid oid, OUT datname name)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
BEGIN
IF (_srvid = 0) THEN
RETURN QUERY SELECT d.oid, d.datname
FROM pg_database d;
ELSE
RETURN QUERY SELECT d.oid, d.datname
FROM powa_databases_src_tmp d
WHERE srvid = _srvid;
END IF;
END;
$function$Cette fonction retourne simplement le contenu de pg_database si les données
locales sont demandées (l’identifiant de serveur 0 est toujours le serveur
local), ou alors le contenu de la table transitoire pour le serveur distant
spécifié.
La fonction de snapshot peut alors facilement effectuer n’importe quel
traitement avec ces données pour le serveur distant voulu. Dans le cas de la
fonction powa_databases_snapshot(), il s’agit simplement de synchroniser la
liste des bases de données, et de stocker le timestamp de suppression si une
base de données qui existait précédemment n’est plus listée.
Pour plus de détails, vous pouvez consulter la documentation concernant l’ajout d’une source de données dans PoWA, qui a été mise à jour pour les spécificités de la version 4.
PoWA 4: nouveautés dans powa-archivist ! was originally published by Julien Rouhaud at rjuju's home on June 05, 2019.
PoWA 4 est disponible en beta.
Le nouveau mode remote est la plus grosse fonctionnalité ajoutée dans PoWA 4, bien qu’il y ait eu d’autres améliorations.
Je vais décrire ici ce que ce nouveau mode implique ainsi que ce qui a changé sur l’UI.
Si de plus amples détails sur le reste des changements apportés dans PoWA 4 vous intéresse, je publierai bientôt d’autres articles sur le sujet.
Pour les plus pressés, n’hésitez pas à aller directement sur la démo v4 de PoWA, très gentiment hébergée par Adrien Nayrat. Aucun authentification n’est requise, cliquez simplement sur “Login”.
Cette fonctionnalité a probablement été la plus fréquemment demandée depuis que PoWA a été publié, en 2014. Et c’est pour de bonnes raisons, car un mode local a quelques inconvénients.
Tout d’abord, voyons comment se présentait l’architecture avec les versions 3 et antérieures. Imaginons une instance contenant 2 bases de données (db1 et db2), ainsi qu’une base de données dédiée à PoWA. Cette base de données dédiée contient à la fois les extensions statistiques nécessaires pour récupérer compteurs de performances actuels ainsi que pour les stocker.
Un background worker est démarré par PoWA, qui est responsable d’effectuer des snapshots et de les stocker dans la base powa dédiée à intervalle réguliers. Ensuite, en utilisant powa-web, vous pouvez consulter l’activité de n’importe laquelle des bases de données locales en effectuant des requêtes sur les données stockées dans la base dédié, et potentiellement en se connectant sur l’une des autres bases de données locales lorsque les données complètes sont nécessaires, par exemple lorsque l’outil de suggestion d’index est utilisé.
Avec la version 4, l’architecture avec une configuration distante change de manière significative:
Vous pouvez voir qu’une base de donnée powa dédiée est toujours nécessaire, mais uniquement pour les extensions statistiques. Les données sont maintenant stockées sur une instance différente. Ensuite, le background worker est remplacé par un nouveau daemon collecteur, qui lit les métriques de performance depuis les serveurs distants, et les stocke sur le serveur repository dédié. Powa-web pourra présenter les données en se connectant sur le serveur repository, ainsi que sur les serveurs distants lorsque des données complètes sont nécessaires.
En résumé, avec le nouveau mode distant ajouté dans cette version 4
L’UI vous accueillera donc maintenant avec une page initiale afin de choisir
lequel des serveurs stockés sur la base de données cible vous voulez
travailler :
La principale raison pour laquelle il a fallu tellement de temps pour apporter ce mode distant est parce que cela apporte beaucoup de complexité, nécessitant une réécriture majeure de PoWA. Nous voulions également ajouter d’abord d’autres fonctionnalités, comme la suggestion globale d’index, avec une validation grâce à hypopg introduit avec PoWA 3.
L’interface graphique est le composant qui a le plus de changements visibles dans cette version 4. Voici les plus changements les plus importants.
Le changement le plus important est bien évidemment le support pour le nouveau mode remote. En conséquence, la première page affichée est maintenant une page de sélection de serveur, affichant tous les serveurs distants enregistrés. Après avoir choisi le serveur distant voulu (ou le serveur local si vous n’utilisez pas le mode distant), toutes les autres pages seront similaires à celles disponibles jusqu’à la version 3, mais afficheront les données pour un serveur distant spécifique uniquement, et bien entendu en récupérant les données depuis la base de données repository, avec en plus de nouvelles informations décrites ci-dessous.
Veuillez notez que puisque les données sont maintenant stockées sur un serveur repository dédié quand le mode remote est utilisé, la majorité de l’UI est utilisable sans se connecter au serveur distant sélectionné. Toutefois, powa-web nécessite toujours de pouvoir se connecter sur le serveur distant quand les données originales sont nécessaires (par exemple, pour la suggestion d’index ou pour montrer des plans avec EXPLAIN). Les mêmes considérations et possibilités concernant l’authentification que pour le nouveau daemon powa-collector (qui sera décrit dans un prochain article) s’appliquent ici.
Quand cette extension est correctement configurée, un nouveau widget timeline apparaîtra, placé entre chaque graph et son aperçu, affichant différents types de changements enregistrés si ceux-ci ont été détectés sur l’intervalle de temps sélectionné. Sur les pages par base de données et par requête, la liste sera également filtrée en fonction de la base de données sélectionnée.
La même timeline sera affichée sur chacun des graphs de chacune des pages, afin de facilement vérifier si ces changements ont eu un impact visible en utilisant les différents graphs.
Veuillez noter que les détails des changements sont affichés au survol de la souris. Vous pouvez également cliquer sur n’importe lequel des événements de la timeline pour figer l’affichage, et tracer une ligne verticale sur le graph associé.
Voici un exemple d’un tel changement de configuration en action :
Veuillez également noter qu’il est nécessaire d’avoir au minimum la version 2.0.0 de pg_track_settings, et que l’extension doit être installée à la fois sur les serveurs distants ainsi que sur le serveur repository.
Quand pg_stat_kcache est configuré, ses informations n’étaient auparavant affichées que sur la page par requête. Les informations sont maintenant également affichées sur les pages par serveur et par base, dans deux nouveaux graphs :
Voici un example de ce nouveau graph System Resources :
Il y avait également un graph Wait Events (disponible quand l’extension pg_wait_sampling est configuée) disponible uniquement sur la page par requête. Ce graph est maintenant disponible sur les pages par serveur et par base également.
Certaines métriques affichées sur l’interface sont assez parlante, mais certaines autres peuvent être un peu obscures. Jusqu’à maintenant, il n’y avait malheureusement aucune documentation pour les métriques. Le problème est maintenant réglé, et tous les graphs ont une icône d’information, qui affichent une description des métriques utilisée dans le graph au survol de la souris. Certains graphs incluent également un lien vers la documentation PoWA de extension statistiques pour les utilisateurs qui désirent en apprendre plus à leur sujet.
Voici un exemple :
Certains problèmes de longues dates ont également été rapportés :
Si un de ces problèmes vous a un jour posé problème, vous serez ravi d’apprendre qu’ils sont maintenant tous corrigés !
Cette 4ème version de PoWA représente un temps de développement très important, de nombreuses améliorations sur la documentation et beaucoup de tests. Nous somme maintenant assez satisfaits, mais il est possible que nous ayons ratés certains bugs. Si vous vous intéressez à ce projet, j’espère que vous essaierez de tester cette beta, et si besoin n’hésitez pas à nous remonter un bug!
PoWA 4 apporte un mode remote, disponible en beta ! was originally published by Julien Rouhaud at rjuju's home on May 17, 2019.
A partir de PostgreSQL 12, les colonnes oid des
tables systèmes perdront leur nature “spéciale”, et la clause
optionnelle WITH OIDS disparaîtra de CREATE TABLE. Concrètement
ça veut dire que quand on fait select * de ces tables, ces colonnes
seront maintenant visibles, ainsi que via
information_schema.columns, ou encore avec \d sous psql.
Jusqu’à présent elles étaient cachées, comme les colonnes systèmes xmin,
xmax, etc…
Le commit dans les sources indique la motivation de ce changement:
author Andres Freund <andres (at) anarazel (dot) de>
Wed, 21 Nov 2018 01:36:57 +0200 (15:36 -0800)
[…]
Remove WITH OIDS support, change oid catalog column visibility.
[…]
The fact that the oid column was not an ordinary column necessitated a significant amount of special case code to support oid columns. That already was painful for the existing, but upcoming work aiming to make table storage pluggable, would have required expanding and duplicating that “specialness” significantly.
En résumé, les caractéristiques spéciales des OIDs compliquait des évolutions importantes, en l’occurrence les formats de tables à la carte comme le très attendu zheap.
Si on regarde des années en arrière, ce changement peut être mis en perspective par rapport à d’autres, qui vont aussi dans le sens de l’obsolescence des OIDs:
oid devient optionnelle.default_with_oids est créé.default_with_oids passe à false par défaut.Mais pourquoi ces colonnes spéciales avaient-elles été inventées au départ? A l’origine, l’OID est lié à l’orientation objet.
Au milieu des années 80, le concept d’orientation objet faisait surface, avec des langages comme C++ qui en était à ses débuts. Dans les bases de données, il y avait cette idée que l’avenir était peut-être à considérer les données en tant qu’objets.
C’est donc assez naturellement que dans les premières versions de Postgres développées à l’Université de Berkeley, l’orientation objet était une composante importante du projet.
Dans les langages de programmation, le concept OO a été un succès avec notamment C++ ou Java par exemple. Mais concernant les bases de données, le concept n’a pas pris, ou en tout cas est resté cantonné à un usage de niche.
Lorsque la communauté de développeurs a repris Postgres au milieu des années 90 pour le faire évoluer en tant que moteur SQL, elle a hérité de fonctionnalités clairement influencées par le paradigme objet, notamment:
Mais la poursuite de la vision orientée objet n’a pas vraiment intéressé les développeurs depuis 1996, suivant en ça les autres communautés des bases de données. Ils ont préféré se concentrer sur d’autres objectifs, comme l’amélioration des performances, de la robustesse, et la conformité au standard SQL, lequel continuant d’ailleurs à évoluer.
Quoiqu’il en soit, la fonctionnalité où une ligne est considérée comme une instance de classe implique l’existence d’un identifiant au-delà des colonnes utilisateur, pour différencier une instance d’une autre. Par analogie avec les langages de programmation, où des classes sont instanciées en mémoire, une instance sera distincte d’une autre au minimum par le fait que leurs adresses en mémoire sont différentes. L’OID, c’est en quelque sorte l’adresse de l’instance de classe sérialisée sous une forme stockable sur disque.
Les anciennes documentations encore en ligne expliquent ce point de vue:
Concepts dans PostgreSQL 6.4 (1998):
The fundamental notion in Postgres is that of a class, which is a named collection of object instances. Each instance has the same collection of named attributes, and each attribute is of a specific type. Furthermore, each instance has a permanent object identifier (OID) that is unique throughout the installation. Because SQL syntax refers to tables, we will use the terms table and class interchangeably. Likewise, an SQL row is an instance and SQL columns are attributes.
Un exemple était donné dans Populating a Class with Instances:
The insert statement is used to populate a class with instances:
INSERT INTO weather VALUES (‘San Francisco’, 46, 50, 0.25, ‘11/27/1994’)
[…]
As previously discussed, classes are grouped into databases, and a collection of databases managed by a single postmaster process constitutes an installation or site.
C’est à partir de la version 7.1, sortie en 2001, que la référence aux classes disparaît, et qu’on parle de “Creating a New Table” au lieu de “Creating a New Class”.
Dans le catalogue il reste quelques vestiges de ce passé, comme la
table des tables qui s’appelle toujours pg_class
(mais il y a une vue pg_tables).
Les OIDs comme colonnes “normales” restent utilisées dans le catalogue
comme clef synthétique primaire partout où c’est utile.
Dans PostgreSQL-12, 39 tables ont un champ nommé oid et 278 colonnes
sont de type oid (contre 39 et 274 en version 11)
postgres=# SELECT
count(*) filter (where attname = 'oid') as "OID as name",
count(*) filter (where atttypid = 'oid'::regtype) as "OID as type"
FROM pg_attribute JOIN pg_class ON (attrelid=oid) WHERE relkind='r';
OID as name | OID as type
-------------+-------------
39 | 278
Par ailleurs, les OIDs restent essentiels dans la gestion des objets larges, qui
stockent des contenus binaires segmentés automatiquement, puisque l’API expose
ces objets exclusivement via leurs OIDs.
Le seul changement visible par les utilisateur en v12 est que
pg_largeobject_metadata.oid devient visible directement, mais un utilisateur n’a
pas vraiment besoin de requêter cette table s’il utilise l’API.
Les OIDs inférieurs à 16384 sont réservés au système de base comme avant.
Le générateur de valeurs pour les OIDs est un compteur au niveau du cluster, donc les valeurs sont distribuées séquentiellement comme si c’était une séquence commune à toutes les bases.
Ce qui donne par exemple:
postgres=# create database db1;
CREATE DATABASE
postgres=# \lo_import .bashrc
lo_import 16404
postgres=# \c db1
You are now connected to database "db1" as user "daniel".
db1=# \lo_import .bashrc
lo_import 16405
Ce comportement où un doublon d’OID est évité alors que les objets larges des deux bases sont totalement indépendants est possiblement un reliquat de l’époque où chaque OID était “unique throughout the installation” comme cité précédemment dans le passage de documentation de la 6.4.
Cette contrainte globale d’unicité a disparu il y a longtemps,
mais le générateur a conservé ce comportement anti-collision qui
fait que ça ne pourra arriver qu’après un cycle de plus de 4 milliards
de valeurs allouées dans le cluster.
(le compteur d’OID étant un entier de 32 bits non signé
qui repart à 16384 quand il atteint 2^32).
Les plus curieux peuvent aller consulter les commentaires de la fonction
GetNewOidWithIndex() pour voir comment le code gère
un possible conflit d’unicité avec un OID pré-existant dans une même table,
ainsi que la fonction SQL pg_nextoid qui est volontairement non mentionnée
dans la documentation.
Ajoutés dans PostgreSQL 9.3, les data checksums peuvent aider à détecter les corruptions de données survenant sur votre stockage.
Les checksums sont activés si l’instance a été initialisée en utilisant initdb
--data-checksums (ce qui n’est pas le comportement par défaut), ou s’ils ont
été activés après en utilisant la nouvelle utilitaire
activated afterwards with the new
pg_checksums
également ajouté dans PostgreSQL
12.
Quand les checksums sont ativés, ceux-ci sont écrits à chaque fois qu’un bloc de données est écrit sur disque, et vérifiés à chaque fois qu’un bloc est lu depuis le disque (ou depuis le cache du système d’exploitation). Si la vérification échoue, une erreur est remontée dans les logs. Si le bloc était lu par un processus client, la requête associée échouera bien évidemment, mais si le bloc était lu par une opération BASE_BACKUP (tel que pg_basebackup), la commande continuera à s’exécuter. Bien que les data checksums ne détecteront qu’un sous ensemble des problèmes possibles, ils ont tout de même une certaine utilisé, surtout si vous ne faites pas confiance à votre stockage.
Jusqu’à PostgreSQL 11, les erreurs de validation de checksum ne pouvaient être trouvées qu’en cherchant dans les logs, ce qui n’est clairement pas pratique si vous voulez monitorer de telles erreurs.
Pour rendre la supervision des erreurs de checksum plus simple, et pour aider
les utilisateurs à réagir dès qu’un tel problème survient, PostgreSQL 12 ajoute
de nouveaux compteurs dans la vue pg_stat_database :
commit 6b9e875f7286d8535bff7955e5aa3602e188e436
Author: Magnus Hagander <magnus@hagander.net>
Date: Sat Mar 9 10:45:17 2019 -0800
Track block level checksum failures in pg_stat_database
This adds a column that counts how many checksum failures have occurred
on files belonging to a specific database. Both checksum failures
during normal backend processing and those created when a base backup
detects a checksum failure are counted.
Author: Magnus Hagander
Reviewed by: Julien Rouhaud
commit 77bd49adba4711b4497e7e39a5ec3a9812cbd52a
Author: Magnus Hagander <magnus@hagander.net>
Date: Fri Apr 12 14:04:50 2019 +0200
Show shared object statistics in pg_stat_database
This adds a row to the pg_stat_database view with datoid 0 and datname
NULL for those objects that are not in a database. This was added
particularly for checksums, but we were already tracking more satistics
for these objects, just not returning it.
Also add a checksum_last_failure column that holds the timestamptz of
the last checksum failure that occurred in a database (or in a
non-dataabase file), if any.
Author: Julien Rouhaud <rjuju123@gmail.com>
commit 252b707bc41cc9bf6c55c18d8cb302a6176b7e48
Author: Magnus Hagander <magnus@hagander.net>
Date: Wed Apr 17 13:51:48 2019 +0200
Return NULL for checksum failures if checksums are not enabled
Returning 0 could falsely indicate that there is no problem. NULL
correctly indicates that there is no information about potential
problems.
Also return 0 as numbackends instead of NULL for shared objects (as no
connection can be made to a shared object only).
Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by: Robert Treat <rob@xzilla.net>
Ces compteurs reflèteront les erreurs de validation de checksum à la fois pour les processus clients et pour l’activité BASE_BACKUP, par base de données.
rjuju=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
[...]
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
[...]
stats_reset | timestamp with time zone | | |La colonne checksum_failures montrera un nombre cumulé d’erreurs, et la
colonne checksum_last_failure montrera l’horodatage de la dernière erreur de
validation sur la base de données (NULL si aucune erreur n’est jamais
survenue).
Pour éviter toute confusion (merci à Robert Treat pour l’avoir signalé), ces deux colonnes retourneront toujours NULL si les data checkums ne sont pas activés, afin qu’on ne puisse pas croire que les checksums sont toujours vérifiés avec succès.
Comme effet de bord, pg_stat_database montrera maintenant également les
statistiques disponibles pour les objets partagés (tels que la table
pg_database par exemple), dans une nouvelle ligne pour laquelle datid vaut
0, et datname vaut NULL.
Une sonde dédiée est également déjà
planifiée dans
check_pgactivity !
Une sonde dédiée est également déjà
disponible
dans check_pgactivity !
Nouveauté pg12: Statistiques sur les erreurs de checkums was originally published by Julien Rouhaud at rjuju's home on April 18, 2019.
J’entends régulièrement des complaintes sur la quantité d’espace disque gâchée par PostgreSQL pour chacune des lignes qu’il stocke. Je vais essayer de montrer ici quelques astuces pour minimiser cet effet, afin d’avoir un stockage plus efficace.
Si vous n’avez pas de table avec plus que quelques centaines de millions de lignes, il est probable que ce n’est pas un problème pour vous.
Pour chaque ligne stockée, postgres conservera quelques données additionnelles pour ses propres besoins. C’est documenté ici. La documentation indique :
| Field | Type | Length | Description |
|---|---|---|---|
| t_xmin | TransactionId | 4 bytes | XID d’insertion |
| t_xmax | TransactionId | 4 bytes | XID de suppresion |
| t_cid | CommandId | 4 bytes | CID d’insertion et de suppression (surcharge avec t_xvac) |
| t_xvac | TransactionId | 4 bytes | XID pour l’opération VACUUM déplaçant une version de ligne |
| t_ctid | ItemPointerData | 6 bytes | TID en cours pour cette version de ligne ou pour une version plus récente |
| t_infomask2 | uint16 | 2 bytes | nombre d’attributs et quelques bits d’état |
| t_infomask | uint16 | 2 bytes | différents bits d’options (flag bits) |
| t_hoff | uint8 | 1 byte | décalage vers les données utilisateur |
Ce qui représente 23 octets sur la plupart des architectures (il y a soit t_cid soit t_xvac).
Vous pouvez d’ailleurs consulter une partie de ces champs grâce aux colonnes cachées présentes dans n’importe quelle table en les ajoutant dans la partie SELECT d’une requête, ou en cherchant pour les numéros d’attribut négatifs dans le catalogue pg_attribute :
# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
# SELECT xmin, xmax, id FROM test LIMIT 1;
xmin | xmax | id
------+------+----
1361 | 0 | 1
# SELECT attname, attnum, atttypid::regtype, attlen
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE relname = 'test'
ORDER BY attnum;
attname | attnum | atttypid | attlen
----------+--------+----------+--------
tableoid | -7 | oid | 4
cmax | -6 | cid | 4
xmax | -5 | xid | 4
cmin | -4 | cid | 4
xmin | -3 | xid | 4
ctid | -1 | tid | 6
id | 1 | integer | 4Si vous comparez ces champs avec le tableau précédent, vous pouvez constater que toutes ces colonnes ne sont pas stockées sur disque. Bien évidemment, PostgreSQL ne stocke pas l’oid de la table pour chaque ligne. Celui-ci est ajouté après, lors de la construction d’une ligne.
Si vous voulez plus de détails techniques, vous pouvez regarder htup_detail.c, en commençant par TupleHeaderData struct.
Puisque ce surcoût est fixe, plus la taille des lignes croît plus il devient négligeable. Si vous ne stocker qu’une simple colonne de type intt (4 octets), chaque ligne nécessitera :
23B + 4B = 27Bsoit 85% de surcoût, ce qui est plutôt horrible.
D’une autre côté, si vous stockez 5 integer, 3 bigint et 2 colonnes de type texte (disons environ 80 octets en moyenne), cela donnera :
23B + 5*4B + 3*8B + 2*80B = 227BC’est “seulement” 10% de surcoût.
L’idée est de stocker les même données, mais avec moins d’enregistrements. Comment faire ? En aggrégeant les données dans des tableaux. Plus vous mettez d’enregistrements dans un seul tableau, plus vous minimiserez le surcoût. Et si vous aggrégez suffisamment de données, vous pouvez bénéficier d’une compression entièrement transparente grâce au mécanisme de TOAST.
Voyons ce que cela donne avec une table ne disposant que d’une seule colonne, avec 10 millions de lignes :
# CREATE TABLE raw_1 (id integer);
# INSERT INTO raw_1 SELECT generate_series(1,10000000);
# CREATE INDEX ON raw_1 (id);Les données utilisateur ne devrait nécessiter que 10M * 4 octets, soit environ 30 Mo, alors que cette table pèse 348 Mo. L’insertion des données prend environ 23 secondes.
NOTE : Si vous faites le calcul, vous trouverez que le surcoût est d’un peu plus que 32 octets par ligne, pas 23 octets. C’est parce que chaque bloc de données a également un surcoût, une gestion des colonnes NULL ainsi que des contraintes d’alignement. Si vous voulez plus d’informations à ce sujet, je vous recommande de regarder cette présentation
Comparons maintenant cela avec la version aggrégées des même données :
# CREATE TABLE agg_1 (id integer[]);
# INSERT INTO agg_1 SELECT array_agg(i)
FROM generate_series(1,10000000) i
GROUP BY i % 2000000;
# CREATE INDEX ON agg_1 (id);Cette requête insèrera 5 éléments par ligne. J’ai fait le même test avec 20, 100, 200 et 1000 éléments par ligne. Les résultats sont les suivants :
NOTE : La taille pour 1000 éléments par ligne est un peu plus importante que pour la valeur précédents. C’est parce que c’est le seul qui implique une taille suffisamment importante pour être TOAST-ée, mais pas assez pour être compressée. On peut donc voir ici un peu de surcoût lié au TOAST.
Jusqu’ici tout va bien, on peut voir de plutôt bonnes améliorations à la fois sur la taille et sur le temps d’insertion, même pour les tableaux les plus petits. Voyons maintenant l’impact pour récupérer des lignes. Je testerai la récupération de toutes les lignes, ainsi qu’une seule ligne au moyen d’un parcours d’index (j’ai utilisé pour les tests EXPLAIN ANALYZE afin de minimiser le temps passé par psql à afficher les données) : psql):
# SELECT id FROM raw_1;
# CREATE INDEX ON raw_1 (id);
# SELECT * FROM raw_1 WHERE id = 500;Pour correctement indexer le tableau, nous avons besoin d’un index GIN. Pour récupérer les valeurs de toutes les données aggrégées, il est nécessaire d’appeler unnest() sur le tableau, et pour récupérer un seul enregistrement il faut être un peu plus créatif :
# SELECT unnest(id) AS id FROM agg_1;
# CREATE INDEX ON agg_1 USING gin (id);
# WITH s(id) AS (
SELECT unnest(id)
FROM agg_1
WHERE id && array[500]
)
SELECT id FROM s WHERE id = 500;Voici le tableau comparant les temps de création de l’index ainsi que la taille de celui-ci, pour chaque dimension de tableau :
L’index GIN est un peu plus que deux fois plus volumineux que l’index btree, et si on accumule la taille de la table à la taille de l’index, la taille totale est presque identique avec ou sans aggrégation. Ce n’est pas un gros problème puisque cet exemple est très naïf, et nous verrons juste après comme éviter d’avoir recours à un index GIN pour conserver une taille totale faible. De plus, l’index est bien plus lent à créer, ce qui signifie qu’INSERT sera également plus lent.
Voici le tableau comparant le temps pour récupérer toutes les lignes ainsi qu’une seule ligne :
Récupérer toutes les lignes n’est probablement pas un exemple intéressant, mais il est intéressant de noter que dès que le tableau contient suffisamement d’éléments cela devient plus efficace que faire la même chose avec la table originale. Nous voyons également que récuérer un seul élément est bien plus rapide qu’avec l’index btree, grâce à l’efficacité de GIN. Ce n’est pas testé ici, mais puisque seul les index btree sont nativement triés, si vous devez récupérer un grand nombre d’enregistrements triés, l’utilisation d’un index GIN nécessitera un tri supplémentaire, ce qui sera bien plus lent qu’un simple parcours d’index btree.
Maintenant que nous avons vu les bases, voyons comment aller un peu plus loin : aggréger plus d’une colonne et éviter d’utiliser trop d’espce disque (et de ralentissements à l’écriture) du fait d’un index GIN. Pour cela, je vais présenter comme PoWA stocke ses données.
Pour chaque source de données collectée, deux tables sont utilisées : une pour les données historiques et aggrégées, ainsi qu’une pour les données courantes. Ces tables stockent les données dans un type de données personnalisé plutôt que des colonnes. Voyons les tables liées à l’extension pg_stat_statements :
Le type de données, grosso modo tous les compteurs présents dans pg_stat_statements ainsi que l’horodatage associé à l’enregistrement :
powa=# \d powa_statements_history_record
Composite type "public.powa_statements_history_record"
Column | Type | Modifiers
---------------------+--------------------------+-----------
ts | timestamp with time zone |
calls | bigint |
total_time | double precision |
rows | bigint |
shared_blks_hit | bigint |
shared_blks_read | bigint |
shared_blks_dirtied | bigint |
shared_blks_written | bigint |
local_blks_hit | bigint |
local_blks_read | bigint |
local_blks_dirtied | bigint |
local_blks_written | bigint |
temp_blks_read | bigint |
temp_blks_written | bigint |
blk_read_time | double precision |
blk_write_time | double precision |La table pour les données courrante stocke l’identifieur unique de pg_stat_statements (queryid, dbid, userid), ainsi qu’un enregistrement de compteurs :
powa=# \d powa_statements_history_current
Table "public.powa_statements_history_current"
Column | Type | Modifiers
---------+--------------------------------+-----------
queryid | bigint | not null
dbid | oid | not null
userid | oid | not null
record | powa_statements_history_record | not nullLa table pour les données aggrégées contient le même identifieur unique, un tableau d’enregistrements ainsi que quelques champs spéciaux :
powa=# \d powa_statements_history
Table "public.powa_statements_history"
Column | Type | Modifiers
----------------+----------------------------------+-----------
queryid | bigint | not null
dbid | oid | not null
userid | oid | not null
coalesce_range | tstzrange | not null
records | powa_statements_history_record[] | not null
mins_in_range | powa_statements_history_record | not null
maxs_in_range | powa_statements_history_record | not null
Indexes:
"powa_statements_history_query_ts" gist (queryid, coalesce_range)Nous stockons également l’intervalle d’horodatage (coalesce_range) contenant tous les compteurs aggrégés dans la ligne, ainsi que les valeurs minimales et maximales de chaque compteurs dans deux compteurs dédiés. Ces champs supplémentaires ne consomment pas trop d’espace, et permettent une indexation ainsi qu’un traitement très efficace, basé sur les modèles d’accès aux données de l’application associée.
Cette table est utilisée pour savoir combien de ressources ont été utilisée par une requête sur un intervalle de temps donné. L’index GiST ne sera pas très gros puisqu’il n’indexe que deux petites valeus pour X compteurs aggrégés, et trouvera les lignes correspondant à une requête et un intervalle de temps données de manière très efficace.
Ensuite, calculer les ressources consommées peut être fait de manière très efficace, puisque les compteurs de pg_stat_statements sont strictement monotones. L’algorithme pourrait être :
NOTE : Dans les faits, l’interface de PoWA dépilera toujours tous les enregistrements contenus dans l’intervalle de temps demandé, puisque l’interface est faite pour montrer l’évolution de ces compteurs sur un intervalle de temps relativement réduit, mais avec une grande précision. Heureusement, dépiler les tableaux n’est pas si coûteux que ça, surtout en regard de l’espace disque économisé.
Et voici la taille nécessaire pour les valeurs aggrégées et non aggrégées. Pour cela j’ai laissé PoWA générer 12 331 366 enregistrements (en configurant une capture toutes les 5 secondes pendant quelques heures, et avec l’aggrégation par défaut de 100 enregistrements par lignes), et créé un index btree sur (queryid, ((record).ts) pour simuler l’index présent sur les tables aggrégées :
Vous trouvez aussi que c’est plutôt efficace ?
Il y a quelques limitations avec l’aggrégation d’enregistrements. Si vous faites ça, vous ne pouvez plus garantir de contraintes telles que des clés étrangères ou contrainte d’unicité. C’est donc à utiliser pour des données non relationnelles, telles que des compteurs ou des métadonnées.
L’utilisation de type de données personnalisés vous permet de faire des choses sympathiques, comme définir des opérateurs personnalisés. Par exemple, la version 3.1.0 de PoWA fournit deux opérateurs pour chacun des types de données personnalisé définis :
Vous pouvez donc faire très facilement des requêtes du genre :
# SELECT (record - lag(record) over()).*
FROM from powa_statements_history_current
WHERE queryid = 3589441560 AND dbid = 16384;
intvl | calls | total_time | rows | ...
-----------------+--------+------------------+--------+ ...
<NULL> | <NULL> | <NULL> | <NULL> | ...
00:00:05.004611 | 5753 | 20.5570000000005 | 5753 | ...
00:00:05.004569 | 1879 | 6.40500000000047 | 1879 | ...
00:00:05.00477 | 14369 | 48.9060000000006 | 14369 | ...
00:00:05.00418 | 0 | 0 | 0 | ...
# SELECT (record / lag(record) over()).*
FROM powa_statements_history_current
WHERE queryid = 3589441560 AND dbid = 16384;
sec | calls_per_sec | runtime_per_sec | rows_per_sec | ...
--------+---------------+------------------+--------------+ ...
<NULL> | <NULL> | <NULL> | <NULL> | ...
5 | 1150.6 | 4.1114000000001 | 1150.6 | ...
5 | 375.8 | 1.28100000000009 | 375.8 | ...
5 | 2873.8 | 9.78120000000011 | 2873.8 | ...Si vous êtes intéressés sur la façon d’implémenter de tels opérateurs, vous pouvez regarder l’implémentation de PoWA.
Vous connaissez maintenant les bases pour éviter le surcoût de stockage par ligne. En fonction de vos besoins et de la spécificité de vos données, vous devriez pouvoir trouver un moyen d’aggréger vos données, en ajoutant potentiellement quelques colonnes supplémentaires, afin de conserver de bonnes performances et économiser de l’espace disque.
Minimiser le surcoût de stockage par ligne was originally published by Julien Rouhaud at rjuju's home on April 06, 2019.
Vous avez la possibilité de visualiser les Wait Events dans PoWA 3.2.0 grâce à l’extension pg_wait_sampling extension.
Les wait events sont une fonctionnalité connues, et bien utiles, dans de
nombreux moteurs de base de données relationnelles. Ceux-ci ont été ajouté à
PostgreSQL 9.6, il
y a maintenant quelques versions. Contrairement à la plupart des autres
statistiques exposées par PostgreSQL, ceux-ci ne sont qu’une vision à un
instant donné des événements sur lesquels les processus sont en attente, et non
pas des compteurs cumulés. Vous pouvez consulter cette information en
utilisant la vue pg_stat_activity, par exemple :
=# SELECT datid, pid, wait_event_type, wait_event, query FROM pg_stat_activity;
datid | pid | wait_event_type | wait_event | query
--------+-------+-----------------+---------------------+-------------------------------------------------------------------------
<NULL> | 13782 | Activity | AutoVacuumMain |
16384 | 16615 | Lock | relation | SELECT * FROM t1;
16384 | 16621 | Client | ClientRead | LOCK TABLE t1;
847842 | 16763 | LWLock | WALWriteLock | END;
847842 | 16764 | Lock | transactionid | UPDATE pgbench_branches SET bbalance = bbalance + 1229 WHERE bid = 1;
847842 | 16766 | LWLock | WALWriteLock | END;
847842 | 16767 | Lock | transactionid | UPDATE pgbench_tellers SET tbalance = tbalance + 3383 WHERE tid = 86;
847842 | 16769 | Lock | transactionid | UPDATE pgbench_branches SET bbalance = bbalance + -3786 WHERE bid = 10;
[...]Dans cet exemple, nous voyons que le //wait event// pour le pid 16615 est un
Lock sur une Relation. En d’autre terme, la requête est bloquée en
attente d’un verrou lourd, alors que le pid 16621, qui clairement détient le
verrou, est inactif en attente de commandes du client. Il s’agit
d’informations qu’il était déjà possible d’obtenir avec les anciennes versions,
bien que cela se faisait d’une autre manière. Mais plus intéressant, nous
pouvons également voir que le //wait event// pour le pid 16766 est un
LWLock, c’est-à-dire un Lightweight Lock, ou verrou léger. Les verrous
légers sont des verrous internes et transitoires qu’il était auparavant
impossible de voir au niveau SQL. dans cet exemple, la requête est en attente
d’un WALWriteLock, un verrou léger principalement utilisé pour contrôler
l’écriture dans les tampons des journaux de transaction. Une liste complète
des //wait events// disponible est disponible sur la documentation
officielle.
Ces informations manquaient curellement et sont bien utiles pour diagnostiquer
les causes de ralentissement. Cependant, n’avoir que la vue de ces //wait
events// à l’instant présent n’est clairement pas suffisant pour avoir une
bonne idée de ce qu’il se passe sur le serveur. Puisque la plupart des //wait
events// sont pas nature très éphémères, ce dont vous avez besoin est de les
échantilloner à une fréquence élevée. Tenter de faire cet échantillonage avec
un outil externe, même à une seconde d’intervalle, n’est généralement pas
suffisant. C’est là que l’extension
pg_wait_sampling apporte
une solution vraiment brillante. Il s’agit d’une extension écrite par
Alexander Korotkov et Ildus Kurbangaliev. Une
fois activée (il est nécessaire de la configurer dans le
shared_preload_libraries, un redémarrage de l’instance est donc nécessaire),
elle échantillonera en mémoire partagée les //wait events// toutes les 10
ms (par défaut), et aggèrega également les compteurs par type de //wait
event// (wait_event_type), //wait event// et queryid (si
pg_stat_statements est également acctivé). Pour plus de détails sur la
configuration et l’utilisation de cette extension, vous pouvez consulter le
README de
l’extension.
Comme tout le travail est fait en mémoire au moyen d’une extension écrite en C,
c’est très efficace. De plus, l’implémentation est faite avec très peu de
verouillage, le surcoût de cette extension devrait être presque négligable.
J’ai fait quelques tests de performance sur mon pc portable (je n’ai
malheureusement pas de meilleure machine sur laquelle tester) avec un
pgbench en
lecture seule où toutes les données tenaient dans le cache de PostgreSQL
(shared_buffers), avec 8 puis 90 clients, afin d’essayer d’avoir le maximum
de surcoût possible. La moyenne sur 3 tests était d’environ 1% de surcoût,
avec des fluctuations entre chaque test d’environ 0.8%.
Ainsi, grâce à cette extension, nous avons à notre disposition une vue cumulée et extrêmement précise des //wait events//. C’est très bien, mais comme toutes les autres statistiques cumulées dans PostgreSQL, vous devez échantillonner ces compteurs régulièrement si vous voulez pouvoir être capable de savoir ce qu’il s’est passé à un certain moment dans le passé, comme c’est d’ailleurs précisé dans le README de l’extension :
[…] Waits profile. It’s implemented as in-memory hash table where count of samples are accumulated per each process and each wait event (and each query with
pg_stat_statements). This hash table can be reset by user request. Assuming there is a client who periodically dumps profile and resets it, user can have statistics of intensivity of wait events among time.
C’est exactement le but de PoWA: sauvegarder les compteurs statistiques de manière efficace, et les afficher sur une interface graphique.
PoWA 3.2 détecte automatiquement si l’extension pg_wait_sampling est déjà présente ou si vous l’installez ultérieurement, et commencera à collecter ses données, vous donnant une vue vraiment précise des //wait events// dans le temps sur vos bases de données !
Les données sont centralisée dans des tables PoWA classiques,
powa_wait_sampling_history_current pour les 100 dernières collectes (valeur
par défaut de powa.coalesce), et les valeurs plus anciennes sont aggrégées
dans la table powa_wait_sampling_history, avec un historique allant jusqu’à
une période définie par powa.retention. Par exemple, voici une requête
simple affichant les 20 premiers changements survenus au sein des 100 premiers
instantanés :
WITH s AS (
SELECT (record).ts, queryid, event_type, event,
(record).count - lag((record).count)
OVER (PARTITION BY queryid, event_type, event ORDER BY (record).ts)
AS events
FROM powa_wait_sampling_history_current w
JOIN pg_database d ON d.oid = w.dbid
WHERE d.datname = 'bench'
)
SELECT *
FROM s
WHERE events != 0
ORDER BY ts ASC, event DESC
LIMIT 20;
ts | queryid | event_type | event | events
-------------------------------+----------------------+------------+----------------+--------
2018-07-09 10:44:08.037191+02 | -6531859117817823569 | LWLock | pg_qualstats | 1233
2018-07-09 10:44:28.035212+02 | 8851222058009799098 | Lock | tuple | 4
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | Lock | tuple | 149
2018-07-09 10:44:28.035212+02 | 8851222058009799098 | Lock | transactionid | 193
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | Lock | transactionid | 1143
2018-07-09 10:44:28.035212+02 | -6531859117817823569 | LWLock | pg_qualstats | 1
2018-07-09 10:44:28.035212+02 | 8851222058009799098 | LWLock | lock_manager | 2
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | LWLock | lock_manager | 3
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | LWLock | buffer_content | 2
2018-07-09 10:44:48.037205+02 | 8851222058009799098 | Lock | tuple | 14
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | Lock | tuple | 335
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | Lock | transactionid | 2604
2018-07-09 10:44:48.037205+02 | 8851222058009799098 | Lock | transactionid | 384
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | LWLock | lock_manager | 13
2018-07-09 10:44:48.037205+02 | 8851222058009799098 | LWLock | lock_manager | 4
2018-07-09 10:44:48.037205+02 | 8221555873158496753 | IO | DataFileExtend | 1
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | LWLock | buffer_content | 4
2018-07-09 10:45:08.032938+02 | 8851222058009799098 | Lock | tuple | 5
2018-07-09 10:45:08.032938+02 | -6860707137622661878 | Lock | tuple | 312
2018-07-09 10:45:08.032938+02 | -6860707137622661878 | Lock | transactionid | 2586
(20 rows)NOTE: Il y a également une version par base de données de ces valeurs pour
un traitement plus efficace au niveau des basesn dans les tables
powa_wait_sampling_history_current_db et powa_wait_sampling_history_db
Et ces données sont visibles avec l’interface powa-web. Voici quelques exemples d’affichage des //wait events// tels qu’affichés par PoWA avec un simple pgbench :
Cette fonctionnalité est disponible depuis la version 3.2 de PoWA. J’espère pouvoir afficher plus de vues de ces données dans le futur, en incluant d’autres graphes, puisque toutes les données sont déjà disponibles en bases. Également, si vous êtes un développeur python ou javascript, les contributions sont toujours bienvenues!
Support des Wait Events pour PoWA was originally published by Julien Rouhaud at rjuju's home on April 02, 2019.
La recherche plein texte se base sur une transformation
en type tsvector du texte brut initial. Par exemple:
test=> select to_tsvector('french', 'Voici notre texte à indexer.');
to_tsvector
-----------------------------
'index':5 'text':3 'voic':1
Ce résultat est une suite ordonnée de lexèmes, avec leurs positions relatives dans le texte initial, qui est obtenue schématiquement par cette chaîne de traitement:
Texte brut => Analyseur lexical (parser) => Dictionnaires configurables => tsvector
Dès qu’on a un volume significatif, on indexe aussi ces vecteurs avec un index GIN ou GIST pour accélérer les recherches.
En SQL on peut inspecter le travail de cette chaîne dans le détail
avec la fonction ts_debug:
test=> select * from ts_debug('french', 'Voici notre texte à indexer.');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-------------------+---------+---------------+-------------+---------
asciiword | Word, all ASCII | Voici | {french_stem} | french_stem | {voic}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | notre | {french_stem} | french_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | texte | {french_stem} | french_stem | {text}
blank | Space symbols | | {} | |
word | Word, all letters | à | {french_stem} | french_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | indexer | {french_stem} | french_stem | {index}
blank | Space symbols | . | {} | |
L’analyseur lexical a découpé le texte en jetons (colonne token), qui ont chacun un type (colonne alias). Puis ces jetons, en fonction de leurs types, sont soumis en entrée à des dictionnaires, qui en ressortent des lexèmes, qui peuvent être vides pour éliminer le terme du vecteur final.
Dans l’exemple ci-dessus, les espaces et la ponctuation ont été
éliminés parce qu’ils ne sont pas associés à un dictionnaire, les
termes communs (“à”, “notre”) ont été éliminés par le dictionnaire
french_stem, et “Voici”, “texte” et “indexer” ont été
normalisés et réduits à leur racine supposée par ce même dictionnaire.
Parfois on a affaire à du texte brut qui n’est pas “propre”, qui contient des bribes de texte parasites. Par exemple dans l’indexation de messages e-mail, des messages mal formatés peuvent laisser apparaître des parties encodées en base64 dans ce qui devrait être du texte en clair. Et quand il s’agit d’une pièce jointe, ça peut occuper une place considérable. Si on examine le passage d’une ligne de contenu de ce genre dans la chaîne de traitement plein texte, voici ce qu’on peut voir:
=# \x
=# select * from ts_debug('french', 'Q29uc2lzdGVuY3kgYWxzbyBtZWFucyB0aGF0IHdoZW4gQWxpY2UgYW5kIEJvYiBhcmUgcnVubmlu');
-[ RECORD 1 ]+-------------------------------------------------------------------------------
alias | numword
description | Word, letters and digits
token | Q29uc2lzdGVuY3kgYWxzbyBtZWFucyB0aGF0IHdoZW4gQWxpY2UgYW5kIEJvYiBhcmUgcnVubmlu
dictionaries | {simple}
dictionary | simple
lexemes | {q29uc2lzdgvuy3kgywxzbybtzwfucyb0agf0ihdozw4gqwxpy2ugyw5kiejvyibhcmugcnvubmlu}
Ce texte est donc analysé comme un seul jeton de type numword, et produit un seul long lexème, vu que ce jeton est associé au dictionnaire simple qui se contente de convertir le terme en minuscules. Se pose la question de savoir si on ne pourrait pas éviter de polluer le vecteur avec ces termes inutiles.
Une idée simple est de considérer que les jetons de grande taille sont inintéressants et supprimables systématiquement. Même s’il y a des mots exceptionnellement longs dans certaines langues comme l’allemand Rindfleischetikettierungsüberwachungsaufgabenübertragungsgesetz avec ses 63 caractères (!), on peut imaginer se fixer une limite de taille au-delà de laquelle la probabilité que le mot soit un parasite est suffisante pour l’éliminer de l’espace de recherche.
Une solution relativement facile à mettre en oeuvre est de créer un dictionnaire qui
va filtrer ces termes comme si c’étaient des “stop words”.
Ce dictionnaire prend concrètement la forme de deux fonctions à
écrire en langage C, et de quelques ordres SQL pour déclarer et assigner notre
nouveau dictionnaire à une configuration de recherche
(ALTER TEXT SEARCH CONFIGURATION).
Dans le code source de PostgreSQL, il y a plusieurs exemples de dictionnaires dont on peut s’inspirer:
On peut démarrer en copier-collant un de ces exemples, car la partie vraiment distinctive de notre dictionnaire personnalisé tout simple s’exprime en seulement quelques lignes en C, comme on va le voir plus bas.
Les deux fonctions à produire sont:
une fonction d’initialisation INIT qui reçoit les paramètres de
configuration du dictionnaire. On en profite pour rendre notre longueur
maximale configurable via ce mécanisme, plutôt que de la coder en dur
dans le source.
une fonction LEXIZE reçevant un terme (le texte associé au jeton)
et chargée d’émettre zéro, un ou plusieurs lexèmes correspondant à
ce terme. Les lexèmes émis peuvent être passés ou non au reste des
dictionnaires de la chaîne s’il y en a d’autres, au choix de cette
fonction.
Dans le cas qui nous intéresse ici, on veut éliminer le
terme s’il est trop long, et sinon le passer tel quel.
On va appeler ce dictionnaire dictmaxlen et son paramètre length.
En suivant le modèle et les conventions des modules additionnels de
contrib/, on peut l’encapsuler dans une
extension Postgres, dans un répertoire dédié avec un Makefile et
un fichier de déclaration SQL spécifiques.
Pour être précis dans la terminologie, ces définitions créent un
modèle de dictionnaire (template) et non directement un dictionnaire.
Le dictionnaire à proprement parler est instancié à partir d’un modèle
par CREATE TEXT SEARCH DICTIONARY (TEMPLATE = ...), avec les valeurs
des éventuels paramètres.
Voici les déclarations SQL des fonctions et du modèle:
CREATE FUNCTION dictmaxlen_init(internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE FUNCTION dictmaxlen_lexize(internal, internal, internal, internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE TEXT SEARCH TEMPLATE dictmaxlen_template (
LEXIZE = dictmaxlen_lexize,
INIT = dictmaxlen_init
);La seule chose spécifique ici est la racine de nommage dictmaxlen, sinon
ce seront les mêmes déclarations pour n’importe quel modèle de dictionnaire.
Datum
dictmaxlen_init(PG_FUNCTION_ARGS)
{
List *options = (List *) PG_GETARG_POINTER(0);
DictMaxLen *d;
ListCell *l;
d = (DictMaxLen *) palloc0(sizeof(DictMaxLen));
d->maxlen = 50; /* 50 caracteres par defaut */
foreach(l, options)
{
DefElem *defel = (DefElem *) lfirst(l);
if (strcmp(defel->defname, "length") == 0)
d->maxlen = atoi(defGetString(defel));
else
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized dictionary parameter: \"%s\"",
defel->defname)));
}
}
PG_RETURN_POINTER(d);
}Datum
dictmaxlen_lexize(PG_FUNCTION_ARGS)
{
DictMaxLen *d = (DictMaxLen *) PG_GETARG_POINTER(0);
char *token = (char *) PG_GETARG_POINTER(1);
int byte_length = PG_GETARG_INT32(2);
if (pg_mbstrlen_with_len(token, byte_length) > d->maxlen)
{
/*
* Si le mot est plus grand que notre limite, renvoie un
* tableau sans lexeme.
*/
TSLexeme *res = palloc0(sizeof(TSLexeme));
PG_RETURN_POINTER(res);
}
else
{
/* Si le mot est court, on le laisse passer en mode "non reconnu" */
PG_RETURN_POINTER(NULL);
}
}Comme pour la plupart des modules de contrib, l’empaquetage de ce code dans une extension est la manière la plus efficace pour le distribuer et le déployer.
La création d’une extension est assez simple en utilisant PGXS,
qui est fourni avec Postgres (pour les distributions Linux, il faudra installer
un paquet de développement, comme postgresql-server-dev-11 pour Debian).
Une extension a besoin d’un fichier de contrôle. Le contenu
ci-dessous fait l’affaire (fichier dict_maxlen.control):
# dict_maxlen extension
comment = 'text search template for a dictionary filtering out long words'
default_version = '1.0'
module_pathname = '$libdir/dict_maxlen'
relocatable = true
Grâce à PGXS on peut se contenter d’un Makefile simplifié qui va
inclure automatiquement les déclarations pour les chemins où
Postgres est installé, les bibliothèques dont il a besoin, etc.
Ci-dessous un Makefile complètement fonctionnel, qui permet de compiler
et installer l’extension avec make && make install:
EXTENSION = dict_maxlen
EXTVERSION = 1.0
PG_CONFIG = pg_config
MODULE_big = dict_maxlen
OBJS = dict_maxlen.o
DATA = $(wildcard *.sql)
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Une fois les fichiers de l’extension compilés et installés, on peut la créer dans une base et y instantier le dictionnaire.
CREATE EXTENSION dict_maxlen;
CREATE TEXT SEARCH DICTIONARY dictmaxlen (
TEMPLATE = dictmaxlen_template,
LENGTH = 40 -- par exemple
);Ensuite il faut associer ce dictionnaire à des types de jetons (produits
par l’analyseur lexical), via ALTER TEXT SEARCH CONFIGURATION ... ALTER MAPPING.
Plus haut on a vu sur un exemple que le type de jeton qui ressortait sur
du contenu encodé était numword, mais on peut aussi vouloir
associer notre dictionnaire aux jetons ne contenant que des lettres:
word et asciiword, ou à n’importe quels autres des 23 types de
jeton
que l’analyseur peut générer actuellement.
Avec psql on peut visualiser ces associations avec \dF+. Pour french,
par défaut on a:
=# \dF+ french
Text search configuration "pg_catalog.french"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+--------------
asciihword | french_stem
asciiword | french_stem
email | simple
file | simple
float | simple
host | simple
hword | french_stem
hword_asciipart | french_stem
hword_numpart | simple
hword_part | french_stem
int | simple
numhword | simple
numword | simple
sfloat | simple
uint | simple
url | simple
url_path | simple
version | simple
word | french_stem
On peut créer une configuration de texte spécifique, pour éviter de perturber celles qui existent déjà, et lui associer ce dictionnaire:
CREATE TEXT SEARCH CONFIGURATION mytsconf ( COPY = pg_catalog.french );
ALTER TEXT SEARCH CONFIGURATION mytsconf
ALTER MAPPING FOR asciiword, word
WITH dictmaxlen,french_stem;
ALTER TEXT SEARCH CONFIGURATION mytsconf
ALTER MAPPING FOR numword
WITH dictmaxlen,simple;Vérification faite avec psql:
=# \dF+ mytsconf
Text search configuration "public.mytsconf"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+------------------------
asciihword | french_stem
asciiword | dictmaxlen,french_stem
email | simple
file | simple
float | simple
host | simple
hword | french_stem
hword_asciipart | french_stem
hword_numpart | simple
hword_part | french_stem
int | simple
numhword | simple
numword | dictmaxlen,simple
sfloat | simple
uint | simple
url | simple
url_path | simple
version | simple
word | dictmaxlen,french_stem
Et voilà, il n’y a plus qu’à vérifier avec un numword dépassant les 40 caractères:
=# select to_tsvector('mytsconf', 'Un lexème trop long: Q29uc2lzdGVuY3kgYWxzbyBtZWFucyB0aGF0IHdoZW4');
to_tsvector
-----------------------------
'lexem':2 'long':4 'trop':3
(1 row)Le jeton indésirable a bien été écarté.
En peut utiliser cette configuration mytsconf en la passant en
argument explicite aux fonctions de recherche et indexation plein
texte, mais aussi la mettre par défaut:
Pour la session:
SET default_text_search_config TO 'mytsconf';
Pour la base (permanent):
ALTER DATABASE nombase SET default_text_search_config TO 'mytsconf';
Le code source de cet exemple de dictionnaire est disponible sur github.
Un serveur PostgreSQL peut être accessible d’Internet, au sens d’avoir le service en écoute sur une adresse IP publique et un port TCP ouvert à toute connexion. A titre indicatif, shodan.io, un service qui sonde ce genre de choses, trouve plus de 650000 instances dans ce cas actuellement. Avec la popularisation du modèle DBaaS (“Database As A Service”), les serveurs PostgreSQL peuvent être légitimement accessibles d’Internet, mais ça peut être aussi le résultat involontaire d’une mauvaise configuration.
Car cette configuration réseau ouverte s’oppose à une autre plus traditionnelle et plus sécurisée lorsque les serveurs de bases de données sont au minimum protégés par un pare-feu, voire n’ont même pas d’interface réseau reliée à Internet, ou bien n’écoutent pas dessus.
La conséquence d’avoir des instances ouvertes est que des tentatives d’intrusion sur le port 5432 sont susceptibles de se produire à tout moment, tout comme il y a des tentatives de piratage en tout genre sur d’autres services comme ssh, le mail ou des applications web populaires comme Wordpress, Drupal ou phpMyAdmin.
Si vous avez un serveur accessible publiquement, il est possible de mettre son IP dans le champ de recherche de shodan.io, histoire de voir ce qu’il sait de vous.
Que vous ayez déjà des instances PostgreSQL ouvertes à l’Internet, que vous envisagiez d’en avoir, ou au contraire que vous vouliez vous assurer que vos instances ne sont pas accessibles, voici deux ou trois réflexions à ce sujet.
Quand on demande “comment activer l’accès à PostgreSQL à partir d’une
autre machine?”, la réponse
typique est d’ajouter
des règles dans pg_hba.conf et de mettre dans postgresql.conf:
listen_addresses = *
(en remplacement du listen_addresses = localhost initial)
Effectivement ça fonctionne, en faisant écouter toutes les interfaces réseau de la machine, pas seulement celle où les connexions PostgreSQL sont attendues. Dans le cas, assez typique, où ces connexions sont initiées exclusivement d’un réseau local privé, on pourrait plutôt préciser les adresses des interfaces concernées. Si par exemple le serveur a une IP privée 192.168.1.12, on pourrait mettre:
listen_addresses = localhost, 192.168.1.12
Pourquoi ces adresses plutôt que * ? On peut se poser plus généralement la
question: pourquoi PostgreSQL n’a pas listen_addresses = * par
défaut, de façon à ce qu’un poste distant puisse se connecter
directement, sans obliger un admin à modifier d’abord la
configuration?
MongoDB faisait ça, et l’ampleur des attaques réussies contre cette base illustre assez bien pourquoi ce n’est pas une bonne idée. En 2015 shodan estimait qu’au moins 30000 instances MongoDB étaient librement accessibles d’Internet, probablement dans leur configuration par défaut, laissant l’accès à 595 TB de données. Fin 2016, une campagne d’attaque dite “Mongo Lock” commençait à affecter une bonne partie de ces victimes potentielles. Le piratage consistait à effacer ou chiffrer les données et exiger une rançon en bitcoins pour les récupérer. Cet épisode a été une vraie débâcle pour la réputation de MongoDB.
Indépendamment de la question du mot de passe, dont l’absence par défaut est aussi un facteur important dans ces attaques, l’ampleur aurait été biensûr moindre si le service écoutait par défaut uniquement sur l’interface réseau locale, puisque c’est suffisant quand un site et sa base sont la même machine.
MongoDB a changé depuis cette configuration par défaut, mais des années après on voit toujours ce qui semble être des exploitations de ce problème, par exemple en janvier 2019, cette fuite de données: MongoDB : 202 millions de CV privés exposés sur internet à cause d’une base de données non protégée.
C’est qu’il y a toujours dans la nature des installations jamais mises à jour dont les gérants, quand il y en a, n’ont aucune idée qu’il y a un danger pour leurs données et qu’il faudrait changer une configuration alors même que “ça marche”…
Evidemment, il faut protéger les comptes utilisateur par des mots de passe solides, mais ça ne suffit pas.
Un pré-requis indispensable est de se tenir au courant des mises à jour
de sécurité et d’être prêt à les appliquer en urgence si nécessaire.
Par exemple en 2013, la faille
de sécurité CVE-2013-1899
permettait de prendre la main à distance sur n’importe quelle instance PostgreSQL,
indépendamment des mots de passe et des règles du pg_hba.conf,
tant qu’on avait un moyen de la joindre par le réseau
(d’où encore une fois l’intérêt de ne pas s’exposer inutilement en
mettant listen_addresses = * quand ce n’est pas indispensable).
Cette faille de sécurité est scrutée par des sondes à qui on a rien demandé, puisque si je regarde les logs récents de mon instance PostgreSQL ouverte sur Internet, je vois des entrées du style (modulo le masquage de la source):
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "template0", SSL on
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "template0", SSL off
2019-01-31 05:51:44 CET FATAL: unsupported frontend protocol 65363.19778: serve
r supports 1.0 to 3.0
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "-h", SSL on
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "-h", SSL off
Le nom de base “-h” n’est pas choisi au hasard, la faille ci-dessus étant décrite par:
Argument injection vulnerability in PostgreSQL 9.2.x before 9.2.4, 9.1.x before 9.1.9, and 9.0.x before 9.0.13 allows remote attackers to cause a denial of service (file corruption), and allows remote authenticated users to modify configuration settings and execute arbitrary code, via a connection request using a database name that begins with a “-“ (hyphen)
Ce genre de tentative peut venir d’un service comme shodan ou d’un bot malveillant, voire d’un attaquant qui vous vise spécifiquement, difficile à savoir.
Il y a des exemples d’attaques réussies sur postgres, notamment visant à faire miner de la cryptomonnaie Monero.
Pour autant qu’on puisse en juger de l’extérieur, ces attaques
n’exploitent pas une faille spécifique de postgres, mais parviennent
à se connecter en super-utilisateur postgres. On peut imaginer que
ça arrive à cause d’un mot de passe trop faible, d’un
pg_hba.conf trop laxiste, ou via le piratage d’un autre service
(typiquement un site web) qui se connecte à PostgreSQL en
super-utilisateur.
Par exemple dans cette question sur dba.stackexchange:
Mysterious postgres process pegging CPU at 100%; no running queries un utilisateur demande pourquoi postgres fait tourner une commande ./Ac2p20853 consommant tout le CPU disponible. L’explication de loin la plus plausible est un piratage dans
lequel ce binaire a été téléchargé et lancé via une fonction postgresql ayant les droits
super-utilisateur.
Cette autre question sur stackoverflow.com (CPU 100% usage caused by unknown postgres query) est assez similaire, mais en plus elle montre des requêtes servant de coquille au programme parasite:
pg_stat_activity:
pid datname username query
19882 postgres postgres select Fun013301 ('./x3606027128 &')
19901 postgres postgres select Fun013301 ('./ps3597605779 &')
top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
19885 postgres 20 0 192684 3916 1420 S 98.3 0.1 5689:04 x3606027128
Ce comportement ressemble trait pour trait à l’attaque que Imperva a détecté via leurs instances “pot de miel”, et disséquée dans leur article A Deep Dive into Database Attacks [Part III]: Why Scarlett Johansson’s Picture Got My Postgres Database to Start Mining Monero.
En résumé, une fois qu’une connexion SQL sur un compte
super-utilisateur est obtenue (par un moyen non précisé), le code attaquant créé
une fonction SQL permettant d’exécuter n’importe quel programme sur disque.
Ensuite il créé sur le disque via lo_export() un programme qui a pour objet
d’aller récupérer sur Internet le vrai programme qui mine.
Le programme en question est sur un site d’images public, en l’occurrence
caché ici dans un fichier photo représentant Scarlett Johansson, d’où la référence improbable
à l’actrice dans le titre de l’article.
Moralité: il faut limiter les comptes super-utilisateur à un usage d’administration,
et éviter de leur attribuer le droit aux connexions distantes, via pg_hba.conf.
Avoir ssl=on dans la configuration serveur signifie que le chiffrage
est possible quand le client le demande, mais pas qu’il est
obligatoire. Le chiffrage évite qu’une tierce partie ayant accès
au réseau puisse lire tout ce qui passe entre le client et le serveur.
Si on veut l’obliger du côté serveur, on peut y arriver via les
règles du fichier pg_hba.conf (les règles sont interprétées
dans l’ordre et le test s’arrête dès qu’une correspondance est trouvée,
comme dans une cascade de IF…ELSEIF…ELSIF…ELSIF…END IF):
# autorise les connexions locales "Unix domain sockets"
# sans mot de passe pour le même utilisateur OS
local all all peer
# permet l'économie du chiffrage, mais pas du mot de passe
# pour les connexions TCP locales
host all all 127.0.0.1/32 md5 # plutôt scram avec postgresql 10 et plus
host all all ::1/128 md5
# rejette les connexions distantes non chiffrées
hostnossl all all 0.0.0.0/0 reject
hostnossl all all ::/0 reject
# ajouter les autres règles à partir d'ici
...
...
Par défaut la bibliothèque cliente la plus souvent utilisée,
libpq,
lorsqu’elle est compilée avec le support SSL, essaie d’abord une connexion
chiffrée, puis le cas échéant une connexion non chiffrée. Ce comportement
correspond à sslmode=prefer dans les paramètres de connexion
(voir le détail dans la section Support de
SSL de la doc).
C’est pour ça que dans les logs, une tentative de connexion infructueuse comme ci-dessus
apparaît en double, une première fois avec SSL=on et la seconde avec SSL=off.
Depuis la version 9.5, il est possible de savoir parmi les connexions établies
quelles sont celles qui sont chiffrées ou pas avec la vue système
pg_stat_ssl
SELECT datname,usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid;A défaut d’interdire les connexions non chiffrées, cette requête permet de vérifier s’il y en a et d’où elles viennent.
Open Food Facts est une base de données contributive sur les produits alimentaires, en français et en anglais, sous licence ouverte ODBL.
Ce service permet sur le web, à partir d’un code de produit (code barres) d’en connaître les ingrédients, de renseigner ceux d’un produit non encore connu de la base pour l’enrichir, et de faire des requêtes en ligne avec un formulaire de recherche. Il a également une application mobile pour les smartphones et une API, le tout étant en source sur github.
Le service utilise le moteur NoSQL MongoDB, mais les données sont disponibles en téléchargement aux formats CSV et RDF entre autres, de sorte qu’on puisse les importer dans l’outil de notre choix. Comme sur ce blog on n’est pas trop branchés MongoDB, mais plutôt PostgreSQL, on va voir comment importer ces données pour les requêter avec notre SGBD préféré :)
Ca peut paraître trivial, puisque PostgreSQL supporte l’import de CSV nativement à travers la commande COPY, mais en pratique ça ne l’est pas pour diverses raisons:
CSV n’est pas un format rigoureusement spécifié a priori. Il est spécifié a posteriori par les règles de la RFC 4180 avec laquelle PostgreSQL est compatible, mais de nombreux exports CSV les ignorent. La seule chose à peu près garantie sur les fichiers annoncés comme CSV, c’est qu’ils ont des champs texte séparés par un certain caractère.
les données OpenData sont souvent plus ou moins “sales”, parfois au point de nécessiter un nettoyage ou filtrage pour les rendre importables.
les fichiers ont souvent un grand nombre de colonnes dont on connait les noms mais pas forcément les types, et une création à la main des tables est fastidieuse.
Ce billet détaille les étapes nécessaires pour importer une extraction d’OpenFoodFacts, jusqu’à arriver à une base requêtable. La majorité des problèmes et solutions mentionnés ne sont pas spécifiques à cet import, ils pourraient se retrouver dans d’autres jeux de données “OpenData”.
L’export se présente sous la forme d’un seul fichier de grande taille, c’est-à-dire, au 20/12/2018, une taille de 2 Go pour un peu plus de 700 mille lignes:
$ wget https://fr.openfoodfacts.org/data/fr.openfoodfacts.org.products.csv
$ wc -l fr.openfoodfacts.org.products.csv
709988 fr.openfoodfacts.org.products.csv
$ ls -sh fr.openfoodfacts.org.products.csv
2.0G fr.openfoodfacts.org.products.csv
Pour tenter l’import avec COPY, il faut d’abord créer une table correspondant à la structure du fichier. En effet COPY ne crée pas la table même quand il y a des noms de colonne sur la 1ere ligne (l’option HEADER ne sert à l’import qu’à ignorer cette ligne).
Les infos dont on dispose pour ça sont:
$ head -n 1 fr.openfoodfacts.org.products.csvPour avoir cette liste des colonnes sous forme lisible, on peut utiliser la commande Unix suivante qui va sortir les noms de colonnes alignés verticalement:
$ head -n 1 fr.openfoodfacts.org.products.csv | sed -e 's/\t/\n/g'
code
url
creator
created_t
created_datetime
last_modified_t
last_modified_datetime
product_name
...etc... (173 lignes)
Pour éviter de spécifier un par un les types de données de chaque colonne, dans un CREATE TABLE, on peut utiliser un programme qui fait ça automatiquement: csvkit.
csvkit est écrit en python et installable avec pip, ou via un paquet
d’une distribution Linux. Ici j’utilise Debian 9, qui propose la
version 0.9.1 de csvkit, installable par:
# apt install python3-csvkit
En plus de bibliothèques en python, ce paquet nous installe une série d’outils appelables en ligne de commande:
/usr/bin/csvformat
/usr/bin/csvcut
/usr/bin/csvjson
/usr/bin/csvclean
/usr/bin/csvsql
/usr/bin/csvstack
/usr/bin/sql2csv
/usr/bin/csvlook
/usr/bin/csvjoin
/usr/bin/csvstat
/usr/bin/in2csv
/usr/bin/csvpy
/usr/bin/csvgrep
/usr/bin/csvsort
Celui qui nous intéresse est csvsql, et d’après sa documentation, il peut générer un script de
création de table, et optionnellement insérer les données.
Par défaut il déclare les champs texte en VARCHAR(N) où N est la
taille maximale constatée dans les données, mais ce n’est pas forcément
pertinent pour PostgreSQL où limiter la taille déclarée n’apporte
aucun gain de performance, et surtout ce n’est pas parce qu’un champ
n’a pas dépassé N caractères jusque là que des entrées qu’on pourrait
ajouter plus tard devraient forcément se conformer à cette limite.
On utilise donc l’option --no-constraints pour éviter ça:
--no-constraints Generate a schema without length limits or null
checks. Useful when sampling big tables.
Donc voici l’invocation qui convient:
$ csvsql --dialect postgresql --tabs --table openfoodfacts \
--no-constraints fr.openfoodfacts.org.products.csv > create_table.sql
La commande prend quelques minutes, et produit ce fichier qui contient un ordre CREATE TABLE avec 173 colonnes comportant:
Dans l’ensemble le résultat semble correct, à part deux détails:
les champs created_datetime et last_modified_datetime mériteraient
d’être en timestamptz. On les modifiera après.
les colonnes nommées _100g sont pratiquement toutes en type “float”
(équivalent à “double precision”), sauf deux:
"nervonic-acid_100g" INTEGER,
"nutrition-score-fr_100g" INTEGER,
C’est probablement parce qu’il n’y avait aucun point décimal dans aucune des valeurs de tout le fichier pour ces deux colonnes. Par souci d’homogénéité, on pourra les retyper comme les autres en “float”.
Pour commençer on créé une base dédiée:
$ psql -d postgres -U postgres
=# CREATE DATABASE foodfacts ENCODING 'UTF8';
Puisque csvsql sait créer la table, autant lui demander d’insérer aussi le contenu,
avec son option --insert.
Une option --db attend une “SQL alchemy connection string” qui, dans le cas où on
reste sur l’utilisateur par défaut, peut se réduire au nom de la base:
--db postgresql:///foodfacts, ou être du style --db postgresql://user@host:port/dbname
Il faudra aussi installer le driver psycopg2 pour python pour profiter de l’import, par exemple sur Debian:
# apt install python3-psycopg2
$ csvsql --db postgresql:///foodfacts --table openfoodfacts --tabs \
--insert --no-constraints fr.openfoodfacts.org.products.csv
L’import fonctionne jusqu’au bout sans message d’erreur, en une douzaine de minutes, mais il s’avère qu’après l’import des contrôles basiques d’intégrité donnent des résultats inquiétants. Pour commencer, il manque des entrées par rapport au nombre de lignes du fichier, 660 exactement:
$ wc -l fr.openfoodfacts.org.products.csv
709988
$ psql -d foodfacts
=> select count(*) from openfoodfacts;
count
--------
709327
Seulement 0.1% des lignes du fichier manquent à l’appel, mais c’est mauvais signe. Il ne devrait y avoir une différence que de 1 entre ces deux nombres (correspondant à la ligne d’entête).
Un autre contrôle basique donne aussi un résultat qui confirme que cet import
pose problème: si on considère la colonne created_datetime, date de création
de l’entrée, et qu’on cherche son minimum, son maximum, et le nombre de fois où
elle n’est pas remplie, on obtient:
=> SELECT min(created_datetime),
max(created_datetime),
count(*) filter (where created_datetime is null)
FROM openfoodfacts;
Résultat:
min | max | count
----------------------+---------------------+-------
2012-01-31T14:43:58Z | Super U, Magasins U | 10
(1 row)
Le min semble correct, mais le max montre qu’un nom d’enseigne
s’est invité dans les dates, ce qui signifie que des données ont
“glissé” d’une colonne à l’autre, voire sur plusieurs colonnes. Par
ailleurs, 10 entrées n’ont pas de date de création, alors qu’on
s’attendrait à ce qu’il y en ait zéro.
Dans la mesure où csvkit ne sort pas de message d’erreur alors qu’il a clairement des problèmes avec ces données, je n’insiste pas avec cet outil pour l’import. Il a été utile pour sortir un CREATE TABLE, c’est déjà pas mal.
COPY FROM FILE est réservé aux superutilisateurs (parce qu’il peut
lire des fichiers sur le serveur avec les droits de postgres), on va
donc utiliser le \copy de psql qui est très proche à l’usage mais
ouvre le fichier côté client et le fait passer par la liaison
client-serveur, ce qui ne nécessite pas de droit particulier:
=> \copy openfoodfacts from 'fr.openfoodfacts.org.products.csv' with (format csv, header, delimiter E'\t')
Le premier résultat est que l’import échoue assez rapidement:
ERROR: missing data for column "bicarbonate_100g"
CONTEXT: COPY openfoodfacts, line 3193: "0011110084767 http://world-fr.openfoodfacts.org/produit/0011110084767/8-white-cake-decorated usda-nd..."
Il s’agit probablement d’un décalage de champ mais comment voir ce qui
ne va pas avec cette ligne? Pour ça on va l’isoler du fichier avec une
commande shell spécifique. Puis on va l’importer en base
en tant qu’un seul champ de texte, au format text au sens de la
commande COPY (et non CSV):
# extraction de la ligne sur laquelle l'erreur est signalée
$ sed '3193q;d' fr.openfoodfacts.org.products.csv > line-3193
# vérification qu'il n'y a pas d'antislash ni de caractère de code 0x01
# (pour l'utiliser comme échappement) dans la ligne de données:
$ fgrep '\' line-3193 # resultat vide
$ fgrep $'\x01' line-3193 # resultat vide
=> CREATE TEMPORARY TABLE ligne(data text);
CREATE TABLE
=> \copy ligne FROM 'line-3193' WITH (format text, delimiter E'\001')
COPY 1
Une fois importée on peut, en SQL, découper cette ligne en champs pour les apparier
avec les colonnes de notre table principale (via pg_attribute et par numéro de champ),
histoire de bien visualiser les associations [numéro de champ] / [nom de colonne] / [valeur]
=> SELECT num, attname, valeur
FROM ligne CROSS JOIN LATERAL regexp_split_to_table(data, E'\t')
WITH ORDINALITY AS res(valeur,num)
JOIN pg_attribute att ON (res.num = att.attnum AND attrelid='openfoodfacts'::regclass);(attention: cette requête suppose qu’on n’a pas supprimé de colonne avec
ALTER TABLE openfoodfacts DROP COLUMN ..., car les colonnes
supprimées restent perpétuellement, avec leurs numéros initiaux,
dans pg_attribute).
Je ne vais reproduire tout le résultat ici car même un seul enregistrement
produit beaucoup de données à l’écran, mais en regardant les champs
un par un, on voit qu’à la colonne serving_size, un guillemet qui en
principe est un caractère spécial en (CSV d’encadrement de champ) est
employé pour signifier le “pouce” anglo-saxon, et pas pour encadrer un
champ.
39 | traces_tags |
40 | traces_fr |
41 | serving_size | 28 g (1 " CUBE | ABOUT)
42 | serving_quantity | 28
43 | no_nutriments |
Ce qui gêne ici, c’est que ça va à l’encontre de cette règle du CSV (de la RFC 4180 citée plus haut):
5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields
Il aurait fallu que le champ soit formatté ainsi: "28 g (1 "" CUBE | ABOUT)".
Comment contourner ce problème? Il se trouve que l’interprétation du
guillemet est paramétrable dans COPY via l’option QUOTE, justement
pour les cas où le format de fichier s’éloigne du CSV strict. Pour
que le caractère guillemet devienne normal, il faut spécifier un autre
caractère à sa place. Les contraintes: que ce caractère n’apparaisse
pas dans les contenus par ailleurs (ou qu’il soit doublé mais on n’est
pas dans ce cas là), et que de plus son codage UTF-8 tienne en un seul
octet non nul, c’est-à-dire dans la plage 0x01-0x7F.
Donc cherchons le premier caractère qui convient (c’est-à-dire qui ait 0 occurrence dans les contenus) avec ces commandes shell successives:
$ fgrep -c $'\x01' fr.openfoodfacts.org.products.csv
4
$ fgrep -c $'\x02' fr.openfoodfacts.org.products.csv
2
$ fgrep -c $'\x03' fr.openfoodfacts.org.products.csv
0
A noter que des octets de valeurs 01 et 02 n’ont en principe pas leur
place dans des contenus texte, il s’agit de codes de contrôle qui ne correspondent à aucune lettre de l’alphabet,
et qui certainement sont du “bruit” supprimable dans ces données.
Quoiqu’il en soit, le code 03 est disponible pour l’indiquer à COPY au lieu
du guillemet pour rendre à ce dernier son caractère neutre, et on peut réessayer notre
import avec l’option quote '\003'. Cette fois-ci, ça va nettement plus loin:
=> \copy openfoodfacts from 'fr.openfoodfacts.org.products.csv' with (format csv, header, delimiter E'\t', quote E'\003')
ERROR: unquoted carriage return found in data
HINT: Use quoted CSV field to represent carriage return.
CONTEXT: COPY openfoodfacts, line 268349
Cette nouvelle erreur nous dit qu’il y a un retour chariot à l’intérieur d’un champ.
Ce n’est pas que le retour chariot soit interdit en CSV (aucun caractère n’est interdit),
mais il est autorisé seulement si l’ensemble du champ est encadré par
des guillemets (ou au pire par le caractère spécifié par l’option quote), mais là
le fichier n’utilise pas l’encadrement des champs comme vu plus haut.
Ceci nous mène à vérifier un point important: y-a-t’il des sauts de ligne (caractère de code 10) à l’intérieur des champs en plus des retours chariot (caractère de code 13)?
C’est vérifiable simplement avec awk, à qui on peut demander les
lignes qui ne contiennent pas strictement 173 champs séparés par des
tabulations:
$ awk -F'\t' '{if (NF!=173) print $0}' fr.openfoodfacts.org.products.csv
Il se trouve qu’il n’y a aucun résultat, c’est-à-dire qu’en fait il n’y a pas de saut de ligne à l’intérieur des champs, sinon forcément certains s’étendraient sur plusieurs lignes et donc certaines lignes porteraient moins de 173 champs.
Du côté des caractères retours chariot, leur nombre est trouvable par la commande:
$ fgrep -c $'\x0d' fr.openfoodfacts.org.products.csv
34
Ces 34 retours chariots dans les contenus, posent problème compte-tenu
du fait que les champs ne sont pas encadrés. On va filtrer
(=supprimer) ces caractères en utilisant une autre clause de \copy,
la clause program avec la commande Unix tr
comme filtre.
=> \copy openfoodfacts from program 'tr -d ''\r'' <fr.openfoodfacts.org.products.csv' with (format csv, header, delimiter E'\t', quote E'\003')
COPY 709987
Et enfin, cette commande-là ne rencontre pas d’erreur et permet d’intégrer la totalité des lignes (709988 moins l’entête), en à peu près une minute.
Une fois l’import passé, faisons encore quelques vérifications de cohérence sur des critères basiques:
=> SELECT
min(created_datetime),
max(created_datetime),
count(*) filter (where created_datetime is null)
FROM
openfoodfacts;
min | max | count
----------------------+----------------------+-------
2012-01-31T14:43:58Z | 2018-12-12T07:24:25Z | 1
On voit qu’on a une entrée avec une date de création nulle, ce qui est anormal mais pas inquiétant en volume.
En principe les valeurs de la colonne code devraient être uniques puisque
c’est le code barres du produit, mais on trouve un certain nombre de doublons:
=> SELECT code,count(*) FROM openfoodfacts GROUP BY code HAVING count(*)>1;
code | count
--------------------+-------
0016000459335 | 2
0019320001376 | 2
0051500006962 | 2
0055577102152 | 2
...
8901262260152 | 2
9415142005904 | 2
(113 rows)
On peut soit ignorer ce problème et décider de ne pas déclarer la clef primaire dans la table, soit créer une clef primaire synthétique (un compteur qui n’a pas d’autre signification que numéro unique), soit supprimer les entrées en doublon.
Si deux lignes sont exactement en doublon (les valeurs de toutes les colonnes sont identiques), il suffit d’en supprimer une des deux. Mais souvent, on a un quasi-doublon, dans le sens où l’application de saisie de ces données a produit deux fiches au lieu d’une pour la même chose, mais pas avec rigoureusement les mêmes contenus.
Voici une méthode pour afficher en SQL les colonnes qui diffèrent
entre deux lignes dont beaucoup de colonnes sont supposément égales
par ailleurs. On va utiliser ici une requête préparée qui prend en
argument ($1 dans le texte de la requête) la valeur de la clef qui
se présente en doublon:
=> PREPARE diff AS
WITH valeurs AS (SELECT key, value
FROM
(SELECT row_to_json(o.*) AS line
FROM openfoodfacts AS o
WHERE code=$1) AS r
CROSS JOIN LATERAL json_each_text(r.line))
SELECT distinct v1.key
FROM valeurs v1 JOIN valeurs v2 ON (v1.key=v2.key
AND v1.value IS DISTINCT FROM v2.value);
=> EXECUTE diff('0051500006962');
key
-----------
countries
(1 row)
=> SELECT code,countries FROM openfoodfacts WHERE code='0051500006962';
code | countries
---------------+---------------
0051500006962 | États-Unis
0051500006962 | United States
(2 rows)
=> EXECUTE diff('6003326008341');
key
------------------------
image_small_url
last_modified_t
image_url
last_modified_datetime
(4 rows)
Ici je vais aller au plus vite en ne gardant pour chaque doublon que
le dernier modifié sur la base de la colonne last_modified_datetime,
et en cas d’égalité sur cette colonne, un arbitrage sur la pseudo-colonne ctid
(emplacement physique de la ligne) qui est forcément différente d’une
ligne à l’autre.
DELETE FROM openfoodfacts o1
USING (SELECT code, max(last_modified_datetime), max(ctid) as mxid
FROM openfoodfacts GROUP BY code HAVING COUNT(*)>1) o2
WHERE o1.code=o2.code
AND (o1.last_modified_datetime < o2.max
OR (o1.last_modified_datetime = o2.max AND o1.ctid < o2.mxid));Finalement on peut créer un index unique:
=> CREATE UNIQUE INDEX code_index ON openfoodfacts(code);
Si on voulait mieux faire, il faudrait examiner au cas par cas ces doublons, et agir pour les supprimer dans la base source des données.
Les colonnes de type “horodatage” n’ont pas été typées en timestamptz par
csvsql, mais il est toujours temps de le faire après coup avec ALTER
TABLE.
On peut aussi supprimer les colonnes created_t et last_modified_t qui
sont des timestamps Unix (nombre de secondes depuis le 1er janvier 1970)
avec la même signification que created_datetime et last_modified_datetime.
En faisant tout dans la même commande:
ALTER TABLE openfoodfacts
ALTER COLUMN created_datetime TYPE timestamptz USING (created_datetime::timestamptz),
ALTER COLUMN last_modified_datetime TYPE timestamptz USING (last_modified_datetime::timestamptz),
ALTER COLUMN "nervonic-acid_100g" TYPE float USING ("nervonic-acid_100g"::float),
ALTER COLUMN "nutrition-score-fr_100g" TYPE float USING ("nutrition-score-fr_100g"::float),
DROP COLUMN created_t,
DROP COLUMN last_modified_t;Pour finir, une petite requête d’exemple montrant le nombre de références ajoutées par an:
=> SELECT extract(year from created_datetime), count(*)
FROM openfoodfacts
GROUP BY 1 ORDER BY 1 DESC;
date_part | count
-----------+--------
| 1
2018 | 316197
2017 | 284243
2016 | 46218
2015 | 35256
2014 | 13417
2013 | 10078
2012 | 4464
(8 rows)
La bêta 4 (déjà!) de PostgreSQL 11 a été annoncée le 20 septembre. Il est vraisemblable qu’on pourra utiliser cette version 11 en production dans quelques mois, mais en attendant il est possible et souhaitable de l’essayer dans nos environnements de test avec nos applis, soit pour vérifier leur compatibilité, soit pour tester les nouveautés et remonter d’éventuels problèmes.
La liste exhaustive des changements par rapport à la version 10 est donnée par les notes de version de la documentation.
Voici une sélection d’articles ou présentations (en anglais) qui détaillent ou mettent en perspective ces nouveautés de manière plus digeste que la liste de la doc:
PostgreSQL 11: something for everyone, par Peter Geoghegan.
Partitioning Improvements in PostgreSQL 11, par Álvaro Herrera.
Major Features: Postgres 11 (support de présentation PDF, 24 pages), par Bruce Momjian.
PostgreSQL 11 New Features With Examples (PDF très complet, 86 pages), par Noriyoshi Shinoda.
SERIALIZABLE in PostgreSQL 11… and beyond, par Thomas Munro.
Parallel PostGIS and PgSQL 11, par Paul Ramsey.
Just In Time Compilation in PostgreSQL 11 and onward, par Andres Freund (support de présentation PDF, 26 pages).
En français, on peut regarder la présentation de Jean-Christophe Arnu au PG Day France 2018, en vidéo (YouTube) ou sur slideshare.
Enfin, la mise à jour de la traduction de la doc est bien avancée par les volontaires francophones sur github.
Une nouvelle version de pg_stat_kcache est disponible, ajoutant la compatibilité avec Windows et d’autres plateformes, ainsi que l’ajout de nouveaux compteurs.
La version 2.1 de pg_stat_kcache vient d’être publiée.
Les deux nouvelles fonctionnalités principales sont:
getrusage() (comme Windows) ;getrusage() sont exposés.Comme je l’expliquais dans a previous article, cette extension est un wrapper sur getrusage, qui accumule des compteurs de performance par requête normalisée. Cela donnait déjà de précieuses informations qui permettaient aux DBA d’identifier des requêtes coûteuse en temps processeur par exemple, ou de calculer un vrai hit-ratio.
Cependant, cela n’était disponible que sur les plateforme disposant nativement
de la fonction getrusage, donc Windows and quelques autres platformes
n’étaient pas supportées. Heureusement, PostgreSQL permet un support basique
de getrusage()
sur ces plateformes. Cette infrastructure a été utilisée dans la version 2.1.0
de pg_stat_kcache, ce qui veut dire que vous pouvez maintenant utiliser cette
extension sur Windows et toutes les autres plateformes qui n’étaient auparavant
pas supportées. Comme il s’agit d’un support limité, seule le temps processeur
utilisateur et système sont supportés, les autres champs seront toujours NULL.
Cette nouvelle version expose également tous les autres champs de getrusage()
ayant un sens dans le cadre d’une accumulation par requête :
accumulated per query:
Un autre changement est de détecter automatiquement la précision du chronomètre système. Sans celas, les requêtes très rapides (plus rapides que la précision maximale du chronomètre) seraient détectées soit comme n’ayant pas consommé de temps processeur, soit ayant consommé le temps processeur d’autres requêtes très rapides. Pour les requêtes durant moins que 3 fois la précision du chronomètre système, où l’imprécision est importante, pg_stat_kcache utilisera à la place la durée d’exécution de la requête comme temps d’utilisation processeur utilisateur et gardera à 0 le temps d’utilisation processeur système.
En fonction de votre plateforme, certains des nouveaux compteurs ne sont pas
maintenus. Sur GNU/Linux par exemple, les swaps, messages IPC et signaux ne
sont malheureusement pas maintenus, mais ceux qui le sont restent tout à fait
intéressants. Par exemple, comparons les context switches si nous effectuons
le même nombre de transactions, mais avec 2 et 80 connexions concurrentes sur
une machine disposant de 4 cœeurs :
psql -c "SELECT pg_stat_kcache_reset()"
pgbench -c 80 -j 80 -S -n pgbench -t 100
[...]
number of transactions actually processed: 8000/8000
latency average = 8.782 ms
tps = 9109.846256 (including connections establishing)
tps = 9850.666577 (excluding connections establishing)
psql -c "SELECT user_time, system_time, minflts, majflts, nvcsws, nivcsws FROM pg_stat_kcache WHERE datname = 'pgbench'"
user_time | system_time | minflts | majflts | nvcsws | nivcsws
-------------------+--------------------+---------+---------+--------+---------
0.431648000000005 | 0.0638690000000001 | 24353 | 0 | 91 | 282
(1 row)
psql -c "SELECT pg_stat_kcache_reset()"
pgbench -c 2 -j 2 -S -n pgbench -t 8000
[...]
number of transactions actually processed: 8000/8000
latency average = 0.198 ms
tps = 10119.638426 (including connections establishing)
tps = 10188.313645 (excluding connections establishing)
psql -c "SELECT user_time, system_time, minflts, majflts, nvcsws, nivcsws FROM pg_stat_kcache WHERE datname = 'pgbench'"
user_time | system_time | minflts | majflts | nvcsws | nivcsws
-------------------+-------------+---------+---------+--------+---------
0.224338999999999 | 0.023669 | 5983 | 0 | 0 | 8
(1 row)Sans surprise, utiliser 80 connexions concurrentes sur un ordinateur portable n’ayant que 4 cœeurs n’est pas la manière la plus efficaces de traiter 8000 transactions. La latence est 44 fois plus lentes avec 80 connexions plutôt que 2. Au niveau du système d’exploitation, on peut voir qu’avec seulement 2 connexions concurrentes, nous n’avons que 8 context switches involontaires sur la totalités des requêtes de la base pgbench, alors qu’il y en a eu 282, soit 35 fois plus avec 80 connexions concurrentes.
Ces nouvelles métriques donnent de nombreuses nouvelles informations sur ce qu’il se passe au niveau du système d’exploitation, avec une granularité à la requête normalisée, ce qui pourra faciliter le diagnostique de problèmes de performances. Combiné avec PoWA, vous pourrez même identifier à quel moment n’importe laquelle de ces métriques a un comportement différent !
pg_stat_kcache 2.1 disponible was originally published by Julien Rouhaud at rjuju's home on July 17, 2018.
Cet article de blog est le résumé d’un problème rencontré en production que j’ai eu à diagnostiquer il y a quelques mois avec des gens d’ Oslandia, et puisqu’il s’agit d’un problème pour le moins inhabituel j’ai décidé de le partager avec la méthodologie que j’ai utilisée, au cas où cela puisse aider d’autres personnes qui rencontreraient le même type de problème. C’est également une bonne occasion de rappeler que mettre à jour PostgreSQL vers une nouvelle version est une bonne pratique.
Le problème de performance initialement rapporté contenait suffisamment d’informations pour savoir qu’il s’agissait d’un problème étrange.
Le serveur utilise un PostgreSQL 9.3.5. Oui, il y a plusieurs versions mineures de retard, et bien évidémment bon nombre de versions majeures de retard. La configuration était également quelque peu inhabituelle. Les réglages et dimensionnement physiques les plus importants sont :
Serveur
CPU: 40 cœurs, 80 avec l'hyperthreading activé
RAM: 128 Go
PostgreSQL:
shared_buffers: 16 Go
max_connections: 1500
La valeur élevée pour le shared_buffers, surtout puisqu’il s’agit d’une
versions de PostgreSQL plutôt ancienne, est une bonne piste d’investigation.
Le max_connections est également assez haut, mais malheureusement l’éditeur
logiciel mentionne qu’il ne supporte pas de pooler de connexion. Ainsi, la
plupart des connexions sont inactives. Ce n’est pas idéal car cela implique un
surcoût pour acquérir un snapshot, mais il y a suffisamment de cœurs de processeur pour gérer un grand nombre de connexions.
Le problème principale était que régulièrement, les même requêtes pouvaient être extrêmement lentes. Ce simple exemple de reqête était fourni :
EXPLAIN ANALYZE SELECT count(*) FROM pg_stat_activity ;
-- Quand le problème survient
"Aggregate (actual time=670.719..670.720 rows=1 loops=1)"
" -> Nested Loop (actual time=663.739..670.392 rows=1088 loops=1)"
" -> Hash Join (actual time=2.987..4.278 rows=1088 loops=1)"
" Hash Cond: (s.usesysid = u.oid)"
" -> Function Scan on pg_stat_get_activity s (actual time=2.941..3.302 rows=1088 loops=1)"
" -> Hash (actual time=0.022..0.022 rows=12 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on pg_authid u (actual time=0.008..0.013 rows=12 loops=1)"
" -> Index Only Scan using pg_database_oid_index on pg_database d (actual time=0.610..0.611 rows=1 loops=1088)"
" Index Cond: (oid = s.datid)"
" Heap Fetches: 0"
"Total runtime: 670.880 ms"
-- Temps de traitement normal
"Aggregate (actual time=6.370..6.370 rows=1 loops=1)"
" -> Nested Loop (actual time=3.581..6.159 rows=1088 loops=1)"
" -> Hash Join (actual time=3.560..4.310 rows=1088 loops=1)"
" Hash Cond: (s.usesysid = u.oid)"
" -> Function Scan on pg_stat_get_activity s (actual time=3.507..3.694 rows=1088 loops=1)"
" -> Hash (actual time=0.023..0.023 rows=12 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on pg_authid u (actual time=0.009..0.014 rows=12 loops=1)"
" -> Index Only Scan using pg_database_oid_index on pg_database d (actual time=0.001..0.001 rows=1 loops=1088)"
" Index Cond: (oid = s.datid)"
" Heap Fetches: 0"
"Total runtime: 6.503 ms"Ainsi, bien que le « bon » temps de traitement est un petit peu lent (bien qu’il y ait 1500 connections), le « mauvais » temps de traitement est plus de 100 fois plus lent, pour une requête tout ce qu’il y a de plus simple.
Un autre exemple de requête applicative très simple était fourni, mais avec un peu plus d’informations. Voici une version anonymisée :
EXPLAIN (ANALYZE, BUFFERS) SELECT une_colonne
FROM une_table
WHERE une_colonne_indexee = 'valeur' AND upper(autre_colonne) = 'autre_value'
LIMIT 1 ;
"Limit (actual time=7620.756..7620.756 rows=0 loops=1)"
" Buffers: shared hit=43554"
" -> Index Scan using idx_some_table_some_col on une_table (actual time=7620.754..7620.754 rows=0 loops=1)"
" Index Cond: ((some_indexed_cold)::text = 'valeur'::text)"
" Filter: (upper((autre_colonne)::text) = 'autre_value'::text)"
" Rows Removed by Filter: 17534"
" Buffers: shared hit=43554"
"Total runtime: 7620.829 ms"
"Limit (actual time=899.607..899.607 rows=0 loops=1)"
" Buffers: shared hit=43555"
" -> Index Scan using idx_some_table_some_col on une_table (actual time=899.605..899.605 rows=0 loops=1)"
" Index Cond: ((some_indexed_cold)::text = 'valeur'::text)"
" Filter: (upper((autre_colonne)::text) = 'autre_value'::text)"
" Rows Removed by Filter: 17534"
" Buffers: shared hit=43555"
"Total runtime: 899.652 ms"Il y avait également beaucoup de données de supervision disponibles sur le
système d’exploitation, montrant que les disques, les processeurs et la
mémoire vive avaient toujours des ressources disponibles, et il n’y avait aucun
message intéressant dans la sortie de dmesg ou aucune autre trace système.
Pour la première requête, nous voyons que le parcours d’index interne augmente de 0.001ms à 0.6ms:
-> Index Only Scan using idx on pg_database (actual time=0.001..0.001 rows=1 loops=1088)
-> Index Only Scan using idx on pg_database (actual time=0.610..0.611 rows=1 loops=1088)Avec un shared_buffers particuli_rement haut et une version de PostgreSQL
ancienne, il est fréquent que des problèmes de lenteur surviennent si la taille
du jeu de données est plus important que le shared_buffers, du fait de
l’algorithme dit de « clocksweep » utilisé pour sortir les entrées du
shared_buffers.
Cependant, la seconde requête montre que le même problème survient alors que
tous les blocs se trouvent dans le shared_buffers. Cela ne peut donc pas
être un problème d’éviction de buffer dû à une valeur de shared_buffers trop
élevée, ou un problème de latence sur le disque.
Bien que des paramètres de configuration de PostgreSQL puissent être améliorés, aucun de ceux-ci ne peuvent expliquer ce comportement en particulier. Il serait tout à fait possible que la modification de ces paramètres corrige le problème, mais il faut plus d’informations pour comprendre ce qui se passe exactement et éviter tout problème de performance à l’avenir.
Puisque les explications les plus simples ont déjà été écartées, il faut penser à des causes de plus bas niveau.
Si vous avez suivi les améliorations dans les dernières versions de PostgreSQL, vous devriez avoir noté un bon nombre d’optimisations concernant la scalabilité et le verrouillage. Si vous voulez plus de détails sur ces sujets, il y a de nombreux articles de blogs, par exemple ce très bon article.
Du côté du du noyau Linux, étant donné le grand nombre de connexions cela peut ếgalement être, et c’est certainement l’explication la plus probable, dû à une saturation du TLB.
Dans tous les cas, pour pouvoir confirmer une théorie il faut utiliser des outils beaucoup plus pointus.
Sans aller trop dans le détail, il faut savoir que chaque processus a une zone
de mémoire utilisée par le noyau pour stocker les « page tables
entries », ou PTE,
c’est-à-dire les translations des adresses virtuelles utilisées par le
processus et la vrai adresse physique en RAM. Cette zone n’est normalement pas
très volumineuse, car un processus n’accès généralement pas à des dizaines de
giga-octets de données en RAM. Mais puisque PostgreSQL repose sur une
architecture où chaque connexion est un processus dédié qui accède à un gros
segment de mémoire partagée, chaque processus devra avoir une translation
d’adresse pour chaque zone de 4 Ko (la taille par défaut d’une page) du
shared_buffers qu’il aura accédé. Il est donc possible d’avoir une grande
quantité de mémoire utilisée pour la PTE, et même d’avoir au total des
translations d’adresse pour adresser bien plus que la quantité total de mémoire
physique disponible sur la machine.
Vous pouvez connaître la taille de la PTE au niveau du système d’exploitation
en consultant l’entrée VmPTE dans le statut du processus. Vous pouvez
également vérifier l’entrée RssShmem pour savoir pour combien de pages en
mémoire partagée il existe des translations. Par exemple :
egrep "(VmPTE|RssShmem)" /proc/${PID}/status
RssShmem: 340 kB
VmPTE: 140 kBCe processus n’a pas accédé à de nombreux buffers, sa PTE est donc petite. If nous essayons avec un processus qui a accédé à chacun des buffers d’un shared\hbuffers de 8 Go :
egrep "(VmPTE|RssShmem)" /proc/${PID}/status
RssShmem: 8561116 kB
VmPTE: 16880 kBIl y a donc 16 Mo utilisés pour la PTE ! En multipliant ça par le nombre
de connexion, on arrive à plusieurs giga-octets de mémoire utilisée pour la
PTE. Bien évidemment, cela ne tiendra pas dans le TLB. Par conséquent, les
processus auront de nombreux « échec de translation » (TLB miss) quand ils
essaieront d’accéder à une page en mémoire, ce qui augmentera la latence de
manière considérable.
Sur le système qui rencontrait ces problèmes de performance, avec 16 Go de shared_buffers et 1500 connexions persistente, la mémoire totale utilisée pour les PTE combinées était d’environ 45 Go ! Une approximation peut être faîte avec le script suivant:
for p in $(pgrep postgres); do grep "VmPTE:" /proc/$p/status; done | awk '{pte += $2} END {print pte / 1024 / 1024}'NOTE: Cet exemple calculera la mémoire utilisée pour la PTE de tous les processus postgres. Si vous avez de plusieurs instances sur la même machine et que vous voulez connaître l’utilisation par instance, vous devez adapter cette commande pour ne prendre en compte que les processus dont le ppid est le pid du postmaster de l’instance voulue.
C’est évidemment la cause des problèmes rencontrés. Mais pour en être sûr,
regardons ce que perf nous remonte lorsque les problèmes de performance
surviennent, et quand ce n’est pas le cas.
Voici les fonctions les plus consommatrices (consommant plus de 2% de CPU) remontées par perf lorsque tout va bien :
# Children Self Command Symbol
# ........ ........ ............... ..................
4.26% 4.10% init [k] intel_idle
4.22% 2.22% postgres [.] SearchCatCacheRien de vraiment bien intéressant, le système n’est pas vraiment saturé. Maintenant, quand le problème survient :
# Children Self Command Symbol
# ........ ........ ............... ....................
8.96% 8.64% postgres [.] s_lock
4.50% 4.44% cat [k] smaps_pte_entry
2.51% 2.51% init [k] poll_idle
2.34% 2.28% postgres [k] compaction_alloc
2.03% 2.03% postgres [k] _spin_lockNous pouvons voir s_lock, la fonction de PostgreSQL qui attend sur un
spinlock consommant preque 9% du
temps processeur. Mais il s’agit de PostgreSQL 9.3, et les ligthweight locks
(des verrous internes transitoires) étaient encore implémentés à l’aide de spin
lock (ils sont maintenant implémentés à l’aide d’opérations
atomiques).
Si nous regardons un peu plus en détails les appeks à s_lock :
8.96% 8.64% postgres [.] s_lock
|
---s_lock
|
|--83.49%-- LWLockAcquire
[...]
|--15.59%-- LWLockRelease
[...]
|--0.69%-- 0x6382ee
| 0x6399ac
| ReadBufferExtended
[...]99% des appels à s_lock sont en effet dûs à des lightweight locks. Cela
indique un ralentissement général et de fortes contentions. Mais cela n’est que la conséquence du vrai problème, la seconde fonction la plus consommatrice.
Avec presque 5% du temps processeur, smaps_pte_entry, une fonction du noyau
effectuant la translation d’addresse pour une entrée, nous montre le problème.
Cette fonction devrait normalement être extrêmement rapide, et ne devrait même
pas apparaître dans un rapport perf ! Cela veut dire que très souvent, quand
un processus veut accéder à une page en mémoire, il doit attendre pour obtenir
sa vraie adresse. Mais attendre une translation d’adresse veut dire beaucoup
de bulles (pipeline stalls).
Les processeurs ont des pipelines de plus en plus profonds, et ces bulles
ruinent complètement les bénéfices de ce type d’architecture. Au final, une
bonne proportion du temps est tout simplement gâchée à attendre des adresses.
Ça explique très certainement les ralentissements extrêmes, ainsi que le manque
de compteurs de plus haut niveau permettant de les expliquer.
Plusieurs solutions sont possibles pour résoudre ce problème.
La solution habituelle est de demande à PostgreSQL d’allouer le
shared_buffers dans des huge
pages.
En effet, avec des pages de 2 Mo plutôt que 4 ko, la mémoire utilisée pour la
PTE serait automatiquement diminuée d’un facteur 512. Cela serait un énorme
gain, et extrêment facile à mettre en place. Malheureusement, cela n’est
possible qu’à partir de la version 9.4, mais mettre à jour la version majeure
de PostgreSQL n’était pas possible puisque l’éditeur ne supporte pas une
version supérieure à la version 9.3.
Un autre moyen de réduire la taille de la PTE est de réduire le nombre de connexion, qui ici est assez haut, ce qui aurait probablement d’autres effets positifs sur les performances. Encore une fois, ce n’était malheureusement pas possible puisque l’éditeur affirme ne pas supporter les poolers de connexion et que le client a besoin de pouvoir gérer un grand nombre de connexions.
Ainsi, la seule solution restante était donc de réduire la taille du shared_buffers. Après quelques essais, la plus haute valeur qui pouvaient être utilisée sans que les ralentissements extrêmes ne surviennent était de 4 Go. Heureusement, PostgreSQL était capable de conserver des performances assez bonnes avec cette taille de cache dédié.
Si des des éditeurs logiciels lisent cette article, il faut comprendre que si on vous demande la compatibilité avec des versions plus récentes de PostgreSQL, ou avec des poolers de connexion, il y a de très bonnes raisons à cela. Il y a généralement très peu de changements de comportement avec les nouvelles versions, et elles sont toutes documentées !
Diagnostique de lenteurs inattendues was originally published by Julien Rouhaud at rjuju's home on July 11, 2018.
Dans ce post, je vais vous expliquer comment mettre en place une streaming replication avec PostgreSQL 10. Par contre, je n’expliquerais pas comment installer PostgreSQL donc je suppose que cela est déjà le cas.
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.
Après avoir attendu tellement de temps pour cette fonctionnalité, HypoPG ajoute le support des index hypothétiques dans PostgreSQl, sous la forme d’une extension.
Cela fait maintenant quelques temps que la deuxième version de PoWA a été annoncée. Une des nouvelles fonctionnalités de cette version est l’extension pg_qualstats, écrite par Ronan Dunklau.
Grâce à cette extension, il est maintenant possible de collecter des statistiques en temps réel afin de détecter des index manquants, et bien plus encore (si cette extension vous intéresse, je vous conseille de lire l’article de Ronan sur pg_qualstats, en anglais). De plus, si vous l’utilisez avec PoWA, vous aurez une interface qui vous permettra de trouver les requêtes les plus coûteuses, et suggèrera des index manquants si c’est le cas.
Ce sont des fonctionnalités vraiment intéressantes, mais maintenant de nombreuses personnes posent cette question toute naturelle : Ok, PoWA me dit qu’il faut que je créé cet index, maix au final est-ce que PostgreSQL l’utilisera ?. C’est une très bonne question, car en fonction de nombreux paramètres de configuration (entre autres), PostgreSQL pourrait choisir de simplement ignorer votre index fraîchement créé. Et si vous avez du attendre plusieurs heures pour sa construction, ça serait une surprise plutôt déplaisante.
Bien evidemment, la réponse à cette question est le support des index hypothétiques. Il ne s’agit vraiment pas d’une nouvelle idée, de nombreux moteurs de bases de données les supportent déjà.
Il y a d’ailleurs déjà eu de précédents travaux sur le sujet il y a quelques années, dont les résultats ont été présentés au pgCon 2010. Ces travaux allaient beaucoup plus loin que le support des index hypothétiques, mais il s’agissait d’un travail de recherche, ce qui signifie que les fonctionnalités qui avaient été développées n’ont jamais vues le jour dans la version officielle de PostgreSQL. Tout cet excellent travail est malheureusement uniquement disponible sous la forme de fork de quelques versions spécifiques de PostgreSQL, la plus récentes étant la 9.0.1.
J’ai utilisé une approche différente pour implémenter les index hypothétiques avec HypoPG.
La seule restriction pour implémenter cette fonctionnalité sous la forme d’une extension est qu’il n’est pas possible de modifier la syntaxe sans modifier le code source de PostgreSQL. Donc tout doit être géré dans des procédures stockées, et le comportement des fonctionnalités existantes, comme la commande EXPLAIN, doit être modifié. On verra cela en détail juste après.
Pour le moment, les fonctions suivantes sont disponibles :
Si des index hypothétiques existent pour des tables utilisées dans une commande EXPLAIN (sans ANALYZE), ils seront automatiquement ajoutés à la liste des vrais index. PostgreSQL choisira alors s’il les utilise ou non.
Installer HypoPG est plutôt simple. En partant du principe que vous avez téléchargé et extrait une archive tar dans le répertoire hypopg-0.0.1, que vous utilisez une version packagée de PostgreSQL et que vous disposez des paquets -dev :
$ cd hypopg-0.0.1
$ make
$ sudo make installHypoPG devrait alors être disponible :
rjuju=# CREATE EXTENSION hypopg ;
CREATE EXTENSIONVoyons quelques tests simplistes. D’abord, créons une petite table :
rjuju=# CREATE TABLE testable AS SELECT id, 'line ' || id val
rjuju=# FROM generate_series(1,1000000) id;
SELECT 100000
rjuju=# ANALYZE testable ;
ANALYZEEnsuite, voyons un plan d’exécution qui pourrait bénéficier d’un index qui n’est pas présent :
rjuju=# EXPLAIN SELECT * FROM testable WHERE id < 1000 ;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on testable (cost=0.00..17906.00 rows=916 width=15)
Filter: (id < 1000)
(2 rows)Sans surprise, un parcours séquentiel est le seul moyen de répondre à cette requête. Maintenant, essayons d’ajouter un index hypothétique, et refaisons un EXPLAIN :
rjuju=# SELECT hypopg_create_index('CREATE INDEX ON testable (id)');
hypopg_create_index
---------------------
t
(1 row)
Time: 0,753 ms
rjuju=# EXPLAIN SELECT * FROM testable WHERE id < 1000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using <41079>btree_testable_id on testable (cost=0.30..28.33 rows=916 width=15)
Index Cond: (id < 1000)
(2 rows)Oui ! Notre index hypothétique est utilisé. On remarque aussi que le temps de création de l’index hypothétique est d’environ 1ms, ce qui est bien loin du temps qu’aurait pris la création de cet index.
Et bien entendu, cet index hypothétique n’est pas utilisé dans un EXPLAIN ANALYZE :
rjuju=# EXPLAIN ANALYZE SELECT * FROM testable WHERE id < 1000 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on testable (cost=0.00..17906.00 rows=916 width=15) (actual time=0.076..234.218 rows=999 loops=1)
Filter: (id < 1000)
Rows Removed by Filter: 999001
Planning time: 0.083 ms
Execution time: 234.377 ms
(5 rows)Maintenant essayons d’aller un peu plus loin :
rjuju=# EXPLAIN SELECT * FROM testable
rjuju=# WHERE id < 1000 AND val LIKE 'line 100000%';
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using <41079>btree_testable_id on testable (cost=0.30..30.62 rows=1 width=15)
Index Cond: (id < 1000)
Filter: (val ~~ 'line 100000%'::text)
(3 rows)Notre index hypothétique est toujours utilisé, mais un index sur id et val devrait aider cette requête. De plus, comme il y a un joker sur le côté droit du motif de recherche du LIKE, la classe d’opérateur text_pattern_ops est requise. Vérifions ça :
rjuju=# SELECT hypopg_create_index('CREATE INDEX ON testable (id, val text_pattern_ops)');
hypopg_create_index
---------------------
t
(1 row)
Time: 1,194 ms
rjuju=# EXPLAIN SELECT * FROM testable
rjuju=# WHERE id < 1000 AND val LIKE 'line 100000%';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Index Only Scan using <41080>btree_testable_id_val on testable on testable (cost=0.30..26.76 rows=1 width=15)
Index Cond: ((id < 1000) AND (val ~>=~ 'line 100000'::text) AND (val ~<~ 'line 100001'::text))
Filter: (val ~~ 'line 100000%'::text)
(3 rows)Et oui, PostgreSQL décide d’utiliser notre nouvel index !
Il y a pour le moment une estimation rapide de la taille d’index, qui peut nous donner un indice sur la taille que ferait un vrai index.
Vérifions la taille estimée de nos deux index hypothétiques :
rjuju=# SELECT indexname,pg_size_pretty(hypopg_relation_size(indexrelid))
rjuju=# FROM hypopg();
indexname | pg_size_pretty
-------------------------------+----------------
<41080>btree_testable_id | 25 MB
<41079>btree_testable_id_val | 49 MB
(2 rows)Maintenant, créons les vrais index, et comparons l’espace occupé :
rjuju=# CREATE INDEX ON testable (id);
CREATE INDEX
Time: 1756,001 ms
rjuju=# CREATE INDEX ON testable (id, val text_pattern_ops);
CREATE INDEX
Time: 2179,185 ms
rjuju=# SELECT relname,pg_size_pretty(pg_relation_size(oid))
rjuju=# FROM pg_class WHERE relkind = 'i' AND relname LIKE '%testable%';
relname | pg_size_pretty
---------------------+----------------
testable_id_idx | 21 MB
testable_id_val_idx | 30 MBLa taille estimée est un peu plus haute que la taille réelle. C’est volontaire. En effet, si la taille estimée était moindre que celle d’un index existant, PostgreSQL préférerait utiliser l’index hypothétique plutôt que le vrai index, ce qui n’est absolument pas intéressant. De plus, pour simuler un index fragmenté (ce qui est vraiment très fréquent sur de vrais index), un taux de fragmentation fixe de 20% est ajoutée. Cependant, cette estimation pourrait être largement améliorée.
Cette version 0.0.1 d’HypoPG est un travail en cours, et il reste encore beaucoup de travail à accomplir.
Voilà les principales limitations (du moins qui me viennent à l’esprit) :
Cependant, cette version peut déjà être utile dans de nombreux contextes.
Maintenant, la prochaine étape est d’implémenter le support d’HypoPG dans PoWA, pour aider les DBA à décider s’ils devraient ou non créer les index suggérés, et supprimer les limitations actuelles.
Si vous voulez essayer HypoPG, le dépôt est disponible ici : github.com/HypoPG/hypopg.
À très bientôt pour la suite !
Parlons des index hypothétiques was originally published by Julien Rouhaud at rjuju's home on July 02, 2015.
Le projet PostgreSQL possède depuis peu son propre dépôt APT pour les différentes versions des serveurs encore maintenu et PgAdmin3, sur les versions de Debian et Ubuntu suivantes
Si vous utilisiez déjà le dépôt squeeze backports par exemple, vous pouvez basculer facilement vers ce nouveau dépôt sans problème
Avant d'installer une version de PostgreSQL, il faut ajouter la clé d'authentification des paquets dans notre trousseau de clé.
wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
Il faut ensuite ajouter le dépôt au source.list, pour cela créer le fichier /etc/apt/sources.list.d/pgdg.list et ajouter les lignes suivantes (l'exemple ci-dessous est pour la version squeeze).
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main
Remplacer Squeeze par le nom de votre distribution.
Pour indiquer à votre distribution de prendre et mettre à jour votre ou vos PostgreSQL à partir de cette source, il faut rajouter une configuration dite de pinning
Créer le fichier /etc/apt/preferences.d/pgdg.pref et ajouter les lignes suivantes
Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500
Une fois la configuration, il faut faire un update pour mettre a jour votre gestionnaire de paquet avec ce nouveau dépôt, et charger le trousseau de clé
apt-get update
apt-get install pgdg-keyring
Ensuite il en reste plus qu'a installer la version de PostgreSQL que vous souhaitez.
apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2 postgresql-plpython-9.2 postgresql-server-dev-9.2 libpq-dev
Mon activité professionnelle chez Bull m'amène à travailler très régulièrement avec la CNAF (Caisse Nationale d'Allocations Familiales).
En 2008-2009, j'ai eu la joie de participer activement à la mise en place de PostgreSQL pour une application éminemment critique, celle qui supporte le cœur du métier des CAF : le calcul et le paiement des prestations sociales.
Cette semaine, j'ai pu compiler diverses statistiques techniques. Quelques règles de 3 plus tard, j'en suis arrivé à la conclusion que nous venions tout juste de franchir la barre symbolique des :
1 500 milliards de requêtes SQL exécutées !
et ceci sans que le SGBD ne soit jamais pris en défaut.
Quelqu'un doutait-il de la robustesse de PostgreSQL ?
Ce billet est le premier d'une série consacrée au projet open source E-Maj.
En deux mots, de quoi s'agit-il ?
E-Maj est une extension au SGBD PostgreSQL. Comme lui, il est disponible sous licence open source, en l’occurrence pour E-Maj la licence GPL.
Il permet d'enregistrer les mises à jour apportées à des tables relationnelles dans l'intention soit de les consulter soit de les annuler. La consultation permet par exemple d'analyser le comportement d'une application ou simplement d'avoir une trace des changements de contenu de tables. Quant à l'annulation des mises à jour, elle peut permettre de repositionner des tables dans un état prédéfini, en annulant l'effet d'un ou plusieurs traitements sur leur contenu.
Et je vous entends déjà dire. « Bon d'accord, il s'agit encore d'un outil de log de mises à jour. Mais il en existe déjà plusieurs dans le monde PostgreSQL. Alors pourquoi en inventer un autre ? ».
Effectivement, des contribs existent, tel que l'excellent table_log d'Andreas Scherbaum. Et le wiki de la communauté montre en détail comment se faire ses propres fonctions (wiki.postgresql.org/wiki/Audit_trig... et //wiki.postgresql.org/wiki/Audit_tr...)
Mais E-Maj présente deux grandes caractéristiques qui le rendent unique (du moins à ma connaissance !) : la manipulation d'ensembles de tables et la présence d'une interface graphique. Alors détaillons un peu ces deux aspects.
L'un des concepts sur lesquels E-Maj a été bâti est le « groupe de tables » (ou Tables Group en anglais). Il s'agit de mettre dans un même paquet, le « groupe de tables », toutes les tables qui vivent au même rythme, c'est à dire dont il faudra nécessairement, le cas échéant, annuler les mises à jour de manière cohérente. Par exemple, si j'ai une table des commandes et une tables des lignes de commande, il serait absurde de pouvoir annuler un ensemble de mises à jour sur l'une des tables sans annuler également les mises à jour de l'autre table. D'ailleurs la présence probable d'une clé étrangère (Foreign Key) entre ces deux tables nous rappellerait rapidement à l'ordre ! Avec E-Maj, le seul objet manipulable par l'utilisateur est le groupe de tables, et annuler les mises à jour d'une seule table est impossible (à moins bien sûr d'avoir un groupe de tables ne comprenant qu'une unique table).
E-Maj comprend en fait deux grands composants :
Mais au fait, d'où vient ce nom étrange d'E-Maj ?
Il s'agit tout simplement de l'acronyme, en français, de « Enregistrement des Mises A Jour ». Oui, je sais, ce n'est pas très original. Mais il fallait trouver un nom. Et puis, prononcé à l'anglaise, cela ressemble au mot « image », E-Maj permettant en effet de reconstruire des sortes d'images de bases de données.
Nous aurons l'occasion de présenter plus en détail cette extension dans les prochains billets.
Une dernière information avant de vous quitter pour répondre aux impatients. Comment se procurer E-Maj ?
E-Maj est disponible sur pgfoundry, et sur le site des extensions PostgreSQL PGXN.org. Et toute la documentation, en français et en anglais, est bien sûr contenu dans le support.
Deux dépôts sur github sont aussi accessibles :
Le plugin phpPgAdmin n'est pas encore disponible en libre service (je vous dirai bientôt pourquoi). Mais il suffit de me le demander par email (phb point emaj at free point fr).
A suivre...
Pour connaitre la taille d'un base de données il faut utiliser la fonction pg_database_size
production=# select pg_database_size('production');
pg_database_size
------------------
513343780
(1 ligne)
Cette taille est donnée en octets, pour avoir une meilleur représentation en Méga ou Giga, il faut utiliser la fonction pg_size_pretty
production=# select pg_size_pretty(pg_database_size('production'));
pg_size_pretty
----------------
490 MB
(1 ligne)
Ensuite si l'on souhaite connaître la taille d'un table il faut utiliser la fonction pg_relation_size.
production=# select pg_size_pretty(pg_relation_size('res_partner'));
pg_size_pretty
----------------
152 kB
(1 ligne)
Si l'on souhaite également avoir la place prise par les indexes, il faut utiliser la fonction pg_total_relation_size
production=# select pg_size_pretty(pg_total_relation_size('res_partner'));
pg_size_pretty
----------------
528 kB
(1 ligne)
Dernière mise à jour:
lundi 6 février 2023 à 21h30
Propulsé par:
