PostgreSQL, le grand oublié

Difficulté : | 10' Publié il y a 7 mois
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 ;)

Tags de l'article :

bonnes pratiques PostgreSQL

Commentaires

  • Il y a 7 mois Dominique De Vito : Répondre

    Tout d'abord, merci pour ce billet qui témoigne de votre expérience.

    > -- Une gestion des clusters qui nous a semblé plus complexe à mettre en place;

    Je me dis que le jour où ce point aura été amélioré, MongoDB pourra fermer boutique !

    > -- La solution d'interface d'administration web Phpgadmin complètement obsolète.

    Même avec la dernière version qui vient de sortir https://www.pgadmin.org/ ?

  • Il y a 7 mois Dominique De Vito : Répondre

    J'ai oublié de mentionner un des autres avantages de Postgresql.

    ++++ une (nouvelle) version significative sort par an. Et chaque nouvelle version apporte son lot de nouveautés et d'améliorations !

    Cela saute aux yeux lors d'une rétrospective des 5 dernières années : http://bonesmoses.org/2016/01/08/pg-phriday-how-far-weve-come/

    Et le plan pour les prochaines versions a l'air appétissant : http://rhaas.blogspot.fr/2016/01/postgresql-past-present-and-future.html

    Bref, à mon sens, miser sur Postgresql, c'est aussi miser sur une communauté qui a déjà accompli beaucoup (!) et qui fait que l'éléphant continue son petit bonhomme de chemin, qui plus est, sur la bonne route !

    • Il y a 7 mois Manuel

      Merci pour ce commentaire qui apporte des précisions à l'article. PhpPgAdmin est un projet a l’abandon et il est beaucoup plus pertinent de passer par PgAdmin et surtout sa nouvelle version 4 (encore non testé).

  • Il y a 7 mois Nayrat Adrien : Répondre

    Merci pour cet article!

    Effectivement phppgadmin subit peu d'évolution. pgadmin4 s'annonce prometteur, il y a également plusieurs projets comme postgresqlstudio ou dbeaver. Mais il faut surtout mentionner la console psql qui est vraiment très puissante (ce qui explique probablement le faible nombre d'outils graphiques).

    En ce qui concerne les benchmark, j'attire votre attention sur le fait que le moteur nécessite une configuration suivant les ressources disponibles. La configuration par défaut est vraiment minimaliste.

    Enfin, il faut également préciser que Postgres est un projet Opensource Communautaire. C'est grâce à cette communauté que le développement est aussi actif. C'est aussi une garantie que le projet ne sera pas détourné par une société.

    Il existe de nombreux évènements par le monde... mais pas que! En France l'association PostgreSQLFR organise les pgday, il existe aussi des meetup Postgres (ça marche bien à Nantes et à Paris). Enfin, des sociétés organisent aussi des conférences.

    Et +1 pour les présentations de Grégoire Hubert! Il est aussi le concepteur de POMM. Ses présentations à ce sujet sont très riches.

    • Il y a 7 mois Manuel

      En effet, ce rapide benchmark est basé sur les configurations par défaut sans tweaks (représentatif de l'essentiel des installations).

      La communauté PostgreSQL est clairement un atout vis à vis de la concurrence et on ne peut que s'en féliciter.

      Merci pour ce commentaire.

  • Il y a 7 mois Gérard Ernaelsten : Répondre

    Juste par curiosité, avez vous déjà mis en place POMM de Grégoire?

    Histoire de by Passer PDO, mais d'avoir quand même un outil puissant permettant une série de fonctions supplémentaires à vos projets.

    • Il y a 7 mois Manuel

      Nous avions suivi une conf sur POMM il y a quelques mois sans ensuite, avoir forcément le temps de tester. Le sujet nous intéresse donc nous allons nous pencher sur cet OMM rapidement.

  • Il y a 7 mois webaaz : Répondre

    Et Pomm alors ? On en parle ? Parce-que parler de Postgres et de Php sans parler de Pomm...

    http://pomm-project.org

    • Il y a 7 mois Manuel

      Votre commentaire est complètement pertinent, c'est un sujet qui viendra par la suite ;)

  • Il y a 7 mois webaaz : Répondre

    L'essayer c'est l'adopter ! http://www.webaaz.com/post/132331759564/pomm-ou-la-red%C3%A9couverte-de-sql


    • Il y a 7 mois François

      Très bon article @webaaz ! Merci :-)

  • Il y a 7 mois Jérôme : Répondre

    Hello,


    Pour les personnes confrontées aux soucis d'imports de data dans PG, le protocole COPY est juste super pratique : https://www.postgresql.org/docs/current/static/sql-copy.html

    Aussi voir du côté de http://pgloader.io/ :)