PostgreSQL, le grand oublié

Bon... Postgre, c'est vrai que pendant des années on s'est croisés, perdus, revus, sans jamais rester fidèles. Cependant, maintenant que tu m'accompagnes au quotidien, rien ne me fait regretter cette vieille base de données que l'on nommait MySQL.

^ Cet article contient des données de benchmark qui ne se veulent en aucun cas une référence dans l'absolu. Nous avons testé le temps d'exécution pour des requêtes MySQL et PostgreSQL exécutées depuis PHP. Nous avons aussi souhaité savoir si le driver utilisé avait une influence sur les performances. En résumé, ce petit comparatif se veut pragmatique et ne préjuge en rien les qualités intrinsèques de chaque serveur de base de données.

Pour la plupart des projets que nous développons, nos clients nous demandent du MySQL/MariaDb. C'est du classique et bien rentré dans les esprits depuis maintenant une bonne décennie avec le classique LAMP.

Ne revenons pas sur le cas du serveur HTTP Apache souvent évoqué dans nos articles que nous remplaçons, aujourd'hui, sans sourciller, par Nginx.

En ce qui concerne MySQL, nous avons au sein de notre agence longtemps hésité à passer le cap vers une autre solution. Il aura fallut l'utilisation poussée de PostgreSQL sur un premier projet il y a quelques années pour réaliser ce changement.

Aujourd'hui, même si nous sommes convaincus des qualités de celui-ci, nous allons essayer de vous convaincre que dans beaucoup de cas, PostgreSQL est une alternative très intéressante pour le développement de vos projets.

Pour être pragmatiques dans ce choix, nous vous proposons d'analyser point par point les différences dans l'utilisation de PostgreSQL versus MySQL. Let's Go Marty !

Installation et intégration de PostgreSQL

Pour MySQL, vous avez depuis quelques années la possibilité d'utiliser le fork open-source MariaDB ou la version originale MySQL. De notre côté, nous conseillons le fork qui grâce à une communauté agrandie évolue plus vite en prenant en compte les besoins de la communauté.

@ Sur la plupart des distributions Linux, vous devrez rajouter des dépôts spécifiques.

Pour une utilisation avec PHP, plusieurs drivers existent : ext/mysqli, pdo_mysql et enfin ext/mysql. L'extension originale de Mysql (ext/mysql) qui date de la version 2 de PHP est obsolète en PHP 5.5 et supprimée en PHP 7.

Nous vous conseillons donc l'utilisation du driver PDO pour ses fonctionnalités, sa gestion des statments et sa capacité à gérer d'autres SGBD.

Enfin, pour l'administration, vous pourrez utiliser le fameux PhpMyAdmin pour une version en ligne ou le logiciel MySQL Workbench.

Du côté de PostgreSQL, l'installation se fera directement par votre gestionnaire de paquets préféré. Deux drivers PHP sont proposés et maintenus : pdo_pgsql et ext/pgsql.

De même, vous pourrez utiliser PhpPgAdmin pour une version en ligne un peu vieillissante ou le logiciel PgAdmin 3 (et la très récente version 4 que nous allons tester dans les prochaines semaines).

Quelques fonctionnalités pour vous faciliter la vie

Parmi les features intéressantes de PostgreSQL, nous en avons sélectionné quelques unes très marquantes.

Insert returning

Ah, celle là, vous allez l'aimer ! En effet, vous allez avoir la possibilité de réaliser une opération (insertion ou mise à jour) tout en récupérant directement les données dans la même requête.

INSERT INTO Member (username) VALUES ("Léo") RETURNING id;

Recursive

Pour des données de caractéristiques ou de nomenclature, il est classique de créer des table arborescence. Pour traiter et rechercher des données dans son arbre de données, cela devient vitre fastidieux à réaliser. PostgreSQL propose une fonction récursive pour traverser les arborescences très facilement.

CREATE TABLE category(
  id serial PRIMARY KEY,
  username varchar,
  parent_id integer
);

WITH RECURSIVE category(id, username) AS (
        SELECT id, username
        FROM category
      UNION ALL
        SELECT c.id, c.username
        FROM category c on referential.parent_id = c.id
)
SELECT * FROM category;

Hstore

Hstore permet de stocker des données noSQL. Hstore est une extension et doit être activée par la commande CREATE EXTENSION HSTORE;.

Dans le même contexte, vous pouvez aussi regarder la gestion JSON et surtout JSONB avec un stockage binaire.

CREATE TABLE members(
  id serial PRIMARY KEY,
  username varchar,
  params hstore
);

On peut ensuite injecter des données sous forme de clé/valeur :

INSERT INTO members (username , params) VALUES (
 'John',
 'address     => "31 rue Grenette",
  city    => "Lyon",
  country     => "France",
  age        => 35'
);

La récupération est maintenant possible directement depuis les attributs disponibles dans le Hstore :

SELECT username, params->'city' as city
FROM members 
WHERE params->'country' = 'France';

Les extensions

