Il arrive parfois qu’on se pose des questions très pointues sur le fonctionnement de notre SGBDR préféré. Plutôt que de rester avec des questions sans réponse, voici 4 méthodes pour enrichir vos connaissances de PostgreSQL.

Pour l’exemple de cet article, nous nous poserons la question suivante :

Lors d’une restauration (« recovery » en anglais) avec une archive_command correctement configurée, si un fichier WAL est disponible à la fois dans les archives et dans le répertoire pg_xlog, lequel est utilisé en priorité ?

Regarder dans la documentation

Effectivement, la première source reste la documentation. Voyons si nous trouvons la réponse quelque part sur le fonctionnement de la restauration. Je ne regarde que la documentation de la version 9.6.

Je commence par regarder le chapitre 27. Recovery Configuration. Ce chapitre présente et explique les différents paramètres, mais ne rentre pas dans les détails de l’exécution de la restauration.

Je cherche ensuite dans le chapitre 30.1 Reliability. Là encore, même si le sujet de la restauration est évoqué, rien n’indique le fonctionnement dans le cas qui nous concerne.

Je cherche enfin dans le chapitre 25.3. Continuous Archiving and Point-in-Time Recovery (PITR). Et je trouve cette phrase qui répond à la question :

WAL segments that cannot be found in the archive will be sought in pg_xlog/; this allows use of recent un-archived segments. However, segments that are available from the archive will be used in preference to files in pg_xlog/.

Ce sont donc les WAL archivés qui sont utilisé de préférence sur les WALs disponibles dans le répertoire pg_xlog. Problème résolu !

Regarder dans le code

Mais où continuer à chercher si je n’ai pas trouvé la réponse dans la documentation ?

Il faut aller voir dans le code. Nous avons la chance de travailler avec un outil open source, c’est l’occasion d’ouvrir le capot !

Le code de PostgreSQL est fabuleusement propre et extrêmement bien documenté. N’hésitez pas à aller y faire un tour, je suis sûre que vous serez agréablement surpris.

Pour terminer cet aparté sur le code de PostgreSQL, si vous arrivez dans le code PostgreSQL, je vous suggère de lire les différents fichier README. C’est un excellent moyen d’en apprendre plus sur PostgreSQL.

La première étape consiste à récupérer le code en local. Plusieurs méthodes sont possible :

• vous pouvez directement naviguer sur cette page 
• ou vous pouvez le récupérer via git

Pour cet article, j’ai travaillé à partir de la version 9.6.3 de PostgreSQL.

Pour faire une recherche dans le code PostgreSQL, j’utilise grep. Vous pouvez utiliser l’outil de votre choix, mais personnellement, je n’ai pas encore trouvé mieux. Sans surprise, c’est le répertoire src qui comporte les sources.

Le plus dur est de trouver un mot-clé efficace qui me rapporte des résultats mais pas trop. Dans mon cas, je suis sûre que le mot-clé recovery est beaucoup trop large car je l’ai déjà trouvé de nombreuses fois dans les commentaires, lors de mes lectures du code. Vous pouvez aussi simplement vous en rendre compte en lançant grep sur ce mot-clé. N’oubliez pas d’exclure les fichiers .po qui sont les fichiers de traduction de PostgreSQL. J’ajoute l’option --directories=recurse car je souhaite faire une recherche récursive sur tout un répertoire.

cd src
grep restore_command --directories=recurse --exclude=*.po .

Voici le résultat que cela me donne :

