Footcow BlogFootcow Blog

Faire le choix entre MySQL ou PostgreSQL est une décision que j'ai du prendre voilà pratiquement 9 ans. A cette époque, j'avais fait un choix, sécuritaire pour un gros client: Oracle. Oracle est sans nul doute, la base de donnée la plus performante, et la plus robuste du marché. C'est aussi de loin la plus complexe à paramétrer, ou à tuner comme on dit dans le jargon. Mais c'était, et je pense que c'est encore, surtout la base de données la plus chère en terme de licence et de support technique. A l'époque, l'extension de serveurs engendrait des coûts de licences astronomiques et mon client m'a demandé de trouver une solution moins coûteuse. Je me suis alors orienté vers l'open source, et j'ai pratiqué des bancs d'essai équivalents aux conditions réelles de charge de nos bases de données de production. Et j'ai en même temps cherché à économiser les temps de développement déjà réalisés (procédures stockées, triggers, développement transactionnel etc.) sous Oracle pour mon prochain choix.

A l'époque les résultats étaient sans équivoque. PostgreSQL était structurellement le plus proche d'Oracle. On trouvait déjà de nombreuses similitudes entre les deux environnements, y compris des scripts de migration des structures de tables, et de nombreux supports pour porter les fonctions écrites sur Oracle en PL/SQL vers le PL/PgSQL de PostgreSQL. De plus les triggers étaient également présents de base dans PostgreSQL. Alors que toutes ces fonctionnalités n'étaient même pas envisageable à l'époque dans MySQL.

Enfin, les tests de charge étaient sans commune comparaison. Oui, MySQL se détachait radicalement sur la vitesse d'écriture, mais la réalité d'une base de données de production d'un service web montrait que la base paniquait assez vite passé un volume d'accès simultanés, tandis que PostgreSQL malgré sa "relative" lenteur tenait un aplomb sans famille avec 2 voir 3 fois le volume d'accès après lequel MySQL s'écroulait.

Le choix était donc fait. Naturellement, depuis, les choses ont évolué, et il est indéniable que les deux moteurs de base de données les plus utilisés du marché ont largement prouvé leurs capacités et leur robustesse. MySQL est beaucoup plus populaire car souvent installé chez les hébergeurs avec son association aux projets LAMP, sa réputation de vitesse et sa simplicité de configuration. Alors que PostgreSQL a souvent été choisi, comme dans mon cas, par des développeurs qui utilisaient avant Oracle ou SQL Serveur. Ces clichés sont maintenant souvent erronés ou dépassés. MySQL est équipé, en particulier depuis la version 5.x, de fonctionnalités avancées, tandis que PostgreSQL a amélioré de façon radicale sa vitesse depuis sa version 8.x. Il faut voir aussi à comparer des choses comparables, MySQL possédant des moteurs différents, moteurs qui ne garantissent pas tous l'intégrité des données de la même manière que le moteur de PostgreSQL. Comparer par exemple MyISAM et PostgreSQL est une hérésie. Aujourd'hui, si on veut véritablement comparer les moteurs on choisira InnoDB, moteur transactionnel de MySQL, et dans ce cas souvent PostgreSQL est bien plus rapide que ce dernier.

Passons donc à une revue de détail des fonctionnalités étalées par ces deux produits.

Perfomance

Les bases de données peuvent être optimisées en fonction de l'environnement ou du cadre spécifique fonctionnel dans lequel elles doivent évoluer. Ainsi, il est très difficile de faire de véritables comparaisons de performance sans porter une attention particulière à la configuration et à l'environnement d'exploitation.PostgreSQL et MysQL se servent toutes les deux de technologies différentes pour améliorer les performances de base :

Historique

MySQL a été développé au départ dans le seul but d'être une base de données rapide, alors que PostgreSQL était orienté sur le respect des standards et les fonctionnalités avancées.

Vitesse d'écriture

MyISAM, comme indiqué au préalable, est bien plus rapide que le moteur de PostgreSQL mais uniquement si on compare des requêtes simples, dans un cadre de fonctionnement simple, sans trop de requêtes simultanées et sans prendre en compte l'intégrité des données. MyISAM ne supporte pas les transactions, ni les clés étrangères et ne garantit pas en conséquence une base de données viable dans le temps à l'inverse de PostgreSQL.

Compression de données

PostgreSQL peut compresser et décompresser ces données à la volée en utilisant un schéma de compression rapide. L'intérêt de la compression de données réside, outre l'espace disque gagné, de réduire les accès sur disque et donc des lectures/écritures plus rapide des données. Seule la version 6.0 de MySQL et son moteur Falcon supporte la compression à la volée, mais uniquement pour l'écriture sur le disque, les données restent décompressées en mémoire.

Accès concurrents

PostgreSQL gère nettement mieux les accès concurrentiels autant vis à vis de la configuration matérielle que de sa méthodologie.

I/O asynchrones

PostgreSQL permet nativement les accès asynchrones au travers d'API utilisées par des applications clientes. Il permet d'optimiser jusqu'à 40% les performance dans certains cas. MySQL ne dispose pas de ce type d'API, même si certains drivers ont été écrits par la communauté pour couvrir se manque en Perl et en Ruby en particulier.

COUNT(*)

