PostgreSQL: fermer toutes les connexions sur une base de données

PostgreSQL dispose d’une table d’état très utile appelée pg_stat_activity. Cette table est similaire au « SHOW PROCESSLIST » qu’on retrouve en MySQL, mais a le net avantage d’être requêtable et dispose d’informations plus précises que MySQL.

En voici un extrait:

Dans certains cas, il peut être utile de couper toutes les connexions à une base de données précise (par exemple des connexions dormantes en masse). Voici une simple requête SQL à jouer sur votre PostgreSQL (9.2 et plus) permettant de couper toutes les connexions: Continuer à lire

PostgreSQL: changer le owner de toutes les tables/séquences d’un schéma

Lors d’une restauration de base de données, parfois il se peut que vous ayez besoin de changer le propriétaire d’une table ou d’une séquence pour un autre user, par exemple si vous prenez une base de production pour la mettre sur votre intégration pour vos développeurs (anonymisées, bien sûr 😉 ).

Plutôt que de devoir faire fastidieusement un ALTER TABLE table par table, voici 2 requêtes SQL qui vont vous permettre de générer les SQL pour changer rapidement le owner de toutes les tables et séquences: Continuer à lire

PostgreSQL: supprimer toutes les tables d’un schéma rapidement

Parfois, il peut être utile de supprimer toutes les tables d’un schéma directement, notamment si vous souhaitez réimporter un backup depuis un autre environnement.

Il peut être fastidieux de faire des DROP TABLE unitaires, heureusement les schémas PostgreSQL respectent la norme SQL. Contrairement à MySQL qui mélange la notion de base de données et schéma, sur PostgreSQL les bases de données peuvent comporter plusieurs schémas.

Continuer à lire

Optimisations BDD: indexes en doublon

Les indexes prennent de l’espace disque et de la mémoire. Les moteurs MySQL/MariaDB/PostgreSQL savent utiliser des indexes plus complexes afin de lire des données nécessitant un seul index donc les champs conditionnant sont inclus dans un index plus complexe.

Exemple ici avec 3 indexes

Si nous faisons une requête filtrant sur le champ id_1 uniquement (SELECT field1 FROM table1 where id_1 = 3), le moteur de BDD est capable d’utiliser 2 indexes: index_b, qui ne contient que id_1 et index_a qui contient id_1 puis id_2.

En revanche, si nous faisons une requête filtrant sur le champ id_2 uniquement (SELECT field1 FROM table1 where id_2 = 6), le moteur de BDD ne pourra utiliser que index_c.

Si nous reprenons index_a, il n’est pas valide car le moteur de BDD lira cet index en trouvant les records discriminants sur id_1 avant les records discriminant sur id_2. Ce n’est pas un chemin optimal pour trouver un résultat dépendant uniquement de id_2. L’ordre des champs d’un index est donc important.

Pour finir, index_a filtrant tout d’abord sur id_1 avant de filtrer sur id_2, il sait donc filtrer uniquement sur id_1 et donc index_b est inutile (il prend des ressources disque et mémoire inutiles). Supprimez donc index_b qui prend de l’espace disque et mémoire pour rien.

PostgreSQL: nettoyer complètement un schéma sans effort

Dans certains cas, comme par exemple dans un environnement d’intégration, il peut être intéressant de nettoyer toutes entrées dans votre BDD PostgreSQL. Lorsque le nombre de tables devient grand et surtout lorsque vous avez construit un schéma robuste à base de clefs étrangères, il peut être fastidieux de nettoyer ce schéma.

Heureusement, il est possible de nettoyer votre base en demandant à PostgreSQL de générer la requête qui va lister les tables du schéma et les nettoyer. Continuer à lire

Benchmark comparatif: PostGreSQL 9.1

Suite à une volonté de tester l’arbre de dports de DragonFlyBSD, et voulant voir ce que vallaient les performances de PostGreSQL sur DragonFlyBSD j’ai décidé de monter un rapide benchmark de performances afin de comparer plusieurs systèmes ayant une version de PostGreSQL commune. J’ai choisi pour ces raisons PostGreSQL 9.1.

Lors de mon benchmark sous Linux (Debian), je me suis retrouvé confronté à des statistiques étonnantes en terme de performances, de ce fait, j’ai décidé de voir ce que vallait une Redhat, j’ai donc pris Centos 6.4.

Les tests ont été effectués sur les plateformes suivantes:

  • DragonFlyBSD 3.4.1 (système de fichiers Hammer)
  • FreeBSD 9.1-p3 (système de fichiers UFS2+J)
  • FreeBSD 9.1-p3 (système de fichiers ZFS v28)
  • Debian 7: Wheezy (système de fichiers ext4)
  • Centos 6.4 (système de fichiers ext4)

Continuer à lire