PostgreSQL La base de donnees la plus sophistiquee au monde.

La planete francophone de PostgreSQL

dimanche 17 septembre 2017

Philippe Florent

Indexation virtuelle

Evaluer la pertinence d'un index en le créant virtuellement avec hypopg

dimanche 17 septembre 2017 à 21h00

mercredi 6 septembre 2017

Loxodata

Connexion à une instance PostgreSQL

Connexion à une instance PostgreSQL

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

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

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

Options de connexion

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Mots de passe

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

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

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

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

Variables d’environnement

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

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

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

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

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

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

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

Chaînes de connexion

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

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

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

URI de connexion

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

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

Par exemple :

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

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

Clé/Valeur

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

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

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

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

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

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

Fichier de service

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Fichier de mots de passe

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

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

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

Par exemple :

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

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

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

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

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

postgres=#

La connexion fonctionne !

Conclusion

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

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

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

par contact@loxodata.com (Loxodata) le mercredi 6 septembre 2017 à 09h28

samedi 2 septembre 2017

Adrien Nayrat

PGDay : Comment fonctionne la recherche plein texte dans PostgreSQL?

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

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

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

 

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

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

 

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

Voici la vidéo de la conférence :

Ainsi que les slides

Share

par Adrien Nayrat le samedi 2 septembre 2017 à 11h04

vendredi 1 septembre 2017

Daniel Verite

Du nouveau dans les triggers avec PostgreSQL 10

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

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

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

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

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

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

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

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

Un schéma de test

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

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

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

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

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

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

ou pour un seul label:

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

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

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

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

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

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

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

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

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

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

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

  RETURN NEW;
END $$ LANGUAGE plpgsql;

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

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

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

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

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

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

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

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

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

Le test

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

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

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

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

Le résultat

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

Graphe performances triggers

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

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

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

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

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

Loxodata

PostgreSQL 9.6.5 et autres correctifs

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

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

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

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

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

Correctifs et améliorations

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

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

Avertissement de fin de vie de vie pour la version 9.2

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

Mise à jour

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

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

Liens

par contact@loxodata.com (Loxodata) le vendredi 1 septembre 2017 à 05h00

mardi 29 août 2017

Philippe Florent

Verrouillage et transactions préparées

Traquer les bloqueurs finaux, le cas des transactions préparées

mardi 29 août 2017 à 19h15

dimanche 27 août 2017

Adrien Nayrat

PostgreSQL 10 et la réplication logique – Restrictions

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

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

 

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

Restrictions

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

Ordres DDL

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

Séquences

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

Ordre TRUNCATE

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

Large Objects

Les Large Objets ne sont pas répliqués

Schéma et tables

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

Replica Identity

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

Share

par Adrien Nayrat le dimanche 27 août 2017 à 16h48

mardi 18 juillet 2017

Nicolas Gollet

Fitrage par IP avec les RLS

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

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

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

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

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

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

LINETPS RLS ON

TPS RLS OFF

DIFF%
14960056270667088
1004203045616358692
1000245852513655197
10000451345291699

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

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

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

par Nicolas GOLLET le mardi 18 juillet 2017 à 19h45

lundi 12 juin 2017

Guillaume Lelarge

Début de la traduction du manuel de PostgreSQL v10

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

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

par Guillaume Lelarge le lundi 12 juin 2017 à 19h45

mardi 6 juin 2017

Daniel Verite

mardi 23 mai 2017

Daniel Verite

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

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

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

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

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

COMMIT et ROLLBACK conditionnel

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

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

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

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

Syntaxe des expressions

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

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

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

Appeler le shell pour tester une condition

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

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

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

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

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

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

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

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

Utiliser l’interpréteur SQL pour tester une condition

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

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

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

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

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

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

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

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

\set ON_ERROR_STOP on

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

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

mercredi 17 mai 2017

Daniel Verite

OpenData: importer les noms de domaines de l’AFNIC

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE EXTENSION pg_trgm;

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

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


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

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

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

SET pg_trgm.similarity_threshold TO 0.5;

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

Ca donne 33 résultats:

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

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

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

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

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

Résultats:

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

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

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

mercredi 1 mars 2017

Damien Clochard

3 Meetups PostgreSQL en 1 semaine !

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

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

par Damien Clochard le mercredi 1 mars 2017 à 21h17

mardi 7 février 2017

Nicolas Gollet

Backends générant des fichiers temporaires

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

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

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

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

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

select * from pg_stat_activity where pid = <PID>;

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

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

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

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

dimanche 5 février 2017

Damien Clochard

PostgreSQL Temboard : Guide de Démarrage

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.

Temboard : PostgreSQL Remote Control

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 :

Temboard PostgreSQL Dashboard

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 !

