标签：PostgreSQL GiST 索引 SQL Oracle 迁移 PL

从Oracle迁移到PostgreSQL的十大理由

PostgreSQLChina 官方微信：开源软件联盟PostgreSQL分会   19 人赞同了该文章

作者：保罗·纳穆格

Paul Namuag能够担任各种职务，受益于在过去的18年中有机会使用各种技术。他从2005年开始担任图形艺术家和MS .Net开发人员，并转而使用开源技术，并且是使用LAMP stack的Web开发人员。之后，他是一名软件工程师/游戏工程师，曾与多家公司合作开发移动或桌面和Web应用程序。2013年下半年，他转任MySQL支持工程师，然后成为Percona的远程DBA，从而有机会了解大数据，高可伸缩性，高可用性应用程序的工作方式。

前言

Oracle关系数据库管理系统（RDBMS）已被企业广泛使用，迄今为止被认为是市场上最先进的数据库技术。通常，它是RDBMS中最常拿来与其他数据库进行比较的的产品，充当了产品应提供的标准“事实”。http://db-engines.com将其评为当今市场上排名第一的RDBMS。PostgreSQL被列为第四名RDBMS，但这并不意味着迁移到PostgreSQL没有任何优势。PostgreSQL自1989年以来一直存在，并于1996年开放源代码。PostgreSQL从2017年和2018年连续两年获得年度DBMS奖。这表明它从没有停止吸引大量用户和企业的青睐。

PostgreSQL之所以引起广泛关注的原因之一是因为人们正在寻找Oracle的替代产品，以便他们可以削减组织的高成本并避免供应商的锁定。

从工作高效的Oracle数据库迁移可能是一项艰巨的任务。诸如公司的TCO（总拥有成本）之类的担忧是公司拖延决定是否放弃甲骨文的原因之一。

在此博客中，我们将介绍一些公司选择离开Oracle并迁移到PostgreSQL的主要原因。

原因一：这是一个真正的开源项目

PostgreSQL是开源的，并根据 PostgreSQL许可证发行， PostgreSQL许可证是自由的开源许可证，类似于BSD或MIT许可证。获得产品和支持不需要任何费用。

如果要使用数据库软件，则意味着您可以免费获得PostgreSQL数据库的所有可用功能。PostgreSQL在数据库领域已有30多年的悠久历史，自1996年以来就一直以开源为基础。数十年来，开发人员一直致力于创建扩展功能。这本身就能促使开发人员，机构和组织选择PostgreSQL用于企业应用程序，支持领先的业务和移动应用程序。

企业再次意识到，像Postgres这样的开源数据库解决方案提供了更大的容量，灵活性和支持，而并不完全依赖于任何一家公司或开发人员。与以前的Linux一样，Postgres由致力于解决日常业务问题，并选择将解决方案返回给社区的用户设计（并将继续保持）。与像Oracle这样的大型开发商不同，后者可能有不同的动机来开发有利可图的产品或支持狭窄但有利润的市场，而Postgres社区致力于为用户日常使用关系数据库开发最好的工具。

PostgreSQL通常执行这些任务而不会增加太多复杂性。其设计严格专注于处理数据库，而不必浪费资源，例如通过添加功能来管理其他IT环境。这是该开源软件的使用者从Oracle迁移到PostgreSQL时的事情之一。花大量时间研究有关Oracle数据库如何运行或如何优化和调优的复杂技术，最终可能会得到其昂贵的支持。这吸引了机构或组织寻找替代方案，可以减轻成本的同时，带来利润和生产率。请查看我们以前的博客，了解PostgreSQL在SQL语法和Oracle语法之间的匹配能力。

原因二：没有许可限制，而且社区很大

对于Oracle RDBMS平台的用户来说，很难找到任何类型的免费或不收取高额费用的社区支持。机构，组织和开发人员通常能在网上找到替代信息，这些信息可以免费提供问题的答案或解决方案。

使用Oracle时，很难确定是否选择产品技术支持，因为，通常涉及较多费用。使用PostgreSQL，该社区是免费的，并且有很多拥有丰富经验的专家，他们乐于为您解决当前的问题。

