Migration Oracle PostGreSQL : Les 13 grandes lacunes qui peuvent s’avérer cauchemardesque !

Certains aficionados de PostGreSQL tente de comparer PostGreSQL à Oracle, affirmant que PostGreSQL fonctionne presque pareil…
Alors, puisque c’est le cas, renchérissent leurs économes, ne payons plus de licences et migrons que diable !
Pourtant de nombreux spécialistes des bases de données ayant une importante expérience dans l’utilisation de divers SGBDR et ayant dû utiliser PostGreSQL en remplacement d’Oracle ou SQL Server s’en sont mordu les doigts…
Voici quelques uns des écueils qui peuvent vous attendre le jour ou vous passerez d’une base commerciale comme IBM DB2 UDB, Oracle ou SQL Server à PostGreSQL.

1 – Aucune gestion des espaces de stockage.

Dans les SGBDR comme Oracle, SQL Server ou IBM DB2, il est possible de gérer les espaces de stockage de manière très précise, et cela afin de savoir précisément ou se trouve une table ou un index, en sus de pouvoir régler la taille et le taux de croissance des espaces de stockage.
Le principe est généralement le suivant. A une base de données est associé un ou plusieurs espaces de stockage (« storage » dans la littérature anglo saxonne) que l’on appelle « tablespace » dans Oracle et « filegroup » dans SQL Server. Ces espaces de stockage constitue la destination du stockage des données des tables. A la création d’une table ou d’un index, il suffit de préciser le « storage » que l’on souhaite affecté à l’objet.
Chaque storage doit comporter au moins un fichier, mais peut en compter plusieurs.
Pour chaque fichier créé on peut généralement préciser la taille minimale, maximale et le pas de croissance.
En fixant une taille minimale, le fichier est directement créé de la taille précisé (ce n’est pas une pré réservation d’espace comme on le croit trop souvent) et l’algorithme utilisé permet d’aller scruter les disques à la recherche du meilleur emplacement possible pour les données. Ce meilleur emplacement sur un disque neuf est constitué par les cylindres extérieur de tous les plateaux !

Le but de ce système est double :

  • permettre de ventiler les entrées/sorties (IO), à condition que chaque fichier soit créé sur un disque physique indépendant (au revoir les VM et attention aux sans avec des LUNs taillées dans la masse, toutes choses qui constituent une plaie pour les DBE).
  • Mais aussi diminuer les temps d’accès disque. On voit bien qu’en utilisant les cylindres externes des disques on fait d’une pierre deux coups : on peut lire des données « simultanément » sur différents plateaux à la fois et le temps d’accès tend vers celui de la vitesse de rotation des disques.

De plus, les SGBDR comme Oracle, SQL Server ou IBM DB2, sont capable de regrouper les pages à écrire par contiguïté géographique, afin de minimiser le trajet de la tête de lecture. C’est loin d’être négligeable et Michael Stonebraker qui a mise en évidence ce problème [1] et réalisé quelques uns des algorithmes pour ce faire le sait bien… Il est aussi à l’origine de postGreQSL ! Encore faut-il que les espaces de stockage aient été créée en un seul bloc pour éviter toute fragmentation !

Enfin, Oracle et SQL Server permettent de placer les espaces de stockage que l’on souhaite en READ ONLY, ce qui permet d’éviter tout verrouillage.

A lire, en complément sur le sujet : Question sur les fichiers et le stockage

Or PostGreSQL ne dispose d’aucune gestion des espaces de stockage, autre que celle de déclarer un ou plusieurs répertoire en tant que « storage » pour une base.
La notion de tablespace PG ne fait qu’indiquer un répertoire.
Voici la syntaxe PG (v9.1.1):
CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'
Ce que vous ne pouvez pas faire avec PostGreSQL :
1) vous ne pouvez pas indiquer une taille minimale de l’espace de stockage
2) vous ne pouvez pas indiquer une taille maximale de l’espace de stockage
3) vous ne pouvez pas indiquer un pas de croissance de l’espace de stockage
4) vous ne pouvez pas ajouter un autre répertoire a un tablespace déja créé
5) vous ne pouvez pas placer un espace de stockage en READ ONLY pour des tables statiques afin d’éviter le verrouillage
Toutes ces possibilités existent sur Oracle, SQL Server, IBM Db2, Sybase ASE…

2 – Une gestion des transactions « curieuse ».