par Damien Clochard le dimanche 5 février 2017 à 10h17

mardi 31 janvier 2017

Cédric Villemain

pgDay 2017 à Paris: conférence PostgreSQL internationale

Un événement communautaire

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

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

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

pgDay 2017 à Paris, soutenu par 2ndQuadrant

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

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

S’enregistrer et venir au pgDay 2017

logo pgDay Paris 2017

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

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

 

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

samedi 10 décembre 2016

Guillaume Lelarge

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

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

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

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

jeudi 29 septembre 2016

Sébastien Lardière

PostgreSQL 9.6.0

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

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

mercredi 28 septembre 2016

Cédric Villemain

pgFincore 1.2, une extension PostgreSQL

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

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

Cache de données

Cache de données

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

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

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

Read Ahead

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

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

pgFincore 1.2

Cette extension permet donc:

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

Obtenir pgFincore

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

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

Besoin d’aide ?

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


Exemples d’utilisation

Installation

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

Information système

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

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

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

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

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

Audit du cache

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

Charger une table en mémoire

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

Vider le cache d’une table

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

Restaurer des pages en cache

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

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

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

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

vendredi 20 mai 2016

Guillaume Lelarge

Quelques nouvelles sur les traductions du manuel

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

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

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

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

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

par Guillaume Lelarge le vendredi 20 mai 2016 à 20h35

dimanche 13 mars 2016

Guillaume Lelarge

Fin de la traduction du manuel de la 9.5

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

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

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

par Guillaume Lelarge le dimanche 13 mars 2016 à 10h41

mardi 8 mars 2016

Sébastien Lardière

Dates à retenir

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

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

samedi 6 février 2016

Guillaume Lelarge

Début de la traduction du manuel 9.5

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

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

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

jeudi 4 février 2016

Rodolphe Quiédeville

Indexer pour rechercher des chaines courtes dans PostgreSQL

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.

par Rodolphe Quiédeville le jeudi 4 février 2016 à 08h38

lundi 1 février 2016

Guillaume Lelarge

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

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

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

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

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

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

mercredi 13 janvier 2016

Sébastien Lardière

Version 9.5 de PostgreSQL - 3

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

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

Ce dernier billet de la série liste quelques paramètres de configuration qui font leur apparition dans cette nouvelle version.Suivi de l'horodatage des 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

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

Rodolphe Quiédeville

Index multi colonnes GIN, GIST

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.

par Rodolphe Quiédeville le mercredi 13 janvier 2016 à 14h11

mardi 12 janvier 2016

Sébastien Lardière

Version 9.5 de PostgreSQL - 2

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

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

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

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

vendredi 8 janvier 2016

Sébastien Lardière

Version 9.5 de PostgreSQL

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

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

Ce premier billet revient donc sur les ajouts au langage SQL de la version 9.5 de PostgreSQL.Ces ajouts portent sur de nombreux champs de fonctionnalités, de la sécurité aux gestions de performances en passant par la gestion des transactions.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

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

jeudi 19 novembre 2015

Guillaume Lelarge

Version finale du livre

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

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

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

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

par Guillaume Lelarge le jeudi 19 novembre 2015 à 22h36

mardi 22 septembre 2015

Guillaume Lelarge

Version beta 0.4 du livre

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

  • Sauvegarde
  • Réplication
  • Statistiques
  • Maintenance

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

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

Bref, c'est par ici.

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

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

par Guillaume Lelarge le mardi 22 septembre 2015 à 21h59

lundi 10 août 2015

Rodolphe Quiédeville

Utiliser pg_shard avec Django

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

par Rodolphe Quiédeville le lundi 10 août 2015 à 10h31

mercredi 5 août 2015

Rodolphe Quiédeville

pgBouncer dans un contexte Django

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

with-pgbouncer.png

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.

without-pgbouncer.png

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 !

par Rodolphe Quiédeville le mercredi 5 août 2015 à 11h23

mardi 14 juillet 2015

Guillaume Lelarge

Comment quantifier le maintenance_work_mem

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

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

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

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

postgres=# SET client_min_messages TO log;
SET

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

par Guillaume Lelarge le mardi 14 juillet 2015 à 07h54

jeudi 2 juillet 2015

Julien Rouhaud

Parlons des index hypothétiques

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.

par Julien Rouhaud le jeudi 2 juillet 2015 à 10h08

mardi 16 juin 2015

Guillaume Lelarge

Différences entre les versions beta du livre

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.

par Guillaume Lelarge le mardi 16 juin 2015 à 20h32

dimanche 17 mai 2015

ulhume

Rooter le LG G Pad 7.0

Lorsque l&aposon achète un device Android, se pose toujours le même problème &aposcomment vais-je rooter ce machin là&apos.