您可以在这里https://lists.postgresql.org/ 订阅邮件列表，以开始与社区联系。接触PostgreSQL 的新手或天才可在这里进行交流，展示和共享解决方案、技术、错误、新发现，甚至共享其新兴软件。您甚至可以使用http://irc.freenode.net并加入#postgresql频道，从IRC聊天中寻求帮助。您还可以加入https://postgres-slack.herokuapp.com/或https://postgresteam.slack.com/通过Slack与社区联系。有很多选择，很多开源组织可以为您提供帮助。

有关从何处开始的更多详细信息，请访问https://www.postgresql.org/community/。

如果您愿意为PostgreSQL中更优质的专业服务买单，这里也有很多选择。即使在网站https://www.postgresql.org/support/professional_support/northamerica/上查看，也可以找到大量公司，其中一些公司的价格便宜。即使在Severalnines，我们也提供对Postgres的支持，它是ClusterControl许可或DBA技术咨询的一部分。

原因三：广泛支持SQL一致性

PostgreSQL一直热衷于适应和遵循SQL语言标准。SQL标准的正式名称是ISO / IEC 9075“数据库语言SQL”。标准发行版的任何后续修订版本都将替换以前的版本，因此声称与早期版本保持一致并没有多少价值。

与Oracle不同，仍然存在一些不符合ANSI标准SQL（结构化查询语言）的关键字或运算符。例如，初学者容易混淆的OUTER JOIN（+）（Oracle运算符语法）。PostgreSQL遵循用于JOIN语法的ANSI-SQL标准，并具有与其他开源RDBMS数据库（例如MySQL / Percona / MariaDB数据库）轻松跳转的优势。

在Oracle中非常常见的另一种语法是使用分层查询。Oracle使用非标准的START WITH..CONNECT BY语法，而在SQL:1999中，层次结构查询是通过递归通用表表达式(CTE)实现的。例如，对比以下分层查询的不同表达：

Oracle

SELECT
    restaurant_name,
    city_name
FROM
    restaurants rs
START WITHrs.city_name = 'TOKYO'
CONNECT BY PRIOR rs.restaurant_name = rs.city_name;

PostgreSQL

WITH RECURSIVE tmp AS (SELECT restaurant_name, city_name
                                 FROM restaurants
                                WHERE city_name = 'TOKYO'
                                UNION
                               SELECT m.restaurant_name, m.city_name
                                 FROM restaurants m
                                 JOIN tmp ON tmp.restaurant_name = m.city_name)
                  SELECT restaurant_name, city_name FROM tmp;

PostgreSQL与其他顶级开源RDBMS（例如MySQL / MariaDB）具有非常相似的方法。

根据PostgreSQL 手册，PostgreSQL开发的目的是与标准的最新正式版本保持一致，在这种情况下，这种一致不与传统功能或常识相矛盾。支持SQL标准所需的许多功能，尽管有时语法或功能略有不同。实际上，这对于PostgreSQL来说是很棒的，因为无论怎样，PostgreSQL都受到不同组织的支持和协作。关键在于其SQL语言符合标准的要求。

PostgreSQL的开发旨在与标准的最新正式版本保持一致，在这种情况下，这种一致性不会与传统功能或常识相抵触。支持SQL标准所需的许多功能，尽管有时语法或功能略有不同。随着时间的流逝，有望进一步实现一致性。

原因四：查询并行

公平地说，与Oracle的SQL语句并行执行相比，PostgreSQL的查询并行性并不丰富。Oracle并行性的功能包括带提示的语句排队，设置并行度（DOP），设置并行度策略或自适应并行性的能力。

PostgreSQL根据支持的计划具有简单的并行度，但这并没有定义Oracle在开源PostgreSQL方面的优势。

PostgreSQL的并行性一直在不断提高，并被社区不断增强。当PostgreSQL 10发布时，它增强了对公众的吸引力，特别是对合并连接，位图堆扫描，索引扫描和仅索引扫描，聚集合并等并行性支持的改进。还为pg_stat_activity添加了统计信息。

在PostgreSQL V10中，默认情况下禁用并行性，您需要设置变量

max_parallel_workers_per_gather
postgres=# \timing
Timing is on.
postgres=# explain analyze select * from imdb.movies wherebirthyear >= 1980 and birthyear <=2005;
                                                   QUERYPLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on movies  (cost=0.00..215677.28rows=41630 width=68) (actual time=0.013..522.520 rows=84473 loops=1)
   Filter: ((birthyear >= 1980) AND (birthyear <=2005))
   Rows Removed by Filter: 8241546
 Planning time: 0.039 ms
 Execution time: 525.195 ms