Une fonction SQL (il n’existe pas de procédure stockée dans PostGreSQL) peut encapsuler n’importe quel code SQL du DML (INSERT, SELECT, UPDATE, DELETE) et semble agir comme une procédure stockée, à une exception près : il est impossible de gérer une transaction à l’intérieur du code d’une fonction postGreSL. En fait PostGreSQL considère qu’une fonction = une transaction !
Ceci pose de nombreux problèmes…
Le fait qu’une fonction soit en elle même une transaction est un choix plus que discutable. En effet que se passe t-il si vous avez lancé une transaction qui lance une procédure ? (transactions imbriquées)
Le cas des transactions imbriquées est toujours complexe, mais sans méthode de résolution, il est difficile de savoir ce qui se passe.
En sus ce n’est pas parce que il y a une erreur que l’on doit considérer automatiquement que la transaction doit être annulé. C’est en principe l’auteur du code et lui seul qui doit décider de la règle de gestion et non le SGBDR !
Considérant par exemple, une gestion de stock, on peut essayer de déstocker du matériel et partir en erreur dans une table car on dépasse le seuil. Mais on peut alors décider en cas d’erreur de lancer une commande. Ce que visiblement PG ne sais pas faire au sein d’une même fonction…

On présente souvent le langage PLpgSQL comme l’équivalent du PL/SQL d’Oracle… Il n’est en évidemment rien. Seul quelques éléments de syntaxe sont commun…

Quand à la récriture d’une base de données avec de nombreuses transactions effectuées dans de nombreuses procédures, c’est un véritables cauchemar à transposer en PostGreSQL !

Un exemple nous est fournit avec la demande suivante émanent du forum PostGreSQL : « Pouvoir lancer deux fonctions qui utilisent la même table ». Ce serait si simple avec une gestion de transactions à l’intérieur de la procédure !!!

3 – Un partitionnement plus que léger

Comme le dit Mladen Gogala « Postgres partitioning is not very robust » ce que confirme différents internautes (voir ce message).
Dans cet article « Partitionner une table. Comparaison PostGreSQL / MS SQL Server » je mentionne l’extrême complexité du partitionnement et les risques énormes de se tromper.
Les développeurs de PostGreSQL conscient du problème ont réagit. A lire « Table Partitionning » mais hélas ce ne sont que des paroles ! Une nouvelle version du partitionnement devait voir le jour avec la version 9.0. Nous en sommes à la 9.1 et toujours rien !

4 – Pas de tag (hint) pour forcer les plans de requête

L’un des principaux griefs des Oracliens sur PostGreSQL est l’absence de hint (ou tag de requête) permettant de forcer le plan. Ce peut être par exemple d’indiquer qu’il faut toujours prendre tel index et jamais la table ou que l’algorithme de la jointure doit être celui-ci et non un autre.
L’utilisation des tags de requête ne doit pas être entrepris à la légère car il fige un plan qui devrait être réévalué à chaque modification de la distribution des données comme du volume. Mais il y a des cas ou la science de l’optimiseur est pris en défaut et d’autre ou l’on souhaite contraindre explicitement, par exemple pour éviter un verrou de table.
Or la philosophe particulièrement croustillante des développeurs de PG est que c’est très mal de mettre des tags dans les requêtes : « Why PostgreSQL Doesn’t Have Query Hints »
Dans un monde parfait, cela serait absolument génial… Mais malheureusement il y a la réalité, et là force est de constater que l’optimisation des requêtes par PostGreSQL est loin d’être… optimale !

Voici un exemple calamiteux ou PostGreSQL choisit de lire une table constituée de 50 pages plutôt que de lire un index de 2 pages (testé en version 9.1) :


CREATE TABLE T (NID INT NOT NULL, DATA VARCHAR(8000));
 
INSERT INTO T SELECT 1, REPEAT('@', 8000);
 
INSERT INTO T SELECT MAX(NID), REPEAT('@', 8000) FROM T;
--> répétée 50 fois
 
CREATE INDEX X ON T (NID);
 
SELECT NID FROM T;

Le plan de requête de PostGreSQL est le suivant :

Seq Scan on t (cost=0.00..11.40 rows=140 width=4)

On voit que PostGreSQL balaye lamentablement toute la table alors qu’il devrait utiliser l’index qui ne contient que la colonne visée par le SELECT !

Dans un SGBDR commercial qui commettrait une telle étourderie, on rajouterais par exemple un tag du style :

SELECT NID FROM T WITH(INDEX(X));

Les seuls paramétrages de PostGreSQL le sont au niveau du serveur ou de la session, ce qui ne permet vraiment pas de corriger les erreur de l’optimiseur pour certaines requêtes…

Mais il existe une alternative… Utiliser une version payante de PostGreSQL, appelée EnterpriseDB qui, curieusement intègre les tags !.
Ce qui contredit les détracteurs du « y’a pas besoin de tag de requête dans SQL Server, puisque son optimiseur est parfait ! »… Mais alors pourquoi et comment EnterpriseDB arrive-il à vendre leur solution (4 495 $ par an, service d’assistance compris) ?

5 – un optimiseur très limité

