{"id":6588,"date":"2026-03-21T19:41:52","date_gmt":"2026-03-21T18:41:52","guid":{"rendered":"https:\/\/rootfan.com\/?p=6588"},"modified":"2026-03-21T19:41:55","modified_gmt":"2026-03-21T18:41:55","slug":"differences-architecturales-entre-oracle-et-postgresql","status":"publish","type":"post","link":"https:\/\/rootfan.com\/fr\/architectural-differences-between-oracle-and-postgresql\/","title":{"rendered":"8 diff\u00e9rences architecturales entre Oracle et PostgreSQL qui ont un impact sur les performances"},"content":{"rendered":"<p>De nombreuses entreprises qui migrent d'Oracle \u00e0 PostgreSQL supposent que le principal d\u00e9fi sera de <strong>Diff\u00e9rences de syntaxe SQL<\/strong>.<\/p>\n\n\n\n<p>Mais en r\u00e9alit\u00e9, les changements les plus importants sont <strong>architectural<\/strong>.<\/p>\n\n\n\n<p>Si vous venez d'un environnement Oracle (RAC, Exadata, Enterprise), il est essentiel de comprendre ces diff\u00e9rences car elles ont une incidence directe :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Optimisation des performances<\/li>\n\n\n\n<li>D\u00e9pannage<\/li>\n\n\n\n<li>Planification des capacit\u00e9s<\/li>\n\n\n\n<li>Strat\u00e9gies de mise \u00e0 l'\u00e9chelle<\/li>\n<\/ul>\n\n\n\n<p>Ci-dessous <strong>8 diff\u00e9rences architecturales cl\u00e9s entre Oracle et PostgreSQL<\/strong> qui ont le plus d'impact sur les performances.<\/p>\n\n\n\n<!--more-->\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table des mati\u00e8res<\/h2><nav><ul><li><a href=\"#1-process-model\">1. Mod\u00e8le de processus<\/a><ul><li><a href=\"#oracle-shared-memory-architecture\">Oracle : Architecture \u00e0 m\u00e9moire partag\u00e9e<\/a><\/li><li><a href=\"#postgre-sql-process-per-connection\">PostgreSQL : Processus par connexion<\/a><ul><li><a href=\"#performance-impact\">Impact sur les performances<\/a><\/li><\/ul><\/li><\/ul><\/li><li><a href=\"#2-concurrency-control-mvcc\">2. Contr\u00f4le de la concomitance (MVCC)<\/a><ul><li><a href=\"#oracle-mvcc\">Oracle MVCC<\/a><\/li><li><a href=\"#postgre-sql-mvcc\">PostgreSQL MVCC<\/a><ul><li><a href=\"#performance-impact-1\">Impact sur les performances<\/a><\/li><\/ul><\/li><\/ul><\/li><li><a href=\"#3-vacuum-vs-oracle-automatic-cleanup\">3. VACUUM vs Oracle Automatic Cleanup<\/a><ul><li><a href=\"#performance-implication\">Implication sur les performances<\/a><\/li><\/ul><\/li><li><a href=\"#4-wal-vs-oracle-redo-logs\">4. WAL vs Oracle Redo Logs<\/a><ul><li><a href=\"#oracle-logging\">Oracle Logging<\/a><\/li><li><a href=\"#postgre-sql-logging\">Journalisation PostgreSQL<\/a><ul><li><a href=\"#performance-implication-2\">Implication sur les performances<\/a><\/li><\/ul><\/li><\/ul><\/li><li><a href=\"#5-index-behavior\">5. Comportement de l'index<\/a><ul><li><a href=\"#performance-implication-3\">Implication sur les performances<\/a><\/li><\/ul><\/li><li><a href=\"#6-query-optimizer-differences\">6. Diff\u00e9rences entre les optimiseurs de requ\u00eates<\/a><ul><li><a href=\"#performance-implication-4\">Implication sur les performances<\/a><\/li><\/ul><\/li><li><a href=\"#7-parallel-execution\">7. Ex\u00e9cution parall\u00e8le<\/a><ul><li><a href=\"#performance-implication-5\">Implication sur les performances<\/a><\/li><\/ul><\/li><li><a href=\"#8-cluster-architecture\">8. Architecture des grappes<\/a><ul><li><a href=\"#oracle-rac\">Oracle RAC<\/a><\/li><li><a href=\"#postgre-sql-replication\">R\u00e9plication PostgreSQL<\/a><ul><li><a href=\"#performance-implication-6\">Implication sur les performances<\/a><\/li><\/ul><\/li><\/ul><\/li><li><a href=\"#oracle-vs-postgre-sql-architecture-summary\">R\u00e9sum\u00e9 de l'architecture Oracle vs PostgreSQL<\/a><\/li><li><a href=\"#the-3-things-that-surprise-oracle-db-as-the-most\">Les 3 choses qui surprennent le plus les administrateurs de bases de donn\u00e9es Oracle<\/a><ul><li><a href=\"#1-autovacuum-problems\">1. Probl\u00e8mes li\u00e9s \u00e0 l'autovacuum<\/a><\/li><li><a href=\"#2-too-many-connections\">2. Trop de connexions<\/a><\/li><li><a href=\"#3-wal-bottlenecks\">3. Goulets d'\u00e9tranglement WAL<\/a><\/li><\/ul><\/li><li><a href=\"#final-thoughts\">R\u00e9flexions finales<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"1-process-model\">1. Mod\u00e8le de processus<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"oracle-shared-memory-architecture\">Oracle : Architecture \u00e0 m\u00e9moire partag\u00e9e<\/h2>\n\n\n\n<p>Oracle s'appuie fortement sur <strong>structures de m\u00e9moire partag\u00e9e<\/strong> \u00e0 l'int\u00e9rieur du SGA (System Global Area).<\/p>\n\n\n\n<p>Architecture typique :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nUser process\n   |\nServer process\n   |\nSGA (shared memory)\n\n<\/pre><\/div>\n\n\n<p>Les principaux \u00e9l\u00e9ments de la SGA sont les suivants<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSGA\n \u251c Buffer cache\n \u251c Shared pool\n \u251c Redo log buffer\n \u2514 Large pool\n\n<\/pre><\/div>\n\n\n<p>De nombreuses sessions acc\u00e8dent \u00e0 la <strong>les m\u00eames structures de m\u00e9moire simultan\u00e9ment<\/strong>.<\/p>\n\n\n\n<p>Pour contr\u00f4ler la concurrence, Oracle utilise des m\u00e9canismes tels que<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Loquets<\/li>\n\n\n\n<li>Mutex<\/li>\n\n\n\n<li>Verrouillage du cache de la biblioth\u00e8que<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"postgre-sql-process-per-connection\">PostgreSQL : Processus par connexion<\/h2>\n\n\n\n<p>PostgreSQL utilise un <strong>architecture processus par connexion<\/strong>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nClient\n   |\nPostgreSQL backend process\n\n<\/pre><\/div>\n\n\n<p>Chaque connexion g\u00e9n\u00e8re un <strong>processus distinct pour le syst\u00e8me d'exploitation<\/strong>.<\/p>\n\n\n\n<p>Exemple :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npostgres\n \u251c backend process 1\n \u251c backend process 2\n \u251c backend process 3\n\n<\/pre><\/div>\n\n\n<p>La m\u00e9moire partag\u00e9e est plus petite et plus simple.<\/p>\n\n\n\n<p>Les principaux domaines partag\u00e9s sont les suivants<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nshared_buffers\nwal_buffers\n\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-impact\">Impact sur les performances<\/h3>\n\n\n\n<p>PostgreSQL ne peut pas g\u00e9rer efficacement les <strong>des milliers de connexions directes<\/strong>.<\/p>\n\n\n\n<p>C'est la raison pour laquelle les pools de connexion tels que :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npgBouncer\npgpool\n\n<\/pre><\/div>\n\n\n<p>sont couramment utilis\u00e9s dans la production.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"2-concurrency-control-mvcc\">2. Contr\u00f4le de la concomitance (MVCC)<\/h1>\n\n\n\n<p>Oracle et PostgreSQL utilisent tous deux <strong>MVCC (Multi-Version Concurrency Control)<\/strong>, mais ils la mettent en \u0153uvre diff\u00e9remment.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"oracle-mvcc\">Oracle MVCC<\/h2>\n\n\n\n<p>Oracle stocke les versions pr\u00e9c\u00e9dentes des lignes dans <strong>Annuler les tablespaces<\/strong>.<\/p>\n\n\n\n<p>Exemple d'op\u00e9ration :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nUPDATE table\n\n<\/pre><\/div>\n\n\n<p>Oracle \u00e9crit la version pr\u00e9c\u00e9dente dans :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nUNDO\n\n<\/pre><\/div>\n\n\n<p>Lorsqu'une requ\u00eate a besoin d'un clich\u00e9 plus ancien, Oracle le reconstruit \u00e0 l'aide de la fonction UNDO.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"postgre-sql-mvcc\">PostgreSQL MVCC<\/h2>\n\n\n\n<p>Magasins PostgreSQL <strong>les versions de ligne directement dans le tableau<\/strong>.<\/p>\n\n\n\n<p>Exemple de mise \u00e0 jour :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nUPDATE row\n\n<\/pre><\/div>\n\n\n<p>PostgreSQL cr\u00e9e un fichier <strong>nouvelle version de la ligne<\/strong>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nold row (dead)\nnew row (visible)\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-impact-1\">Impact sur les performances<\/h3>\n\n\n\n<p>Comme les anciennes versions des lignes restent \u00e0 l'int\u00e9rieur des tables, les tables PostgreSQL s'agrandissent avec le temps.<\/p>\n\n\n\n<p>C'est pourquoi PostgreSQL exige :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nVACUUM\n\n<\/pre><\/div>\n\n\n<p>En l'absence d'un nettoyage ad\u00e9quat, on obtient :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\ntable bloat\nindex bloat\n\n<\/pre><\/div>\n\n\n<p>Il s'agit de l'un des <strong>Principales diff\u00e9rences conceptuelles pour les administrateurs de bases de donn\u00e9es Oracle<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"3-vacuum-vs-oracle-automatic-cleanup\">3. VACUUM vs Oracle Automatic Cleanup<\/h1>\n\n\n\n<p>Oracle g\u00e8re automatiquement les anciennes versions de lignes \u00e0 l'aide du syst\u00e8me UNDO.<\/p>\n\n\n\n<p>PostgreSQL doit \u00eatre nettoy\u00e9 p\u00e9riodiquement par <strong>ASPIRATEUR<\/strong>.<\/p>\n\n\n\n<p>Il en existe deux types principaux :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nVACUUM\nAUTOVACUUM\n<\/pre><\/div>\n\n\n<p>Autovacuum supprime automatiquement les tuples morts.<\/p>\n\n\n\n<p>Si l'autovacuum n'est pas correctement r\u00e9gl\u00e9, vous risquez de rencontrer des probl\u00e8mes :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\ntable bloat\nslow sequential scans\nvery large indexes\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-implication\">Implication sur les performances<\/h3>\n\n\n\n<p>Dans les environnements PostgreSQL :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nautovacuum tuning is critical\n<\/pre><\/div>\n\n\n<p>De nombreux probl\u00e8mes de production ont leur origine :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nautovacuum not aggressive enough\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"4-wal-vs-oracle-redo-logs\">4. WAL vs Oracle Redo Logs<\/h1>\n\n\n\n<p>Les deux syst\u00e8mes utilisent <strong>l'enregistrement des donn\u00e9es \u00e0 l'avance<\/strong> pour la r\u00e9cup\u00e9ration en cas d'accident.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"oracle-logging\">Oracle Logging<\/h2>\n\n\n\n<p>Oracle utilise <strong>journaux de r\u00e9tablissement (redo logs)<\/strong>.<\/p>\n\n\n\n<p>Les changements s'encha\u00eenent :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nredo log buffer\n\u2192 redo log files\n<\/pre><\/div>\n\n\n<p>Processus d'arri\u00e8re-plan responsable :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nLGWR\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"postgre-sql-logging\">Journalisation PostgreSQL<\/h2>\n\n\n\n<p>PostgreSQL utilise <strong>WAL (Write Ahead Logging)<\/strong>.<\/p>\n\n\n\n<p>Les donn\u00e9es sont \u00e9crites dans :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nWAL segments\n<\/pre><\/div>\n\n\n<p>Emplacement :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npg_wal\/\n<\/pre><\/div>\n\n\n<p>Exemple de fichier :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n00000001000000000000000A\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-implication-2\">Implication sur les performances<\/h3>\n\n\n\n<p>Les performances de PostgreSQL d\u00e9pendent souvent de :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nWAL throughput\ndisk latency\ncheckpoint tuning\n<\/pre><\/div>\n\n\n<p>Les param\u00e8tres importants sont les suivants<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\ncheckpoint_timeout\nmax_wal_size\nwal_buffers\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"5-index-behavior\">5. Comportement de l'index<\/h1>\n\n\n\n<p>Les index Oracle restent relativement stables m\u00eame en cas de mises \u00e0 jour importantes.<\/p>\n\n\n\n<p>Les index PostgreSQL se comportent diff\u00e9remment \u00e0 cause de MVCC.<\/p>\n\n\n\n<p>Lorsqu'une ligne est mise \u00e0 jour :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nUPDATE row\n\n<\/pre><\/div>\n\n\n<p>PostgreSQL cr\u00e9e :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nnew row version\nnew index entry\n\n<\/pre><\/div>\n\n\n<p>L'entr\u00e9e d'index pr\u00e9c\u00e9dente est conserv\u00e9e jusqu'\u00e0 ce que le vide soit fait.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-implication-3\">Implication sur les performances<\/h3>\n\n\n\n<p>Les index PostgreSQL peuvent <strong>gonfler de mani\u00e8re significative<\/strong>.<\/p>\n\n\n\n<p>Un entretien p\u00e9riodique peut \u00eatre n\u00e9cessaire :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nREINDEX\nVACUUM FULL\n\n<\/pre><\/div>\n\n\n<p>Ou des vues de contr\u00f4le telles que :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npg_stat_all_indexes\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"6-query-optimizer-differences\">6. Diff\u00e9rences entre les optimiseurs de requ\u00eates<\/h1>\n\n\n\n<p>Oracle dispose d'un optimiseur extr\u00eamement sophistiqu\u00e9 avec des fonctionnalit\u00e9s telles que :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nadaptive plans\nparallel query\noptimizer hints\nstatistics feedback\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>L'optimiseur de PostgreSQL est plus simple et s'appuie principalement sur :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nstatistics\ncost model\n\n<\/pre><\/div>\n\n\n<p>Les statistiques sont stock\u00e9es dans :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npg_statistic\n\n<\/pre><\/div>\n\n\n<p>Mise \u00e0 jour par :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nANALYZE\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-implication-4\">Implication sur les performances<\/h3>\n\n\n\n<p>Dans PostgreSQL :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nbad statistics = bad execution plans\n\n<\/pre><\/div>\n\n\n<p>C'est pourquoi <strong>analyse de l'autovacuum<\/strong> est important.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"7-parallel-execution\">7. Ex\u00e9cution parall\u00e8le<\/h1>\n\n\n\n<p>Oracle parallel query est tr\u00e8s avanc\u00e9.<\/p>\n\n\n\n<p>Exemple :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSELECT \/*+ PARALLEL(8) *\/ ...\n\n<\/pre><\/div>\n\n\n<p>PostgreSQL a ajout\u00e9 le parall\u00e9lisme plus tard.<\/p>\n\n\n\n<p>Les op\u00e9rations les plus courantes sont les suivantes<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nParallel Seq Scan\nParallel Hash Join\nParallel Aggregate\n\n<\/pre><\/div>\n\n\n<p>Contr\u00f4l\u00e9 par des param\u00e8tres tels que<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nmax_parallel_workers_per_gather\nmax_worker_processes\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-implication-5\">Implication sur les performances<\/h3>\n\n\n\n<p>Le parall\u00e9lisme dans PostgreSQL est <strong>moins agressif qu'Oracle<\/strong>, mais toujours pr\u00e9cieux pour :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nlarge analytical queries\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"8-cluster-architecture\">8. Architecture des grappes<\/h1>\n\n\n\n<p>C'est sur ce point qu'Oracle et PostgreSQL diff\u00e8rent le plus.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"oracle-rac\">Oracle RAC<\/h2>\n\n\n\n<p>Oracle RAC permet <strong>plusieurs n\u0153uds pour \u00e9crire simultan\u00e9ment<\/strong>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nNode1\nNode2\nNode3\n\n<\/pre><\/div>\n\n\n<p>Tous les n\u0153uds acc\u00e8dent au <strong>m\u00eame base de donn\u00e9es partag\u00e9e<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"postgre-sql-replication\">R\u00e9plication PostgreSQL<\/h2>\n\n\n\n<p>PostgreSQL utilise un <strong>architecture \u00e0 une seule \u00e9criture<\/strong>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nPrimary\n   |\nStandby replicas\n\n<\/pre><\/div>\n\n\n<p>Les m\u00e9thodes de r\u00e9plication comprennent<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nstreaming replication\nlogical replication\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"performance-implication-6\">Implication sur les performances<\/h3>\n\n\n\n<p>Les strat\u00e9gies de mise \u00e0 l'\u00e9chelle diff\u00e8rent consid\u00e9rablement.<\/p>\n\n\n\n<p>Mise \u00e0 l'\u00e9chelle d'Oracle RAC :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nadd nodes \u2192 more write capacity\n\n<\/pre><\/div>\n\n\n<p>Mise \u00e0 l'\u00e9chelle de PostgreSQL :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nread scaling \u2192 replicas\nwrite scaling \u2192 sharding\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"oracle-vs-postgre-sql-architecture-summary\">R\u00e9sum\u00e9 de l'architecture Oracle vs PostgreSQL<\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Fonctionnalit\u00e9<\/th><th>Oracle<\/th><th>PostgreSQL<\/th><\/tr><\/thead><tbody><tr><td>Mod\u00e8le de processus<\/td><td>threads\/m\u00e9moire partag\u00e9e<\/td><td>processus par connexion<\/td><\/tr><tr><td>MVCC<\/td><td>UNDO<\/td><td>versions en ligne<\/td><\/tr><tr><td>Nettoyage<\/td><td>automatique<\/td><td>ASPIRATEUR<\/td><\/tr><tr><td>Enregistrement<\/td><td>refaire<\/td><td>WAL<\/td><\/tr><tr><td>Comportement de l'index<\/td><td>stable<\/td><td>peut gonfler<\/td><\/tr><tr><td>Optimiseur<\/td><td>complexe<\/td><td>plus simple<\/td><\/tr><tr><td>Requ\u00eate parall\u00e8le<\/td><td>tr\u00e8s avanc\u00e9<\/td><td>mod\u00e9r\u00e9<\/td><\/tr><tr><td>Regroupement<\/td><td>CAR<\/td><td>r\u00e9plication<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"the-3-things-that-surprise-oracle-db-as-the-most\">Les 3 choses qui surprennent le plus les administrateurs de bases de donn\u00e9es Oracle<\/h1>\n\n\n\n<p>Lors de migrations r\u00e9elles, ces trois probl\u00e8mes apparaissent fr\u00e9quemment.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"1-autovacuum-problems\">1. Probl\u00e8mes li\u00e9s \u00e0 l'autovacuum<\/h2>\n\n\n\n<p>Sympt\u00f4mes :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\ntables growing\nindexes growing\nslow queries\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"2-too-many-connections\">2. Trop de connexions<\/h2>\n\n\n\n<p>PostgreSQL ne s'adapte pas bien \u00e0 un grand nombre de connexions.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n1000+ connections\n\n<\/pre><\/div>\n\n\n<p>La solution typique est la suivante :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\npgBouncer\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"3-wal-bottlenecks\">3. Goulets d'\u00e9tranglement WAL<\/h2>\n\n\n\n<p>Les charges de travail lourdes en \u00e9criture peuvent saturer :<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code\" data-no-translation=\"\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nWAL disk throughput\n\n<\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"final-thoughts\">R\u00e9flexions finales<\/h1>\n\n\n\n<p>La migration d'Oracle vers PostgreSQL ne se limite pas \u00e0 la traduction du langage SQL.<\/p>\n\n\n\n<p>Il faut comprendre <strong>diff\u00e9rences architecturales fondamentales<\/strong> qui influencent les performances, l'\u00e9chelle et les pratiques op\u00e9rationnelles.<\/p>\n\n\n\n<p>Une fois ces diff\u00e9rences comprises, PostgreSQL devient une plateforme extr\u00eamement puissante et flexible pour les syst\u00e8mes de donn\u00e9es modernes.<\/p>","protected":false},"excerpt":{"rendered":"<p>De nombreuses entreprises qui migrent d'Oracle vers PostgreSQL supposent que les diff\u00e9rences de syntaxe SQL constituent le principal d\u00e9fi \u00e0 relever. Mais en r\u00e9alit\u00e9, les changements les plus importants sont d'ordre architectural. Si vous venez d'un environnement Oracle (RAC, Exadata, Enterprise), il est essentiel de comprendre ces diff\u00e9rences car elles affectent directement : Voici 8 diff\u00e9rences architecturales cl\u00e9s entre Oracle et PostgreSQL qui ... <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/rootfan.com\/fr\/architectural-differences-between-oracle-and-postgresql\/\" class=\"more-link\">Continuer la lecture<span class=\"screen-reader-text\"> de &laquo;&nbsp;8 Architectural Differences Between Oracle and PostgreSQL That Impact Performance&nbsp;&raquo;<\/span><\/a><\/p>","protected":false},"author":1,"featured_media":6590,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_focus_keyword":"","rank_math_title":"","rank_math_description":"","rank_math_robots":"","rank_math_og_title":"","rank_math_og_description":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[126],"tags":[137,138],"class_list":["post-6588","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-migration","tag-performance-tuning"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/rootfan.com\/wp-content\/uploads\/pexels-photo-1690351.jpeg?fit=943%2C1300&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6588","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/comments?post=6588"}],"version-history":[{"count":5,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6588\/revisions"}],"predecessor-version":[{"id":6602,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/posts\/6588\/revisions\/6602"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/media\/6590"}],"wp:attachment":[{"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/media?parent=6588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/categories?post=6588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rootfan.com\/fr\/wp-json\/wp\/v2\/tags?post=6588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}