Blog entries by Philippe Pepiot [1]

Le 8 juin 2017 nous avons assisté à pgDay, le moment de rencontre et de conférences de la communauté PostgreSQL francophone, qui s'est déroulée au campus de Météo France à Toulouse.

https://www.logilab.org/file/10126216/raw/logo_pgfr_sans_900_400x400.png

Partitionement

Gilles Darold nous a fait un tour d'horizon des solutions de partitionnement, de la méthode manuelle avec des triggers et d'héritage de table en passant par l'extension pg_partman jusqu'au partitionnement déclaratif de la future version 10 avec la syntaxe PARTITION OF

Le partitionnement permet de gérer plus facilement la maintenance et les performances de tables avec beaucoup d'enregistrements.

Transaction autonomes

Le même Gilles Darold nous a parlé des transactions autonomes c'est-à-dire des transactions qui s'exécutent dans une transaction parente et qui peut être validée ou annulée indépendamment de celle-ci, ce qui peut être utile pour enregistrer des événements.

PostgreSQL buffers

Vik Fearing nous a expliqué le fonctionnement et l'interaction des différents tampons mémoire dans PostgreSQL.

Les pages sont chargées du disque vers les shared_buffers, qui sont partagés par toutes les connexions, et ont un usageCount entre un et cinq qui est incrémenté à chaque fois qu'elle est accédée. Lorsqu'une nouvelle page doit être chargée, un mécanisme de clock-sweep boucle sur le cache et décrémente l'usageCount et quand il vaut zéro la page est remplacée par la nouvelle. Ainsi pour une page avec un usageCount à cinq, il faudra au moins cinq tours des shared_buffers par le clock-sweep avant quelle ne soit remplacée.

En cas d'un accès à une grosse table pour ne pas vider tout le cache, PostgreSQL utilise un tampon circulaire (ou ring buffer) limité en taille pour cette table.

Les tables temporaires utilisent un tampon dédié, le temp_buffers.

Quand une page est modifiée, elle l'est d'abord dans les wal buffers qui sont écrits sur disque lors du commit par le processus wal writer.

Le writer process parcoure les shared_buffers tout les bgwriter_delay (200ms) et écrit sur disque un certain nombre de pages qui ont été modifiées, ce nombre est contrôlé par les paramètres bgwriter_lru_maxpages et bgwriter_lru_multiplier.

Des checkpoint s'exécutent aussi tout les checkpoint_timeout ou plus fréquemment quand la taille des wals dépasse la valeur du paramètre max_wal_size. Lors d'un checkpoint on cherche des pages à écrire (ou dirty pages) et on les trie pour éviter les écritures aléatoires sur le disque. Le paramètre checkpoint_completion_target permet d'étaler la vitesse d'écriture entre deux checkpoint. Idéalement on veut qu'ils se déclenchent toujours par timeout et que l'écriture soit la plus étalée pour avoir des performances de lecture et d'écriture constantes.

Pour déboguer l'utilisation des buffers et les I/O disques il y a la table pg_stat_bgwriter, l'extension pg_buffercache, et le paramètre track_io_timing à utiliser avec EXPLAIN (ANALYZE, BUFFERS).

Les pires pratiques PostgreSQL

Thomas Reiss et Philippe Beaudoin nous ont présenté quelques unes des plus mauvaises pratiques avec PostgreSQL, notamment de celle répandue du manque ou d'excès d'index. À ce sujet Dalibo a développé l'outil PoWA qui analyse l'activité d'une base et fait des suggestions d'index. Attention aussi à la tentation de (trop) destructurer les données, PostgreSQL possède de nombreux types qui offrent une garantie forte sur la consistance des données et de nombreuses opérations dessus, par exemple les types ranges.

La communauté des développeurs de PostgreSQL

Daniel Vérité nous a fait un historique de Ingres puis Postgres, et enfin PostgreSQL avant de nous montrer des statistiques sur les commits et la liste de diffusion utilisée pour le développement de PostgreSQL

Les éléphants mangent-ils des cubes ?

Cédric Villemain nous a parlé des fonctionnalités de PostgreSQL pour des requêtes de type OLAP. L'implémentation de TABLESAMPLE qui permet de faire des requêtes sur un échantillon aléatoire d'une table. Le paramètre default_statistic_target et la nouvelle commande de la version 10 CREATE STATISTICS qui permettent d'obtenir de meilleurs statistiques sur la distribution de la table et donc d'avoir de meilleurs plans d'exécution.

Aussi depuis la version 9.4, la syntaxe GROUP BY ROLLUP permet de faire des agrégats sur plusieurs GROUP BY dans une seule requête. Auparavant il fallait faire plusieurs UNION pour obtenir le même résultat.

À noter aussi l'utilisation d'index BRIN et BLOOM.

Comment fonctionne la recherche plein texte ?

Adrien Nayrat nous a présenté les fonctions de recherche plein texte dans PostgreSQL et le moyen de l'améliorer en créant ses propres configurations et dictionnaires de mots, ainsi qu'à la rendre plus performante avec les index GIN et GIST.

GeoDataScience

Olivier Courtin nous a montré avec un exemple concret comment PostgreSQL pouvait être un environnement idéal pour la géomatique et le machine learning avec les extensions PostGIS, ainsi que plpythonu utilisé pour exécuter du code python directement sur le serveur PostgreSQL. L'extension dédiée crankshaft propose des API basées sur scipy et scikit-learn et peut être appelée via des procédures SQL.

https://www.logilab.org/file/10126217/raw/freefall.gif
blog entry of