Nous venons de voir une erreur de l’optimiseur très grossière… Ce n’est hélas pas la seule. Dans l’article sur le partitionnement nous avons montré des plans de requêtes particulièrement épouvantable ôtant tout intérêt au partitionnement
Il n’est qu’a consulter la mailing list consacré au sujet des performances de PostGreSQL pour constater les difficultés de certains pour obtenir un plan de requête efficace.
Une des anomalies les plus fréquente est celle concernant le calcul des statistiques qui permettent à l’optimiseur de choisir le bon plan de requête. Il semble qu’il faille régulièrement changer le nombre d’histogramme de l’échantillonnage que PG effectue. Quel est le bon réglage ? À priori il semble dépendre de la dispersion des données et du nombre de lignes… Bref, une partie du travail de l’optimiseur doit être fait au quotidien par le DBA… Pas très pratique !

6 – Une indexation à la traine

En matière d’index, PostGreSQL montre certaines lacunes. Par exemple il n’y a toujours pas d’index couvrants (clause INCLUDE), ni d’index XML et la méthode d’indexation de PostGIS avec les index GIST est loin d’être d’une grande efficacité. Pour un comparaison, lire « Tout sur l’index »

Il n’existe pas non plus de vues matérialisées comme ce qu’offre Oracle ou de vues indexées à la SQL Server. C’est pourtant d’une redoutable efficacité dès que l’on a des calculs d’agrégats portant sur de forts volumes de données.

En sus il n’y a pas moyen de savoir quel sont les index manquant comme on le trouve par exemple dans MS SQL Server avec les vues systèmes sys.dm_db_missing_index_details et autres.
Bien qu’il soit possible de savoir comment sont utilisés les index (vue pg_stat_sys_indexes) les informations ne montrent pas le nombre de mise à jour effectuées sur l’index qu’il faut aller récupérer par approximation au niveau table (pg_stat_all_tables).
SQL server : index manquants
Liste des index manquants (MS SQL Server)

Avec PostGreSQL, il faut donc deviner quel sont les index à poser et ceux à supprimer, et comme les outils de monitoring sont assez pauvres, la partie est loin d’être gagnée…

7 – Pas de parallélisme des requêtes

Lors d’un congrès PG Days organisé par Dalibo, Bull montrait comment les caisses d’allocations familiales avaient migré un ancien système propriétaire de stockage des données en le remplaçant par PostGreSQL. Discutant avec l’un des responsable du projet, ce dernier me confiait que pour lui le principal manque de PostGreSQL résidait dans le fait de l’absence de parallélisme pour traiter une même requête.
Oracle, SQL Server et DB2 savent faire cela depuis de nombreuses années…
Les conséquences de cet absence de parallélisme peuvent être très problématiques sur de grands volumes :

  • il augmente considérablement le temps de réponse d’une requête
  • la table étant verrouillée plus longtemps la concurrence d’accès diminue

Tout cela ne plaide pas en faveur de l’exploitation de grandes bases de données avec PostGreSQL.

8 – Une administration couteuse

Lorsque l’on compare les outils administration des grands SGBDR par rapport à l’offre PostGreSQL le DBA se sent immédiatement frustré !
Son entreprise ne le sait pas encore mais il va lui falloir passer beaucoup plus de temps, car les outils, de monitoring comme ceux administration sont encore embryonnaires. Nous en avons déjà parlé au niveau des index ou tout doit se faire à la main (recherches des index manquants ou inutiles). Un seul exemple nous montrera l’étendu de l’écart. SQL Server dispose d’un outil de reniflage de l’activité du serveur (le profiler SQL) qui permet de lister par exemple toutes les requêtes et procédures, leurs temps CPU ou intrinsèque, le nombre de lectures ou d’écriture, etc et ce de manière ou débusquer les optimisation a entreprendre. Associé à RML par exemple il permet de faire des tests comparatif en reproduisant exactement (y compris la concurrence des différentes sessions) la charge d’une base ou d’un serveur sur une autre machine configurée différemment.
En comparaison, PostGreSQL ne propose que des outils frustes, simplistes et rarement « friendely user » : pgfouine / trsung. Il faut aller en chercher quelques uns ailleurs, payant bien sûr. Et trouver la documentation pour savoir s’en servir correctement est une gageure car il n’existe aucune méthodologie…

Ne parlons pas du merveilleux (?) VACUUM, bonne à tout faire de PostGreSQL que la plupart des DBA habitués à des outils précis et peu intrusifs exècre… Il bloque et lock à mort !

9 – pas de pooling des connexions

La plupart des SGBDR comme Oracle ou SQL Server intègre un pooling de connexions. Pas PostgreSQL. Il faut rajouter un module nommé PG Pool pour ce faire. Le pooling permet d’éviter que trop de connexion écroulent le système, en faisant alterner les connexions et les threads d’exécution entre les différentes demandes.

10 – Pas de vision d’un plan en cours d’utilisation

L’absence de « shared pool » (cache de procédure partagé) rend impossible de lire le plan d’une requête qui vient d’être exécutée, contrairement à ce que l’on peut faire avec Oracle ou SQL Server

11 – Un processus de sauvegarde peu performant