Voici donc comment faire pour la tablette LG GPad 7.0.

Pourquoi faire ?

Comme mon sujet pour l&aposannée 2015 est clairement le développement d&aposapplications mobile (allez zouh, un peu de pub ;-), après m&aposêtre commis à acheter un Mac Mini et un iPad Mini, j&aposai aussi dut faire l&aposacquisition d&aposun fairphone (pour me racheter un peu...) et d&aposune tablette Android, le LG G Pad 7.0 (le Windows Phone... m&aposa été gracieusement donné, ouf !! ;-).

Concernant les deux devices Android, seul le fairphone a le bon goût d&aposêtre rooté en sortie d&aposusine. En revanche, le LG, c&aposest prison dorée... Or pour développer sous Android, rooter est l&aposétape nécessaire. Car outre l&aposaspect philosophique (je ne supporte pas l&aposidée qu&aposon m&aposempêche de faire ce que je veux de ce que j&aposachète), l&aposaccès root me permet d&aposutiliser ADB en mode réseau (c&aposest à dire sans cable USB). Et ça, je ne sais pas m&aposen passer...

Bref, trêve de blabla, voyons comment casser la bête...

Passer en mode développeur

La première étape consiste déjà à activer le mode développeur, ce qui n&aposest pas aussi évident que l&aposon pourrait se l&aposimaginer. Pour cela vous devrez aller dans les réglages, puis dans le menu à propos de la tablette, et enfin dans information sur le logiciel. Arrivé là, accrochez-vous bien, vous devez taper plusieurs fois sur le Numéro de build.

La tablette vous demande alors si vous êtes certain de votre action (non, non, j&aposai tapé 20 fois là dessus par pur hasard...), puis fera apparaître, à la positive, le menu tant utile pour les développeurs.

Dans ce menu, vous allez devoir vous rendre dans la section debuggage pour activer le debuggage de la connection USB. Ce qui est le pré-requis indispensable pour pouvoir utiliser ADB mais aussi pour pouvoir rooter l&aposappareil.

Cas n°1, KitKat

Récupération de Purple Drake

Pour ceux qui ont acquis leur tablette à sa sortie, la version Android de base est KitKat. Pour rooter cette version, le sésame s&aposappelle Purple Drake. Purple Drake Pour le rooting à proprement parlé, le sésame s&aposappelle Purple Drake. Téléchargez donc la dernière version (R3 dans mon cas) et décompressez là en local.

Pour ceux qui utilisent (encore) Wheezy

Pour ceux qui sont sur une version récente du kernel, cette étape peut être zappée. Dans mon cas, la version de GLibC incluse dans Debian Wheezy ne me permet de lancer le script tel quel avec les versions binaires d&aposadb incluse dans l&aposarchive.

Cela se règle cependant simplement en installant la version debian d&aposadb par sudo apt-get install android-adb. Ceci fait, allez dans le dossier assets de l&aposarchive décompressée de Purple Drake et éditez le fichier purpledrake_main.sh pour remplacer en ligne 16, $1 par /usr/bin.

Lorsque tout est en ordre, il ne reste plus qu&aposà lancer l&aposoutil. Assurez-vous que la tablette n&aposa aucune application de lancée (rebootez là si nécessaire), que le cable USB est bien connecté, et que dans la barre de status de la tablette vous voyez bien que la connection USB se fait en mode debuggage.

root !

Si tout est OK, lancez le rooting par sudo ./purpledrake_linux.sh. La raison du sudo ici est que sous Debian, en standard, seul root a accès au device USB. Cela peut se configurer au niveau d&aposudev mais ça me saoule un peu d&aposavoir à faire cela à chaque fois, sur chaque machine et pour chaque device. Et c&aposest d&aposailleurs l&aposune des raisons qui me fait passer ADB en mode réseau.

Une fois l&aposoutil lancé il suffit de se laisser guider par le script. Il va d&aposabord rebooter le device, puis installer un root temporaire, puis l&aposutiliser, si vous le désirez, pour mettre en place le root permanent.

Lorsque la tablette a redémarré, tout application qui demande l&aposaccès root devrait ainsi l&aposobtenir. C&aposest une première étape mais ce n&aposest pas très sécurisé, loin de là.

Super pouvoirs à la demande

Pour aller un cran plus loin, rendez vous sur le market et téléchargez SuperSU. Cette application au démarrage va remplacer la commande su fournie par Purple Drake, par une version qui vous demandera si telle application a bien le droit d&aposobtenir l&aposaccès root.

Au premier lancement, SuperSU va vous proposer une procédure de remplacement de la commande en mode normal ou recovery. J&aposai personnellement pris l&aposoption normal.

Et après une installation avec succès suivi d&aposun redémarrage de l&aposengin, tout était opérationnel.

