Connexion à une instance PostgreSQL

La connexion à une instance PostgreSQL utilise le protocole client-serveur implanté par la libpq, fournie avec PostgreSQL.

Cette bibliothèque offre plusieurs méthodes pour indiquer les coordonnées de l’instance PostgreSQL.

Cet article propose de revoir les différentes possibilités. C’est peut-être l’opportunité de mettre en place des configurations plus souples et stables que celles couramment utilisées.

Options de connexion

Pour se connecter à une instance PostgreSQL, il faut fournir plusieurs informations servant à trouver l’instance, puis à s’y authentifier :

• le nom d’hôte ou l’adresse IP du système où fonctionne l’instance ;
• le port TCP sur lequel écoute l’instance, par défaut 5432 ;
• le nom de la base de données de l’instance ;
• le nom du rôle ;
• et, très souvent, un mot de passe.

Ces différentes informations sont utilisées pour contacter une instance PostgreSQL, et tenter d’y ouvrir une connexion. La plupart des outils « client » utilisent les mêmes options :

• -h : Nom d’hôte ou adresse IP, voire chemin vers la « socket Unix » ;
• -p : Numéro de port TCP, par défaut 5432 ;
• -U : Nom du rôle, par défaut le même nom que l’utilisateur système exécutant le client ;
• -d : Nom de la base de données, dans l’instance, par défaut le même nom que le rôle.

Ces options ne sont pas obligatoires, ayant toutes une valeur par défaut.

Selon la configuration de l’instance PostgreSQL, et la méthode d’appel de l’application cliente, il est possible de se connecter à PostgreSQL sans préciser aucune valeur.

Par exemple, dans un système Debian ou Redhat, en utilisant le même utilisateur système que celui exécutant l’instance (par défaut postgres), il suffit de lancer la commande psql pour ouvrir une session interactive dans l’instance :