PostgreSQL offre un catalogue de fonctionnalités complémentaires disponible sous forme d'extensions. Elle peuvent en général s'installer facilement, proposer des nouveaux types de données et des fonctions spécifiques.

Les plus connues sont PostGIS pour la gestion de données cartographiques, Pgcrypto, PostPic ou encore Hstore.

De la perf tout simplement !

Pour l'occasion, j'ai réalisé une petite application de benchmark que je vous invite à tester et surtout à forker selon votre gout.

Le script va tester les différents drivers MySQL et PostgreSQL sur des traitements simples. L'occasion de vérifier si les versions récentes des drivers sont plus rapides que les autres et de voir si PostgreSQL réagit mieux sur la quantité que MySQL. C'est parti !

Le mode opératoire du benchmark a été de calculer la moyenne sur 10 appels du script de test. Pour ce petit comparatif, nous avons utilisé la version 5.7 de MySQL et PostgreSQL 9.5.

7 opérations simples sont testées :

• Insertion d'un enregistrement de deux champs ;
• Insertion d'un enregistrement avec une clé étrangère ;
• Récupération de tous les champs avec une condition ;
• Comptage du nombre d'enregistrements total ;
• Comptage du nombre d'enregistrement avec une condition ;
• Mise à jour d'un champ pour un enregistrement ;
• Suppression d'un enregistrement.

100 opérations

Sur un faible volume.

1 000 opérations

PostgreSQL est entre 1,5 et 1,8 fois plus rapide que Mysql sur les opérations d'insertion et de suppression. Pour les autres, la différence est négligeable. Nous constatons un grand écart de performance entre le driver natif pgSQL et la version PDO.

5 000 opérations

Dans ce cas, il semble que la différence entre PostgreSQL et MySQL soit légèrement moins importante que sur l'opération d'insertion à 1000 éléments. En suppression, PostgreSQL est 1.7 fois plus rapide que MySQL. Le driver PDO de PostgreSQL est toujours mis à mal alors que le driver natif est très efficace.

10 000 opérations

Des niveaux de performance relativement similaires que sur le test précédent. Sur l'opération de suppression, PostgreSQL est 2.2 fois plus rapide que MySQL.

Nous remarquons aussi que PostgreSQL est en grande difficulté sur le comptage sans condition et beaucoup plus performant avec des conditions.

Le test banzai !!!

Pour cet ultime test, nous avons inséré 1M de données puis lancé 100 fois les opérations suivantes : Récupération avec condition, comptage avec condition, mise à jour avec condition.

Les résultats sont un peu surprenants car MySQL se débrouille finalement bien en récupération sur une grosse volumétrie. En revanche, il perd de sa pertinence dès qu'il faut faire des mises à jour.

Force et faiblesse de PostgreSQL

• ++ Accès en concurrence bien plus performant avec MVCC;
• ++ Les bonnes performances;
• ++ Sa gestion des données JSON ou array;
• ++ Son moteur de base de données interne et maîtrisé (MySQL repose sur des solutions tiers InnoDB ou MyIsam);
• -- Une gestion des clusters qui nous a semblé plus complexe à mettre en place;
• -- Beaucoup de mots-clés réservés (ex: USER);
• -- La gestion des séquences qui peut paraître déroutante au début et demande une vigilance;
• -- La solution d'interface d'administration web Phpgadmin complètement obsolète.

Conclusion

Aussi facile d'installation et d'utilisation, rempli de fonctionnalités pratiques et bien pensées, vous n'avez pas beaucoup de raison pour ne pas utiliser PostgreSQL dans vos futurs projets.

Souvent nous avons expliqué que PostgreSQL c'était bien pour les gros volumes et les gros projets et que MySQL était adapté petits projets. Nous ne dirons plus ça aujourd'hui, car notre expérience nous a démontré que sa mise en place n'est pas plus compliquée, qu'il ne prend pas plus de ressources et qu'il est performant même sur un faible volume de données. PostgreSQL est un sujet important à discuter sur, par exemple, des audits de performances.

Dans le cas d'une migration d'un projet actuellement en MySQL, cela n'est clairement pas aussi simple car vous serez obligés de réaliser un réadaptation complète de votre modèle, repasser sur beaucoup de code (même avec un ORM).

Bref, cela se prépare bien et ne dois pas s'improviser. OpenClassRoom à justement fait un petit retour d’expérience sur ce point très interessant.

Si vous voulez passer le pas, notre dernier conseil sera de ne pas oublier les tâches de maintenance du type Vacuum qui permet de nettoyer et donc optimiser votre base de données. Un point particulièrement important sur PostgreSQL qui ne supprime pas physiquement les enregistrements automatiquement.

Enfin pour approfondir le sujet, je ne peux que vous conseiller les fameuses conférences du Doc SQL alias Grégoire Hubert. Enfin, si vous souhaitez compléter cet article et témoigner de votre retour d'expérience sur PostgreSQL : on vous laisse nous déposer vos commentaires ;)