Félicitation, votre device vous appartient !

Cas n°2, Lollipop

Si vous avez acheté votre LG plus récemment, ou si, comme moi, vous avez clické sur "mettre à jour" sans faire tourner votre cerveau, vous n&aposêtes pas sous Kitkat mais sous Lollipop. Et là, le purple drake il marche plus du tout !

Alors heureusement il y a une autre méthode qui fonctionne parfaitement mais elle nécessite... windows. Je sais, c&aposest pas cool, mais je n&aposai pas trouvé mieux.

Pour rooter, petite liste de courses :

  1. Les pilotes Android de chez LG. Perso, je les ai trouvés .
  2. Une petite application toute mignonette qui va vous simplifier le rootage, et que vous trouverez ici

Première étape, installer les pilotes sur le windows. Je ne sais pas pour vous mais chez moi cela a pris des plombes !!!

Ceci fait, connectez votre LG par le câble USB et lancez l&aposapplication puis clickez sur ROOT DEVICE. Cela va lancer une console et le script associé va redémarrer le device. Si après ce redémarrage vous vous retrouvez à nouveau sous votre home Android, c&aposest que quelque chose n&aposaura pas marché. Dans ce cas le script va (sans doute) vous dire (je traduis ;-) "désolé, mais j&aposai échoué salement à passer le device en mode série, fait le toi-même, moi j&aposattends".

En effet, le script a besoin que le device soit dans un mode très spécial de mise à jour du firmeware qui place le port USB en mode "port série". Dans ce mode, il lui sera possible de lire un numéro de série qui permettra par la suite de rooter l&aposengin.

Pour faire cela manuellement procédez comme suit sans toucher au script côté windows qui doit afficher un waiting device. Qu&aposil attende donc...

  1. Débranchez le cable,
  2. Éteignez le device par une longue pression sur le bouton d&aposallumage, puis Éteindre,
  3. Pressez le bouton qui monte le volume (c&aposest celui qui est prêt du bouton d&aposallumage),
  4. Tout en maintenant ce bouton pressé, rebranchez le cable USB
  5. Là le device doit s&aposallumer et après un temps il va se stabiliser dans un mode étrange avec une sorte de barre de progression pour la mise à jour du firmware.
  6. Le script côté windows doit logiquement se réveiller et recommencer à vous causer,
  7. Lorsque le script a terminé son boulot, le device a redémarré en mode Android classique.
  8. Le script vous demande (en anglais) de l&aposarrêter (???) en pressant les touches Control+C , puis de pressez N puis Enter.
  9. Ceci fait, vous êtes de retours sur l&aposinterface graphique.

À ce stage, normalement c&aposest tout bon et le LG est rooté. Pour tester, déverrouillez la home et sur l&aposinterface côté Windows pressez le bouton "ADB SHELL". Vous devriez voir apparaître une console avec quelque chose comme &aposshell@e7wifi:/ $&apos. Maintenant tapez su puis validez. Et là, côté Android, une boite de dialogue doit apparaître pour vous demander si vous authoriser ce passage en mode root. Acceptez. De retour sous Windows, dans la console, devrait alors s&aposafficher root@e7wifi:/ #.

C&aposest bon, vous êtes root !

Tester le tout

Pour tester, le plus simple est d&aposinstaller ADB Wifi à partir du market et de le lancer. Si les étapes précédentes ont fonctionnées, lorsque vous activerez la ADB en mode réseau, une boite de dialogue doit apparaître pour valider l&aposaccès.

Ceci fait, ADB Wifi doit vous indiquer qu&aposil est en écoute et vous fournis l&aposIP de connection. Vous n&aposavez alors plus qu&aposà vous connecter de votre machine comme ceci :

$sudo adb connect 192.168.154.21
connected to 192.168.154.21:5555
$sudo adb shell

Conclusion

Il est toujours navrant d&aposavoir à perdre du temps sur quelque chose d&aposaussi trivial.

Espérons que peu à peu les constructeurs s&aposinspire de FairPhone et arrêtent ainsi de prendre leurs clients pour des crétins... On peut toujours rêver ;-)

Please enable JavaScript to view the comments powered by Disqus.

dimanche 17 mai 2015 à 22h13

dimanche 19 avril 2015

Guillaume Lelarge

Analyse du VACUUM

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

par Guillaume Lelarge le dimanche 19 avril 2015 à 17h21

dimanche 29 mars 2015

Guillaume Lelarge

Version 0.2 de mon livre sur PostgreSQL

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

par Guillaume Lelarge le dimanche 29 mars 2015 à 16h46

mercredi 25 mars 2015

Rodolphe Quiédeville

PgDay Paris

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 !

par Rodolphe Quiédeville le mercredi 25 mars 2015 à 20h18