{"id":14352,"date":"2020-06-24T17:42:04","date_gmt":"2020-06-24T15:42:04","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/"},"modified":"2020-06-24T17:42:04","modified_gmt":"2020-06-24T15:42:04","slug":"some-myths-about-postgresql-vs-oracle","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/","title":{"rendered":"Some myths about PostgreSQL vs. Oracle"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nI originally wrote this as a comment on the following post that you may find on internet:
\nhttps:\/\/www.2ndquadrant.com\/en\/blog\/oracle-to-postgresql-reasons-to-migrate\/<\/a>
\nbut my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there. <\/p>\n

You should never take any decision on what you read on the internet without verifying. It is totally valid to consider a move to Open Source databases, but doing it without good understanding is a risk for your migration project success.<\/p>\n

In italics<\/em> are the quotes from the article.<\/h3>\n

Kirk,
\nAs you do a comparison and link to a list of PostgreSQL features, let me refine the name and description of the Oracle features you compare to, so that people can find them and do a fair comparison. I’m afraid they may not recognize the names and descriptions you provide, at least in current versions. As an example, nobody will get search hits for “Federation”, or “plSQL”, or “HTML DB”\u2026 in the Oracle documentation but they will find “Oracle Gateway”, “PL\/SQL”, “APEX”\u2026
\n<\/p>\n

Federation vs. Foreign Data\u00a0Wrappers<\/h3>\n

<\/em>
\nThere is no feature called “Federation”.\u00a0
\nThe closest from your description is Database links and Heterogeneous Services through Database Gateway. They go further than FDW in many points. But anyway, I would never use that for ETL. ETL needs optimized bulk loads and there are other features for that (like External Tables to read files, and direct-path inserts to fast load). If your goal is to federate and distribute some small reference tables, then Materialized Views is the feature you may look for.
\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/heter\/introduction.html#GUID-EC402025-0CC0-401F-AF93-888B8A3089FE<\/a>
\n<\/p>\n

plSQL vs. everything else<\/h3>\n

<\/em>
\n“Oracle has a built-in programming language called plSQL.”<\/em>
\nPL\/SQL is more than that. It is compiled (to pcode or native), manages dependencies (tracks dependencies on schema objects), optimized for data access (UDF can even be compiled to run within the SQL engine), can be multithreaded (Parallel Execution). That’s different from PL\/pgSQL which is interpreted at execution time. You mention languages as “as plug-ins”<\/em> and for this, there are other ways to run different languages (external procedures, OJCM, External Table preprocessor,\u2026) but when it comes to performance, transaction control, dependency tracking,\u2026 that’s PL\/SQL.
\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/lnpls\/overview.html#GUID-17166AA4-14DC-48A6-BE92-3FC758DAA940<\/a>
\n<\/p>\n

Application programming<\/h3>\n

<\/em>
\nProviding an “API to communicate with the database”<\/em> is not about open source as the main goal is: encapsulation and hide implementation details. In order to access internal structures, which is what you mention, Oracle provides relational views (known as V$ views) accessible with the most appropriate API for a relational database: SQL
\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/refrn\/dynamic-performance-views.html#GUID-8C5690B0-DE10-4460-86DF-80111869CF4C<\/a>
\n<\/p>\n

Internationalization and Localization<\/h3>\n

<\/em>
\nThe “globalization toolkit”<\/em> is only one part of the globalization features. You can also use any “any character encoding, collation and code page”<\/em> but not relying on the OS implementation of it makes it cross-platform compatible and OS upgrade compatible (see https:\/\/wiki.postgresql.org\/wiki\/Locale_data_changes<\/a>)
\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/nlspg\/overview-of-globalization-support.html#GUID-6DD587EE-6686-4802-9C08-124B495978D5<\/a>
\n<\/p>\n

Web Development<\/h3>\n

<\/em>
\n“Oracle acknowledges the existence of HTML through HTML DB. PostgreSQL natively supports JSON, XML and plugs in Javascript”<\/em>. HTML DB can be found in paper books, but the name is “APEX” since 2006. And it is not (only) about HTML, JSON, or XML but is a low-code Rapid Application Development with no equivalent for other databases.
\nSupport for the structures and languages you mention are all there. The latest trend being JSON: https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/adjsn\/index.html<\/a>
\n<\/p>\n

Authentication<\/h3>\n

<\/em>
\n“Oracle has a built-in authentication system.”<\/em>
\nYes, to be platform-independent, and has many other External Authentication: https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/dbseg\/configuring-authentication.html#GUID-BF8E5E84-FE7E-449C-8081-755BAA4CF8DB<\/a>
\n<\/p>\n

Extensibility<\/h3>\n

<\/em>
\n“Oracle has a plug-in system”<\/em>. I don’t know what you are referring to. Oracle is multi-platform proprietary software. Commercial, which means with vendor supported. There are a lot of APIs for extensions, but the vendor must have to control what runs in the engine in order to provide support.
\n<\/p>\n

Read Scalability<\/h3>\n

<\/em>
\n“PostgreSQL can create a virtually unlimited read cluster”<\/em>. Oracle has active\/active cluster (called RAC) and read replicas (called Active Data Guard). For horizontal scalability, you use the same as for vertical (Parallel Execution) across multiple nodes (in sync, with instance affinity on partitions,\u2026)
\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/20\/vldbg\/parallel-exec-intro.html#GUID-F9A83EDB-42AD-4638-9A2E-F66FE09F2B43<\/a>
\n<\/p>\n

Cost<\/h3>\n

<\/em>
\n“they don’t mind charging you again for every single instance.”<\/em>\u00a0
\nNo, that’s wrong, license metrics are on processors (CPU) or users (NUP). You run as many instances as you want on your licensed servers for your licensed users: https:\/\/www.oracle.com\/a\/ocom\/docs\/corporate\/oracle-software-licensing-basics.pdf<\/a>
\n“jamming everything into a single instance just to reduce costs”<\/em>.\u00a0
\nNo, database consolidation is recommended to scale the management of multiple databases, but not for licensing costs. If you go there, there are a lot of features to allow isolation and data movement in consolidated databases: Multitenant, Resource Manager, Online Relocate, Lockdown Profiles,\u2026
\n<\/p>\n

Performance<\/h3>\n

<\/em>
\n“differentiate the tuning parameters for your warehouse to OLTP to reporting to the data lake”<\/em>: I already mentioned the point about read replicas and about multiple instances in a server. But with oracle, all the parameters I want to set different for OLTP or reporting do not require another instance. They can be set at session or PDB level. As Oracle does not need the filesystem buffer cache, there’s no need to separate on different servers to avoid noisy neighbours.\u00a0<\/p>\n

I hope this helps to look further at the features. There are many reasons to migrate and the main one is the will to move from a commercial model (with license and support) to an open-source one (start with low cost, help from community). But decision must be made on facts and not rumours.<\/p>\n

Franck.<\/p>\n","protected":false},"excerpt":{"rendered":"

By Franck Pachot . I originally wrote this as a comment on the following post that you may find on internet: https:\/\/www.2ndquadrant.com\/en\/blog\/oracle-to-postgresql-reasons-to-migrate\/ but my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there. You should never take any decision on what you read […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59,83],"tags":[220,221,15,64,96,66,223,77],"type_dbi":[],"class_list":["post-14352","post","type-post","status-publish","format-standard","hentry","category-oracle","category-postgresql","tag-cdb","tag-data-guard","tag-migration","tag-multitenant","tag-oracle","tag-pdb","tag-pluggable-databases","tag-postgresql"],"acf":[],"yoast_head":"\nSome myths about PostgreSQL vs. Oracle - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Some myths about PostgreSQL vs. Oracle\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . I originally wrote this as a comment on the following post that you may find on internet: https:\/\/www.2ndquadrant.com\/en\/blog\/oracle-to-postgresql-reasons-to-migrate\/ but my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there. You should never take any decision on what you read […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-24T15:42:04+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"Some myths about PostgreSQL vs. Oracle\",\"datePublished\":\"2020-06-24T15:42:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/\"},\"wordCount\":1047,\"commentCount\":0,\"keywords\":[\"CDB\",\"Data Guard\",\"Migration\",\"multitenant\",\"Oracle\",\"PDB\",\"Pluggable Databases\",\"PostgreSQL\"],\"articleSection\":[\"Oracle\",\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/\",\"name\":\"Some myths about PostgreSQL vs. Oracle - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-06-24T15:42:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/some-myths-about-postgresql-vs-oracle\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Some myths about PostgreSQL vs. Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Some myths about PostgreSQL vs. Oracle - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/","og_locale":"en_US","og_type":"article","og_title":"Some myths about PostgreSQL vs. Oracle","og_description":"By Franck Pachot . I originally wrote this as a comment on the following post that you may find on internet: https:\/\/www.2ndquadrant.com\/en\/blog\/oracle-to-postgresql-reasons-to-migrate\/ but my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there. You should never take any decision on what you read […]","og_url":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/","og_site_name":"dbi Blog","article_published_time":"2020-06-24T15:42:04+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"headline":"Some myths about PostgreSQL vs. Oracle","datePublished":"2020-06-24T15:42:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/"},"wordCount":1047,"commentCount":0,"keywords":["CDB","Data Guard","Migration","multitenant","Oracle","PDB","Pluggable Databases","PostgreSQL"],"articleSection":["Oracle","PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/","url":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/","name":"Some myths about PostgreSQL vs. Oracle - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-06-24T15:42:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/some-myths-about-postgresql-vs-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Some myths about PostgreSQL vs. Oracle"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14352"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14352\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14352"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}