Nouvel article : Migration Oracle ou SQL Server vers PosteGreSQL - les écueils

[SQLpro]

Bonjour

Bon, je crois que je crois me faire encore quelques ennemis de plus...

Voici un article détaillant les inconvénients d'une migration d'Oracle ou de SQL Server vers PostGreSQL...
http://blog.developpez.com/sqlpro/p1...esql-ou-com-2/

Au sommaire :

1 - Aucune gestion des espaces de stockage.
2 - Une gestion des transactions "curieuse".
3 - Un partitionnement plus que léger
4 - Pas de tag (hint) pour forcer les plans de requête
5 - un optimiseur très limité
6 - Une indexation à la traine
7 - Pas de parallélisme des requêtes
8 - Une administration couteuse
9 - pas de pooling des connexions
10 - Pas de vision d'un plan en cours d'utilisation
11 - Un processus de sauvegarde peu performant
12 - Pas de compression ni de cryptage des données
13 - Pas de réplication des données
14 - PostGreSQL reste un bon SGBDR réellement libre
15 - En conclusion

Merci d'avance pour vos commentaires.

A +

[scheu]

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

Envoyé par 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 !

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
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

Envoyé par 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 !

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 ...

[SQLpro]

Intéressant, cela confirme mes modestes expériences et celles que m'ont racontés de nombreux DBA qui ont du migrer...

Puis-je poster ton texte dans le blog, mais un peu différemment ?

Je vais créer deux entrées :
1 retour d'expérience tel quel
Ton paragraphe "autre" spécifiqaue à Oracle

A +

[MaitrePylos]

ne payons plus de licences et migrons que diable !

Ceci est le nerf de la guerre, encore faut-il que le DBA Oracle, puisse gérer PostgreSQL.

Perso, je trouve que la politique de prix de Oracle n'est pas cohérente, faites venir deux commerciaux différents et vos prix seront différents.

la passion du libre l'emporte souvent contre la raison du technique !

C'est vrai....dans l'enseignement....autour du café.....sur les forums, beaucoup moins en entreprise.