PostGreSQL n’a pas la possibilité de paralléliser sa sauvegarde sur plusieurs fichiers simultanément, ce qui ne permet pas de diviser par n (n étant le nombre de support de la sauvegarde) le temps de la sauvegarde.
De même PostGreSQL ne permet pas de réaliser des sauvegardes différentielles, c’est à dire des sauvegardes qui ne prennent en compte que les données ayant changées depuis la dernière sauvegarde.
Ce sont des choses indispensables lorsque l’on commence à attaquer des bases de grandes volumétries.

12 – Pas de compression ni de cryptage des données

PostGreSQL ne permet pas de compresser les données à aucun moment et n’intègre aucun mécanisme de cryptage. En comparaison SQL Server intégre le cryptage depuis la version 2005 et la compression au niveau page ou ligne depuis la version 2008.

13 – Pas de réplication des données *

Mise à Part les contributions externes Slony et Londist (peu performant car basés sur des triggers), PostGreSQL ne dispose d’aucun mécanisme intégré de réplication des données, contrairement à ce qui se fait par exemple chez SQL Server ou l’on dispose de 4 modes de réplications en fonction des besoins, en sus de Service Broker qui peut être utilisé dans ce sens : réplication transactionnelle, réplication par cliché (snapshot), réplication de fusion et réplication point à point.

14 – PostGreSQL reste un bon SGBDR réellement libre

Après avoir brossé ce tableau noir, dire de PostGreSQL qu’il reste un bon SGBDR semble utopique… Mais c’est pourtant vrai !
En effet, aucun autre SGBDR dans le libre n’arrive à la hauteur de PostGreSQL en terme de fonctionnalités, de fiabilité et de performances, lorsqu’on le maintient dans certaines limites. Il m’arrive de le préconiser en alternative aux SGBDR des principaux éditeurs lorsque ces derniers veulent ne pas payer de licence. Je leur fait connaître les limites de l’outil et leur indique que l’administration risque d’être un peu plus couteuse, notamment par rapport à SQL Server.

Il convient donc de cantonner PostGreSQL a ce qu’il sait bien faire. C’est à dire comme tout bon outil, lui faire faire ce pourquoi il est fait.
En particulier, les limitations absolues que je considère actuellement à l’utilisation de PostGreSQL sont les suivantes :

  • Une centaine d’utilisateurs concurrents (il peut y en avoir bien plus de connecté, mais évitions qu’ils lancent touts au même moment leur requête !)
  • Des bases limitées à quelques centaines de Go (pour ma part je ne conseille pas d’aller au delà de 250 Go toutes bases confondues sur un même serveur). Par exemple en l’absence d’un mécanisme de sauvegarde performant le temps de la sauvegarde peu devenir un réel handicap.
  • Des requêtes limitées à quelques tables en jointures ou sous requêtes.
  • Une complexité transactionnelle limitée (découper au maximum par les fonctions PG)
  • Pas de décisionnel. PostGreSQL ne propose pas de technique de stockage spécifique pour gérer des bases de données OLAP comme le non stockage du NULL, la compression des données, le précalcul d’agrégat, le stockage des résultats de requête en cache…

En matière de requête portant sur un fort volume de données, je me suis amusé à montré ce que pouvait impliquer en terme de performance les manques fonctionnels des SGBDR comme PostGreSQL et MySQL. Dans cet article « Agrégation d’intervalles en SQL : en pratique » on constate que PostGreSQL est à terme 10 fois moins rapide que SQL Server…

15 – En conclusion

Vous comprendrez que présenter PostGreSQL comme une alternative crédible à Oracle ou même SQL Server sur des bases à forte volumétrie, important trafic ou grande concurrence et est un phantasme de nerd mais est loin d’être une réalité !
Soyons modeste pour ne pas avoir de couteuses déconvenues et évitons les dogmes.
Je sais qu’en rédigeant un tel article je vais m’attirer les foudres de nombreux internautes. Je m’y attend et reste serein. J’ai des arguments et une expérience acquise depuis plus de 20 ans par mon travail avec de nombreux SGBDR…
Mais je sais hélas que, chez certains, la passion du libre l’emporte souvent contre la raison du technique !

NOTE :

* Une réplication de données consiste à prendre certaines données de certaines tables et placer ces informations dans une autre base. Certains confondent réplication de données et duplication d’une base. Un mécanisme comme la « réplication streaming » n’est pas à proprement parler une réplication, mais la duplication d’une base, c’est à dire un mécanisme de haute disponibilité. C’est l’équivalent par exemple du mirroring asynchrone dans SQL Server.

RÉFÉRENCES :

Livre cité

[1] A lire dans « Readings in Database Systems » de Joseph M. Hellerstein et Michael Stonebraker

NON, PostGreSQL n’est pas un clone d’Oracle !

Database preferences and product selection methodolgy
« Oracle vs. Postgres (PostgreSQL) »

Migration

Migration de Oracle vers PostGreSQL
Guide de migration vers PostGreSQL

Manques et problèmes de PostGreSQL

Top 10 Missing PostgreSQL Features
Top Missing PostgreSQL Features
PostgreSQL Gotchas (attention, cela date un peu)
Une discussion animée sur l’absence des tags : Why PostgreSQL doesn’t have query hints