Le COUNT(*) de PostgreSQL est particulièrement lent parce qu'au lieu de compter les enregistrements en utilisant un index il va parcourir séquentiellement l'intégralité de la table. PostgreSQL a implémenté le COUNT(*) de cette façon car la méthode de stockage du MVCC (système concurrentiel de PostgreSQL) réside dans les données et non dans un index. Ce qui garantit aussi un comptage parfaitement exact. Le moteur MyISQM de MySQL utilise un index pour le COUNT(*) et met également en cache le résultat du comptage, ce qui rend le comptage instantané, mais dans un fonctionnement transactionnel, ce comptage peut par conséquent être erroné. Avec le moteur InnoDB de MySQL, le fonctionnement est strictement comparable à celui de PostgreSQL et les performances également,

Compatibilité ACID

ACID signifie : Atomicity, Consistency, Isolation and Durability. Ce modèle est utilisé pour juger de l'intégrité des données dans le système de gestion des données. La plupart des base de données utilisent ACID pour leur système transactionnel. PostgreSQL est 100% compatible ACID, tout comme InnoDB qui est le seul moteur de MySQL dans ce cas.

Fonctions

PostgreSQL et MySQL ont toutes les deux un grand nombre de fonctions internes qui permettent d'augmenter l'intégrité des données, les fonctionnalités et les performances.

Insert Ignore / Replace

MySQL supporte les commandes INSERT IGNORE et REPLACE qui permettent d'insérer des enregistrements. Si un enregistrement existe déjà alors rien ne se passe sinon dans l'autre cas il remplace l'enregistrement actuellement présent. PostgreSQL ne supporte aucune de ces fonctions, mais elles peuvent être simulées au travers de procédures stockées. La version 8.3 de PostgreSQL permet également l'insertion multiple d'enregistrements dans une seule commande comme le permet MySQL depuis longtemps.

Contraintes

MySQL et PostgreSQL supportent les contraintes de type Not Null, Unique, Primary Key et Foreign Key. Seul PostgreSQL supporte la fonction Check constraint.

Procedures stockées

PostgreSQL et MySQL supportent les procédures stockées. Le langage principal pour PostgreSQL est PL/PgSQL, assez similaire au PL/SQL d'Oracle. PostgreSQL supporte également les procédures stockées dans de nombreux autres langages comme PHP, Python, Perl, TCL, Java & le langage C. MySQL respecte la syntaxe SQL:2003 tout comme DB2 d'IBM depuis la version 5.1.

Triggers

PostgreSQL et MySQL supportent les triggers. Un trigger PostgreSQL peut exécuter n'importe quelle fonction écrite dans n'importe quel langage procédural et pas seulement en PL/PGSQL. Les triggers de MySQL ne sont activés que par des requêtes SQL. Ils ne sont pas activées par des changements dans les tables réalisés par des API qui ne peuvent pas transmettre des instructions SQL au serveur MySQL, en particulier, ils ne sont pas activées par des mises à jour effectués à l'aide des API NDB. PostgreSQL supporte également des "rules," qui permettent d'intervenir sur l'arbre de syntaxe des requêtes et qui peuvent faire des opérations plus simplement que par celles faites par de simples triggers.

Réplication et haute disponibilité

La réplication est un système qui permet de dupliquer les données d'une base soit pour en faire une sauvegarde, soit pour permettre une mise à disposition constante d'un service sans coupure en cas de panne d'un serveur par exemple. PostgreSQL et MySQL supportent toutes les deux la réplication:

PostgreSQL

PostgreSQL est un système modulaire qui n'intègre pas la réplication en standard. Il existe différentes solutions de réplication :

• PGCluster
• Slony-I
• DBBalancer
• pgpool
• SkyTools
• Sequoia

MySQL

Depuis la version 5.1 de MySQL, un système de réplication est intégré de deux façons : statement based replication (SBR) et row based replication (RBR). SBR collecte les requêtes SQL qui font des modifications sur la base de données dans un fichier binaire de log auquel les esclaves souscrivent pour appliquer les modifications. RBR stocke les enregistrements modifiés dans un fichier binaire de log qui est appliqué aux esclaves.

Types de données

PostgreSQL ne supporte pas le type de données entier non signé, mais il a un nombre considérable de types de données bien plus riche dans différents aspects: support des standards, le type BOOLEAN, la possibilité de créer ses propres types pour l'utilisateur, et de nombreux types internes ou fournis par la communauté. PostgreSQL permet de définir un colonne d'une table comme un tableau multidimensionnel, un type définit par l'utilisateur lui-même, un type sur liste de donnée, ou encore des types composites peuvent être créés. MySQL ne supporte pas les types du genre adresse IP par exemple.

Sous-requêtes

MySQL depuis la version 5 et PostgreSQL depuis toujours supportent les sous-requêtes. MySQL dans certain cas perd énormément en performance dans certain cas complexes, ce qui est corrigé en version 6.0.

Communauté

PostgreSQL n'est pas contrôlé par aucune société, mais fonctionne uniquement au travers d'un communauté de développeurs et de sociétés qui travaillent au développement et à l'amélioration du produit. La communauté PostgreSQL est assurément l'une des plus active, et surtout réactive en cas de problème. C'est un véritable bonheur d'avoir des personnes aussi impliquées qui répondent et agissent autant pour un projet open source. MySQL est la propriété d'une société suédoise, MySQL AB, aujourd'hui filiale de Sun Microsystems. Certains moteurs sont propriétés de société comme Oracle, ou sont en open source.

Voilà, je pense avoir grossièrement fait le tour des deux projets ... vous connaissez tous mon penchant pour PostgreSQL, libre à vous de faire le bon choix, le plus approprié à vos besoins réels.