Je ne suis pas contre de payer pour un produit de qualité et qui répond à mes besoins, mes besoins sont mieux rencontrés par PostgreSQL-PostGis que par Oracle-Spatial (le prix n'est pas innocent).

J'ai également vu l'inverse, un déploiement Oracle pour un schéma de quatre tables et 1247 records au total et refus total de migrer vers Oracle Express.

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.

Ceci est la phrase qui faut retenir, quelque soit le SGBDR.

Peux-tu faire le même article avec Firebird ?


MaitrePylos

[scheu]

Envoyé par SQLpro

Intéressant, cela confirme mes modestes expériences et celles que m'ont racontés de nombreux DBA qui ont du migrer...

Puis-je poster ton texte dans le blog, mais un peu différemment ?

Je vais créer deux entrées :
1 retour d'expérience tel quel
Ton paragraphe "autre" spécifiqaue à Oracle

A +

No problemo

[SQLpro]

Envoyé par MaitrePylos

Je ne suis pas contre de payer pour un produit de qualité et qui répond à mes besoins, mes besoins sont mieux rencontrés par PostgreSQL-PostGis que par Oracle-Spatial (le prix n'est pas innocent).

Tu n'as pas essayé SQL Server 2008 ? J'ai publié dans cet article un comparatif des solution GIS de PG et SQL Server. Il sont très comparable à 3 exceptions près :
la fonction MakeValid qui n'existe pas dans PG
les SRID qui ne sont pas gérés sous PG
l'agrégat d'objet spatiaux qui n'existe pas sous SQL Server (faut faire des requêtes récursives.
SQL Server 2012 va intégrer les agrégats spatiaux et les courbes.
Et en plus c'est gratuit le spatial, car intégré dans toutes les éditions, même Express !

Envoyé par MaitrePylos

Peux-tu faire le même article avec Firebird ?

Hélas non, car cela fait des années que je n'ai plus donné dans IB/FB... Et ce serait plus pitoyable encore...

A +

[estofilo]

Il y a de nombreuses erreurs montrant que tu ne connais que superficiellement le sujet.
C'est un article à charge, rien qu'au sommaire on sait déjà à quoi s'en tenir.
La mauvaise foi déployée est stupéfiante pour arriver à prétendre que "PostGreSQL ne dispose d'aucun mécanisme intégré de réplication des données".

[punkoff]

Envoyé par estofilo

Il y a de nombreuses erreurs montrant que tu ne connais que superficiellement le sujet.

Pourriez-vous pointer les erreurs, ommissions, ou autre ?

[SQLpro]

Envoyé par estofilo

Il y a de nombreuses erreurs montrant que tu ne connais que superficiellement le sujet.
C'est un article à charge, rien qu'au sommaire on sait déjà à quoi s'en tenir.
La mauvaise foi déployée est stupéfiante pour arriver à prétendre que "PostGreSQL ne dispose d'aucun mécanisme intégré de réplication des données".

Tu commet la même erreur que beaucoup en confondant réplication de données et réplication de base (ce qui d'ailleurs n'est pas une réplication, mais une duplication, soyons précis ! _lis bien la note en bas...). A part Slony I et Londiste (deux modules à part et basés sur des triggers...) qui permet de faire de la réplication de données, c'est pauvre et peu performant...

D'autre part, soit précis dans tes arguments. Tu dis que c'est farci d'erreur, mais tu ne m'en cite qu'une (la réplication) et encore ce n'est pas une erreur, c'est une incompréhension de ta part. L'honnêteté voudrais que tu me sortes quelques arguments techniques, des affirmations tenus sur des sites crédibles, des exemples de code... Mais sans aucun autre argument que d'affirmer que ce je dis dans cet article est un tissus de mensonges...
Je pensais que tu étais pragmatique et j'ai l'impression de découvrir un partisan aveugle.... dommage.

[estofilo]

Envoyé par SQLpro

Tu commet la même erreur que beaucoup en confondant réplication de données et réplication de base (ce qui d'ailleurs n'est pas une réplication, mais une duplication, soyons précis ! _lis bien la note en bas...).

Il n'y a pas de confusion, le terme de réplication est utilisé par le reste du monde pour désigner à la fois la réplication de table et la réplication de base, et la réplication de toutes sortes d'autres objets informatiques d'ailleurs.
Pour la définition, celle de wikipedia me convient très bien.

Donc quand tu écris "Pas de réplication des données *" l'étoile ramenant à "ah au fait j'ai ma propre interprétation de qu'on appelle réplication des données qui fait que je peux me permettre d'écrire ça", libre à chacun d'apprécier l'honnêteté du procédé.

[estofilo]

Voici ce que j'appelerais les ratés de l'article pré-cité:

- "gestion des espaces de stockage"
aucune mention des fichiers WAL qui justement sont l'espace contigu d'écriture qui est si important. Aucune mention du fait qu'une table ou un index ou les WAL peuvent être placés sur n'importe quelle partition et des disques différents.

- "une fonction SQL peut encapsuler n'importe quel code SQL du DML".
Comment ça du DML? Les fonctions exécutent aussi du DDL et ce DDL est transactionné. C'est une différence essentielle par rapport aux autres SGBDs.

- "une fonction = une transaction".
Où est le sens de cette affirmation? L'idée c'est qu'une fonction doit impérativement être lancée dans le cadre d'une requête SQL, et pas en-dehors contrairement aux procédures stockées. Du coup une fonction ne peut pas faire un commit ou un rollback, parce qu'on ne fait pas de commit en plein milieu d'une requête. C'est cohérent dans l'architecture. Par contre une fonction peut elle-même lancer des requêtes en tout genre.

- "une erreur annule automatiquement une transaction".
Seulement dans certaines circonstances. Dans une fonction il suffit d'un BEGIN.. END pour attraper les erreurs et les gérer sans rien annuler, exemple dans la doc avec le INSERT or UPDATE gérant la violation de clef primaire. Dans du SQL hors fonction on utilise des SAVEPOINT si on veut se prévenir d'une annulation totale de la transaction en cours pour une instruction qui part en erreur. C'est seulement si on fait rien de tout ça qu'une erreur termine sans appel la transaction où elle se produit.

- l'exemple prétendument "calamiteux" de l'optimiseur qui serait supposé utiliser l'index est fait sur une requête ou l'index n'a pas d'intérêt puisque toutes les lignes de table sont renvoyées. Ca n'est pas un problème de choix de plan et s'il y avait des "hints" ça n'y changerait rien. la raison est que postgres ne sait pas faire des lectures "index seulement", ce sera d'ailleurs faisable dans la prochaine version majeure cette partie étant déjà développée.

- "Les seuls paramétrages de PostGreSQL le sont au niveau du serveur"
Non puisqu'une session peut à tout moment forcer pour elle seule tous les paramètres réglant l'optimiseur et notamment le forçage d'index ou les stratégies pour les jointures ou les tris.

- "VACUUM, bonne à tout faire"
la recommandation c'est de ne pas faire de vacuum manuel et de laisser faire auto-vacuum. Le DBA qui lancerait des vacuum manuels à tout bout de champ, il faut simplement qu'il s'abstienne de faire ça.

- "pas de pooling des connexions. Ah si en fait mais pas intégré."
Certes, et alors? L'intégration ne présente aucun avantage particulier.

- "pas de possibilité de paralléliser sa sauvegarde..."
la sauvegarde filesystem à chaud est une bête copie de fichiers, rien n'empêche de copier des répertoires en parallèle avec les outils classiques tar, rsync et compagnie.
Quant à l'export pg_dump il n'y a pas d'option pour le paralléliser contrairement à pg_restore mais il peut être lancé simultanément sur des objets ou bases différentes.

- "pas de sauvegarde différentielle"
faux aussi, certains font des rsync à chaud avec une sauvegarde précédente et d'autres sauvent les fichiers de WAL=journaux de transaction qui sont des différentiels par définition.

[SQLpro]

Je dois sans doute est un idiot et ne pas savoir le français, mais il me semble qu'il y a une différence fondamentale entre les termes :
Réplication de données
et
Réplication de bases de données

Le problème est que souvent les gens qui viennent du monde "libre" ne connaissant que très partiellement les bases de données, l’apprennent par des outils qui sont loin d'être complet et pense que la vérité s’appelle MySQL ou PostGreSQL... On n'apprends pas l'informatique en lisant les notices des logiciels, ni les blogs de nerds.
PostGreSQL étant très limité en matière de réplication de données (solutions médiocres en terme de performances car basé sur des triggers), je comprends que certains aient des difficultés à saisir la nuance....

Je vous donne la définition de Gardarin sur le sujet de la réplication :

Réplica ou copie de données :
Fragment horizontal ou vertical d’une table stockée dans une base de données qui est copiée et transféré vers une autre base de données

Sans doute Gardarin est un obscur scribouillon...

Prenons un cours de Telecom Paris...
Je cite :

Définition des objets répliqués
table cible = sous-ensemble horizontal et/ou vertical d'une ou p tables

http://www.infres.enst.fr/~talel/cours/inf345/bdr1.pdf

Mais sans doute l'ENST est une obscure école anti logiciel libre...

Allons dans un pôle de recherches prendre une défintion de la réplication :
3 Réeplication
On veut limiter l'utilisation du réeseau. Une idéee est de travailler sur une
copie locale d'une table distante....
http://www.irccyn.ec-nantes.fr/~pauleve/coursbdd4.pdf

Mais peut être l'Institut de Recherche en Communications et Cybernétique de Nantes est-il une association de rigolards...


A +

[SQLpro]

Ha enfin du concret... Merci !

Envoyé par estofilo

Voici ce que j'appelerais les ratés de l'article pré-cité:

- "gestion des espaces de stockage"
aucune mention des fichiers WAL qui justement sont l'espace contigu d'écriture qui est si important. Aucune mention du fait qu'une table ou un index ou les WAL peuvent être placés sur n'importe quelle partition et des disques différents.

N'importe quel SGBDR, sait faire cela ! Mais ce n'est pas de cela que je parle? Relisez bien...
La notion de tablespace PG ne fait qu'indiquer un répertoire. Rien d'autre !
Voici la syntaxe PG (v9.1.1):

Code :

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...
Indiquez moi comment vous ferez le jour ou votre tablespace placé sur un disque est saturé ?.

- "une fonction SQL peut encapsuler n'importe quel code SQL du DML".
Comment ça du DML? Les fonctions exécutent aussi du DDL et ce DDL est transactionné. C'est une différence essentielle par rapport aux autres SGBDs.

Désolé, mais Sybase et SQL Server savent transactionner cela depuis l'origine. C'est d’ailleurs ce que la norme SQL exige. Il n'y a que Oracle à ne pas le faire. De là à dire que c'est une différence essentielle....

- "une fonction = une transaction".
Où est le sens de cette affirmation? L'idée c'est qu'une fonction doit impérativement être lancée dans le cadre d'une requête SQL, et pas en-dehors contrairement aux procédures stockées. Du coup une fonction ne peut pas faire un commit ou un rollback, parce qu'on ne fait pas de commit en plein milieu d'une requête. C'est cohérent dans l'architecture. Par contre une fonction peut elle-même lancer des requêtes en tout genre.

Ce qui n'est pas conforme à la norme SQL pour lequel ce qui est procédure doit pouvoir incorporer la gestion des transactions. Mais on pourrait d'ailleurs le dire différemment : PostGreSQL ne sait pas faire de procédures stockées. En effet la norme SQL précise que la fonction ou UDF ne peut pas être transactionnées, car elle doit être appelée dans une requête à linverse, la procédure doit pouvoir incorporer les ordres BEGIN WORK/TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL....
Autrement dit vos propos me confirme ce que j'affirme depuis très longtemps : PostGreSQL ne sait pas faire de procédures stockées !

- "une erreur annule automatiquement une transaction".
Seulement dans certaines circonstances. Dans une fonction il suffit d'un BEGIN.. END pour attraper les erreurs et les gérer sans rien annuler, exemple dans la doc avec le INSERT or UPDATE gérant la violation de clef primaire. Dans du SQL hors fonction on utilise des SAVEPOINT si on veut se prévenir d'une annulation totale de la transaction en cours pour une instruction qui part en erreur. C'est seulement si on fait rien de tout ça qu'une erreur termine sans appel la transaction où elle se produit.

Hors fonction, c'est donc pas dans la fonction. tant que je peut pas faire de COMMIT ou ROLLBACK l'intérêt des routines SQL de PG est très limité !

- l'exemple prétendument "calamiteux" de l'optimiseur qui serait supposé utiliser l'index est fait sur une requête ou l'index n'a pas d'intérêt puisque toutes les lignes de table sont renvoyées. Ca n'est pas un problème de choix de plan et s'il y avait des "hints" ça n'y changerait rien. la raison est que postgres ne sait pas faire des lectures "index seulement", ce sera d'ailleurs faisable dans la prochaine version majeure cette partie étant déjà développée.

Et bien si ça change beaucoup de chose, relisez l'exemple que je donne, car lire des lignes de 8 Ko dans une table à la place de lire des données de 4 octets dans un index ça fait quand même une différence de 7996 octets par lignes... Une toute petite paille !

- "Les seuls paramétrages de PostGreSQL le sont au niveau du serveur"
Non puisqu'une session peut à tout moment forcer pour elle seule tous les paramètres réglant l'optimiseur et notamment le forçage d'index ou les stratégies pour les jointures ou les tris.

Au niveau session, OK, mais c’est loin d'être l'équivalent des hints que l'on trouve sous Oracle, Sybase, SQL Server ou IBM DB2 ou l'on peut régler chaque requête indépendamment, voir en fonction de l'utilisateur (exemple le resources Governor de SQL Server)

- "VACUUM, bonne à tout faire"
la recommandation c'est de ne pas faire de vacuum manuel et de laisser faire auto-vacuum. Le DBA qui lancerait des vacuum manuels à tout bout de champ, il faut simplement qu'il s'abstienne de faire ça.

Pour vous donner une idée de la différence, sachez qu'il y a pas loin de 30 commandes et techniques différentes pour auditer la fragmentation, vérifier l'intégrité des pages, défragmenter ou reconstruire les index, libérer les espaces morts, voire réduire les fichiers, y compris en mode ONLINE dans SQL Server (donc sans perte de l'index en cours de reconstruction pour les utilisateurs etr sans verrou).
Dans PG VACUUM fait tout d'un coup et pour les grosses bases, c'est très pénalisant... Alors que dans Oracle ou SQL Server on peut le faire en différentes phases à différentes planification.
Par exemple vérifier l'intégrité des données une fois par semaine et défragmenter certaines tables/index tous les jours et récupérez de la place qu'une fois par mois...
Ceci afin d'éviter des processus très consommateurs de temps qui nuisent aux grosses bases.
Pour ce qui est de l'"auto" du vacuum... il existe un mécanisme similaire dans SQL Server pour recalculer les statistiques qui agit en tâche de fond en mode synchrone ou asynchrone. Enfin, toutes les tâches de DBA peuvent être planifiées "On Idle", c'est à dire quand le processeur passe en dessous d'un certains seuil de charge au moins un certain temps...
Ceci existe depuis la version 7 de SQL Server datant de 1998.

- "pas de possibilité de paralléliser sa sauvegarde..."
la sauvegarde filesystem à chaud est une bête copie de fichiers, rien n'empêche de copier des répertoires en parallèle avec les outils classiques tar, rsync et compagnie.
Quant à l'export pg_dump il n'y a pas d'option pour le paralléliser contrairement à pg_restore mais il peut être lancé simultanément sur des objets ou bases différentes.

La copie de fichier n'a rien à voir avec une sauvegarde digne de ce nom puisqu'il est impossible de copier des fichiers à chaud alors que la base travaille.... En principe point n'est besoin d'arrêter une base ou un serveur pour effectuer une sauvegarde.
Rien à voir avec une lecture en // des données dans la base et une écriture en // sur différents supports, vu que PG ne sait pas faire des lectures // de ses tables.

- "pas de sauvegarde différentielle"
faux aussi, certains font des rsync à chaud avec une sauvegarde précédente et d'autres sauvent les fichiers de WAL=journaux de transaction qui sont des différentiels par définition.

ça ce sont des sauvegardes du journal de transaction. Rien à voir avec des sauvegardes différentielles. Les sauvegardes différentielles sauvegardent les pages de données qui ont été modifiées depuis un certain temps (une sauvegarde précédente).
Lisez par exemple ceci pour comprendre les différences : http://fr.wikipedia.org/wiki/Sauvegarde

Sachez que j'ai été content pour une fois d'avoir un détracteur avec un peu de matière. ça change des rigolo qui affirment votre article est nul, c'est tout faux, sans aucun argument.

Je retiens le fait du paramétrage de session que j'ai effectivement omis et vais rectifier mon papier.

A +

[frost242]

Bonjour Frédéric,

Je reviens ici, car le climat sera plus calme pour discuter.

Envoyé par SQLpro

Et bien si ça change beaucoup de chose, relisez l'exemple que je donne, car lire des lignes de 8 Ko dans une table à la place de lire des données de 4 octets dans un index ça fait quand même une différence de 7996 octets par lignes... Une toute petite paille !

Voici la nouvelle fonctionnalité dont estofilo parle, et qui arrivera pour la 9.2: index-only-scans.
C'était clairement un manque cruel de Postgres, on est d'accord. C'est dommage toutefois d'appuyer cet argument avec cet exemple précis, car il est connu que Postgres ne sait pas utiliser uniquement une lecture d'index pour lire des données (d'où un fullscan pour un SELECT count(*) sur une table).
Pour ma part, j'aurai aimé que vous donniez un exemple plus "pertinent". Pertinent dans le sens où vous mettez vraiment en défaut l'optimiseur de Postgres sur un cas qu'il est censé pouvoir traiter correctement.

Concernant la gestion de l'espace de stockage, je ne vais pas revenir sur l'ordre CREATE TABLESPACE et tout le toutim. Néanmoins, comme évoqué ailleurs, Postgres vous laisse la gestion du stockage au niveau OS.
Une chose me gène néanmoins dans le premier point de votre article (j'essaye d'en avoir une lecture neutre et pas partisane, je précise). Un tablespace va donc bien pointer un répertoire et à vous de vous démerder pour le reste. Mais ce tablespace pointe en général vers un point de montage sur Linux/Unix (désolé, Windows n'est pas ma tasse de thé), et en général, sur un disque physique différent. De cette façon, vous ventilez les I/O sur différents axes.
Concernant la gestion des écritures, Postgres va laisser à l'OS la charge de regrouper les pages contigües. On peut toutefois s'attendre à des pics d'écriture à l'issue de chaque CHECKPOINT. Ce n'est pas forcément la panacée, mais la complexité du code dédié aux écritures dans le SGBD diminue significativement.
Du coup, en vous lisant, j'ai l'impression que Postgres est en dessous de tout sur ce point de vue là. Pour moi, les choses ne sont pas si noires que le ressenti que j'ai à la lecture de votre article.

Pour terminer, j'aimerai juste ajouter une précision par rapport au point 8: VACUUM ne pose aucun verrou sur la table. VACUUM FULL oui. Cf la doc de l'ordre VACUUM. Mais effectivement, je ne vous contredirai pas si vous dites que VACUUM peut être particulièrement pénalisant en terme d'entrées/sorties disque.

[MaitrePylos]

Pour sa défense, Estofilo est tout sauf un détracteur, mais bien tout comme toi, un de nos spécialistes DBA, avec une expertise PostgreSQL.

Je parlais déteacteur de mon article !

Par contre je suis dubitatif sur les avantages Oracle suivants:

Il ne s'agit pas d'avantages, bien au contraire, mais de vieilleries Oracle...
De plus, si vous aviez bien lu, ce n'est pas moi qui ait posté cela... !
C'est désagréable de voir que tout le monde s'en prend à moi.. Relisez l'article :
Ci joint les commentaires de scheu postés à l'origine dans ce message

- 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)

La norme a mal définit ce point particulier hélas.

- sous PG, mot clé "AS" obligatoire pour renommer les colonnes dans une requête SQL

NON, vous vous trompez, la norme l'indique comme facultatif aussi bien pour un nom de colonne que pour un alias de table.

- PG sensible à la casse sur les noms d'objets
Cela est dû à son historique Unix, d'ailleurs par défaut il met tout en minuscule.

Historique ou pas, c'est pas conforme à la norme. SQL Server non plus qui est tantôt conforme, tantôt pas dépendant de la collation d'installation du serveur.

Pour information, je rédige depuis des mois un article sur ces petites choses qui rendent incompatibles des requêtes ultra simples.

A +

[scheu]

En restant objectif sans prendre parti dans ce débat, je tiens à préciser quand-même, de part ma modeste expérience, que, concernant les points cités précédemment :

- le forçage des paramètres niveau session pour combler l'absence de hints est une usine à gaz. D'autant que dans une même session, d'une requête à une autre, les paramètres doivent être changés en cours de route pour s'adapter à chaque requête.
Cela revient à dire au développeur qu'avant chaque requête SQL il doit potentiellement modifier 10 paramètres Postgresql à chaque fois (enable_mergejoin, enable_nestloop, enable_seqscan, ...). Bref bon courage ... !

- l'autovacuum de PG est, tout comme le job de calcul automatique des stats sous Oracle ou SQL Server, inutile dans de nombreux cas.
En effet, dans un traitement ETL par exemple, qui charge plusieurs tables temporaires avant d'alimenter une table cible, c'est au traitement à lui-même calculer les stats sur les tables temporaires une fois alimentées, sinon les plans d'exécutions seront souvent mauvais car le traitement n'aura pas attendu l'éventuel déclenchement de l'autovacuum en cours de route pour continuer.

- le VACUUM full est une calamité, déjà très long, il pose en plus un verrou sur la table. Quand je veux défragmenter une table, je sais que j'aurais plus vite fait de faire un export/import, avec toutes les contraintes qui s'en suivent si la table comporte des triggers, des FK dépendantes, ...)

[scheu]

Envoyé par MaitrePylos

Pour sa défense, Estofilo est tout sauf un détracteur, mais bien tout comme toi, un de nos spécialistes DBA, avec une expertise PostgreSQL.

Par contre je suis dubitatif sur les avantages Oracle suivants:



Or si je lis bien ton cours "Le null n'est ni la chaîne vide, ni le zéro"



Donc :

Code :

Cela me rappelle MySQL, c'est comme demander d'additionner 3 oranges + 2 pommes = Impossible.

PostgreSQL respecte la norme SQL à ce sujet


Respect de la norme


Cela est dû à son historique Unix, d'ailleurs par défaut il met tout en minuscule.

C'est moi qui ait soulevé ces points dans la première réponse du message d'origine de SQLPro
Cette partie concerne les principales difficultés de migration de Oracle vers Postgresql (de part mon expérience personnelle), mais ne constituent en rien un avantage d'Oracle ou un inconvénient de Postgresql.
Au contraire pour la plupart ces incompatibilités sont dues au fait que Postgresql respecte beaucoup plus la norme SQL que Oracle
C'est plutôt une mise en garde pour les gens voulant faire une migration

[MaitrePylos]

Envoyé par scheu

Au contraire pour la plupart ces incompatibilités sont dues au fait que Postgresql respecte beaucoup plus la norme SQL que Oracle

Nous sommes d'accord

[frost242]

Envoyé par scheu

- le VACUUM full est une calamité, déjà très long, il pose en plus un verrou sur la table. Quand je veux défragmenter une table, je sais que j'aurais plus vite fait de faire un export/import, avec toutes les contraintes qui s'en suivent si la table comporte des triggers, des FK dépendantes, ...)