Modules externes compensatoires :
contribution pg_stat_statements pour surveiller les statistiques d’exécution de tous les ordres SQL exécutés par un serveur.
contribution pgcrypto, propose des fonctions de cryptographie
module pg_statsinfos (sté NTT OSS) rapports statistiques d’activité du serveur

Fonctionnement interne de PostgreSQL
Postgres Internals Presentations (Bruce Momjian)
A Tour of PostgreSQL Internals (Tom Lane)
Inside the PostgreSQL Shared Buffer Cache (Greg Smith)

Performances PostGreSQL vs SQL Server

Même chez Enterprise DB qui fournit un PostGreSQL Amélioré on est conscient des manques de postGreSQL qui font la différence. Question de crédibilité !
PostgreSQL Performance vs. Microsoft SQL Server
Un des rares benchmark sérieux : Open Source PostgreSQL Trails Oracle In Benchmark, But Not By Much

Une exemple de comparatif inepte :

Oracle vs PostgreSQL
A la lecture de cet édifiant tissus de mensonges et d’âneries on se demande encore pourquoi tant d’entreprise et de collectivités continuent d’utiliser Oracle…

Un exemple de comparatif technique très documenté :

PostgreSQL vs Oracle

Plans de requêtes et tags (hints en anglais)

Why the F&#% Doesn’t Postgres Have Hints?!?!
Le point de vu d’un contributeur PG

DISCUSSIONS…

Mon article ayant fait polémique, les internautes ont postés un peu partout. Voici quelques un des liens pour suivre les conversations animées en cours…
Forum PostGreSQL de developpez.net
Forum francophone de PostGreSQL.fr

Un exemple de la problématique de non existence des sauvegardes différentielles (liste pgsql-general@postgresql.org)
Are file system level differential/incremental backups possible?

****************************
POUR SUIVRE LA DISCUSSION…
****************************

N’hésitez pas à lire les commentaires et en postez vous même à l’entrée figurant dans ce lien….


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