postgres@pg10:~$ psql
psql (10beta3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

La commande \conninfo de psql permet de voir quels sont les paramètres réels de la connexion.

Il est également possible de préciser les options de connexions dans la ligne de commande :

postgres@pg10:~$ psql -h pg10 -Upostgres
psql (10beta3)
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

On remarque ici que la connexion utilise TLS, c’est-à-dire que les données sont chiffrées entre le client et le serveur ; ceci est négocié au moment de l’ouverture de la connexion.

Mots de passe

Lorsque la connexion exige un mot de passe, en suivant la méthode présentée, il doit être saisi interactivement ; il n’existe pas d’option permettant de passer le mot de passe en paramètre de la commande :

postgres@pg10:~$ psql -W -h pg10 -Upostgres
Password for user postgres:
psql (10beta3)
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Dans l’exemple, l’option -W permet de forcer l’invite du mot de passe.

Variables d’environnement

Il est possible de se connecter de la même façon en utilisant des variables d’environnement, qui sont définies dans l’interpréteur de commande de l’utilisateur. Les variables d’environnements disponibles sont :

• PGHOST pour le nom d’hôte ou PGHOSTADDR pour une adresse IP ;
• PGPORT pour le numéro de port TCP ;
• PGDATABASE pour le nom de la base de données ;
• PGUSER pour le nom du rôle ;
• PGPASSWORD pour le mot de passe ;
• PGAPPNAME pour le nom de l’application, correspondant à application_name côté serveur.

L’exemple suivant permet de se connecter en utilisant ces variables :

postgres@pg10:~$ export PGHOST=pg10
postgres@pg10:~$ export PGPASSWORD=mybadpasswd
postgres@pg10:~$ psql
psql (10beta3)
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Idéalement, ces variables seront enregistrées dans les fichiers d’environnement de l’interpréteur de commandes.

Attention, le stockage du mot de passe en clair dans un tel fichier est loin d’être idéal.

Chaînes de connexion

Autre technique pour se connecter, l’utilisation de chaînes de connexion, technique largement connue des développeurs.

Cette méthode fonctionne aussi pour les outils en ligne de commande.

Il existe deux formats de chaînes. La première sous la forme d’une URI, et la seconde sous la forme d’un ensemble de paires clé-valeur.

URI de connexion

L’URI de connexion est régulièrement utilisée dans des langages tels que Java, mais peut tout à fait être utilisée dans d’autres cadres. La forme de l’URI est la suivante :

postgres://user:password@host:port/dbname?params

Par exemple :

postgres@pg10:~$ psql postgres://postgres:mybadpasswd@pg10/postgres
psql (10beta3)
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Clé/Valeur

L’autre format de chaîne de connexion utilise des clés, correspondant aux paramètres déjà décrits :

• host : Nom d’hôte, ou hostaddr pour une adresse IP ;
• port : Numéro de port ;
• user : Nom de rôle ;
• dbname : Nom de la base de données ;
• password : Mot de passe ;
• application_name : Nom de l’application.

Ces paramètres se combinent avec leurs valeurs en une chaîne, comme dans l’exemple suivant :

postgres@pg10:~$ psql "host=pg10 user=postgres password=mybadpasswd"
psql (10beta3)
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".
SSL connexion (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Cette chaîne, aisément forgée, peut être stockée dans une variable pour être utilisée.

Fichier de service

En utilisant les mêmes clés que dans l’exemple précédent, il est possible de définir un fichier de service contenant plusieurs connexions distinctes, identifiées par un mot-clé.

Ce mot-clé identifie le service utilisé par le client pour ouvrir la connexion.

Le choix de ce mot-clé est laissé à l’utilisateur.

Le fichier peut être spécifique à un utilisateur, ou bien global à un système.

Le fichier « utilisateur » est ~/.pg_service.conf, donc un fichier caché dans le répertoire utilisateur ; ce fichier peut être surchargé par la variable d’environnement PGSERVICEFILE.

Le fichier global dépend de la façon dont à été compilé PostgreSQL. Dans les systèmes Debian, le fichier est /etc/postgresql-common/pg_service.conf et dans les systèmes Redhat /etc/sysconfig/pgsql/pg_service.conf.

Un service est défini comme dans l’exemple suivant :

[cnxpg10]
host=pg10
user=postgres
dbname=postgres
password=mybadpasswd

Ensuite, il est possible d’utiliser ce service, soit avec la clé service, soit avec la variable d’environnement PGSERVICE :

postgres@pg10:~$ psql "service=cnxpg10"
psql (10beta3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".

postgres@pg10:~$ export PGSERVICE=cnxpg10
postgres@pg10:~$ psql
psql (10beta3)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pg10" at port "5432".

Cette technique permet de gérer les connexions depuis un fichier dédié ; ce qui est idéal lorsque les systèmes sont administrés depuis des outils de gestion de configuration tel qu’Ansible, ou équivalent.

Fichier de mots de passe

Il existe un fichier dédié aux mots de passe, afin d’éviter de stocker les mots de passe dans le fichier de service ou dans une variable d’environnement.

Ce fichier n’est protégé que par la vérification des droits Unix : si les droits sont trop larges, alors le fichier n’est pas utilisé. Dans ce cas, une erreur est levée par PostgreSQL.

Le format du fichier est simple : une ligne par connexion, en suivant le motif : hostname:port:database:username:password

Par exemple :

echo "pg10:5432:postgres:postgres:mybadpasswd" >> ~/.pgpass

Le mot de passe ayant été retiré du fichier de service, on utilise l’option -w pour ne pas demander de mot passe interactivement :

psql -w "service=cnxpg10"
WARNING: password file "/var/lib/postgresql/.pgpass" has group or world access; permissions should be u=rw (0600) or less
psql: fe_sendauth: no password supplied

On obtient une erreur à cause des droits mal positionnés sur le fichier .pgpass. Après correction :

postgres@pg10:~$ chmod 0600 .pgpass
postgres@pg10:~$ psql -w "service=cnxpg10"
psql (10beta3)

postgres=#

La connexion fonctionne !

Conclusion

La gestion des connexions peut s’avérer difficile. Notamment lors de l’utilisation de solutions de haute disponibilité, où les noms des machines peuvent changer. Les différentes possibilités présentées ici permettent de simplifier cette gestion.

Dans de prochains articles, nous aborderons des notions plus avancées de la connexion aux instances PostgreSQL.

Nous aborderons notamment l’utilisation de certificats TLS pour l’authentification, le nouveau protocole d’échange de mot de passe, ou encore la possibilité d’indiquer plusieurs noms d’hôte pour une connexion.

Lors du dernier PGDay j’ai fait une présentation sur le fonctionnement de la recherche plein texte (Full Text Search – FTS) dans PostgreSQL.

Cette fonctionnalité est malheureusement trop peu connue. Je vois plusieurs raisons à cela :

• Complexité : Le FTS fait appels à des notions inconnues d’un DBA : lemmatisation, représentation vectorielle d’un document…
• La tendance à utiliser un outil dédié à la recherche plein texte : Elasticsearch, SOLR …
• Ignorance des fonctionnalités avancées de PostgreSQL

Pourtant, il y a plusieurs avantages à utiliser le FTS de PostgreSQL :

• On conserve un seul et même langage : le SQL
• Pas de duplication des données entre la base de donnée et le moteur d’indexation
• On conserve une certaine cohérence :
  • Pas besoin de faire des synchronisations entre les données contenues dans votre base de donnée et le moteur d’indexation
  • Un document supprimé de la base de donnée ne sera pas oublié dans le moteur d’indexation
• On bénéficie de toutes les fonctionnalités avancées de PostgreSQL, notamment des index performants.
• Le moteur est extensible, on peut personnaliser la configuration FTS

Quand j’ai commencé à me plonger dans le FTS j’ai vite été rebuté par des termes qui m’était inconnus, des syntaxes particulières avec de nouveaux opérateurs. Au lieu de faire une présentation sur l’étendue des possibilités du FTS, je me suis donc attaché à présenter son fonctionnement par « la base ». C’est à dire toute la « mécanique » qui se cache derrière.

Voici la vidéo de la conférence :

Ainsi que les slides

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

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

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

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

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

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

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

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

Un schéma de test

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

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

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

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

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

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

ou pour un seul label:

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

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

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

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

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

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

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

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

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

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

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

  RETURN NEW;
END $$ LANGUAGE plpgsql;

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

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

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

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

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

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

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

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

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

Le test

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

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

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

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

Le résultat

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

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

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

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

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

Le PGDG publie un ensemble de correctifs. Il s’agit d’une mise à jour cumulative pour toutes les versions actuellement supportées de PostgreSQL.

Les versions publiées sont 9.6.5, 9.5.9, 9.4.14, 9.3.19 et 9.2.23.

Ce correctif inclut un patch qui corrige une instabilité de pg_restore lors d’une restauration en mode parallèle. Il corrige également quelques bugs recensés depuis la sortie du dernier correctif en août dernier.

Pour la version 9.4.13 seulement, un problème lié aux processus d’envoi des journaux de transaction (WAL) empêchait l’arrêt du serveur primaire autrement qu’avec le mode immediate. Ce souci a aussi été corrigé dans la version 9.4.14.

Nous vous recommandons de planifier une mise à jour lors de votre prochaine fenêtre de maintenance.

Correctifs et améliorations

Cette mise à jour embarque également un certain nombre d’anomalies rapportées ces dernières semaines, ainsi que quelques améliorations. Certaines ne concernent que la version 9.6, mais la plupart concernent toutes les versions supportées.

• Affichage des tables étrangères dans la vue information_schema.table_privileges. Cette amélioration ne concerne que les nouvelles bases de données. Merci de vous référer aux notes de version pour appliquer ce correctif sur une base existante ;
• Identification correcte des colonnes de type plage de valeurs ou de type domaine quand une recherche porte sur un type composite ou un de type domaine ;
• Correction du bug qui faisait crasher l’instance si un type de donnée de taille fixe était passé par référence à des processus en exécution parallèle ;
• Modification du parser ecpg pour permettre une clause RETURNING sans variable C attachée ;
• Modification du parser ecpg pour reconnaître la continuité de ligne via la barre oblique inverse (backslash) des lignes de commande du préprocesseur C ;
• Amélioration de la sélection des options de compilation pour PL/Perl sous Windows.

Avertissement de fin de vie de vie pour la version 9.2

PostgreSQL version 9.2 arrive en fin de vie en septembre 2017. Cette version ne fera vraisemblablement l’objet que d’une ultime mise à jour. Nous conseillons très fortement aux utilisateurs de cette version de planifier une montée de version le plus vite possible. Voir la politique de versionnement pour plus d’informations.

Mise à jour

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

Pour les détails, se référer aux notes de versions.

Liens

• Page de téléchargement : http://www.postgresql.org/download/
• Note de version : http://www.postgresql.org/docs/current/static/release.html
• Page de sécurité
• Politique de versionnement

Cet article est la suite d’une série d’articles sur la réplication logique dans la version 10 de PostgreSQL :

1. PostgreSQL 10 et la réplication logique – Fonctionnement
2. PostgreSQL 10 et la réplication logique – Mise en oeuvre
3. PostgreSQL 10 et la réplication logique – Restrictions

Celui-ci va porter sur les restrictions de la réplication logique.

Restrictions

La documentation de postgreSQL est très bien faite. Il y a toute une page sur les restrictions.

Ordres DDL

Le schéma et les ordres DDL ne sont pas répliqués. Il faudra donc veiller à bien reporter toute modification de schéma. Dans le cas contraire un conflit de réplication pourrait se produire ce qui bloque toute réplication. Une fois la correction apportée la réplication peut reprendre. Pour éviter toute erreur il est préférable d’appliquer les changements de schéma sur le “secondaire” en premier.

Séquences

Les séquences ne sont pas répliquées. Les données d’une colonne serial seront bien répliquées mais le compteur de la séquence ne sera pas incrémenté. Si le “secondaire” est seulement utilisé en lecture seule ça ne posera pas de problème. En revanche, en cas de bascule il faudra mettre à jour la séquence ou la placer à une valeur supérieure à la valeur la plus haute présente dans la table.

Ordre TRUNCATE

Les ordres TRUNCATE ne sont pas répliqués. Il peut être utile de révoquer le droit de TRUNCATE sur les tables répliquées avec : REVOKE TRUNCATE ON TABLE matable FROM utilisateur.

Large Objects

Les Large Objets ne sont pas répliqués

Schéma et tables

La réplication logique ne réplique que des tables, il n’est pas possible de répliquer des vues, vues matérialisées, partition racine, foreign tables… Les schéma « source » et « destination » doivent être identiques. Il n’est pas possible de répliquer la table t1 du schéma s1 dans un schéma s2 sur un secondaire.

Replica Identity

Pour les opérations UPDATE et DELETE, la table doit avoir une replica identity. Dans le cas contraire le moteur remontera une erreur. Par défaut c’est la clé primaire qui est utilisée.

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

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

Quand on cherche une donnée stockée dans un modèle relationnel, en principe on sait dans quelle table et colonne elle se trouve, on l’indique en SQL via une requête simple de la forme SELECT colonne FROM table WHERE condition, et le moteur se charge de trouver les lignes qui correspondent.

Mais quand on travaille sur une base qu’on ne connait pas, il arrive qu’on cherche à l’inverse dans quelle table et colonne (voire dans quel schéma) une valeur donnée peut bien avoir été stockée.

Le modèle relationnel n’est pas prévu pour faire ça efficacement, mais c’est quand même possible en cherchant dans tous les champs de toutes les tables de tous les schémas. Voyons comment y arriver avec PostgreSQL de la manière la moins coûteuse possible, et en permettant quand même de filtrer un peu la liste des tables où chercher.

Chercher via une fonction plpgsql

La question est comment faire l’équivalent d’un grep -F chaine * en SQL, où * représente toutes les tables?

Itérer sur les tables

D’abord il faut itérer sur le contenu de information_schema.tables, qui est une vue sortant notamment:

• table_schema
• table_name
• table_type

Ici on va filtrer sur table_type = 'BASE TABLE' pour chercher uniquement dans les tables persistantes locales.

Désigner une ligne particulière

Généralement, une ligne d’une table est désignée par la valeur de la clef primaire. Mais pour un affichage homogène applicable à toute table, ressortir cette valeur n’est pas forcément la meilleure idée, car d’une table à l’autre les clefs primaires peuvent être très hérérogènes, avec des tailles, type de contenu et nombres de colonnes variables. En plus il peut ne pas y en avoir, puisqu’il n’est pas interdit à une table de n’avoir aucune clef primaire.

Dans le cadre de cette recherche, on va plutôt retourner le pointeur interne de tuple ctid qui indique la position physique de la ligne. Cette valeur n’est pas durable mais sera suffisante le temps d’une recherche. En cas de crainte sur des modifications concurrentes, il est toujours possible d’initier la recherche dans une transaction avec le mode d’isolation repeatable read, ce qui garantira la stabilité de ces données dans le cadre de cette transaction.

Utiliser la représentation textuelle des lignes

En principe, on pourrait fabriquer et exécuter la requête SELECT nomcolonne,ctid FROM nomtable WHERE nomcolonne='valeur' pour chaque colonne de chaque table. Mais ça ferait beaucoup de requêtes et de comparaison.

Pour réduire ce nombre, on utilise une astuce consistant à faire une pré-recherche dans la ligne entière au format texte, obtenue par CAST(table.* AS text), qui sort les colonnes dans format similaire à celui du constructeur ROW() tel que décrit dans la syntaxe SQL En effet quand le terme cherché est égal à une valeur d’une colonne, il est forcément aussi présent dans la ligne considérée en entier.

La requête pour préfiltrer va être la suivante, avec %I.%I remplacés par schemaname.tablename et %L par le terme à chercher:

  SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0

Pour chaque ctid trouvé par cette requête, il reste à faire le test pour chaque colonne:

  SELECT true FROM %I.%I WHERE cast(%I /*colonne*/ as text)=%L AND ctid=%L

Faire état des progrès

Une recherche globale en base risque d’être longue, et une information vers l’utilisateur sur la progression de l’opération est toujours bonne à prendre. On utilise RAISE INFO, qui a l’avantage d’être transmis immédiatement au client, notamment psql qui l’affichera immédiatement, contrairement aux résultats du RETURN NEXT de la fonction, qui en interne sont accumulés et ne sont envoyés effectivement au client qu’une fois la fonction terminée.

Code complet

Voici une fonction complète qui prend les tables et schémas optionnels en paramètre, pour affiner là où on veut chercher, sous forme de tableaux. Elle offre également une option pour savoir quel type d’information de progression on souhaite:

• null (défaut) pour aucune
• tables pour savoir quelle table la fonction commence à examiner
• hits par résultat pour le connaitre sans attendre le retour de la fonction
• all pour avoir tables et hits combiné

Enfin elle retourne en résultat un ensemble d’enregistrements comportant le nom du schéma, le nom de la table, le nom et la colonne et le ctid de la ligne où la valeur à chercher a été trouvée.

CREATE OR REPLACE FUNCTION global_search(
    search_term text,
    param_tables text[] default '{}',
    param_schemas text[] default '{public}',
    progress text default null -- 'tables','hits','all'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid tid)
AS $$
declare
  query text;
  hit boolean;
begin
  FOR schemaname,tablename IN
      SELECT table_schema, table_name
      FROM information_schema.tables t
      WHERE (t.table_name=ANY(param_tables) OR param_tables='{}')
        AND t.table_schema=ANY(param_schemas)
        AND t.table_type='BASE TABLE'
  LOOP
    IF (progress in ('tables','all')) THEN
      raise info '%', format('Searching globally in table: %I.%I',
         schemaname, tablename);
    END IF;

    query := format('SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0',
	    schemaname,
	    tablename,
	    search_term);
    FOR rowctid IN EXECUTE query
    LOOP
      FOR columnname IN
	  SELECT column_name
	  FROM information_schema.columns
	  WHERE table_name=tablename
	    AND table_schema=schemaname
      LOOP
	query := format('SELECT true FROM %I.%I WHERE cast(%I as text)=%L AND ctid=%L',
	  schemaname, tablename, columnname, search_term, rowctid);
        EXECUTE query INTO hit;
	IF hit THEN
	  IF (progress in ('hits', 'all')) THEN
	    raise info '%', format('Found in %I.%I.%I at ctid %s',
		   schemaname, tablename, columnname, rowctid);
	  END IF;
	  RETURN NEXT;
	END IF;
      END LOOP; -- for columnname
    END LOOP; -- for rowctid
  END LOOP; -- for table
END;
$$ language plpgsql;

Exemples d’utilisation

Pour chercher ‘foo’ dans toutes les tables du schéma public :

 SELECT * FROM global_search('foo');

Pour chercher ‘foo’ dans toutes les tables des schémas s1, s2 :

 SELECT * FROM global_search('foo', param_schemas:=array['s1','s2']);

Pour chercher ‘foo’ dans toutes les tables sauf bigtable :

 SELECT * FROM global_search('foo', param_tables:=array(
    select table_name::text from information_schema.tables where table_name<>'bigtable')
 );

Chercher dans la sortie de pg_dump

Une autre possibilité parfois plus simple ou plus rapide à faire est d’exporter toute la base en fichier texte avec pg_dump, puis d’utiliser grep sur le résultat. Ca a le mérite qu’on n’a pas à écrire de code, mais il y a quand même quelques ombres au tableau:

• si pg_dump n’est pas en local sur le serveur, la totalité de la base va devoir transiter par le réseau, la recherche se faisant côté client.
• si on stocke temporairement l’export sur disque, notamment pour faire des recherches multiples, il faut estimer à l’avance combien d’espace disque ça va prendre, et évidemment avoir la place libre en question.

Indépendamment de ces difficultés potentielles, pour exploiter les résultats de grep, il faut plutôt extraire les tables avec les options --column-inserts, de façon à retrouver sur chaque ligne le nom de la table et des colonnes, ce qui au passage augmente encore le volume de l’export. En effet, les données sont au format COPY par défaut, donc en l’absence de cette option, si les lignes sorties par grep ont bien la donnée cherchée, elles n’ont ni le nom de la table ni celui de la colonne. Ces informations se trouvent au début de la commande COPY correspondant à la table en question, lequel peut très bien se trouver plusieurs millions de lignes plus haut.

Le filtrage par grep pose aussi des problèmes plus subtils sur des cas d’école, qui rendent vraiment difficile une solution robuste:

• s’il y a des sauts de ligne dans une colonne de la ligne qu’on cherche, grep sera incapable de distinguer un saut de ligne entre deux INSERT d’un saut de ligne à l’intérieur de champ, et pourra toujours sortir un morceau d’INSERT sans table ni colonne, malgré l’option --column-inserts sur l’extraction.

• l’extraction n’a pas les contenus verbatim, elle a nécessairement un format d’encapsulation, même léger, dans lequel certains caractères spéciaux seront forcément échappés. Si la chaîne à chercher contient ces caractères, il peut être compliqué de formuler l’expression exacte à chercher: il faut maîtriser à la fois les échappements syntaxiques de PostgreSQL et ceux des expressions régulières pour le grep, sans oublier ceux du shell via lequel on exprime la commande.

• s’il se trouve que le contenu à localiser est aussi un nom de colonne ou quelque chose d’autre dans l’export comme un mot utilisé dans le SQL courant, les résultats auront des faux positifs qu’il faudra post-filtrer manuellement.

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

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

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

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

COMMIT et ROLLBACK conditionnel

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

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

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

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

Syntaxe des expressions

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

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

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

Appeler le shell pour tester une condition

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

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

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

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

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

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

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

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

Utiliser l’interpréteur SQL pour tester une condition

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

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

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

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

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

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

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

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

\set ON_ERROR_STOP on

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT date_trunc('year',date_creation)::date as annee, count(*)
FROM domain_fr  WHERE idn=1 GROUP BY 1 ORDER BY 1;

   annee     | count 
 ------------+-------
  2012-01-01 | 20001
  2013-01-01 |  6900
  2014-01-01 |  4734
  2015-01-01 |  3754
  2016-01-01 |  3353
  2017-01-01 |   775
 (6 rows)

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

SELECT nom_be,count(*)
 FROM domain_fr
 WHERE date_creation>='2016-01-01'::date AND date_creation<'2017-01-01'
 GROUP BY 1
 ORDER BY 2 DESC
 LIMIT 10;

                                  nom_be                                  | count  
 -------------------------------------------------------------------------+--------
  OVH                                                                     | 207393
  1&1 Internet SE                                                         |  74446
  GANDI                                                                   |  63861
  ONLINE SAS                                                              |  15397
  LIGNE WEB SERVICES - LWS                                                |  14138
  AMEN / Agence des Médias Numériques                                     |  13907
  KEY-SYSTEMS GmbH                                                        |  12558
  PAGESJAUNES                                                             |  11500
  Ascio Technologies Inc. Danmark - filial af Ascio Technologies Inc. USA |   9303
  InterNetX GmbH                                                          |   9028

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

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

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

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

CREATE EXTENSION pg_trgm;

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

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


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

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

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

SET pg_trgm.similarity_threshold TO 0.5;

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

Ca donne 33 résultats:

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

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

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

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

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

Résultats:

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

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

Toulouse, Nantes ou Paris ? Vous avez l’embarras du choix 3 rendez-vous autour de PostgreSQL s’offrent à vous dans les jours à venir !

• Le 2 mars chez Air France à Toulouse

• Le mercredi 8 mars chez la Cordée à Nantes

• Le jeudi 9 mars chez Magency à Paris

A noter pour être complet qu’il y a aussi un groupe actif à Lyon et un autre à Lille

Et si vous en voulez encore, vous avez aussi deux conférences PostgreSQL en approche :

• Le PG Day Paris le 23 mars avec un programme très riche et intégralement en anglais

• Le PG Day France le 8 juin chez Météo France à Toulouse

Bref il y en a pour tous les gouts et aux 4 coins de la France :-)

Le prochain défi pour la communauté PostgreSQL est de produire des outils graphiques riches et bien conçus. C’est un domain dans lequel nous avons une grande marge de progression et Temboard est un des projets (parmi d’autres) qui a l’ambition de devenir un outil graphique pour PostgreSQL.

Puisque que nous parlons d’un outil graphique, allons directement jetez un coup d’oeuil ! Voici 3 étapes pour déployer un environement de test composé de 3 instances Postgres et un serveur central Temboard.

1- Installer docker et Installer docker-compose

2- Récuperer fichier docker compose pré-configuré et le lancer

wget https://raw.githubusercontent.com/dalibo/docker/master/temboard/docker-compose.yml
docker-compose up

3- Aller sur https://127.0.0.1:8888

• Connectez-vous sur le serveur Temboard avec les codes admin / admin
• Connectez-vous sur chaque instance Postgres avec alice / alice

Vous devriez voir apparaitre quelque chose comme ça :

C’est tout pour aujourd’hui ! Je ferai un autre article prochainement pour présenter les fonctionnalités de Temboard notamment le tableau de bord, la versionnement des fichiers postgresql.conf, comment tuer une transaction, etc.

En attendant si vous prenez le temps de tester cet outil, envoyez nous un message à contact@dalibo.com et dites-nous ce que vous en pensez, quelle fonctionnalités vous manquent !

Temboard est encore un projet très jeune et nous avons prévu d’ajouter plus de plugins dans les mois à venir !

Un événement communautaire

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

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

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

pgDay 2017 à Paris, soutenu par 2ndQuadrant

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

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

S’enregistrer et venir au pgDay 2017

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

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

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

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

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

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

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

Cache de données

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

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

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

Read Ahead

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

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

pgFincore 1.2

Cette extension permet donc:

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

Obtenir pgFincore

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

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

Besoin d’aide ?

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

Exemples d’utilisation

Installation

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

Information système

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

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

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

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

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

Audit du cache

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

Charger une table en mémoire

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

Vider le cache d’une table

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

Restaurer des pages en cache

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

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

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

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

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

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

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

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

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

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

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

Trois dates à retenir autour de PostgreSQL : 17 mars, à Nantes, un premier meetup, dans lequel j'évoquerai les nouveautés de PostgreSQL 9.5. 31 mars, à Paris, où j'essayerai de remonter le fil du temps de bases de données. 31 mai, à Lille, où je plongerai dans les structures du stockage de PostgreSQL. Ces trois dates sont l'occasion... Lire Dates à retenir

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

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

Les champs de recherche dans les applications web permettent de se voir propooser des résultats à chaque caractère saisies dans le formulaire, pour éviter de trop solliciter les systèmes de stockage de données, les modules standards permettent de définir une limite basse, la recherche n'étant effective qu'à partir du troisième caractères entrés. Cette limite de 3 caractères s'explique par la possibilité de simplement définir des index trigram dans les bases de données, pour PostgreSQL cela se fait avec l'extension standard pg_trgm, (pour une étude détaillé des trigrams je conseille la lecture de cet article).

Si cette technique a apporté beaucoup de confort dans l'utilisation des formulaires de recherche elle pose néanmoins le problème lorsque que l'on doit rechercher une chaîne de deux caractères, innoportun, contre-productif me direz-vous (je partage assez cet avis) mais imaginons le cas de madame ou monsieur Ba qui sont présent dans la base de données et dont on a oublié de saisir le prénom ou qui n'ont pas de prénom, ils ne pourront jamais remonter dans ces formulaires de recherche, c'est assez fâcheux pour eux.

Nous allons voir dans cet article comment résoudre ce problème, commençons par créer une table avec 50000 lignes de données text aléatoire :

CREATE TABLE blog AS SELECT s, md5(random()::text) as d 
   FROM generate_series(1,50000) s;
~# SELECT * from blog LIMIT 4;
 s |                 d                
---+----------------------------------
 1 | 8fa4044e22df3bb0672b4fe540dec997
 2 | 5be79f21e03e025f00dea9129dc96afa
 3 | 6b1ffca1425326bef7782865ad4a5c5e
 4 | 2bb3d7093dc0fffd5cebacd07581eef0
(4 rows)

Admettons que l'on soit un fan de musique des années 80 et que l'on recherche si il existe dans notre table du texte contenant la chaîne fff.

~# EXPLAIN ANALYZE SELECT * FROM blog WHERE d like '%fff%';

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on blog  (cost=0.00..1042.00 rows=5 width=37) (actual time=0.473..24.130 rows=328 loops=1)
   Filter: (d ~~ '%fff%'::text)
   Rows Removed by Filter: 49672
 Planning time: 0.197 ms
 Execution time: 24.251 ms
(5 rows)

Sans index on s'en doute cela se traduit pas une lecture séquentielle de la table, ajoutons un index. Pour indexer cette colonne avec un index GIN nous allons utiliser l'opérateur gin_trgm_ops disponible dans l'extension pg_trgm.

~# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
~# CREATE INDEX blog_trgm_idx ON blog USING GIN(d gin_trgm_ops);
CREATE INDEX
~# EXPLAIN ANALYZE SELECT * FROM blog WHERE d like '%fff%';
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on blog  (cost=16.04..34.46 rows=5 width=37) (actual time=0.321..1.336 rows=328 loops=1)
   Recheck Cond: (d ~~ '%fff%'::text)
   Heap Blocks: exact=222
   ->  Bitmap Index Scan on blog_trgm_idx  (cost=0.00..16.04 rows=5 width=0) (actual time=0.176..0.176 rows=328 loops=1)
         Index Cond: (d ~~ '%fff%'::text)
 Planning time: 0.218 ms
 Execution time: 1.451 ms

Cette fois l'index a pu être utilisé, on note au passage que le temps de requête est réduit d'un facteur 20, mais si l'on souhaite désormais rechercher une chaîne de seulement 2 caractères de nouveau une lecture séquentielle a lieu, notre index trigram devient inefficace pour cette nouvelle recherche.

~# EXPLAIN ANALYZE SELECT * FROM blog WHERE d like '%ff%';
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on blog  (cost=0.00..1042.00 rows=3030 width=37) (actual time=0.016..11.712 rows=5401 loops=1)
   Filter: (d ~~ '%ff%'::text)
   Rows Removed by Filter: 44599
 Planning time: 0.165 ms
 Execution time: 11.968 ms

C'est ici que vont intervenir les index bigram, qui comme leur nom l'index travaille sur des couples et non plus des triplets. En premier nous allons tester pgroonga, packagé pour Debian, Ubuntu, CentOS et d'autres systèmes exotiques vous trouverez toutes les explications pour le mettre en place sur la page d'install du projet.

Les versions packagées de la version 1.0.0 ne supportent actuellement que les versions 9.3 et 9.4, mais les sources viennent d'être taguées 1.0.1 avec le support de la 9.5.

CREATE EXTENSION pgroonga;

La création de l'index se fait ensuite en utilisant

~# CREATE INDEX blog_pgroonga_idx ON blog USING pgroonga(d);
CREATE INDEX
~# EXPLAIN ANALYZE SELECT * FROM blog WHERE d like '%ff%';
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on blog  (cost=27.63..482.51 rows=3030 width=37) (actual time=3.721..5.874 rows=2378 loops=1)
   Recheck Cond: (d ~~ '%ff%'::text)
   Heap Blocks: exact=416
   ->  Bitmap Index Scan on blog_pgroonga_idx  (cost=0.00..26.88 rows=3030 width=0) (actual time=3.604..3.604 rows=2378 loops=1)
         Index Cond: (d ~~ '%ff%'::text)
 Planning time: 0.280 ms
 Execution time: 6.230 ms

On retrouve une utilisation de l'index, avec comme attendu un gain de performance.

Autre solution : pg_bigm qui est dédié plus précisément aux index bigram, l'installation se fait soit à partie de paquets RPM, soit directement depuis les sources avec une explication sur le site, claire et détaillée. pg_bigm supporte toutes les versions depuis la 9.1 jusqu'à 9.5.

~# CREATE INDEX blog_bigm_idx ON blog USING GIN(d gin_bigm_ops);
CREATE INDEX
~# EXPLAIN ANALYZE SELECT * FROM blog WHERE d like '%ff%';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on blog  (cost=35.48..490.36 rows=3030 width=37) (actual time=2.121..5.347 rows=5401 loops=1)
   Recheck Cond: (d ~~ '%ff%'::text)
   Heap Blocks: exact=417
   ->  Bitmap Index Scan on blog_bigm_idx  (cost=0.00..34.73 rows=3030 width=0) (actual time=1.975..1.975 rows=5401 loops=1)
         Index Cond: (d ~~ '%ff%'::text)
 Planning time: 4.406 ms
 Execution time: 6.052 ms

Sur une table de 500k tuples la création de l'index prend 6,5 secondes pour bigm contre 4,8 pour pgroonga ; en terme de lecture je n'ai pas trouvé de pattern avec de réelle différence, bien pgroonga s'annonce plus rapide que pg_bigm, ce premier étant plus récent que le second on peut s'attendre à ce qu'il ait profité de l'expérience du second.

Coté liberté les deux projets sont publiés sous licence PostgreSQL license.

La réelle différence entre les deux projets est que Pgroonga est une sous partie du projet global Groonga qui est dédié à la recherche fulltext, il existe par exemple Mgroonga dont vous devinerez aisément la cible, pg_bigm est lui un projet autonome qui n'implémente que les bigram dans PostgreSQL.

Vous avez désormais deux méthodes pour indexer des 2-gram, prenez garde toutefois de ne pas en abuser.

La version 9.4.5 de PostgreSQL a été utilisée pour la rédaction de cet article.

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

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

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

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

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

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

• les nouveautés dans le langage SQL
• Les nouvelles méthodes internes du moteur
• De nouvelles directives de configurations

Ce dernier billet de la série liste quelques paramètres de configuration qui font leur apparition dans cette nouvelle version.Suivi de l'horodatage des COMMITLe paramètre track_commit_timestamp permet de marquer dans les journaux de transactions ("WAL") chaque validation ("COMMIT") avec la date et l'heure du serveur. Ce paramètre est un booléen, et... Lire Version 9.5 de PostgreSQL - 3

Ce billet intéressera tous les utilisateurs de colonnes de type hstore ou json avec PostgreSQL. Bien que celui-ci prenne pour exemple hstore il s'applique également aux colonnes json ou jsonb.

Commençons par créer une table et remplissons là avec 100 000 lignes de données aléatoires. Notre exemple représente des articles qui sont associés à un identifiant de langue (lang_id) et des tags catégorisés (tags), ici chaque article peut être associé à un pays qui sera la Turquie ou l'Islande.

~# CREATE TABLE article (id int4, lang_id int4, tags hstore);
CREATE TABLE
~# INSERT INTO article 
SELECT generate_series(1,10e4::int4), cast(random()*20 as int),
CASE WHEN random() > 0.5 
THEN 'country=>Turquie'::hstore 
WHEN random() > 0.8 THEN 'country=>Islande' ELSE NULL END AS x;
INSERT 0 100000

Pour une recherche efficace des articles dans une langue donnée nous ajountons un index de type B-tree sur la colonne lang_id et un index de type GIN sur la colonne tags.

~# CREATE INDEX ON article(lang_id);
CREATE INDEX
~# CREATE INDEX ON article USING GIN (tags);
CREATE INDEX

Nous avons maintenant nos données et nos index, nous pouvons commencer les recherches. Recherchons tous les articles écrit en français (on considère que l'id du français est le 17), qui sont associés à un pays (ils ont un tag country), et analysons le plan d'exécution.

~# EXPLAIN ANALYZE SELECT * FROM article WHERE lang_id=17 AND tags ? 'country';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on article  (cost=122.42..141.21 rows=5 width=35) (actual time=12.348..13.912 rows=3018 loops=1)
   Recheck Cond: ((tags ? 'country'::text) AND (lang_id = 17))
   Heap Blocks: exact=663
   ->  BitmapAnd  (cost=122.42..122.42 rows=5 width=0) (actual time=12.168..12.168 rows=0 loops=1)
         ->  Bitmap Index Scan on article_tags_idx  (cost=0.00..12.75 rows=100 width=0) (actual time=11.218..11.218 rows=60051 loops=1)
               Index Cond: (tags ? 'country'::text)
         ->  Bitmap Index Scan on article_lang_id_idx  (cost=0.00..109.42 rows=4950 width=0) (actual time=0.847..0.847 rows=5016 loops=1)
               Index Cond: (lang_id = 17)
 Planning time: 0.150 ms
 Execution time: 14.111 ms
(10 rows)

On a logiquement 2 parcours d'index, suivis d'une opération de combinaison pour obtenir le résultat final. Pour gagner un peu en performance on penserait naturellement à créer un index multi colonnes qui contienne lang_id et tags, mais si vous avez déjà essayé de le faire vous avez eu ce message d'erreur :

~# CREATE INDEX ON article USING GIN (lang_id, tags);
ERROR:  42704: data type integer has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
LOCATION:  GetIndexOpClass, indexcmds.c:1246

Le HINT donnne une piste intéressante, en effet les index de type GIN ne peuvent pas s'appliquer sur les colonnes de type int4 (et bien d'autres).

La solution réside dans l'utilisation d'une extension standard, qui combine les opérateurs GIN et B-tree, btree-gin, précisons tout de suite qu'il existe l'équivalent btree-gist.

Comme toute extension elle s'installe aussi simplement que :

~# CREATE EXTENSION btree_gin;
CREATE EXTENSION

Désormais nous allons pouvoir créer notre index multi-colonne et rejouer notre requête pour voir la différence.

~# CREATE INDEX ON article USING GIN (lang_id, tags);
CREATE INDEX

~# EXPLAIN ANALYZE SELECT * FROM article WHERE lang_id=17 AND tags ? 'country';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on article  (cost=24.05..42.84 rows=5 width=35) (actual time=1.983..3.777 rows=3018 loops=1)
   Recheck Cond: ((lang_id = 17) AND (tags ? 'country'::text))
   Heap Blocks: exact=663
   ->  Bitmap Index Scan on article_lang_id_tags_idx  (cost=0.00..24.05 rows=5 width=0) (actual time=1.875..1.875 rows=3018 loops=1)
         Index Cond: ((lang_id = 17) AND (tags ? 'country'::text))
 Planning time: 0.211 ms
 Execution time: 3.968 ms
(7 rows)

A la lecture de ce deuxième explain le gain est explicite, même avec un petit jeu de données le coût estimé est divisé par 3, l'on gagne une lecture d'index et une opération de composition. Maintenant nous pouvons supprimer les 2 autres index pour ne conserver que celui-ci.

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

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

• les nouveautés dans le langage SQL
• Les nouvelles méthodes internes du moteur
• De nouvelles directives de configurations

Ce deuxième billet évoque donc les nouvelles méthodes internes du moteur, c'est-à-dire de nouveaux outils dont PostgreSQL dispose pour traiter les données.Index BRINIl s'agit d'un nouveau type d'index, créé pour résoudre des problèmes d'accès à de très gros volumes de données ; le cas d'usage est une table de log, dans laquelle les données sont... Lire Version 9.5 de PostgreSQL - 2

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

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

• les nouveautés dans le langage SQL
• Les nouvelles méthodes internes du moteur
• De nouvelles directives de configurations

Ce premier billet revient donc sur les ajouts au langage SQL de la version 9.5 de PostgreSQL.Ces ajouts portent sur de nombreux champs de fonctionnalités, de la sécurité aux gestions de performances en passant par la gestion des transactions.UPSERTCe mot clé désigne en réalité la possibilité d'intercepter une erreur de clé primaire sur un ordre... Lire Version 9.5 de PostgreSQL

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

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

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

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

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

• Sauvegarde
• Réplication
• Statistiques
• Maintenance

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

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

Bref, c'est par ici.

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

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

L'hiver dernier CitusData à ouvert le code source de son outil de partitionnement pg_shard, le code est désormais publié sous licence LGPL version 3, et disponible sur github. Le 30 juillet dernier la version 1.2 a été releasé, ce fut l'occasion pour moi de tester la compatibilité de Django avec cette nouvelle extension PostgreSQL.

Pour rappel le sharding permet de distribuer le contenu d'une table sur plusieurs serveurs, pg_shard permet également de gérer de multiples copies d'un même réplicats afin de palier à une éventulle faille sur l'un des noeuds. L'intérêt principal du sharding est de pouvoir garantir la scalabilité quand le volume de données augmente rapidement, l'accés aux données se faisant toujours sur le noeud principal sans avoir à prendre en compte les noeuds secondaires qui sont trasparents pour le client.

Autant le dire tout de suite, et ne pas laisser le suspens s'installer, Django n'est pas compatible avec pg_shard, cela pour trois raisons principales détaillée ci-dessous. D'auutres points sont peut-être bloquant, mais je n'ai pas introspecté plus en avant après avoir déjà constaté ces premiers points de blocage.

Lors de la sauvegarde d'un nouvel objet dans la base Django utilise la clause RETURNING dans l'INSERT afin de récupérer l'id de l'objet. A ce jour pg_shard ne supporte pas RETURNING, un ticket est en cours, espérons qu'une future version soit publiée avec cette fonctionnalité.

Plus problématique car cela demanderai un hack un peu plus profond dans l'ORM de Django, le non support des séquences qui sont utilisées par le type SERIAL afin de bénéficier de la numérotation automatique et unique des clés primaires. C'est ce type qui est utilisé par défaut par Django pour les pk. Là encore des discussions sont en cours pour supporter les sequences dans pg_shard.

Enfin et c'est peut-être ce qui serait le plus bloquant pour une utilisation avec Django ou un autre ORM, pg_shard ne supporte pas les transactions multi-requêtes. Les transactions étant la base de la garantie de l'intégrité des données ; à part être dans un cas d'usage où l'on ne modifie pas plus d'une donnée à la fois, cela peut être une raison pour ne pas adopter pg_shard dans l'état.

Malgré ces constats pg_shard reste une solution très intéressante, qu'il faut garder dans un coin de sa veille techno, à l'époque où le big data revient si souvent dans les conversations autour de la machine à café.

PgBouncer est un gestionnaire de pool de connexion pour PostgreSQL très efficace, il permet de réduire drastiquement le temps de connexion à la base depuis votre client.

Dans un contexte d'utilisation avec Django l'intérêt peut ne pas apparaître de suite, le temps passé dans l'exécution et la récupération de la requête étant souvent bien supérieur au temps de connexion. Ce paradigme tend à s'inverser dans un contexte d'API ; j'ai eu récemment l'occasion de mesurer l'impact de son utilisation sur un cas réel suite à un problème de timeout sur une API.

L'API est consommée à des taux certes raisonnables, autour de 25 appels par secondes, mais l'accroissement régulier faisait apparaitre des TIMEOUT de plus en plus souvent au niveau du client. En frontal les appels sont reçus par Nginx qui renvoit ceux-ci à des process gunicorn, le timeout coté Nginx est de 60 secondes, c'est ce timeout qui se déclenche. Les mesures sur l'infra de tests de performances continus montraient des temps de réponses de l'ordre de 120msec sous faible charge, ce qui n'était pas cohérent avec les 60 sec du timeout de Nginx.

Seulement après une revue complète de l'infrastucture du SI il est apparu que sur l'environnement de test pgbouncer était installé et correctement configuré, alors que cela n'était le cas du coté de la production. J'ai alors mené une série de tests avec et sans pgbouncer sur la même architecture, afin de mesurer son impacte réel ; PgBouncer faisant partie des préconisations initiales que j'avais faite sur ce projet.

Le test effectue un appel simple avec des données aléatoire et injecte un nombre croissant d'utilisateur pour arriver au plateau de 60 users/sec; il a été mené avec Gatling.

Les premiers tests avec pgbouncer donnent des temps de réponses médians de 285ms avec un 99th percentile à 1650ms, toutes les requêtes sont traitées avec succès

Si on débranche pgbouncer le temps de réponses médian croit à 14487ms et surtout le max dépasse 60126ms ce qui donne un nombre croissant de requête en timeout sur la fin du test quand on arrive à pleine charge.

Sur la plateforme de test PgBouncer est installé sur la machine qui fait tourner les process gunicorn, le configuration de Django est donc positionnée sur le loopback. La base de données PostgreSQL est elle sur une machine distante avec une connexion sur le LAN.

PgBouncer peut apparaître comme un outil compliqué quand on a pas l'habitude des bases de données, mais il est fort à parier que votre DBA le connait déjà, alors si l'utilisation de vos API croît ayez le réflex PgBouncer !

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

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

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

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

postgres=# SET client_min_messages TO log;
SET

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

Introduction

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.

Index Hypothétiques

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.

Une implémentation plus légère : HypoPG

J’ai utilisé une approche différente pour implémenter les index hypothétiques avec HypoPG.

• Pour commencer, cela doit pouvoir s’ajouter sur une version standard de PostgreSQL. C’est disponible en tant qu’extension et peut être utilisé (pour le moment) sur n’importe quelle version de PostgreSQL en version 9.2 ou plus ;
• Cela doit être le moins intrusif possible. C’est utilisable dès que l’extension a été créée, sans avoir besoin de redémarrer. De plus, chaque processus client dispose de son propre ensemble d’index hypothétiques. Concrètement, si vous ajoutez un index hypothétiques, cela ne perturbera absolument pas les autres connexions. De plus, les index hypothétiques sont stockés en mémoire, donc ajouter et supprimer un grand nombre d’index hypothétiques ne fragmentera pas le catalogue système.

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.

Fonctionnalités

Pour le moment, les fonctions suivantes sont disponibles :

• hypopg(): retourne la liste des index hypothétiques (dans un format similaire à pg_index).
• hypopg_add_index(schema, table, attribute, access_method): créé un index hypothétique sur une seule colonne.
• hypopg_create_index(query): créé un index hypothétique en utilisant un ordre standard CREATE INDEX.
• hypopg_drop_index(oid): supprime l’index hypothétique spécifié.
• hypopg_list_indexes(): retourne une courte version lisible de la liste
• des index hypothétiques.
• hypopg_relation_size(oid): retourne la taille estimée d’un index hypothétique.
• hypopg_reset(): supprime tous les index hypothétiques.

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.

Utilisation

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 install

HypoPG devrait alors être disponible :

rjuju=# CREATE EXTENSION hypopg ;
CREATE EXTENSION

Voyons 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 ;
ANALYZE

Ensuite, 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 !

Estimation de la taille d’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 MB

La 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.

Limitations

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) :

• seuls les index hypothétiques de type btree sont gérés ;
• pas d’index hypothétiques sur des expressions ;
• pas d’index hypothétiques sur des prédicats ;
• il n’est pas possible de spécifier le tablespace ;
• l’estimation de la taille de l’index pourrait être améliorée, et il n’est pas possible de changer le pourcentage de fragmentation.

Cependant, cette version peut déjà être utile dans de nombreux contextes.

Et pour la suite ?

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/dalibo/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.

Je me suis rendu compte ce week-end qu'on n'avait pas publié d'informations sur ce qui avait été ajouté entre les différentes versions beta, en dehors des nouveaux chapitres. Voici donc la liste des modifications, un peu éditée pour être plus lisible :

Pour la beta2 :

• Nouveaux chapitres
  • protocole de communication
  • connexions
• Chapitre fichiers
  • ajout d'une note sur l'option --no-clean de la commande initdb
  • ajout d'une note sur les versions 9.1 (et inférieures) et la colonne spclocation du catalogue pg_tablespace
  • ajout d'une note sur le fichier pgstat.stat des versions 9.3 et antérieures
• Chapitre processus
  • refonte des sections pour trier les processus par activité (et non par nom)
  • revue du résumé sur les processus d'écriture dans les fichiers de données suite à une remarque d'un lecteur dans le forum du livre (forum uniquement accessible par les lecteurs actuels)
  • correction des processus équivalents au niveau Oracle, suite là-aussi à un autre commentaire d'un lecteur
• Chapitre mémoire
  • ajout de deux paragraphes sur l'utilisation (partielle) du cache pour les requêtes
  • présentation de deux colonnes de la vue pg_stat_database permettant de quantifier l'utilisation des fichiers temporaires

Et pour la beta 3 :

• Nouveaux chapitres
  • gestion des objets
  • transactions
• Global
  • remplacement du terme maître par serveur primaire et du terme esclave par serveur secondaire (suite à la demande d'un lecteur)
• Chapitre mémoire
  • ajout d'une note sur l'intérêt du paramètre maintenance_work_mem dans le cadre d'un import de données avec pg_restore
• Chapitre processus
  • ajout d'une partie sur les écritures dans les fichiers de données suite à un CHECKPOINT, avec quelques graphes, pour mieux expliquer les différents paramètres checkpoint_*
• Chapitre Fichiers
  • ajout d'un paragraphe sur la génération des relfilnode
  • ajout d'infos sur le LSN et les journaux de transactions

Je publierais dans ce blog les nouveautés de chaque version beta à chaque sortie, ce sera plus facile pour les lecteurs.

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

Et voilà, deux nouveaux chapitres écrits, dont un sur le système transactionnel de PostgreSQL. Ce dernier m'a demandé d' étudier plus attentivement le travail de l'opération VACUUM. J'en connaissais le principe et son fonctionnement, à savoir un fonctionnement en trois phases : recherche des éléments à flagguer comme invisibles, suppression de ces éléments dans les index, puis suppression dans la table (pas physiquement). Cependant, je ne l'avais pas regardé plus précisément.

J'ai donc lu le code, puis écrit un petit patch pour mieux suivre cela (disponible en pièce jointe). J'ai exécuté un script SQL pour visualiser différents comportements. Par exemple, on ajoute dix lignes dans une nouvelle table, puis on met à jour une ligne sur trois, et enfin on exécute un VACUUM sur cette table :

CREATE TABLE t2(c1 integer);
ALTER TABLE t2 SET (autovacuum_enabled=off);
INSERT INTO t2 SELECT generate_series(1, 10);
UPDATE t2 SET c1=-c1 where c1%3=1;
SET client_min_messages to log;
VACUUM t2;

Voici le log fourni par le patch :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82231
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82231) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 1 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 4 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 11
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 12
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 7 is now REDIRECTed to item 13
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 10 is now REDIRECTed to item 14
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (10 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 320)

Il n'y a là qu'une seule étape exécutée. En effet, dû au très petit nombre de lignes dans la table, le seul bloc de 8 Ko n'a pas été entièrement occupé. Du coup, PostgreSQL place les nouvelles versions des lignes mises à jour dans le même bloc que les anciennes versions et utilise le Heap Over Tuple pour lier les enregistrements. De plus, comme il n'y a pas d'index, pas besoin de les mettre à jour.

Maintenant, faisons la même chose avec 400 lignes (en fait, suffisamment pour remplir plus d'un bloc). Les logs sont beaucoup plus importants.

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82234
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82234) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 3 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 76 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 1 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 4 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 223 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 226 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (150 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 4800)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 1 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 4 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 223 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 226 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 1 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 4 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 223 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 226 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (150 have storage, 76 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 4800)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 58 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 3 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 6 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 171 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 174 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (168 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5376)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 3 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 6 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 9 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 171 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 174 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 3 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 6 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 171 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 174 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (168 have storage, 58 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5376)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 0 deleted items found

Chaque élément devenu invisible est déclaré DEAD lors de la première étape, puis UNUSED à la troisième étape.

Il est dit que le fillfactor permet d'augmenter l'utilisation du Heap Over Tuple. Voici ce que cela donne avec un fillfactor à 90% :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82237
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82237) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 3 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 68 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 205
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 206
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 225
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 64 is now REDIRECTed to item 226
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 67 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 70 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 199 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 202 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (158 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5056)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 67 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 70 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 199 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 202 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 67 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 70 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 199 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 202 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (158 have storage, 46 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5056)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 66 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 205
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 206
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 225
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 64 is now REDIRECTed to item 226
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 67 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 70 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 193 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 196 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (160 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5120)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 67 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 70 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 193 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 196 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 67 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 70 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 193 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 196 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (160 have storage, 44 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5120)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 0 deleted items found