C'est pour cela qu'il est vivement recommandé de ne pas utiliser VACUUM FULL. Et en plus il pourrie les index au passage, qu'il faudra donc reconstruire par la suite.

En revanche, VACUUM est vivement recommandé pour pouvoir réutiliser l'espace "mort" - l'espace laissé par des lignes mortes.

[SQLpro]

Envoyé par frost242

...
Concernant la gestion de l'espace de stockage, je ne vais pas revenir sur l'ordre CREATE TABLESPACE et tout le toutim. Néanmoins, comme évoqué ailleurs, Postgres vous laisse la gestion du stockage au niveau OS.

Mais ça ne veut rien dire. Tous les SGBDR aussi reposent sur un OS et peuvent utiliser un SAN du RAID et tout le toutim exactement dans les mêmes conditions que PG le fait. Mais EN PLUS ils permettent de gérer administrativement et techniquement les espaces de stockage.
Donc dans PG, il n'y a aucune gestion intégrée des espaces de stockage propre aux bases du SGBDR !

Envoyé par frost242

Une chose me gène néanmoins dans le premier point de votre article (j'essaye d'en avoir une lecture neutre et pas partisane, je précise). Un tablespace va donc bien pointer un répertoire et à vous de vous démerder pour le reste. Mais ce tablespace pointe en général vers un point de montage sur Linux/Unix (désolé, Windows n'est pas ma tasse de thé), et en général, sur un disque physique différent. De cette façon, vous ventilez les I/O sur différents axes.

D'un point de vu système ce que vous dites est vrai. Mais c'est vrai aussi sur n'importe quel OS, Windows compris...
MAIS, cela n'apporte pas grand chose pour le SGBDR.
Je vous donne un seul exemple (c'est d'ailleurs lié au parallélisme des requêtes que PG ne sait pas gérer)...

Imaginez que les données d'une grosse table sur lequel on doit faire une requête de type SUM ne soit pas dans le cache. Imaginez maintenant que l'on ait créé deux espaces de stockage sur deux disques physiques différents (ou agrégats RAID, ou LUN d'un SAN taillé sur une alignement de disques physiques). Comme le SGBDR sait que sa table est répartie sur les deux espaces de stockage son plan de requête va donc être parallélisé pour diviser par deux le temps de réponse global. Un thread va faire la somme des lignes remonté par un disque tandis qu'un autre thread va faire la somme des lignes de l'autre disque. Au final il reste une opération, faire la somme des sommes partielles. En temps CPU on aura perdu car le parallélisme coute, mais en temps réel d'exécution on est pas loin de diviser par deux...
Et cela est impossible si c'est effectué par l'OS, car l'OS ne sait pas comment la table est répartie, de même pour un SAN, car le SGBDR ne sait pas que le SAN parallélise. C'est pour cela qu'il existe un OS interne au SGBDR et des routines spéciales de bas niveau en pour les lectures/écritures que le SGBDR effectue. dans SQL Server ou Oracle.

En fait tout est lié : l'absence de // est lié à l'absence de gestion des espaces de stockage.
Mais il y a d'autres avantage considérable à la gestion des espaces de stockage, comme la réservation physique de l'espace qui évite toute fragmentation physique et permet de créer le fichier sur le meilleur endroit du disque (les cylindres externes).

Envoyé par frost242

Concernant la gestion des écritures, Postgres va laisser à l'OS la charge de regrouper les pages contigües. On peut toutefois s'attendre à des pics d'écriture à l'issue de chaque CHECKPOINT. Ce n'est pas forcément la panacée, mais la complexité du code dédié aux écritures dans le SGBD diminue significativement.

La chance et la malchance veut que PG ait organisé son système de stockage par de multiples fichiers (en gros une table est un fichier).
L'OS peut donc savoir quels fichiers vont être impacté par une mise à jour des données. Il me semble qu'il lui est en revanche impossible de savoir quelle page dans le fichier doit être impactée.

Il est d'ailleurs très amusant de voir que c'est Stonebraker qui a mis en évidence l'algorithme de regroupement des pages à écrire pour les SGBDR (à lire dans : http://www.amazon.com/Readings-Database-Systems-Joseph-Hellerstein/dp/0262693143/ref=sr_1_1?s=books&ie=UTF8&qid=1318494794&sr=1-1) et qui est aussi l'initiateur de PostGreSQL !

Du coup, en vous lisant, j'ai l'impression que Postgres est en dessous de tout sur ce point de vue là. Pour moi, les choses ne sont pas si noires que le ressenti que j'ai à la lecture de votre article.

Avez vous travaillé avec des grandes volumétries et comparé les temps de réponse de PG par rapport à du Oracle ou du SQL Server. Je citais un exemple d'un collectivité local ou il a fallut de débarrasser de PG au profit de SQL Server pour un cas doublement critique : forte volumétrie (et exigence d'un temps de réponse rapide) et haute dispo, pour un système de gestion de catastrophes naturelles extrêmement sensible.

Envoyé par frost242

Pour terminer, j'aimerai juste ajouter une précision par rapport au point 8: VACUUM ne pose aucun verrou sur la table. VACUUM FULL oui. Cf la doc de l'ordre VACUUM. Mais effectivement, je ne vous contredirai pas si vous dites que VACUUM peut être particulièrement pénalisant en terme d'entrées/sorties disque.

Je ne crois pas. VACUUM FULL fait un verrou exclusif de la table entière. Le mécanisme n'est pas décrit en détail pour les autres options, mais vu qu'il ne parle pas de snapshot, je suppose qu'il verrouille page par page, ce qui est effectivement moins contraignant mais génère une IO par page, d'où la remarque "VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions"
Je rajoute ceci qui montre qu'il verrouille plus mal que je le pensais : http://blog.kimiensoftware.com/2011/06/vacuum-locks-358
ShareUpdateExclusiveLock sur la table + RowExclusiveLock sur chaque index....
Là encore le choix de conception de la non gestion des espaces de stockage comme l'absence de // se fait sentir.
Pour info, SQL Server utilise un snapshot pour l'analyse (donc aucun verrou d'aucune sorte) et permet de reconstruire les index en parallèle, ce qui fait que les utilisateurs peuvent continuer d'utiliser l'ancienne version de l'index, la nouvelle remplaçant l'ancienne une fois la reconstruction terminée. Le tout étant fait en //

A +