(5 rows)
Time: 525.582 ms
postgres=# \o /dev/null
postgres=#  select * from imdb.movies where birthyear>= 1980 and birthyear <=2005;
Time: 596.947 ms

查询计划显示，它的实际时间大约可以是522.5 ms，然后实际查询执行时间大约是596.95 ms。

来看启用并行性后的结果：

postgres=# set max_parallel_workers_per_gather=2;
Time: 0.247 ms
postgres=# explain analyze select * from imdb.movies wherebirthyear >= 1980 and birthyear <=2005;
                                                          QUERYPLAN
-------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..147987.62 rows=41630width=68) (actual time=0.172..339.258 rows=84473 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on movies (cost=0.00..142824.62 rows=17346 width=68) (actual time=0.029..264.980 rows=28158 loops=3)
         Filter: ((birthyear >= 1980) AND (birthyear <=2005))
         Rows Removed by Filter: 2747182
 Planning time: 0.096 ms
 Execution time: 342.735 ms
(8 rows)
Time: 343.142 ms
postgres=# \o /dev/null
postgres=#  select * from imdb.movies where birthyear>= 1980 and birthyear <=2005;
Time: 346.020 ms

查询计划确定查询需要使用并行性，然后使用Gather节点。它的实际时间估计为339毫秒（包含2个并行工作进程），估计为264毫秒（查询计划汇总之前）。现在，查询的实际执行时间为346ms，非常接近查询计划中估计的实际时间。

这仅说明了PostgreSQL的性能和益处。尽管PostgreSQL 在使用并行性或查询计划确定它比使用并行性要快时有其自身的局限性，但它的功能并没有与Oracle产生巨大差异。PostgreSQL的并行性很灵活，只要您的查询与查询并行性所需的顺序匹配，就可以正确启用或利用它。

原因五：先进的JSON支持，并且不断在提升

与其他开放源代码RDBMS相比，PostgreSQL中的JSON支持始终处于同等水平。可以看一下LiveJournal的博客（博客地址https://obartunov.livejournal.com/200076.html），其中PostgreSQL的JSON支持显示出相比其他RDBMS总是更高级。PostgreSQL具有大量的JSON函数和功能。

JSON数据类型在PostgreSQL-9.2中引入。从那时起，它有了许多重大的提升，并且在PostgreSQL-9.4中出现了主要的提升，增加了JSONB的数据类型。PostgreSQL提供了两种存储JSON数据的数据类型：json和JSONB。对于JSONB，它是JSON数据类型的高级版本，以二进制格式存储JSON数据。这是主要的增强功能，与在PostgreSQL中搜索和处理JSON数据的方式有很大的不同。

Oracle也广泛支持JSON。相比之下，PostgreSQL提供了广泛的支持，可以用于数据检索、数据格式化或条件操作，这些操作会影响数据的输出，甚至影响数据库中存储的数据。使用jsonb数据类型存储的数据具有很大的优势，即能够使用GIN（通用倒排索引），GIN可用于有效搜索大量jsonb文档中出现的键或键/值对。

PostgreSQL有额外的扩展，有助于将jsonb的类型转换为它支持的过程语言。这些扩展是针对PL/Perl的jsonb_plperl和jsonb_plperlu。而对于PL/Python，它们是jsonb_plpythonu、jsonb_plpython2u和jsonb_plpython3u。例如，使用jsonb值来映射Perl数组，您可以使用jsonb_plperl或jsonb_plperlu扩展。

ArangoDB发布了一个基准，将PostgreSQL的JSON性能与其他支持JSON的数据库进行了比较。尽管这是一个较老的博客，但它仍然展示了PostgreSQL的JSON与其他数据库相比的性能。在其他数据库中，JSON是其内核的核心功能。这说明JSON即使只是PostgreSQL的附加功能也具有一定的优势。

原因六：主要云供应商对DBaaS的支持

PostgreSQL已作为DBaaS得到广泛支持。这些服务来自亚马逊、微软Azure数据库和谷歌Cloud SQL。

相比之下，Oracle仅在Amazon RDS for Oracle 上可用。主要参与者提供的服务以实惠的价格开始，并且可以根据您的需求进行灵活设置。这有助于机构和组织进行相应的设置，并减轻Oracle平台上捆绑的巨额成本。

原因七：更好地处理海量数据

PostgreSQL RDBMS不适用于处理分析和数据仓库类的工作负载。PostgreSQL是一个面向行的数据库，但是它具有存储大量数据的能力。PostgreSQL在处理数据存储时有以下限制：

限制	值
最大数据库大小	无限
最大表尺寸	32TB
最大行大小	1.6TB
最大字段大小	1GB
每个表的最大行数	无限
每个表的最大列数	250-1600，取决于列类型
每张表的最大索引	无限

PostgreSQL的主要优点是，已经有一些插件可以合并以处理大数据量。TimeScaleDB和CitusData的cstore_fdw可以合并到时间序列数据库，存储移动、物联网应用程序中的大量数据，作为数据分析或数据仓库的插件之一。实际上，ClusterControl（某产品）提供了对TimeScaleDB的支持，该支持既简单又易于部署。

如果要使用PostgreSQL的核心功能，则可以使用jsonb存储大量数据。例如，大量文档（PDF，Word，电子表格）可以使用jsonb数据类型存储。对于地理定位应用程序或系统，可以使用PostGIS。

 

原因八：廉价的可伸缩性，高可用性，冗余/地理冗余和容错解决方案

Oracle提供了类似的解决方案，例如OracleGrid，Oracle Real Application Clusters（RAC），Oracle Clusterware和Oracle Data Guard。这些技术可能会增加您的成本，并且部署和稳定维护的费用出乎意料地昂贵，而且一旦用上很难放弃这些解决方案。必须培训技术人员增强技能，并培养参与部署和实施过程的人员。

PostgreSQL有大量的支持，并且有很多选择。PostgreSQL包含内置核心程序包中的流和逻辑复制。您还可以为PostgreSQL设置同步复制，以拥有更多的高可用群集，同时使备用节点处理读取查询。对于高可用性，我们建议您阅读我们的博客PostgreSQL顶级PG集群高可用性（HA）解决方案，其中涵盖了许多不错的工具和技术供您选择。

还有一些企业功能可以提供高可用性，监视和备份解决方案。ClusterControl是这项技术之一，与Oracle解决方案相比，价格合理。

原因九：支持多种过程语言：PL/pgSQL、PL/Tcl、PL/Perl和PL/Python

从9.4版开始，PostgreSQL具有一个很棒的功能，您可以根据自己的选择定义新的过程语言。尽管不支持所有编程语言，但是它具有许多受支持的语言。当前，通过基本发行版，它包括PL / pgSQL，PL / Tcl，PL/ Perl和PL / Python。外部语言是：

名称	语言	网站
PL/Java	Java	https://tada.github.io/pljava/
PL/Lua	Lua	https://github.com/pllua/pllua
PL/R	R	https://github.com/postgres-plr/plr
PL/ sh	Unix shell	https://github.com/petere/plsh
PL/ v8	JavaScript	https://github.com/plv8/plv8

这样做的好处是，与Oracle不同，新进入PostgreSQL的开发人员可以快速为他们的应用程序系统提供业务逻辑，而无需花费更多时间来学习PL / SQL。PostgreSQL使开发人员的环境更加轻松和高效。PostgreSQL的这种性质有助于开发人员喜欢PostgreSQL并开始将企业平台解决方案转移到开源环境。

原因十：大数据集、文本数据的灵活索引（GIN，GiST，SP-GiST和BRIN）

PostgreSQL在支持索引方面具有巨大的优势，这有利于处理大数据。Oracle有很多索引类型，它们也有利于处理大型数据集，尤其是全文索引。但是对于PostgreSQL，这些类型的索引是根据你的目的而灵活设置的。例如，这些类型的索引适用于大数据:

GIN-（广义倒排索引）

此类索引适用于jsonb，hstore，range和arrays数据类型列。当您的数据类型在单个列中包含多个值时，此功能很有用。根据PostgreSQL文档，“ GIN用于处理要建立索引的项目是复合值的情况，并且要通过索引处理的查询搜索出现在复合项目内的元素值。例如，这些项目可以是文档，而查询可以是对包含特定单词的文档的搜索。”

 

GiST-（通用搜索树）

由节点页面组成的高度平衡的搜索树。节点由索引行组成。通常，叶节点的每一行（叶行）都包含一些谓词（布尔表达式）和对表行（TID）的引用。GiST索引最好用于几何数据类型，比如查看两个多边形是否包含某个点。在一种情况下，一个特定的点可能包含在一个框中，而另一个点只存在于一个多边形中。在处理全文搜索时，要利用GiST索引的最常见数据类型是几何类型和文本

在选择使用哪种索引类型（GiST或GIN）时，请考虑以下性能差异：

• GIN索引查找速度比GiST快三倍
• GIN索引的构建时间比GiST长三倍
• GIN索引的更新速度稍慢于GiST索引，但是如果禁用了快速更新支持，则大约慢10倍
• GIN索引比GiST索引大2至3倍

根据经验，GIN索引最适合静态数据，因为查找速度更快。对于动态数据，GiST索引的更新速度更快。

SP-GiST-（空间分区GiST）

对于具有自然但不均匀集群的较大数据集。这种类型的索引利用空间分区树。当您的数据有一个自然的集群元素，并且不是一个均衡的树时，SP-GiST索引最有用。一个很好的例子是电话号码，例如在美国，他们使用以下格式:

• 3位区号
• 前缀为3位数字（与电话运营商的交换机有关）
• 行号的4位数字

这意味着您在第一组3位数，第二组3位数周围有一些自然的聚类，然后数字可能会以更均匀的分布散开。但是，由于电话号码的存在，一些区号比其他区号的饱和度要高得多。结果可能是树非常不平衡。由于预先的自然聚类和数据的不均匀分布，像电话号码这样的数据可以作为spgist的一个很好的例子。

BRIN-（区块范围索引）

对于按顺序排列的大型数据集。块范围是一组彼此相邻的页面，其中所有这些页面的摘要信息存储在索引中。块范围索引可以专注于一些与SP-GiST相似的用例，当对数据进行自然排序时，数据往往非常大，最好使用它们。譬如有十亿记录表，尤其是时间序列数据，BRIN可能会提供有效帮助。如果要查询自然分组在一起的大量数据，例如几个邮政编码的数据（然后汇总到某个城市），则BRIN有助于确保相似的邮政编码在磁盘上彼此靠近。

当您拥有非常大的数据集（例如日期或邮政编码）时，BRIN索引可让您快速跳过或排除许多不必要的数据。此外，BRIN被维护为相对于整体数据大小而言较小的索引，这使它们在处理大型数据集时非常有优势。

结论

在与Oracle的企业平台和业务解决方案竞争时，PostgreSQL具有一些主要优势。将PostgreSQL作为开源RDBMS的首选是绝对可信赖的，因为它几乎像Oracle一样强大。

Oracle很难被击败（这是一个很难接受的事实），并且抛弃技术巨头的企业平台也不容易。当系统为您提供能力和生产成果时，这可能是一个难题。

但有时需要在某些情况下必须做出选择，因为对平台的持续过度投资可能会超过其他业务层优先级的成本，从而影响进度。

可以选择 PostgreSQL及其底层平台解决方案来帮助您降低成本，减轻预算问题；而且所有的变化都不算大。

更多精彩内容，请关注以下平台、网站：

中国Postgre SQL分会官方公众号（技术文章、技术活动）：

开源软件联盟PostgreSQL分会

中国Postgre SQL分会技术问答社区：

www.pgfans.cn

中国Postgre SQL分会官方网站：

www.postgresqlchina.com

 

号外！！！秋季投稿征集令——送走了春夏 迎来了金秋 在这金秋送爽之季 开源软件联盟PostgreSQL分会迎来了新一批的稿件征集 内容主要是与咱postgresql紧密相关即可，文章题材涵盖领域不限，最好是系列型文章 欢迎广大技术人员继续投稿！

投稿格式：word PDF

投稿邮箱：[email protected]

近期我们将举行PGcon活动 有幸邀请到了许多大咖 请大家留意咱官方公众号 听取进一步消息通知安排！

标签：PostgreSQL,GiST,索引,SQL,Oracle,迁移,PL
From： https://www.cnblogs.com/sexintercourse/p/18131903