Certains enregistrements bénéficient de HOT, mais la majorité deviennent UNUSED. Essayons avec un fillfactor de 50% :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82240
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82240) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 4 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 38 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 109 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 112 is now REDIRECTed to item 151
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 38 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 2 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 5 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 110 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 113 is now REDIRECTed to item 151
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 37 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 3 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 6 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 108 is now REDIRECTed to item 149
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 111 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 3
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 21 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 62
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 63
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 58 is now REDIRECTed to item 81
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 82
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (61 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 1952)

Dans ce cas, tous les enregistrements bénéficient de HOT, aucun n'est UNUSED. Cela permet des mises à jour et une maintenance plus rapides, mais c'est au prix d'une table plus volumineuse (sur disque et dans le cache des relations de PostgreSQL).

Et du coup, vous vous demandez peut-être quand va sortir la version beta 0.3 du livre ? D'ici peu a priori. Un peu de relecture, quelques ajustements de dernières minutes, et ça devrait être prêt

La version 0.2 de mon livre est sortie hier. En dehors des ajouts/correctifs dans les précédents chapitres, elle ajoute deux nouveaux chapitres.

Le premier concerne le protocole de communication client/serveur de PostgreSQL. Il permet de bien prendre conscience du dialogue et des possibilités d'échange entre ces deux entités. Le second aborde la question des connexions : comment s'établie une connexion, quels paramètres de configuration existent pour les connexions, comment gérer les connexions, etc.

