PostgreSQL已經(jīng)成為當(dāng)下最流行的開源關(guān)系數(shù)據(jù)庫系統(tǒng)之一。當(dāng)客戶從Oracle及微軟SQL Server等商業(yè)數(shù)據(jù)庫向外遷移時，PostgreSQL已經(jīng)成為一大首選替代方案。目前，AWS為大家提供兩種PostgreSQL托管選項(xiàng)：Amazon RDS與Amazon Aurora。
除了提供托管PostgreSQL服務(wù)之外，AWS還準(zhǔn)備了一系列用于協(xié)助遷移的工具與資源。AWS Schema Conversion Tool（SCT）就是一款免費(fèi)AWS工具，可幫助您轉(zhuǎn)換現(xiàn)有schema，且同時支持多個源數(shù)據(jù)庫與目標(biāo)數(shù)據(jù)庫。AWS Database Migration Service（數(shù)據(jù)庫遷移服務(wù)，簡稱DMS）則用于在異構(gòu)數(shù)據(jù)庫與同構(gòu)數(shù)據(jù)庫之間完成數(shù)據(jù)的傳輸與連接復(fù)制。當(dāng)然，AWS還提供遷移指導(dǎo)手冊，其中包含關(guān)于商業(yè)數(shù)據(jù)庫以及開源數(shù)據(jù)庫（包括PostgreSQL）之間的大量功能映射說明。
在今天的文章中，我們將介紹從PL/SQL轉(zhuǎn)換為PL/pgSQL的技巧與最佳實(shí)踐，希望幫助大家在順利將代碼轉(zhuǎn)換為PostgreSQL形式的同時，獲取良好的數(shù)據(jù)庫運(yùn)行性能。本文主要面向從事數(shù)據(jù)庫遷移的開發(fā)人員，并要求您預(yù)先掌握關(guān)于數(shù)據(jù)庫及PL/SQL的基礎(chǔ)知識。
性能考量
本節(jié)主要探討一系列在從商業(yè)或傳統(tǒng)數(shù)據(jù)庫（例如SQL Server及Oracle）向PostgreSQL遷移時，可能對數(shù)據(jù)庫性能造成影響的具體因素。雖然大部分?jǐn)?shù)據(jù)庫中包含類似的對象，但其中仍有部分對象可能在遷移之后影響到系統(tǒng)的運(yùn)作方式。本節(jié)將向大家介紹如何通過調(diào)整存儲流程、函數(shù)以及SQL語句獲得更好的性能。
數(shù)據(jù)類型
為了避免不必要的返工，在正式啟動遷移項(xiàng)目之前，大家需要將目標(biāo)數(shù)據(jù)庫中的數(shù)據(jù)類型與源系統(tǒng)正確映射起來。下表總結(jié)了從Oracle到SQL Server、再到PostgreSQL的一系列常見數(shù)據(jù)類型映射。
為什么要使用smallint/integer/bigint，而不直接使用數(shù)字？
要在數(shù)據(jù)庫中獲取最佳性能，選擇最適合的數(shù)據(jù)類型非常重要。
如果您的表列中最多只能包含四位數(shù)字，那么具有2字節(jié)（smallint）的列數(shù)據(jù)類型就足以完成任務(wù)，意味著我們不必將其定義為4字節(jié)（整數(shù)/實(shí)數(shù)）、8字節(jié)（bigint/雙精度）或者可變字節(jié)（數(shù)字）等更占資源的數(shù)據(jù)類型。
數(shù)值是一種可以容納13萬1千個數(shù)位的復(fù)雜類型，主要用于表示貨幣金額及其他少數(shù)需要極高精度的數(shù)量。但與整數(shù)類型或者浮點(diǎn)類型相比，數(shù)字的運(yùn)算符處理速度很慢，因此計算速度也相當(dāng)緩慢。
在下表的示例當(dāng)中，我們可以看到在分別使用smallint/int/bigint建立無索引非精確列時，表整體大小發(fā)生的變化。
AWS SCT在不了解實(shí)際數(shù)據(jù)大小的情況下，也能夠?qū)?shù)字與表中的數(shù)字?jǐn)?shù)據(jù)類型映射起來。這款工具還提供選項(xiàng)，幫助用戶在轉(zhuǎn)換過程中配置/映射正確的數(shù)據(jù)類型。
存儲過程與函數(shù)
PostgreSQL 10及較早版本并不支持存儲過程。Oracle與SQL Server中的所有存儲過程與函數(shù)都將被映射為PostgreSQL中的函數(shù)。但從版本11開始，PostgreSQL也引入了存儲過程支持，其基本原理與Oracle類似。
PostgreSQL支持三種波動函數(shù)類別，您需要在遷移當(dāng)中根據(jù)函數(shù)特性指定適當(dāng)?shù)念悇e，即：Volatile、Stable與Immutable。正確標(biāo)記函數(shù)類別，有望給我們的數(shù)據(jù)庫性能帶來顯著提升。
Volatile
執(zhí)行以下函數(shù)即可查看執(zhí)行成本。
類型表示該函數(shù)無法修改數(shù)據(jù)庫。此外，Stable還表示在單一表掃描操作當(dāng)中，它對于相同的參數(shù)值將始終返回相同的結(jié)果，但具體結(jié)果可能會在不同SQL語句之間有所區(qū)別。如果需要創(chuàng)建一條結(jié)果取決于數(shù)據(jù)庫查找或者參數(shù)變量（例如當(dāng)前時區(qū)）的函數(shù)，那么Stable類型往往是理想的選擇。current_timestamp函數(shù)家族就是其中的典型代表，它們的值在事務(wù)執(zhí)行過程中始終保持不變。
下面是一條示例函數(shù)，用以顯示執(zhí)行Stable函數(shù)需要花費(fèi)多長時間。
執(zhí)行以下函數(shù)即可查看執(zhí)行成本。
Immutable
Immutable
類型表示該函數(shù)無法修改數(shù)據(jù)庫，而且在給定相同的參數(shù)值時將始終返回相同的結(jié)果。這意味著起無法自行數(shù)據(jù)庫查找，也無法使用參數(shù)列表中未直接存在的信息。如果選定此選項(xiàng)，對該函數(shù)的一切全常數(shù)參數(shù)調(diào)用都將被立即替換為該函數(shù)的值。
下面是一條示例函數(shù)，用以顯示執(zhí)行Immutable函數(shù)需要花費(fèi)多長時間。
執(zhí)行以下函數(shù)即可查看執(zhí)行成本。
對各函數(shù)執(zhí)行的測試結(jié)果表明，這些函數(shù)的基本功能完全相同，但其中Immutable函數(shù)的執(zhí)行時長最短。這是因?yàn)镮mmutable類別允許優(yōu)化程序在查詢調(diào)用期間通過常量參數(shù)對該函數(shù)進(jìn)行預(yù)評估。
視圖與查詢中的函數(shù)調(diào)用
許多應(yīng)用程序都會使用包含函數(shù)調(diào)用的視圖與查詢。如上一節(jié)所述，在PostgreSQL當(dāng)中，函數(shù)調(diào)用有可能占用大量資源，特別是在未能正確設(shè)置函數(shù)volatility類別的情況下。此外，函數(shù)調(diào)用本身也會增加相應(yīng)的查詢成本。
為此，我們需要根據(jù)函數(shù)功能為函數(shù)選擇適當(dāng)類別。如果您的函數(shù)更適合Immutable或者Stable條件，那么正確設(shè)置以取代默認(rèn)的Volatile將帶來一定性能優(yōu)勢。
以下示例代碼，為一條包含Volatile函數(shù)調(diào)用的查詢。
其中的getDeptname()函數(shù)被標(biāo)記為volatile。該查詢的總運(yùn)行時長為2秒886毫秒。
下面來看包含Stable函數(shù)調(diào)用的查詢示例。
其中的getDeptname()函數(shù)被標(biāo)記為stable，其總運(yùn)行時長為2秒644毫秒。
以下示例代碼將函數(shù)替換為功能。
Exception優(yōu)化
PostgreSQL允許用戶使用Exception與Raise語句捕捉并觸發(fā)錯誤的功能。這項(xiàng)功能雖然具有現(xiàn)實(shí)意義，但也要付出一定代價。Raise語句會在PL/pgSQL函數(shù)的執(zhí)行過程中引發(fā)錯誤與異常。在默認(rèn)情況下，PL/pgSQL函數(shù)內(nèi)部發(fā)生的任何錯誤都會導(dǎo)致執(zhí)行中止以及變更回滾。為了從錯誤中正?；謴?fù)，PL/pgSQL可以使用Exception子句捕捉具體錯誤。要實(shí)現(xiàn)這項(xiàng)功能，我們需要保證PostgreSQL在輸入還有異常處理的代碼段之前保存事務(wù)狀態(tài)。這項(xiàng)操作會占用大量資源，因此間接增加了運(yùn)行成本。
為了避免這部分成本，我們的建議是：要么在應(yīng)用程序端捕捉異常，要么確保提前進(jìn)行必要驗(yàn)證、使得函數(shù)永遠(yuǎn)不會發(fā)生異常。
以下代碼示例展示了在函數(shù)調(diào)用中納入異常，會給數(shù)據(jù)庫性能造成怎樣的影響。
如果大家無法在無異常狀況下進(jìn)行驗(yàn)證，那么異常將不可避免。在以上示例中，我們可以檢查診斷結(jié)果以跟蹤是否存在需要關(guān)注的變更。另外，如果可能，請盡量不要使用異常處理機(jī)制。
無需提取操作的計數(shù)器
不少應(yīng)用程序需要進(jìn)行游標(biāo)循環(huán)并獲取計數(shù)，才能完成記錄內(nèi)容的提取工作。由于提取操作會在無對應(yīng)記錄時返回null，因此最好能用提取狀態(tài)來替代聲明兩項(xiàng)變量的傳統(tǒng)計數(shù)檢查方法。如此一來，我們可以避免聲明額外變量并對其進(jìn)行檢查，從而減少需要執(zhí)行的語句數(shù)量并獲得更好的性能。具體請參見以下代碼示例。
我們也可以按照以下步驟重新編寫上述代碼，其中使用游標(biāo)本體進(jìn)行迭代并利用游標(biāo)狀態(tài)中斷/退出循環(huán)，這就有效避免了引入兩個新的變量。
檢查EXISTS，而非直接計數(shù)
在舊版應(yīng)用程序當(dāng)中編寫SQL查詢時，我們首先需要查找匹配的記錄數(shù)，而后才能應(yīng)用所需的業(yè)務(wù)邏輯。如果表中包含數(shù)十億條記錄，那么獲取記錄數(shù)量往往需要占用大量資源。
以下代碼示例演示了如何先檢查行數(shù)，而后更新數(shù)據(jù)。
此查詢的總運(yùn)行時長為163毫秒。
我們也可以重新編寫代碼以檢查一列——而非一整行，這樣可以節(jié)約成本并提高性能。具體請參見以下代碼示例。
這條查詢的總運(yùn)行時長為104毫秒。
在DML語句后記錄計數(shù)結(jié)果
在大多數(shù)舊版應(yīng)用程序中，我們可以通過記錄計數(shù)結(jié)果來判斷數(shù)據(jù)操作語句是否引發(fā)了變更。在PostgreSQL中，這部分信息被保留在統(tǒng)計信息當(dāng)中，用戶可以隨時檢索以避免在操作之后對值進(jìn)行計數(shù)。我們可以使用診斷程序檢索受影響的行數(shù)，具體如以下代碼示例所示。
從表中檢索數(shù)據(jù)時，常見的做法是將通配符%或 _ 與LIKE表達(dá)式配合使用（在進(jìn)行非敏感搜索時也可以使用ILIKE）。如果通配符位于給定schema的開頭，那么即使存在索引，查詢規(guī)劃程序也無法使用該索引。在這種情況下，我們就必須使用順序掃描，而這是一項(xiàng)相當(dāng)耗時的操作。為了在處理數(shù)百萬條記錄時獲得良好性能，并保證查詢規(guī)劃程序正常使用可用的索引，大家需要在謂詞的中間或結(jié)尾處（而非開頭）使用通配符，從而強(qiáng)制引導(dǎo)規(guī)劃程序使用索引。
除了LIKE表達(dá)式之外，大家也可以使用pg_trgm模塊/擴(kuò)展進(jìn)行模式匹配。其中pg_trgm模塊將為我們提供用于確定字母數(shù)字文本相似性的函數(shù)與運(yùn)算符，同時提供支持相似字符串快速搜索的索引運(yùn)算符類。關(guān)于更多詳細(xì)信息，請參閱PostgreSQL網(wǎng)站上發(fā)布的pg_tram說明文檔。
在Oracle、SQL Server以及PostgreSQL之間進(jìn)行映射轉(zhuǎn)換
本節(jié)主要介紹在Oracle、SQL Server以及PostgreSQL數(shù)據(jù)庫中編寫SQL語句方面的不同之處。
默認(rèn)FROM子句
在Oracle當(dāng)中，FROM子句具有強(qiáng)制性，因此只能在代碼中使用Select 1 from Dual;。而在PostgreSQL與SQL當(dāng)中，大家可以選擇使用代碼Select 1;。
生成值集合
通過指定開始數(shù)字與結(jié)束數(shù)字，我們可以生成一個值集合。
在Oracle中，我們不需要起始數(shù)字，但可以提供結(jié)束數(shù)字。具體代碼示例如下。
在使用起始與結(jié)束數(shù)字時，使用以下代碼。
聯(lián)接（+）運(yùn)算符
在Oracle中，左聯(lián)接運(yùn)算的實(shí)現(xiàn)需使用以下代碼。
若需了解更多詳細(xì)信息，請參閱Oracle數(shù)據(jù)庫網(wǎng)站上的SQL新手指南（第五部分）：聯(lián)接。
PostgreSQL與SQL Server上并不存在“+”這種可對表進(jìn)行左右聯(lián)接的功能；相反，二者使用以下兩項(xiàng)查詢。
將類型作為函數(shù)參數(shù)
在SQL Server中，我們可以使用Type數(shù)據(jù)類型傳遞多條記錄。要在PostgreSQL中實(shí)現(xiàn)相同的效果，大家可以通過JSON格式或者數(shù)組形式將該類型視為JSON或者文本數(shù)據(jù)類型。在以下示例代碼中，JSON格式的文本數(shù)據(jù)類型就包含有多條記錄。您可以將其插入臨時表，并在后續(xù)代碼中執(zhí)行進(jìn)一步處理。
Oracle
以下代碼所示，為多條記錄如何在Oracle的varchar數(shù)據(jù)類型中實(shí)現(xiàn)傳遞。
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的表類型中實(shí)現(xiàn)傳遞。
PostgreSQL
以下代碼所示，為多條記錄如何在PostgreSQL中實(shí)現(xiàn)與之前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轉(zhuǎn)換
在PostgreSQL當(dāng)中，pivoting功能無法直接啟用，需要額外擴(kuò)展提供支持。tablefunc擴(kuò)展帶來的crosstab函數(shù)可用于創(chuàng)建數(shù)據(jù)pivot表，其功能與SQL Server及Oracle類似。以下是Oracle、SQL Server以及PostgreSQL中的pvioting功能代碼。
Oracle
使用以下代碼在Oracle中實(shí)現(xiàn)pivoting功能。
SQL Server
使用以下代碼在SQL Server中實(shí)現(xiàn)pivoting功能。
PostgreSQL
使用以下代碼在PostgreSQL中實(shí)現(xiàn)pivoting功能。
對數(shù)組進(jìn)行unpivoting
PostgreSQL同樣無法直接提供Unpivot函數(shù)。在將SQL Server或者Oracle轉(zhuǎn)換為PostgreSQL時，unpivot函數(shù)會被映射為一個數(shù)組。具體請參見以下代碼示例。
Oracle
使用以下代碼示例在Oracle中實(shí)現(xiàn)unpivoting功能。
SQL ServerSQL Server
使用以下代碼示例在SQL Server中實(shí)現(xiàn)unpivoting功能。
從單一函數(shù)處返回多個結(jié)果集
SQL Server可以將多條結(jié)果按多行形式直接返回。大家可以使用游標(biāo)在PostgreSQL與Oracle中實(shí)現(xiàn)相同的效果，如以下示例所示。
Oracle
使用以下代碼在Oracle中通過單一過程返回多個結(jié)果集。
SQL Server
使用以下代碼在SQL Server中通過單一過程返回多個結(jié)果集。SQL Server不需要使用額外其他參數(shù)。
PostgreSQL
使用以下代碼在PostgreSQL中通過單一過程返回多個結(jié)果集。
帶別名的內(nèi)聯(lián)查詢
PostgreSQL語義可將內(nèi)聯(lián)視圖稱為Subselect或者Subquery。Oracle支持在內(nèi)部語句中省略別名，PostgreSQL與SQL Server則要求必須使用別名。
Oracle
使用以下代碼在Oracle中執(zhí)行內(nèi)聯(lián)查詢演示。
SQL Server與PostgreSQL
在Oracle中編寫的示例內(nèi)聯(lián)查詢?nèi)粢赟QL Server及PostgreSQL中運(yùn)行，則必須使用別名
數(shù)據(jù)順序
將數(shù)據(jù)從Oracle或SQL Server遷移至PostgreSQL之后，數(shù)據(jù)的檢索順序也可能發(fā)生改變。這種改變可能是受到了插入順序、列數(shù)據(jù)類型及具體數(shù)值、或者排序規(guī)則的影響。
為了保證數(shù)據(jù)順序的正確性，我們需要確定業(yè)務(wù)需求并在查詢當(dāng)中采用Order by子句以匹配數(shù)據(jù)內(nèi)容。
dblink與外部數(shù)據(jù)包裝器
dblink是一項(xiàng)負(fù)責(zé)在同構(gòu)與異構(gòu)數(shù)據(jù)庫間實(shí)現(xiàn)通信的功能。截至本文撰寫之時，Amazon RDS與Aurora PostgreSQL還不提供異構(gòu)支持，但已經(jīng)能夠支持跨PostgreSQL數(shù)據(jù)庫間的通信。
跨同構(gòu)數(shù)據(jù)庫通信
PostgreSQL可利用dblink與外部數(shù)據(jù)包裝器（FDW）實(shí)現(xiàn)跨數(shù)據(jù)庫間的正常通信。在本節(jié)中，我們將具體聊聊dblink與FDW的使用方法。
使用外部數(shù)據(jù)包裝器
·使用以下代碼創(chuàng)建該擴(kuò)展。
Select*From imported_public2.emptable
跨異構(gòu)數(shù)據(jù)庫通信
PostgreSQL不支持跨數(shù)據(jù)庫通信。在實(shí)現(xiàn)跨數(shù)據(jù)庫的異構(gòu)通信方面，Amazon Aurora PostgreSQL確實(shí)存在一定局限，但大家可以在源環(huán)境（例如Oracle或者SQL Server）上建立指向目標(biāo)（PostgreSQL）的dblink，而后對數(shù)據(jù)執(zhí)行pull或push操作。
若需了解更多詳細(xì)信息，請參閱 在Compose PostgreSQL上進(jìn)行跨數(shù)據(jù)庫查詢。
使用dblink為外部數(shù)據(jù)庫表創(chuàng)建視圖
選項(xiàng)二：對訪問細(xì)節(jié)進(jìn)行拆分，并使用連接對象
在這種選項(xiàng)中，主機(jī)與連接細(xì)節(jié)在同一個位置進(jìn)行定義，并使用連接名稱實(shí)現(xiàn)跨數(shù)據(jù)庫連接。
考慮使用自聯(lián)接以實(shí)現(xiàn)更新
當(dāng)在select語句中的from子句內(nèi)使用相同的源表（正在更新的表）時，PostgreSQL與SQL Server的具體更新機(jī)制將有所區(qū)別。與SQL Server不同，PostgreSQL中from子句的第二次引用將獨(dú)立于第一次引用，且變更將被應(yīng)用于整個表。
以下示例代碼，用于更新部門1中員工的薪水。
總結(jié)
本文從商業(yè)數(shù)據(jù)庫到PostgreSQL的遷移場景出發(fā)，向開發(fā)者朋友們分享了一些技巧與最佳實(shí)踐。本文的重點(diǎn)在于介紹遷移過程中需要面對的種種決策，以及決策結(jié)果給數(shù)據(jù)庫性能造成怎樣的影響。在遷移過程中，請牢記這些性能方面的影響因素，這將幫助大家提前避免隨后可能因遷移出現(xiàn)的種種性能問題。
本篇作者
Viswanatha Shastry Medipalli
印度AWS ProServe團(tuán)隊(duì)顧問。他在SQL數(shù)據(jù)庫遷移領(lǐng)域擁有廣泛的專業(yè)知識與經(jīng)驗(yàn)積累。他曾參與設(shè)計過眾多成功的數(shù)據(jù)庫解決方案，幫助客戶克服一系列極具挑戰(zhàn)性的業(yè)務(wù)難題。他曾利用Oracle、SQL Server以及PostgreSQL構(gòu)建起用于報告、商務(wù)智能、應(yīng)用程序及開發(fā)等場景的解決方案。此外，他還擁有豐富的自動化與編排專業(yè)知識。目前，他的工作重心是由本地數(shù)據(jù)庫向Amazon RDS/Aurora PostgreSQL的同構(gòu)與異構(gòu)遷移。
AWS
專欄
閩公網(wǎng)安備 35010202001226號