11 réflexions au sujet de « Migration Oracle PostGreSQL : Les 13 grandes lacunes qui peuvent s’avérer cauchemardesque ! »

  1. Avatar de HAL-1HAL-1

    Voici un autre point de vue sur le sujet.
    Je suis DBA Oracle et PostgreSQL dans un grand compte et je fais entre autres des migration de Oracle vers PostgreSQL :

    1 – Aucune gestion des espaces de stockage.
    L’utilisation des file systems permet de n’avoir que les files systems a surveiller pour le stockage plutôt que les tablespaces + les files systems. C’est plutôt agréable. Quand au placement sur les disques cela fait plus de 10 ans que je ne le fais plus puisque l’on a plus que des volumes plus ou moins virtuels.

    2 – Une gestion des transactions « curieuse ».
    Une fonction doit s’inscrire dans une transaction, c’est un chois d’implémentation. Il semblerait que les transactions autonomes arrivent avec la version 10 en 2017.

    3 – Un partitionnement plus que léger
    Oui, c’était l’utilisation de l’héritage ( qui n’existe à ma connaissance que dans PostgreSQL )
    un partitionnement est en cours d’implémentation et devrait arriver avec la version 10 en 2017.

    4 – Pas de tag (hint) pour forcer les plans de requête
    L’exemple porte sur la capacité de faire un index only scan pour ne lire que l’index plutôt que la table. Cette fonctionnalité à été ajouté en 9.2 https://wiki.postgresql.org/wiki/Index-only_scans
    Le fait de ne pas utiliser de hint est un choix, pas une lacune.
    Le choix est que dans le cas ou un plan d’exécution est mal choisi par l’optimiseur en fonction des statistiques, alors il faut corriger l’optimiseur. Si vous avez un cas, soumettez le à la bonne mailing list pour que l’optimiseur soit amélioré.

    5 – un optimiseur très limité
    Que dire des problèmes de binds picking d’oracle ou le plan d’exécution est décidé d’après les valeur du premier a passer la requête.
    Pour les grands nombre de jointure, PostgreSQL a aussi un optimiseur génétique.
    L’optimiseur se base sur les statistiques et souvent le problème est que les statistiques ne reflètent pas les données et qu’il faut les améliorer pour obtenir un meilleur plan, c’est juste logique.

    6 – Une indexation à la traine
    De ce coté la, il vaut mieux e pas utiliser les hash, mais il y a les btree, gin, gist, sp_gist, brin, bloom.
    Oracle n’en a pas autant et sql server non plus.
    Ce n’est pas PostgreSQL qui est a la traine.

    7 – Pas de parallélisme des requêtes
    C’est arrivé avec la version 9.6 et cela va continuer à s’améliorer.

    8 – Une administration couteuse
    Plutôt moins couteuse, en tout cas par rapport à Oracle que je connais bien. cloud control est gratuit … mais pas les packs :-(
    Et il faut d’abord se payer les licences Oracle.
    Il existe bien d’autres outils listés sur le site de PostgreSQL https://www.postgresql.org/download/products/1-administrationdevelopment-tools/

    9 – pas de pooling des connexions
    Je conseille plutôt pgbouncer que Pg Pool II pour le pooling de connexions.
    Des solutions existent …. ce n’est pas un problème.

    10 – Pas de vision d’un plan en cours d’utilisation
    Le fait de gérer un cache de plan a un coût, les plans peuvent être listés avec l’extension auto explain.
    Le fait de cacher et réutiliser les plans pose des problèmes de bind picking chez Oracle.

    11 – Un processus de sauvegarde peu performant
    barman et pgbackrest permettent de faire des backups full incrémentaux et performants.

    12 – Pas de compression ni de cryptage des données
    En fait si lorsque qu’il y a le mécanisme du TOAST,il y a compression de certaines données.
    Les stratégies sont différentes entre les SGBD/R … Les données peuvent être posées sur du LVM chiffré.
    C’est le point qui sera à suivre dans les versions à venir, il y a des discussions sur le sujet.

    13 – Pas de réplication des données
    Oh que si, synchrone, asynchrone et mixte.
    Je trouve les mode mixte très intéressant, il permet de taguer les transactions qui doivent être répliquées en ode sychrone alors que le reste est asynchrone, ce qui permet de n’être impacté que pour ce qui est important. Bref, le beurre et l’argent du beurre …
    il y a aussi pg_logical et j’attends avec impatience BDR sur la version communautaire

    14 & 15 .. Conclusion
    Je ne parlerais pas de SQL Server, ne le connaissant pas.
    Il y a des domaines ou PostgreSQL est en avance par rapport à Oracle, par exemple pour le choix des index, l’extension spatiale, le type JSON et d’autres pour lequel Oracle à des avantages comme la compression, le chiffrage et une offre intégrée de cluster matériel/logiciel avec exadata …
    Pour ce qui est de la volumétrie, l’extension citus permet à PostgreSQL de faire du sharding avec réplication et il y a aussi PG-XL.

    Notre plus grosse base en production a dépassé la TeraOctet, le fait que Skype et « le bon coin » par exemple fonctionnent avec PostgreSQL rassure quand à la possibilité de gérer du volume et de la charge, ce qui n’est simple avec aucun SGBD/R.

    Les principes sont les même pour tous les SGBD, mais les implémentations sont différentes et les avantages / désavantages dépendent fortement du contexte et de l’utilisation.
    SQL Server vient de Sybase qui vient de Ingres dont PostgreSQL qui s’applelait Post-Ingres est un reboot pour éliminer des limitations.

    Un exemple édifiant, à mon avis, est que Oracle compacte les données dans ses blocs alors que PostgreSQL les aligne par rapport aux registres du processeur.
    Le même volume de données pendra (généralement puisque ce n’est pas si simple) moins de place disque avec Oracle qui donc bénéficie de cette lecture moindre que les disques pour sa performance.
    D’un autre coté, avec l’amélioration des vitesses d’accès au stockage avec les SSD et flashs et bientôt 3D Xpoint, le choix de PostgreSQL sera un avantage puisqu’il n’a pas a décompacter les données (Ce choix à déjà été fait pour de la performance).

    3D Xpoint :
    http://www.zdnet.fr/actualites/comment-3d-xpoint-d-intel-transformera-les-serveurs-et-le-stockage-39839122.htm

    1. Avatar de SQLproSQLpro Auteur de l’article

      Ce que vous appelez choix ne sont en fait que des limitations montrant bien l’incapacité de PostGreSQL à effectuer ceci ou cela. Par exemple, pour le stockage, toujours pas capable de faire des espaces de stockage « read only » et plus dramatique, la possibilité de supprimer un fichier de la base sans que jamais une erreur ne soit remontée aux clients connectés ! Autre exemple, sur les transactions, impossible de faire des transactions « partielles » ou « imbriquées »… Et votre commentaire sur l’absence de tag de requêtes est parfaitement malhonnête, car cela existe bien dans la version payante. Ainsi lorsque vous êtes piégés par l’optimiseur (qui déconne sérieusement dès que l’on dépasse les 12 jointures – limite geqo) il vous faut passer à la version payante Entreprise DB qui coûte un bras ! (seriez vous sponsorisés par ces derniers ? ;-)
      Sur l’indexation, les index GIN et GIST sont assez mauvais comparées aux autres solutions mises en œuvre dans les autres SGBDR… Et pour ne citer que SQL Server nous avons les index suivants : Btree simple, clustered et couvrant (include), columnstore simples et clustered, index spatiaux, index XML, soit 7 types d’index…
      Il est vrai que le parallélisme vient d’arriver, mais c’est tellement embryonnaire, qu’aucune des requêtes que j’ai en production chez les clients qui ont migré n’en ont encore bénéficié !
      Le coût des outils d’administration est une chose. Le coût de l’administration elle-même en est une autre. Lorsqu’il faut passer trois heures sur une requête PostGreSQL parce que l’outil n’existe pas (par exemple le diagnostic automatique d’indexation) c’est beaucoup de temps perdu et le temps c’est de l’argent…
      Pourvoir savoir quelles sont les requêtes en cours est indispensable dans certaines cas, notamment lorsque le serveur est trop chargé afin de diagnostiquer en live les causes de blocage, attentes, etc. Et de pouvoir tuer l’une des sessions afin de libérer les autres, plutôt que de devoir arrêter le serveurs et par le fait de tuer toutes les sessions !
      Le fait de faire des sauvegardes est une chose, les faire vite et bien est une autre chose. PostGreSQL est très très lent comparativement à Oracle ou SQL Server !
      TOAST ne compresse que les LOBs ce qui n’offre que peu d’intérêt. La compression des données relationnelles comme celle des index est fondamentale notamment pour la dataWarehousing !
      Vous confondez réplication de données (c’est à dire la faculté de propager certaines données de certaines lignes de certaines tables d’une base à une autre) et haute disponibilité qui est la réplication de l’intégralité de la base. Et même dans ce dernier cas PostGreSQL est assez mauvais, car par exemple il ne sait pas propager certaines données afin d’éviter des divergences de données ! L’exemple typique est l’UUID (ou GUID) ou l’horodatation. En effet au lieu de répliquer la donné, PostGreSQL réplique souvent la commande, produisant une divergence des données dans les bases, rendant fonctionnellement inexploitable les réplicas !

      Enfin, vos exemples avec Skype et Le bon coin me font doucement marrer… Il y a belle lurette que Skype a abandonné PostGreSQL avant même son rachat par Microsoft ! Quand au bon coin, il utilise plus de 100 serveurs PostGreSQL pour partager la base car elle est trop grosse pour tenir sur un seul et il faut arrêter la nuit tous les serveurs pour la maintenance ! Bref, un coût exorbitant, là ou un seul serveur Oracle ou SQL Server aurait suffit !

    1. Avatar de SQLproSQLpro Auteur de l’article

      Voici quels sont les points depuis la version 9.6 de PostGreSQL :
      point 1 à 5 toujours valables
      Point 6, il existe une solution embryonnaire de vues matérialisées
      Point 7, le parallélisme des requêtes est arrivé avec la version 9.6 il est cependant très rudimentaire.
      Il faut préciser à la main le degré de parallélisme (set max_parallel_degree) et certaines conditions empêchent un plan parallèle (requêtes d’écriture INSERT, UPDATE ou DELETE, requêtes de curseur, transaction serializable, dynamic_shared_memory_type mis à « none »…) de plus la parallélisation ne concerne que certaines étapes du plan essentiellement les « scans » de tables et limités par certaines conditions;
      La conclusion est qu’il faudra ré écrire les requêtes pour en tirer partie en indiquant, avant de lancer la requête, le degré de parallélisme souhaitable, là ou Oracle ou SQL Server le font de manière automatique sans devoir récrire les requêtes !
      Il ne concerne pas, bien entendu et hélas, les outils de maintenance tels que VACUMM ou CLUSTER ce qui est bien dommage car permettrais de restreindre les temps de traitement des tâches de maintenance
      Point 8 à 11 : toujours valables
      Point 12 : il existe un module externe de chiffrement nommé pg_crypto peu sécurisé : pas de salage automatique, clé devant être présente suer le serveur sous forme de fichier….
      Point 13 : toujours valable

  2. Avatar de sqlprosqlpro Auteur de l’article

    Ci joint les commentaires de scheu postés à l’origine dans ce message

    Quelques retours d’expérience personnels qui peuvent éventuellement compléter ton article si tu les juges utiles :

    Paragraphe 4 (hints)
    Effectivement le fait que les hints soient intégrés dans la version payante laisse à penser que jamais la version communautaire gratuite de Postgresql ne les intègrera jamais, au grand regret des DBA …
    La seule marge de manoeuvre quand un plan d’exécution est mauvais se situe au niveau des paramètres du serveur (enable_mergejoin, enable_nestloop, enable_seqscan, …) que l’on peut modifier dans le SQL, ou par login Postgresql.
    Par exemple si une requête fait un nested loop alors que c’est contre-performant comparé à un hash join, on peut faire avant la requête un « set enable_nestloop to off »
    Bien que parfois même en modifiant ces paramètres ça n’empêche pas tel ou tel type d’opération …
    Mais ça doit rester exceptionnel, car ça devient vite une usine à gaz si on modifie ces paramètres spécifiquement pour chaque requête

    == Citation SQLpro ==
    Or la philosophe particulièrement croustillante des développeurs de PG est que c’est très mal de mettre des tags dans les requêtes : « Why PostgreSQL Doesn’t Have Query Hints »
    Dans un monde parfait, cela serait absolument génial… Mais malheureusement il y a la réalité, et là force est de constater que l’optimisation des requêtes par PostGreSQL est loin d’être… optimale !
    == fin citation ==

    Tout-à-fait d’accord avec toi
    Autant sur des applications développées « maison » on peut éventuellement chercher à modifier le SQL pour optimiser (et encore, ce n’est pas toujours simple), autant quand on a à faire à un progiciel dont nous n’avons pas la main pour modifier les requêtes, c’est impossible.
    Et quand un plan d’exécution pourri arrive du jour au lendemain en production et qu’on demande au DBA de trouver une « rustine » rapide, c’est souvent mission impossible et on regrette de ne pas être sur Oracle ou SQL Server

    Paragraphe 5 (optimiseur)
    Ca arrive encore trop souvent que les plans d’exécution soient pourris sur Postgresql (même avec des stats à jour et des indexes bien placés) dès qu’on a des requêtes un peu complexes (jointure entre 6-7 tables, auto-jointures, sous-jointures, …), ou comportant trop de colonnes filtrées sur les tables

    Le principal défaut est qu’il sous-estime trop souvent les cardinalités (nombre de lignes retournées à chaque étape du plan), ce qui conduit au bout d’un moment :
    – soit à lui faire choisir des « nested loop » au lieu de « hash join » en cas de jointure
    – soit à des « index range scan » trop coûteux alors qu’un full scan de la table serait au final meilleur
    Je pense que ce problème est lié au fait que Postgresql sait très mal évaluer les nombres de lignes retournés pour des filtres qui comportent plusieurs colonnes
    Là où Oracle ou SQL Server, sur un index (col1,col2,col3), sait faire des stats dessus et savoir estimer, pour chaque valeur du triplet, le nombre de lignes dans la table, Postgresql ne sait le faire que dans de très rares cas, les histogrammes se limitant sur des colonnes uniques et pas des t-uples

    Paragraphe 10 (plans d’exécution)
    Oui, tu ne peux que les voir une fois que la requête est exécutée, et encore ce n’est pas en natif, il faut utiliser pour cela la contrib « auto_explain » qui permet de les récupérer dans la trace du serveur Postgresql, même si ce n’est pas toujours lisible facilement et qu’il faut souvent reformater le fichier à la main

    Autre (spécificité Oracle)
    Concernant la migration de Oracle vers PG (je ne connais pas suffisamment bien SQL Server pour comparer) : tu pourrais aussi éventuellement rajouter un paragraphe sur les principales différences de comportement entre Oracle et Postgresql quand on fait une migration en laissant les paramètres par défaut des 2 côtés, notamment :
    – mode « autocommit » par défaut sur Postgresql
    – gestion des NULL différentes (sous Oracle, NULL = chaîne de caractères vide, mais pas sous PG, donc différences de comportement d’une même requête)
    – conversions de type implicites qui passent sous Oracle mais pas sous PG (test des requêtes d’une appli et réécriture éventuelle à prévoir)
    – syntaxe des jointures ouvertes (+) spécifique à Oracle
    – sous PG, mot clé « AS » obligatoire pour renommer les colonnes dans une requête SQL
    – pas de synonymes ni de packages sous PG
    – type « date » de Oracle = type « timestamp » de Postgresql
    – ordre de tri différente selon la casse
    – PG sensible à la casse sur les noms d’objets

    == Citation SQLpro ==
    Vous comprendrez que présenter PostGreSQL comme une alternative crédible à Oracle ou même SQL Server sur des bases à forte volumétrie, important trafic ou grande concurrence et est un phantasme de nerd mais est loin d’être une réalité !
    Soyons modeste pour ne pas avoir de couteuses déconvenues et évitons les dogmes.
    Je sais qu’en rédigeant un tel article je vais m’attirer les foudres de nombreux internautes. Je m’y attend et reste serein. J’ai des arguments et une expérience acquise depuis plus de 20 ans par mon travail avec de nombreux SGBDR…
    Mais je sais hélas que, chez certains, la passion du libre l’emporte souvent contre la raison du technique !
    == Fin citation ==

    Je suis assez d’accord avec toi.
    Postgresql a depuis quelques versions de nombreuses fonctionnalités qui se rapprochent des besoins des entreprises (hot standby, sauvegarde à chaud en continu, fonctions analytiques, …)
    Au jour d’aujourd’hui Postgresql peut faire l’affaire pour des petites applications non critiques, à faible volumétrie (quelques dizaines de Go) et à faible complexité (pas de grosses requêtes faisant des jointures sur plus de 5-6 tables)
    Mais dès qu’on arrive dans des environnements complexes, volumineux ou critiques, Postgresql montre quand-même ses limites, et ça peut vite devenir critique quand par exemple (c’est du vécu) un problème de mauvais plan d’exécution survient en production, la requête vous mange 100% de CPU et dure 15 heures, et qu’aucune rustine rapide n’est envisageable
    C’est toujours le compromis à trouver entre budget et risque pour une entreprise …

Laisser un commentaire