PostgreSQL已经成为当下最流行的开源关系数据库系统之一。当客户从Oracle及微软SQL Server等商业数据库向外迁移时，PostgreSQL已经成为一大首选替代方案。目前，AWS为大家提供两种PostgreSQL托管选项：Amazon RDS与Amazon Aurora。
除了提供托管PostgreSQL服务之外，AWS还准备了一系列用于协助迁移的工具与资源。AWS Schema Conversion Tool（SCT）就是一款免费AWS工具，可帮助您转换现有schema，且同时支持多个源数据库与目标数据库。AWS Database Migration Service（数据库迁移服务，简称DMS）则用于在异构数据库与同构数据库之间完成数据的传输与连接复制。当然，AWS还提供迁移指导手册，其中包含关于商业数据库以及开源数据库（包括PostgreSQL）之间的大量功能映射说明。
在今天的文章中，我们将介绍从PL/SQL转换为PL/pgSQL的技巧与最佳实践，希望帮助大家在顺利将代码转换为PostgreSQL形式的同时，获取良好的数据库运行性能。本文主要面向从事数据库迁移的开发人员，并要求您预先掌握关于数据库及PL/SQL的基础知识。
性能考量
本节主要探讨一系列在从商业或传统数据库（例如SQL Server及Oracle）向PostgreSQL迁移时，可能对数据库性能造成影响的具体因素。虽然大部分数据库中包含类似的对象，但其中仍有部分对象可能在迁移之后影响到系统的运作方式。本节将向大家介绍如何通过调整存储流程、函数以及SQL语句获得更好的性能。
数据类型
为了避免不必要的返工，在正式启动迁移项目之前，大家需要将目标数据库中的数据类型与源系统正确映射起来。下表总结了从Oracle到SQL Server、再到PostgreSQL的一系列常见数据类型映射。
为什么要使用smallint/integer/bigint，而不直接使用数字？
要在数据库中获取最佳性能，选择最适合的数据类型非常重要。
如果您的表列中最多只能包含四位数字，那么具有2字节（smallint）的列数据类型就足以完成任务，意味着我们不必将其定义为4字节（整数/实数）、8字节（bigint/双精度）或者可变字节（数字）等更占资源的数据类型。
数值是一种可以容纳13万1千个数位的复杂类型，主要用于表示货币金额及其他少数需要极高精度的数量。但与整数类型或者浮点类型相比，数字的运算符处理速度很慢，因此计算速度也相当缓慢。
在下表的示例当中，我们可以看到在分别使用smallint/int/bigint建立无索引非精确列时，表整体大小发生的变化。
AWS SCT在不了解实际数据大小的情况下，也能够将数字与表中的数字数据类型映射起来。这款工具还提供选项，帮助用户在转换过程中配置/映射正确的数据类型。
存储过程与函数
PostgreSQL 10及较早版本并不支持存储过程。Oracle与SQL Server中的所有存储过程与函数都将被映射为PostgreSQL中的函数。但从版本11开始，PostgreSQL也引入了存储过程支持，其基本原理与Oracle类似。
PostgreSQL支持三种波动函数类别，您需要在迁移当中根据函数特性指定适当的类别，即：Volatile、Stable与Immutable。正确标记函数类别，有望给我们的数据库性能带来显著提升。
Volatile
执行以下函数即可查看执行成本。
类型表示该函数无法修改数据库。此外，Stable还表示在单一表扫描操作当中，它对于相同的参数值将始终返回相同的结果，但具体结果可能会在不同SQL语句之间有所区别。如果需要创建一条结果取决于数据库查找或者参数变量（例如当前时区）的函数，那么Stable类型往往是理想的选择。current_timestamp函数家族就是其中的典型代表，它们的值在事务执行过程中始终保持不变。
下面是一条示例函数，用以显示执行Stable函数需要花费多长时间。
执行以下函数即可查看执行成本。
Immutable
Immutable
类型表示该函数无法修改数据库，而且在给定相同的参数值时将始终返回相同的结果。这意味着起无法自行数据库查找，也无法使用参数列表中未直接存在的信息。如果选定此选项，对该函数的一切全常数参数调用都将被立即替换为该函数的值。
下面是一条示例函数，用以显示执行Immutable函数需要花费多长时间。
执行以下函数即可查看执行成本。
对各函数执行的测试结果表明，这些函数的基本功能完全相同，但其中Immutable函数的执行时长最短。这是因为Immutable类别允许优化程序在查询调用期间通过常量参数对该函数进行预评估。
视图与查询中的函数调用
许多应用程序都会使用包含函数调用的视图与查询。如上一节所述，在PostgreSQL当中，函数调用有可能占用大量资源，特别是在未能正确设置函数volatility类别的情况下。此外，函数调用本身也会增加相应的查询成本。
为此，我们需要根据函数功能为函数选择适当类别。如果您的函数更适合Immutable或者Stable条件，那么正确设置以取代默认的Volatile将带来一定性能优势。
以下示例代码，为一条包含Volatile函数调用的查询。
其中的getDeptname()函数被标记为volatile。该查询的总运行时长为2秒886毫秒。
下面来看包含Stable函数调用的查询示例。
其中的getDeptname()函数被标记为stable，其总运行时长为2秒644毫秒。
以下示例代码将函数替换为功能。
Exception优化
PostgreSQL允许用户使用Exception与Raise语句捕捉并触发错误的功能。这项功能虽然具有现实意义，但也要付出一定代价。Raise语句会在PL/pgSQL函数的执行过程中引发错误与异常。在默认情况下，PL/pgSQL函数内部发生的任何错误都会导致执行中止以及变更回滚。为了从错误中正常恢复，PL/pgSQL可以使用Exception子句捕捉具体错误。要实现这项功能，我们需要保证PostgreSQL在输入还有异常处理的代码段之前保存事务状态。这项操作会占用大量资源，因此间接增加了运行成本。
为了避免这部分成本，我们的建议是：要么在应用程序端捕捉异常，要么确保提前进行必要验证、使得函数永远不会发生异常。
以下代码示例展示了在函数调用中纳入异常，会给数据库性能造成怎样的影响。
如果大家无法在无异常状况下进行验证，那么异常将不可避免。在以上示例中，我们可以检查诊断结果以跟踪是否存在需要关注的变更。另外，如果可能，请尽量不要使用异常处理机制。
无需提取操作的计数器
不少应用程序需要进行游标循环并获取计数，才能完成记录内容的提取工作。由于提取操作会在无对应记录时返回null，因此最好能用提取状态来替代声明两项变量的传统计数检查方法。如此一来，我们可以避免声明额外变量并对其进行检查，从而减少需要执行的语句数量并获得更好的性能。具体请参见以下代码示例。
我们也可以按照以下步骤重新编写上述代码，其中使用游标本体进行迭代并利用游标状态中断/退出循环，这就有效避免了引入两个新的变量。
检查EXISTS，而非直接计数
在旧版应用程序当中编写SQL查询时，我们首先需要查找匹配的记录数，而后才能应用所需的业务逻辑。如果表中包含数十亿条记录，那么获取记录数量往往需要占用大量资源。
以下代码示例演示了如何先检查行数，而后更新数据。
此查询的总运行时长为163毫秒。
我们也可以重新编写代码以检查一列——而非一整行，这样可以节约成本并提高性能。具体请参见以下代码示例。
这条查询的总运行时长为104毫秒。
在DML语句后记录计数结果
在大多数旧版应用程序中，我们可以通过记录计数结果来判断数据操作语句是否引发了变更。在PostgreSQL中，这部分信息被保留在统计信息当中，用户可以随时检索以避免在操作之后对值进行计数。我们可以使用诊断程序检索受影响的行数，具体如以下代码示例所示。
从表中检索数据时，常见的做法是将通配符%或 _ 与LIKE表达式配合使用（在进行非敏感搜索时也可以使用ILIKE）。如果通配符位于给定schema的开头，那么即使存在索引，查询规划程序也无法使用该索引。在这种情况下，我们就必须使用顺序扫描，而这是一项相当耗时的操作。为了在处理数百万条记录时获得良好性能，并保证查询规划程序正常使用可用的索引，大家需要在谓词的中间或结尾处（而非开头）使用通配符，从而强制引导规划程序使用索引。
除了LIKE表达式之外，大家也可以使用pg_trgm模块/扩展进行模式匹配。其中pg_trgm模块将为我们提供用于确定字母数字文本相似性的函数与运算符，同时提供支持相似字符串快速搜索的索引运算符类。关于更多详细信息，请参阅PostgreSQL网站上发布的pg_tram说明文档。
在Oracle、SQL Server以及PostgreSQL之间进行映射转换
本节主要介绍在Oracle、SQL Server以及PostgreSQL数据库中编写SQL语句方面的不同之处。
默认FROM子句
在Oracle当中，FROM子句具有强制性，因此只能在代码中使用Select 1 from Dual;。而在PostgreSQL与SQL当中，大家可以选择使用代码Select 1;。
生成值集合
通过指定开始数字与结束数字，我们可以生成一个值集合。
在Oracle中，我们不需要起始数字，但可以提供结束数字。具体代码示例如下。
在使用起始与结束数字时，使用以下代码。
联接（+）运算符
在Oracle中，左联接运算的实现需使用以下代码。
若需了解更多详细信息，请参阅Oracle数据库网站上的SQL新手指南（第五部分）：联接。
PostgreSQL与SQL Server上并不存在“+”这种可对表进行左右联接的功能；相反，二者使用以下两项查询。
将类型作为函数参数
在SQL Server中，我们可以使用Type数据类型传递多条记录。要在PostgreSQL中实现相同的效果，大家可以通过JSON格式或者数组形式将该类型视为JSON或者文本数据类型。在以下示例代码中，JSON格式的文本数据类型就包含有多条记录。您可以将其插入临时表，并在后续代码中执行进一步处理。
Oracle
以下代码所示，为多条记录如何在Oracle的varchar数据类型中实现传递。
DECLARE
StructType Varchar2(1000) Default ‘[{“empid” : 1, “last_name”:”AccName1″, “first_name”:”AccName1″, “deptid”:”1″, “salary”:”1234.578″}
,{“empid” : “2”, “last_name”:”AccName2″, “first_name”:”AccName2″, “deptid”:”2″, “salary”:”4567.578″}
]’;
Begin
Insert Into emptable1 (empid,last_name,first_name,deptid,salary)
With Json As
( Select StructType –‘[{“firstName”: “Tobias”, “lastName”:”Jellema”},{“firstName”: “Anna”, “lastName”:”Vink”} ]’ doc
from dual
)
Select empid,last_name,first_name,deptid,salary
From json_table( (Select StructType from json) , ‘$[*]’
Columns ( empid PATH ‘$.empid’
,last_name Path ‘$.last_name’
, first_name Path ‘$.first_name’
,deptid Path ‘$.deptid’
,salary Path ‘$.salary’
)
);
End;
SQL Server
以下代码所示，为多条记录如何在SQL Server的表类型中实现传递。
PostgreSQL
以下代码所示，为多条记录如何在PostgreSQL中实现与之前Oracle及SQL Server相同的传递效果。
Do $$
Declare
StructType Text Default ‘[{“empid” : “1”, “last_name”:”AccName1″, “first_name”:”AccName1″, “deptid”:”1″, “salary”:”1234.578″},
{“empid” : “2”, “last_name”:”AccName2″, “first_name”:”AccName2″, “deptid”:”2″, “salary”:”4567.578″}]’;
Begin
Insert Into emptable
Select * From json_to_recordset(StructType::json)
as x(“empid” Int, “last_name” Varchar, “first_name” Varchar, “deptid” Int, “salary” Double Precision);
Pivoting转换
在PostgreSQL当中，pivoting功能无法直接启用，需要额外扩展提供支持。tablefunc扩展带来的crosstab函数可用于创建数据pivot表，其功能与SQL Server及Oracle类似。以下是Oracle、SQL Server以及PostgreSQL中的pvioting功能代码。
Oracle
使用以下代码在Oracle中实现pivoting功能。
SQL Server
使用以下代码在SQL Server中实现pivoting功能。
PostgreSQL
使用以下代码在PostgreSQL中实现pivoting功能。
对数组进行unpivoting
PostgreSQL同样无法直接提供Unpivot函数。在将SQL Server或者Oracle转换为PostgreSQL时，unpivot函数会被映射为一个数组。具体请参见以下代码示例。
Oracle
使用以下代码示例在Oracle中实现unpivoting功能。
SQL ServerSQL Server
使用以下代码示例在SQL Server中实现unpivoting功能。
从单一函数处返回多个结果集
SQL Server可以将多条结果按多行形式直接返回。大家可以使用游标在PostgreSQL与Oracle中实现相同的效果，如以下示例所示。
Oracle
使用以下代码在Oracle中通过单一过程返回多个结果集。
SQL Server
使用以下代码在SQL Server中通过单一过程返回多个结果集。SQL Server不需要使用额外其他参数。
PostgreSQL
使用以下代码在PostgreSQL中通过单一过程返回多个结果集。
带别名的内联查询
PostgreSQL语义可将内联视图称为Subselect或者Subquery。Oracle支持在内部语句中省略别名，PostgreSQL与SQL Server则要求必须使用别名。
Oracle
使用以下代码在Oracle中执行内联查询演示。
SQL Server与PostgreSQL
在Oracle中编写的示例内联查询若要在SQL Server及PostgreSQL中运行，则必须使用别名
数据顺序
将数据从Oracle或SQL Server迁移至PostgreSQL之后，数据的检索顺序也可能发生改变。这种改变可能是受到了插入顺序、列数据类型及具体数值、或者排序规则的影响。
为了保证数据顺序的正确性，我们需要确定业务需求并在查询当中采用Order by子句以匹配数据内容。
dblink与外部数据包装器
dblink是一项负责在同构与异构数据库间实现通信的功能。截至本文撰写之时，Amazon RDS与Aurora PostgreSQL还不提供异构支持，但已经能够支持跨PostgreSQL数据库间的通信。
跨同构数据库通信
PostgreSQL可利用dblink与外部数据包装器（FDW）实现跨数据库间的正常通信。在本节中，我们将具体聊聊dblink与FDW的使用方法。
使用外部数据包装器
·使用以下代码创建该扩展。
Select*From imported_public2.emptable
跨异构数据库通信
PostgreSQL不支持跨数据库通信。在实现跨数据库的异构通信方面，Amazon Aurora PostgreSQL确实存在一定局限，但大家可以在源环境（例如Oracle或者SQL Server）上建立指向目标（PostgreSQL）的dblink，而后对数据执行pull或push操作。
若需了解更多详细信息，请参阅 在Compose PostgreSQL上进行跨数据库查询。
使用dblink为外部数据库表创建视图
选项二：对访问细节进行拆分，并使用连接对象
在这种选项中，主机与连接细节在同一个位置进行定义，并使用连接名称实现跨数据库连接。
考虑使用自联接以实现更新
当在select语句中的from子句内使用相同的源表（正在更新的表）时，PostgreSQL与SQL Server的具体更新机制将有所区别。与SQL Server不同，PostgreSQL中from子句的第二次引用将独立于第一次引用，且变更将被应用于整个表。
以下示例代码，用于更新部门1中员工的薪水。
总结
本文从商业数据库到PostgreSQL的迁移场景出发，向开发者朋友们分享了一些技巧与最佳实践。本文的重点在于介绍迁移过程中需要面对的种种决策，以及决策结果给数据库性能造成怎样的影响。在迁移过程中，请牢记这些性能方面的影响因素，这将帮助大家提前避免随后可能因迁移出现的种种性能问题。
本篇作者
Viswanatha Shastry Medipalli
印度AWS ProServe团队顾问。他在SQL数据库迁移领域拥有广泛的专业知识与经验积累。他曾参与设计过众多成功的数据库解决方案，帮助客户克服一系列极具挑战性的业务难题。他曾利用Oracle、SQL Server以及PostgreSQL构建起用于报告、商务智能、应用程序及开发等场景的解决方案。此外，他还拥有丰富的自动化与编排专业知识。目前，他的工作重心是由本地数据库向Amazon RDS/Aurora PostgreSQL的同构与异构迁移。
相关推荐: 盛夏来袭，科学防暑，纸鸢APP为您带来清凉解暑好物盛夏来袭，科学防暑，纸鸢APP为您带来清凉解暑好物
近期高温频发，热射病成为人们关注的焦点。为了帮助大家科学有效地预防高温疾病，纸鸢APP特别推出了一系列防暑建议和清凉解暑好物，为消费者带来舒爽的夏日体验。 面对高温天气，科学的防暑措施尤为重要。炎热天气下，身体容易失水，因此要多喝水补充水分。同时避免高温时段外…
码刀科技（www.lekshop.cn）是国内知名企业级电商平台提供商，为企业级商家提供最佳的电商平台搭建（多种模式电商平台搭建：B2B/B2B2C/B2C/O2O/新零售/跨境等）、平台管理系统开发及互联网采购解决方案服务, 联系客服了解更多.