./test/perl/PostgresNode.pm:Restoring WAL segments from archives using restore_command can be enabled
./test/perl/PostgresNode.pm:restore_command = '$copy_command'
./backend/replication/README:restorable using restore_command, it starts up the walreceiver process
./backend/postmaster/startup.c:static volatile sig_atomic_t in_restore_command = false;
./backend/postmaster/startup.c:	if (in_restore_command)
./backend/postmaster/startup.c:	 * Set in_restore_command to tell the signal handler that we should exit
./backend/postmaster/startup.c:	in_restore_command = true;
./backend/postmaster/startup.c:	in_restore_command = false;
./backend/access/transam/xlogarchive.c:	/* In standby mode, restore_command might not be supplied */
./backend/access/transam/xlogarchive.c:	 * from archive because the restore_command may inadvertently restore
./backend/access/transam/xlog.c:	XLOG_FROM_ARCHIVE,			/* restored using restore_command */
./backend/access/transam/xlog.c:		if (strcmp(item->name, "restore_command") == 0)
./backend/access/transam/xlog.c:					(errmsg_internal("restore_command = '%s'",
./backend/access/transam/xlog.c:					(errmsg("recovery command file \"%s\" specified neither primary_conninfo nor restore_command",
./backend/access/transam/xlog.c:					 errmsg("recovery command file \"%s\" must specify restore_command when standby mode is not enabled",
./backend/access/transam/xlog.c:		 * before reaching this point; e.g. because restore_command or
./backend/access/transam/xlog.c: * The segment can be fetched via restore_command, or via walreceiver having
./backend/access/transam/recovery.conf.sample:# restore_command
./backend/access/transam/recovery.conf.sample:#restore_command = ''		# e.g. 'cp /mnt/server/archivedir/%f %p'
./backend/access/transam/recovery.conf.sample:# This can be useful for cleaning up after the restore_command.
./backend/access/transam/recovery.conf.sample:# restore_command and/or primary_conninfo.

D’après les résultats, il me semble que je trouverai des choses intéressantes dans le fichier ./backend/access/transam/xlog.c. (On peut aussi se tromper et devoir faire plusieurs fichiers.)

Dans le fichier ./backend/access/transam/xlog.c, je trouve ce code (que j’ai épuré) :

if (lastSourceFailed)
{
[...]
}
else if (currentSource == XLOG_FROM_PG_XLOG)
{
        /*
            * We just successfully read a file in pg_xlog. We prefer files in
            * the archive over ones in pg_xlog, so try the next file again
            * from the archive first.
            */
        if (InArchiveRecovery)
                currentSource = XLOG_FROM_ARCHIVE;
}

Le commentaire indique clairement qu’on préfère les fichiers archivés à ceux présents dans pg_xlog. Problème résolu !

Créer un cas de test

Je vais créer un cas de test de toutes pièces me permettant de savoir quel fichier est préféré. Pour ce faire, je monte une instance PostrgreSQL de test et j’active l’archivage. Je fais une sauvegarde avec pg_basebackup, je switche quelques fichiers WAL et j’arrête l’instance.

Je tente ensuite une restauration en copiant ma sauvegarde dans PGDATA. Je prends soin de mettre dans le répertoire pg_xlog mes fichiers WALs archivés. Dans le répertoire des archives, je “corromps” un des fichiers WAL en ouvrant le fichier et en ajoutant et/ou supprimant des caractères au hasard (c’est efficace).

Puis, je crée le fichier recovery.conf et je redémarre l’instance.

Les logs de l’instance me donnent ça :

<2017-07-09 18:19:51 CEST - 3065 - >LOG:  début de la restauration de l'archive
<2017-07-09 18:19:51 CEST - 3065 - >LOG:  restauration du journal de transactions « 00000002.history » à partir de l'archive
<2017-07-09 18:19:51 CEST - 3065 - >LOG:  restauration du journal de transactions « 000000020000000000000099 » à partir de l'archive
<2017-07-09 18:19:51 CEST - 3094 - postgres>FATAL:  le système de bases de données se lance
<2017-07-09 18:19:51 CEST - 3065 - >LOG:  la ré-exécution commence à 0/99000028
<2017-07-09 18:19:51 CEST - 3065 - >LOG:  état de restauration cohérent atteint à 0/99000130
<2017-07-09 18:19:51 CEST - 3065 - >LOG:  restauration du journal de transactions « 00000002000000000000009A » à partir de l'archive
<2017-07-09 18:19:52 CEST - 3101 - postgres>FATAL:  le système de bases de données se lance
<2017-07-09 18:19:52 CEST - 3065 - >FATAL:  le fichier d'archive « 00000002000000000000009B » a la mauvaise taille : 16777223 au lieu de 16777216
<2017-07-09 18:19:52 CEST - 3063 - >LOG:  processus de lancement (PID 3065) quitte avec le code de sortie 1
<2017-07-09 18:19:52 CEST - 3063 - >LOG:  arrêt des autres processus serveur actifs
<2017-07-09 18:19:52 CEST - 3063 - >LOG:  le système de base de données est arrêté
<2017-07-09 18:21:29 CEST - 3140 - >LOG:  le système de bases de données a été interrompu lors d'une récupération à 2017-07-09 17:43:19 CEST
	(moment de la journalisation)
<2017-07-09 18:21:29 CEST - 3140 - >ASTUCE :  Si c'est arrivé plus d'une fois, des données ont pu être corrompues et vous
	pourriez avoir besoin de choisir une cible de récupération antérieure.

La restauration n’ayant pas fonctionné pour cause de fichier corrompu, c’est qu’il n’a pas regardé les fichiers présents dans pg_xlog, mais ceux archivés. Le processus de recovery préfère donc aller chercher les WAL archivés à ceux présents dans pg_xlog. Problème résolu !

Demander à mon cercle de confiance

En tant qu’informaticienne, je trouve très important d’avoir un cercle de pairs à qui je peux poser ce genre de question. Effectivement, vous ne pouvez pas tout savoir, mais parmi les personnes que vous connaissez, il y a des chances que quelqu’un le sache. Il suffit donc de demander.

Pourquoi ai-je mis ce point en dernier ? Pour deux raisons :

• d’abord parce que par expérience je sais que je retiens quelque chose d’autant mieux que je me suis arraché les cheveux avant pour essayer de trouver comment ça marchait 
• et ensuite parce que je trouve que c’est le plus élémentaire des respects envers mes interlocuteurs, que de chercher avant de poser une question.

Conclusion

Voici donc plusieurs méthodes vous permettant d’enrichir vos connaissances sur PostgreSQL, et gratuitement en plus ! Ce serait dommage de rester avec des questions non résolues, non ?

Mise à jour de sécurité du 10 août 2017

Le PostgreSQL Global Development Group publie une mise à jour de toutes les versions supportées. Ces mises à jour incluent les versions 9.6.4, 9.5.8, 9.4.13, 9.3.18 et 9.2.22.

Ces nouvelles versions corrigent trois failles de sécurité. Elles intègrent aussi la correction de plus de 50 autres bugs rapportés ces 3 derniers mois.

Les utilisateurs qui rencontrent les failles de sécurité listées ci-dessous doivent mettre à jour leur système le plus tôt possible. Les utilisateurs affectés par CVE-2017-7547 devront effectuer des étapes supplémentaires à la fin de la mise à jour pour corriger le problème. Pour les autres utilisateurs, nous conseillons de planifier la mise à jour lors de la prochaine maintenance préventive.

Failles de sécurité

Trois vulnérabilités ont été corrigées dans cette mise à jour:

• CVE-2017-7546: Mot de passe vide accepté pour certaines méthodes d’authentification
• CVE-2017-7547: La vue “pg_user_mappings” divulgue les mots de passe aux utilisateurs non autorisés
• CVE-2017-7548: la fonction lo_put() ignore les ACLs

CVE-2017-7546: Mot de passe vide accepté pour certaines méthodes d’authentification

libpq (et par extension tout pilote de connexion qui utilise libpq) ignore les mots de passe vides et ne les transmet pas à l’instance. Quand on utilise libpq ou un pilote de connexion basé sur libpq pour une authentification par mot de passe, définir un mot de passe vide est équivalent à désactiver la connexion par mot de passe. Cependant, l’utilisation d’un pilote de connexion non basé sur libpq peut permettre à un client avec un mot de passe vide de se connecter à une instance.

Pour corriger le problème, cette mise à jour désactive les mots de passe vides pour toute méthode d’authentification basée sur les mots de passe. L’instance rejettera toute tentative d’utilisation d’un mot de passe vide sur les comptes utilisateur.

CVE-2017-7547: La vue “pg_user_mappings” divulgue les mots de passe aux utilisateurs non autorisés

Le soucis concerne le mapping entre les utilisateurs du serveur local et les utilisateurs distants lors de l’utilisation des Foreign Data Wrapper.

Avant ce correctif, un utilisateur pouvait accéder à la vue pg_user_mappings, même si celui-ci n’avait pas les droits d’USAGE sur l’instance distante. Cela signifie qu’un utilisateur pouvait voir des informations comme les mots de passe (s’ils avaient été créés par l’administrateur de l’instance plutôt que par l’utilisateur lui-même).

Ce correctif ne sera actif que pour les nouveaux groupes de bases de données créés avec initdb.

Pour corriger ce problème sur des systèmes existants, il faut effectuer les étapes suivantes. Pour plus d’informations, merci de regarder les release notes.

1. Dans le fichier postgresql.conf, ajouter le paramètre suivant:

allow_system_table_mods = true

1. Après avoir ajouté cette ligne, redémarrer l’instance PostgreSQL.

2. Dans chaque base de données de l’instance, exécuter les commandes suivantes (en tant que superuser) :

   SET search_path = pg_catalog;
   CREATE OR REPLACE VIEW pg_user_mappings AS
   SELECT
   U.oid       AS umid,
   S.oid       AS srvid,
   S.srvname   AS srvname,
   U.umuser    AS umuser,
   CASE WHEN U.umuser = 0 THEN
       'public'
   ELSE
       A.rolname
   END AS usename,
   CASE WHEN (U.umuser <> 0 AND A.rolname = current_user
                 AND (pg_has_role(S.srvowner, 'USAGE')
                     OR has_server_privilege(S.oid, 'USAGE')))
               OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE'))
               OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user)
               THEN U.umoptions
             ELSE NULL END AS umoptions
   FROM pg_user_mapping U
   LEFT JOIN pg_authid A ON (A.oid = U.umuser)
   JOIN pg_foreign_server S ON (U.umserver = S.oid);
   

3. Cette commande doit également être jouée sur les bases template0 et template1, sans quoi la vulnérabilité continuera d’exister dans les futures bases de données créées.
   Il faut tout d’abord autoriser l’accès pour template0. Pour PostgreSQL 9.5 :

   ALTER DATABASE template0 WITH ALLOW_CONNECTIONS true;
   

   Pour PostgreSQL 9.4 et inférieur :

   UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';
   

   Ensuite, dans les bases de données template0 et template1, exécuter les commandes de l’étape 3.
   Puis, désactiver les connexions sur template0. Pour PostgreSQL 9.5 :

   ALTER DATABASE template0 WITH ALLOW_CONNECTIONS false;
   

   Pour PostgreSQL 9.4 et inférieur :

   UPDATE pg_database SET datallowconn = false WHERE datname = 'template0';
   

4. Supprimer du fichier postgresql.conf la ligne :

   allow_system_table_mods = false
   

5. Redémarrer l’instance postgreSQL

Pour plus d’information, voir les release notes.

CVE-2017-7548: la fonction lo_put() ignore les ACLs

La fonction lo_put() devrait nécessiter les mêmes permissions que lowrite(), mais il manquait une vérification qui permettait à n’importe quel utilisateur de changer les données d’un large object.

Pour corriger ce problème, la fonction lo_put() a été changée pour vérifier les droits d’UPDATE sur l’objet cible.

Corrections de bugs et améliorations

Cette mise à jour corrige aussi un certain nombre de bugs rapportés ces derniers mois. Certains ne concernent que la version 9.6, mais la plupart concernent toutes les version supportées.

• pg_upgrade: la documentation décrivant la procédure de mise à jour d’instances secondaires,en conservant une synchronisation correcte entre nœud primaire et secondaire, a été corrigée. Une note a été ajoutée pour enjoindre l’utilisateur à vérifier que le paramètre wal_level est différent de minimal, ce qui empêcherait la reconnexion des secondaires au redémarrage.
• Correction du problème d’accès concurrents qui pouvait faire échouer un UPDATE
• Plusieurs solutions permettant d’empêcher des scenarios (peu probables) de corruption de données
• Un correctif pour empêcher un crash de l’instance lors des tris de plus d’un milliard de lignes en mémoire
• Un correctif, pour la version Windows, permettant d’essayer de créer un nouveau processus au cas où les adresses de la mémoire partagée ne seraient plus allouées, cas typique d’intéraction avec un logiciel antivirus
• un correctif de libpq pour assurer que les tentatives de connexions en erreur utilisant les authentifications GSS/SASL et SSPI soit correctement fermées
• Un correctif de gestion des connexions SSL, et de gestion de leurs traces
• Un correctif pour permettre aux fonctions de fenêtrage d’être utilisées dans une sous-requête utilisée comme argument d’une fonction d’aggrégation
• Permettre le parallélisme d’un plan d’exécution quand COPY est utilisé à partir d’une requêtre SQL
• Plusieurs correctifs sur ALTER TABLE
• Un correctif pour que les ordres SQL ALTER USER ... SET et ALTER ROLE ... SET acceptent les mêmes syntaxes
• Des correctifs sur le collecteur de statistiques permettant que les collectes de statistiques effectuées juste après une demande d’arrêt de l’instance soient écrites sur disque
• Correction de la possible création d’un fichier WAL invalide lors de la promotion d’un nœud secondaire
• Plusieurs correctifs concernant les processus walsender et walreceiver, notamment sur le traitement des signaux et sur les arrêts/redémarrages
• Plusieurs correctifs sur le décodage logique des WAL, y compris la suppression de fuites disques sur les petites sous-transactions
• Permettre à une contrainte de type CHECK d’être dans l’état NOT VALID lors de l’exécution d’un ordre CREATE FOREIGN TABLE
• Des correctifs de postgres_fdw pour appliquer les changements rapidement après un ordre ALTER SERVER ou ALTER USER MAPPING et pour améliorer la gestion des serveurs/instances qui ne répondent pas
• Plusieurs correctifs de pg_dump et pg_restore, y compris un correctif de pg_dump lors de l’utilisation de la sortie standard sous Windows
• Corrige la sortie standard de pg_basebackup sous Windows, similaire au correctif de pg_dump
• Corrige pg_rewind pour lui permettre de gérer correctement des fichiers de plus de 2Go, même s’il est rare de trouver des fichiers de cette taille dans un répertoire de données
• Plusieurs correctifs pour pouvoir compiler PostgreSQL avec Microsoft Visual C (MSVC)

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 fera l’objet d’une dernière mise-à-jour. Nous conseillons très fortement aux utilisateurs de cette version de planifier une montée de version le plus vite possible. Voir notre 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 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, on peut 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

Celui-ci va porter sur la mise en œuvre de la réplication logique.

Installation

Lors de la rédaction de cet article la version 10 n’est pas encore sortie. Cependant la communauté met à disposition les paquets des versions bêtas. Bien entendu, à ne pas utiliser en production.

Installation du dépôt pgdg (PostgreSQL Developpement Group)

Il suffit d’aller sur le site http://www.postgresql.org puis « download » -> « debian ». Sur cette page, le site vous indique la marche à suivre pour installer le dépôt du pgdg. Cependant il ne va vous proposer que les versions stables. Mais, le site vous renvoie vers une page du wiki :

For more information about the apt repository, including answers to frequent questions, please see the apt page on the wiki.

Sur la page wiki vous trouverez :

For packages of development/alpha/beta versions of PostgreSQL, see the FAQ entry about beta versions.

Qui vous indique :

To use packages of postgresql-10, you need to add the 10 component to your /etc/apt/sources.list.d/pgdg.list entry, so the 10 version of libpq5 will be available for installation

Donc pour debian, les commandes se résument à :

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main 10" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update

Installation des paquets

sudo apt install postgresql-10

Création des instances

Une première instance est créée lors de l’installation. Nous allons installer une seconde instance. Ainsi l’une sera le « publisher » et l’autre sera « subscriber ».

# pg_lsclusters 
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

# pg_createcluster 10 sub
Creating new PostgreSQL cluster 10/sub ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/sub --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/sub ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/sub -l logfile start

Ver Cluster Port Status Owner Data directory Log file
10 sub 5433 down postgres /var/lib/postgresql/10/sub /var/log/postgresql/postgresql-10-sub.log

# pg_ctlcluster 10 sub start

# pg_lsclusters 
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
10 sub 5433 online postgres /var/lib/postgresql/10/sub /var/log/postgresql/postgresql-10-sub.log

Mise en place de la réplication logique

Prérequis

Il y a très peu de changements à apporter à la configuration par défaut. La plupart des paramètres sont déjà positionnés pour mettre en place la réplication logique. Toutefois il y a un paramètre à modifier sur l’instance qui « publie » : le wal_level. Celui-ci est à « replica« , il doit être placé à « logical« .

Ca se passe dans /etc/postgresql/10/main/postgresql.conf

wal_level = logical

Pour appliquer le changement il faut redémarrer l’instance :

pg_ctlcluster 10 main restart

Publication

Créons une base b1 qui contient une table t1.

postgres=# create database b1;
CREATE DATABASE
postgres=# \c b1
You are now connected to database "b1" as user "postgres".

b1=# create table t1 (c1 text);
CREATE TABLE
b1=# insert into t1 values ('un');
INSERT 0 1
b1=# select * from t1;
 c1 
----
 un
(1 row)

Ensuite nous allons jouer l’ordre pour créer la publication :

b1=# CREATE PUBLICATION pub1 FOR TABLE t1 ;

Et c’est tout! Notez qu’il est possible d’utiliser le mot clé « FOR ALL TABLES » pour que le PostgreSQL ajoute toutes les tables présentes et futures à la publication.

On peut vérifier que la publication a bien été créée avec la méta-commande psql suivante :

b1=# \dRp+
 Publication pub1
 All tables | Inserts | Updates | Deletes 
------------+---------+---------+---------
 f | t | t | t
Tables:
 "public.t1"

Souscription

La réplication logique ne réplique pas les ordres DDL, nous devons donc créer la table sur l’instance « sub ». Il n’est pas nécessaire d’avoir le même nom de base, nous allons donc créer une base b1.

postgres@blog:~$ psql -p 5433
psql (10beta2)
Type "help" for help.

postgres=# CREATE DATABASE b2;
CREATE DATABASE
postgres=# \c b2 
You are now connected to database "b2" as user "postgres".
b2=# create table t1 (c1 text);
CREATE TABLE

Comme pour la publication, la souscription se crée avec un ordre SQL :

b2=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=/var/run/postgresql port=5432 dbname=b1' PUBLICATION pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
b2=# select * from t1;
 c1 
----
 un
(1 row)

La partie la plus importante est : CONNECTION 'host=/var/run/postgresql port=5432 dbname=b1'

En effet, on indique au « subscriber » comment se connecter à la publication. Dans mon exemple les deux instances sont sur la même machine et écoutent sur une socket locale. Si vous avez des instances sur différentes machines il faudra spécifier l’adresse IP. Ensuite le port et ne surtout pas oublier la base où a été créée la publication.

Comme vous pouvez le voir, le moteur s’est automatiquement chargé de rapatrier les données qui étaient déjà présentes dans la table t1.

Maintenant si j’ajoute des lignes dans la table t1, elles seront automatiquement répliquées sur l’instance « sub » :

postgres@blog:~$ psql b1
psql (10beta2)
Type "help" for help.

b1=# insert into t1 values ('deux');
INSERT 0 1
postgres=# \q
postgres@blog:~$ psql -p 5433 b2
psql (10beta2)
Type "help" for help.
b2=# select * from t1;
 c1 
------
 un
 deux
(2 rows)

Comme pour la publication, il existe une méta-commande pour afficher les souscriptions :

\dRs+
 List of subscriptions
 Name | Owner | Enabled | Publication | Synchronous commit | Conninfo 
------+----------+---------+-------------+--------------------+----------------------------------------------
 sub1 | postgres | t | {pub1} | off | host=/var/run/postgresql port=5432 dbname=b1
(1 row)

C’est tout pour cet article, dans un prochain article nous verront les restrictions d’usage.

Certains d’entre vous sont déjà au courant, la nouvelle version majeure de PostgreSQL approche à grands pas. Elle devrait sortir dans le courant du mois de septembre.

Comme chaque nouvelle version la liste de nouveautés est assez impressionnante :

• partitionnement
• amélioration des performances sur les tris et fonctions d’agrégation
• extension du parallélisme : parcours d’index parallélisé,  jointure parallélisée, parallélisation des sous-requêtes…
• statistiques étendues
• support des collations  ICU : va permettre d’exploiter les « abbreviated keys  » qui avaient dû être désactivées en 9.5 à cause d’un bug dans la libc. Les « abbreviated keys » permettaient un gain de l’ordre de 20-30% sur les tris et créations d’index.
• et je m’arrête là, vous pouvez avoir un aperçu des nouveautés sur la page wiki ou dans les releases notes.

Une grande nouveauté de la version 10 que je vais présenter dans une série d’articles est la réplication logique.

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

Rappel

La réplication existe déjà dans PostgreSQL ¹. Elle reposait sur la réplication dite « physique » : le moteur ne réplique pas des requêtes mais le résultat de la requête. Plus précisément les modifications des blocs de données.

Le serveur secondaire se contente de rejouer les journaux de transaction. Cette technique est assez simple et est particulièrement efficace et fiable.

Néanmoins elle présente quelques limitations :

• il n’existe aucune granularité, on est obligé de répliquer l’intégralité de l’instance
• il n’est pas possible de faire une réplication entre différentes architecture (x86, ARM…).
• le secondaire n’accepte aucune requête en écriture. Il n’est dont pas possible de créer des vues personnalisées ou des index.

Fonctionnement

Contrairement à la réplication physique, la réplication logique ne réplique pas les blocs de données. Elle décode le résultat des requêtes qui sont transmis au secondaire. Celui-ci applique les modifications SQL issues du flux de réplication logique.

Le serveur secondaire est en réalité un serveur primaire, dans le sens où il s’agît d’un serveur qui accepte des requêtes en écritures comme n’importe quel instance primaire.

Il sera ainsi possible de choisir les tables à répliquer, de rajouter des vues et index sur le serveur secondaire etc…

1. Une « publication » est créée sur le serveur primaire (« publieur »), celle-ci comprend les tables appartenant à la publication.
2. Le secondaire souscrit à cette publication, c’est un « souscripteur ».
3. Ensuite un processus spécial est lancé : le  « bgworker logical replication ». Il va se connecter à un slot de réplication sur le serveur primaire.
4. Le serveur primaire va procéder à un décodage logique des journaux de transaction pour extraire les résultats des ordres SQL.
5. Le flux logique est transmis au secondaire qui les applique sur les tables.

La mise en oeuvre dans un prochain article…

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 !

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

J'ai toujours eu en tête qu'une connexion mettait du temps à s'établir entre un client et PostgreSQL. J'avais en tête un nombre qui me semblait plausible mais j'avoue que je n'avais jamais fait réellement le test.

Ce week-end, travaillant sur le chapitre sur la gestion des connexions, je me suis demandé si on pouvait calculer ce temps. J'ai donc regardé le code des processus postmaster/postgres pour ajouter quelques traces, histoire d'en savoir plus. Voici le patch que j'ai réalisé :

diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index f05114d..9d8fb8a 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2198,6 +2198,8 @@ ConnCreate(int serverFd)
 {
        Port       *port;
 
+       elog(LOG, "patch - ConnCreate(%d)", serverFd);
+
        if (!(port = (Port *) calloc(1, sizeof(Port))))
        {
                ereport(LOG,
@@ -3760,6 +3762,8 @@ BackendStartup(Port *port)
        Backend    *bn;                         /* for backend cleanup */
        pid_t           pid;
 
+       elog(LOG, "patch - BackendStart()");
+
        /*
         * Create backend data structure.  Better before the fork() so we can
         * handle failure cleanly.
@@ -3814,6 +3818,8 @@ BackendStartup(Port *port)
 
                MyProcPid = getpid();   /* reset MyProcPid */
 
+               elog(LOG, "patch - new pid is %d", MyProcPid);
+
                MyStartTime = time(NULL);
 
                /* We don't want the postmaster's proc_exit() handlers */
@@ -3916,6 +3922,8 @@ BackendInitialize(Port *port)
        char            remote_port[NI_MAXSERV];
        char            remote_ps_data[NI_MAXHOST];
 
+       elog(LOG, "patch - BackendInitialize()");
+
        /* Save port etc. for ps status */
        MyProcPort = port;
 
@@ -4096,6 +4104,8 @@ BackendRun(Port *port)
        int                     usecs;
        int                     i;
 
+       elog(LOG, "patch - BackendRun()");
+
        /*
         * Don't want backend to be able to see the postmaster random number
         * generator state.  We have to clobber the static random_seed *and* start
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index bc4eb33..4e1a3f7 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3578,6 +3578,7 @@ PostgresMain(int argc, char *argv[],
        sigjmp_buf      local_sigjmp_buf;
        volatile bool send_ready_for_query = true;
 
+       elog(LOG, "patch - PostgresMain()");
        /*
         * Initialize globals (already done if under postmaster, but not if
         * standalone).
@@ -3845,6 +3846,8 @@ PostgresMain(int argc, char *argv[],
         * were inside a transaction.
         */
 
+       elog(LOG, "patch - PostgresMain() - ready to execute command");
+
        if (sigsetjmp(local_sigjmp_buf, 1) != 0)
        {
                /*
@@ -4056,12 +4059,16 @@ PostgresMain(int argc, char *argv[],
                if (ignore_till_sync && firstchar != EOF)
                        continue;
 
+               elog(LOG, "patch - PostgresMain() - processing command");
+
                switch (firstchar)
                {
                        case 'Q':                       /* simple query */
                                {
                                        const char *query_string;
 
+                                       elog(LOG, "patch - PostgresMain() - executing simple query");
+
                                        /* Set statement_timestamp() */
                                        SetCurrentStatementStartTimestamp();
 
@@ -4279,6 +4286,8 @@ PostgresMain(int argc, char *argv[],
                        case 'X':
                        case EOF:
 
+                               elog(LOG, "patch - PostgresMain() - exiting");
+
                                /*
                                 * Reset whereToSendOutput to prevent ereport from attempting
                                 * to send any more messages to client.

En configurant PostgreSQL pour qu'il ajoute la date (à la milliseconde près) et le PID, et en configurant la trace des connexions et déconnexions :

log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] '

et en exécutant la commande suivante :

$ psql -c "select * from t1 limit 200" b1

nous obtenons les traces suivantes :

2015-02-22 22:47:23.022 CET [6087] LOG:  patch - ConnCreate(5)
2015-02-22 22:47:23.022 CET [6087] LOG:  patch - BackendStart()
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - new pid is 6283
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - BackendInitialize()
2015-02-22 22:47:23.023 CET [6283] LOG:  connection received: host=[local]
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - BackendRun()
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - PostgresMain()
2015-02-22 22:47:23.025 CET [6283] LOG:  connection authorized: user=postgres database=b1
2015-02-22 22:47:23.027 CET [6283] LOG:  patch - PostgresMain() - ready to execute command
2015-02-22 22:47:23.027 CET [6283] LOG:  patch - PostgresMain() - processing command
2015-02-22 22:47:23.028 CET [6283] LOG:  patch - PostgresMain() - executing simple query
2015-02-22 22:47:23.028 CET [6283] LOG:  duration: 0.691 ms  statement: select * from t1 limit 200
2015-02-22 22:47:23.736 CET [6283] LOG:  patch - PostgresMain() - processing command
2015-02-22 22:47:23.736 CET [6283] LOG:  patch - PostgresMain() - exiting
2015-02-22 22:47:23.737 CET [6283] LOG:  disconnection: session time: 0:00:00.913 user=postgres database=b1 host=[local]

Autrement dit, il faut compter quelques millisecondes pour établir une connexion sans pooler. Après différents tests (impliquant notamment pgbench), le pire que j'ai vu est 10 millisecondes. Pas bien méchant quand on y pense. J'ai aussi noté que la toute première connexion était bien plus lente (dans les 40 millisecondes), ce qui reste encore bien loin de ce que j'imaginais.

J'ai aussi testé avec différentes valeurs du shared_buffers car il semblerait que la taille mémoire d'un processus a une importance dans la durée d'exécution de l'appel système fork().

Comme quoi il est vraiment préférable de tout tester pour ne pas avoir d'idées préconçues.