De plus, le site d-booker, éditeur du livre, publie une interview de l'auteur (donc moi).

Si vous avez lu le livre « PostgreSQL, architectures et notions avancées », j'aimerais beaucoup savoir ce que vous en avez pensé. N'hésitez pas à intervenir sur le forum pour me remonter vos impressions ou tout problème que vous aurez constaté.

J'ai la plaisir cette année de participer au comité de sélection du PgDay Paris qui se déroulera le 21 Avril 2015.

Le pgDay Paris est une journée de conférences et d'échanges organisée par la communauté française de PostgreSQL. Un ensemble de présentations en anglais et en français sera proposé, couvrant des sujets techniques ainsi que des retours d'expérience d'utilisation de PostgreSQL en production.

Que vous soyez développeur, administrateur système ou de bases de données ou bien « décideur » (DSI, directeur technique, etc), nous aurons du contenu pour vous !

Les inscriptions à l'événement sont dès maintenant disponibles pour le prix de 65 € pour la journée, qui inclut les pauses cafés et le déjeuner complet sur place.

Les places étant limitées, je vous invite à vous inscrire au plus tôt afin d'être sûr de pouvoir venir.

• https://www.postgresql.eu/events/register/pgdayparis2015/

Le prix des places est maintenu volontairement bas afin de permettre au plus grand nombre de participer. Cela est rendu possible grâce au soutien des sponsors, et il reste là aussi des places. Alors si vous souhaitez apporter votre contribution au développement de PostgreSQL n'hésitez pas à prendre contact, toutes les coordonnées sont sur le site de l'évènement.

Rendez-vous le 21 !